Performing Application Consistent Backups of SQL Server VM

Purpose

This document describes the steps needed to configure VMware VSS Provider and verify if VM backups result in application consistent images for SQL Server. 

Support Environments

• vSphere 6.5 or higher
• VM version 13 (ESXi 6.5 or later compatibility)
• Windows 2008 R2 or higher
• VMware tools 10.1 or higher
• SQL Server 2012 or higher

Setting up VSS Provider

Historically, VMware VSS Provider had been a hit or miss when it comes to performing application consistent backups. With vSphere 6.5, VMware had enhanced VSS Provider. With the arrival of vSphere 6.5, it is now possible to invoke VSS writers with specific quiescence flags to perform copy backups, truncate logs etc. It is also possible to configure the time out for virtual machines from 5 minutes to 4 hours. Clumio recommends that application consistent backups for SQL Server are attempted only on VMs running on vSphere 6.5 or higher. 

It is imperative that the customer double checks that the VSS Provider is installed properly and functional before Clumio backup is attempted to generate application consistent backups. 

During the installation of VMware Tools (this is required to install the VMware Snapshot Provider Service) pay attention to the following

• Ensure that the COM+ System Application service is listed as Started and that the startup type is listed as Manual.
• Ensure that the COM+ Event System service is listed as Started and that the startup type is listed as Automatic.

Once installed, during the idle state check the following

• Ensure that the COM+ System Application is listed as Started and that the startup type is listed as Manual.
• Ensure that the COM+ Event System service is listed as Started and that the startup type is listed as Automatic.
• Ensure that the Volume Shadow Copy service is not running and the startup type is listed as Manual.
• The _Microsoft Software Shadow Copy Provide_r service may or may not be started. Ensure that the startup type is listed as Manual.
• Ensure that the VMware Snapshot Provider is not running and that the startup type is listed as Manual.

Checking the Health of VSS Writer

Run ‘vssadmin list writers’ from Windows command prompt. Make sure that the writer ‘SQLServerWriter’ is in stable and no error condition.  

Verify Application Consistency SQL Server Data on VM Backups

There are no changes to the way you setup Clumio backup. Simply add the SQL Server VM to the policy and let it start seeding. Once the VM snapshot stage is complete (which you can verify from vSphere console ⇒ Click on VM ⇒ Monitor ⇒ Tasks), do the following on SQL Server to verify if SQL Server was protected in app consistent way by VSS. 

• Open Windows Event Viewer, navigate to Windows Logs ⇒ Applications, locate the VSS events generated by MSSQLSERVER after the backup was initiated from  Clumio, but before the VM snapshot creation task completed in vSphere.
  Note that the task category is backup, and backup device type is VIRTUAL_DEVICE. This confirms that SQL Server recognized the backup request via VSS. 
• Optionally, you can query the backup catalog on SQL Server (on msdb) to confirm that database backup event was registered. Execute the following from SQL Server Management Studio and make sure that it returns backup information for the time of the VM backup

USE msdb;
GO
SELECT 
CONVERT(CHAR(100), SERVERPROPERTY('Servername')) AS server_name, 
bs.database_name, 
bs.backup_start_date, 
bs.backup_finish_date, 
DATEDIFF(mi,bs.backup_finish_date, bs.backup_start_date) AS duration_in_sec,
bs.expiration_date, 
bs.backup_size, 
bmf.logical_device_name, 
bmf.physical_device_name, 
bs.name AS backupset_name, 
bs.description 
FROM msdb.dbo.backupmediafamily bmf
 JOIN msdb.dbo.backupset bs ON bmf.media_set_id = bs.media_set_id 
WHERE (CONVERT(datetime, bs.backup_start_date, 102) >= GETDATE() - 30) 
ORDER BY 
bs.database_name, 
bs.backup_finish_date;

Contact [email protected] in case of any clarifications or questions.