Unsolved
This post is more than 5 years old
23 Posts
0
750
How to backup DBs from a secondary node using SQL Server 2012 with AlwaysOn feature
Hi folks,
Environment
- 3 servers (VM) running MS SQL Server 2012 with AlwaysOn feature, OS: Windows Server 2012 R2
VMSQL01: Primary, synchronous commit
VMSQL02: Secondary, synchronous commit
VMSQL03: Secondary, synchronous commit
- Cluster name: SQL-ALWAYSON
- We installed both Networker client 8.1.1 & NMM 3.0.1 on each servers
Objective
- We want to backup databases from a secondary node, VMSQL03 for instance
Current situation
- We've managed to backup databases from the primary node, using the following Networker config
1/ Creation of a Networker client “SQL-ALWAYSON” with the following settings.
Save set: All
Backup command:
Application information:
Remote access: *@*
This client is in the group Index only.
2/ Creation of 3 Networker clients “VMSQL01”, “VMSQL02” and “VMSQL03” with the following settings
Save set: APPLICATIONS:\SqlServerWriter
Backup command: nsrsnap_vss_save.exe
Application information: “NSR_SNAP_TYPE=VSS” and “NSR_VSS_FORCE_SYSTEM_PROVIDER=yes”
Remote access: *@*
These clients are in groups with the feature “snapshot” enabled
Question
- With this config we can backup databases on the primary node. We use NMM in order to recover backed-up databases. What we would like to do is to backup databases from a secondary node.
- My understanding is that since we perform a “VSS” type backup we can only backup databases on the primary node.
- When we run the command “nsrsnap_vss_save.exe -? –v” on the 3 nodes, we can see that only the primary node displays the different databases. The secondary nodes display a message saying databases will be skipped (“Database xxxxx of SQL Server xxxxx is a secondary replica of SQL Server 2012 AlwaysOn avaibility group. (..) In case of backup, il will be skipped ”)
- Can someone confirm we can only backup databases from primary node when using VSS type backup?
- If so, is there a solution to perform a backup from secondary node instead of primary?
Any help much appreciated.
Thanks.