Performance tuning for NetBackup for Microsoft SQL Server backups

When looking to enhance performance of SQL backups, it is important to recognize a few factors. First, overall performance should be at an optimum level. If there are problems with the performance of file system backups on the SQL server, or if the network in general is slow, SQL backups will be slow, and no amount of performance tuning will resolve these issues. Next, it is important to understand that in general terms, the faster a backup runs, the slower the restore will run. While the use of multiple stripes will generally speed up the performance on a backup, because the backup is now "fragmented" on the tapes, the restores will take longer to run. It is important to strike a balance between backup and restore speeds. Finally, there is no "silver bullet" which will make backups run at an optimal level. The only way to determine how best to optimize performance is to test a variety of different configurations, and see what works best in a given environment. This article contains explanations of configuration parameters, which provides a road map for testing to determine the best settings for the parameters.  

Baseline Testing:

Start by running FTP tests to and from the SQL server and the media server, to establish a baseline for theoretical maximum possible throughput of the network. From the SQL client server, take a large file (50+ MB) and FTP the file to the media server from the SQL server several times and record the speeds. Then, still on the SQL client server, pull the large file back down to the SQL server several times, and record the speeds. Perform these same two tests from the media server: push the file to the SQL server, and then pull the file from the SQL server to the media server. This testing eliminates NetBackup from the picture, and provides a picture of the theoretical speeds for backups and restores. Please note if there are anomalies in any of the FTP speeds, this is an indication of problems with either the network hardware, or the network in general.  

Once FTP speeds are determined to be at acceptable levels, then test file system backups, and determine the throughput. If the SQL server is not in a MS-Windows-NT backup policy, create one and test the backup of the server's file system files. If necessary, adjust settings within NetBackup, and tune the performance of file system backups until the speeds are acceptable. Refer to the following Veritas articles (links in the Related Documents section below) for more information on performance tuning for file system backups.

 

- NET_BUFFER_SZ, SIZE_DATA_BUFFERS and NUMBER_DATA_BUFFERS - how they work and how to configure them.
 

- How to configure buffers for Veritas NetBackup in a Windows NT/2000 environment.
 

Once the network and the file system backups are running at the desired level, then focus on SQL backup performance.  

Alternate Buffer Method:

If possible, consider testing and using the alternate buffer method. The Alternate Buffer Method allows NetBackup to use the same memory buffers as SQL, so data is not being transferred from the SQL buffers to the NetBackup buffers. This alone allows for a performance enhancement.  

The requirements for the Alternate Buffer Method are as follows:
1. Must use shared memory. NetBackup uses shared memory by default, as long as the NOSHM touch file is not in place. This file would be on the media server, in the <install_path>\veritas\netbackup\ directory or /usr/openv/netbackup directory). If the file is present, exercise caution in removing it, as it may have been placed there to resolve other issues.  
2. Backup cannot be multiplexed. To confirm the backup is not multiplexed, open the SQL policy, and open the Default-Application-Backup schedule. On the bottom right side of the window, look for the Media Multiplexing option, and confirm it is set to 1.
3. The NetBackup buffer size (SIZE_DATA_BUFFERS) equals the SQL server block size (BLOCKSIZE).

 

- The NetBackup buffer size, by default, is 64 kilobytes. It can be overridden by the <install_path>\db\config\SIZE_DATA_BUFFERS touch file.
 

- The default size for SQL server block size is also 64 kilobytes. To override this value, and start backups with the desired SQL Server block size, set this parameter either in the Client Options dialog of the NetBackup Database Extension GUI or adjust it directly using the BLOCKSIZE option in the batch file. See the NetBackup for Microsoft SQL server documentation for the appropriate values of the BLOCKSIZE keyword.  
 

4. The NetBackup for SQL job must start as the same account as NetBackup Client Service; this is automatic for scheduled backups.
5. The backup must be a tape based backup.

Additionally, the alternate buffer method is automatically used on a restore if the SQL server is running SQL2K and the alternate buffer method was used for backup.

Other performance enhancements:

STRIPES and BATCHSIZE:

STRIPES refers to the number of "streams" of data being pulled off a single database during a backup operation.  

Dividing the database backup into multiple stipes is useful in a situation when the SQL Server can dump data faster than the tape drive is capable of writing. While it is possible to multiplex many stripes to the same drive, in general, the only time a performance gain will be seen is when the number of tape drives equals the number of streams. Use the " How Many MS SQL Server Stripes?" edit box in the NetBackup SQL Server Database Extension GUI to adjust this parameter.  

Enabling multiple stripes on a backup is useful on backups of larger databases where the overheads associated with setting up multiple stripes is not going to be a significant factor. When dealing with many smaller databases, striping can actually decrease performance speed.

To enable the backup to run each stripe to a different drive, confirm the policy is not set up to multiplex. Do this by opening the SQL policy, and opening the " Default-Application-Backup" schedule. On the " Attributes" tab, confirm the Media Multiplexing is set to 1 (one).  

BATCHSIZE is the number of databases NetBackup will attempt to back up simultaneously. If, for example, there are 50 databases on the SQL server, and the BATCHSIZE variable is set to 5, when the backup initiates, the NetBackup SQL extension will grab the first five databases, and start running the backup of those five databases. Depending on the configuration of the media server and the storage units, the backup will use five drives. See the article "How to back up multiple Microsoft SQL Databases in parallel using more than 1 tape drive" in the Related Documents section below for more information on how to use multiple drives for this type of operation.

In general, if SQL server has only a few large databases, use of stripes will improve performance. If the server has numerous smaller databases, Batchsize will improve performance. Please note it is possible to use both stripes and batchsize at the same time, however, be careful not to overwhelm the system resources.  

Maxtransfersize, Blocksize, and Number of Buffers per Stripe

Caution: The parameters MAXTRANSFERSIZE, BLOCKSIZE, and NUMBER OF BUFFERS PER STRIPE only affect Virtual Device Interface (VDI). Increasing the MAXTRANSFERSIZE and NUMBER OF BUFFERS PER STRIPE impacts the amount of memory that SQL uses. Setting these too high can be detrimental to system and SQL server performance.  Please adjust these settings with care.

MAXTRANSFERSIZE
MAXTRANSFERSIZE is the buffer size used by SQL Server for reading and writing backup images. This parameter is set either in the NetBackup SQL GUI (Under Options | Set NetBackup Client Options) or in the batch file (see "Overview of Batch Files" on page 126 of the Veritas NetBackup (tm) for Microsoft SQL Server System Administrator's Guide for Windows, link in the Related Documents section below). It can be set from 64 KB to 4 MB in size, and the default is 64 KB.  

Think of MAXTRANSFERSIZE as a "holding area" where data is temporarily placed before sending it to the media server. The size of MAXTRANSFERSIZE will be the size of a buffer area where data is held. When the buffer is full, data is then transferred to the media server.  

BLOCKSIZE
BLOCKSIZE is the incremental size that SQL Server uses for reading and writing data to the MAXTRANSFERSIZE buffer. This parameter is set either in the NetBackup SQL GUI (Under Options | Set NetBackup Client Options) or in the batch file (see "Overview of Batch Files" on page 126 of the  VERITAS NetBackup (tm) for Microsoft SQL Server System Administrator's Guide for Windows). It ranges in size from 512 bytes to 64KB, and the default is 64KB.

BLOCKSIZE is the size of the "chunks" of data the SQL agent transfers from the database to the buffer created by the MAXTRANSFERSIZE variable. So, if MAXTRANSFERSIZE is large, and BLOCKSIZE is small, it will take longer for the MAXTRANSFERSIZE buffer to get filled.  

NUMBER OF BUFFERS PER STRIPE
The NUMBER OF BUFFERS PER STRIPE parameter affects buffer space availability. This parameter is set either in the NetBackup SQL GUI (Under Options | Set NetBackup Client Options) or in the batch file (see "Overview of Batch Files" on page 126 of the  VERITAS NetBackup (tm) for Microsoft SQL Server System Administrator's Guide for Windows). NetBackup for SQL Server uses this parameter to decide how many buffers to allocate for reading or writing each data stream during a backup or restore operation. By allocating a greater number of buffers, you can affect how quickly NetBackup for SQL Server can send data to the NetBackup master server.

The default value for the NUMBER OF BUFFERS PER STRIPE is 1.
NUMBER OF BUFFERS PER STRIPE creates that many buffers the size of MAXTRANSFERSIZE, which are then in turn, filled using BLOCKSIZE, for transfer to the media server.