SQL Server: Database Snapshots


  • Available only in the Enterprise version.
  • Snapshots provide read-only, static views of a database.
  • Each snapshot capture the state of all committed data at the point of snapshot creation.
  • Snapshots must be on the same server instance as the source database.
  • Users can query the database snapshot

Why use database snapshots
  • "Freeze data at a point-in-time:" Can be used for reporting data in a particular point in time. (i.e. financial quarter)
  • If snapshot available for query in a mirrored server, it deflects read load from the production server.
  • Protect agains administrative errors:
  • Can be created just before a major change to the database. (i.e. bulk update, schema chenage). Reverting here is much faster than restoring a backup.
  • Protect against user errors:
  • In case of user errors, database can be restored to the time of snapshot creation.
  • Manage a test database: guaratee identical data at each round of testing.

Snapshots use:
  • sparce files
  • copy-on-write operations

How snapshots work
  • When you create a snapshot, no data is necessarily copied from the source database.
  • The snapshot may be thought as being the source database itself.
  • Copy from source to the snapshot occurs only as pages are modified in the source database.
  • The snapshot uses one or more sparse files.
  • Only as DMLs are issued, the original pages are copied to the snapshot before being modified, in a copy-on-write operation.
  • Subsequent updates are not copied to the snapshot.
  • Snapshots contain a catalog of changed pages: a bitmap with the list of pages in the source database that have changed since the snapshot creation.
  • When a page is copied from the source to the snapshot sparse file, its corresponding bit in the catalog of changed pages is updated from o to 1.
  • Growth rate:
    • In this way, the size of the snapshot is proportional to the intensity of database writes on different pages.
    • If most of the updates are repeated changes to a small subset of pages, the growth rate of the snapshot sparse files will be slow.
    • On the other hand, if ALL original pages receive at least one update, the snapshot will grow to the size of the source database.
  • Read operations in a snapshot always access the original data pages, either in the source database (those that have not been modified since the snapshot was created) or in the sparse files with original page data (for the pages modified after the snapshot creation).
Creating a snapshot
  • Use the CREATE DATABASE ... AS SNAPSHOT OF statement. It has to include:
  • One entry for each filename in the source database
  • The logical filename has to be equal to the logical filename in the source database
  • The physical filename indicates where the sparse file will be created.
CREATE DATABASE AdventureWorks2008R2_dbss ON
    ( name = adventureworks2008r2_Data,
            filename = 'c:\mssqldrive\e\AdventureWorks2008R2_Data.ss' )
            AS snapshot OF adventureworks2008r2;
GO

Checking snapshot files and file size:
  • During database snapshot creation, sparse files are created by using the file names in the CREATE DATABASE statement.
  • These file names are stored in sys.master_files in the physical_name column.
  • The size-on-disk of the snapshot files is listed in the size column (in 8-kb page units).
SELECT name, physical_name, type_desc, state_desc, size
    FROM sys.master_files
    WHERE DB_NAME(database_id) = 'AdventureWorks2008R2_dbss';
name physical_name type_desc state_desc size ------------------------- ------------------------------------------- --------- ---------- ------- adventureworks2008r2_Data e:\mssqldrive\e\AdventureWorks2008R2_Data.ss ROWS ONLINE 23024 FileStreamDocuments2008R2 c:\Program...\DATA\Documents2008R2 FILESTREAM ONLINE 0

  • You can also use the fn_virtualfilestats system table-valued function.
  • fn_virtualfilestats takes database_id and file_id as parameters.
SELECT database_id, FILE_ID, physical_name
    FROM sys.master_files
    WHERE DB_NAME(database_id) = 'AdventureWorks2008R2_dbss';
database_id file_id physical_name ----------- ------- ------------ 11 1 e:\mssqldrive\e\AdventureWorks2008R2_Data.ss 11 65537 c:\Program...\DATA\Documents2008R2
SELECT FROM fn_virtualfilestats(11,1);
GO
DbId FileId TimeStamp NumberReads BytesRead ... NumberWrites BytesWritten ... BytesOnDisk ...
---- ------ --------- ----------- ---------     ------------ ------------     -----------
11   1      23108022  195         1597440       163          1466368          3407872

Some restrictions:
  • You cannot backup, restore or detach a database snapshot
  • It must be in the same SQL Server instance as the source database
  • Full-text indexes are not supported
  • You cannot drop, detach or restore a source database if it has a snapshot.
  • You cannot perform structural changes (i.e. add/remove filegroup) to the source database

Dropping a snapshot
DROP DATABASE AdventureWorks2008R2_dbss;

Reverting a database from a Snapshot
Restrictions:
  • Only a single snapshot can exist against a source database before it can be reverted
  • Reverting a database rebuilds the transaction log, so no recovery can be performed aftewards
  • Source database and snapshot database must be offline
USE MASTER;
GO
RESTORE DATABASE AdventureWorks2008R2 
 FROM DATABASE_SNAPSHOT = 'AdventureWorks2008R2_dbss';
GO

Managing Snapshot
(a) Check the MyDB database files
(b) Verify rows on emp table
(c) Create snapshot of MyDB database
(d) Check created snapshot files
(e) Check the size on disk of snapshot files
(f) Insert data on emp table in the source database.
(g) Query snapshot. Emp table is empty.
(a) Check the MyDB database files
sp_helpdb myDb; 

name          fileid filename                           filegroup size    usage
------------  ------ ------------------------------------ --------- -----   --------
MyDB_primary  1      c:\Program...\data\MyDB_Prm.mdf      PRIMARY   4096 KB data only
MyDB_archlog1 2      c:\Program...\data\MyDB_archlog1.ldf NULL      1024 KB log only
MyDB_FG1_Dat1 3      c:\Program...\data\MyDB_FG1_1.ndf    MyDB_fg1  1024 KB data only
MyDB_FG1_Dat2 4      c:\Program...\data\MyDB_FG1_2.ndf    MyDB_fg1  1024 KB data only
MyDB_FG1_Dat3 5      c:\Program...\data\MyDB_FG1_3.ndf    MyDB_fg1  1024 KB data only
MyDB_FG1_Dat4 6      c:\Program...\data\MyDB_FG1_4.ndf    MyDB_fg1  1024 KB data only
MyDB_FG2_Dat1 7      c:\Program...\data\MyDB_FG2_1.ndf    MyDB_fg2  1024 KB data only

(b) Verify rows on emp table
USE MyDB;
GO
SELECT * 
    FROM emp;
empid name ----- ---- (c) Create snapshot of MyDB database
CREATE DATABASE MyDB_dbss ON
    ( name = MyDB_primary,
                filename = 'c:\mssqldrive\e\MyDB_Prm.ss' ),
    ( name = MyDB_FG1_Dat1,
                filename = 'c:\mssqldrive\e\MyDB_FG1_1.ss' ),
    ( name = MyDB_FG1_Dat2,
                filename = 'c:\mssqldrive\e\MyDB_FG1_2.ss' ),
    ( name = MyDB_FG1_Dat3,
                filename = 'c:\mssqldrive\e\MyDB_FG1_3.ss' ),
    ( name = MyDB_FG1_Dat4,
                filename = 'c:\mssqldrive\e\MyDB_FG1_4.ss' ),
    ( name = MyDB_FG2_Dat1,
                filename = 'c:\mssqldrive\e\MyDB_FG2_1.ss' )
            AS snapshot OF MyDB;
GO
(d) Check created snapshot files
SELECT name, physical_name, type_desc, state_desc, size
    FROM sys.master_files
    WHERE DB_NAME(database_id) = 'MyDB_dbss';
name physical_name type_desc state_desc size ------------- ----------------------------- --------- --------- ---- MyDB_primary c:\mssqldrive\e\MyDB_Prm.ss ROWS ONLINE 512 MyDB_FG1_Dat1 c:\mssqldrive\e\MyDB_FG1_1.ss ROWS ONLINE 128 MyDB_FG1_Dat2 c:\mssqldrive\e\MyDB_FG1_2.ss ROWS ONLINE 128 MyDB_FG1_Dat3 c:\mssqldrive\e\MyDB_FG1_3.ss ROWS ONLINE 128 MyDB_FG1_Dat4 c:\mssqldrive\e\MyDB_FG1_4.ss ROWS ONLINE 128 MyDB_FG2_Dat1 c:\mssqldrive\e\MyDB_FG2_1.ss ROWS ONLINE 128 (e) Check the size on disk of snapshot files
SELECT database_id, FILE_ID, physical_name
    FROM sys.master_files
    WHERE DB_NAME(database_id) = 'MyDB_dbss';
database_id file_id physical_name ----------- ------- ------------- 12 1 c:\mssqldrive\e\MyDB_Prm.ss 12 3 c:\mssqldrive\e\MyDB_FG1_1.ss 12 4 c:\mssqldrive\e\MyDB_FG1_2.ss 12 5 c:\mssqldrive\e\MyDB_FG1_3.ss 12 6 c:\mssqldrive\e\MyDB_FG1_4.ss 12 7 c:\mssqldrive\e\MyDB_FG2_1.ss
SELECT * 
    FROM fn_virtualfilestats(12,1);
DbId FileId TimeStamp NumberReads BytesRead ... NumberWrites BytesWritten ... BytesOnDisk ... ---- ------ --------- ----------- --------- ------------ ------------ ----------- 12 1 24067974 50 409600 3 24576 131072 (f) Insert data on emp table in the source database.
USE MyDB;
GO
DECLARE @a INT;
SELECT @a = 1;
WHILE (@a < 100) -- Insert 100 new rows
    BEGIN
    INSERT INTO emp
            VALUES (@a, 'John');
    SELECT @a = @a +1;
    END;
GO
SELECT COUNT(*) num_rows FROM emp;
num_rows ------- 99 (g) Query snapshot. Data is not there.
USE MyDB_dbss;
GO
SELECT COUNT(*) num_rows  FROM emp;
num_rows ------- 0

Reverting database MyDB from database snapshot Mydb_dbss
(a) Check num rows in emp, MyDB database.
(a) Emp table is dropped in MyDB
(b) Revert the database from MyDB_dbss
(c) At the end, Emp table will have been recovered, but without the data inserted/updated after the snapshot had been created.
(a) check table emp;
USE MyDB;
SELECT COUNT(*) num_rows 
    FROM emp;
num_rows ------- 99 (b) drop table emp
USE MyDB;
DROP TABLEemp;
(c) Revert the database snapshot. At the end, the emp table will be available, but without the data inserted after the snapshot creation.
USE MASTER;
GO
RESTORE DATABASE mydb 
 FROM database_snapshot = 'mydb_dbss';
USE MyDB;
SELECT COUNT(*) num_rows 
    FROM emp;
num_rows ------- 0





No comments:

Post a Comment