Configurar um grupo de disponibilidade do SQL Server para escala de leitura no Linux

Artigo
06/28/2024

Aplica-se a: SQL Server – Linux

Este artigo explica como criar um AG (Grupo de Disponibilidade) Always On do SQL Server no Linux sem um gerenciador de clusters. Essa arquitetura fornece apenas uma escala de leitura. Ela não oferece alta disponibilidade.

Há dois tipos de arquiteturas para grupos de disponibilidade. Uma arquitetura para alta disponibilidade usa um gerenciador de clusters para fornecer uma continuidade dos negócios aprimorada. Para criar a arquitetura de alta disponibilidade, confira Configurar o grupo de disponibilidade Always On do SQL Server para alta disponibilidade no Linux.

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.

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.

No Linux, você deve criar um grupo de disponibilidade antes de adicioná-lo como um recurso de cluster para ser gerenciado pelo cluster. Este documento fornece um exemplo que cria o grupo de disponibilidade.

Atualize o nome do computador para cada host.

Cada nome de Instância do SQL Server deve:
- ter 15 caracteres ou menos.
- Ser exclusivo dentro da rede.
Para definir o nome do computador, edite /etc/hostname. O script a seguir permite que você edite /etc/hostname com vi:
```
sudo vi /etc/hostname
```
Configurar o arquivo dos hosts.

Observação

Se os nomes de host estiverem registrados com o endereço IP no servidor DNS, não será necessário executar as etapas abaixo. Valide que todos os nós que farão parte da configuração do grupo de disponibilidade possam se comunicar uns com os outros. (Um ping para o nome do host deve fornecer o endereço IP correspondente.) Além disso, verifique se o arquivo /etc/hosts não contém um registro que mapeia o endereço IP do localhost 127.0.0.1 com o nome do host do nó.

O arquivo de hosts em cada servidor contém os endereços IP e nomes de todos os servidores que farão parte do grupo de disponibilidade.

O comando a seguir retorna o endereço IP do servidor atual:
```
sudo ip addr show
```
Atualizar /etc/hosts. O script a seguir permite que você edite /etc/hosts com vi:
```
sudo vi /etc/hosts
```
A exemplo a seguir mostra /etc/hosts no node1 com adições para node1, node2 e node3. Neste exemplo, node1 refere-se ao servidor que hospeda a réplica primária e node2 e node3 refere-se aos servidores que hospedam as réplicas secundárias.
```
127.0.0.1    localhost localhost4 localhost4.localdomain4
::1          localhost localhost6 localhost6.localdomain6
10.128.18.12 node1
10.128.16.77 node2
10.128.15.33 node3
```

Instale o SQL Server

Instale o SQL Server. Os links a seguir apontam para instruções de instalação do SQL Server para diversas distribuições:

Habilitar Grupos de Disponibilidade Always On

Habilite grupos de disponibilidade Always On para cada nó que hospeda uma instância do SQL Server e reinicie mssql-server. Execute o seguinte script:

sudo /opt/mssql/bin/mssql-conf set hadr.hadrenabled 1
sudo systemctl restart mssql-server

Habilitar uma sessão do evento AlwaysOn_health

Como opção, você pode habilitar Eventos Estendidos (XE) para ajudar com o diagnóstico da causa raiz ao solucionar problemas de um grupo de disponibilidade. 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 XE, veja Configurar eventos estendidos para grupos de disponibilidade.

Criar um certificado

O serviço do SQL Server no Linux usa certificados para autenticar a comunicação 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. Conecte-se à instância primária do SQL Server. Para criar o certificado, execute o seguinte script Transact-SQL:

CREATE MASTER KEY ENCRYPTION BY PASSWORD = '<Master_Key_Password>';
CREATE CERTIFICATE dbm_certificate WITH SUBJECT = 'dbm';
BACKUP CERTIFICATE dbm_certificate
    TO FILE = '/var/opt/mssql/data/dbm_certificate.cer'
    WITH PRIVATE KEY (
        FILE = '/var/opt/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 /var/opt/mssql/data/dbm_certificate.cer e uma chave privada em var/opt/mssql/data/dbm_certificate.pvk. Copie esses dois arquivos no mesmo local em todos os servidores que hospedarão as réplicas de disponibilidade. Use o usuário mssql ou conceda permissão ao usuário mssql para acessar esses arquivos.

Por exemplo, no servidor de origem, o comando a seguir copia os arquivos para o computador de destino. Substitua os valores <node2> pelos nomes das instâncias do SQL Server que hospedarão as réplicas.

cd /var/opt/mssql/data
scp dbm_certificate.* root@<node2>:/var/opt/mssql/data/

Em cada servidor de destino, dê permissão ao usuário mssql para acessar o certificado.

cd /var/opt/mssql/data
chown mssql:mssql dbm_certificate.*

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. 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 script a seguir em todos os servidores secundários:

CREATE MASTER KEY ENCRYPTION BY PASSWORD = '<Master_Key_Password>';
CREATE CERTIFICATE dbm_certificate
    FROM FILE = '/var/opt/mssql/data/dbm_certificate.cer'
    WITH PRIVATE KEY (
        FILE = '/var/opt/mssql/data/dbm_certificate.pvk',
        DECRYPTION BY PASSWORD = '<Private_Key_Password>'
    );

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

Os pontos de extremidade de espelhamento de banco de dados usam o Protocolo de Controle de Transmissão (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 exclusivo número de porta TCP.

O script Transact-SQL a seguir cria um ponto de extremidade de escuta chamado Hadr_endpoint para o grupo de disponibilidade. Ele começa no ponto de extremidade e concede a permissão de conexão para o certificado que você criou. Antes de executar o script, substitua os valores entre < ... >. Ou é 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;

Observação

Se você usar a edição SQL Server Express em um nó para hospedar uma réplica somente de configuração, o único valor válido para ROLE será WITNESS. Execute o seguinte script na edição SQL Server Express:

CREATE ENDPOINT [Hadr_endpoint]
    AS TCP (LISTENER_PORT = 5022)
    FOR DATABASE_MIRRORING (
        ROLE = WITNESS,
        AUTHENTICATION = CERTIFICATE dbm_certificate,
        ENCRYPTION = REQUIRED ALGORITHM AES
    );

ALTER ENDPOINT [Hadr_endpoint] STATE = STARTED;

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

Importante

Para a versão do SQL Server 2017 (14.x), o único método de autenticação com suporte para o ponto de extremidade com espelhamento de banco de dados é CERTIFICATE. A opção WINDOWS não está disponível.

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

Crie o grupo de disponibilidade

Crie o grupo de disponibilidade. Defina CLUSTER_TYPE = NONE. Além disso, defina cada réplica com FAILOVER_MODE = MANUAL. Os aplicativos cliente que executam cargas de trabalho de análise ou relatório podem se conectar aos bancos de dados secundários diretamente. 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 definida 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 do SQL Server secundárias 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. Em cada réplica secundária do SQL Server, execute o seguinte script Transact-SQL para ingressar o grupo de disponibilidade:

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 do banco de dados. No SQL Server principal, execute o seguinte script Transact-SQL (T-SQL) para criar e fazer backup de um banco de dados chamado db1:

CREATE DATABASE [db1];
GO

ALTER DATABASE [db1]
SET RECOVERY FULL;
GO

BACKUP DATABASE [db1]
TO DISK = N'/var/opt/mssql/data/db1.bak';

Na réplica principal do SQL Server, execute o seguinte script T-SQL para adicionar um banco de dados chamado db1 a um grupo de disponibilidade chamado ag1:

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

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

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

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;
GO

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 Always On para SQL Server em Linux. Especificamente, crie o AG com CLUSTER_TYPE=WSFC (em Windows) ou com CLUSTER_TYPE=EXTERNAL (em Linux). Depois você pode integrar com um gerenciador de clusters usando o clustering de failover do Windows Server no Windows ou o Pacemaker no Linux.

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

Há duas maneiras de 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. Eles também podem usar roteamento somente leitura, que requer um ouvinte.

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.

Compartilhar via

Configurar um grupo de disponibilidade do SQL Server para escala de leitura no Linux

Pré-requisitos

Instale o SQL Server

Habilitar Grupos de Disponibilidade Always On

Habilitar uma sessão do evento AlwaysOn_health

Criar um certificado

Criar o certificado em servidores secundários

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

Crie o grupo de disponibilidade

Ingressar instâncias do SQL Server secundárias 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

Comentários

Recursos adicionais

Compartilhar via

Configurar um grupo de disponibilidade do SQL Server para escala de leitura no Linux

Pré-requisitos

Instale o SQL Server

Habilitar Grupos de Disponibilidade Always On

Habilitar uma sessão do evento AlwaysOn_health

Criar um certificado

Criar o certificado em servidores secundários

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

Crie o grupo de disponibilidade

Ingressar instâncias do SQL Server secundárias 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

Conteúdo relacionado

Comentários

Recursos adicionais