SnapCenter and SQL Server Always On Availability Groups: Why Back Up Databases from All Nodes?
Manohar Kulkarni
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.
What Are the Advantages of SnapCenter for SQL Server Always On Availability Groups?
You probably already know about the key SnapCenter benefit of application-consistent backup, along with the space savings that you can achieve with NetApp Snapshot™ capabilities. But here’s a benefit that you might not know about: SnapCenter allows you to back up databases from all the nodes that participate in a SQL Server Always On availability group. This option isn’t a default, however; you have to enable it from the SnapCenter policy settings. But that step is easy, as you will see later. We have also made some other improvements to SnapCenter software. We further optimized SnapCenter 4.1 to improve the restore procedure when the transaction log backup is applied. For a reseed operation, SnapCenter restores the last full backup copy that’s available on your current controller, then restores all the transaction logs that you have backed up from the primary and the secondary hosts. SnapCenter first tries to access the transaction log backup folder on the primary host through a Uniform Naming Convention (UNC) path. If it doesn’t have the required permission, SnapCenter mounts the volume from a Snapshot copy and attaches the disk to the target server to perform the log restore. This approach is quicker than copying the primary database to a replica, and it requires less network bandwidth. Following is an illustration of a scenario with a two-node availability group that hosts a 20GB database. SnapCenter is scheduled to create a full backup once a day at 3 a.m. and to create a log backup every hour. In the following figure, because of an internal error, the availability group state was not in sync after 6 a.m. System engineers were alerted by 9 a.m. In this situation, SQL Server can’t resume synchronization with the databases part of the availability group, and the only solution is to completely restore the database on the secondary node. The primary SQL Server instance has an up-to-date log backup, so by performing a reseed operation, SnapCenter can restore the last full backup and apply a log backup from the available Snapshot copies. [caption id="attachment_19171" align="alignnone" width="770"]
*WSFC : Windows Server Failover Cluster[/caption]
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.
Where Do You Set Backup Preferences for Availability Groups?
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:
- Go to SnapCenter > Settings > Policies.
- In the Backup Type section, expand the availability group settings.
- In Select Replicas for Backup, expand the menu. As you can see in the following screenshot, SnapCenter allows you to choose the node where you want to back up databases.
With just a few clicks, SnapCenter lets you choose your backup preferences with the options that Microsoft SQL Server Always On availability groups offer.
How Do You Reseed Your Database on the Secondary Node?
SnapCenter also makes this task easy. If you need to reseed your secondary database:- Select the secondary database that is discovered from the SnapCenter resources page.
- Click the Reseed button at the top right corner.
- At the prompt, click OK to confirm that you want to proceed.
SnapCenter then performs all the steps to back up and restore your database and to add it to your availability groups.
Make the Most of Your Availability Group Options
Start making your database backups faster, simpler, and more efficient. To streamline your database backup management and to find out more on SnapCenter, go to our NetApp SnapCenter page.
Manohar Kulkarni
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.
