Configure Data Protection for SQL Server before you start protecting your applications.
Before you begin
Data Protection for SQL Server must be installed on your system. A Tivoli Storage Manager server must be available to communicate with Data Protection for SQL Server.
Configuring Data Protection for SQL Server
Configuration requirements for Data Protection for SQL Server, Tivoli Storage Manager, and other applications vary. The requirements depend on which Data Protection for SQL Server features you want to use. For example, if you plan on using VSS operations, the Tivoli Storage Manager backup-archive client (serving as the VSS requestor), must also be installed and configured.
About this task
To configure Data Protection for SQL Server, complete the following steps:
Procedure
1. Start the Management Console by clicking Start > All Programs > Tivoli Storage Manager> Data Protection for Microsoft SQL Server > DP for SQL Management Console.
2. From the start page, click Configuration. Alternatively, from the tree view, navigate to the Configuration node. Then, double-click Wizards.
3. In the results pane, double-click TSM Configuration to open the Tivoli Storage Manager Configuration Wizard.
4. Follow the instructions on the pages of the wizard and click Next to move to the next page.
a. In the Data Protection Selection page, select SQL Server. Click Next.
b. Review the results of the requirements check and ensure that you address any errors or warnings.
Click Show Details to view a list of individual requirement results. If you are configuring an application for which you do not have the necessary license, the license requirement check fails. You must either go back to the Data Protection Selection page and clear the selected application to proceed with the configuration, or obtain the necessary license.
c. In the TSM Node Names page, specify the Tivoli Storage Manager node names that exist on the same system to use for the applications that you want to protect.
v In the VSS Requestor field, enter the node name that communicates with the VSS Service to access the SQL data. This node name is the Tivoli Storage Manager backup-archive client node name, also known as the DSM agent node name.
v In the Data Protection for SQL field, enter the node name where the Data Protection application is installed. This is the node name that is used to store the Data Protection for SQL Server backups.
v If you are configuring Data Protection for SQL Server for SQL Server 2012, enter a node name in the AlwaysOn Node field. This is the node name that is used to back up the availability databases in an AlwaysOn Availability Group. By default, the Windows Failover Cluster name is used.
v If the Tivoli Storage Manager for Virtual Environments Recovery Agent license is available, enter the data center node name. The data center node is the virtual node that maps to a data center.
Create a node name that can help you distinguish the type of backup that is run. For example, if your host name is MALTA, you can name the VSS requestor node name MALTA, and you can create a Data Protection node name that is called MALTA_SQL. For an SQL configuration, the AlwaysOn node name does not have to be related to the VSS Requestor or the Data Protection for SQL Server node name. For example, you can name it TSM_ALWAYSON.
d. Enter information for the Tivoli Storage Manager server that you are connecting to and click Next to continue.
v In the Tivoli Storage Manager Server Address field, enter the TCP/IP domain name or a numeric IP address for the Tivoli Storage Manager server that contains the backups. Obtain this information from your Tivoli Storage Manager server administrator.
v In the Tivoli Storage Manager Server Port field, enter the port number for the Tivoli Storage Manager server that contains the backups. Obtain this information from yourTivoli Storage Manager administrator.
v Specify whether to have the wizard to configure the Tivoli Storage Manager server for you by generating a configuration macro file.
If you click No, the macro file is available at the final page of the wizard so that it can be provided to the Tivoli Storage Manager administrator as an example of one way to configure the Tivoli Storage Manager server to support application data protection.
If you click Yes, the wizard starts the macro during the Configuration step in the wizard. Review the macro file and update it if needed.
After you click Yes, enter the following information in the appropriate field:
– The name of the Tivoli Storage Manager administrator account.
– The password for the Tivoli Storage Manager administrator.
– Click Test Communications if you want to test your connection with the Tivoli Storage Manager server. This button is not available until the VSS requestor is installed.
– Click Review/Edit to review or update the Tivoli Storage Manager macro file. Alternatively, you can review the macro file and directly run the commands on the Tivoli Storage Manager server.
e. Select the Default configuration setting. When you select the Default configuration setting, in addition to configuring the applications that you selected, the VSS Requestor is configured. The client and agent services are also registered and configured, and a schedule to support historical
managed capacity is defined.
f. Review the results of the configuration process. Click Show Details to view a list of individual configuration results.
5. Click Finish in the Completion page to complete the wizard.
6. Optional: For a VSS configuration, verify that the Run VSS diagnostics when this wizard exitsoption is selected. When this option is selected, after you complete the wizard, a diagnostic process tests the VSS snapshots on your system.
Attention: If the configuration is for space-efficient target volumes for SVC or Storwize V7000, testing VSS snapshots deletes previous backups that are created for the volumes that are selected in the test wizard.
What to do next
The configuration wizard automatically installs the Tivoli Storage Manager backup-archive client.
After you configure Data Protection for SQL Server, complete the following steps to verify the configuration:
1. In the Management Console, click the Automate tab to access the integrated command-line interface.
2. On the lower half of the screen, click the Open folder icon, and select the verify_sql.txtfile.
3. Click Open. These commands are displayed in the command-line panel:
query tdp query tsm query sql
4. Click Enter to run the commands to verify your configuration.
Related tasks:
“Quick installation and configuration” on page 45
“Manually configuring Data Protection for SQL Server”
Manually configuring Data Protection for SQL Server
If you manually configure Data Protection for SQL Server, complete the following steps.
Perform these tasks on the computer running the SQL Server
For best results, use the configuration wizards to configure Data Protection for SQL Server for a step-by-step guide of the configuration requirements. However, if you prefer to do these steps manually, follow these configuration instructions.
Before you begin
Before you begin, ensure that the SQL server is running.
Procedure
Perform these steps on the computer where the SQL Server is installed and running:
1. Specify your Data Protection for SQL Server node name and communication method in the dsm.opt file located (by default) in the Data Protection for SQL Server installation directory. More options are also available.
2. Using the set command, specify your Data Protection for SQL Server
preferences (language, date format, log file) in the tdpsql.cfg file in the Data
3. If you are configuring Data Protection for SQL Server in an SQL Server 2012 environment, specify the Tivoli Storage Manager node name that is used to back up the AlwaysOn availability databases. You can specify the AlwaysOn node name by using the alwaysonnode option in the tdpsql.cfg file. For example:
set alwaysonnode myAlwaysOnNode
All availability databases in an availability group are backed up under this node name. Any stand-alone databases are backed up under the standard Data Protection for SQL Server node name.
4. For SQL Server 2012: If you want all databases to be backed up by default under the AlwaysOn node, specify the usealwaysonnode option in the tdpsql.cfgfile. For example:
usealwaysonnode yes
This option is useful if you are changing your database backups from the standard Data Protection for SQL Server node to an AlwaysOn node.
5. (VSS only) Specify your VSSPOLICY statement in your Data Protection for SQL Server configuration file.
6. (VSS only) Configure the Tivoli Storage Manager backup-archive client if it is not already configured. If the backup-archive client is already configured, you can use existing client services. The backup-archive client Setup Wizard can guide you through the configuration process. In the backup-archive client GUI menu, select Utilities > Setup Wizard > Help me configure the TSM Backup Archive Client. The node name for this system is referred to as the Local DSMAGENT Nodeand is specified with the localdsmagentnode parameter in the Data Protection for SQL Server configuration file (tdpsql.cfg).
For more information, see Tivoli Storage Manager for Windows Backup-Archive Clients Installation and User's Guide and “Proxy node definitions”.
7. (VSS only) Install and configure the Tivoli Storage Manager Client Acceptor Service (CAD) if it is not already installed and configured. In the
backup-archive client GUI menu, select Utilities > Setup Wizard > Help me configure the TSM Web Client. Make sure that the CAD service is running before you proceed to the next step.
8. (VSS only) Install and configure the Tivoli Storage Manager Remote Client Agent Service (DSMAGENT) if it is not already installed and configured. In the backup-archive client GUI menu, select Utilities > Setup Wizard > Help me configure the TSM Web Client. If a DSMAGENT is already installed and configured, you can use the existing one.
9. (VSS only) If you want to manage local persistent VSS snapshots, which are created for VSS backups to LOCAL, VSS Instant Restores, and want to run offloaded backups, you must install IBM Tivoli Storage FlashCopy Manager.
10. (VSS only) Install and configure a VSS provider. Consult the VSS provider documentation for information about configuration of that software. There is no installation or configuration that is required if you are using the default Windows VSS System Provider.
11. (VSS only) Change the SQL Server VSS Writer from Manual to Automatic and start the service.
12. (VSS only) Define storage space to hold VSS backups that is on local shadow volumes. Define enough space to hold all copies of the VSS backups as designated by your policies. Provisioning storage space to manage VSS snapshots is dependent on the VSS provider that you use. Consult the VSS Provider documentation for more details.
Related concepts:
“Specifying Data Protection for SQL Server options” on page 37
“Specifying Data Protection for SQL Server preferences” on page 40
“Proxy node definitions (VSS backups)” on page 34
“Back up to Tivoli Storage Manager storage versus back up to local shadow volumes” on page 21
Related reference:
“Set positional parameters” on page 239
Perform these tasks on the Tivoli Storage Manager server
Ensure sure that the Tivoli Storage Manager server is available before you process this task.
Procedure
Follow these steps on the Tivoli Storage Manager server:
1. Define the policy domains, policy sets, management classes, copy groups, and storage pools. Choose what is necessary to meet your Data Protection for SQL Server backup and restore requirements. For VSS operations, Tivoli Storage Manager server authentication must be on.
2. Register your Data Protection for SQL Server node name and password with the Tivoli Storage Manager register node command. For example, for VSS operations, this node is the target node. When you register nodes to the Tivoli Storage Manager server specifically for VSS operations, do not specify the Tivoli Storage Manager USerid=NONE parameter. VSS operations fail when this parameter is specified.
3. (VSS only) If not already defined, register your Tivoli Storage Manager backup-archive client node name and password for the system where the SQL Server is installed. For example, this agent node is the Local DSMAGENT Node for VSS operations.
4. (VSS only) If you plan to run offloaded backups from a particular system, first register the Tivoli Storage Manager backup-archive client node name and password for the system. For example, the agent node is the Remote
DSMAGENT Node. BAOFF is used here (and in Step 5) to differentiate between this Remote DSMAGENT Node and the Local DSMAGENT Node (Step 3). You can replace BAOFF with the node name of your backup-archive client, and remove the BAOFF from the grant proxynode command.
5. (VSS only) Define the proxy node relationship (for the target node and agent nodes) with the Tivoli Storage Manager grant proxynode command. For example:
grant proxynode target=alwayson node name agent=BAnodename
What to do next
For any warning messages that are displayed during the configuration process, resolve the issue noted in the warning. Some warnings include a link to a macro that you can use to configure Tivoli Storage Manager. Other warnings have links to web sites where you can download the packages that you are to successfully complete the configuration process.
Perform these tasks on the machine running the offloaded backups
This task is for VSS operations only.
Procedure
Perform the following steps on the computer that is running the offloaded backups:
1. Configure the Tivoli Storage Manager backup-archive client if it is not already configured. If the backup-archive client is already configured, you can use existing client services. In the backup-archive client GUI menu, select Utilities
> Setup Wizard > Help me configure the TSM Backup Archive Client. The node name for this system is called the Remote DSMAGENT Node and is specified with the remotedsmagentnode parameter in the Data Protection for SQL Server configuration file (tdpsql.cfg) on the local, not offload, system.
2. Install and configure the Tivoli Storage Manager Client Acceptor (CAD) Service and the Remote Client Agent Service (DSMAGENT) if they are not already installed. If a client CAD Service is already installed and configured, you can use an existing one. Use the backup-archive client Setup Wizard to guide you through the CAD installation process by selecting Utilities > Setup Wizard >
Help me configure the TSM Web Client.
3. Install and configure a VSS provider if you are not using the default system VSS provider. Consult the VSS provider documentation for information about the configuration of that software.
Perform these tasks to verify your configuration
Before attempting to perform a backup or restore operation, verify that Data Protection for SQL Server is installed and configured correctly.
Manually verifying the installation and configuration of Data Protection for SQL Server from the command line
You can issue query commands at the command line to verify the installation and configuration.
1. Click Start > All Programs > Tivoli Storage Manager > Data Protection for Microsoft SQL Server> SQL Client - Command Line.
2. Enter the following commands:
tdpsqlc query tdp tdpsqlc query tsm tdpsqlc query sql
The Data Protection for SQL Server Server configuration has been verified when these commands complete without errors or warnings.
Verifying that a SQL Server is ready to perform VSS operations
Perform the following tests to verify that your SQL Server is ready to perform VSS operations. For best results, perform these tests prior to installing Tivoli Storage Manager.
When these tests complete without errors, you can install Tivoli Storage Manager.
For Windows 2008 and later, the DiskShadow tool is preloaded. You can run the diskshadow commands, but you must download the diskshadow tool to run them.
Using the DISKSHADOW command (Windows 2008 and later)
Before installing Data Protection for Microsoft SQL Server, test core VSS functionality first. VSS functionality can be validated with the Windows 2008 Server-embedded command DISKSHADOW. DISKSHADOW is available for Windows Server 2008 and Windows Server 2008 R2. The following DISKSHADOW tests can be completed before any Tivoli Storage Manager components are installed.
1. Test non-persistent shadow copy creation and deletion. Run diskshadow in a command window and enter the following commands:
DISKSHADOW>begin backup
DISKSHADOW>add volume f: (Database volume) DISKSHADOW>add volume g: (Log volume) DISKSHADOW>create
DISKSHADOW>end backup
DISKSHADOW>list shadows all (this may take a few minutes) DISKSHADOW>delete shadows all
Volumes f: and g: represent the SQL Database and log volumes.
Repeat this sequence of commands 4 times. Verify the Windows Event Log contains no errors.
2. Test persistent shadow copy creation and deletion. Run diskshadow in a command window and enter the following commands:
DISKSHADOW>set context persistent DISKSHADOW>begin backup
DISKSHADOW>add volume f: (Database volume) DISKSHADOW>add volume g: (Log volume) DISKSHADOW>create
DISKSHADOW>end backup
DISKSHADOW>list shadows all (This may take a few minutes) DISKSHADOW>delete shadows all
Volumes f: and g: represent the SQL Database and log volumes.
Repeat this sequence of commands 4 times. Verify the Windows Event Log contains no errors.
3. Test non-persistent transportable shadow copy creation and deletion.
Run diskshadow in a command window and enter the following commands:
DISKSHADOW>set context persistent DISKSHADOW>set option transportable DISKSHADOW>begin backup
DISKSHADOW> add volume f: (Database volume) DISKSHADOW> add volume g: (Log volume)
DISKSHADOW>set metadata c:\metadata\sqlmeta.cab (specify the path where you want the metadata stored)
DISKSHADOW> create DISKSHADOW>end backup
Manually copy the sqlmeta.cab file from the source server to the offload server and run the following commands:
DISKSHADOW>LOAD METADATA path to sqlmeta.cab DISKSHADOW>IMPORT
DISKSHADOW>list shadows all (This can take a few minutes) DISKSHADOW>delete shadows all
Volumes f: and g: represent the SQL Database and log volumes.
Repeat this sequence 4 times. Verify the Windows Event Log contains no errors.
After the tests complete satisfactorily, you can install Tivoli Storage Manager components.
Diagnose the cause of common errors returned from VSS operations
The following two errors are commonly returned when performing a VSS operation. Information is provided to help locate the cause of the error.
ANS1017E (RC-50) Session rejected: TCP/IP connection failure This message is displayed when the Tivoli Storage Manager
backup-archive client CAD is either not running or is not configured properly.
ANS1532E (RC5722) Proxy Rejected: Proxy authority has not been granted to this node. This message is displayed when the Tivoli Storage Manager server has not
been configured for the proxy nodes correctly.
Configuring where scheduled backups are run on availability replicas
You can configure to select an availability replica on which to run a backup.
About this task
When an availability database is replicated across multiple availability replicas in an availability group, a mechanism is required to select a single replica on which to run a backup operation instead of backing up all replicas. This mechanism can also offload the backup from a primary replica to a secondary replica for load balancing. When databases failover, backups must continue to run from other
When an availability database is replicated across multiple availability replicas in an availability group, a mechanism is required to select a single replica on which to run a backup operation instead of backing up all replicas. This mechanism can also offload the backup from a primary replica to a secondary replica for load balancing. When databases failover, backups must continue to run from other