One of the fantastic features of Always On Availability Groups is that you can take one of the replicas for a database offline for planned maintenance or unplanned outage and resume it back when the maintenance is done or when the issue is resolved.
Pause and Resume are two important availability features with Always On AG. This blog will explore how to pause and resume an availability database inside SQL Server Always On AG.
Suspend an Availability Group database
You can suspend a database in the Always On availability group using SQL Server Management Studio, Transact-SQL, or PowerShell. The command must be issued on the server instance that hosts the database to be suspended or resumed.
Suspend a secondary database
Only the local secondary database becomes suspended. The synchronization state is Not Synchronizing, and data communication with the primary database is interrupted. Readable secondaries refuse new connections, but existing connections remain usable. The suspended database begins to fall behind the primary database.
Note: While a secondary database is paused, the send queue of the corresponding primary database will accumulate unsent transaction log records. The connections to the secondary replica return data when the pause was invoked.
If you have multiple secondary availability group replicas, run the script (specified below) only on the secondary replica you want to suspend data movement.
The suspend process on the secondary database can impact redundancy and failover capabilities for the primary database
Suspend a Primary database
If you suspend the primary availability group database, it stops data movement to all secondary(connected) databases. The primary replica database remains available to the client for queries. Its database continues running in the exposed mode.
Note: The suspend process on the primary database affects redundancy and failover capabilities until the primary database is resumed and synchronized with secondary databases.
T-SQL Script to SUSPEND data movement
Connect to SQL Server instance where you require to suspend data movement for SQL Server Always on Availability Group Database.
--Replace the availability group database name in the script below.
ALTER DATABASE <database_name> SET HADR SUSPEND;Use SQL Server Management Studio for suspending data movement
Connect to SQL instance in SSMS and navigate to Always On High Availability and the Availability Groups node.
In the Availability Databases node, right-click the database, and choose Suspend Data Movement. It opens the Suspend Data Movement dialog box, click OK for confirmation.
RESUME Availability Group database
You can resume the suspended data in the SQL Server Always On Availability Group. Once we resume the data movement, the secondary database receives the transactions from the primary database and turns them into the SYNCHRONIZING state.
If you have suspended the secondary database locally from the secondary replica instance, you need to resume the database locally.
If you had suspended the data movement on your primary, you should resume it as well on your primary. It resumes data movements for all secondary replicas. If you have suspended the primary database and resumed it, all secondary replicas are resumed.
T-SQL Script to RESUME data movement
Connect to required AG replica ( Primary or secondary ) and run the following script after replacing the database name
ALTER DATABASE <database_name> SET HADR RESUME;Use SQL Server Management Studio for resuming data movement
Connect to SQL instance in SSMS and navigate to Always On High Availability and the Availability Groups node.
In the Availability Databases node, right-click the database, and choose Suspend Data Movement. It opens the RESUME Data Movement dialog box, click OK for confirmation.
Recommendations
During a bottleneck, temporarily suspending one or more secondary databases may be helpful to improve the performance of the primary replica. The transaction log for that corresponding primary database cannot be truncated during the suspended state. This causes log records to accumulate on the primary database. Therefore, immediately resume (if possible) or remove (if required) any suspended secondary databases in order to reduce temporary storage of log records on the primary.