Performance tuning and configuration options
The Microsoft SQL Server tab in a MS-SQL-Server backup policy contains the tuning parameters that can improve the performance of your backups. These settings, and other factors that affect performance, are discussed in this topic. When you configure a Microsoft SQL Server protection plan, these options are available on the Backup options page. For protection plans, some options cannot be changed when you edit an existing plan or when you subscribe an asset to the plan.
See NetBackup for SQL Server performance factors.
Note:
Filegroup backups are only available for policies and not for protection plans.
Caution:
Do not enable multiplexing if the policy is also configured with multiple stripes. Restores fail when both multiplexing and multiple stripes are configured for a backup policy.
Table: Tuning parameters for SQL Server backups
Field | Description |
|---|---|
For policies, the selection on the Select instances and databases tab determines the options you can select in this list. and are only available for instances, intelligent groups, and instance groups. The following preference settings are available:
| |
This option applies to stream-based backups only. Sets the incremental size that SQL Server uses for reading and writing backup images and can be set for each backup operation. Calculated as 512 bytes * 2^BLOCK_SIZE. The value for this option ranges from 0.5 KB to 64 KB. The default is 64 KB. | |
See Number of backup stripes. | |
(Stream-based backups only) This option affects buffer space availability. NetBackup uses this parameter to decide how many buffers to allocate for reading or writing each data stream during a backup operation. By allocating a greater number of buffers, you can affect how quickly NetBackup can send data to the NetBackup primary server. The default value for this option is 2, which allows double buffering. You may get slightly better performance by increasing this value to a higher value. The range is 1 - 32. | |
(Databases only) If no previous full backup exists for the database or filegroup, then NetBackup converts a differential backup to a full backup. | |
|
| (Transaction logs only) If no previous full backup exists for the database, then NetBackup converts a transaction backup to a full backup. |
(Databases only) This option allows SQL Server to create an out-of-band backup so that it does not interfere with the normal backup sequence. The default value is cleared except for full database Instant Recovery backups. See Using copy-only snapshot backups to affect how differentials are based. | |
(Databases only) This option quiesces a group of databases together and creates a snapshot to back them up as a group. NetBackup automatically discovers and groups any databases that can be grouped, up to this value. A separate snapshot is created for the master database. If both availability databases and standard databases are included in the same policy, separate snapshots are created for each availability group and for the standard databases. If the limit is reached, NetBackup creates additional snapshots. The default value is 1. The range is 1 to 64. | |
(Stream-based backups only) This option is the buffer size used by SQL Server for reading and writing backup images. Generally, you can get better SQL Server performance by using a larger value. This option can be set for each backup operation. Calculated as 64 KB * 2^MAX_TRANSFER_SIZE. It ranges in size from 64 KB to 4 MB. The default is 4 MB. | |
Verifies the checksums before the backup. Note that this verification imposes a performance penalty on a backup or restore operation. To verify the checksums before the backup, choose one of the following options:
| |
This option divides the backup operation into multiple concurrent streams. A stream corresponds to a job in the activity monitor. For example, if the value is , each database is backed up using three jobs. This configuration applies in any situation in which SQL Server dumps data faster than your tape drive is capable of writing. The default value for this option is 1. The range is 1 - 32. | |
This option is the number of backup operations to start simultaneously, per database instance. The range is 1 - 32. The default is 1. You may need to configure other options when you configure two or more parallel backup operations. See Configure the number of jobs allowed for backup operations. | |
(Databases only and policies only) This option excludes any filegroups that are read-only from the backup. The resulting backup is a partial image because the image does not contain all filegroups. The partial image contains data from the read-write filegroups and data from the primary filegroup. This option applies only to the backup selection. | |
NetBackup skips any database with a status that prevents NetBackup from successfully backing up the database. These statuses include offline, restoring, recovering, and emergency mode, etc. NetBackup skips the backup of the unavailable database, but continues with the backup of the other databases that the policy includes. The backup completes with a status 0 and the job details indicate that the database was skipped. See Schedule backup types for SQL Server Intelligent Policies. | |
Enable this option to use SQL Server to compress the backup image. If you enable SQL Server compression, do not enable NetBackup compression. SQL Server compression is not supported for snapshot backups. | |
(Transaction logs only) This option backs up the transaction log and removes the inactive part of the transaction log. This option is enabled by default. | |
(Databases only) Determines the time-out interval for SQL Server Virtual Device Interface. The selected interval is applied to backups and restores of databases and of transaction logs. The default value for backups is 300. The default value is 600 for restores. The range is 300 - 2147483647. |
More Information