Data Protection for SQL Server provides certain parameters that can be tuned for optimum performance.
Many factors can affect the backup and restore performance of Data Protection for SQL Server, such as hardware configuration, network type, and capacity. These factors are not within the scope of this document. However, some parameters that are related to Data Protection for SQL Server can be tuned for optimum performance.
For Legacy and VSS Backups, the RESOURCEUTILIZATION client option is important. This option increases or decreases the ability of the client to create multiple sessions. The higher the value, the more session the client can start. The range for the option is from 1 to 10. For more information about RESOURCEUTILIZATION, see the IBM® Tivoli Storage Manager Performance Tuning Guide.
Data Protection for SQL Server is a multi-threaded application that uses asynchronous execution threads to transfer data between the SQL and Tivoli Storage Manager servers. To accomplish this, multiple data buffers are used to allow one thread to receive data from one side, while another thread sends data to the other side. For example, one thread can be reading data from a SQL Server while another is sending data to the Tivoli Storage Manager server.
The number of buffers that Data Protection for SQL Server allocates to these threads is specified by the /buffers and /sqlbuffers parameters. The size of these buffers is specified by the /buffersize and /sqlbuffersize parameters. These parameters are set on the Properties page. When the parameters are set on the Properties page, the dsm.opt file is updated. You can also use the command-line interface to update the dsm.opt file.
In addition to multi-threading to maximize throughput on a single session, Data Protection for SQL Server uses separate threads to support SQL data striping, which allows use of multiple parallel sessions to backup and restore a single database. This is another method to maximize data throughput. If a single session cannot fully exploit available bandwidth, multiple parallel sessions can yield improved data throughput, especially if the database is spread across multiple physical volumes.
If you use one data stripe per physical volume for both the SQL Server and the Tivoli Storage Manager server, the performance, which is measured as the amount of time necessary to backup or restore a particular SQL database, should show an improvement over the unstriped case. The improvement is approximately proportional to the number of data stripes used, given the constraints of the devices and the network used, and the striping independent overhead in SQL Server, Tivoli Storage Manager server, and Data Protection for SQL Server.
You can specify the number of stripes to use with the /STRIPes parameter on the command-line interface. You can also specify the number of stripes to use from the Management Console (MMC) GUI, by changing the number in the Stripes field in the Backup options or Restore options panel.