View a markdown version of this page

Configurar o roteamento somente para leitura nos grupos de disponibilidade do Always On no SQL Server em AWS - Recomendações da AWS
ResumoPré-requisitos e limitaçõesArquiteturaFerramentasPráticas recomendadasÉpicosSolução de problemasRecursos relacionadosMais informações

As traduções são geradas por tradução automática. Em caso de conflito entre o conteúdo da tradução e da versão original em inglês, a versão em inglês prevalecerá.

Configurar o roteamento somente para leitura nos grupos de disponibilidade do Always On no SQL Server em AWS

Subhani Shaik, Amazon Web Services

Resumo

Esse padrão aborda como usar a réplica secundária em espera no SQL Server Always On (Sempre ativo), transferindo as workloads somente leitura da réplica primária para a réplica secundária.

O espelhamento do banco de dados tem one-to-one mapeamento. Você não pode ler o banco de dados secundário diretamente, então deve criar snapshots. O atributo de grupo de disponibilidade Always On (Sempre ativo) foi introduzido no Microsoft SQL Server 2012. Em versões posteriores, as principais funcionalidades foram introduzidas, incluindo roteamento somente leitura. Nos grupos de disponibilidade Always On (Sempre ativo), você pode ler os dados diretamente da réplica secundária alterando o modo de réplica para somente leitura.

A solução de grupos de disponibilidade Always On (Sempre ativo) oferece suporte à alta disponibilidade (HA), recuperação de desastres (DR) e uma alternativa ao espelhamento de banco de dados. Os grupos de disponibilidade Always On (Sempre ativo) processam no nível do banco de dados e maximizam a disponibilidade de um conjunto de bancos de dados de usuários.

O SQL Server usa o mecanismo de roteamento somente leitura para redirecionar as conexões somente leitura de entrada para a réplica de leitura secundária. Para fazer isso, você deve adicionar os seguintes parâmetros e valores na string de conexão:

  • ApplicationIntent=ReadOnly

  • Initial Catalog=<database name>

Pré-requisitos e limitações

Pré-requisitos

  • Um ativo Conta da AWS com uma nuvem privada virtual (VPC), duas zonas de disponibilidade, sub-redes privadas e um grupo de segurança

  • Duas máquinas Amazon Elastic Compute Cloud (Amazon EC2) com SQL Server 2019 Enterprise Edition Amazon Machine Image com Windows Server Failover Clustering (WSFC) configurado no nível da instância e um grupo de disponibilidade Always On configurado no nível do SQL Server entre o nó primário WSFCNODE1 () e o nó secundário (), que fazem parte do diretório chamado WSFCNODE2 AWS Directory Service for Microsoft Active Directory tagechtalk.com

  • Um ou mais nós configurados para aceitar read-only na réplica secundária

  • Um receptor com o nome de SQLAG1 para o grupo de disponibilidade Always On (Sempre ativo)

  • Mecanismo de banco de dados do SQL Server em execução com a mesma conta de serviço em dois nós

  • SQL Server Management Studio (SSMS)

  • Um banco de dados de teste chamado test

Versões do produto

  • SQL Server 2014 e versões posteriores

Arquitetura

Pilha de tecnologias de destino

  • Amazon EC2

  • AWS Managed Microsoft AD

  • Amazon FSx

Arquitetura de destino

O diagrama a seguir mostra como o receptor do Grupo de disponibilidade Always On (AG) redireciona as consultas que contêm o parâmetro ApplicationIntent na conexão para o nó secundário apropriado.

Processo em três etapas entre duas zonas de disponibilidade para o nó 1 do WSFC e o nó 2 do WSFC com o Amazon EFS.

  1. Uma solicitação é enviada para o receptor do grupo de disponibilidade Always On (Sempre ativo).

  2. Se a string de conexão não tiver o parâmetro ApplicationIntent, a solicitação será enviada para a instância primária.

  3. Se a cadeia de conexão contiverApplicationIntent=ReadOnly, a solicitação será enviada para a instância secundária com configuração de roteamento somente para leitura, que é WSFC com um grupo de disponibilidade Always On.

Ferramentas

Serviços da AWS

  • AWS Directory Service for Microsoft Active Directorypermite que suas cargas de trabalho e AWS recursos com reconhecimento de diretório usem o Microsoft Active Directory no. Nuvem AWS

  • O Amazon Elastic Compute Cloud (Amazon EC2) oferece capacidade de computação escalável na Nuvem AWS. Você poderá iniciar quantos servidores virtuais precisar e escalá-los na vertical rapidamente.

  • FSxA Amazon fornece sistemas de arquivos que oferecem suporte a protocolos de conectividade padrão do setor e oferecem alta disponibilidade e replicação em todo o mundo. Regiões da AWS

Outros serviços

  • O SQL Server Management Studio (SSMS) é uma ferramenta para conectar, gerenciar e administrar as instâncias do SQL Server.

  • sqlcmd é um utilitário de linha de comando.

Práticas recomendadas

Para obter mais informações sobre grupos de disponibilidade Always On (Sempre ativo), consulte a documentação do SQL Server.

Épicos

TarefaDescriptionHabilidades necessárias

Atualizar as réplicas para somente leitura.

Para atualizar a réplica primária e a secundária para somente leitura, conecte-se à réplica primária a partir do SSMS e execute o código da Etapa 1 na seção Informações adicionais.

DBA

Criar o URL de roteamento.

Para criar o URL de roteamento para ambas as réplicas, execute o código da Etapa 2 na seção Informações adicionais. Nesse código, tagechtalk.com é o nome do AWS Managed Microsoft AD diretório.

DBA

Criar a lista de roteamento.

Para criar a lista de roteamento para ambas as réplicas, execute o código da Etapa 3 na seção Informações adicionais.

DBA

Validar a lista de roteamento.

Conecte-se à instância primária do SQL Server Management Studio e execute o código da Etapa 4 na seção Informações adicionais para validar a lista de roteamento.

DBA
TarefaDescriptionHabilidades necessárias

Conecte-se usando o ApplicationIntent parâmetro.

  1. No SSMS, conecte-se ao nome do receptor do grupo de disponibilidade Always On (Sempre ativo) com ApplicationIntent=ReadOnly;Initial Catalog=test.

  2. A conexão é estabelecida com a réplica secundária. Para testar, execute o comando a seguir para mostrar o nome do servidor conectado.

    SELECT SERVERPROPERTY('ComputernamePhysicalNetBios')

    A saída mostrará o nome da réplica secundária atual (WSFCNODE2).

DBA

Executar um failover.

  1. No SSMS, conecte-se ao nome do receptor do grupo de disponibilidade Always On (Sempre ativo).

  2. Verifique se os bancos de dados primário e secundário estão sincronizados, sem perda de dados.

  3. Execute um failover para que a réplica primária atual se torne a réplica secundária e a réplica secundária se torne a réplica primária.

  4. No SSMS, conecte-se ao nome do receptor do grupo de disponibilidade Always On (Sempre ativo) com ApplicationIntent=ReadOnly;Initial Catalog=test.

  5. A conexão é estabelecida com a réplica secundária. Para testar isso, mostre o nome do servidor conectado executando o seguinte comando.

    SELECT SERVERPROPERTY('ComputernamePhysicalNetBios')

    Ele exibirá o nome da réplica secundária atual (WSFCNODE1).

DBA
TarefaDescriptionHabilidades necessárias

Conectar usando sqlcmd.

Para se conectar a partir do sqlcmd, execute o código da Etapa 5 na seção Informações adicionais no prompt de comando. Após conectar, execute o comando a seguir para mostrar o nome do servidor conectado.

SELECT SERVERPROPERTY('ComputernamePhysicalNetBios') .

A saída exibirá o nome da réplica secundária atual (WSFCNODE1).

DBA

Solução de problemas

ProblemaSolução

A criação do receptor falha com a mensagem “O cluster WSFC não pôde colocar o recurso de nome de rede online”.

Para obter mais informações, consulte a postagem do blog da Microsoft Criar receptor falha com a mensagem “O cluster WSFC não pôde colocar o recurso de nome da rede online”.

Problemas potenciais, incluindo outros problemas de receptor ou problemas de acesso à rede.

Consulte Solução de problemas configuração de Grupos de disponibilidade Always On (SQL Server) na documentação da Microsoft.

Recursos relacionados

Mais informações

Etapa 1. Atualizar as réplicas para somente leitura

ALTER AVAILABILITY GROUP [SQLAG1] MODIFY REPLICA ON N'WSFCNODE1' WITH (SECONDARY_ROLE (ALLOW_CONNECTIONS = READ_ONLY))
GO
ALTER AVAILABILITY GROUP [SQLAG1] MODIFY REPLICA ON N'WSFCNODE2' WITH (SECONDARY_ROLE (ALLOW_CONNECTIONS = READ_ONLY))
GO

Etapa 2. Criar o URL de roteamento

ALTER AVAILABILITY GROUP [SQLAG1] MODIFY REPLICA ON N'WSFCNODE1' WITH (SECONDARY_ROLE (READ_ONLY_ROUTING_URL = N'TCP://WSFCNode1.tagechtalk.com:1433'))
GO
ALTER AVAILABILITY GROUP [SQLAG1] MODIFY REPLICA ON N'WSFCNODE2' WITH (SECONDARY_ROLE (READ_ONLY_ROUTING_URL = N'TCP://WSFCNode2.tagechtalk.com:1433'))
GO

Etapa 3. Criar a lista de roteamento

ALTER AVAILABILITY GROUP [SQLAG1] MODIFY REPLICA ON N'WSFCNODE1' WITH (PRIMARY_ROLE(READ_ONLY_ROUTING_LIST=('WSFCNODE2','WSFCNODE1')));
GO
ALTER AVAILABILITY GROUP [SQLAG1] MODIFY REPLICA ON N'WSFCNODE2' WITH (PRIMARY_ROLE (READ_ONLY_ROUTING_LIST=('WSFCNODE1','WSFCNODE2')));
GO

Etapa 4. Validar a lista de roteamento

SELECT AGSrc.replica_server_name AS PrimaryReplica, AGRepl.replica_server_name AS ReadOnlyReplica, AGRepl.read_only_routing_url AS RoutingURL , AGRL.routing_priority AS RoutingPriority FROM sys.availability_read_only_routing_lists AGRL INNER JOIN sys.availability_replicas AGSrc ON AGRL.replica_id = AGSrc.replica_id INNER JOIN sys.availability_replicas AGRepl ON AGRL.read_only_replica_id = AGRepl.replica_id INNER JOIN sys.availability_groups AV ON AV.group_id = AGSrc.group_id ORDER BY PrimaryReplica

Etapa 5. Utilitário de comando SQL

sqlcmd -S SQLAG1,1433 -E -d test -K ReadOnly