Create a point-in-time database environment on the fly using MS-SQL Server

Introduction

In the growing digital world, there is an endless supply of data software in the data-driven world, which includes sophisticated new databases and applications. This data is embedded into every decision, interaction and process. The reliability and quality of data are determined by the extent to which it has undergone testing, verification, and audit toll-gates.

In a complex IT environment, developers are often faced with scenarios to freeze some of their source databases or create a read-only environment to test/verify specific feature of an application or datasets, and run multiple tests on their application without being worried too much about the source database changes. Most of these business problems are still resolved through traditional approaches, by creating a new environment (a new server with SQL server installation) or by restoring and placing a read-only copy of a database. However, this won’t work in the near future once restores become more frequent, databases get larger, and restore processes take longer.

The DTAP environment (Develop, Test, Accept and Production) might be used to handle this at the enterprise level, but there would only be one point-in-time restore of the source databases. Furthermore, unless the database state is changed to read-only, “zero-database-change” is not guaranteed in this setup.

MS-SQL Server has an elegant solution to this problem and it is called “Database Snapshot”. A snapshot represents a point-in-time reference to the original database and it doesn’t involve locking. Database Snapshots are typically useful for purposes such as testing, audits, reporting, maintaining historical dataset and preventing data against administrative error. In certain use-cases, it serves as a backup or an alternate way to rebuild individual table or lost data immediately.

In this article, we go over the Database Snapshot feature and run through the steps involved to automate the process using SQL snapshots and synonyms.

Database snapshot

Database snapshot is a view of a database at a certain point in time. The data and page state are read-only copies, made possible via a pointer file known as the sparse file, sometimes referred to as the side file. A snapshot contains the original version of the pages, along with the changes in the source since the snapshot was created.

Technical concept — How does it work

A Database snapshots is a read-only, static view of a SQL database. The database snapshot is transactionally consistent with source database, as of the moment of the snapshot’s creation. It always resides on the same server instance as its source database.

When-ever a change occurs in the source database, the data pages are stored separately in a sparse file (Snapshot database). These modified pages as shown below i.e. Page 3, 6 & 7, holds both the original pages, and the changes on source database.

During a read operation, the database snapshot always access the original pages. It first looks into the source database for original pages i.e. Page 1,2,4,5,8,9,10 which are unmodified pages, and then pulls the rest modified pages i.e. Page 3,6,7 from the snapshot database. Using sparse file, which holds the pointers to the original source of the data pages, the snapshot database could distinguish the original and modified version. It pulls only the required original pages for the read operation.

The more the changes on the source database, the faster the snapshot database grows. Hence, the growth of the snapshot is directly proportional to the transaction rates that occur on the source database. Therefore, it is always recommended to keep an eye on the snapshot database size to avoid low disk error.

One must understand that the snapshot database is not a full copy of the database. The database snapshots are directly dependent on the source databases. One could create multiple snapshots on the source database. It can only be created using T-SQL statement, and its file name accepts arbitrary file extension.

For the creation of database snapshot, please refer to the syntax below.

Create a database snapshot using transact-SQL

Tip: 
The database snapshot feature is made available in all editions, 
starting with SQL Server 2016 SP1.

Key advantages — When one could consider using Snapshot

After understanding how the snapshot works, one could think of many scenarios in your organization, where this feature would come handy saving tremendous costs.

• One could create a historical reference to the data for audit purposes, provided the creation and deletion of snapshots are well thought off beforehand to avoid low-disk contingencies.
• Snapshot helps setup a test environment on the fly to test critical datasets and applications. For instance, one could create a perfect static environment on their source databases, by creating snapshots on them and quickly run through the test routines. This would guarantee a read-only source data sources. It could also help in application deployment and release management when huge data changes are involved.
• Snapshot provides a speedy access to reports on high WRITE databases, as it could reduce blocking. Most of the database writes are contained only to source database, hence snapshot database which is a read only database, would perform much faster on read operations.
• It provides an alternate way to rebuild dropped tables or lost data. One could go back in time and restore the entire database from the snapshot. Also, handy when one wants to apply uncertain changes to the database, and still decide to revert back to the original state using point in time restore.

To every advantages, one must also pay attention to their corresponding disadvantages.

Based on the scenarios, this might or might not be a disadvantages hence, one need to understand the impact based on the application, where you would use snapshot.

• There is very little performance degradation when pages has to be read from 2 databases (resulting in 2 read operations). It does contradict to speedy access to reports, but when database deals with huge write operations, this argument is still valid.
• When the snapshot file(s) are on the same disk(s) as the source databases, it could increase disk contention, slowing down both databases.
• Index operation are not copied to snapshot database resulting in slower performance. As snapshot database is read-only, it can’t take advantage of new indexes.

Automate snapshot creation

An agnostic approach to snapshot would be to automate the creation, deletion and usage of snapshots based on project needs. Below is an example to freeze source databases and create a static database environment for testers.

In this scenario, we use SQL synonyms to easily switch between source databases. Using script, one could change the source database reference on the fly to a newly created snapshot. Using 2 simple scripts, one could initiate a static database environment for testers:

Script 1 - Initiate snapshot creation on source databases

Script 2 - Switch application database synonyms

One could make these steps even more robust by combining both scripts into one stored Procedure. Once the snapshot stored procedure is executed, your database would automatically refer to a newly created read-only snapshot database, ideal for test routines and auditing. Let’s walk through the setup, as illustrated below.

Configuration tables:

We use 2 tables to store the snapshot settings.

TABLE 1: SnapshotConfig - This table holds source databases, which would be used as input to create a read-only snapshot. Once the snapshot is created, the last active snapshot against each database would be shown as shown below. i.e. Database [KPI] has an active snapshot database, which is [KPI_SS_3101]

TABLE 2: SnapshotLog - This holds snapshot logs i.e. create, delete, switch etc …

As previously stated, one must have all SQL objects, referring to the source systems, only via SQL synonyms. The view uses synonym(s) to fetch data from the source database.

Stored procedures:

Using 3 SPs as shown below, once could CREATE, SWITCH and MANAGE snapshots.

Setup execution:

By executing the MANAGE SP, the active database(s) from the settings table would result in new database snapshot, as shown below.

It would also switch the synonym(s) to refer to the newly created snapshot.

Rollback setup:

Once the test routine is finalized, the whole setup could be rolled back. One could revert back the synonym(s) using the same SP with REVERSE option, as shown below.

As seen from the script, the SQL synonym(s) are reverted back.

If you would like to avail the automation source-code, please contact me via LinkedIn.

Conclusion

Database snapshot is a great feature to create static view of a database. It let developers to take advantage when scenarios emerges. A nice to have feature up your sleeve for developers. Finally, leaving you with this wonderful quote …

“Opportunities come to the prepared mind” -Abraham Lincoln

Buy Solution Ready SQL Code: https://baranid.gumroad.com/l/MSDatabaseSnapshot

Published By

Barani Dakshinamoorthy

Originally published at https://www.linkedin.com