No IT administrator wants to be “that person” who took the time to setup the SQL Server but forgot to prepare for what happens next. Whether your corporate IT system gets hacked, a machine or network fails, human error deletes the wrong database entries, or mother nature produces a real disaster, you as the IT administrator should have a backup and recovery plan in place to help mitigate data loss. You also want to be able to spot potential problems before a disaster happens.
This solution helps customers design and implement optimal configurations specifically for Microsoft SQL Servers on VMware® vSAN™ for Day 2 operations like backup, restore and operation monitoring using an integrated Hyper-converged solution.
The key highlights of the solution are:
- VMware vSphere snapshot and clone technologies provide support for Microsoft SQL Server Day 2 backup and recovery operations. This method applies to standalone databases and databases configure with AlwaysOn Availability Groups.
- Veeam, as a backup and recovery partner of VMware, provides the database or application consistency capability to perform a backup of the transactions logs of all changes after the point-in-time backup of VMs and can help to recover VMs to the point-in-time. This method applies to standalone and SQL Server Availability Groups.
- vRealize Operations Manager™6 with Management packs and Blue Medora VMware vRealize Operations Management Pack for Microsoft SQL Server provides centralized SQL Server operational and optimization consideration, centralized monitoring and management.
But How do I Backup and Recover?
There are six different backup and recovery scenarios described and validated in our solution, including VMDK based single instance SQL Server backup and recovery, VM based single instance & AAG SQL Server backup and recovery, Veeam based single instance SQL Server backup and recovery on local & remote vSAN cluster, and Veeam based AAG SQL Server backup and recovery. We briefly describe one such scenario here. For all validated scenarios and steps, see Microsoft SQL Server Database on VMware vSAN—Day 2 Operations and Management.
Sample Backup and Recovery Method
In this example, backup and recovery is accomplished by using a combination of VM-level snapshots and VM clones of a standalone SQL Server in a VM. The recovery process powers on the cloned VM and the SQL Server automatically recovers, equivalent to a cold reboot.
We ran a HammerDB workload, a transactional TPC-C workload, against the single instance SQL Server in a VM running on a vSAN Cluster as our testing tool. The steps to validate this backup and recovery method while the workload is running are:
- Create a crash-consistent backup of the SQL Server by either of these methods:
- Use vSphere Web Client to clone the SQL Server VM. This process will create a temporary snapshot used for cloning and then deletes the temporary snapshot after the cloning operation is complete
- Use PowerCLI to:
- Create a VMware snapshot of the SQL Server VM
- Clone the snapshot VM
- Delete the snapshot
- Power on the cloned virtual machine, the cloned database is then started and recovered using the SQL Server Instance recovery mechanism
But How Can I Monitor Performance?
Our solution describes and validates how to provide SQL Server centralized management using vRealize Operations Manager 6.6 with management packs of vRealize Operations Native Management Pack for Storage Devices, and Blue Medora VMware vRealize Operations Management Pack for Microsoft SQL Server.
Again, we used HammerDB to generate an OLTP workload for extended periods of times. Details can be found in our reference architecture. One such example shows the database level metrics during the 12-hour OLTP run. The test run was using TPC-C benchmark on HammerDB for SQL Server on a 50GB database initially. The virtual user number was 125 and total transaction per user was 1 million. The figure shows the transactions per second, CPU utilization and disk usage. The average transaction per second was around 14 thousand at the database level. This end-to-end view and correlation can help identify key trends and troubleshoot bottlenecks.
Conclusion
VMware vSAN is a cost-effective and high-performance HCI platform that is rapidly deployed, easy to maintain, and is fully integrated into the VMware vSphere platform.
VMware vSAN 6.0 and above has improved the snapshot capability, which provides users with enterprise-class snapshots and clones.
Veeam Backup & Replication provides a set of features for performing data protection and disaster recovery tasks. Veeam Backup and Recovery for Microsoft SQL Server can be complementary to the VMware native backup and recovery to recover SQL Server standalone VM, and AlwaysOn Availability Groups enabled databases.
The VMware vRealize Operations Native Management Pack for Storage Devices and the Blue Medora VMware vRealize Operations Management Pack for Microsoft SQL Server provide robust management and monitoring capabilities for SQL Server running on vSAN. With the metrics and relationship data from these management packs, operations teams can consolidate reporting on health, performance and capacity reports are all from inside vRealize Operations, thereby increasing the productivity of HCI administrator.
In summary, this operation guide validates vSAN to be an HCI platform that can deliver efficient SQL Server Database Day 2 Operations. Also, vSAN provides improved data protection, cloning, and day-to-day management and troubleshooting functions by integrating with SQL Server database technologies.
For more information, so are not “that person,” see our online solution article on storagehub.com
Meanwhile, check out the video on YouTube: SQL Server Database Day 2 Operations Day 2 Operations and Management.
