February 28, 2011 at 10:20 am
Hey folks,
We have our SQL Servers running on a virtual cluster with fail-over everything.
I've been baby sitting this data for quite a few years, and have always pride myself with the knowledge my data was safe.. I did full backups nightly, followed by log backups ever 5 minutes and copied all these to secondary and tertiary servers and restored them near real time.. so I always had a server that I could turn to should the hardware fail. I also kept one server at the previous nights backup without the transaction logs restored automatically.. so If i needed a point in time.. I could restore the logs to that point.
I had backups I knew were good every day.. since I restored them every night.
In our new virtualized environment, My infrastructure team keeps telling me I have nothing to worry about in terms of hardware failure. (yeahrightsure). if that really were the case, my question is.. would I continue doing log backups? Could I convert everything over to Simple recovery, and do a few snapshots a day to give me the ability to get back to a point in time should I need it?
It appears that virtualization has reduced our need to backup to protect against physical errors. to backing up only to recover from logical errors. Am I missing something? If that's the case I would want to tighten the window on my snapshots so I could get back to a particular time quickly.. how much overhead does each snapshot require.. I realize that a daily snapshot contains all the transaction from that day and subsequent snapshot will contain slightly less transactions.. but I gain the ability to restore more quickly to that point in time ..
We have plenty of horsepower and space. I wanted to do hourly snapshots during production hours and fifteen minute rotating snapshots within the hour..over writing the 15 minute snapshots to conserve space after each hourly snapshot.
so theoretically .. I would have 10 hourly backups basically 7x the number of transactions snapshoted during the coarse of the day.
Thoughts?
February 28, 2011 at 11:23 am
Patrick Trongo (2/28/2011)
Hey folks,We have our SQL Servers running on a virtual cluster with fail-over everything.
I've been baby sitting this data for quite a few years, and have always pride myself with the knowledge my data was safe.. I did full backups nightly, followed by log backups ever 5 minutes and copied all these to secondary and tertiary servers and restored them near real time.. so I always had a server that I could turn to should the hardware fail. I also kept one server at the previous nights backup without the transaction logs restored automatically.. so If i needed a point in time.. I could restore the logs to that point.
I had backups I knew were good every day.. since I restored them every night.
In our new virtualized environment, My infrastructure team keeps telling me I have nothing to worry about in terms of hardware failure. (yeahrightsure). if that really were the case, my question is.. would I continue doing log backups? Could I convert everything over to Simple recovery, and do a few snapshots a day to give me the ability to get back to a point in time should I need it?
It appears that virtualization has reduced our need to backup to protect against physical errors. to backing up only to recover from logical errors. Am I missing something? If that's the case I would want to tighten the window on my snapshots so I could get back to a particular time quickly.. how much overhead does each snapshot require.. I realize that a daily snapshot contains all the transaction from that day and subsequent snapshot will contain slightly less transactions.. but I gain the ability to restore more quickly to that point in time ..
We have plenty of horsepower and space. I wanted to do hourly snapshots during production hours and fifteen minute rotating snapshots within the hour..over writing the 15 minute snapshots to conserve space after each hourly snapshot.
so theoretically .. I would have 10 hourly backups basically 7x the number of transactions snapshoted during the coarse of the day.
Thoughts?
You would still have to worry about a hardware failure - of the host. The guest VMs can be easily copied (if they are accessible) to any other host and quickly brought back up. You still also have to worry about the hard drives - though on a VM, I would guess that these would most likely be on a SAN drive array (properly RAIDed), and would be less susceptible to hardware issues.
Here's what I would do... I'd reach out to Brent Ozar. He has done A LOT of SQL virtualization, and IMHO would be the best resource to latch on to for these questions.
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
February 28, 2011 at 11:48 am
Thanks Wayne...
You are correct.. We have Multiple San boxes. Each is striped and then our data is striped across the boxes. We took a big jump on this one.. we went from 5 Local drives to almost 120 spindles in one night. We can unplug multiple boxes and pull multiple drives and still have the puppy barking..
on top of that we are doing some live replication offsite..
I still don't want to give up my blanky though.. I remember a controller failure that was writing bad data across multiple drives and no one knew. We had perfectly striped garbage.
Thanks again,
Patrick
February 28, 2011 at 2:41 pm
I would not stop performing transaction log backups and rely solely on database snapshots or even SAN snapshots. First, you cannot restore to a point in time from a snapshot - you can only roll back to that snapshot. Second, you cannot backup a snapshot - copy it over to your DR server, etc...
And finally, the overhead on multiple database snapshots is going to be fairly high. Each database snapshot will be updated as data changes in the source system. The more snapshots you have - the more SQL Server has to manage each snapshot and update it with the changes.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
February 28, 2011 at 10:01 pm
That sounds like putting all your eggs in one big (albeit powerful) basket. Assuming that you're still running nightly full backups and making copies of it off-site, you'll lose a day's worth of work if that basket breaks (which may be acceptable to your company since it'll probably take something catastrophic to break a basket of that size).
SQL BAK Explorer - read SQL Server backup file details without SQL Server.
Supports backup files created with SQL Server 2005 up to SQL Server 2017.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply