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.
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)