March 31, 2017 at 6:43 am
There is a five year old thread that discusses whether VM backups are good enough for a databas recovery strategy. The general consensus then was that they were not. I am interested in whether the opinion has changed at all.
We are running SQL2008 & SQL2008R2 (don't go there...) On VMs running a mix of WS2003, WS2008, WS2008R2, WS2012. We take transaction log backups every 4 hours which meets our SLA, and we currently run backups (logs and database) to the disks in the VM. Our network guys are pointing out that all we are doing is making the VM backup larger because we effectively have two copies of the database. The decision to go VM on the servers was to make the Disaster Recovery planning much simpler and cheaper.
I am considering dropping the database backups processes on the argument that we can always restore the VM and replay the transaction logs. Having the transaction log backups only on the VM makes me nervous because if the VM image becomes corrupted, I have no way of recovering the transaction logs. [The only time I have ever lost a significant amount of source code was when the repository was on a VM and the VM died! - luckily I also had an almost up-to-date copy of the repository on my laptop!)
What is a prudent strategy here. My SLA is 1 working day and the applications are only in use during business hours so I have a 10 hour window for maintenance (lucky me!). If it makes any difference we are using VEEM
Aaron
March 31, 2017 at 6:50 am
Hi,
wich version of VEEAM are you using. I think, only the new version of VEEAM is able to restore singel files. In case of emergency, maybe it is required, just to restore the mdf and log files.
The earlier version of VEEAM only could restore complete volumes, or the complete VM.
Kind regards,
Andreas
March 31, 2017 at 8:47 am
>> I am considering dropping the database backups processes on the argument that we can always restore the VM and replay the transaction logs.
Are you in a position to regularly test this restore option? If you are not then do not even consider it. As the DBA it is you who is responsible for restoring DBs.
Looking at the limited information available, I would be inclined to look at SQL server backups either to a network share or to the VM but immediately moving it to a share. Maybe:
1. A FULL backup each evening. If VSS looks as though it is attempting DIFFERENTIAL backups I would make the FULL backup COPY_ONLY.
2. Log backups every 15 minutes.
I would also look at automating test restores, CHECKDB etc on a different VM.
March 31, 2017 at 8:55 am
Have you tested a restore? Does it meet your SLA, the RPO and the RTO? If so, you're golden. Don't sweat it. However, most of the time, that kind of backup doesn't meet all the TLAs that define recovery.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
March 31, 2017 at 11:29 am
I think like Grant said, I'd test the restore. Go with the assumption of worst case scenario:
the VM gets corrupted
Time how long it takes from getting your VM guys to get a new VM spun up from backup because until that is done, you can't do anything. Now lets say they get it back up, but their backup had a few bits of corruption in it that caused all of your user database files and log files to be corrupted. So you need to restore from your backups (which we will asusme are not corrupted). How long does that take? Does that fit inside your SLA's?
I would not store my database backups inside the VM as Ken pointed out. What would you do if the VM got corrupted AND the VM backup was corrupted as well? Your VM guys can spin up a brand new VM for you and you can reinstall SQL, but with no known good database backups, there isn't much point.
Where I work, at one point they upgraded their backup software and it decided that our SQL backups were not important enough to write to tape and it just stopped backing them up. As the DBA I had no clue until one of the IT guys noticed that the weekly backups were a LOT smaller than they were the month before.
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
April 1, 2017 at 2:47 am
Reading everyone else's responses. Good stuff.
The thing is, it's not about who, how, or where the backup is done. I could care less. So could the company. It's about the restore. The recovery. Can we do it? Consistently? Quickly? Accurately? Yes to all those, then I could care less how the backup was done. No to any of those, I'm taking charge of the backups because I know how to make all the answers be yes.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
April 1, 2017 at 10:50 am
Grant Fritchey - Saturday, April 1, 2017 2:47 AMIt's about the restore. The recovery. Can we do it? Consistently? Quickly? Accurately?
I want to add that can we restore just the database. Most scenarios do not require the restore of the entire vm. Can i restore the database independently? If not, then the solution is not adequate.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
April 3, 2017 at 7:12 am
this is essentially placing the database backup management outside of the DBA, if the company is haapy with that and that rerstores will be performed by someone who is not necessarily a DBa then fine.
Personally i would push for the sql server level backups and store the files on a backup share so that the VM is not bloated, if thats the primary concern
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
April 3, 2017 at 12:15 pm
Perry Whittle - Monday, April 3, 2017 7:12 AMthis is essentially placing the database backup management outside of the DBA, if the company is haapy with that and that rerstores will be performed by someone who is not necessarily a DBa then fine.Personally i would push for the sql server level backups and store the files on a backup share so that the VM is not bloated, if thats the primary concern
The paranoiac in me would say, keep taking regular SQL backups and moving them off the VM immediately to somewhere that gets backed up (file system) on a regular basis (regular being defined by your organization.)
If the organization directs you to stop running SQL backups, the VEEM backups will be good enough; first, politely point out all the potential pitfalls of that approach (kiss point-in time recovery good bye, no ability to restore just ONE database on the server, etc,) then if they still prefer that solution, get it in writing with your objections noted.
Why?
CYA.
Because when a SQL server fails, and it turns out the VEEM backups maybe weren't working, or were only being done once a day in the morning and the entire days work is lost, the finger-pointing *will* commence. Really, that's a problem anytime someone other than the DBA is responsible for database backups.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply