Sql-server – Skipping VM backups for high workload SQL Server

backupsql servervirtualisation

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.

Best Answer

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.

Related Solutions

Sql-server – Automated SQL backup on a timely fashion, & cleaup the database for the backed up data

Instead of rewriting your own solution, my suggestion is to use SQL Server Maintenance Solution - SQL Server Backup

Edit: Below script will help you once you deploy Ola's script and create required objects:

EXECUTE dbo.DatabaseBackup
@Databases = 'USER_DATABASES', -- put the databases you want to backup
@Directory = 'C:\Backup',      -- change the backup directory to suit your needs
@BackupType = 'FULL',          -- This will take FULL database backups
@Verify = 'Y',                 -- This will verify the backups
@Compress = 'Y',               -- Since you are using SQLServer2008R2, standard and enterprise both have backup compression available. 
@CheckSum = 'Y',
@CleanupTime = 24              -- This will delete the backup older than 24 hrs !!

Sql-server – Alternative for taking the backup of SQL DB’s on backup drive

Speaking as someone who has taken over from multiple third parties who have done NetBackup database backups you can safely assume three things.

Many of your servers and individual databases have not been backed up. You would do best to confirm yourself through msdb checks.
The "tapes" being backed up to are probably not accessible, do not work, or otherwise don't exist.
You can probably find a lot of NetBackup failure notifications in ErrorLog which have been ignored for days, months, and years.

Regarding your specific situation backups are meant to be taken to network shares and backing up to the same server is wrong. What is going to happen when your server VM crashes or becomes corrupt? You've lost all your backups.

Whatever security / audit issues have come up are obviously wrong and you should be rallying against them. Or - print out your ironclad disclaimer and get your manager to sign it - because they're going to be fired down the line when they lose everything and you don't want this to destroy your career along with their poor decisions.

Best practice? A network share like you mentioned, and standardise on Ola Hallengren's scripts like others mentioned.

Don't be afraid to modify the setup script a little (or add your own to run afterwards) to set up the jobs the way you like them and to schedule them; you can also stagger them server by server if you like to spread the load. I wrote a corresponding script in PowerShell to help do the deployments (create a folder on each server to store the output text files; grant the service account access; create the network folder and grant the service or network account access; then deploy the jobs in the way we want them and with appropriate schedules).

You should also prioritise setting up your registered server list so that in the mean time you can run mass checks of msdb catalogs to make sure you haven't missed anything.

Network and CPU going high doesn't mean anything unless it impacts Production; the only important thing aside from gigabit network interfaces is that the network share is somewhere relatively close to whatever network segments you have. And the initial FULL backups will always be an issue, that's why they're scheduled for weekends, then daily DIFF will be fine.

Otherwise the only databases where this really becomes a problem are when they start going over 1TB (especially if it's much higher, 5TB, 20TB) and those need special attention.

Best Answer

Related Solutions

Sql-server – Automated SQL backup on a timely fashion, & cleaup the database for the backed up data

Sql-server – Alternative for taking the backup of SQL DB’s on backup drive

Related Question