Partilhar via

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

Aplica-se a:SQL Server em Linux

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

Existem dois tipos de arquiteturas para grupos de disponibilidade. Uma arquitetura de alta disponibilidade para utiliza um gestor de cluster para fornecer uma continuidade de negócios aprimorada. Para criar a arquitetura de alta disponibilidade, consulte Configurar o SQL Server Always On Availability Group para alta disponibilidade no Linux.

Um grupo de disponibilidade com CLUSTER_TYPE = NONE pode incluir réplicas hospedadas em diferentes plataformas de sistema operacional. Não suporta alta disponibilidade.

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.

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

  1. Atualize o nome do computador para cada host.

    Cada nome de instância do SQL Server deve ser:

    • 15 caracteres ou menos.
    • Único 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

  2. Configure o ficheiro hosts.

    Observação

    Se os nomes de host estiverem registrados com seu endereço IP no servidor DNS, você não precisará executar as etapas a seguir. Valide se todos os nós destinados a fazer parte da configuração do grupo de disponibilidade podem se comunicar entre si. (Um ping para o nome do host deve responder com o endereço IP correspondente.) Além disso, assegure-se de que o arquivo /etc/hosts não contenha um registo que associe o endereço IP do localhost 127.0.0.1 ao hostname do nó.

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

    O comando a seguir retorna o endereço IP do servidor atual:

    sudo ip addr show

    Atualização /etc/hosts. O script a seguir permite que você edite /etc/hosts com vi:

    sudo vi /etc/hosts

    O exemplo a seguir mostra /etc/hosts em node1 com adições para node1, node2e node3. Neste exemplo, node1 refere-se ao servidor que hospeda a réplica primária e node2 e node3 referem-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

Instalar o SQL Server

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

Ativar grupos de disponibilidade Always On

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

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

Ativar uma sessão de evento AlwaysOn_health

Opcionalmente, você pode habilitar os Eventos Estendidos (XE) para ajudar com o diagnóstico de causa básica 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 esta sessão XE, consulte Configurar eventos estendidos para grupos de disponibilidade.

Criar um certificado

O serviço SQL Server no Linux usa certificados para autenticar a comunicação 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. 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>'
);

Neste ponto, 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 para o mesmo local em todos os servidores que hospedarão réplicas de disponibilidade. Use o usuário mssql ou dê permissão ao usuário mssql para acessar esses arquivos.

Por exemplo, no servidor de origem, o comando a seguir copia os arquivos para a máquina 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 do backup que você criou na réplica primária do SQL Server. Atualize o script com senhas fortes. A palavra-passe de desencriptação é a mesma palavra-passe que usou para criar o ficheiro .pvk num passo anterior. Para criar o certificado, execute o seguinte script 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>'
);

No exemplo anterior, substitua <private-key-password> pela mesma senha usada ao criar o certificado na réplica primária.

Criar os endpoints de espelhamento da base de dados em todas as réplicas

Os endpoints de espelhamento de bases de dados utilizam o protocolo TCP (Transmission Control Protocol) para enviar e receber mensagens entre as instâncias do servidor que participam nas sessões de espelhamento de bases de dados ou nas réplicas de disponibilidade do host. O ponto de extremidade de espelhamento da base de dados escuta num número de porta TCP único.

O script de Transact-SQL a seguir cria um ponto de extremidade de escuta chamado Hadr_endpoint para o grupo de disponibilidade. Ele inicia o endpoint e dá permissão de conexão ao certificado que criaste. 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;

Observação

Se você usar o SQL Server Express edition 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 no SQL Server Express edition:

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

O único método de autenticação suportado para o endpoint de espelhamento de base 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).

Criar o grupo de disponibilidade

Crie o AG. Defina CLUSTER_TYPE = NONE. Além disso, configure cada réplica com FAILOVER_MODE = MANUAL. 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 redirecionam as 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 Transact-SQL a seguir cria um AG chamado ag1. O script configura as réplicas AG 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 AG. 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 AG

O script Transact-SQL a seguir une um servidor a um AG 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 no AG:

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. No SQL Server primário, 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 primária 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];

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

Em cada réplica secundária do SQL Server, execute a seguinte consulta 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

Este AG não é uma configuração de alta disponibilidade. Se você precisar de alta disponibilidade, siga as instruções em Configurar SQL Server Always On Availability Group para alta disponibilidade no Linux. Especificamente, crie o AG com CLUSTER_TYPE=WSFC (no Windows) ou CLUSTER_TYPE=EXTERNAL (no Linux). Em seguida, você pode integrar com um gerenciador de cluster, usando o cluster de failover do Windows Server no Windows ou o Pacemaker no Linux.

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

Existem duas maneiras de conectar a réplicas secundárias de leitura somente. Os aplicativos podem se conectar diretamente à instância do SQL Server que hospeda a réplica secundária e consultar os bancos de dados. Eles também podem usar o roteamento de leitura única, que requer um listener.

Falha da(s) réplica(s) principal(is) de 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 tipo NONE, o processo de failover é manual.

Há duas maneiras de executar o failover da réplica primária num grupo de disponibilidade com o tipo de cluster de 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. Altere a réplica primária atual para secundária e a réplica de destino para primá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 os papéis do primário antigo e das outras réplicas secundárias para SECONDARYe depois execute o seguinte comando na instância do SQL Server que hospeda a antiga réplica primária:

    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 criado para fins de escala de leitura e que não seja gerenciado por um gerenciador de cluster. Se o ouvinte original apontar para a primária antiga, solte-a e recrie-a para apontar para a nova 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, 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 um failover manual com perda de dados da réplica primária N1 para a réplica secundária N2, siga estas etapas:

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

    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 do aplicativo está apontado para o ouvinte e/ou para a nova réplica primária.

  4. Se o primário original (N1) entrar 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.

Conteúdo relacionado

  • Last updated on