Configurar a escala de leitura para um Grupo de Disponibilidade AlwaysOn

Artigo
04/26/2023

Aplica-se a: SQL Server

É possível configurar um grupo de disponibilidade AlwaysOn do SQL Server para cargas de trabalho de escala de leitura no Windows. Há dois tipos de arquitetura para grupos de disponibilidade:

Uma arquitetura para alta disponibilidade que usa um gerenciador de clusters para oferecer uma melhor continuidade de negócios 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 dá suporte apenas a cargas de trabalho de escala de leitura.

Este artigo explica como criar um grupo de disponibilidade sem um gerenciador de clusters para cargas de trabalho de escala de leitura. Essa arquitetura fornece apenas uma escala de leitura. Ela não oferece alta disponibilidade.

Observação

Um grupo de disponibilidade com CLUSTER_TYPE = NONE pode incluir réplicas hospedadas em diferentes plataformas de sistema operacional. Ele não é compatível com alta disponibilidade. Para o sistema operacional Linux, consulte Configurar um grupo de disponibilidade do SQL Server para a escala de leitura no Linux.

Pré-requisitos

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

Definir seu ambiente para que todos os servidores que hospedam réplicas de disponibilidade possam se comunicar.
Instale o SQL Server. Consulte Instalar o SQL Server para obter detalhes.

Habilitar grupos de disponibilidade Always On e reiniciar o mssql-server

Observação

O comando a seguir utiliza cmdlets do módulo sqlserver publicado na Galeria do PowerShell. É possível instalar esse módulo usando o comando Install-Module.

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

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

Habilitar uma sessão de evento AlwaysOn_health

Para ajudar com o diagnóstico da causa raiz ao solucionar problemas de um grupo de disponibilidade, é possível opcionalmente habilitar uma sessão de eventos estendidos (XEvents) de grupos de disponibilidade Always On. Para fazê-lo, 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 essa sessão de XEvents, consulte Eventos estendidos de grupos de disponibilidade Always On.

Autenticação do ponto de extremidade de espelhamento de banco de dados

Para a sincronização funcionar corretamente, as réplicas envolvidas no grupo de disponibilidade de escala de leitura precisarão autenticar pelo 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 Active Directory em que todas as réplicas secundárias são ingressadas no mesmo domínio, o SQL Server pode autenticar usando a conta de serviço. Você precisa criar explicitamente um logon para a conta de serviço em cada instância do SQL Server:

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

Em ambientes em que as réplicas secundárias podem não ser ingressadas em um Domínio do Active Directory, é necessário utilizar a autenticação do SQL. O script Transact-SQL a seguir cria um logon denominado dbm_login e um usuário chamado dbm_user. Atualize o script com uma senha forte. 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 = '**<1Sample_Strong_Password!@#>**';
CREATE USER dbm_user FOR LOGIN dbm_login;

Autenticação de certificado

Se você utiliza uma réplica secundária que requer autenticação com a autenticação do SQL, use um certificado para autenticar entre os pontos de extremidade 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 = '**<Master_Key_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>**'
   );

Nesse momento, 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 no mesmo local em todos os servidores que hospedarão as 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 com base no backup que você criou na réplica primária do SQL Server. O comando também autoriza usuários a acessarem o certificado. Atualize o script com senhas fortes. A senha de descriptografia é a mesma senha que você usou para criar o arquivo .pvk em uma etapa anterior. Para criar o certificado, execute o seguinte script em todas as réplicas secundárias:

CREATE MASTER KEY ENCRYPTION BY PASSWORD = '**<Master_Key_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 pontos de extremidade de espelhamento de banco de dados em todas as réplicas

Os pontos de espelhamento de banco de dados usam o protocolo TCP para enviar e receber mensagens entre as instâncias de servidor que participam das sessões de espelhamento de banco de dados ou hospedam réplicas de disponibilidade. O ponto de extremidade de espelhamento de banco de dados escuta em um número da porta TCP exclusivo.

O script Transact-SQL a seguir cria um ponto de extremidade de escuta chamado Hadr_endpoint para o grupo de disponibilidade. Ele inicia o ponto de extremidade e concede permissão de conexão à conta de serviço ou ao logon do SQL criado em uma etapa anterior. Antes de executar o script, substitua os valores entre **< ... >**. Opcionalmente, é possível incluir um endereço IP, LISTENER_IP = (0.0.0.0). O endereço IP do ouvinte deve ser um endereço IPv4. Também é possível usar 0.0.0.0.

Atualize o script Transact-SQL a seguir 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, defina cada réplica com FAILOVER_MODE = NONE. Aplicativos cliente que executam cargas de trabalho de análises ou de relatórios podem conectar-se diretamente aos bancos de dados secundários. Também é possível 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 usando um mecanismo round robin.

O script 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 fará o SQL Server criar automaticamente o banco de dados em cada servidor secundário após ele ser adicionado ao grupo de disponibilidade.

Atualize o script a seguir para o seu ambiente. Substitua os valores <node1> e <node2> pelos nomes das instâncias do SQL Server que hospedam as réplicas. Substitua o <5022> valor pela porta que você definiu para o ponto de extremidade. Execute o script Transact-SQL a seguir 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;

Ingressar instâncias secundárias do SQL Server no grupo de disponibilidade

O script Transact-SQL a seguir ingressa um servidor em 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 esse 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';

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

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

Verificar 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 consulta a seguir 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;

Esse 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 AlwaysOn para o SQL Server em Linux ou Criação e configuração de grupos de disponibilidade no Windows.

Conectar-se a réplicas secundárias somente leitura

Há duas maneiras para se conectar a réplicas secundárias somente leitura:

Os aplicativos podem conectar-se diretamente à instância do SQL Server que hospeda a réplica secundária e consulta os bancos de dados. Para obter mais informações, consulte Réplicas secundárias legíveis.
Aplicativos também podem usar roteamento somente leitura, que requer um ouvinte. Se você estiver implantando um cenário de escala de leitura sem um gerenciador de cluster, ainda poderá criar um ouvinte que aponte para o endereço IP da réplica primária atual e a mesma porta que aquela em que o SQL Server escuta. Será necessário 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.

Fazer failover da réplica primária em um grupo de disponibilidade de 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 é a primária, faça failover. Em um grupo de disponibilidade típico, o gerenciador de cluster automatiza o processo de failover. Em um grupo de disponibilidade com o tipo de cluster 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 a instância que hospeda a réplica primária. Para evitar a perda potencial 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:

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

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

Para identificar que as transações ativas foram confirmadas na réplica primária e em pelo menos uma réplica secundária síncrona, execute a consulta a seguir:
```
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.
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 script a seguir, substitua ag1 pelo nome do seu grupo de disponibilidade:
```
ALTER AVAILABILITY GROUP [AGRScale] 
     SET (REQUIRED_SYNCHRONIZED_SECONDARIES_TO_COMMIT = 1);
```
Essa configuração garante que todas as transações ativas são confirmadas na réplica primária e em, pelo menos, uma réplica secundária síncrona.

Observação

Essa configuração não é específica do failover e deve ser definida com base nos requisitos do ambiente.
Defina a réplica primária e as réplicas secundárias que não participam do failover offline para se prepararem para a alteração de função:
```
ALTER AVAILABILITY GROUP [AGRScale] OFFLINE
```

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

ALTER AVAILABILITY GROUP AGRScale FORCE_FAILOVER_ALLOW_DATA_LOSS;

Atualize a função da réplica primária antiga e outras secundárias para SECONDARY e 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.
Retome a movimentação de dados, execute o seguinte comando para cada banco de dado no grupo de disponibilidade da instância de SQL Server que hospeda a réplica primária:
```
ALTER DATABASE [db1]
     SET HADR RESUME
```
Crie novamente qualquer ouvinte criado para fins de escala de leitura e que não seja gerenciado por um gerenciador de clusters. Se o ouvinte original apontar para a réplica primária antiga, descarte-o e crie-o novamente para apontar para a nova réplica primária.

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, você precisará forçar um failover para a réplica secundária com perda de dados. No entanto, se a réplica primária original for recuperada após o failover, ela assumirá a função primária. Para evitar que cada réplica esteja em um estado diferente, remova a primária original do grupo de disponibilidade após um failover forçado com perda de dados. Quando a primária original voltar a ficar online, remova-a totalmente do grupo de disponibilidade.

Para forçar um failover manual com perda de dados da réplica primária N1 para a réplica secundária N2, siga estas etapas:

Na réplica secundária (N2), inicie o failover forçado:

ALTER AVAILABILITY GROUP [AGRScale] FORCE_FAILOVER_ALLOW_DATA_LOSS;

Na nova réplica primária (N2), remova a primária original (N1):
```
ALTER AVAILABILITY GROUP [AGRScale]
REMOVE REPLICA ON N'N1';
```
Valide se todo o tráfego do aplicativo é apontado para o ouvinte e/ou para a nova réplica primária.
Se a primária original (N1) ficar online, use imediatamente o grupo de disponibilidade AGRScale offline na primária original (N1):
```
ALTER AVAILABILITY GROUP [AGRScale] OFFLINE
```
Se houver dados ou alterações não sincronizadas, preserve esses dados por meio de backups ou de outras opções de replicação de dados que atendam às suas necessidades de negócios.
Em seguida, remova o grupo de disponibilidade da primária original (N1):
```
DROP AVAILABILITY GROUP [AGRScale];
```
Remova o banco de dados do grupo de disponibilidade na réplica primária original (N1):
```
USE [master]
GO
DROP DATABASE [AGDBRScale]
GO
```
(Opcional) Se desejar, agora você poderá adicionar a N1 de volta como uma nova réplica secundária ao grupo de disponibilidade AGRScale.

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

Compartilhar via

Configurar a escala de leitura para um Grupo de Disponibilidade AlwaysOn

Pré-requisitos

Habilitar grupos de disponibilidade Always On e reiniciar o mssql-server

Habilitar uma sessão de evento AlwaysOn_health

Autenticação do ponto de extremidade de espelhamento de banco de dados

Conta de serviço

Autenticação de certificado

Criar o certificado em servidores secundários

Criar pontos de extremidade de espelhamento de banco de dados em todas as réplicas

Criar um grupo de disponibilidade

Ingressar instâncias secundárias do SQL Server no grupo de disponibilidade

Adicionar um banco de dados ao grupo de disponibilidade

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

Conectar-se a réplicas secundárias somente leitura

Fazer failover da réplica primária em um grupo de disponibilidade de escala de leitura

Failover manual sem perda de dados

Failover manual forçado com perda de dados

Próximas etapas

Comentários

Recursos adicionais

Compartilhar via

Configurar a escala de leitura para um Grupo de Disponibilidade AlwaysOn

Pré-requisitos

Habilitar grupos de disponibilidade Always On e reiniciar o mssql-server

Habilitar uma sessão de evento AlwaysOn_health

Autenticação do ponto de extremidade de espelhamento de banco de dados

Conta de serviço

Autenticação de logon do SQL

Autenticação de certificado

Criar o certificado em servidores secundários

Criar pontos de extremidade de espelhamento de banco de dados em todas as réplicas

Criar um grupo de disponibilidade

Ingressar instâncias secundárias do SQL Server no grupo de disponibilidade

Adicionar um banco de dados ao grupo de disponibilidade

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

Conectar-se a réplicas secundárias somente leitura

Fazer failover da réplica primária em um grupo de disponibilidade de escala de leitura

Failover manual sem perda de dados

Failover manual forçado com perda de dados

Próximas etapas

Comentários

Recursos adicionais