Search <book_title>...

NetBackup™ for Microsoft SQL Server Administrator's Guide

Last Published: 2024-09-30

Product(s): NetBackup & Alta Data Protection (10.5)

NetBackup for SQL Server performance factors

Many factors can influence the backup performance, including your hardware environment and the settings in SQL Server and NetBackup.

Note:

Some of the factors are only applicable to SQL Server stream-based operations and have no effect on snapshot backups or restores.

For a SQL Server Intelligent policy, set these parameters in the policy, on the Microsoft SQL Server tab. For a backup batch file 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.

SQL Server buffer space parameters

The Maximum transfer size, Backup block size, and Client buffers per stripe 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 Maximum transfer size parameter can be set for each backup or restore operation. Maximum transfer size 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 Backup block size parameter can be set for each backup operation. For restore operations, NetBackup automatically chooses the same size that that was used for the backup. Backup block size is the incremental size that SQL Server uses for reading and writing backup images.

The Client buffers per stripe 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 1 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 32, normally a value of 2 or 3 is sufficient.

Stripes and parallel backup operations

You can improve performance and throughput by increasing the backup stripes or parallel backup operations, depending on the size and number of databases.

Multiple stripes (Number of backup stripes) are useful for larger databases when the performance gains outweigh the additional overhead for the SQL Server agent to configure them. For 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 Parallel backup operations 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 Configure 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.

Shared memory usage

For optimal performance, 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 unless you create a install_path\NetBackup\NOSHM file.

Alternate buffer method

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 Maximum transfer size for the backup to the maximum allowed, which is 4 MB.

About alternate buffer method with backup operations

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.

About alternate buffer method with restore operations

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.

SQL Server checksum

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 batch file-based policies, set the Page verification value when you create the script. For restore scripts, choose Verify backup image, but don't restore option when you create the script.

Instant data file initialization

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.

Using read-write and read-only filegroups

You can significantly reduce backup time and the storage media that is needed if you periodically back up only the 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.