Skip to main content
Enterprise applications

SQL Server availability group with SnapCenter

Contributors manoharvk
PDFs

SnapCenter supports backup of SQL Server availability group database configured with Windows failover cluster.

SnapCenter plugin for Microsoft SQL Server must be installed on all nodes of Windows server failover cluster. Refer the documentation on prerequisites and the steps to setup the SnapCenter plugins.

SnapCenter discovers all the databases, instances and availability groups in Windows hosts and resources are enumerated on the SnapCenter resource page.

Protecting databases in always on availability group

Databases in availability group can be protected in multiple ways.

  • Database level backup: Select the availability database for the database resource page, add the policy consisting of full/log backup, schedule the backup. SnapCenter takes the backup irrespective of the database role whether it is a primary replica or a secondary replica. The protection can also be configured by adding databases to resource group.

  • Instance level backup: Select the instance and all the databases running on the instance are protected based on the selected policy. All the databases, including the availability database running as primary or secondary replica are backed up using SnapCenter. The protection can also be configured by adding instance to resource group.

  • Availability group level backup: While configuring the policy, SnapCenter have a advance option for availability group level backup. The availability group setting in policy allows users to select the replica preference for backup. You could select primary, secondary replica or all of them. The default option is based on backup replica set in SQL Server availability group configuration.

The availability group setting in SnapCenter policy will apply only if availability group level backup is used to protect availability group databases and do not apply for database or instance level backup.

Tip NetApp recommends to use availability level backup to backup across all the replica running on NetApp ONTAP storage.

Configuring log backup in SnapCenter

If availability group is setup on standalone SQL Server setup then a dedicated disk must be mounted on each node of a Windows server failover cluster. Dedicated disk should be used to configure log directory to save transaction log backups.

If availability group is setup on SQL Server failover cluster then clustered disk should be created on SQL Server failover cluster instance to host log directory.

Restoring database in availability group setup with SnapCenter

  • SnapCenter provide reseed option to automatically recover the database from the latest snapshot available at the secondary replica. Reseed operation will automatically restore and join the database backup to availability group.

  • Alternate way to restore replica database in availability group is by breaking the availability group and performing the complete full and log restore. Use SnapCenter to restore database in norecovery mode and then use SQL Server management studio or T-SQL to join the database back to availability group.

  • To recover just subset of data, clone capability from SnapCenter can be used to create clone copy of database. Database copy is created within few minutes using SnapCenter, then export the data to primary replica using SQL Server native tools.

For best practice to setup database storage layout to meet the RTO and RPO requirement, please see TR-4714 Best practices for Microsoft SQL Server using NetApp SnapCenter.

Note SnapCenter do not support distributed availability group and contained availability group.