Configurar um grupo de disponibilidade Always On distribuído

Para criar seu grupo de disponibilidade distribuída usando a propagação automática, use o seguinte código Transact-SQL:

CREATE AVAILABILITY GROUP [distributedAG]
   WITH (DISTRIBUTED)
   AVAILABILITY GROUP ON
      'ag1' WITH
      (
         LISTENER_URL = 'tcp://ag1-listener.contoso.com:5022',
         AVAILABILITY_MODE = ASYNCHRONOUS_COMMIT,
         FAILOVER_MODE = MANUAL,
         SEEDING_MODE = AUTOMATIC
      ),
      'ag2' WITH
      (
         LISTENER_URL = 'tcp://ag2-listener.contoso.com:5022',
         AVAILABILITY_MODE = ASYNCHRONOUS_COMMIT,
         FAILOVER_MODE = MANUAL,
         SEEDING_MODE = AUTOMATIC
      );
GO

Para criar seu grupo de disponibilidade distribuída usando a propagação manual, use o seguinte código Transact-SQL:

CREATE AVAILABILITY GROUP [distributedAG]
   WITH (DISTRIBUTED)
   AVAILABILITY GROUP ON
      'ag1' WITH
      (
         LISTENER_URL = 'tcp://ag1-listener.contoso.com:5022',
         AVAILABILITY_MODE = ASYNCHRONOUS_COMMIT,
         FAILOVER_MODE = MANUAL,
         SEEDING_MODE = MANUAL
      ),
      'ag2' WITH
      (
         LISTENER_URL = 'tcp://ag2-listener.contoso.com:5022',
         AVAILABILITY_MODE = ASYNCHRONOUS_COMMIT,
         FAILOVER_MODE = MANUAL,
         SEEDING_MODE = MANUAL
      );
GO

Para ingressar no seu grupo de disponibilidade distribuído usando a sementeira automática, use o seguinte código Transact-SQL:

ALTER AVAILABILITY GROUP [distributedAG]
   JOIN
   AVAILABILITY GROUP ON
      'ag1' WITH
      (
         LISTENER_URL = 'tcp://ag1-listener.contoso.com:5022',
         AVAILABILITY_MODE = ASYNCHRONOUS_COMMIT,
         FAILOVER_MODE = MANUAL,
         SEEDING_MODE = AUTOMATIC
      ),
      'ag2' WITH
      (
         LISTENER_URL = 'tcp://ag2-listener.contoso.com:5022',
         AVAILABILITY_MODE = ASYNCHRONOUS_COMMIT,
         FAILOVER_MODE = MANUAL,
         SEEDING_MODE = AUTOMATIC
      );
GO

Para ingressar no seu grupo de disponibilidade distribuída utilizando a propagação manual, use o seguinte código Transact-SQL:

ALTER AVAILABILITY GROUP [distributedAG]
   JOIN
   AVAILABILITY GROUP ON
      'ag1' WITH
      (
         LISTENER_URL = 'tcp://ag1-listener.contoso.com:5022',
         AVAILABILITY_MODE = ASYNCHRONOUS_COMMIT,
         FAILOVER_MODE = MANUAL,
         SEEDING_MODE = MANUAL
      ),
      'ag2' WITH
      (
         LISTENER_URL = 'tcp://ag2-listener.contoso.com:5022',
         AVAILABILITY_MODE = ASYNCHRONOUS_COMMIT,
         FAILOVER_MODE = MANUAL,
         SEEDING_MODE = MANUAL
      );
GO

Se a propagação manual for utilizada para criar a base de dados no encaminhador, é necessário executar um backup completo e um backup do log de transações a partir do primário global, e restaurá-los para o encaminhador utilizando a opção NORECOVERY. Por exemplo:

Para fazer backup da base de dados primária global:

BACKUP DATABASE [db1]
    TO DISK = '<full backup location>'
    WITH FORMAT;

BACKUP LOG [db1]
    TO DISK = '<log backup location>'
    WITH FORMAT;

Para restaurar para o reencaminhador:

RESTORE DATABASE [db1] FROM DISK = '<full backup location>'
    WITH NORECOVERY;

RESTORE LOG [db1] FROM DISK = '<log backup location>'
    WITH NORECOVERY;

Depois disso, execute o seguinte no forwarder:

ALTER DATABASE [db1]
    SET HADR AVAILABILITY GROUP = [distributedAG];

No SQL Server 2022 (16.x) e posterior, você pode definir a REQUIRED_SYNCHRONIZED_SECONDARIES_TO_COMMIT configuração para um grupo de disponibilidade distribuído, que foi projetado para garantir que não haja perda de dados quando um grupo de disponibilidade distribuído fizer failover. Se essa configuração estiver definida, siga as etapas nesta seção para fazer failover do seu grupo de disponibilidade distribuída. Se você não quiser usar a REQUIRED_SYNCHRONIZED_SECONDARIES_TO_COMMIT configuração, siga as instruções para fazer failover de um grupo de disponibilidade distribuído no SQL Server 2019 e versões anteriores.

Etapas para garantir que não haja perda de dados

Para garantir que não haja perda de dados, você deve primeiro configurar o grupo de disponibilidade distribuída para não oferecer suporte a nenhuma perda de dados seguindo estas etapas:

1. Para se preparar para o failover, verifique se o encaminhador e o primário global estão no modo . Caso contrário, defina-os através SYNCHRONOUS_COMMIT do ALTER AVAILABILITY GROUP.
2. Defina o grupo de disponibilidade distribuída como confirmação de sincronização, tanto no primário global como no encaminhador.
3. Aguarde até que o grupo de disponibilidade distribuída seja sincronizado.
4. Na primária global, defina a configuração do grupo REQUIRED_SYNCHRONIZED_SECONDARIES_TO_COMMIT de disponibilidade distribuída como 1 usando ALTER AVAILABILITY GROUP.
5. Verifique se todas as réplicas nos AGs locais e no grupo de disponibilidade distribuída estão íntegras e se o grupo de disponibilidade distribuída está SINCRONIZADO.
6. Na réplica primária global, defina a função de grupo de disponibilidade distribuída como SECONDARY, o que torna o grupo de disponibilidade distribuída indisponível.
7. No encaminhador (o novo primário pretendido), faça failover do grupo de disponibilidade distribuída usando ALTER AVAILABILITY GROUP com FORCE_FAILOVER_ALLOW_DATA_LOSS.
8. No novo secundário (a réplica primária global anterior), defina o grupo REQUIRED_SYNCHRONIZED_SECONDARIES_TO_COMMIT de disponibilidade distribuída como 0.
9. Opcional: Se os grupos de disponibilidade estiverem em uma distância geográfica que causa latência, altere o modo de disponibilidade para ASYNCHRONOUS_COMMIT. Isso reverte a alteração da primeira etapa, se necessário.

Exemplo de T-SQL

Esta seção fornece as etapas em um exemplo detalhado para fazer failover do grupo de disponibilidade distribuído nomeado distributedAG usando o Transact-SQL. O ambiente de exemplo tem um total de 4 nós para o grupo de disponibilidade distribuída. O grupo global de disponibilidade de host primário N1 e N2ag1, e o grupo de disponibilidade do encaminhador de host N3 e N4ag2. O grupo distributedAG de disponibilidade distribuída envia as alterações de ag1 para ag2.

1. Consulta para verificar as primárias dos grupos de disponibilidade local que formam o grupo de disponibilidade distribuído SYNCHRONOUS_COMMIT. Execute o seguinte T-SQL diretamente no encaminhador e no primário global:

   SELECT DISTINCT ag.name AS [Availability Group],
                   ar.replica_server_name AS [Replica],
                   ar.availability_mode_desc AS [Availability Mode]
   FROM sys.availability_replicas AS ar
        INNER JOIN
        sys.availability_groups AS ag
        ON ar.group_id = ag.group_id
        INNER JOIN
        sys.dm_hadr_database_replica_states AS rs
        ON ar.group_id = rs.group_id
           AND ar.replica_id = rs.replica_id
   WHERE ag.name IN ('ag1', 'ag2')
         AND rs.is_primary_replica = 1
   ORDER BY [Availability Group];
   --if needed, to set a given replica to SYNCHRONOUS for node N1, default instance. If named, change from N1 to something like N1\SQL22
   
   ALTER AVAILABILITY GROUP [testag] MODIFY REPLICA ON N'N1\SQL22' WITH (AVAILABILITY_MODE = SYNCHRONOUS_COMMIT);
   

2. Defina o grupo de disponibilidade distribuída como confirmação síncrona executando o seguinte código no primário global e no encaminhador:

    -- sets the distributed availability group to synchronous commit
    ALTER AVAILABILITY GROUP [distributedAG] MODIFY AVAILABILITY GROUP ON
    'ag1' WITH (AVAILABILITY_MODE = SYNCHRONOUS_COMMIT),
    'ag2' WITH (AVAILABILITY_MODE = SYNCHRONOUS_COMMIT);
   

   Observação

   Em um grupo de disponibilidade distribuída, o status de sincronização entre os dois grupos de disponibilidade depende do modo de disponibilidade de ambas as réplicas. Para o modo de confirmação síncrona, tanto o grupo de disponibilidade primária atual quanto o grupo de disponibilidade secundária atual devem estar no modo de disponibilidade SYNCHRONOUS_COMMIT. Por esse motivo, você deve executar esse script na réplica primária global e no encaminhador.

3. Aguarde até que o status do grupo de disponibilidade distribuída mude para SYNCHRONIZED. Execute a consulta seguinte na primária global:

   -- Run this query on the Global Primary
   
   -- Check the results to see if synchronization_state_desc is SYNCHRONIZED
   SELECT ag.name,
          drs.database_id AS [Availability Group],
          db_name(drs.database_id) AS database_name,
          drs.synchronization_state_desc,
          drs.last_hardened_lsn
   FROM sys.dm_hadr_database_replica_states AS drs
        INNER JOIN
        sys.availability_groups AS ag
        ON drs.group_id = ag.group_id
   WHERE ag.name = 'distributedAG'
   ORDER BY [Availability Group];
   

   Prossiga após o grupo de disponibilidade synchronization_state_desc estar SYNCHRONIZED.

4. Para SQL Server 2022 (16.x) e posterior, na primária global, defina REQUIRED_SYNCHRONIZED_SECONDARIES_TO_COMMIT como 1 usando o seguinte T-SQL:

   ALTER AVAILABILITY GROUP distributedAG SET (REQUIRED_SYNCHRONIZED_SECONDARIES_TO_COMMIT = 1);
   

5. Verifique se os seus grupos de disponibilidade estão íntegros em todas as réplicas, interrogando o primário global e o encaminhador:

   SELECT ag.name AS [AG Name],
          db_name(drs.database_id) AS database_name,
          ar.replica_server_name AS [replica],
          drs.synchronization_state_desc,
          drs.last_hardened_lsn
   FROM sys.dm_hadr_database_replica_states AS drs
        INNER JOIN
        sys.availability_groups AS ag
        ON drs.group_id = ag.group_id
        INNER JOIN
        sys.availability_replicas AS ar
        ON drs.replica_id = ar.replica_id
           AND drs.replica_id = ar.replica_id
   WHERE ag.name IN ('ag1', 'ag2', 'distributedAG');
   

6. Na primária global, defina a função do grupo de disponibilidade distribuída como SECONDARY. Neste ponto, o grupo de disponibilidade distribuída não está disponível. Após a conclusão desta etapa, você não poderá fazer failback até que o restante das etapas seja executado.

   ALTER AVAILABILITY GROUP distributedAG SET (ROLE = SECONDARY);
   

7. Faça failover do primário global executando a seguinte consulta no encaminhador para fazer a transição dos grupos de disponibilidade e colocar o grupo de disponibilidade distribuído online novamente:

   -- Run the following command on the forwarder, the SQL Server instance that hosts the primary replica of the secondary availability group.
   ALTER AVAILABILITY GROUP distributedAG FORCE_FAILOVER_ALLOW_DATA_LOSS;
   

   Após este passo:

   • As transições primárias globais de N1 para N3.
   • O transitário transita de N3 para N1.
   • O grupo de disponibilidade distribuída está disponível.

8. No novo encaminhador (primário global anterior, N1), limpe a propriedade REQUIRED_SYNCHRONIZED_SECONDARIES_TO_COMMIT do grupo de disponibilidade distribuída definindo-a como 0:

   ALTER AVAILABILITY GROUP distributedAG SET (REQUIRED_SYNCHRONIZED_SECONDARIES_TO_COMMIT = 0);
   

9. OPCIONAL: Se os grupos de disponibilidade estiverem em uma distância geográfica que cause latência, considere alterar o modo de disponibilidade de volta para ASYNCHRONOUS_COMMITno primário global e no encaminhador. Isso reverte a alteração feita na primeira etapa, se necessário.

    -- If applicable: sets the distributed availability group to asynchronous commit:
    ALTER AVAILABILITY GROUP distributedAG MODIFY AVAILABILITY GROUP ON
    'ag1' WITH (AVAILABILITY_MODE = ASYNCHRONOUS_COMMIT),
    'ag2' WITH (AVAILABILITY_MODE = ASYNCHRONOUS_COMMIT);
   

As instruções nesta seção se aplicam se REQUIRED_SYNCHRONIZED_SECONDARIES_TO_COMMIT não estiverem definidas para o grupo de disponibilidade distribuída, como versões anteriores ao SQL Server 2022 (16.x), pois não oferecem suporte a ele para grupos de disponibilidade distribuída.

Etapas para garantir que não haja perda de dados

Para garantir que não haja perda de dados, siga estas etapas:

1. Pare todas as transações em execução no(s) banco(s) de dados do grupo de disponibilidade primária global.
2. Verifique se o primário global e o encaminhador estão no modo SYNCHRONOUS_COMMIT. Caso contrário, defina-os através SYNCHRONOUS_COMMIT do ALTER AVAILABILITY GROUP.
3. Configure o grupo de disponibilidade distribuída para confirmação síncrona no primário global e no encaminhador.
4. Aguarde até que o grupo de disponibilidade distribuído esteja sincronizado e tenha o mesmo last_hardened_lsn por banco de dados. Aguarde até que o last_hardened_lsn por base de dados seja igual entre réplicas.
5. Na réplica primária global, defina a função de grupo de disponibilidade distribuída como SECONDARY, o que torna o grupo de disponibilidade distribuída indisponível.
6. Teste a preparação para failover verificando se os valores last_hardened_lsn são os mesmos no primário global e no encaminhador. Se eles não corresponderem, retorne à primária global e reinicie o processo desde o primeiro passo.
7. Faça failover do grupo de disponibilidade principal usando ALTER AVAILABILITY GROUP com FORCE_FAILOVER_ALLOW_DATA_LOSS.
8. Opcional: Se os grupos de disponibilidade estiverem em uma distância geográfica que causa latência, altere o modo de disponibilidade de volta para ASYNCHRONOUS_COMMIT.

Exemplo de T-SQL

Esta seção fornece as etapas em um exemplo detalhado para fazer failover do grupo de disponibilidade distribuído nomeado distributedAG usando o Transact-SQL. O ambiente de exemplo tem um total de 4 nós para o grupo de disponibilidade distribuída. O grupo global de disponibilidade de host primário N1 e N2ag1, e o grupo de disponibilidade do encaminhador de host N3 e N4ag2. O grupo distributedAG de disponibilidade distribuída envia as alterações de ag1 para ag2.

1. Para garantir que nenhum dado seja perdido, interrompa todas as transações nos bancos de dados primários globais (ou seja, bancos de dados do grupo de disponibilidade principal).

2. Consulta para verificar SYNCHRONOUS_COMMIT nas primárias dos grupos de disponibilidade locais que compõem os grupos de disponibilidade distribuídos. Executar diretamente no sistema de encaminhamento e no sistema primário global.

   SELECT ag.name AS [Availability Group],
          ar.replica_server_name AS [Replica],
          ar.availability_mode_desc AS [Availability Mode]
   FROM sys.availability_replicas AS ar
        INNER JOIN
        sys.availability_groups AS ag
        ON ar.group_id = ag.group_id
        INNER JOIN
        sys.dm_hadr_database_replica_states AS rs
        ON ar.group_id = rs.group_id
           AND ar.replica_id = rs.replica_id
   WHERE ag.name IN ('ag1', 'ag2')
         AND rs.is_primary_replica = 1
   ORDER BY [Availability Group];
   

3. Defina o grupo de disponibilidade distribuída como confirmação síncrona executando o seguinte código no primário global e no encaminhador:

    -- sets the distributed availability group to synchronous commit
    ALTER AVAILABILITY GROUP [distributedAG]
    MODIFY AVAILABILITY GROUP ON
    'ag1' WITH (AVAILABILITY_MODE = SYNCHRONOUS_COMMIT),
    'ag2' WITH (AVAILABILITY_MODE = SYNCHRONOUS_COMMIT);
   
    -- query to verify the commit state of the distributed availability group
    SELECT
     ag.name,
     ag.is_distributed,
     ar.replica_server_name [Local AG Names],
     ar.availability_mode_desc,
     ars.connected_state_desc,
     ars.role_desc,
     ars.operational_state_desc,
     ars.synchronization_health_desc
    FROM sys.availability_groups ag
    INNER JOIN sys.availability_replicas ar ON ag.group_id=ar.group_id
    LEFT JOIN sys.dm_hadr_availability_replica_states ars on ars.replica_id=ar.replica_id and ag.group_id = ars.group_id
    WHERE ag.is_distributed=1;
   

   Observação

   Em um grupo de disponibilidade distribuída, o status de sincronização entre os dois grupos de disponibilidade depende do modo de disponibilidade de ambas as réplicas. Para o modo de confirmação síncrona, tanto o grupo de disponibilidade primária atual quanto o grupo de disponibilidade secundária atual devem ter SYNCHRONOUS_COMMIT modo de disponibilidade. Por esse motivo, você deve executar esse script na réplica primária global e no encaminhador.

4. Aguarde até que o status do grupo de disponibilidade distribuído seja alterado para SYNCHRONIZED e todas as réplicas tenham a mesma last_hardened_lsn (por banco de dados). Execute a consulta seguinte tanto na primária global, que é a réplica primária do grupo de disponibilidade primária, como no reencaminhador, para verificar o synchronization_state_desc e o last_hardened_lsn.

   -- Run this query on the global primary and the forwarder
   
   -- Check the results to see if synchronization_state_desc is SYNCHRONIZED, and the last_hardened_lsn is the same per database on both the global primary and forwarder
   
   -- If not, rerun the query on both side every 5 seconds until it is the case
   
   --
   SELECT ag.name AS [AG Name],
          ar.replica_server_name AS [replica],
          db_name(drs.database_id) AS database_name,
          drs.synchronization_state_desc,
          drs.last_hardened_lsn
   FROM sys.availability_groups AS ag
        INNER JOIN
        sys.availability_replicas AS ar
        ON ag.group_id = ar.group_id
        INNER JOIN
        sys.dm_hadr_database_replica_states AS drs
        ON drs.replica_id = ar.replica_id
           AND ag.group_id = drs.group_id
   WHERE ag.name IN ('ag1', 'ag2');
   

   Prossiga depois que a synchronization_state_desc do grupo de disponibilidade for SYNCHRONIZED, e o last_hardened_lsn for o mesmo por base de dados tanto no primário global quanto no encaminhador. Se synchronization_state_desc não estiver SYNCHRONIZED ou last_hardened_lsn não for igual, execute o comando de cinco em cinco segundos até que ele mude. Para evitar a perda de dados, não prossiga até que o synchronization_state_desc = SYNCHRONIZED e last_hardened_lsn seja o mesmo por banco de dados.

5. Na primária global, defina a função do grupo de disponibilidade distribuída como SECONDARY:

   ALTER AVAILABILITY GROUP distributedAG SET (ROLE = SECONDARY);
   

   Neste ponto, o grupo de disponibilidade distribuída não está disponível.

6. Teste a prontidão para alternância de serviços. Execute novamente a consulta SELECT para verificar o last_hardened_lsn (por base de dados) tanto no primário global como no encaminhador. Verifique se o last_hardened_lsn corresponde para todos os bancos de dados entre o primário global e o encaminhador.

7. Dependendo do resultado da etapa anterior, sua próxima etapa variará.

   • Se o last_hardened_lsns corresponder à consulta SELECT, efetue a mudança do grupo de disponibilidade principal para o grupo de disponibilidade secundário. Execute o comando ALTER AVAILABILITY GROUP no encaminhador, o SQL Server que hospeda a réplica primária do grupo de disponibilidade secundário.

   • Se os last_hardened_lsnsnão corresponderem da consulta SELECT depois de algum tempo, retorne o AG distribuído ao encaminhador original (N1) executando o comando failover no primário global. Em seguida, inicie o processo novamente a partir da primeira etapa.

   -- If the last_hardened_lsns match, you can run the failover query below against the forwarder
   
   -- If the last_hardened_lsns do not match after some time, you can run the failover query against the original global primary
   ALTER AVAILABILITY GROUP distributedAG FORCE_FAILOVER_ALLOW_DATA_LOSS;
   

   Após esta etapa, o grupo de disponibilidade distribuída estará disponível.

8. OPCIONAL: Após um failover bem-sucedido, se os grupos de disponibilidade estiverem a uma distância geográfica onde se verifica latência, considere alterar o modo de disponibilidade de volta para ASYNCHRONOUS_COMMIT reverter a alteração feita anteriormente.

    -- If applicable: sets the distributed availability group to asynchronous commit:
    ALTER AVAILABILITY GROUP distributedAG MODIFY AVAILABILITY GROUP ON
    'ag1' WITH (AVAILABILITY_MODE = ASYNCHRONOUS_COMMIT),
    'ag2' WITH (AVAILABILITY_MODE = ASYNCHRONOUS_COMMIT);
   

Depois de concluir essas etapas, o grupo de disponibilidade distribuído fará failover sem qualquer perda de dados.