How database snapshots work

Reference: http://msdn.microsoft.com/zh-cn/express/ms187054.aspx

 

SQL Server 2008 books online (January 2009) How database snapshots work

Database snapshots provide read-only and static views of the source database when creating snapshots, excluding uncommitted transactions. Because the database engine resumes operation after snapshot creation, uncommitted transactions are rolled back in the newly created database snapshot (transactions in the database are not affected ).

Database snapshots are related to the source database. The database snapshot must be on the same server instance as the database. In addition, if the database is unavailable for some reason, all its database snapshots will also be unavailable.

Snapshots can be used in reports. In addition, if a user error occurs in the source database, you can restore the source database to the status when the snapshot is created. The lost data is only the data updated by the database after the snapshot is created. In addition, it is useful to create a database snapshot before making major changes to the database (for example, changing the schema or structure of the table. For more information about how to use snapshots, see typical usage of database snapshots.

Although snapshots are not necessarily required, understanding how they work will be helpful. Database snapshots run on the data page. Before you modify the source database page for the first time, copy the original page from the source database to the snapshot. This process is called "copy operation during write" . Snapshots store the original pages and keep their data records when creating snapshots. Subsequent Updates to records on the modified page will not affect the snapshot content. Repeat this process for each page for the first modification. In this way, the snapshot retains the original page of all data records modified after the snapshot is created.

To store the copied original page, the snapshot uses one or more "sparse files" . Initially, sparse files are essentially Empty files that do not contain user data and are not allocated disk space for storing user data. As more and more pages are updated in the source database, the size of files is also increasing. When a snapshot is created, sparse files occupy a small amount of disk space. However, since the database is constantly updated over time, sparse files will grow into a large file. For more information about sparse files, see learn about the sparse file size in database snapshots.

The copy operation during write. The light gray box in the snapshot relationship diagram indicates the unallocated potential space in the sparse file. When you receive the first update of the page in the source database, the database engine writes the file, the operating system allocates space to the sparse file of the snapshot, and copies the original page to this place. Then, the database engine updates the page in the source database. Describes the replication operation for such writes.

Important:
Because database snapshots are not redundant, they do not prevent disk errors or other types of damages. To protect the database, it is necessary to regularly perform backups and test the restoration plan. If you must restore the source database to the time point when the database snapshot is created, implement a backup policy that allows you to perform this operation.

Read database snapshots

For users, the database snapshot seems to remain unchanged because the read operations on the database snapshot always access the original data page, regardless of the location where the page resides.

If the page in the source database is not updated, the read operation on the snapshot will read the original page from the source database. Displays the read operations on the newly created snapshot (so its sparse file does not contain pages. This read operation only reads data from the source database.

After the page is updated, the read operation on the snapshot still accesses the original page, which is now stored in sparse files. The read operation on the snapshot used to access the Update page of the source database. This read operation reads the original page from the sparse file of the snapshot.

Impact of the update mode on the Growth of database snapshots

If your source database is too large and you are worried about disk space usage, you should replace the old snapshot with a new snapshot at some time. The ideal validity of a snapshot depends on its growth rate and the disk space that can be used for its sparse files. The disk space required by the snapshot depends on the number of different pages updated in the endogenous database during the snapshot usage. Therefore, if you update a small subset of pages that are repeatedly updated in most cases, the growth rate will decrease over time and the snapshot space will be relatively small. On the contrary, if all the original pages are finally updated at least once, the snapshot will increase to the size of the source database. If the disk is full, snapshots compete for disk space. If the disk drive is full, the operation cannot be written to all snapshots.

Note:
For information about the actual size and potential size of a snapshot, see learn about the sparse file size in a database snapshot.

Therefore, it is useful to understand the common update mode of the database when the amount of space required for scheduled snapshots is expected to be used. For some databases, the update rate may be quite stable; for example, the inventory database may update many pages every day, which is very useful for replacing old snapshots every day or every week. For other databases, the ratio of update pages may vary in the business cycle. For example, a directory database may be updated quarterly and occasionally at other times; the logical policy is to create snapshots before and after quarterly updates. In the event of a serious update error, you can restore the snapshot before the update, and the updated snapshot is used to report the writes in the next quarter.

The effect of the two relative update modes on the snapshot size is described. Update Mode A reflects the environment in which only 30% of the original pages are updated during the snapshot validity period. Update mode B reflects the environment in which 80% of the original pages are updated within the snapshot validity period.

Metadata of database snapshots

For database snapshots, the database metadata includesSource_database_idAttribute, which is stored inSYS. DatabasesDirectory View column. For more information about this attribute, see SYS. Databases (TRANSACT-SQL ).

Generally, database snapshots do not expose their own metadata, But they expose the metadata of the source database. For example, the metadata includes the data returned by the following statement:

Use   < Database_snapshot >   Select   *   From SYS. database_files

 

Where,<Database_snapshot> Is the name of the database snapshot.

The only exception is that when the source database uses a full-text search or database image, some values in the snapshot metadata are changed, so the source database is disabled on the snapshot.

See

Concept

Measure the test taker's knowledge about the sparse file size in database snapshots.
Other resources

Database Snapshot