Partilhar via

Configurar a escala de leitura para um grupo de disponibilidade "Always On"

Aplica-se a:SQL Server

Você pode configurar um grupo de disponibilidade Always On do SQL Server para cargas de trabalho em escala de leitura no Windows. Existem dois tipos de arquitetura para grupos de disponibilidade:

  • Uma arquitetura para alta disponibilidade que usa um gerenciador de cluster para fornecer continuidade de negócios aprimorada e que pode incluir réplicas secundárias legíveis. Para criar essa arquitetura de alta disponibilidade, consulte Criar e configurar grupos de disponibilidade no Windows.
  • Uma arquitetura que suporta apenas cargas de trabalho em escala de leitura.

Este artigo explica como criar um grupo de disponibilidade sem um gestor de cluster para cargas de leitura em escala. Essa arquitetura fornece somente capacidade de leitura. Não oferece alta disponibilidade.

Observação

Um grupo de disponibilidade com CLUSTER_TYPE = NONE pode incluir réplicas hospedadas em várias plataformas de sistema operacional. Não suporta alta disponibilidade. Para o sistema operacional Linux, consulte Configurar um grupo de disponibilidade do SQL Server para escala de leitura no Linux.

Pré-requisitos

Antes de criar o grupo de disponibilidade, você precisa:

  • Defina seu ambiente para que todos os servidores que hospedarão réplicas de disponibilidade possam se comunicar.
  • Instale o SQL Server. Consulte o guia de instalação do SQL Server para obter detalhes.

Habilite grupos de disponibilidade Always On e reinicie o mssql-server

Observação

O comando a seguir utiliza cmdlets do módulo sqlserver publicado na Galeria do PowerShell. Você pode instalar este módulo usando o Install-Module comando.

Habilite grupos de disponibilidade Always On em cada réplica que hospeda uma instância do SQL Server. Em seguida, reinicie o serviço SQL Server. Execute o seguinte comando para habilitar e reiniciar os serviços do SQL Server:

Enable-SqlAlwaysOn -ServerInstance <server\instance> -Force

Ativar uma sessão de evento AlwaysOn_health

Para ajudar com o diagnóstico de causa básica ao solucionar problemas de um grupo de disponibilidade, você pode, opcionalmente, habilitar uma sessão de eventos estendidos de grupos de disponibilidade Always On (XEvents). Para fazer isso, execute o seguinte comando em cada instância do SQL Server:

ALTER EVENT SESSION AlwaysOn_health ON SERVER WITH (STARTUP_STATE = ON);
GO

Para obter mais informações sobre esta sessão XEvents, consulte Configurar eventos estendidos para grupos de disponibilidade.

Autenticação de terminal de espelhamento de banco de dados

Para que a sincronização funcione corretamente, as réplicas envolvidas no grupo de disponibilidade em escala de leitura precisam ser autenticadas no ponto de extremidade. Os dois cenários principais que você pode usar para essa autenticação são abordados nas próximas seções.

Conta de serviço

Em um ambiente do Ative Directory em que todas as réplicas secundárias são associadas ao mesmo domínio, o SQL Server pode autenticar utilizando a conta de serviço. Você deve criar explicitamente um logon para a conta de serviço em cada instância do SQL Server:

CREATE LOGIN [<domain>\service account] FROM WINDOWS;

Autenticação de login SQL

Em ambientes onde as réplicas secundárias podem não estar associadas a um domínio do Ative Directory, você deve utilizar a autenticação SQL. O script Transact-SQL a seguir cria um login chamado dbm_login e um usuário chamado dbm_user. Substitua <password> por uma senha válida. Para criar o usuário de ponto de extremidade de espelhamento de banco de dados, execute o seguinte comando em todas as instâncias do SQL Server.

CREATE LOGIN dbm_login WITH PASSWORD = '<password>';
CREATE USER dbm_user FOR LOGIN dbm_login;

Autenticação de certificado

Caso utilize uma réplica secundária que exija autenticação SQL, use um certificado para autenticar entre os endpoints de espelhamento.

O script Transact-SQL a seguir cria uma chave mestra e um certificado. Em seguida, ele faz backup do certificado e protege o arquivo com uma chave privada. Atualize o script com senhas fortes. Execute o script na instância primária do SQL Server para criar o certificado:

CREATE MASTER KEY ENCRYPTION BY PASSWORD = '<dmk-password>';
CREATE CERTIFICATE dbm_certificate WITH SUBJECT = 'dbm';

BACKUP CERTIFICATE dbm_certificate
TO FILE = 'C:\Program Files\Microsoft SQL Server\MSSQL14.MSSQLSERVER\MSSQL\DATA\dbm_certificate.cer'
    WITH PRIVATE KEY (
        FILE = 'c:\Program Files\Microsoft SQL Server\MSSQL14.MSSQLSERVER\MSSQL\DATA\dbm_certificate.pvk',
        ENCRYPTION BY PASSWORD = '<private-key-password>'
    );

Neste ponto, sua réplica primária do SQL Server tem um certificado em c:\Program Files\Microsoft SQL Server\MSSQL14.MSSQLSERVER\MSSQL\DATA\dbm_certificate.cer e uma chave privada em c:\Program Files\Microsoft SQL Server\MSSQL14.MSSQLSERVER\MSSQL\DATA\dbm_certificate.pvk. Copie esses dois arquivos para o mesmo local em todos os servidores que hospedarão réplicas de disponibilidade.

Em cada réplica secundária, verifique se a conta de serviço da instância do SQL Server tem permissões para acessar o certificado.

Criar o certificado em servidores secundários

O script Transact-SQL a seguir cria uma chave mestra e um certificado do backup que você criou na réplica primária do SQL Server. O comando também autoriza os usuários a acessar o certificado. Atualize o script com senhas fortes. A palavra-passe de desencriptação é a mesma palavra-passe que utilizou para criar o ficheiro de .pvk num passo anterior. Para criar o certificado, execute o seguinte script em todas as réplicas secundárias:

CREATE MASTER KEY ENCRYPTION BY PASSWORD= '<dmk-password>';

CREATE CERTIFICATE dbm_certificate
    AUTHORIZATION dbm_user
    FROM FILE = 'C:\Program Files\Microsoft SQL Server\MSSQL14.MSSQLSERVER\MSSQL\DATA\dbm_certificate.cer'
    WITH PRIVATE KEY (
        FILE = 'c:\Program Files\Microsoft SQL Server\MSSQL14.MSSQLSERVER\MSSQL\DATA\dbm_certificate.pvk',
        DECRYPTION BY PASSWORD = '<private-key-password>'
    );

Criar endpoints de espelhamento de bases de dados em todas as réplicas

Os pontos de extremidade de espelhamento de banco de dados usam o protocolo TCP (Transmission Control Protocol) para enviar e receber mensagens entre as instâncias do servidor que participam de sessões de espelhamento de banco de dados ou réplicas de disponibilidade do host. O endpoint de espelhamento de base de dados ouve numa porta TCP única.

O script a seguir de Transact-SQL cria um endpoint de escuta chamado Hadr_endpoint para o grupo de disponibilidade. Inicia o ponto de extremidade e concede permissão de conexão à conta de serviço ou ao login SQL que foi criado numa etapa anterior. Antes de executar o script, substitua os valores entre < ... >. Opcionalmente, você pode incluir um endereço IP, LISTENER_IP = (0.0.0.0). O endereço IP do ouvinte deve ser um endereço IPv4. Você também pode usar 0.0.0.0.

Atualize o seguinte script de Transact-SQL para seu ambiente em todas as instâncias do SQL Server:

CREATE ENDPOINT [Hadr_endpoint]
    AS TCP (LISTENER_PORT = **<5022>**)
    FOR DATABASE_MIRRORING (
        ROLE = ALL,
        AUTHENTICATION = CERTIFICATE dbm_certificate,
        ENCRYPTION = REQUIRED ALGORITHM AES
    );
ALTER ENDPOINT [Hadr_endpoint] STATE = STARTED;
GRANT CONNECT ON ENDPOINT::[Hadr_endpoint] TO [<service account or user>];

A porta TCP no firewall deve estar aberta para a porta do ouvinte.

Para obter mais informações, consulte O ponto de extremidade de espelhamento de banco de dados (SQL Server).

Criar um grupo de disponibilidade

Crie um grupo de disponibilidade. Defina CLUSTER_TYPE = NONE. Além disso, configure cada réplica com FAILOVER_MODE = NONE. Os aplicativos cliente que executam cargas de trabalho de análise ou relatórios podem se conectar diretamente aos bancos de dados secundários. Você também pode criar uma lista de roteamento somente leitura. As conexões com a réplica primária encaminham solicitações de conexão de leitura para cada uma das réplicas secundárias da lista de roteamento de forma round-robin.

O script de Transact-SQL a seguir cria um grupo de disponibilidade chamado ag1. O script configura as réplicas do grupo de disponibilidade com SEEDING_MODE = AUTOMATIC. Essa configuração faz com que o SQL Server crie automaticamente o banco de dados em cada servidor secundário depois que ele é adicionado ao grupo de disponibilidade.

Atualize o script a seguir para seu ambiente. Substitua os valores <node1> e <node2> pelos nomes das instâncias do SQL Server que hospedam as réplicas. Substitua o valor <5022> pela porta definida para o ponto de extremidade. Execute o seguinte script Transact-SQL na réplica primária do SQL Server:

CREATE AVAILABILITY GROUP [ag1]
    WITH (CLUSTER_TYPE = NONE)
    FOR REPLICA ON
        N'<node1>' WITH (
            ENDPOINT_URL = N'tcp://<node1>:<5022>',
		    AVAILABILITY_MODE = ASYNCHRONOUS_COMMIT,
		    FAILOVER_MODE = MANUAL,
		    SEEDING_MODE = AUTOMATIC,
                    SECONDARY_ROLE (ALLOW_CONNECTIONS = ALL)
		    ),
        N'<node2>' WITH (
		    ENDPOINT_URL = N'tcp://<node2>:<5022>',
		    AVAILABILITY_MODE = ASYNCHRONOUS_COMMIT,
		    FAILOVER_MODE = MANUAL,
		    SEEDING_MODE = AUTOMATIC,
		    SECONDARY_ROLE (ALLOW_CONNECTIONS = ALL)
		    );

ALTER AVAILABILITY GROUP [ag1] GRANT CREATE ANY DATABASE;

Associar instâncias secundárias do SQL Server ao grupo de disponibilidade

O script de Transact-SQL a seguir une um servidor a um grupo de disponibilidade chamado ag1. Atualize o script para o seu ambiente. Para ingressar no grupo de disponibilidade, execute o seguinte script Transact-SQL em cada réplica secundária do SQL Server:

ALTER AVAILABILITY GROUP [ag1] JOIN WITH (CLUSTER_TYPE = NONE);

ALTER AVAILABILITY GROUP [ag1] GRANT CREATE ANY DATABASE;

Adicionar um banco de dados ao grupo de disponibilidade

Verifique se o banco de dados adicionado ao grupo de disponibilidade está no modelo de recuperação completa e tem um backup de log válido. Se o banco de dados for um banco de dados de teste ou um banco de dados recém-criado, faça um backup de banco de dados. Para criar e fazer backup de um banco de dados chamado db1, execute o seguinte script Transact-SQL na instância primária do SQL Server:

CREATE DATABASE [db1];
ALTER DATABASE [db1] SET RECOVERY FULL;
BACKUP DATABASE [db1]
   TO DISK = N'c:\Program Files\Microsoft SQL Server\MSSQL14.MSSQLSERVER\MSSQL\Backup\db1.bak';

Para adicionar um banco de dados chamado db1 a um grupo de disponibilidade chamado ag1, execute o seguinte script Transact-SQL na réplica primária do SQL Server:

ALTER AVAILABILITY GROUP [ag1] ADD DATABASE [db1];

Verifique se o banco de dados foi criado nos servidores secundários

Para ver se o banco de dados db1 foi criado e está sincronizado, execute a seguinte consulta em cada réplica secundária do SQL Server:

SELECT * FROM sys.databases WHERE name = 'db1';
GO
SELECT DB_NAME(database_id) AS 'database', synchronization_state_desc FROM sys.dm_hadr_database_replica_states;

Este grupo de disponibilidade não é uma configuração de alta disponibilidade. Se precisar de alta disponibilidade, siga as instruções em Configurar um grupo de disponibilidade Always On para SQL Server no Linux ou Criação e configuração de grupos de disponibilidade no Windows.

Conectar-se a réplicas secundárias só de leitura

Pode conectar-se a réplicas secundárias apenas de leitura de duas maneiras:

  • Os aplicativos podem se conectar diretamente à instância do SQL Server que hospeda a réplica secundária e consultar os bancos de dados. Para obter mais informações, consulte Réplicas secundárias legíveis.
  • Os aplicativos também podem usar roteamento somente leitura, que requer um ouvinte. Se estiver a implementar um cenário de leitura em escala sem um gestor de cluster, ainda pode criar um listener que aponte para o endereço IP da réplica primária atual e para a mesma porta na qual o SQL Server escuta. Você precisará recriar o ouvinte para apontar para o novo endereço IP primário após um failover. Para obter mais informações, consulte Roteamento somente leitura.

Failover da réplica principal em um grupo de disponibilidade em escala de leitura

Cada grupo de disponibilidade tem apenas uma réplica primária. A réplica primária permite leituras e gravações. Para alterar qual réplica é primária, você pode fazer failover. Em um grupo de disponibilidade típico, o gerenciador de cluster automatiza o processo de failover. Em um grupo de disponibilidade com cluster do tipo NONE, o processo de failover é manual.

Há duas maneiras de fazer failover da réplica primária em um grupo de disponibilidade com o tipo de cluster NONE:

  • Failover manual sem perda de dados
  • Failover manual forçado com perda de dados

Failover manual sem perda de dados

Use esse método quando a réplica primária estiver disponível, mas você precisará alterar temporária ou permanentemente qual instância hospeda a réplica primária. Para evitar possíveis perdas de dados, antes de emitir o failover manual, verifique se a réplica secundária de destino está atualizada.

Para fazer failover manualmente sem perda de dados:

  1. Torne a réplica atual primária e a réplica de destino secundária SYNCHRONOUS_COMMIT.

    ALTER AVAILABILITY GROUP [AGRScale] 
     MODIFY REPLICA ON N'<node2>' 
     WITH (AVAILABILITY_MODE = SYNCHRONOUS_COMMIT);

  2. Para identificar que as transações ativas estão comprometidas com a réplica primária e pelo menos uma réplica secundária síncrona, execute a seguinte consulta:

    SELECT ag.name, 
   drs.database_id, 
   drs.group_id, 
   drs.replica_id, 
   drs.synchronization_state_desc, 
   ag.sequence_number
FROM sys.dm_hadr_database_replica_states drs, sys.availability_groups ag
WHERE drs.group_id = ag.group_id;

    A réplica secundária é sincronizada quando synchronization_state_desc é SYNCHRONIZED.

  3. Atualize REQUIRED_SYNCHRONIZED_SECONDARIES_TO_COMMIT para 1.

    O script a seguir define REQUIRED_SYNCHRONIZED_SECONDARIES_TO_COMMIT como 1 em um grupo de disponibilidade chamado ag1. Antes de executar o seguinte script, substituaag1 pelo nome do seu grupo de disponibilidade:

    ALTER AVAILABILITY GROUP [AGRScale] 
     SET (REQUIRED_SYNCHRONIZED_SECONDARIES_TO_COMMIT = 1);

    Essa configuração garante que cada transação ativa seja confirmada para a réplica primária e pelo menos uma réplica secundária síncrona.

    Observação

    Essa configuração não é específica para failover e deve ser definida com base nos requisitos do ambiente.

  4. Defina a réplica primária e a(s) réplica(s) secundária(s) que não participa(m) do failover offline para se prepararem para a alteração de função:

    ALTER AVAILABILITY GROUP [AGRScale] OFFLINE

  5. Promova a réplica secundária de destino para primária.

    ALTER AVAILABILITY GROUP AGRScale FORCE_FAILOVER_ALLOW_DATA_LOSS;

  6. Atualize a função da réplica primária antiga e de outras réplicas secundárias para SECONDARY, execute o seguinte comando na instância do SQL Server que hospeda a réplica primária antiga:

    ALTER AVAILABILITY GROUP [AGRScale] 
     SET (ROLE = SECONDARY);

    Observação

    Para excluir um grupo de disponibilidade, use DROP AVAILABILITY GROUP. Para um grupo de disponibilidade criado com o tipo de cluster NONE ou EXTERNAL, execute o comando em todas as réplicas que fazem parte do grupo de disponibilidade.

  7. Retome a movimentação de dados, execute o seguinte comando para cada banco de dados no grupo de disponibilidade na instância do SQL Server que hospeda a réplica primária:

    ALTER DATABASE [db1]
     SET HADR RESUME

  8. Recrie qualquer ouvinte que você criou para fins de escala de leitura e que não esteja sob a gestão de um gestor de cluster. Se o ouvinte original apontar para o primário antigo, elimine-o e recrie-o para apontar para o novo primário.

Failover manual forçado com perda de dados

Se a réplica primária não estiver disponível e não puder ser recuperada imediatamente, será necessário forçar um failover para a réplica secundária com perda de dados. No entanto, se a réplica primária original se recuperar após o failover, ela assumirá a função principal. Para evitar que cada réplica esteja em um estado diferente, remova o primário original do grupo de disponibilidade após um failover forçado com perda de dados. Quando o primário original voltar a ficar online, remova totalmente o grupo de disponibilidade.

Para forçar uma transição manual com perda de dados da réplica primária N1 para a réplica secundária N2, siga os seguintes passos:

  1. Na réplica secundária (N2), inicie o failover de forma forçada:

    ALTER AVAILABILITY GROUP [AGRScale] FORCE_FAILOVER_ALLOW_DATA_LOSS;

  2. Na nova réplica primária (N2), remova a primária original (N1):

    ALTER AVAILABILITY GROUP [AGRScale]
REMOVE REPLICA ON N'N1';

  3. Valide se todo o tráfego da aplicação está apontado para o ouvinte e/ou para a nova réplica primária.

  4. Se o primário original (N1) ficar online, coloque imediatamente o grupo de disponibilidade AGRScale offline no primário original (N1):

    ALTER AVAILABILITY GROUP [AGRScale] OFFLINE

  5. Se houver dados ou alterações não sincronizadas, preserve esses dados por meio de backups ou outras opções de replicação de dados que atendam às suas necessidades de negócios.

  6. Em seguida, remova o grupo de disponibilidade do primário original (N1):

    DROP AVAILABILITY GROUP [AGRScale];

  7. Remova o banco de dados do grupo de disponibilidade na réplica primária original (N1):

    USE [master]
GO
DROP DATABASE [AGDBRScale]
GO

  8. (Opcional) Se desejar, agora você pode adicionar N1 novamente como uma nova réplica secundária ao grupo de disponibilidade AGRScale.

Observe que, se você estiver usando um ouvinte para se conectar, precisará recriar o ouvinte depois de executar o failover.

Próximos passos

  • Last updated on