How Database Snapshot works?
Initially while creating a database snapshot if source database has 100 pages then snapshot file (Sparse File NTFS sparse file gets created) created same as source database 100 pages, but the snapshot file size is very small even though the file size shows same size as database file.
If any update / insert happen on source database after creation of database snapshot before writing those pages to disk a pre change copy of those page is pushed into database snapshots which gives point in time view of the source database. As discussed If we update data in database (for ex: 10 pages), the copy of those 10 pages were moved into database snapshot before updating in the source database that means we have two copies of those pages 1) before update pages in database snapshot sparse file and 2) after updating pages in original database, due to this size of sparse file increased and it may affect the performance of source database and IO overhead because it needs to copied pages if there are huge transactions on the database.
If we do a select on snapshot the unchanged pages will come from source database and changed paged will read from sparse file (already we have unchanged pages in sparse) this is how it gives a static view of point in time.
Note – The database snapshot is transactionally consistent, means while creating database snapshot if there are any active transactions in source database that will roll back on the database snapshot.
Advantages
We can generate snapshot while doing any production database upgrade in downtime, because if something goes wrong we can always restore database from Database Snapshot till point in time when it was created. Restoring database from database backup will take time depends on size of the database, whereas restoring database from database snapshot can be performed quickly
We can’t query the mirrored database but we can create database snapshots on mirror server and can be used for reporting purpose.
Initially while creating a database snapshot if source database has 100 pages then snapshot file (Sparse File NTFS sparse file gets created) created same as source database 100 pages, but the snapshot file size is very small even though the file size shows same size as database file.
If any update / insert happen on source database after creation of database snapshot before writing those pages to disk a pre change copy of those page is pushed into database snapshots which gives point in time view of the source database. As discussed If we update data in database (for ex: 10 pages), the copy of those 10 pages were moved into database snapshot before updating in the source database that means we have two copies of those pages 1) before update pages in database snapshot sparse file and 2) after updating pages in original database, due to this size of sparse file increased and it may affect the performance of source database and IO overhead because it needs to copied pages if there are huge transactions on the database.
If we do a select on snapshot the unchanged pages will come from source database and changed paged will read from sparse file (already we have unchanged pages in sparse) this is how it gives a static view of point in time.
Note – The database snapshot is transactionally consistent, means while creating database snapshot if there are any active transactions in source database that will roll back on the database snapshot.
Advantages
We can generate snapshot while doing any production database upgrade in downtime, because if something goes wrong we can always restore database from Database Snapshot till point in time when it was created. Restoring database from database backup will take time depends on size of the database, whereas restoring database from database snapshot can be performed quickly
We can’t query the mirrored database but we can create database snapshots on mirror server and can be used for reporting purpose.
No comments: