Resuming data movement on database HADR
Rédigé par Sozezzo - - Aucun commentaireWhen we have un have an unhealthy data synchronization state, we can always use graphic interface to resume data movement.
This script resume when the database need it, but you must connect on secondary server to run it.
-- You can use SQLCMD Mode -- :CONNECT <MyServer> PRINT '-- SQL Script to SET HADR RESUME' PRINT '' SET NOCOUNT ON; DECLARE @sql nvarchar(MAX); DECLARE @dbname nvarchar(255); BEGIN TRY DROP TABLE #db; END TRY BEGIN CATCH END CATCH; SELECT sys.databases.[name] INTO #db FROM sys.dm_hadr_database_replica_states INNER JOIN sys.databases ON sys.dm_hadr_database_replica_states.database_id = sys.databases.database_id WHERE sys.dm_hadr_database_replica_states.synchronization_health = 0 AND sys.dm_hadr_database_replica_states.is_primary_replica = 0 AND sys.databases.state_desc = 'ONLINE' DECLARE @SqlTemplate nvarchar(MAX) = 'ALTER DATABASE [#(name)] SET HADR RESUME;' WHILE ((SELECT count(*) FROM #db)>0) BEGIN SET @sql = ''; SELECT TOP 1 @dbname = name , @sql = REPLACE(@SqlTemplate,'#(name)', name) FROM #db f ORDER BY f.name; DELETE FROM #db WHERE name = @dbname; IF NOT(@sql = '') BEGIN SET @sql = 'PRINT ''Database:'+@dbname+''';' + CHAR(13) + CHAR(10)+@sql; PRINT @sql; --EXEC (@sql); END END