3

For those who have virtualized SQL Servers with very high volume transaction workload (very high rate of data change), I am curious how you handle VM backups, or if you even do them.

Of course, I manage a separate chain of transactionally-consistent native database backups. But the standard policy at the virtualization level has always been to backup all VMs. This is currently using a typical 3rd party VM backup solution (Initiate VM snapshot, copy snapshot data to backup appliance, release snapshot). However, these servers have an extremely high rate of data change, resulting in the change files growing large very quickly. And the system is reaching a point where it is choking on the amount of data that needs merged during the snapshot release.

I am toying with the idea of skipping the VM backups altogether. I don't permit any other apps to be installed on SQL Servers, and the base OS and SQL configuration is pretty vanilla. So it wouldn't take much in a failure scenario to just deploy a new VM from template, install SQL bits on it, and start restoring database backups.

Thoughts? Alternatives? Pitfalls I'm missing? Thanks for your input.

4

One option is to set the disks that contain the data and log files of the user databases to independent disks, so the VM snapshot backups will not include those disks. As long as your database backup strategy is sound, you don't need to include the data and log files in the VM backup. This may not be possible, of course, if you have applications and database files installed on the same disk.

One helpful thing here is to have your master, model, and msdb on one of the disks that is included in the VM backup, so that when the VM backup is restored you won't have to restore the system databases, which is a bit of a pain. I scripted a process to move system databases--can be found at Script to move SQL Server System Database Files.

If this approach is used, restoring the server will take some work as you'll have to restore the VM backup, then create the disks and directories for the database files, then restore databases, etc.

A side benefit of this approach is that if you have really large volumes (multi-terrabyte), a VM snapshot of all of the disks will typically pause the server for several seconds, so this method will effectively eliminate that "stunning" effect. If you are using clusters/availability groups, the pause can cause availability groups to go offline for a bit.

One reason why you don't want to stop backing up the VM is that if you build a new server or restore the OS from an old backup, it will require a lot of time to get all of the Windows Updates installed, and this could amount to a significant amount of downtime. If you have a VM template (or whatever deployment method that is in use) that is kept updated this of course wouldn't be an issue.

2
  • That's an interesting approach. I hadn't considered independent disks. However, regardless of location of system dbs if I had to recover from a server-level failure I would probably restore system dbs anyways to ensure they were up to date with the most recent changes (logins, Agent jobs, etc.). And Microsoft support policy would require the backups to be VSS, which would still involve freezing IO on the system DBs, effectively freezing the entire instance. It just might be for a shorter overall duration. Nov 19 '19 at 14:38
  • I keep the system DBs on the disks that are backed up via snapshot, and I also keep a current backup of system DBs on that disk, so if there is a need to restore the latest master or msdb it's quite easy and can be scripted ahead of time. Nov 19 '19 at 15:58

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.