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):
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.