Database Snapshots: Creating read-only copies of a database |
 |
EXPERT RESPONSE FROM: Adam Machanic
|
|
|
|
| > |
QUESTION POSED ON: 25 July 2006
I'm interested in using SQL Server 2005's Database Snapshot feature offered by the Enterprise Edition. I want to create a read-only copy of a database at a point-in-time for reporting purposes. I've read that for unchanged data, reads are transparently redirected to the primary database and for changed data the reads hit a sparse file.
My question is related to locking. One of the primary reasons we want to have a separate database for read-only reporting is that we do not want reads to be going directly against our production database. When reading unchanged data from a snapshot, are read locks placed on the primary database? If so, are these locks the same as the locks placed if I had directly read from the primary database? If the locking is the same for unchanged data, using snapshots in my eyes may result in similar locking issues. If this is the case, the feature has lost some of its luster.
|
|
| > |
EXPERT RESPONSE
When querying a database snapshot, there are no locks taken in the source database. It is quite easy to test this in order to verify. First, create a snapshot of a large database on your system. Next, run a complex and time-consuming query against the snapshot -- the kind of query that you definitely do not want holding locks! While the query is running, you can monitor locking using the sp_lock system stored procedure. You should notice that locks are taken in the snapshot database, but not in the source database.
|
|
|
|
|
|
|
|
Search and Browse the Expert Answer Center
Search and browse more than 25,000 question and
answer pairs from more than 250 TechTarget industry experts.
|
|
|
|
|
|
|
|
|
|
|
|
