About NetBackup for SQL performance factors
Many factors can influence the backup performance, including your hardware environment and the settings in SQL Server and NetBackup. To optimize your system for SQL Server backups the first step is to tune your environment for standard backup operations. Details are provided in the NetBackup Backup Planning and Performance Tuning Guide. When this tuning is complete, you can adjust several things specific to SQL Server.
Note:
Some of the factors are only applicable to SQL Server stream-based operations and have no affect on snapshot backups or restores.
For a SQL Server Intelligent policy, set these parameters in the policy, on the tab. For a backup batch file (legacy SQL Server policy) or for a restore batch file, configure these parameters in the NetBackup MS SQL Client interface. The parameters in the NetBackup client properties are saved for the session.
The following factors can affect performance:
The , , and can increase buffer space in SQL Server. SQL Server must have the available resources to support the increase of these values. Buffer space parameters are applicable for stream-based backups only.
The parameter can be set for each backup or restore operation. 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.
The parameter can be set for each backup operation. For restore operations, NetBackup automatically chooses the same size that that was used for the backup. is the incremental size that SQL Server uses for reading and writing backup images.
The determines how many buffers to allocate for reading or writing each data stream during a backup or restore operation. Setting this factor to a value greater than enables multi-buffer during data transfer. By allocating a greater number of buffers, you can affect how quickly NetBackup can send data to the NetBackup media server. Multi-buffer prevents short-term producer-consumer imbalances during a backup or restore operation. Although you can set the number of buffers as high as , normally a value of or is sufficient.
You can improve performance and throughput by increasing the backup stripes or parallel backup operations, depending on the size and number of databases.
Enabling multiple stripes () is useful for larger databases when the performance gains outweigh the additional overhead necessary for the SQL Server agent to configure them. When protecting smaller databases, striping can decrease performance speed. In general, if the SQL Server instance only has a few large databases, the use of stripes improves performance. If the instance has numerous smaller databases, increasing the amount of is a better choice to improve performance. You can increase both stripes and parallel backup operations at the same time, but be careful not to overwhelm the system resources.
See Configuring the number of jobs allowed for backup operations.
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.
Optimal performance is seen if you install NetBackup server on the same host as NetBackup for SQL Server. Also use shared memory for data transfer instead of sockets. Shared memory is the default for this configuration and is used unless you create a install_path\NetBackup\NOSHM file.
NetBackup for SQL Server supports an alternate buffer method. It optimizes CPU usage by allowing NetBackup and SQL Server to share the same memory buffers without transferring data between them.
The alternate buffer method for backup and restore typically does not improve data transfer rate, only CPU utilization. A situation may occur in which the transfer rate is significantly degraded when alternate buffer method is in use. To improve the transfer rate set the for the backup to the maximum allowed, which is 4 MB.
This method is chosen automatically for backups if all of the following conditions apply:
NetBackup shared memory is in use.
The backup is stream-based.
The backup is not multiplexed.
The backup policy does not specify either NetBackup compression or NetBackup encryption.
The NetBackup buffer size equals the SQL Server block size.
The default NetBackup buffer size is 64 KB, but this value can be overridden in the following settings:
install_path\NetBackup\db\config\SIZE_DATA_BUFFERS (for tape backups), or,
install_path\NetBackup\db\config\SIZE_DATA_BUFFERS_DISK (for disk backups)
NetBackup for SQL Server agent is started with the same account as the NetBackup Client Service.
The backups that are initiated from an automatic backup policy are started with the NetBackup Client Service so the same account is already in use. However, you can start a SQL Server backup through NetBackup for SQL Server or through dbbackex. In this case, your logon account must be the same as the NetBackup Client Service account. Then your backups can be candidates for the alternate buffer method.
Conditions for backups require that you use the alternate buffer method. Restores also require that backups have been made with the alternate buffer method. You can verify that the alternate buffer method was used. Look for the words Using alternate buffer method, which appear in the dbclient log and the progress report.
You can choose to perform a checksum before you perform a backup. When this option is enabled, it imposes a performance penalty on a backup or restore operation.
For legacy backup policies, set the value when you create the script. For restore scripts, choose option when you create the script.
When you restore a database, filegroup, or database file, SQL Server zeroes the file space before it begins the restore operation. This action can slow the total recovery time by as much as a factor of 2. To eliminate file initialization, run the MSSQLSERVER service under a Windows account that has been assigned the SE_MANAGE_VOLUME_NAME. For more information, see the SQL Server and the Windows documentation.
You can significantly reduce backup time and the storage media that is needed if you periodically back up only read-write filegroups. Then keep a single backup of read-only filegroups, which is retained infinitely. You can set the retention level in the schedule.