Snapshots
-
SnapshotsI've been doing a little work with Database Snapshots lately, reviewing a book that a friend is writing. So I've had to dig into how they work and double check the accuracy of the writing and it's been interesting. Today's article shows a basic view of what a database snapshot is, and there's much more to learn, hopefully in some future articles we'll publish on this site.
A snapshot is essentially a point in time view of a database. It's read-only and it can be used for reporting, separately from the database from which it was created. There are a lot of cool things about database snapshots that I liked at first glance.
But as I've dug into them and seen the limitations, restrictions, and potential performance problems, I'm not sure they're a great tool. I think someone using snapshots could very quickly get into trouble with their server. And in a way that might not be apparent during testing until you really think things through. I think a series of articles might make things more clear, but for now let me touch on just use I've seen proposed for database snapshots.
Reporting - It sounds good, grab a snapshot hourly and let people query that instead of the main database. A separate database, setup on separate disks will reduce the load on the main database, right? Well, probably not. For starters, any data pages that haven't changed since the snapshot was created are queried from the source database. That means that if you report on old information, you're still querying the old database, plus you're incurring the cost of determining if the pages have changed.
Add to that the downtime of dropping the snapshot and recreating it each hour, or whatever period you choose. It's not much downtime, but since you don't know when people are running reports, you might kick them out of the database, they might receive a "database not found" message, or even have their application blow up since the database they queried at 9:58 isn't the one they connect to at 10:02.
Even if you use snapshots for historical reports, say end of month/quarter/year reports, then you might avoid the performance problems. However you have another one: disasters. See if you depend on snapshots for historical capture of data, you might get into problems if you need to move servers or make a change to the source database, or worse, encounter a disaster. Snapshots can't be backed up, restored, or otherwise handled like any other database. So if your server crashes and you restore on a new machine, you'll still have a very interesting conversation with the CFO, explaining why the end-of-year database from last year is not longer available.
Database snapshots have some useful properties and there are places they will help, but it seems like a half-baked technology in SQL Server 2005. Maybe we'll see some enhancements in SQL Server 2008 that make them truly a "static" view of the database.
Steve Jones
-
One thing that I like about snapshots is the ability to combine then with mirroring and turn your inactive failover server into a reporting server.
As for on my production server, we've discussed taking a snapshot at month end to allow vertification of the end of month reports if need. Those are only needed for a few days though. The month end reports must be completed and verified by the 5th working day.
If there's a disaster, we'll still have the end of mnth backup, and the usual backups, all of which can be restored. the snapshot would just make vertification easier.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverabilityWe walk in the dark places no others will enter
We stand on the bridge and no one may pass -
I admit that I have not read thru the article however I did read over some info on DB Snapshots in BOL 2005. My question is can you create a DB Snapshot that is independent of the source DB when qeurying? By that I mean can I create a DB Snapshot so that when querying against it, only the DB Snapshot is accessed and not the source DB? I can see using DB Snapshots for doing end of month reporting (for example) and in that scenario I would not need for the query to check the source DB to see if any data has been updated but pull just from the DB Snapshot itself. Kind of like doing an actual backup at some point in time, restoring it and doing reporting against it. Can a DB Snapshot not be setup to work this way? I didn't see any details in BOL 2005 about any options for DB Snapshot creation.
Thanks
Ed
Kindest Regards,
Just say No to Facebook! -
No, because a snapshot is not a real copy of a database. It's a virtual copy where pages that are changed in the source database are copied to the snapshot before they're overwritten.
Data that's changed in the source since the snapsho was created is in the snapshot's file, the rest is in the source database's data file.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverabilityWe walk in the dark places no others will enter
We stand on the bridge and no one may pass - "Data that's changed in the source since the snapsho was created is in the snapshot's file, the rest is in the source database's data file"I'm a little confused. Does this mean that the snapshot will be updated each time data in the source data file is changed?
MG
"There are two ways of constructing a software design. One way is to make it so simple that there are obviously no deficiencies. And the other way is to make it so complicated that there are no obvious deficiencies."
Tony Hoare"If you think it's expensive to hire a professional to do the job, wait until you hire an amateur." Red Adair.
-
Hers is a plan.
- Prepare a snapshot
- Use that to extract historical data to permanent media.
- Off-line the extract.
In this way the snapshot acts like it should. A "freeze frame" of the database at a moment in time. You can then use an application to copy the snapshot to a database, mark that as read only, detach the copy, and move it to DVD or what have you.
OLAP is On-Line Annlyitcal Process. What is the opposite? Off-Line? I need another kind of O.
ATBCharles Kincaid
-
I'm a little confused. Does this mean that the snapshot will be updated each time data in the source data file is changed?
Not necessarily. Sorry if I confused, let me be a little clearer.
When the snapshot is created, the snapshot's datafile is mostly empty. Queries against the snapshot at this point will all read from the source database's data file
A page is changed in the source database. The old page is copied to the snapshot file, the page is then updated in the source database (copy-on-write). Queries against the snapshot will now read that page from the snapshot and the rest from the source database.
The page is changed again in the source. This time, nothing happens, as the page is already in the snapshot.
The snapshot contains the original pages of the source database only for pages that have changed since the snapshot was created. When a page in the source database is changes, the old version of the page is copied to the snapshot, if it is not already there.
Does that make any more sense?
Would anyone be interested in a article on the technical details of snapshots? (if there isn't one in the pipeline already)
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverabilityWe walk in the dark places no others will enter
We stand on the bridge and no one may pass -
GilaMonster, thank you for the explanation. I would like to read the article on the technical details.
MG
"There are two ways of constructing a software design. One way is to make it so simple that there are obviously no deficiencies. And the other way is to make it so complicated that there are no obvious deficiencies."
Tony Hoare"If you think it's expensive to hire a professional to do the job, wait until you hire an amateur." Red Adair.
-
Not sure I agree that its half baked. Yes, you need to understand its limitations, though hopefully we agree that's true of all features!
It's a very low cost (disk space, time) way to report on a static view. If a user needs the ability to do backup/restore operations on it, or to be able to use it independently of the original they can fall back on what we've always used - a parallel restore of the db in question, or replication where we just queue the changes for x hours/days so that the subscriber is static.
-
It's flaky in reporting situations because as soon as you depend on it, you've lost the ability to ensure it's there. A backup restore should be allowed because if there's an issue, it's gone.
If someone deletes it the day after it's made, you've lost the point in time. If you need to restore it elsewhere, it's can't be done. If you have an issue with the main database that requires restore, you have to delete it.
Allowing a backup of a snapshot, copying out the pages from the source and snapshot as needed, would have solved most of the issues and couldn't have taken much time to implement.
-
If you need a point-in-time copy of a database that must survive hardware failures and natural disasters and exist for several months, then just restore a backup and set it readonly.
Snapshots are not the solution in a situation like that, just like log shipping is not ideal in a situation where a 5 sec failover is required. Use clustering or mirroring instead.
For reporting off a mirror/logshipping secondary, snapshots are lovely. I'm contemplating using then when we do deployments, so that if anything goes wrong it's easy to roll back. For month end/day end verifications with a short timeframe, they're great. Beats restoring the entire database just so someone can double check the reports if there's a query.
Backing up a snapshot by copying pages from the source doesn't make sense. Rather backup your source database at the time you would have created the snapshot (month/year end) and put that somewhere safe.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverabilityWe walk in the dark places no others will enter
We stand on the bridge and no one may pass -
I have a question.. does it mean that.. if i take a backup and restore the source database else where say on the different server.. is the database snapshot still valid on the new server?
-
No, you cannot move snapshots in any way, so there's no way to get it to the new server. They can't be backed up, they can't be detached.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverabilityWe walk in the dark places no others will enter
We stand on the bridge and no one may pass -
Not being able to backup/restore a snapshot, is, IMHO, a big hole.
I submitted a connect item on it: https://connect.microsoft.com/SQLServer/feedback/details/533288/allow-backup-restore-snapshot-databases
SnapshotsViewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply