This article is half-done without your Comment! *** Please share your thoughts via Comment ***
Since SQL Server 2005, The Database Snapshot exists with the SQL Server. In this post, I am going to share a basic and easy note of Database Snapshot.
The Database Snapshot is not related to Snapshot Isolation, Snapshot Replication, Snapshot Backup. You can create Snapshot Database with the SQL Server Enterprise Edition. Image reference taken from msdn.
The SQL Server Database Snapshot:
We can create a Snapshot of the user database and it provides a read-only and static view of the source database.
The Database Snapshot is fully dependent on the source database and must be on the same server. If we make source database unavailable, Database Snapshot also becomes unavailable.
We can reduce the load on Source Database because the Snapshot is very good option for reporting purpose where all the reporting related queries can execute on the Snapshot instead of the actual database.
How it works?
I asked one interview question to DBA that does Snapshot update when the source database is updated.
Most of the time the answer was: Yes, Snapshot automatically update.This is a wrong answer.
Initially, Snapshot is a pointer of the source database and when we are selecting data from Snapshot, it retrieves data from the actual source database.
Whenever we update the data in the actual database, it copies that actual data page from the source database to Snapshot database.
The Snapshot uses one or more sparse file to store the original pages.
That means, You can read a new version of the data in the actual source database and you can read an old version of the data in the Snapshot database.
Now we can say, Snapshot database is a report purpose database and only for historical and old data.
A Sparse file provided by the NTFS file system that requires much less disk space than other.
If your source database frequently updates, the size of Snapshot Database also frequently increase.
Snapshots could also be used to restore the current database to the point the snapshot was taken. It would perform a very quick point in time restore of the database.
If you want updated Snapshot, Periodically bases on requirement you should drop and recreate Snapshot Database.
The Performance is reduced, due to increased I/O on the source database resulting from a copy-on-write operation to the snapshot every time a page is updated.
The Real Time Use:
One of the main use is for reporting purpose and read-only database access.
We can create multiple Snapshot databases to maintain the different version of the Actual Database.
For example, if we are working with financial systems, we can create yearly Snapshot databases and we can populate different statistical report on it.
Leave a Reply