IBM Tivoli Storage Manager for Databases, Data Protection for Microsoft SQL Server, Version 7.1

Performance

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.

Note: Legacy backups are a stream of bytes that Data Protection for SQL Server stores on the Tivoli® Storage Manager server. VSS backups differ since they are at the volume and file-level. In a situation where a SQL Server database is not fully allocated, a Legacy backup might transfer a smaller amount of data for a Tivoli Storage Manager backup than for a VSS backup because a VSS backup transfers the entire file, regardless of its allocation.

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.

Buffering (Legacy only)

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.

Data Striping (Legacy only)

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.

Note:
  • Additional striping does not necessarily improve performance and may even decrease performance if system constraints involving real and paged memory, processors, network interface cards, networks, device reads and writes, and RAID become saturated or exceed capacity.
  • If you use striping in conjunction with SQL buffers, be certain that the number of SQL buffers specified is equal to or greater than the number of stripes.
  • The default values that Data Protection for SQL Server assigns to buffers, buffer size, and stripes can be changed in the Data Protection for SQL Server configuration file. Use the set command or the Performance property page in the MMC GUI to modify the configuration file.

LAN-free environment (Legacy and VSS)

Running Data Protection for SQL Server in a LAN-free environment if you are equipped to do so avoids network constraints.
  • For Legacy backups, specify enablelanfree yes in the Data Protection for SQL Server options file.
  • For VSS backups, specify enablelanfree yes in the DSMAGENT (VSS Requestor) dsm.opt file only.
For information on setting up a LAN-free environment, refer to the Tivoli publication: IBM Tivoli Storage Manager for SAN for Windows Storage Agent User's Guide.


Feedback