Partilhar via

Configurar o roteamento somente leitura para um grupo de disponibilidade (SQL Server)

  • Artigo

Para configurar um grupo de disponibilidade AlwaysOn para dar suporte ao roteamento somente leitura no SQL Server 2014, você pode usar o Transact-SQL ou o PowerShell. O roteamento somente leitura refere-se à capacidade de SQL Server rotear solicitações de conexão somente leitura qualificadas para um réplica secundário legível AlwaysOn disponível (ou seja, um réplica configurado para permitir cargas de trabalho somente leitura durante a execução na função secundária). Para dar suporte ao roteamento somente leitura, o grupo de disponibilidade deve ter um ouvinte do grupo de disponibilidade. Clientes somente leitura devem direcionar suas solicitações de conexão para este ouvinte e as cadeias 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 de intenção de leitura.

Observação

Para obter informações sobre como configurar uma réplica secundária legível, confira Configurar o acesso somente leitura em uma réplica de disponibilidade (SQL Server).

Observação

Não há suporte para a configuração do roteamento somente leitura por SQL Server Management Studio.

Antes de começar

Pré-requisitos

Quais as propriedades de réplica você precisa configurar para dar suporte a roteamento somente 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 do roteamento somente leitura deve ser especificada réplica por réplica, quando necessário. Cada URL de roteamento somente leitura é usada para solicitações de conexão de intenção de leitura para uma réplica secundária legível específica. Normalmente, toda réplica secundária legível é atribuída uma URL de roteamento somente leitura.

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

  • Para cada réplica de disponibilidade que você quer dar suporte a roteamento somente leitura quando é a réplica primária, você precisará especificar uma lista de roteamento somente leitura. Uma determinada lista de roteamento somente leitura só entra em vigor quando a réplica local estiver sendo executada em uma 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 e conexão de intenção de leitura são roteadas para a primeira réplica secundária legível disponível na lista de roteamento somente leitura da réplica primária atual. Não há nenhum balanceamento de carga.

Observação

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

Segurança

Permissões

Tarefa Permissões
Para configurar réplicas ao criar um grupo de disponibilidade Requer a associação na função de servidor fixa sysadmin e a permissão de servidor CREATE AVAILABILITY GROUP, a permissão ALTER ANY AVAILABILITY GROUP ou a permissão CONTROL SERVER.
Para modificar uma réplica de disponibilidade Requer a permissão ALTER AVAILABILITY GROUP no grupo de disponibilidade, a permissão CONTROL AVAILABILITY GROUP, a permissão ALTER ANY AVAILABILITY GROUP ou a permissão CONTROL SERVER.

Usando o Transact-SQL

Para configurar o roteamento somente leitura

Observação

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 um réplica para um grupo de disponibilidade existente, use a instrução Transact-SQL ALTER AVAILABILITY GROUP.

    • Para configurar o roteamento somente 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:

      SECONDARY_ROLE ( READ_ONLY_ROUTING_URL ='TCP://system-address:port')

      Os parâmetros da URL de roteamento somente 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.

      port
      É 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, o ALLOW_CONNECTIONS será opcional se a réplica já estiver configurada para permitir conexões somente leitura.

      Para obter mais informações, consulte Calculando read_only_routing_url de AlwaysOn.

    • Para configurar o roteamento somente leitura para a função primária, na cláusula ADD REPLICA ou 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 leitura em um grupo de disponibilidade.

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

      Observação

      Você precisa definir a URl de roteamento somente leitura antes de configurar a lista de roteamento somente leitura.

Exemplo (Transact-SQL)

O exemplo a seguir modifica duas réplicas de disponibilidade de um grupo de disponibilidade existente, AG1 para oferecer suporte ao roteamento somente leitura quando uma dessas réplicas possui a função primária no momento. 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

Para configurar o roteamento somente leitura

Observação

Para obter um exemplo de código, consulte Exemplo (PowerShell), posteriormente nesta seção.

  1. Defina o padrão (cd) para a instância de 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 somente leitura para a função secundária, especifique o parâmetro ReadonlyRoutingConnectionUrl""url" .

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

      Para obter mais informações, consulte Calculando read_only_routing_url de AlwaysOn.

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

      Observação

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

    Observação

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

Para configurar e usar o provedor do PowerShell SQL Server, confira SQL Server Provedor do PowerShell e Obter Ajuda 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 o roteamento somente leitura. Primeiro, o exemplo atribui uma URL de roteamento somente leitura a cada réplica. Em seguida, ele define a lista de roteamento somente leitura na réplica primária. As conexões com o conjunto de propriedades "ReadOnly" na cadeia de conexão serão redirecionados à 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 leitura

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

Dica

Ao usar o Utilitário bcp ou o Utilitário sqlcmd, você pode especificar o acesso somente leitura a qualquer réplica secundário habilitado para acesso somente leitura especificando a opção -K ReadOnly .

Requisitos e recomendações para cadeias de conexão de 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.

  • Referenciar o ouvinte de um grupo de disponibilidade que está configurado para oferecer suporte ao roteamento somente leitura.

  • Referenciar 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. A cadeia de conexão de exemplo a seguir para o provedor de dados .NET Framework 4.0.2 para SQL Server ilustra as partes de uma cadeia de conexão que são necessárias e recomendadas no 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.

Related Tasks

Para exibir configurações do roteamento somente leitura

Para configurar o acesso de conexão de cliente

Para usar cadeias de conexão em aplicativos

Conteúdo relacionado

Consulte Também

Visão geral de grupos de disponibilidade AlwaysOn (SQL Server)
Visão geral de grupos de disponibilidade AlwaysOn (SQL Server)
Secundárias ativas: Réplicas secundárias para leitura (Grupos de Disponibilidade AlwaysOn)
Sobre Acesso de conexão de cliente a réplicas de disponibilidade (SQL Server)
Ouvintes do grupo de disponibilidade, conectividade de cliente e failover de aplicativo (SQL Server)