A brief discussion of snapshots in SQL Server

Original: A brief introduction to snapshots in SQL Server

Brief introduction

A database snapshot, as its name implies, is a view of the database at a point in time. is an attribute introduced by SQL Server in the version after 2005. Snapshot scenarios are much more, but the snapshot design was initially designed for reporting Services. For example, I need a 2011 balance sheet, which requires data to remain in the state of 0 o'clock on December 31, 2011, and this can be achieved with snapshots. Snapshots can also be combined with mirroring to achieve the purpose of read and write separation. Let's look at what a snapshot is.

What is a snapshot

A database snapshot is a read-only static view of a SQL Server database (the source database). In other words, a snapshot can be understood as a read-only database. Using snapshots, you can provide the following benefits:

• Provides a static view to service the report
• Database snapshots can be used to recover the database, which is significantly faster than backup recovery (I'll explain why)
• Combined with database mirroring to provide read and write separation
• As a backup before the test environment or data changes, such as when I want to bulk import or delete data, or provide the data to the tester before the test, make a snapshot, if there is a problem, you can take the snapshot back to the state of the snapshot when it was established

The principle of snapshots

Unlike backing up a database to replicate an entire database, a snapshot does not replicate the entire database's pages, but only the pages that have changed after the snapshot's establishment point in time. As a result, when using snapshots for database recovery, only those pages that make changes are restored to the source database, which is no doubt much faster than backup and recovery methods. This principle is shown in 1 (figure from the Secret of SQL Server 2008).

Figure 1: The Principle of mirroring

As you can see from Figure 1, the snapshot does not replicate the entire database, but only the snapshot stores the original page. As a result, it can be seen that the creation of snapshots on the source database adds an additional burden to IO. When querying the snapshot database, the data that changes after the snapshot point in time queries the data file. This concept is shown in 2 (figure from the Secret of SQL Server 2008).

Figure 2: Query distribution When querying a snapshot database

Copy on Writing and sparse files (Sparse flie) on write

As can be seen in the snapshot database files are based on sparse files (Sparse file), sparse files are an attribute of the NTFS file system. The so-called sparse file refers to a large number of 0 of the data in the file, which is of little use to us, but occupies the same amount of disk space. Therefore, NTFS optimizes this and uses the algorithm to compress the file. So when a sparse file is created, the sparse file is initially small (even empty), and the file shown in 3 is a sparse file. Although it logically accounted for 21M, the file actually accounted for 128KB of disk space.

Figure 3. A sparse file

For snapshots, in addition to the snapshot's size through the properties of the snapshot database file, it can be viewed through the DMV, as shown in 4.

Figure 4: View the snapshot database size through the DMV

When the snapshot is created, as the changes to the source database increase gradually, the sparse files grow slowly, as shown in concept 4.

Figure 5. As the source database changes more and more, the sparse files are growing

So, in general, when a sparse file grows to 30% of the size of the source database file, you should consider rebuilding the snapshot.

The sparse file is written using Microsoft's write-time replication technology (copy-on-writing), meaning that when copying an object does not actually copy the object to another location, but instead maps a pointer in the new object, pointing to the location of the original object. This way, when you perform a read operation on a new object, it points directly to the original object. When you perform a write operation on a new object, the pointer to the part of the object is directed to the new address. and modify the mapping table to the new location.

Limitations of using snapshots

There are many limitations to using snapshots, because the list is too long (refer to msdn:http://msdn.microsoft.com/zh-cn/library/ms175158.aspx#limitationsrequirements for details), I'll just summarize the main limitations.

• When you use a snapshot to recover a database, you first delete other snapshots
• A snapshot is not recorded at the point in time at which it was created without a commit data
• A snapshot is a snapshot of the entire database, not a portion of the database
• Snapshots are read-only, meaning that you cannot add any changes to the snapshot, even if you want to add an index that allows the report to run faster
• Snapshot and source databases are not available when recovering a database with a snapshot
• Snapshot and source data must be on the same instance
• The file for the snapshot database must be on an NTFS-formatted disk
• The snapshot database is set to the suspect state when the disk does not meet the growth of the snapshot
• The full-text index cannot exist on the snapshot

In fact, although the limit looks a lot, but as long as the principle of understanding the snapshot, you can naturally speculate on the limitations of the snapshot.

Creation and use of snapshots

Whether using SSMS or the command line, a snapshot can be created only through T-SQL statements. Before you create a database, you must first know that the database is distributed across several files, because snapshots need to be copy-on-writing for each file. As shown in 6.

Figure 6: First find out the file distribution of the database

Based on the database distribution in Figure 6, we create a snapshot from T-SQL, as shown in 7.

Figure 7, creating a database snapshot based on the database information in Figure 6

When the snapshot database is created successfully, you can use the snapshot database as you would with a normal database, as shown in 8.

Figure 8. The snapshot database is used just like a normal database

As you can see from the following statement, the snapshot database files and the source database files seem to be indistinguishable, just the snapshot database files are sparse files, 9.

Figure 9. Source and Snapshot databases

The deletion of the snapshot database and the deletion of the normal database are no more than two, and only the drop statement is used, as shown in 10.

Figure 10: Deleting a snapshot database

We can also use snapshots to recover the database, which is much faster than normal backup-restore, which can also present the database to testers, and when the test is finished, restore the database to its pre-test state. As shown in 11.

Figure 11: Recovering a database with a snapshot

Other factors to consider in using snapshots

1. The security settings of the snapshot database inherit the security settings of the source database. This means that users or roles that can access the source database can access the snapshot database, of course, because the snapshot database is read-only, so no role or person can modify the snapshot database.

2. We see from Figure 5 in front of the article that the snapshot continues to grow as snapshots exist for longer periods of time. Therefore, it is recommended that snapshots be recreated before the snapshot reaches the source database size of 30%.

3. Because snapshots are a drag on database performance, there is no reason to have too many snapshot databases.

Summarize

This paper briefly describes the concept, principle and use of database snapshots. Database snapshots can be used in a number of scenarios, whether for reporting or mirroring, and for recovering a database using snapshots, a snapshot would be a weapon.

This sample code is punched here

A brief discussion of snapshots in SQL Server