Bulletproof Your SQL Server with Clumio’s Single-Pass Backup

If you are anything like me, you love demo videos. So, today I am excited to announce that we are kicking off a new series of blog posts and videos that showcase how IT administrators can quickly and easily protect public, private, and SaaS data in Clumio. If you are not familiar with Clumio, it’s the industry’s first cloud native data platform to backup heterogeneous cloud data sources in one service, on one single pane of glass, and with one customer experience.

Today, I will provide step-by-step details on how IT admins can protect SQL-Server databases via Clumio using a single-pass backup. To make it easy to consume, the demo is divided into two parts – In part one we will walk through a “full restore;” While part two will showcase how to perform a “point in time recovery” with the Clumio platform

The prerequisites for the demo are:

• Volume Shadow Copy Services (VSS) that must be running on the servers, and
• VMTools that has to be installed and
• vSphere version >= 6.5.

Figure 1: SQL-Server backup and recovery using Clumio

For part one of this demo, I want to simulate real world conditions, so I am running HammerDB in the background. HammerDB is a leading benchmarking and load testing tool which supports most of the popular databases. As you will see in figure 2 (below) and throughout the demo, HammerDB was generating a load of 20,000 transactions per minute of TPC-C(OLTP) based data, on the SQL server database. So with HammerDB running, let’s jump into the demo.

Figure 2: HammerDB load generation

Part 1: Rapid Restore from Clumio’s backup

This part begins to show the database’s initial state: the reference database and the reference table. When the database is restored from the Clumio data platform, the reference database and the reference table should be in the same state (figure 3).

Figure 3: Reference table

In Clumio, we take an on-demand backup of the database, as shown in figure 4.

Figure 4: On-demand backup in Clumio

At this point, we need to check that SQL Server VSS writer is running on the database server. Then, check the event viewer for backup status. As shown in figure 5, the backup was reported as successful by the Volume Shadow Copy Service.

Figure 5: Event viewer backup status

The backups are logged in the “backupset” table in the SQL server. This Clumio backup gets updated as Full Backup and Copy-only backup; since it is a copy-only backup, it does not break the LSN chain. See figure Figure 6.

Figure 6: Backupset table

Now we begin the restoration of the SQL Server virtual machine from Clumio. We need to select the virtual machine in the particular vCenter, which takes you to the Calendar view and then we select the date. Once the date is selected, it comes up with a popup-view shown in figure 7, where we need to provide the new VM name, datastore, and enable the network.

Figure 7: Restore Virtual Machine

Once the virtual machine is restored, we can connect to the SQL Server Management Studio and check the reference table. It should have all the records as seen previously in Figure 3, the reference table. So as seen, the restores are simple and straightforward using Clumio for the SQL server.