patternsqlMinor
SQL Server - Snapshot is useful in production environments?
Viewed 0 times
productionsqlsnapshotusefulserverenvironments
Problem
I'm reading about SQL Server high availability solutions and disaster recovery and among the available resources the SQL Server have snapshot feature. In theory all seems like beautiful.
I also read that a snapshot will copy a database at a point in time and you can use this to restore a database.
In this answer there is a comment (by Peter Schofield, 2013) about SQL Server snapshots not having support, and is useful in a development environment for quick rollbacks.
[...] Perhaps the biggest hindrance to adoption is that Management Studio didn't offer support[...]
[...] It sounds like an ideal use of snapshots in a dev environment just for quick script deployments and quick roll backs.[...]
I would like to know if snapshots are really useful in production environments. What are some examples of usage in production, and please include personal examples about when you've used snapshots to provide a solution on production systems.
The principal objective is provide some examples of real usage and through these examples get some useful ideas for me and for everyone who will be reading this post.
In my case I use SQL Server 2017 Enterprise Edition in production environment.
I also read that a snapshot will copy a database at a point in time and you can use this to restore a database.
In this answer there is a comment (by Peter Schofield, 2013) about SQL Server snapshots not having support, and is useful in a development environment for quick rollbacks.
[...] Perhaps the biggest hindrance to adoption is that Management Studio didn't offer support[...]
[...] It sounds like an ideal use of snapshots in a dev environment just for quick script deployments and quick roll backs.[...]
I would like to know if snapshots are really useful in production environments. What are some examples of usage in production, and please include personal examples about when you've used snapshots to provide a solution on production systems.
The principal objective is provide some examples of real usage and through these examples get some useful ideas for me and for everyone who will be reading this post.
In my case I use SQL Server 2017 Enterprise Edition in production environment.
Solution
As an opinion based question and answer, I suspect that this will get closed, but here's my two cents.
I have used and seen Database Snapshots in my production environments, although rarely.
Scenario A) We used database snapshots to provide a static image of a database for reporting (ETL to a datawarehouse) purposes. Daily a script would run at the specified time and create a snapshot for reports to use. This database was transactionally consistent as of it's date of creation so useful for that.
Scenario B) A terrible maintenance job that ran and did some analysis of a very deep table and then would start deleting appropriate entries. We created a snapshot at the start of this process to prevent any blocking from taking place. So the maintenance job was changed to get the list of "what needed work" from the snapshot, then drop the snapshot and then "do the work" on the live database.
The use case for database snapshots is niche for anything except CHECKDB. SQL Server does some lock isolation tricks in the background that make it a good choice if you need a quick and dirty fix for certain classes of operations.
I have used and seen Database Snapshots in my production environments, although rarely.
Scenario A) We used database snapshots to provide a static image of a database for reporting (ETL to a datawarehouse) purposes. Daily a script would run at the specified time and create a snapshot for reports to use. This database was transactionally consistent as of it's date of creation so useful for that.
Scenario B) A terrible maintenance job that ran and did some analysis of a very deep table and then would start deleting appropriate entries. We created a snapshot at the start of this process to prevent any blocking from taking place. So the maintenance job was changed to get the list of "what needed work" from the snapshot, then drop the snapshot and then "do the work" on the live database.
The use case for database snapshots is niche for anything except CHECKDB. SQL Server does some lock isolation tricks in the background that make it a good choice if you need a quick and dirty fix for certain classes of operations.
Context
StackExchange Database Administrators Q#253166, answer score: 6
Revisions (0)
No revisions yet.