SQL Server - Suspend & resume data movement

Compatible from SQL Server 2012

Sometimes you need to reinitialize data movement in Availability Group. You can do that by right clicking Availability Database in SSMS then clicking “Suspend Data Movement” then clicking “Resume Data Movement” it is handy when data synchronization became stuck after automatic fail-over or connection break between nodes.

 

 

 

 

 

It is not a big deal once you have few databases included in AG, but when you have dozens of database it can be time consuming task. You can use this script to do it for you. By default EXEC command are commented out only to print out sequence of commands for you.

Script source:

DECLARE @dbname NVARCHAR(512)
DECLARE @TSQL NVARCHAR(512)

DECLARE db_cursor CURSOR LOCAL FAST_FORWARD
FOR
SELECT db.database_name FROM 
	master.sys.availability_databases_cluster db
INNER JOIN 
	master.sys.availability_groups ag
ON db.group_id = ag.group_id
WHERE ag.name LIKE '' -- name of AG there


OPEN db_cursor
FETCH NEXT FROM db_cursor INTO @dbname
WHILE @@FETCH_STATUS = 0
BEGIN
	-- first suspend the synchronization
	PRINT '-- Suspending data movement for database ' + @dbname;
	SET @TSQL = 'ALTER DATABASE [' + @dbname + '] SET HADR SUSPEND'
	
	-- uncomment folowing line for direct execution
	-- EXECUTE sp_executesql @TSQL
	PRINT @TSQL

	-- then resume back
	PRINT '-- Resuming data movement for database ' + @dbname;
	SET @TSQL = 'ALTER DATABASE [' + @dbname + '] SET HADR RESUME'

	-- uncomment folowing line for direct execution
	-- EXECUTE sp_executesql @TSQL
	PRINT @TSQL

	FETCH NEXT FROM db_cursor INTO @dbname
END
CLOSE db_cursor
DEALLOCATE db_cursor

Results

Script messages after execution:

-- Suspending data movement for database AlertPortal
ALTER DATABASE [AlertPortal] SET HADR SUSPEND
-- Resuming data movement for database AlertPortal
ALTER DATABASE [AlertPortal] SET HADR RESUME
-- Suspending data movement for database Archive
ALTER DATABASE [Archive] SET HADR SUSPEND
-- Resuming data movement for database Archive
ALTER DATABASE [Archive] SET HADR RESUME
-- Suspending data movement for database AuthService
ALTER DATABASE [AuthService] SET HADR SUSPEND
-- Resuming data movement for database AuthService
ALTER DATABASE [AuthService] SET HADR RESUME
... (depends on count of Availability Databases)

Leave a Reply

Your email address will not be published. Required fields are marked *