Configuring Data Streams for Backups Using the SQL Server Agent

You can increase the speed of SQL backups by increasing the number of streams used by the backup operation to a number greater than one, and by enabling software compression at the subclient level. While performing backups using multiple streams, ensure that you have the same number of streams to restore the data.

For example, if during a backup operation, backup data is sent to the media in two parallel streams by default, the parallel streams reduce the backup time by half when compared with the backup time with one data stream.

You can also increase the number of streams for backing up a subclient. For example, if you increase the number of streams from two to three, then the backup time reduces from half to one-third.

If you are performing a restore operation from a secondary copy that has the Combined Stream option enabled, the restored data is temporarily staged at the Job Results folder before restoring it to a SQL Server.

You can change the location of the staging folder by following one of the options mentioned below:

• Change the value of additional setting sStageFolderAuxCopyRestore. For steps on modifying an additional setting from the CommCell Console, see Adding or Modifying Additional Settings from the CommCell Console.

• From the SQL Server Properties dialog box, select the Staging Location for Restores check box, and then type or browse to the staging location path.

Before You Begin

• Verify that the number of streams available to back up a subclient does not exceed the maximum number of streams configured in the subclient's storage policy.

• Verify that you have enough space at the staging location to stage the data being restored.

Procedure

1. Verify the number of streams that are set in the subclient storage policy:

   1. From the CommCell Browser, click Policies > Storage Policies.

   2. Right-click the storage policy associated to the subclient for which you want to increase the streams, and then click Properties.

      The Storage Policy Properties dialog box is displayed.

   3. On the General tab, verify that number of streams in the Device Streams box is greater than the number of streams you want to configure to back up the subclient.

   4. Click OK.

2. Set the number of streams for the subclient:

   1. From the CommCell Browser, click Client Computers > client > SQL Server > instance.

   2. Right-click the subclient for which you want to increase the streams, and then click Properties.

      The Subclient Properties dialog box is displayed.

   3. Click the Storage Device tab.

      • On the Data Storage Policy sub-tab, in the Number of Streams for data backup box, change the number of streams.

      • On the Log Storage Policy sub-tab, in the Number of Streams for transaction log box, change the number of streams.

   4. Click OK.