With Microsoft SQL Server Always On availability groups, you have the option to back up databases from either the primary node, the secondary node, or the preferred node. This option has been available ever since availability groups were introduced. The capability to offload the backup task to a secondary instance is the primary use case for availability groups. But you might encounter scenarios in which the secondary database node becomes out of sync with the primary database and the availability group state isn’t healthy. In those cases, recovering your database can involve extensive effort, especially if your database is large. Your recovery efforts might include removing the database from the availability group, restoring a full database backup, then adding the database back to the availability group. And let’s not forget that large chunks of transaction log files must be restored on a full database backup. The whole task can be cumbersome if a multiple-node environment is involved in which one node is healthy, but the remaining nodes are in an unhealthy state. That’s where NetApp® technology can help. With NetApp SnapCenter® software, you can reseed databases on a secondary node without affecting any SQL Server settings and without writing any complex script. It’s easy: SnapCenter lets you configure a backup on all the nodes while you create your backup policies.
The next figure shows the sequence in which SnapCenter executes the reseed operation. To access the log backup from the primary SQL Server instance, SnapCenter accesses the transaction log backup through the UNC path. If it does not have permission, then to restore the log backup, SnapCenter mounts the NetApp FlexClone® volume of the Snapshot copy that contains the log backups.
With SnapCenter, it’s simple to enable backup from all the availability group nodes. The option to set your preferences is available while you are creating backup policies:
Manohar Kulkarni is a Technical Marketing Engineer (TME) focusing on data protection of Microsoft applications on ONTAP storage systems at NetApp. Being in product management, he helps in product designing, strategizing, developing new ideas, and on a need basis providing consulting and sales assistance. He likes troubleshooting problems and coding to automate tasks to simplify day-to-day tasks.