What’s in Enterprise only? (Database Snapshot)

If you require one or more read-only, static views of a database, which is transactionally consistent with the source database at the moment the static view was created, beginning with SQL Server 2005 you can take advantage of Database Snapshots , which is only available in Enterprise Edition (or a Developer Edition or an Evaluation Edition, both of which behave exactly like the Enterprise does. The only difference among them three is their licensing.)

There are a good number of reasons why this feature might be interesting in your case. Among them, we’ve seen it used to:

• maintain historical data for report generation,
• in conjunction with database mirroring to create snapshots off the mirror to offload reporting to that standby instance,
• to safeguard the data stored in a database against user/DBA initiated errors that could cause wiping some critical data.

If you feel you have or may have in the future the need to cover any of these scenarios or some other where database snapshot fits as the most appropriate solution, remember that it will require the Enterprise Edition.

Trying to create a snapshot of a database against a non-Enterprise SKU, will return error 1844 “Database Snapshot is not supported on %ls Edition.” (being %ls whatever edition of the product you are running.)

This post is part of the a series I decided to call “Understanding the value of the Enterprise Edition, one feature at a time” which I started in September 2011 and will grow on a weekly basis, having a new post incorporated to the family every Friday.