Configurar o roteamento de somente leitura para um grupo de disponibilidade Always On

Aplica-se:SQL Server

Para configurar um grupo de disponibilidade Always On para oferecer suporte ao roteamento somente leitura no SQL Server, você pode usar o Transact-SQL ou o PowerShell. Roteamento de somente leitura refere-se à capacidade do SQL Server de encaminhar solicitações qualificadas de conexão de somente leitura para uma réplica secundária legível do Always On disponível (ou seja, uma réplica configurada para permitir cargas de trabalho de somente leitura quando estiver em execução na função secundária). Para oferecer suporte ao roteamento de somente leitura, o grupo de disponibilidade deve ter um listener de grupo de disponibilidade. Clientes somente de leitura devem direcionar suas solicitações de conexão para este listener, e as strings de conexão do cliente devem especificar a intenção do aplicativo como "somente leitura". Ou seja, elas devem ser solicitações de conexão com intenção de leitura.

O roteamento somente de leitura está disponível no SQL Server 2016 (13.x) e em versões posteriores.

Pré-requisitos

• O grupo de disponibilidade deve possuir um ouvinte de grupo de disponibilidade. Para obter mais informações, confira Criar ou configurar um ouvinte do grupo de disponibilidade (SQL Server).

• Uma ou mais réplicas de disponibilidade devem estar configuradas para aceitar somente leitura na função secundária (ou seja, para serem réplicas secundárias legíveis). Para obter mais informações, consulte Configurar o acesso somente para leitura em uma réplica de disponibilidade (SQL Server).

• Você deve estar conectado à instância do servidor que hospeda a réplica primária atual.

• Se estiver usando um Logon do SQL, verifique se a conta está configurada corretamente. Para obter mais informações, consulte Gerenciamento de logins e tarefas para os bancos de dados de um Grupo de Disponibilidade (SQL Server).

Quais propriedades de réplica você precisa configurar para oferecer suporte ao roteamento somente de leitura?

• Para cada réplica secundária legível que deve dar suporte a roteamento somente leitura, você precisa especificar uma URL de roteamento somente leitura. Esta URL só entra em vigor quando a réplica local estiver sendo executada sob a função secundária. A URL de roteamento somente leitura deve ser especificada réplica por réplica, quando necessário. Cada URL de roteamento somente para leitura é usada para rotear solicitações de conexão com intenção de leitura para uma réplica secundária legível específica. Normalmente, toda réplica secundária legível recebe uma URL de roteamento somente de leitura.

  Para obter informações sobre como calcular a URL de roteamento somente de leitura de uma réplica de disponibilidade, consulte Calculando read_only_routing_url para Always On

• Para cada réplica de disponibilidade para a qual você quiser dar suporte ao roteamento somente leitura quando ela for a réplica primária, será necessário especificar uma lista de roteamento de somente leitura. Uma lista de roteamento de somente leitura só tem efeito quando a réplica local está em execução na função primária. Essa lista deve ser especificada réplica por réplica, quando necessário. Normalmente, cada lista de roteamento somente leitura deveria conter todas as URLs de roteamento somente leitura, com a URL da réplica local no final da lista.

  Observação

  As solicitações de conexão com intenção de leitura são roteadas para a primeira entrada disponível na lista de roteamento somente para leitura da réplica primária atual. No entanto, o balanceamento de carga entre réplicas de somente leitura é suportado. Para obter mais informações, consulte Configurar o balanceamento de carga entre réplicas somente de leitura.

Permissões

Usando o Transact-SQL

Configurar uma lista de roteamento somente de leitura

Use as etapas a seguir para configurar o roteamento somente leitura usando o Transact-SQL. Para obter um exemplo de código, veja Exemplo (Transact-SQL), mais adiante nesta seção.

1. Conecte-se à instância de servidor que hospeda a réplica primária.

2. Se você estiver especificando uma réplica para um novo grupo de disponibilidade, use a instrução Transact-SQL CREATE AVAILABILITY GROUP. Se você estiver adicionando ou modificando uma réplica para um grupo de disponibilidade existente, use a ALTER AVAILABILITY GROUP instrução Transact-SQL.

   • Para configurar o roteamento somente de leitura para a função secundária, na cláusula ADD REPLICA ou MODIFY REPLICA WITH, especifique a opção SECONDARY_ROLE, da seguinte forma:

     FUNÇÃO_SECUNDÁRIA ( URL_DE_ROTEAMENTO_SOMENTE_LEITURA ='TCP://endereço-do-sistema:porta')

     Os parâmetros da URL de roteamento somente de leitura são os seguintes:

     system-address
     É uma cadeia de caracteres, como um nome de sistema, um nome de domínio totalmente qualificado ou um endereço IP, que identifica de forma exclusiva o sistema do computador de destino.

     porta
     É um número de porta que é usado pelo mecanismo de banco de dados da instância do SQL Server .

     Por exemplo: SECONDARY_ROLE (READ_ONLY_ROUTING_URL = N'TCP://COMPUTER01.contoso.com:1433')

     Em uma cláusula MODIFY REPLICA, ALLOW_CONNECTIONS é opcional se a réplica já estiver configurada para permitir conexões somente de leitura.

     Para obter mais informações, consulte Como calcular read_only_routing_url para o Always On.

   • Para configurar o roteamento de somente leitura para a função primária, na cláusula ADD REPLICA ou na cláusula MODIFY REPLICA WITH, especifique a opção PRIMARY_ROLE, da seguinte forma:

     PRIMARY_ROLE ( READ_ONLY_ROUTING_LIST =('server' [ , ...n ] ))

     em que server identifica uma instância de servidor que hospeda uma réplica secundária somente de leitura no grupo de disponibilidade.

     Por exemplo: PRIMARY_ROLE (READ_ONLY_ROUTING_LIST=('Server1','Server2'))

     Observação

     Você deve definir a URL de roteamento somente de leitura antes de configurar a lista de roteamento somente de leitura.

Configurar o balanceamento de carga entre réplicas somente de leitura

A partir do SQL Server 2016 (13.x), é possível configurar o balanceamento de carga entre um conjunto de réplicas somente de leitura. Anteriormente, o roteamento de somente leitura sempre direcionava o tráfego para a primeira réplica disponível na lista de roteamento. Para aproveitar esse recurso, use um nível de parênteses aninhados ao redor das instâncias de servidor em READ_ONLY_ROUTING_LIST nos comandos CREATE AVAILABILITY GROUP ou ALTER AVAILABILITY GROUP.

Por exemplo, a lista de roteamento a seguir distribui a carga da solicitação de conexão com intenção de leitura entre duas réplicas somente leitura, Server1 e Server2. Os parênteses aninhados que envolvem esses servidores identificam o conjunto com balanceamento de carga. Se nenhuma das réplicas estiver disponível nesse conjunto, ele tentará se conectar sequencialmente às outras réplicas, Server3 e Server4, na lista de roteamento de somente leitura.

READ_ONLY_ROUTING_LIST = (('Server1','Server2'), 'Server3', 'Server4')  

Observe que cada entrada na lista de roteamento pode ser, por si só, um conjunto de réplicas somente de leitura com balanceamento de carga. O exemplo a seguir demonstra isso.

READ_ONLY_ROUTING_LIST = (('Server1','Server2'), ('Server3', 'Server4', 'Server5'), 'Server6')  

Há suporte para apenas um nível de parênteses aninhados.

Exemplo (Transact-SQL)

O exemplo a seguir modifica duas réplicas de disponibilidade de um grupo de disponibilidade existente, AG1, para dar suporte ao roteamento somente para leitura se uma dessas réplicas estiver atualmente na função primária. Para identificar as instâncias de servidor que hospedam a réplica de disponibilidade, este exemplo especifica os nomes da instância –COMPUTER01 e COMPUTER02.

ALTER AVAILABILITY GROUP [AG1]  
 MODIFY REPLICA ON  
N'COMPUTER01' WITH   
(SECONDARY_ROLE (ALLOW_CONNECTIONS = READ_ONLY));  
ALTER AVAILABILITY GROUP [AG1]  
 MODIFY REPLICA ON  
N'COMPUTER01' WITH   
(SECONDARY_ROLE (READ_ONLY_ROUTING_URL = N'TCP://COMPUTER01.contoso.com:1433'));  
  
ALTER AVAILABILITY GROUP [AG1]  
 MODIFY REPLICA ON  
N'COMPUTER02' WITH   
(SECONDARY_ROLE (ALLOW_CONNECTIONS = READ_ONLY));  
ALTER AVAILABILITY GROUP [AG1]  
 MODIFY REPLICA ON  
N'COMPUTER02' WITH   
(SECONDARY_ROLE (READ_ONLY_ROUTING_URL = N'TCP://COMPUTER02.contoso.com:1433'));  
  
ALTER AVAILABILITY GROUP [AG1]   
MODIFY REPLICA ON  
N'COMPUTER01' WITH   
(PRIMARY_ROLE (READ_ONLY_ROUTING_LIST=('COMPUTER02','COMPUTER01')));  
  
ALTER AVAILABILITY GROUP [AG1]   
MODIFY REPLICA ON  
N'COMPUTER02' WITH   
(PRIMARY_ROLE (READ_ONLY_ROUTING_LIST=('COMPUTER01','COMPUTER02')));  
GO  
  

Usando o PowerShell

Configurar uma lista de roteamento somente de leitura

Use as etapas a seguir para configurar o roteamento somente leitura usando o PowerShell. Para obter um exemplo de código, consulte Exemplo (PowerShell), posteriormente nesta seção.

1. Defina o padrão (cd) para a instância do servidor que hospeda a réplica primária.

2. Ao adicionar uma réplica de disponibilidade a um grupo de disponibilidade, use o cmdlet New-SqlAvailabilityReplica . Ao modificar uma réplica de disponibilidade existente, use o cmdlet Set-SqlAvailabilityReplica . Os parâmetros relevantes são os seguintes:

   • Para configurar o roteamento de somente leitura para a função secundária, especifique o parâmetro ReadonlyRoutingConnectionUrl"url".

     em que a URL é o FQDN (nome de domínio totalmente qualificado) de conectividade e a porta a ser usada ao rotear para a réplica para conexões somente leitura. Por exemplo: -ReadonlyRoutingConnectionUrl "TCP://DBSERVER8.manufacturing.Adventure-Works.com:7024"

     Para obter mais informações, consulte Calculando a read_only_routing_url para o Always On.

   • Para configurar o acesso de conexão para a função primária, especifique ReadonlyRoutingList"server" [ , ...n ], em que server identifica uma instância de servidor que hospeda uma réplica secundária somente leitura no grupo de disponibilidade. Por exemplo: -ReadOnlyRoutingList "SecondaryServer","PrimaryServer"

     Observação

     Você precisa definir a URL de roteamento de somente leitura de uma réplica antes de configurar a lista de roteamento de somente leitura dela.

   Observação

   Para exibir a sintaxe de um cmdlet, use o cmdlet Get-Help no ambiente do SQL Server PowerShell. Para obter mais informações, consulte Get Help SQL Server PowerShell.

Configurar e usar o provedor do SQL Server PowerShell

• Provedor do SQL Server PowerShell

• Obter ajuda do SQL Server PowerShell

Exemplo (PowerShell)

O exemplo a seguir configura a réplica primária e uma réplica secundária em um grupo de disponibilidade para roteamento somente leitura. Primeiro, o exemplo atribui uma URL de roteamento somente para leitura a cada réplica. Em seguida, ele configura a lista de roteamento somente para leitura na réplica primária. As conexões com a propriedade "ReadOnly" definida na string de conexão serão redirecionadas à réplica secundária. Se a réplica secundária não estiver legível (conforme determinado pela configuração ConnectionModeInSecondaryRole ), a conexão será direcionada de volta para a réplica primária.

Set-Location SQLSERVER:\SQL\PrimaryServer\default\AvailabilityGroups\MyAg  
$primaryReplica = Get-Item "AvailabilityReplicas\PrimaryServer"  
$secondaryReplica = Get-Item "AvailabilityReplicas\SecondaryServer"  
  
Set-SqlAvailabilityReplica -ReadOnlyRoutingConnectionUrl "TCP://PrimaryServer.domain.com:1433" -InputObject $primaryReplica  
Set-SqlAvailabilityReplica -ReadOnlyRoutingConnectionUrl "TCP://SecondaryServer.domain.com:1433" -InputObject $secondaryReplica  
Set-SqlAvailabilityReplica -ReadOnlyRoutingList "SecondaryServer","PrimaryServer" -InputObject $primaryReplica  

Acompanhamento: Depois de configurar o roteamento somente para leitura

Quando a réplica primária atual e as réplicas secundárias legíveis estiverem configuradas para oferecer suporte ao roteamento de conexões somente de leitura em ambas, as réplicas secundárias legíveis poderão receber solicitações de conexão com intenção de leitura de clientes que se conectam por meio do ouvinte do grupo de disponibilidade.

Requisitos e recomendações para strings de conexão do cliente

Para que um aplicativo cliente use o roteamento somente leitura, sua cadeia de conexão deve atender aos seguintes requisitos:

• Usar o protocolo TCP.

• Definir o atributo/propriedade de intenção do aplicativo como readonly.

• Fazer referência ao ouvinte de um grupo de disponibilidade configurado para dar suporte ao roteamento de somente leitura.

• Fazer referência a um banco de dados nesse grupo de disponibilidade.

Além disso, é recomendável que cadeias de conexão habilitem o failover de várias sub-redes, oferecendo suporte a um thread de cliente paralelo para cada réplica em cada sub-rede. Isso minimiza o tempo de reconexão do cliente após um failover.

A sintaxe de uma cadeia de conexão depende do provedor SQL Server que um aplicativo está usando. O exemplo de string de conexão a seguir para o Provedor de Dados do .NET Framework 4.0.2 para SQL Server ilustra as partes da string de conexão que são necessárias e recomendadas para funcionar com o roteamento somente leitura.

Server=tcp:MyAgListener,1433;Database=Db1;IntegratedSecurity=SSPI;ApplicationIntent=ReadOnly;MultiSubnetFailover=True  

Para obter mais informações sobre a intenção do aplicativo somente leitura e o roteamento somente leitura, confira Ouvintes do grupo de disponibilidade, conectividade de cliente e failover de aplicativo (SQL Server).

Se o roteamento somente leitura não estiver funcionando corretamente

Para obter informações sobre como solucionar problemas de uma configuração de roteamento somente leitura, veja O roteamento somente leitura não está funcionando corretamente.

Reverter para o comportamento de roteamento padrão

A partir do SQL Server 2025 (17.x), você pode especificar NONE como o READ_WRITE_ROUTING_URL ou o READ_ONLY_ROUTING_URL destino para reverter o roteamento especificado para a réplica de disponibilidade e rotear o tráfego com base no comportamento padrão. Para saber mais, examine ALTER AVAILABILITY GROUP Transact-SQL comando.

Próximas etapas

Para exibir configurações de roteamento somente leitura

• sys.availability_read_only_routing_lists (Transact-SQL)

• sys.availability_replicas (Transact-SQL) (coluna read_only_routing_url)

Para configurar o acesso à conexão do cliente

• Criar ou configurar um listener de grupo de disponibilidade (SQL Server)

• Configurar o acesso somente leitura em uma réplica de disponibilidade (SQL Server)

Para usar cadeias de conexão em aplicativos

• Suporte do SQL Server Native Client à alta disponibilidade e recuperação de desastre

• Usando palavras-chave da cadeia de conexão com o SQL Server Native Client

Blogs:

• Calculando read_only_routing_url para Always On

• Blogs da equipe do AlwaysOn do SQL Server: o blog oficial da equipe do AlwaysOn do SQL Server

• Blogs dos engenheiros do CSS SQL Server

Conteúdo adicional

• Visão geral dos Grupos de Disponibilidade AlwaysOn (SQL Server)

• Secundárias ativas: Réplicas secundárias legíveis (grupos de disponibilidade Always On)

• Sobre o acesso do cliente às réplicas de disponibilidade (SQL Server)

• Listeners do Grupo de Disponibilidade, Conectividade do Cliente e Failover de Aplicativos (SQL Server)