SQL Server Database Snapshot – How it works?

25 Jul
2010

SQL Server database snapshot feature provides you to have a read-only copy of your current database. You can create a snapshot only on the server which is actual db located. In other words, you can’t create a snapshot on any other server than the server that host your actual database.

We use snapshots for solving some reporting problem and some scenarios like that. Benefits of the snapshot is not the subject of this blog entry.

If you have experience with SQL Server snapshot, did you think about this limitation? What is the design issues under this limitation?

When you create a new snapshot of a big sized database SQL Server engine creates a new snapshot database in seconds. How can be happened? It is obvious that SQL Server doesn’t copying all data from source database to the snapshot database. SQL Server allocates the required space for snapshot database and links this to database. SQL Server manages snapshot as data pages (you know that allocation units which are 8KB in memory), not as rows.

So, what is going on?

SQL Server uses original data pages for each databases until any query attempting to make some updates, before applying the changes to the data pages (which are belongs to the actual database) SQL Server copies the data page to the snapshot database. In this way SQL Server only copies required (changing any data page after snapshot creating time creates this requirement) data pages to a snapshot.

IC353003[1]

This image has gotten from MSDN article.

If you want to learn about how SQL Server Snapshot mechanism works, please read this MSDN article.

When you create a new snapshot of a big sized database SQL Server engine creates a new snapshot database in seconds. How can be happened? It is obvious that SQL Server doesn’t copying all data from source database to the snapshot database. SQL Server allocates the required space for snapshot database and links this to database. SQL Server manages snapshot as data pages (you know that allocation units which are 8KB in memory), not as rows.

So, what is going on?

SQL Server uses original data pages for each databases until any query attempting to make some updates, before applying the changes to the data pages (which are belongs to the actual database) SQL Server copies the data page to the snapshot database. In this way SQL Server only copies required (changing any data page after snapshot creating time creates this requirement) data pages to a snapshot.

IC353003[1]

This image has gotten from MSDN article.

If you want to learn about how SQL Server Snapshot mechanism works, please read this MSDN article.



Comment Form

top