How database snapshots work in SQL Server

In SQL Server 2005, another powerful new feature is database snapshots. A database snapshot is a read-only copy of a database. It is the ing of all data in the database, and its content is determined by the time when the snapshot is executed.

These database snapshots are very valuable in terms of reports, because in the snapshot database or in the original database, no locks will be executed for any queries. Snapshots can also be used in disaster recovery because you can recover existing data to existing snapshots, alternatively, you can store individual necessary tables and data in events declared for harmful data operations.

How does a database snapshot work?

You can use the create database statement of a typical DATABASE command to generate a DATABASE snapshot. The Declaration contains an additional description of the source DATABASE snapshot. When a snapshot is created, a sparse file is generated at the same time. This file can only be used in NTFS volumes.) No disk space is allocated to it during initialization-although you may see the file size in WINDOWS resource manager, it looks the same size as the original source database file. For disks, the file size is close to zero.

The data files read during database snapshot initialization are from the source database. When the data in the source database changes, the Data Engine copies the original data from the source database to the snapshot database. This technology ensures that the snapshot database only reflects the data status when the snapshot is executed. When the SELECT command is used to publish an apsaradb for RDS snapshot, no locks are released regardless of whether the data page is located in the source database data file or in the snapshot database data file. Because no locks are released in read-only database snapshots, database snapshots are an important solution for report solutions.

A snapshot instance

Now, let's take a look at how database snapshots work in SQL Server 2005. To do this, first I need a source database as the source of the snapshot. The following script creates a source database:

USE master GO IF EXISTS(SELECT name from sysdatabases where [name] = 'SourceDatabase') DROP DATABASE SourceDatabase GO CREATE DATABASE SourceDatabaseON PRIMARY ( NAME = SourceDatabase_Data, FILENAME = 'C:SQLServerSourceDatabase_Data.mdf' ) LOG ON ( NAME = SourceDatabase_Log, FILENAME = 'C:SQLServerSourceDatabase_Log.ldf' ) GO

Note the size of the product area. I define its size as CHAR150) to emphasize the growth level of data files, so that in my next instance it will be easier to explain how snapshots work.

Now that I have a source database, I load some data to expand the size of the data file. In this way, use the above script to create a sales history table.

USE SourceDatabase GO IF OBJECT_ID('SalesHistory')>0      DROP TABLE SalesHistory GO CREATE TABLE SalesHistory (      SaleID INT IDENTITY(1,1),      Product CHAR(150),       SaleDate DATETIME,       SalePrice MONEY ) DECLARE @i INT SET @i = 1 WHILE (@i <=10000) BEGIN      INSERT INTO SalesHistory      (Product, SaleDate, SalePrice)      VALUES      ('Computer', DATEADD(mm, @i, '3/11/1919'), DATEPART(ms, GETDATE()) + (@i + 57) )       INSERT INTO SalesHistory      (Product, SaleDate, SalePrice)      VALUES      ('BigScreen', DATEADD(mm, @i, '3/11/1927'), DATEPART(ms, GETDATE()) + (@i + 13) )       INSERT INTO SalesHistory      (Product, SaleDate, SalePrice)      VALUES      ('PoolTable', DATEADD(mm, @i, '3/11/1908'), DATEPART(ms, GETDATE()) + (@i + 29) )       SET @i = @i + 1 END GO

Once you run the following script, you can manipulate the location where the database files are stored. On this instance, I put these files under the C: SQL Server folder. On my computer, when the database is initialized and created, the data file size is 1, kb, and the data size after loading is 7, kb. Since some data is already in the source database, we can create a snapshot now. Use the following script to create a database snapshot.

CREATE DATABASE SnapshotDatabase ON ( NAME = 'SourceDatabase_Data', FILENAME = 'C:SQLServerSnapshotDatabase.mdf' ) AS SNAPSHOT OF SourceDatabase

The syntax for creating a snapshot is very similar to that for creating a database. There are two major differences: The first difference is the as snapshot of SourceDatabase statement, which specifies the database on the server instance AS the source database OF the SNAPSHOT; The second difference is that, in fact, database snapshots do not generate log files. Because no data operation occurs only in a read-only database, no logs are required.

This database script creates a sparse file named SnapshotDatabase. mdf whose data file extension does not require mdf. If you operate on this file in WINDOWS Resource Manager and view its properties at the same time, you will see that the file size is the same as the size of the source database file; however, in fact, the disk size is close to zero. At this time, the database snapshot does not have its own data.

You can run the above script to re-insert 10,000 rows of data into the SourceDatabase database to the SalesHistory table. At this time, the size of my SourceDatabase database is 12,480 KB, and the size of my SourceDatabase database on the disk is now 448KB. At this time, all the changed data pages in the SourceDatabase database have been copied to the SnapshotDatabase database, which can explain why the disk size has increased.

Further consideration

Database snapshots allow you to create read-only databases for reports. If necessary, you can restore your source database to your database snapshots. Similarly, you can create any number of database snapshots for your report purpose.

Note that these database snapshots occupy disk space. If there are too many database snapshots, it will soon fill up your disk array, especially in a product environment, if data is often updated, it is easier to fill up the disk array.

In addition, the use of database snapshots reduces the performance of the database, because in the database, copying data pages as write operations increases the input/output of the database.

Despite these disadvantages, if you can propose a good database snapshot creation solution for the report, more users will use this new feature of SQL Server 2005.