March 18, 2010 at 9:50 am
I think the best solution for us will be database snapshots every 15 minutes. This comes with a price. Losing transactions between snapshots. But, restore time is just a few seconds.
I am trying different scenarios now and it looks promising.
JT
March 18, 2010 at 10:17 am
jorge.torralba (3/18/2010)
I think the best solution for us will be database snapshots every 15 minutes. This comes with a price. Losing transactions between snapshots. But, restore time is just a few seconds.I am trying different scenarios now and it looks promising.
JT
Interesting solution. It could work. I would be careful to ensure that a snapshot is saved multiple places in case of emergency - just like a backup. I would also hope that this will not replace a full database backup. You will also have the decision to make of what to do with your transaction log and whether or not to leave the db in full or simple recovery mode.
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
March 18, 2010 at 10:37 am
CirquedeSQLeil (3/18/2010)
Brandie Tarvin (3/18/2010)
... (keeping in mind you first have to restore the full db to SQL 2005 and it will be upgraded).You only need to restore the Primary Filegroup (the filegroup containing the system objects). This filegroup can be as small as 50mb. However, after that, you would need to restore each filegroup necessary to access the requisite data.
Um, if upgrading to SQL 2005? Are you sure about that? I would think taking a SQL 2000 backup to 2005 would require the FULL restore.
March 18, 2010 at 10:42 am
jorge.torralba (3/18/2010)
I think the best solution for us will be database snapshots every 15 minutes. This comes with a price. Losing transactions between snapshots. But, restore time is just a few seconds.
Any chance I can change your mind on this? Relying on db snapshots as a permanent part of your backup plan is not necessarily a good idea.
Snapshots have their places, but when you go to restore, you can only have one snapshot on the db at a time (it won't restore if you have multiple snapshots). So you'll have to delete all those snapshots first and possibly lose the one you really need.
Secondly, snapshots can take up a lot of storage space. As they fill up, copying blocks of 'pre-modified' data to their data files, they can grow as large as the database. And multiple copies of them?
Restore may be a little faster with a snapshot, but I wouldn't recommend going this route.
March 18, 2010 at 11:28 am
Brandie Tarvin (3/18/2010)
Any chance I can change your mind on this? Relying on db snapshots as a permanent part of your backup plan is not necessarily a good idea.
Snapshots have their places, but when you go to restore, you can only have one snapshot on the db at a time (it won't restore if you have multiple snapshots). So you'll have to delete all those snapshots first and possibly lose the one you really need.
Secondly, snapshots can take up a lot of storage space. As they fill up, copying blocks of 'pre-modified' data to their data files, they can grow as large as the database. And multiple copies of them?
Restore may be a little faster with a snapshot, but I wouldn't recommend going this route.
I thought about that. I would have scheduled backups as well as the snap shots. I woudl rely on my REAL backups for true disaster recovery. I would use snapshot restores for fat finger mistakes like the one we experienced the other day.
BTW, our restore took 11 hours using backups and trx logs. The snapshot in my testing has taken 8 seconds to restore. Now, if i schedule every 15 minutes, worse case scenario, I lose 15 minutes of data. Or, I can restore backup and lose 11 hours of production. Need to weigh my options.
JT
March 18, 2010 at 11:52 am
jorge.torralba (3/18/2010)
Brandie Tarvin (3/18/2010)
Any chance I can change your mind on this? Relying on db snapshots as a permanent part of your backup plan is not necessarily a good idea.
Snapshots have their places, but when you go to restore, you can only have one snapshot on the db at a time (it won't restore if you have multiple snapshots). So you'll have to delete all those snapshots first and possibly lose the one you really need.
Secondly, snapshots can take up a lot of storage space. As they fill up, copying blocks of 'pre-modified' data to their data files, they can grow as large as the database. And multiple copies of them?
Restore may be a little faster with a snapshot, but I wouldn't recommend going this route.
I thought about that. I would have scheduled backups as well as the snap shots. I woudl rely on my REAL backups for true disaster recovery. I would use snapshot restores for fat finger mistakes like the one we experienced the other day.
BTW, our restore took 11 hours using backups and trx logs. The snapshot in my testing has taken 8 seconds to restore. Now, if i schedule every 15 minutes, worse case scenario, I lose 15 minutes of data. Or, I can restore backup and lose 11 hours of production. Need to weigh my options.
JT
I think it is a very viable and good solution.
Thanks for keeping us posted.
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
March 18, 2010 at 12:33 pm
Thinking about this a bit.
Snapshot at 5:00 PM.
Developer fat fingers code dropping a table at 5:05 PM.
No restore, but recreate the table in original database using the data in the table from the 5:00 PM snapshot.
-- No need to delete all other snapshots in order to revert the database back. Would this work?
March 18, 2010 at 12:37 pm
good point. was not thinking that far out.
March 18, 2010 at 12:46 pm
The only other question then would be how long do you retain the snap shots? If you keep them too long, you will mostly start to experience performance issues and maybe even disk space issues as well.
March 18, 2010 at 1:16 pm
Lynn Pettis (3/18/2010)
Thinking about this a bit.Snapshot at 5:00 PM.
Developer fat fingers code dropping a table at 5:05 PM.
No restore, but recreate the table in original database using the data in the table from the 5:00 PM snapshot.
Snapshot is good only is you know which table\data is modified. What If two or more DDL\DML changes were made during time frame (5:00 and 5:05) and you want to revert only one DDL change, then you cannot use snapshot to revert, I don't think snap shot is good idea for more active OLTP database server in production.
-- No need to delete all other snapshots in order to revert the database back. Would this work?
I agree.
March 18, 2010 at 1:32 pm
GTR (3/18/2010)
Snapshot is good only is you know which table\data is modified. What If two or more DDL\DML changes were made during time frame (5:00 and 5:05) and you want to revert only one DDL change, then you cannot use snapshot to revert
UPDATE Original_Database FROM Snapshot.
No need to do a full revert, as that would also lost all legit changes since the snapshot's creation.
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
March 18, 2010 at 1:37 pm
Lynn Pettis (3/18/2010)
The only other question then would be how long do you retain the snap shots? If you keep them too long, you will mostly start to experience performance issues and maybe even disk space issues as well.
Performance issues aren't around how long you keep them, though the disk space issues are. Performance issues are around how many you have. Multiple snapshots can have really nasty effect on insert/update/delete performance because the changes to the snapshot(s) have to be hardened to disk before the transaction that made the changes can be considered complete.
A rather old article, but makes the point - http://www.sqlservercentral.com/articles/Performance+Tuning/64080/
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
March 18, 2010 at 4:57 pm
I would have a job that runs every 15 minutes and rotates the files every hour. So, I would never have more than 4 snapshots no older than an hour. This would also address the growth of the file.
March 19, 2010 at 2:33 am
I know this is probably a silly question, but why was a developer DBA fiddling around with a production database? Aren't there test databases he could have used?
March 19, 2010 at 5:07 am
Paul,
It isn't a silly question. Developers should not have that ability on Production. In fact, there should be a tight throttle on the # of people who do have production rights and most of them should be read only when they are allowed.
Question for all others, how does one update a database from a snapshot without doing a RESTORE command? I've used snapshots quite regularly and never seen an UPDATE command for a snapshot.
Unless... Are you treating it as a separate database in your query code?
Viewing 15 posts - 16 through 30 (of 35 total)
You must be logged in to reply to this topic. Login to reply