Enabling application consistency for MSSQL DB in OpenStack for backup via NetBackup

Description

This article discusses freeze and thaw scripts for MSSQL DB for taking application consistent backup of MSSQL DB running in the guest/instance.

Introduction:

SQL Server databases fairly dependent upon application-consistent backups. In default configurations SQL do not normally write directly to their database files but instead rely upon log files. How do we make sure our application data is consistent? We use application-consistent backups.

Since NetBackup Openstack only supports image level backups, it cannot just copy the data bases files from the guest. To achieve consistent backups, NetBackup Openstack need to quiesce the SQL database and then take NetBackup Openstack snapshots.  Since, NetBackup Openstack snapshots are time efficient, the amount of time that the database is frozen is small .

To ensure MSSQL application consistency , NetBackup Openstack relies on QEMU Guest Agent functionality. A good description on QEMU Guest Agent can be found here.  QEMU agent put the guest file system into consistent state also allow guest applications to register and be notified prior to a snapshot, in order for them to allow flushing their data to disk. QEMU guest agent leverage VSS writers as components of the VSS service in Windows to correctly quiesce the application.  NetBackup Openstack leverages freeze and thaw commands of the guest agent to orchestrate database backup and resume operations.

QEMU guest agent

QEMU guest agent is a daemon program running inside the guest instance which helps management applications with executing functions that needed guest OS assistance. It allows hypervisor to issue commands to the guest operating system using libvirt, helping with functions such as freezing and thawing filesystems.

There are two ways to enable the communication channel between hypervisor and qemu guest agent in the guest.

1. If the instance is created from a glance image, glance image must have hw_qemu_guest_agent=yes metadata property set on the image before instance is created.
2.  If the instance is already created without this parameter set on the image:

• Add metadata hw_qemu_guest_agent=yes to the install image.
• Hard reboot the instance.

Package qemu-guest-agent must be installed on the guest OS.

MSSQL freeze and thaw

When NetBackup Openstack runs snapshot qemu guest agent issues fsfreeze command , it calls the VSS requester to freeze filesystems and applications. When the VSS service is triggered, it will notify these writers that a backup is occurring. The VSS writer for Microsoft SQL server allows the information in memory to be purged and the pending I/O operations to be flushed to disk in the correct transactional order so the backup of the disk with the application data contains consistent transactional information. 
When the VSS backup is complete, it signals the VSS writers, which are then to resume normal operation.
So when the freeze call is run database will be in consistent state and nbos can then start uploading snapshot data.

Prerequisite for SQL server backup

1. QEMU Guest Agent version should be 7.4.5 and above . This is the latest but not mention as stabled . 
   Which can be downloaded from https://fedoraproject.org/wiki/Windows_Virtio_Drivers .
2. Proper COM access permissions should be given . In order to avoid access denied error for VSS when a backup is running .   Following link gives the resolution - https://www.terabyteunlimited.com/kb/article.php?id=563  .
3. In order to run MSSQL backup in consistent state all the VSS writers should be in "Stable" state with "No Error " . You can check vss writer status with following command .
   vssadmin list writers
   For failed writes you can restart corresponding service. 
4. In order to avoid timeout for VSS we can follow the following link - https://technet.microsoft.com/en-us/library/dd364933(v=ws.10).aspx 

Recovering SQL Database

1. NetBackup Openstack allows VM level recovery . Restoration of  SQL application VM to previous state or for disaster recovery purposes is possible . All the database will also get restored with VM Restore.
2. For Table level recovery NetBackup Openstack support Kroll OnTrack Powercontrol . Steps to restore table with Kroll are as follow :

• Install Kroll OnTrack Powercontrol on Target Machine .
• Mount the nbos snapshot on Target Machine .
• Bring the Disk online with Device Manager on Windows .
• Open Kroll OnTrack Poercontrol select the source and target in Kroll OnTrack .Preview the table to restore .
• After that you can drag and drop the table as required . 

Applicable to:

OpenStack Distribution: RedHat OpenStack 16, 16.1
Components: Datamover
NBOSVM Version: 9.0.0.1