SUMMARY
Best practices when protecting virtual machines containing Microsoft SQL Server Availability Groups at the hypervisor level.
ISSUE
Issue
When protecting virtual machines containing Microsoft SQL Server Availability Groups at the hypervisor level, there are a few key considerations to ensure proper consistency for backup and recovery.
Database Consistency
Unitrends performs VMware vSphere, Microsoft Hyper-V, and Citrix XenServer backups through the hypervisor APIs for single-server configurations. By default, Unitrends protects the virtualized applications in a consistent fashion through the hypervisor guest tools and Microsoft Volume Shadow Copy Services (VSS). This integration is critical for the application to take the proper actions for backup and restore.
With the release of SQL Server 2012 SP2, this general VSS integration allows databases with Availability Groups defined to be protected in a consistent fashion across all nodes in the Availability Group cluster. For this database configuration, Unitrends recommends Image Agent backup technology and also supports classic file agent + App Agent backup of SQL AGs and not virtual backups. Microsoft does not support VSS Copy snapshots of SQL AG nodes as this may result in unplanned cluster failover events due to snapshot stun timing.
VMWare has its own explicit configuration requirements for the structure of the AG VMs, and until recently appeared to support VMWare snapshots of AG nodes for backup and restore at the virtual level contrary to Microsoft documentation. Specific configuration requirements were documented here. http://www.vmware.com/content/dam/digitalmarketing/vmware/en/pdf/solutions/sql-server-on-vmware-availability-and-recovery-options.pdf When following that guide, Unitrends would also support virtual snapshot backup of SQL AG nodes understanding transaction logs are not truncated using this method (Simple Model DB configuration would be recommended or a 3rd party solution for log management). Recently, this document has been removed from VMWare's site. Other backup vendors have also ceased referencing this information and recommend image backup technology. At of July 2024 we are unable to confirm VMWare still supports this position, so Unitrends cannot at this time recommends VMWare backups of AG nodes as a best practice.
The remaining information in this KB remains for historical references for customers that may have had prior access to VMWare SQL AG Configuration documentation, architected their SQL nodes to those very specific standards, and wish to continue using VMWare backup of AG nodes. Customers who are unsure if their SQL nodes were deployed to VMWare's strict standards (which are not typical for any windows server and require substantial customization) should not attempt VMWare backups of AG nodes.
SQL Server Transaction Log Management
SQL Server offers 3 recovery model options for their databases, Simple, Full and Bulk Logged. When using the Simple recovery model, transaction log space is automatically reclaimed, thereby keeping the log file size relatively small. When using the Full or Bulk Logged recovery model, however, SQL Server transaction logs can grow over time and cause major issues for systems if they completely consume all remaining disk space. SQL Server Availability Group databases are required to use the Full recovery model. It is therefore important that transaction log growth is managed in one of two ways if Availability Groups are defined:
- Run periodic SQL Server transaction log backups
- Use native SQL Server commands that are executed either manually or scripted
Scripting against native SQL Server commands may provide the most benefit in terms of storage consumption because additional backup storage is not required. However, if you are not interested in running scripts to manage SQL Server transaction log sizes, you can use native SQL transaction log backups – or other tools that perform SQL Server transaction log backups. Microsoft has written many on how to do this using SQL Server Agent Jobs to perform SQL Server transaction log backups.
Note: Since any node can become Primary, a separate SQL Server backup job (or jobs) must be created to take transaction log backups of every node in the Availability Group. The user must decide whether to backup databases on the AG Primary, Secondary, or both, and create the jobs accordingly. Consult the SQL Server documentation for recommendations on how to set up backup jobs for Availability Groups.
Recovering Nodes Containing Availability Groups
When a backup of an Availability Group node the databases on that node will be restored to the state they were in when the backup was taken. This means a database in an Availability Group will not be in sync with the other databases on other nodes in the Availability Group. The Availability Group will likely have failed over to another node in the cluster when the failed client is restored, so the database on the restored VM will likely need to be removed from the Availability Group and deleted on the restored VM, then re-added to the Availability Group and re-synced. Microsoft has articles on how to do this – see https://docs.microsoft.com/en-us/sql/database-engine/availability-groups/windows/manually-prepare-a-secondary-database-for-an-availability-group-sql-server.
How to Audit the Restore
1. Perform a VMware Instant Recovery in Audit Mode.
2. Log into the virtual machine presented by the VMIR.
3. Enter SQL Server Management Studio.
4. Note that the databases are in recovery mode.
5. Delete the availability group.
6. Execute a query to set the database to emergency mode.
ALTER DATABASE dbName SET EMERGENCY
7. Execute a query to set the database to online mode.
ALTER DATABASE dbName SET ONLINE
8. Now the database should be available to run test queries to confirm the data state.
9. When you are done with the VMIR, tear down the VMIR using the Unitrends appliance web interface.
System Requirements
Note: Review the Unitrends Compatibility Matrix to determine the latest supported versions by Unitrends.
Supported hypervisors:
-
VMware vSphere 5.0 and above
-
Microsoft Hyper-V 2008 R2 and above
-
Citrix XenServer 6.2 and above
Supported SQL Server versions:
-
SQL Server 2012 SP2 (Cumulative Update 2) and above