SQL Server Database Snapshots -2

Mehmet Salih Deveci

6 years ago

I will continue to explain Database Snapshots in SQL Server in this article.

Read first article before this post.

SQL Server Database Snapshots -1

Now lets look at the Advantages and disadvantages of SQL Server Snapshot database.

Advantages

It is an practical method for reporting. It is a very necessary and perfect feature for people who do not need instantaneous and only those who do reporting for certain times. Because, when reporting, Stable data will be read through this feature. This is very important for reporting.
It also allows you to have Historical data to create Report.
It can be used instead of normal copy in terms of Disk space because it does not create a physical copy. Only changed data is stored in the Sparse file and original data reads from the Source database.
The source database is made very fast backup method according to normal Backup provided that it is not damaged in this way. However, if a damage occurs in the source database, the Snapshot database cannot be restored.
It is very effective against administrative errors. An error, such as an incorrectly Update and Insert script or a drop table, can be immediately restored thanks to a Snapshot restore.
It offers ready-made test database to users. It will be very easy for the user to restore if they make error.

Disadvantages

Provides Extra load for Databases that require performance. Each page that changes in the database also copies to the Database and Disk.
Snapshot is also inaccessible when the Master database migrates or the Master Database crashed because the Snapshot database fully depends to the Master database.
The Snapshot database cannot be used as an effective Backup-Restore process.
Snapshot database is Read-Only. Snapshot is taken again when you need updated data.
Snapshot and Source Database have to be on the same instance.
It is not recommended for databases that consistently have too many transactions.
it does not support Full Text Indexing.
It is available only in SQL Server Enterprise editions. This feature is not supported for other SQL Server Versions.
Snapshot of System databases such as Model, Master, tempdb is not taken.
Database Snapshot Only supports the NTFS file system, the Snapshot Sparse file cannot be created in the FAT32 file system .

Following picture that summarizes the most effective and most used database snapshot In SQL Server .

Snapshot database feature is mostly used in the SQL Server Mirroring database.