TDP - Installing Storage Manager for Databases (Microsoft SQL Server)

From UNIX Systems Administration
Jump to: navigation, search

Planning

  1. Determine your backup strategy.
    1. VSS Backups (Full backup only)
    2. Legacy Backups (Full/Differential/Logs)

Server Preparation

  1. Two nodes must be registered, the node that will be used for the Backup/Archive Client, and one for TDP.
  2. Register the Backup/Archive node.
    1. tsm> REGister Node <hostname/clustered name> <password> DOmain=<domain> CONtact=<contact info>
  3. Register the Data Protection node.
    1. tsm> REGister Node <hostname/clustered name>-TDP <password> DOmain=<domain> CONtact=<contact info>
  4. Create the proxy access for the agent.
    1. tsm> GRANT PROXynode TArget=<hostname/clustered name>-TDP AGent=<hostname/clustered name>
  5. Backup Strategy
    1. If the path to the schedule includes a space, use the MS-DOS naming convention such as C:\Program Files would be C:\PROGRA~1
    2. Legacy Backups
      1. Create the full backup client schedule.
        1. tsm> DEFine SCHedule <domain> TDP-MSSQL-FULL Type=Client DESCription="TDP for MSSQL Full Backup" ACTion=Command OBJects="<C:\path\to\sqlfull.cmd>" STARTDate=MM/DD/YYYY STARTTime=HH:MM:SS SCHEDStyle=Enhanced DAYofweek=<day>
      2. Create the differential backup client schedule.
        1. tsm> DEFine SCHedule <domain> TDP-MSSQL-DIFF Type=Client DESCription="TDP for MSSQL Differental Backup" ACTion=Command OBJects="<C:\path\to\sqldiff.cmd>" STARTDate=MM/DD/YYYY STARTTime=HH:MM:SS SCHEDStyle=Enhanced DAYofweek=<day,day,day,...>
      3. Create the log backup client schedule.
        1. tsm> DEFine SCHedule <domain> TDP-MSSQL-LOGS Type=Client DESCription="TDP for MSSQL Log Backup and Truncation" ACTion=Command OBJects="<C:\path\to\sqllogs.cmd>" STARTDate=MM/DD/YYYY STARTTime=HH:MM:SS SCHEDStyle=Classic PERiod=4 PERUnits=Hours DAYofweek=ANY
    3. VSS Backups
      1. Create the full backup client schedule.
        1. tsm> DEFine SCHedule <domain> TDP-MSSQL-VSS Type=Client DESCription="TDP for MSSQL VSS Backup" ACTion=Command OBJects="<C:\path\to\sqlvss.bat>" STARTDate=MM/DD/YYYY STARTTime=HH:MM:SS SCHEDStyle=Classic PERiod=1 PERUnits=Days DAYofweek=ANY

TDP Client Installation

  1. Install the Tivoli Backup/Archive Client before proceeding.
  2. Install the Data Protection for SQL Server.
    1. Execute the setupfcm.exe program.
  3. Follow the instructions to complete the installation.

TDP Client Configuration

  1. Start the TDP MMC.
    1. Start > All Programs > Tivoli Storage Manager > Data Protection for Microsoft SQL Server > DP for SQL Management Console
  2. Expand: IBM Tivoli Storage Manager->Dashboard->Manage
  3. Click: Configuration
  4. Double click: Wizards
  5. Double click: TSM Configuration
  6. Restart the system.

TDP Client Verification

  1. Start the TDP MMC.
    1. Start > All Programs > Tivoli Storage Manager > Data Protection for Microsoft SQL Server > DP for SQL Management Console
  2. Expand: IBM Tivoli Storage Manager->Dashboard->Protect and Recover Data
  3. Click: SQL Server
  4. Click on Tab: Automate
  5. Change PowerShell to Command Line
  6. In the command window below execute the following commands.
    1. q tsm
    2. q tdp
    3. q sql
  7. If these commands do not return errors, the TDP Client is configured properly.

Installing the TDP Scheduler

  1. The scheduler requires the TSM Backup/Archive Client to be previously installed.
  2. Install a scheduler for TDP
    1. Run CMD.EXE as Administrator.
      1. Path to C:\Program Files\Tivoli\TSM\baclient
      2. dsmcutil inst /name:"TSM MSSQL Client Scheduler" /node:<hostname>-TDP /clientdir:"C:\Program Files\Tivoli\TSM\baclient" /optfile:"C:\Program Files\Tivoli\TSM\TDPSql\dsm.opt" /startnow:no
    2. A domain level service account with system administration abilities to MSSQL is required for the backups to work correctly. The logon account for the TSM MSSQL Client Scheduler from the previous step must be modified to this user.
      1. Start->Run, in the input field type services.msc
      2. Locate and select the TSM MSSQL Client Scheduler
      3. Right Click: Properties
      4. Select Tab: Logon
      5. Select: This Account
      6. Provide the fully qualified user name and password in the fields.
      7. Click: OK

Required Scripts

  1. Legacy Backups
    1. Create the following scripts by copying the C:\Program Files\TSM\TDPSql\sqlfull.smp file to the following names.
      1. sqlfull.cmd
      2. sqldiff.cmd
      3. sqllogs.cmd
    2. Full Backup Script
      1. Edit the sqlfull.cmd line.
        1. Modify
          1. %sql_dir%\tdpsqlc backup * full /tsmoptfile=%sql_dir%\srvrname.opt /logfile=%sql_dir%\sqlfull.log >> %sql_dir%\sqlsched.log
        2. To
          1. %sql_dir%\tdpsqlc backup * full /tsmoptfile=%sql_dir%\dsm.opt /logfile=%sql_dir%\sqlfull.log >> %sql_dir%\sqlsched.log
    3. Differential Backup Script
      1. Edit the sqldiff.cmd
        1. Modify
          1. %sql_dir%\tdpsqlc backup * full /tsmoptfile=%sql_dir%\srvrname.opt /logfile=%sql_dir%\sqlfull.log >> %sql_dir%\sqlsched.log
        2. To
          1. %sql_dir%\tdpsqlc backup * diff /tsmoptfile=%sql_dir%\dsm.opt /logfile=%sql_dir%\sqlfull.log >> %sql_dir%\sqlsched.log
    4. Log Backup and Truncation Script
      1. Edit the sqllogs.cmd
        1. Modify
          1. %sql_dir%\tdpsqlc backup * full /tsmoptfile=%sql_dir%\srvrname.opt /logfile=%sql_dir%\sqlfull.log >> %sql_dir%\sqlsched.log
        2. To
          1. %sql_dir%\tdpsqlc backup * log /tsmoptfile=%sql_dir%\dsm.opt /TRUNCate=yes /logfile=%sql_dir%\sqlfull.log >> %sql_dir%\sqlsched.log

File Permissions

  1. The TDP user will need to have full control over the following directories.
    1. C:\Program Files\Tivoli\TSM\TDPSql
    2. C:\Program Files\Tivoli\TSM\baclient

Further Reading

  1. IBM Knowledge Center
  2. Data Protection for Microsoft SQL Server
  3. Data Protection for SQL services to Microsoft Failover Cluster Management