Example of of backup policy on Spectrum Protect Server

Protect> q copy
BATABASE      ACTIVE        MSSQL6D     STANDARD      No Limit     No Limit            8           8

https://www.ibm.com/support/pages/configuring-sql-alwayson-node-legacy-backup

Data Protection for SQL AlwaysOn (AlwaysOn Availability Groups AAGs) node configuration can be complicated, the examples below demonstrate how option files, configuration files and Tivoli Storage Manager server commands may be set up in a working environment.

On Data Protection for SQL server machine:

There are 3 SQL server machines:

NOTE: They must have the same level of Backup/Archive client and Data Protection for SQL installed.

NOTE Since Spectum Protect version 8.1.2, the SQL account use to backup, restore, or visual studio must have specific permissions (read/write is not enough):

• full access on C:\Program Files\Tivoli\ (containing dsmcert certificates and configuration)
• full access on C:\ProgramData\Tivoli\ (containing server certificates)

Host Name: maltavm1.storage.usca.ibm.com
SQL Instance: maltavm1

Host Name: maltavm2.storage.usca.ibm.com
SQL Instance: maltavm2\maltavm2sql

Host Name: maltavm3.storage.usca.ibm.com
SQL Instance: maltavm3\maltavm3sql

Cluster Name: sqlcvtclstr1

Sample dsm.opt (Data Protection for SQL):

NODename MALTAVM1_SQL
PASSWORDAccess generate
TCPServeraddress gijoe
TCPPort 1500
HTTPport 1582

Sample tdpsql.cfg (Data Protection for SQL):

LOCALDSMAgentnode MALTAVM1
BACKUPMethod Legacy
ALWAYSONNode sqlcvtclstr1

Sample dsm.opt (Backup/Archive client):

NODename MALTAVM1
PASSWORDAccess generate
TCPServeraddress gijoe
TCPPort 1500
CLUSTERnode no
CLUSTERDISKSOnly no

NOTE: Configuration files on other machines are similar with above. (change hostname, set sqlinstance name)

On the Tivoli Storage Manager server (gijoe.storage.usca.ibm.com): Sample commands for node registration on the Tivoli Storage Manager server:

Register the VSS Requestor nodes (Backup/Archive client):

    reg node maltavm1 maltavm1 passexp=0 backdel=yes dom=standard maxnummp=10
    reg node maltavm2 maltavm2 passexp=0 backdel=yes dom=standard maxnummp=10
    reg node maltavm3 maltavm3 passexp=0 backdel=yes dom=standard maxnummp=10

Register the Data Protection for SQL client nodes:

    reg node maltavm1_sql maltavm1_sql passexp=0 backdel=yes dom=standard maxnummp=10
    reg node maltavm2_sql maltavm2_sql passexp=0 backdel=yes dom=standard maxnummp=10
    reg node maltavm3_sql maltavm3_sql passexp=0 backdel=yes dom=standard maxnummp=10

Register the SQL AlwaysOn node:

    reg node sqlcvtclstr1 sqlcvtclstr1 passexp=0 backdel=yes dom=standard maxnummp=10

Sample commands to grant proxy authentication on the Tivoli Storage Manager server:

    grant proxy agent=maltavm1 target=maltavm1_sql
    grant proxy agent=maltavm2 target=maltavm2_sql
    grant proxy agent=maltavm3 target=maltavm3_sql
    grant proxy agent=maltavm1 target=sqlcvtclstr1
    grant proxy agent=maltavm2 target=sqlcvtclstr1
    grant proxy agent=maltavm3 target=sqlcvtclstr1
    grant proxy agent=maltavm1_sql target=sqlcvtclstr1
    grant proxy agent=maltavm2_sql target=sqlcvtclstr1
    grant proxy agent=maltavm3_sql target=sqlcvtclstr1

Query proxy for alwaysonnode on the Tivoli Storage Manager server:

tsm: GIJOE_SERVER1>q proxynode ta=sqlcvtclstr1


Target Node	Agent Node
--------------- 	---------------------------------------------
SQLCVTCLSTR1	MALTAVM1 MALTAVM1_SQL MALTAVM2 MALTAVM2_SQL MALTAVM3 MALTAVM3_SQL

Legacy backups are distributed across AAG replicas

  When you configure your environment to distribute a legacy backup across AAG replicas, follow these steps:

      Set the preferred replica to Prefer secondary replica.
      Install IBM Spectrum Protect Snapshot for SQL Server on all replicas that are eligible to run a backup.
      Create a command script to run a .CMD file with a backup command similar to the following example:

        tdpsqlc backup db1,db2,db3 full /alwaysonpriority

      Associate each IBM Spectrum Protect Snapshot for SQL Server node with the defined schedule.
      Run backups on the SQL node according to defined priorities for each database.