May 18, 2012 at 9:30 am
We are looking into options for backup and restore for our new database that is going to be about 5TB in size.
I will consider SQL backups but my concern is that even with compression etc. a full backup will just take too long. We are therefore also looking a O/S disk snapshots possibly cold or hot.
Our database will need to run in Full recovery mode so my question is:
If I restore the database from our O/S backup can I then roll forward through transaction log backups?
Hope this make sense.
Cheers,
Gary.
May 18, 2012 at 9:37 am
i don't beleive this is possible at all
to restore a database to point in time (or alternatly restoring t-logs while in full recovery mode) the database needs to be in "NORECOVERY" mode - which is how it is left after a normal restore operation.
i really don't recommend doing what you are planning as it's possible that your data file and log file will be slightly out of sync (if one backs up after the other) and therefore completely useless. - you would only be able to do this if you have exclusive access to the files - which involves shutting down SQL server.
have a look at Hyperbac and SQL Litespeed - both of these not only compress your backups, but make them faster as well
MVDBA
May 18, 2012 at 9:40 am
May 18, 2012 at 9:41 am
i also thought i might add - i've seen the strategy of taking SAN snapshots before. the particular client of ours was keeping a record of the data churn so that he could do point in time restore by using the SAN point in time recovery tool.
while it worked there was one massive drawback
the database reindexing/index defrag jobs generated a lot of noise in the backup space. the index defrag jobs were generating between 6 and 10 times the amount of data than was actually in the file itself. - their solution - disable all index maintenance operations - result - performance was a nightmare.
MVDBA
May 18, 2012 at 10:10 am
Our weekly 5TB database backup completes in 5 hours. The daily differentials complete in around 40 minutes. 5 minute Transaction log backups complete in around 20-40 seconds.
Data used grows by approx 5-10GB per day.
Hope this helps.
May 18, 2012 at 10:57 am
No way. What you are doing is saying, can I restore T-logs at any point in time to my production databases. That's what an OS snapshot is.
The answer is no. You cannot restore a t-log unless the database is in the restoring state. You cannot switch from online to restoring. You have to run a full restore to get there.
OS level, or SAN, snapshots can work for quick recovery. It's not instant, and you might get into a weird state. For example, if you have someone in progress, like an index rebuild, and you turn on a SAN snapshot, you could end up in a rollback state. If that's the case, then your database needs to rollback all the work that SQL thinks was in progress, but can't complete. Same thing for large updates, ETL loads, etc.
If you are worried about DR, what I'd suggest is that you get a standby server ready, and restore your full every night, or couple days or some relatively "short" time period and have that warm. 5TB isn't huge, nor small, but perhaps you can spare the space no Tier 2 type storage for disasters. Or restore it offsite somewhere. As long as you have the logs from the time of that full until the event, you can bring it forward relatively quickly.
Keep in mind that the time to get things running, your RTO, is almost always longer than you think. You have
Disaster time -> notification time -> decisions time on action -> find all backups -> perform restores.
Even with a warm server, this can potentially be hours without a full backup. Talk through various disasters with your management and not hurricane/tornado/earthquake disasters. Talk about things like this: http://www.youtube.com/watch?v=EL_g0tyaIeE
Note that your warm server is restore WITH RECOVERY. Actually, ALWAYS restore with NORECOVERY. It's trivial to switch the database online. Not trivial to recover from an accidental restore with RECOVERY (the default)
May 18, 2012 at 1:16 pm
SAN level snapshots will work - if they are done correctly and managed by the SAN vendors software it is very likely you will have the ability to perform a point in time restore.
It really depends on the SAN vendor and their utilities - and how well they are written to work with SQL Server.
You cannot just perform a snapshot on the LUN with the standard SAN utilities. The integration with SQL Server is required to update the databases (e.g. differential bit map, LSN chain, etc...). In all cases that I am aware of, you also have to perform your normal transaction log backups - but, it usually has to be done through that vendors utility so the utility is updated with the information also.
As for native backups - with a good system configuration, you could easily get a 5TB database backed up in less than 2 hours. It will require spreading the data files across multiple LUNs and possibly striping the backup across multiple LUNs and setting up the system with multiple HBA's so you can dedicate specific IO channels to each of the LUNs.
I know of several systems that backup 2TB databases in less than an hour - so it is entirely possible.
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
May 18, 2012 at 1:21 pm
Jeffrey Williams 3188 (5/18/2012)
SAN level snapshots will work - if they are done correctly and managed by the SAN vendors software it is very likely you will have the ability to perform a point in time restore.It really depends on the SAN vendor and their utilities - and how well they are written to work with SQL Server.
You cannot just perform a snapshot on the LUN with the standard SAN utilities. The integration with SQL Server is required to update the databases (e.g. differential bit map, LSN chain, etc...). In all cases that I am aware of, you also have to perform your normal transaction log backups - but, it usually has to be done through that vendors utility so the utility is updated with the information also.
As for native backups - with a good system configuration, you could easily get a 5TB database backed up in less than 2 hours. It will require spreading the data files across multiple LUNs and possibly striping the backup across multiple LUNs and setting up the system with multiple HBA's so you can dedicate specific IO channels to each of the LUNs.
I know of several systems that backup 2TB databases in less than an hour - so it is entirely possible.
Its worth reiterating that as Steve says, you will NOT be able to apply transaction log backups. You could potentially attach a database file but this will be online and not in the recovering state required to restore subsequent logs. Even if it were you wouldnt have a change of getting a backup with the correct LSN's to apply.
May 19, 2012 at 9:35 am
MysteryJimbo (5/18/2012)
Its worth reiterating that as Steve says, you will NOT be able to apply transaction log backups. You could potentially attach a database file but this will be online and not in the recovering state required to restore subsequent logs. Even if it were you wouldnt have a change of getting a backup with the correct LSN's to apply.
You absolutely can apply transaction logs, if you utilize the SAN utilities that are designed for SQL Server to restore the snapshot. Again, you have to use the SAN utilities that are designed to integrate with SQL Server - so the database is frozen (for consistency) and updated and marked as backed up in SQL Server.
Netapp Snap Manager for SQL Server is a good example - which I have used to recover a database to a point in time as well as restoring to another system from a SAN snapshot.
You need to be aware of what each SAN's utility can do - and how you have to setup the LUNs to support the functionality but it is very possible.
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
May 19, 2012 at 8:30 pm
How can you apply transaction logs to a running database? Forget about the snapshot, how can you apply a transaction log to a database that is running without ever doing a full backup?
May 20, 2012 at 2:45 pm
There's a difference between a file-level snapshot (which Steve it thinking of) and a VDI SAN snapshot backup which really is a full database backup with some really weird SAN tricks in it.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
May 21, 2012 at 3:07 am
it's exactly as Gila mentions.
I'm currently testing this with IBM's svc flashcopy manager.
These types of specialized SAN apps are fully SQL aware and use VDI (same as Microsoft's DPM solution) to pause the DB IO in order to take snapshots (which SQL sees as a full backup btw... you can see the meta data get updated in the DB after the SAN takes the snapshot).
if you wanna know more about this, you need to fully understand how VDI works/etc... and some SAN vendors have ways of apply t-log backups into the mix when restoring.
Also, for OP, just another data point, we do full backups of our 2 TB DB in just over 30 minutes. Backup is written to 8 files, across 4 luns and gets somewhere around 8-900 MB/s.
I have a scripted job that does the backup, remaps the luns to a 'reporting' server and does a restore each night. The restore takes just over 2 hours.
May 21, 2012 at 6:11 am
The point is you cannot apply transaction log backups to an OS level disk snapshot as suggested in the original post.
Gary Bourne-389258 (5/18/2012)
We are therefore also looking a O/S disk snapshots possibly cold or hot.......If I restore the database from our O/S backup can I then roll forward through transaction log backups?
Yes, there are alternatives if you can budget for the functionality (unless you already have it).
May 21, 2012 at 8:44 am
Jeff Kelly-310227 (5/21/2012)
it's exactly as Gila mentions.I'm currently testing this with IBM's svc flashcopy manager.
These types of specialized SAN apps are fully SQL aware and use VDI (same as Microsoft's DPM solution) to pause the DB IO in order to take snapshots (which SQL sees as a full backup btw... you can see the meta data get updated in the DB after the SAN takes the snapshot).
This is a SQL level backup, though, not an OS backup, or is it both? When you restore, is it quicker than a normal SQL full restore?
May 21, 2012 at 9:48 am
Steve Jones - SSC Editor (5/21/2012)
Jeff Kelly-310227 (5/21/2012)
it's exactly as Gila mentions.I'm currently testing this with IBM's svc flashcopy manager.
These types of specialized SAN apps are fully SQL aware and use VDI (same as Microsoft's DPM solution) to pause the DB IO in order to take snapshots (which SQL sees as a full backup btw... you can see the meta data get updated in the DB after the SAN takes the snapshot).
This is a SQL level backup, though, not an OS backup, or is it both? When you restore, is it quicker than a normal SQL full restore?
It's instigated at the OS Level, but it's effectively both. Even if you backup a SQL database file using the Windows Backup Service in recent versions of Windows, the Volume Shadow Copy Service executes a (copy_only) snapshot backup within the SQL Engine so it's transactionally consistent and can be treated like any other backup (along with associated log restores etc.):
http://msdn.microsoft.com/en-us/library/ms189548%28v=sql.105%29.aspx
The SAN/Backup tool just has to integrate with the correct API. Netapp/EMC etc. all then have their own interfaces with the relevant options (e.g. restore with/without recovery so additional transaction log backups can be applied) and they communicate with the Windows/SQL API's to acheive it.
Restores can't be as fast by definition, as you don't have a backwards path to a point in time restore (Oracle has Flashback, a kind of reverse transaction log), so I'm not aware of any vendors that offer more clever a solutions than just overwriting the current state with the previous backup.
Its slightly outdated that SAN/OS level backups are "hot" backups, some are, some aren't. I've used NetApp's stuff on a trial basis and it all works quite nicely, but we opted for native in the end as there was too much of a disconnect between the SAN team and the DBA team for everyone to be comfortable with it.
Viewing 15 posts - 1 through 15 (of 20 total)
You must be logged in to reply to this topic. Login to reply