AlwaysOn: Agregar manualmente un nuevo nodo réplica usando una ruta de copia de seguridad personalizada con credenciales personalizadas

El asistente para Grupos de Disponibilidad AlwaysOn del SQL Server Management Studio cubre bien la mayoría de los escenarios comunes, pero tiene una limitación importante: no puede suplantar a un usuario personalizado para acceder a una ruta de copia de seguridad remota con credenciales no predeterminadas. Cuando el recurso compartido de copia de seguridad requiere una cuenta de dominio específica o una combinación personalizada de usuario y contraseña, es necesario realizar los pasos iniciales de copia de seguridad y restauración manualmente y luego usar el asistente en modo Solo unión.

Este procedimiento automatiza ese trabajo manual: monta la ruta de copia de seguridad con credenciales personalizadas, crea una copia de seguridad completa más dos copias de seguridad del registro de transacciones de cada base de datos protegida por AlwaysOn, y genera (o ejecuta) las instrucciones de restauración correspondientes en el nuevo nodo secundario.

Requisitos previos

xp_cmdshell debe estar habilitado en los nodos primario y secundario (utilizado para el montaje/desmontaje con net use)
La cuenta de servicio de SQL Server necesita los permisos adecuados en la ruta de destino de la copia de seguridad
Las bases de datos deben estar en modo de recuperación COMPLETO para admitir copias de seguridad de registros
Ejecute el script principal en el nodo primario y el script secundario en el nuevo nodo secundario

Paso 1: Copia de seguridad de todas las bases de datos AlwaysOn (ejecutar en el nodo primario)

El script monta la ruta de copia de seguridad remota, itera sobre todas las bases de datos protegidas actualmente por algún Grupo de Disponibilidad AlwaysOn, y genera (o ejecuta) una copia de seguridad completa más dos copias de seguridad de registros por base de datos. Establezca @runnow = 'YES' para ejecutar inmediatamente, o déjelo como 'NO' para previsualizar primero las instrucciones generadas.

-- EXECUTE THIS ON THE PRIMARY NODE

-- CONFIGURE HERE ----------------------------------------
DECLARE @destpath    NVARCHAR(255)  = 'REMOTE_PATHDIR'
DECLARE @runnow      NVARCHAR(3)    = 'NO'   -- 'YES' to execute, 'NO' to preview only
DECLARE @mount_cmd   NVARCHAR(4000) = 'net use REMOTE_PATH /user:myuser mypassword'
DECLARE @dismount_cmd NVARCHAR(4000) = 'net use REMOTE_PATH /delete'
-- -------------------------------------------------------

DECLARE @DbName    VARCHAR(40)
DECLARE @primary   VARCHAR(256)
DECLARE @cmd       NVARCHAR(4000)
DECLARE @cmd2      NVARCHAR(4000)
DECLARE @cmd3      NVARCHAR(4000)
DECLARE @msgerror  VARCHAR(80)

SELECT @primary = primary_replica FROM sys.dm_hadr_availability_group_states

-- Mount the remote path with custom credentials
IF (@runnow = 'YES')
    EXEC xp_cmdshell(@mount_cmd)

-- Iterate over all AlwaysOn-protected databases
DECLARE cursor_DBs CURSOR FOR
    SELECT name FROM master.sys.sysdatabases
    WHERE name IN (SELECT DB_NAME(database_id) FROM sys.dm_hadr_database_replica_states)

OPEN cursor_DBs
FETCH NEXT FROM cursor_DBs INTO @DbName

WHILE @@FETCH_STATUS = 0
BEGIN
    SET @cmd  = 'BACKUP DATABASE [' + @DbName + '] TO DISK = ''' + @destpath + @DbName + '_FULL'' WITH NOFORMAT, COMPRESSION, NOINIT, NAME = N''' + @DbName + '-Full'', SKIP, NOREWIND, NOUNLOAD, STATS = 10'
    SET @cmd2 = 'BACKUP LOG ['      + @DbName + '] TO DISK = ''' + @destpath + @DbName + '_LOG1'' WITH NOFORMAT, COMPRESSION, NOINIT, NAME = N''' + @DbName + '-Log1'',  SKIP, NOREWIND, NOUNLOAD, STATS = 10'
    SET @cmd3 = 'BACKUP LOG ['      + @DbName + '] TO DISK = ''' + @destpath + @DbName + '_LOG2'' WITH NOFORMAT, COMPRESSION, NOINIT, NAME = N''' + @DbName + '-Log2'',  SKIP, NOREWIND, NOUNLOAD, STATS = 10'

    BEGIN TRY
        PRINT '-- DB: ' + @DbName
        IF (@runnow = 'YES')
        BEGIN
            EXEC(@cmd);  EXEC(@cmd2);  EXEC(@cmd3)
        END
        ELSE
        BEGIN
            PRINT @cmd;  PRINT @cmd2;  PRINT @cmd3
        END
    END TRY
    BEGIN CATCH
        SET @msgerror = 'BACKUP FAILED FOR ' + @DbName + ': ' + ERROR_MESSAGE()
        PRINT @msgerror
    END CATCH

    FETCH NEXT FROM cursor_DBs INTO @DbName
END

CLOSE cursor_DBs
DEALLOCATE cursor_DBs

IF (@runnow = 'YES')
    EXEC xp_cmdshell(@dismount_cmd)

Paso 2: Restauración con NORECOVERY (ejecutar en el nuevo nodo secundario)

En el nuevo secundario, monte el mismo recurso compartido de copia de seguridad y restaure todas las bases de datos con NORECOVERY; esto las deja en estado de restauración, listas para unirse al Grupo de Disponibilidad. La estructura refleja el script primario; configure las mismas rutas y credenciales.

-- EXECUTE THIS ON THE NEW SECONDARY NODE

-- CONFIGURE HERE ----------------------------------------
DECLARE @destpath    NVARCHAR(255)  = 'REMOTE_PATHDIR'
DECLARE @runnow      NVARCHAR(3)    = 'NO'
DECLARE @mount_cmd   NVARCHAR(4000) = 'net use REMOTE_PATH /user:myuser mypassword'
DECLARE @dismount_cmd NVARCHAR(4000) = 'net use REMOTE_PATH /delete'
-- -------------------------------------------------------

DECLARE @DbName   VARCHAR(40)
DECLARE @cmd      NVARCHAR(4000)
DECLARE @cmd2     NVARCHAR(4000)
DECLARE @cmd3     NVARCHAR(4000)
DECLARE @msgerror VARCHAR(80)

IF (@runnow = 'YES')
    EXEC xp_cmdshell(@mount_cmd)

DECLARE cursor_DBs CURSOR FOR
    SELECT name FROM master.sys.sysdatabases
    WHERE name IN (SELECT DB_NAME(database_id) FROM sys.dm_hadr_database_replica_states)

OPEN cursor_DBs
FETCH NEXT FROM cursor_DBs INTO @DbName

WHILE @@FETCH_STATUS = 0
BEGIN
    SET @cmd  = 'RESTORE DATABASE [' + @DbName + '] FROM DISK = ''' + @destpath + @DbName + '_FULL'' WITH NORECOVERY, STATS = 10'
    SET @cmd2 = 'RESTORE DATABASE [' + @DbName + '] FROM DISK = ''' + @destpath + @DbName + '_LOG1'' WITH NORECOVERY, STATS = 10'
    SET @cmd3 = 'RESTORE DATABASE [' + @DbName + '] FROM DISK = ''' + @destpath + @DbName + '_LOG2'' WITH NORECOVERY, STATS = 10'

    BEGIN TRY
        PRINT '-- DB: ' + @DbName
        IF (@runnow = 'YES')
        BEGIN
            EXEC(@cmd);  EXEC(@cmd2);  EXEC(@cmd3)
        END
        ELSE
        BEGIN
            PRINT @cmd;  PRINT @cmd2;  PRINT @cmd3
        END
    END TRY
    BEGIN CATCH
        SET @msgerror = 'RESTORE FAILED FOR ' + @DbName + ': ' + ERROR_MESSAGE()
        PRINT @msgerror
    END CATCH

    FETCH NEXT FROM cursor_DBs INTO @DbName
END

CLOSE cursor_DBs
DEALLOCATE cursor_DBs

IF (@runnow = 'YES')
    EXEC xp_cmdshell(@dismount_cmd)

Paso 3: Unir el nodo mediante el asistente de SSMS

Una vez que ambos scripts se completen sin errores, todas las bases de datos del nuevo secundario estarán en estado RESTORING. Abra SSMS en el primario, inicie el asistente Agregar réplica y elija el modo Solo unión. El asistente unirá el secundario al Grupo de Disponibilidad e iniciará la sincronización sin necesidad de realizar su propio ciclo de copia de seguridad y restauración.

Verifique el estado de sincronización en el Panel de AlwaysOn o con:

SELECT
    ag.name                         AS availability_group,
    ar.replica_server_name,
    drs.synchronization_state_desc,
    drs.synchronization_health_desc,
    DB_NAME(drs.database_id)        AS database_name
FROM sys.dm_hadr_database_replica_states drs
JOIN sys.availability_replicas ar ON drs.replica_id = ar.replica_id
JOIN sys.availability_groups   ag ON ar.group_id    = ag.group_id
ORDER BY ag.name, ar.replica_server_name;