SQL Server Snapshot Application Introduction

Basically, database snapshots allow you to create a static read-only copy of your database for other purposes, such as reporting, auditing, or data recovery.

How does it work?

A database snapshot creates a database shell for your existing database. When the data page is modified, the changes are also written into the sparse file. When people obtain data, the unchanged part of the data is obtained from the original database, and the changed part is obtained from sparse files.

To clearly express this meaning, please refer to the following picture. As shown in the following figure, 90% of the data is not modified, so when a query occurs, of the data is obtained from the original database. The remaining 10-percent change data is obtained from sparse files.

Source: SQL Server 2005 books online

Sparse file and database Snapshot

When a database snapshot is created, the first snapshot is created very quickly. This is because a shell is actually created to record the modified file. Over time, files are constantly modified, and these modification pages are written into sparse files. The more files modified in your primary database, the more files are written into sparse files. Therefore, more and more disk space is used to store your master database and snapshot database, and the number of disk Input and Output times on your server is also increased.

A sparse file is written into a block with a size of 64 KB. Each group block increment can contain eight 8 data pages with a size of 8 KB. Therefore, every time there is any data change in your primary database, you will first copy the data page to the sparse file, and then write the file changes in the primary database to the sparse file. Once data pages are written into sparse files, they no longer need to be written. Because all the content on the page is protected, it is in the status when the snapshot is created.

To optimize disks and eliminate disk conflicts, creating sparse files in independent drives and arrays other than the primary database is a known action. There are two reasons:

First, when a snapshot is created, no data is written to a sparse file. All data access from snapshots is actually in the master database file. Over time, you can reduce the input/output burden by reading unmodified files from the primary file database and the modified data from sparse files on different arrays and disks.

Second, based on the variability of your database data and the number of data changes, you can reduce the bottleneck of input and output by separating reading from writing sparse files in the primary database.

Use Database snapshots

The thing you must remember here is that your query request still accesses your primary database. When an initial snapshot is created, only an empty shell is created. All data requests are completed in the master database file. As time passes and files are constantly modified, some data requests are separated from the initial database files and point to sparse files. Therefore, although it seems to be an independent database, the fundamental data is still derived from the primary database.

In view of this, you need to be sure not to try to query beyond the scope of your daily activities. In this case, you have created a snapshot, read and write operations, and recorded each person. When those records are queried, they still affect the primary database. Therefore, make sure that any new activity does not affect the primary data activity.

In addition, you need to remember which data is written into sparse files, rather than believing that all possible data is written into sparse files. Basically, when a snapshot is created, the size of the master database is the potential size of the sparse snapshot file. If the data volume in the sparse file has reached or even more than half of the database, it is better to create a complete copy of the database to replace the existing snapshot.

in conclusion, I think database snapshots are a very new feature. I also want to apply this function in all versions of SQL server2005, not just the Enterprise and Development editions. One thing we didn't discuss is that we didn't discuss how to use snapshots for database images. In fact, both the image and the original database give you the best way to take snapshots. Because the image is offline and you cannot access the data, the image or the original database provides you with the best method. Take some time to understand how snapshots are applied to your environment, and confirm that you are monitoring the impact of snapshot maintenance and data storage through snapshots.