SQL Server Database Snapshots -2

Hi

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

Read first article before this post.

 

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

Advantages

  1. 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.
  2. It also allows you to have Historical data to create Report.
  3. 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.
  4. 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.
  5. 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.
  6. It offers ready-made test database to users. It will be very easy for the user to restore if they make error.

 

 

Disadvantages

 

  1. Provides Extra load for Databases that require performance. Each page that changes in the database also copies  to the Database and Disk.
  2. Snapshot is also inaccessible when the Master database migrates or the Master Database crashed  because the Snapshot database fully depends to the Master database.
  3. The Snapshot database cannot be used as an effective Backup-Restore process.
  4. Snapshot database is Read-Only. Snapshot is taken again when you need updated data.
  5. Snapshot and Source Database have to be on the same instance.
  6. It is not recommended for databases that consistently have too many transactions.
  7. it does not support Full Text Indexing.
  8. It is available only in SQL Server Enterprise editions. This feature is not supported for other SQL Server Versions.
  9. Snapshot of System databases such as Model, Master, tempdb is not taken.
  10. 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.

 

 

Mehmet Salih Deveci

I am Founder of IT Tutorial and Certified Expert about Oracle & SQL Server database, Goldengate, Exadata Machine, Oracle Database Appliance administrator with 10+years experience. I have OCA, OCP, OCE RAC Expert Certificates I have worked 100+ Banking, Insurance, Finance, Telco and etc. clients as a Consultant, Insource or Outsource. I have done 200+ Operations in this clients such as Exadata Installation & PoC & Migration & Upgrade, Oracle & SQL Server Database Upgrade, Oracle RAC Installation, SQL Server AlwaysOn Installation, Database Migration, Disaster Recovery, Backup Restore, Performance Tuning, Periodic Healthchecks. I have done 2000+ Table replication with Goldengate or SQL Server Replication tool for DWH Databases in many clients. If you need Oracle DBA, SQL Server DBA, APPS DBA,  Exadata, Goldengate, EBS Consultancy and Training you can send my email adress mehmetsalih.deveci@outlook.com. -                                                                                                                                                                                                                                                 - Oracle DBA, SQL Server DBA, APPS DBA,  Exadata, Goldengate, EBS ve linux Danışmanlık ve Eğitim için  mehmetsalih.deveci@outlook.com a mail atabilirsiniz.