Specifying Data Protection for SQL Server preferences

You define Data Protection for SQL Server configuration parameters in the Data Protection for SQL Server configuration file (tdpsql.cfg by default). These configuration parameters determine such preferences as the location of your log

file, how date and time stamps display, and the number of buffers to use. You can set the values of the Data Protection for SQL Server configuration parameters in two ways:

򐂰 Using the menu Edit→ Configuration of the Data Protection for SQL Server GUI.

򐂰 Using the tdpsqlc set command in the Data Protection for SQL Server command-line interface.

Note the following characteristics of Data Protection for SQL Server configuration parameters:

򐂰 The value of a configuration parameter specified on a command-line invocation overrides (but does not change) the value of the configuration parameter specified in the Data Protection for SQL Server configuration file.

򐂰 During a command-line invocation that does not specify an overriding value for a configuration file parameter, the values in the default Data Protection for SQL Server configuration file (tdpsql.cfg) are used.

Set the policy for VSS backups by specifying the VSSPOLICY statement in your Data Protection for SQL Server configuration file. You set this parameter only in the configuration file - it cannot be specified using the tdpsqlc set command or the menu Edit→ Configuration task of the Data Protection for SQL Server GUI.

VSSPOLICY statements are processed from the bottom up and processing stops at the first match. To ensure that more specific specifications are processed at all, the more general specification should be listed before the more specific ones, so as to be processed after the more specific specifications. Otherwise, the more general specification matches the target before the more specific specifications are seen.

By default, Microsoft SQL Server accepts connections on TCP/IP port 1433. If SQL Server is configured to listen on a TCP/IP port different than 1433, you must specify the TCP/IP port for the parameters SQLSERVer and FROMSQLserver.

Example 3-16 shows how to configure Tivoli Data Protection for SQL Server to access the instance SQL_STDBY on server COPPER using port 5555.

Example 3-16 TDPSQL.cfg parameter file for Microsoft SQL Server with non-default port SQLSERVer COPPER\SQL_STDBY,5555

FROMSQLserver COPPER\SQL_STDBY,5555

Chapter 3. Installation and configuration 109 If you do not specify the TCP/IP port when accessing a Microsoft SQL Server instance listening on a non-default port, you receive an error message similar to Figure 3-27.

Figure 3-27 Tivoli Data Protection for SQL Server connection error

Configuring Data Protection preferences in a standalone environment

In a non-clustered environment, you can configure the preferences in the default preferences files located on C:\Program Files\Tivoli\TSM\TDPSQL\tdpsql.cfg. If you have more than one backup policy, you can create different preferences files.

See Example 3-17 and Example 3-18 on page 110.

Example 3-17 Data Protection for SQL Server configuration file for daily backups (tdpsql_daily.cfg)

SQLSERVer COPPER FROMSQLserver COPPER

SQLAUTHentication INTegrated nodename copper_sql

MOUNTWaitfordata Yes BACKUPMethod LEGACY DIFFESTimate 20

Note: This error can occur when you install Microsoft SQL Server 2000 and 2005 on the same machine. In our tests, we installed Microsoft SQL Server 2000 first and then SQL Server 2005. Although SQL Server tools were able to connect to both SQL Server installations, Tivoli Data Protection for SQL Server was able to connect only to the SQL server listening on port 1433. To resolve this issue, it was necessary to include in the TDPSQL.cfg

configuration file the port that SQL server was listening on.

You can verify the port that the SQL server is listening on, using Microsoft SQL Server tools, such as the Server Network utility for SQL Server 2000 or SQL Server Configuration Manager for SQL Server 2005. Another way to verify this information is to check the SQL server error log.

BUFFers 3

BACKUPDestination Both LOCALDSMAgentnode copper_vss REMOTEDSMAgentnode

********** Server Storage Group Name BU Type BU Dest. Mgmt Class

********** --- --- --- --- ---VSSPOLICY * * FULL TSM VSS_FULL_TSM VSSPOLICY * * LOCAL LOCAL VSS_LOCAL

Example 3-18 Data Protection for SQL Server configuration file for monthly backups (tdpsql_long.cfg)

SQLSERVer COPPER FROMSQLserver COPPER

SQLAUTHentication INTegrated nodename copper_lng

MOUNTWaitfordata Yes BACKUPMethod LEGACY DIFFESTimate 20

BACKUPDestination TSM

LOCALDSMAgentnode copper_vss REMOTEDSMAgentnode

********** Server Storage Group Name BU Type BU Dest. Mgmt Class

********** --- --- --- --- ---VSSPOLICY * * FULL TSM VSS_LONG_TSM

We recommend creating a shortcut for the Data Protection for SQL Server GUI pointing to each options file. You can follow the steps 1 on page 100 through 3 on page 101 described starting on page 100 to create the shortcuts.

Chapter 3. Installation and configuration 111 To create shortcuts for daily and monthly backups, you must specify the options file and configuration file. See Example 3-19 and Example 3-20.

Example 3-19 Shortcut definition for daily SQL backup

"C:\Program Files\Tivoli\TSM\TDPSql\tdpsql.exe" /tsmoptfile="C:\Program Files\Tivoli\TSM\TDPSql\dsm_daily.opt" /configfile="C:\Program

Files\Tivoli\TSM\TDPSql\tdpsql_daily.cfg"

Example 3-20 Shortcut definition for monthly SQL backup

"C:\Program Files\Tivoli\TSM\TDPSql\tdpsql.exe" /tsmoptfile="C:\Program Files\Tivoli\TSM\TDPSql\dsm_long.opt" /configfile="C:\Program

Files\Tivoli\TSM\TDPSql\tdpsql_long.cfg"

Configuring Data Protection for SQL Server preferences in a clustered environment

In a clustered environment, we created the DSMAgent options file for each node in a cluster was created. At this point, it is necessary to configure Data Protection for SQL Server preferences for each node in the cluster. You can use a directory in an external disk and configure both files, one for each node in the cluster. You need to match the corresponding local DSMAgent node according to the specific machine in the cluster.

If you are using an offloaded backup server, it is necessary to configure the remote DSMAgent node pointing to your offloaded node. This parameter is required only if you are using an offloaded backup server.

This configuration also contains the VSSPOLICY statements, so if you have more than one backup policy, it is necessary to specify more than one

configuration. See Example 3-21, Example 3-22 on page 112, Example 3-23 on page 112, and Example 3-24 on page 113.

Example 3-21 Data Protection for SQL Server configuration file for daily backups (tdpsql_libra_daily.cfg)

SQLSERVer CLUSQL01\SQL01 FROMSQLserver CLUSQL01\SQL01 SQLAUTHentication INTegrated nodename CLUSQL01_Daily

MOUNTWaitfordata Yes BACKUPMethod LEGACY DIFFESTimate 20 BUFFers 3 BUFFERSIze 1024

STRIPes 1 SQLBUFFers 0 SQLBUFFERSIze 1024 LOGPrune 60

LANGuage ENU

BACKUPDestination Both LOCALDSMAgentnode libra_vss REMOTEDSMAgentnode leo_offload

********** Server Storage Group Name BU Type BU Dest. Mgmt Class

********** --- --- --- ---

********** --- --- --- --- ---VSSPOLICY * * FULL TSM VSS_FULL_TSM VSSPOLICY * * LOCAL LOCAL VSS_LOCAL

Example 3-22 Data Protection for SQL Server configuration file for monthly backups (tdpsql_libra_long.cfg)

SQLSERVer CLUSQL01\SQL01 FROMSQLserver CLUSQL01\SQL01 SQLAUTHentication INTegrated nodename clusql01_lng

MOUNTWaitfordata Yes BACKUPMethod LEGACY DIFFESTimate 20

BACKUPDestination TSM LOCALDSMAgentnode libra_vss REMOTEDSMAgentnode leo_offload

********** Server Storage Group Name BU Type BU Dest. Mgmt Class

********** --- --- --- --- ---VSSPOLICY * * FULL TSM VSS_LONG_TSM

Example 3-23 Data Protection for SQL Server configuration file for daily backups (tdpsql_leo_daily.cfg)

SQLSERVer CLUSQL01\SQL01 FROMSQLserver CLUSQL01\SQL01 SQLAUTHentication INTegrated nodename CLUSQL01_Daily

Chapter 3. Installation and configuration 113 MOUNTWaitfordata Yes

BACKUPMethod LEGACY DIFFESTimate 20

BACKUPDestination Both LOCALDSMAgentnode leo_vss REMOTEDSMAgentnode libra_offload

********** Server Storage Group Name BU Type BU Dest. Mgmt Class

********** --- --- --- ---

********** --- --- --- --- ---VSSPOLICY * * FULL TSM VSS_FULL_TSM VSSPOLICY * * LOCAL LOCAL VSS_LOCAL

Example 3-24 Data Protection for SQL Server configuration file for monthly backups (tdpsql_leo_long.cfg)

SQLSERVer CLUSQL01\SQL01 FROMSQLserver CLUSQL01\SQL01 SQLAUTHentication INTegrated nodename clusql01_lng

MOUNTWaitfordata Yes BACKUPMethod LEGACY DIFFESTimate 20

BACKUPDestination TSM LOCALDSMAgentnode leo_vss REMOTEDSMAgentnode libra_offload

********** Server Storage Group Name BU Type BU Dest. Mgmt Class

********** --- --- --- --- ---VSSPOLICY * * FULL TSM VSS_LONG_TSM

We recommend creating a shortcut for the Data Protection for SQL Server GUI pointing to each options file. You can follow steps 1 on page 100 through 3 on page 101, which are described starting on page 100, to create the shortcuts.

To create shortcuts for daily and monthly backups, you must specify the options and configuration file. See Example 3-25, Example 3-26, Example 3-27, and Example 3-28.

Example 3-25 Shortcut definition for daily SQL backup for server Libra

"C:\Program Files\Tivoli\TSM\TDPSql\tdpsql.exe"

/tsmoptfile="E:\tsmdata\dsm_mssqldb_daily.opt"

/configfile="E:\tsmdata\tdpsql_libra\tdpsql_libra_daily.cfg"

Example 3-26 Shortcut definition for daily SQL backup for server Leo

"C:\Program Files\Tivoli\TSM\TDPSql\tdpsql.exe"

/tsmoptfile="E:\tsmdata\dsm_mssqldb_daily.opt"

/configfile="E:\tsmdata\tdpsql_libra\tdpsql_leo_daily.cfg"

Example 3-27 Shortcut definition for monthly SQL backup for server Libra

"C:\Program Files\Tivoli\TSM\TDPSql\tdpsql.exe"

/tsmoptfile="E:\tsmdata\dsm_mssqldb_long.opt"

/configfile="E:\tsmdata\tdpsql_libra\tdpsql_libra_long.cfg"

Example 3-28 Shortcut definition for monthly SQL backup for server Leo

"C:\Program Files\Tivoli\TSM\TDPSql\tdpsql.exe"

/tsmoptfile="E:\tsmdata\dsm_mssqldb_daily.opt"

/configfile="E:\tsmdata\tdpsql_libra\tdpsql_leo_long.cfg"

Chapter 3. Installation and configuration 115