Kentico CMS High Availability & Scalability – Backup & Restore
After a few months I’m back with the second part of the Kentico CMS High Availability and Scalability series. This part is focused on Backup & Restore of your databases.
Backup databases
As you can read in
my previous blog post, Backup & Restore is one of the oldest HA features and it’s very helpful (can say the only way) in situations when users modify any data and it’s necessary to rollback these changes.
In the past, there were just simple backup & restore database operations, but as the SQL Server became more and more sophisticated, the backup & restore possibilities got more complex. Before you start to backup & restore your data, you should get familiar with the recovery models and backup types.
Recovery models
Latest versions of MS SQL Server support three different recovery models which determine how transactions are logged and therefore have an impact on the type of backup operations that can be performed on a database. Before backing up your database, it’s extremely important to select the right recovery model.
MS SQL Server 2008 offers the following models:
-
Full recovery model: Provides the highest level of data protection by logging all transactions. It is usually the most preferred recovery model for all production databases. It also gives you the most flexibility in your backup strategy by allowing you to perform any type of backup available in SQL Server.
-
Bulk-logged recovery model: This model is very similar to the full recovery model, except for the bulk operations where the end result is logged instead of the entire operation. The limitation of this model is that you may not be able to do a point-in-time recovery.
-
Simple recovery model: This model does not offer any type of logged data protection and is generally not used in production environments that require point-in-time recovery since the last full backup. When using this model, you cannot perform transaction log backups and bulk operations are also logged minimally.
To determine which recovery model you are using, use the following T-SQL command:
SELECT DATABASEPROPERTYEX('<<database_name>>','RECOVERY');
To change it, use:
ALTER DATABASE <<database_name>> SET RECOVERY FULL;
Backup types
SQL Server supports several options for backing up the whole or just a part of your database. The most common backup type is full backup, followed by the differential backup and transaction log backup. In the next part, we will look a bit closer at these types.
Full backup
This backup captures the database, including the transaction log, so that the database may be completely restored to a consistent state at the point-in-time when the full backup was completed. Any uncommitted transactions that were open at the time the backup completed will be rolled back during the restore.
The full backup is also a start point for subsequent differential and log backups. You can’t run differential or log backups on a database if you have never taken a full backup.
Basic syntax to create a full backup is:
BACKUP DATABASE <<database_name>>
TO DISK = '<<full_path_to_the_backup_file>>'
Differential backup
A differential backup captures all changes that have occurred in the database since the last full backup. Each differential backup contains all changes made since the last full backup, not just the changes made since the last differential backup.
To create a differential backup we use the following syntax:
BACKUP DATABASE <<database_name>>
TO DISK = '<<full_path_to_the_backup_file>>'
WITH DIFFERENTIAL
Transactional log backup
Transactional log backup not only provides the highest level of data protection, but it also truncates the inactive part of the log. If you never back up your transaction log, it will never be truncated and will ultimately grow out of control. Each transaction log backup only contains the new records that were not backed up in the previous transaction log backup.
You cannot make transaction log backups using the simple recovery model because SQL Server truncates the log on checkpoint automatically in this model.
Basic syntax to create a transactional log backup is:
BACKUP LOG <<database_name>>
TO DISK = '<<full_path_to_the_backup_file>>'
Partial backup
Partial backups were introduced in SQL Server 2005 to provide an alternative to back up large databases that contain read-only filegroups. Partial backups are similar to full backups, except that they are designed to back up only the primary filegroup or any optionally read/write or read-only filegroups. You can use differential partial backup that contains only the extents changed since the last differential partial backup.
File backup
File backup allows you to create a backup of an individual file or filegroup. This type doesn’t backup the transactional log, so to completely restore a database using file backups, you must have the appropriate transaction log backups as well. You can also create a differential file backup that contains the changed extents since the last full file backup.
Copy-only backup
Copy-only backup was introduced in SQL Server 2005 to enable you to backup a database and a transactional log without interfering with the normal backup and restore routine. You can’t use Copy-only backup when the simple recovery mode is enabled.
Other backup features
There are a few other features that can have impact on your backup strategy. They contain:
-
Backup destination: When creating a backup, we can save it on a disk or a tape. It is also possible to mirror it on other file/tape destination.
-
Backup compressions: This feature was introduced in SQL 2008 Enterprise Edition and is also available in SQL 2008 R2 Standard Edition. This feature is turned off at the server level by default. It allows you to create a compressed backup.
-
Backup Encryption: For security reasons, you can also encrypt the backup. There are several options like encryption by password, by private key, by master key, etc.
Backup strategy
As you can see, there are several possibilities how to back up your data. Depending on the size of the database, your hardware, security requirements, etc., you can establish several backup strategies.
System databases
Backup of your system databases has to be a part of your backup strategy. Unfortunately, this step is very often ignored by database administrators. Why is it so important? Information about your SQL Server, users, jobs for SQL Agent, information about replications, backups and many more is stored in the system databases.
The following is a list of the system databases and some things that you should know about them:
-
master: The master database is the most important as it contains information about other databases, logins, etc. Its backup uses the simple recovery model, and you can only perform full database backups of the master database.
-
msdb: The msdb database logs historical information for items like backups, SQL Agent jobs or database mail. It uses the simple recovery model by default, but it can be configured to use any recovery model.
-
model: This database uses the full recovery model by default, but it can be configured to use any other recovery model. The model database serves as a template for all new user databases, if you change the recovery model for it, all newly created databases will be created with this recovery model as well.
-
tempdb: The recovery model for tempdb is simple and cannot be changed. Every time you restart your SQL Server, this database is recreated. Since the database is recreated every time SQL Server is restarted, you are not allowed to perform any type of backup operations on it.
So, when backup system databases? Whenever you create any modification of them….
User databases
Before you will start to back up your database, please consider how “large” your database is, which type of devices you will use, do you need an encrypted backup etc. After that you can decide which recovery model and which backup types to use.
The most common scenarios are:
-
For very small and development databases the simple recovery model is enough.
-
For mid-size/large production databases, use the full recovery model where you will have one full backup per week. At the end of the day, you would create a differential backup and during the day, you would process several transactional log backups.
-
For very large databases, it’s recommended to combine full backup with partial backups and file backups.
But in your environment, it could be different…..
Restore databases
In the previous chapter, we looked at the various recovery models available in SQL Server. Remember, the chosen model will determine your available options when the time comes to restore your database.
Unlike the database backup, the database restore is very often an offline process, which means the database is not accepting any transactions!
Depending on the chosen model, you can make one of the following restores:
-
Full database restore: Restoring a full backup brings a database back to the state when the full database backup occurred. A full backup restore is required as the first step of the recovery process for differential and/or transaction log backups. Full database backup restores are offline restores.
-
Differential restore: Allows you to restore all changes that have occurred since the last full backup was applied.
-
Transaction log restore: Allows you to restore the database to a specific point-in-time.
-
Files and filegroups restore: Enables you to restore a damaged file instead of restoring an entire database. During the restore process, only the restoring file/filegroup will be off-line.
-
Pages restore: You can use it to restore specific pages without restoring the whole database. To restore pages, you can use differential and transaction log backups. This restore option is an on-line operation.
-
Piecemeal restore: The piecemeal restore is an option to restore your primary filegroup and indicate that you will restore other filegroups later. The main idea of this restore is to get your database up and running as soon as possible.
This is just a very quick and short overview of Backup and Restore. For more information look at MSDN:
http://msdn.microsoft.com/en-us/library/ms187048.aspx
Kentico CMS and Backup&Restore
As you can see, the Backup & Restore is driven by the SQL Server without any support from the database application. Backup & Restore is not only about “saving” data and then “restoring” them, but it also requires a well-prepared backup strategy to be sure you will not lose any important data.
So, how can Kentico CMS improve this process, if there is no support on the Kentico CMS side required? There is still one small “disadvantage” of the current Backup & Restore implementation in SQL Server. They don’t support backup or restore of only a part of your data. With SQL Server implementation, you have to create full restore on a separate server and then copy this part to the production server.
Yes, you can buy advanced SQL Server Backup & Restore applications that will support it, but it’s not necessary for Kentico
CMS websites.
Kentico CMS contains an export/import “tool” using which we can backup/restore only a part of your data
or specific web site within your installation. Just go to
Site Manager -> Sites and click on Import site or objects or Export objects. The implementation in version 5.5 supports export/import of
any content of the web site, except some purely online data such as event log events etc.
For version 6.0, we are preparing full granular export/import that will be able to be used for export/import of particular Documents from the content tree. We are also preparing a scheduling task for exporting of your data from the website.
Summary
In this part we looked closer at Backup & Restore of databases. We learned that this is an out-of-the-box solution driven by the SQL Server without any support from the database application. On the other hand, it requires a well-prepared backup strategy by the DBA. We also discovered that Kentico CMS supports more granular “backup&restore” via export/import.
A final question for you ….
Do you think that it will be good to help you manage your SQL Server backups directly from the Kentico CMS? (recovering of your database from within Kentico CMS user interface is a bit impossible at the moment as the database is offline during the full/differential/transactional restore)