Optimizing performance has always been a hot topic. This post contains information on how to optimize your MSSQL backup performance with the Data Protector. As always, with this kind of advice, this MAY be helpful in most environments but is not a general solution for any environment. Please test any of the changes and consult your SQL Server administrator if you have any doubts.
- General recommendations
- Tuning using omnirc
- Database integrity check
- SQL backup compression
- Parallel database backup
- Multi-stream database backup
Parallel database and Multi-stream database backup for Microsoft SQL is available since Data Protector A.09.08.
1. General recommendations
Tuning using omnirc
The following omnirc options are usually a good choice to optimize general backup performance using the MSSQL online integration agent. Using the settings will increase the transfer size and block size to their maximum values. This assumes that the underlying file systems were formatted correctly (64k).
# Increase block size and transfer size for MSSQL backups OB2SQLBLOCKSIZE=65536 OB2SQLMAXTRANSFERSIZE=4194304
Database integrity check
The option Check database integrity is useful to detect silent database corruption at backup time. Checking the database integrity takes time and eats disk and CPU performance on the database server. If you have different backup jobs for full backups and transaction log backups it is recommended to Disable the Check database integrity on transaction log backups, especially if they have to run very frequently.
SQL backup compression
Data Protector allows to configure the usage of SQL backup compression. By default the MSSQL server settings, configured in the database, are used. Typical backup devices such as tape and deduplication devices (StoreOnce, Data Domain) offer a high performance data reduction at backup time but having issues with pre-compressed data. If you have doubts, or not full control over the MSSQL server, choose to Disable for SQL backup compression.
2. Parallel database backup: Large number of databases per instance
This is a typical example for MSSQL servers that contain hundreds of databases per instance. Even if they are relatively small the processing rate will be slow. The solution is to configure the integration agent to backup multiple databases at the same time. The following example assumes you have successfully installed the agent on the client system. Then add the following omnirc options to the client system to enable parallel processing.
# Allow to backup multiple SQL databases in parallel, one stream per database
OB2_PREFER_DATABASE_PARALLELISM=1
OB2_DEFAULT_CONCURRENT_STREAMS=1
The entire MSSQL Server needs to be selected for backup.
Choose the right amount of target devices. B2D libraries can open multiple data streams per gateway. In our example here up to 4 data streams will be accepted.
In Backup Object Summary select Properties of the MSSQL server and change Concurrent streams in the MS SQL Integration tab to match the number of data streams selected for destination devices. This is 4 in our example.
When the backup is executed we see 4 active data connections and the backup proceed very quickly compared to a single stream backup.
3. Multi-stream database backup: For of a few large databases
If the MSSQL server that contains only a few, but very large databases the following might be useful. The solution is to configure the integration agent to backup databases with multiple data streams. The following example assumes you have successfully installed the agent on the client system. Then add the following omnirc options to the client system to enable multi-streaming. Please note that the number of streams per database is configured in this file. In our example we use 2 concurrent streams.
# Allow to backup multiple SQL databases in parallel, 2 streams per database
OB2_PREFER_DATABASE_PARALLELISM=1
OB2_DEFAULT_CONCURRENT_STREAMS=2
The entire MSSQL Server needs to be selected for backup. Each database will be backed up in 2 pieces.
Choose the right amount of target devices. B2D libraries can open multiple data streams per gateway. If we choose 4 here we will be able to accept up to 4 data streams. This is true for multi-streaming as well as for parallel backup.
In Backup Object Summary select Properties of the MSSQL server and change Concurrent streams in the MS SQL Integration tab to match the number of data streams selected for destination devices. This is 4 in our example.
When the backup is executed we see 4 active data connections and the backup proceed very quickly compared to a single stream backup. 2 databases are backed up at the same time and each database is backed up in 2 pieces.
