Resuming data movement on database HADR

Rédigé par Sozezzo - - Aucun commentaire

When 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

Les commentaires sont fermés.