Database Snapshots with Always On Availability Groups (SQL Server)
Published: May 17, 2016
Updated: May 17, 2016
Applies To: SQL Server 2016
THIS TOPIC APPLIES TO: SQL Server (starting with 2016)Azure SQL DatabaseAzure SQL Data Warehouse Parallel Data Warehouse
You can create a database snapshot on an primary or secondary database in an availability group. The replica role must be either PRIMARY or SECONDARY, not in the RESOLVING state.
We recommend that the database synchronization state be SYNCHRONIZING or SYNCHRONIZED when you create a database snapshot. However, database snapshots can be created when the database synchronization state is NOT SYNCHRONIZING.
A database snapshot on a secondary replica should continue to work if the replica is DISCONNECTED from the primary replica.
Some Always On Availability Groups conditions cause both the source database and its database snapshots to be restarted, temporarily disconnecting users. These conditions are as follows:
The primary replica changes roles, whether because the current primary replica goes off line and comes back online on the same server instance or because the availability group fails over.
The database enters the secondary role.
If the availability replica that hosts database snapshots is failed over, the database snapshots remain on the server instance where they were created. Users can continue to use the snapshots after the failover.If performance is a concern in your environment, we recommend that you create database snapshots only on secondary databases that are hosted by a secondary replica that is configured for manual failover mode. If you ever manually fail over the availability group to this secondary replica, you can create a new set of database snapshots on another secondary replica, redirect clients to the new database snapshots, and drop all of the database snapshots from the now primary databases.