Specifying Data Protection for SQL options

Once Data Protection for SQL is registered to a Tivoli Storage Manager server, several Data Protection for SQL parameters need to be configured. The Tivoli Storage Manager

administrator should have provided you with the node name, password, and the

communications method with the appropriate parameters to connect to the Tivoli Storage Manager server. These values, together with other parameters, are stored in an options file in the Data Protection for SQL directory. The default options file name is dsm.opt it can be edited with a standard text editor. The options file includes the following parameters, which are required for initial configuration:

Nodename

The Tivoli Storage Manager nodename is the unique name by which Tivoli Storage Manager knows the machine running Data Protection for SQL.

Note: It is recommended that the Local DSMAgent client node be a separate node from your normal backup-archive client, as this CAD service will need to be made a non cluster option. So, make sure to specify the following options “clusternode no” and “clusterdiskonly no” in each of the dsm.opt files that are used for the LOCALDSMAGENT.

Servername

This is the name of the Tivoli Storage Manager server to which you backup SQL databases.

Commmethod

The communication protocols link the Data Protection for SQL node with the Tivoli Storage Manager server. Data Protection for SQL supports the same set of communication protocols supported by other Tivoli Storage Manager clients.

Passwordaccess

When set to the value GENERATE, this instructs the Tivoli Storage Manager API to store the current password (encrypted) in the Windows registry and automatically generate a new one when the current one expires. This method of password management is recommended when running scheduled, unattended backups since it ensures that the backup never fails because of an expired password. The default value is PROMPT. A utility program named dsmcutil.exe allows you to manage (update or display) the password as stored in the registry. This utility program is distributed with the Tivoli Storage Manager backup-archive client package. For more information on using the dsmcutil program, see the dsmcutil.hlp file or the dsmcutil.txt file which are distributed with the Tivoli Storage Manager Backup-Archive Client package.

Compression

A setting of COMPRESSION ON instructs the Tivoli Storage Manager API to compress data before sending it to the Tivoli Storage Manager server; this reduces traffic and storage requirements.

Clusternode

A setting of CLUSTERNODE YES directs the Tivoli Storage Manager API and Data Protection for SQL to be cluster-aware when running in a MSCS or VCS environment. This option must be specified for Data Protection for SQL to function properly in a MSCS or VCS environment.

Enablelanfree

A setting of ENABLELANFREE YES allows Data Protection for SQL to run in a LAN free environment if you are equipped to do so.

Configure Data Protection options file in a standalone environment

In a non-clustered environment, you can use the default standard name and location (C:\Program Files\Tivoli\TSM\TDPSql\dsm.opt). In our case we have two different backup policies configured, one for daily backups and another one for monthly backups. For this scenario, it is necessary to have a different options file for each type of retention policy with an associated node name.

Data Protection for SQL allows you to utilize Tivoli Storage Manager automatic expiration and version control by policy. Setting automatic policy for backup data is accomplished through the Data Protection for SQL options file. Use include and exclude statements in the options

Note: If you are running Data Protection for SQL in a MSCS envronment, the options file on both nodes of the cluster must be identical, including the nodename.

Note: You can create additional Data Protection for SQL options files to point to other IBM Tivoli Storage Manager servers.

See Example 3-12 on page 85 for Daily Backups and Example 3-13 on page 85 for monthly backups.

Example 3-12 Options file for daily SQL Backups NODename copper_sql

INCLUDE "\...\meta\...\*" SQL_DAILY INCLUDE "\...\data\...\*" SQL_DAILY INCLUDE "\...\meta\...\log*" SQL_LOGS INCLUDE "\...\data\...\log*" SQL_LOGS EXCLUDE "\...\master\...\log*"

EXCLUDE "\...\msdb\...\log*"

Example 3-13 Options file for Monthly SQL Backups NODename copper_lng

INCLUDE "\...\meta\...\*" SQL_LONG INCLUDE "\...\data\...\*" SQL_LONG EXCLUDE "\...\master\...\log*"

EXCLUDE "\...\msdb\...\log*"

Configure Data Protection options file in a clustered environment

In a clustered environment, the Data Protection options file must be accessible from both nodes. You can use a directory in an external disk, to store this file. This directory must be in the cluster configuration to fail over with the SQL Server instance. You can use automatic expiration in a clustered enviroment, the same as you can in a standalone configuration. So, if you have different backup policies for legacy backups, you need to create a different options file. It is necessary to specify that we are in a cluster environment, so the parameter

“CLUSTERNODE yes” needs to be specified.

See Example 3-14 on page 86 for a daily backup example and Example 3-15 on page 86 for a monthly backup example.

Example 3-14 Options file for daily SQL Backups NODename clussql01_daily

INCLUDE "\...\meta\...\*" SQL_DAILY INCLUDE "\...\data\...\*" SQL_DAILY INCLUDE "\...\meta\...\log*" SQL_LOGS INCLUDE "\...\data\...\log*" SQL_LOGS EXCLUDE "\...\master\...\log*"

EXCLUDE "\...\msdb\...\log*"

Example 3-15 Options file for Monthly SQL Backups NODename clussql01_lng

INCLUDE "\...\meta\...\*" SQL_LONG INCLUDE "\...\data\...\*" SQL_LONG EXCLUDE "\...\master\...\log*"

EXCLUDE "\...\msdb\...\log*"