july 27, 2024
Enhancing DWH resilience through backup optimisation in SQL Server
Backup is the process of creating a duplicate copy of data on a separate medium (local or remote) to prevent the loss of original data and ensure the possibility of quickly restoring it in case of unforeseen situations, such as damage or loss of the original data.
Backup methods
1. Full backup
A full backup creates a copy of the entire database, including all objects and data in the system tables. Restoring the database from a full backup is quick because it does not depend on other backups. However, creating such a complete copy takes a lot of time and disk space. For large databases, it may be necessary to create differential backups in addition to full backups.
2. Differential backup
A differential backup involves creating a copy of only the data that has changed since the last full backup. This type of backup is used in conjunction with a full backup, as a full backup is needed to restore from a differential backup. Consequently, the restoration process is slower than using just a full backup. However, differential backups are more optimised in terms of memory usage and time.
When choosing a backup method, it is important to consider the volume of data changes. If the daily data turnover is quite high, differential backups may become impractical as they will occupy a significant amount of memory.
Creating a backup
1. Through SQL Server Management Studio
To create a backup, select the desired database in the object explorer, right-click on it, and choose Tasks -> Back Up. The following window with backup settings will appear:
Figure 1. Database backup bettings (General)
1. Database name
2. Backup type
3. Backup destination path
Figure 2. Database backup settings (Backup options tab)
1. Backup name
2. Compression (Yes, No, Default)
2. Through script
The same backup settings can be specified through a script:
Differential backup:
Parameters are listed with commas.
Parameters for backup optimisation
The following backup settings parameters affect backup optimisation:
1. BLOCKSIZE = {blocksize | @ blocksize_variable} This parameter defines the size of the physical data block used when creating the backup (in bytes). The supported block sizes are: 512, 1024, 2048, 4096, 8192, 16384, 32768, and 65536 bytes (64 KB). By default, the block size is 512 bytes, but it is recommended to choose at least 4 KB, as a small block size results in numerous data blocks, which slows down the backup and restoration process. A too-large block size can also lead to decreased performance because if an error occurs during backup creation, the entire block has to be re-backed up.
2. BUFFERCOUNT = {buffercount | @ buffercount_variable} This parameter sets the total number of input-output buffers to be used during the backup operation. Any positive integer value can be specified. However, setting too many buffers can lead to an out-of-memory error. This is because excessive use of the virtual address space by the Sqlservr.exe process can occupy more available memory.
The BUFFERCOUNT parameter does not have specific limits on allowable values. However, SQL Server developers suggest using the following formula to calculate the recommended value:
(NumberofBackupDevices * GetSuggestedIoDepth) + NumberofBackupDevices + (2 * DatabaseDeviceCount)
Where:
- GetSuggestedIoDepth — the disk performance indicator. For standard PC hard drives, it is usually 3 by default, while for virtual disks (VDI) it ranges from 1 to 4.
- NumberofBackupDevices — the number of devices used for the backup. SQL Server supports up to 64 devices.
- DatabaseDeviceCount — the number of database files and transaction log files. To find this count, you can use the following script:
3. MAXTRANSFERSIZE = {maxtransfersize | @ maxtransfersize_variable} This parameter specifies the maximum size of a data packet in bytes for data exchange between SQL Server and the backup medium. Supported values are multiples of 65,536 bytes (64 KB) up to 4,194,304 bytes (4 MB).
The total amount of space used by buffers is determined by the following formula:
BUFFERCOUNT * MAXTRANSFERSIZE
Demonstrating the impact of buffering parameters on backup performance
To demonstrate the impact of buffering parameters on backup performance, testing was conducted using an 18 GB database. Various parameter values were tested to identify the most optimal ones:
- Block size (BLOCKSIZE) ranging from 4096 to 65,536 bytes.
- Number of buffers (BUFFERCOUNT) ranging from 7 (calculated for the minimum suitable value for the test database) to 224 (calculated for the maximum suitable value for the test database) (see the formula above).
- Maximum transfer size (MAXTRANSFERSIZE) ranging from 524,288 (512 KB) to 4,194,304 (4 MB).
For each combination of parameters, the time required to perform the backup was measured. Based on the data obtained, graphs were constructed to reflect the dependency of execution time on the specified parameters.
Figure 3 shows the dependency of backup creation on the BLOCKSIZE parameter. The graph indicates that the most optimal values for the BLOCKSIZE parameter were 8192, 16384, and 65536 bytes.
Figure 3. Dependency of backup creation time on the BLOCKSIZE parameter
Figure 4 shows the dependency of backup creation time on the BUFFERCOUNT parameter. The graph indicates that the most optimal BUFFERCOUNT values were 28 and 56.
Figure 4. Dependency of backup creation time on the BUFFERCOUNT parameter
Figure 5 shows the dependency of backup creation time on the MAXTRANSFERSIZE parameter. The graph indicates that the most optimal value for the MAXTRANSFERSIZE parameter was 512 KB.
Figure 5. Dependency of backup creation time on the MAXTRANSFERSIZE parameter
Before changing the parameters, the backup creation time with default settings was 59 seconds.
We found a combination of backup parameters (Table 1) that reduced the time by 23 seconds (40%).
Table 1. Selected combination of parameters
| Backup creation time | BLOCKSIZE | BUFFERCOUNT | MAXTRANSFERSIZE |
|---|---|---|---|
|
36 |
4096 |
7 |
524288 |
Table 2 shows a real case of backup optimisation by changing parameters for a database of 78 GB.
Table 2. Time measurements of the backup operation
| Type of backup buffering parameters | Execution time in seconds (1 Run) | Execution time in seconds (2 Run) | Execution time in seconds (3 Run) |
|---|---|---|---|
|
Configured parameters:
|
51 |
53 |
50 |
|
Default parameters |
130 |
127 |
132 |
Conclusion
With the correct configuration of buffering parameters, the backup time can be reduced by up to 2.5 times.
