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. 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. Unitrends recommends running SQL Server 2012 SP2 Cumulative Update 2 due to the following note from Microsoft:
SQL Server 2012 SP2 only contains a partial fix for this issue. Servers running case-sensitive sort orders will require SQL Server 2012 SP2 Cumulative Update 2.
VMWare has it's own explicit configuration requirements for the structure of the AG VMs, storage, and other settings to ensure their integration works properly. See this VMWare KB and related Microsoft articles linked from within for more information: http://www.vmware.com/content/dam/digitalmarketing/vmware/en/pdf/solutions/sql-server-on-vmware-availability-and-recovery-options.pdf
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 is restored, 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: