Database snapshots -- are you using them?

I'm using them to test database modifications:

http://www.simple-talk.com/sql/t-sql-programming/close-these-loopholes---testing-database-modifications/

We use them in QA and Dev to try several approaches to data modifications/settings and roll it all back to the initial state very quickly.

In addition some applications I can not disclose, need stability for reporting rapid changing data as if it was a point in time. Those reports run off the snapshot while still allowing the db to accept the new changes.

Testing a deployment that I might want to rollback.  Or testing ETL functionality that I want to rollback.  I am in love with them actually, incredibly useful.

-Creighton

These are all excellent for testing. Anybody uses the feature in prod at all?

I've used it for testing as well (it's great for reverting to a known state after running unit tests). I've also used it in production for ETL processes. Take a snapshot prior to starting the ETL, and if there is a failure, you can revert to the snapshot. However, because of the performance overhead, I don't typically use it if the ETL process is moving a large amount of data.

Hi Linchi

At my workplace we do not use them at all. Actually, I do not see any reason ( I might be wrong) to use it on production as if you want test the script or run something  you will do that on the test server. Just my two cents.

We don't use them in production either. One of the drawbacks in SQL 2K5, anytime a snapshot is dropped entire procedure cache on the whole instance is flushed. This may not be suitable on high performance systems.

http://sankarreddy.spaces.live.com/blog/cns!1F1B61765691B5CD!319.entry

I'm building a code generator for SQL Server. When the metadata is changed, the code generator will generate a script to implement just the changes, without affecting the existing data. When SQL 2005 came around, I decided to use database snapshot as a safe way to ensure that on failuer of any part of the modification script, the database can be easily brought back to its prior state.

Until I realised database snapshots are an enterprise only feature. I don't want to limit my code generator to enterprise, so I had to take it out again and use the tried ans trusted BEGIN TRAN and COMMIT or ROLLBACK instead.

Hi Hugo

Do you use snapshot in production? I work for the company where OLTP the data changed very frequently and even if you create a snapshot and try to bring back to its prior state we are about to lose the data...

We use snapshots in conjunction with datbase mirroring to populate the data warehouse during the day.  We make a snapshot off the mirror and run our SSIS packages against that.  Obviously we're all Enterprise Edition.

I'm using it in a production environment. Reporting on a mirrored database -- the secondary instance.

Not here, cannot justify Enterprise licenses

Hi Uri,

>>Do you use snapshot in production?<<

No. As I said, I wanted to use it for my schema upgrade scripts, but had to revert to using transactions because I don't want to force the cost of enterprise edition on my customers.

>>I work for the company where OLTP the data changed very frequently and even if you create a snapshot and try to bring back to its prior state we are about to lose the data...<<

Yes, of course you would. Everything that happens after making the snapshot will be lost. Heavy OLTP is not a good scenario for restoring from a database snapshot.

We use them for BI/Reporting – i.e. generating snapshots post ETL for SSRS/SSAS. Gives us the flexibility to run our ETL processes in a loop; continuously refreshing data.

We use a snapshot as the data source for our reports as the nightly ETL run often encroaches upon the following business day and that way the previous day's position can be used until the ETL is finished.

Having said that, we've had some odd issues with query results and performance against the snapshot that we're finding it very difficult to track down.

PingBack from http://www.pythian.com/news/1764/log-buffer-141-a-carnival-of-the-vanities-for-dbas

We tried to use database snapshot on the mirror db for reporting purpose, as mentioned by SR13, dropping snapshots frequently flushes the cache and also, had some difficulties trying to implement mirroring monitoring. See this http://www.mssqltips.com/tip.asp?tip=1647

I mirror 3 of my main production databases and have snapshots of various types.

First, I have some very large databases and many of the reporting services and ETL processes can have a severe impact on performance in production.  Instead, I point all these heavy processes to the snapshots and production is not affected.

Second, I have instant access to a previous state of the data without restoring a backup.  These restores can be time consuming but I can respond instantly to "lost data" issues.  Depending on the purpose of the production database, I have various types of snapshots.  On my largest, there is an hourly snapshot and 3 daily snapshots overwritten daily.  On another db I have an hourly snapshot and 3 daily snapshots going back 5 business days.  I have found them useful for many reasons and appreciate the flexibility they offer.

Snapshot has its biggest (imho) drawback, i.e. snapshot file size limitation. We did not know this until we used it for our production upgrade, we just wanted to rollback safely if our upgrade went wrong and we intended to drop the snapshot once the upgrade succeeded, however this upgrade involved huge data change (including index rebuild) and finally we got lots of weird errors, and extensive research found a blog entry in MS team said this was limitation of snapshot (restriction of spare file). After this, we never bothered to use it again.

Hi Linchi,

We use them in unit testing for databases. The ability to quickly take databases back to a known state before running each test is excellent.

Regards,

Greg

Jeff;

Can you post the referenced MS team blog entry? What is the file size limit?

Hey guys!

Don't forget about the extra I/O you get for each snapshot that you take. Whenever a page is modified for the first time in the actual database, that page has to be copied to each of the snapshots. If you have an OLTP database that takes a lot of changes, you'll be seeing a severe performance hit.

You can check this by setting custom performance counters to monitor the I/O of each snapshot database.

All the best!

Bogdan

We are using them for reporting. This save the production system from any potential slowdowns due to the complexity of the reports.

Database snapshots would enjoy more popularity if Standard Edition had them.  We can't justify the 3x expense of Enterprise Edition, and therefore only use them in dev/testing scenarios.  What a shame!

We're thinking of using snapshots as supporting evidence for reports we write to the feds.  We have a C# web application with a SQL Server 2005 back end.  Most of our colleagues in other states are still using Access databases that are backed up to CD every year.  So that's what our federal project officers expect us to use too.  I wonder if snapshots would give everyone what they need.

PingBack from http://michaeljswart.com/?p=44