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

Para configurar um grupo de disponibilidade AlwaysOn para oferecer suporte ao roteamento somente leitura no SQL Server 2012, você pode usar o Transact-SQL ou o PowerShell. Roteamento somente leitura refere-se à capacidade de o SQL Server rotear solicitações de conexão somente leitura para uma réplica secundária legível AlwaysOn disponível (ou seja, uma réplica que é configurada para permitir cargas de trabalho somente leitura ao ser executada sob a função secundária). Para dar suporte a roteamento somente leitura, o grupo de disponibilidade deve ter um ouvinte de 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, consulte Configurar o acesso somente leitura em uma réplica de disponibilidade (SQL Server).

• Antes de começar:

  Pré-requisitos

  Quais as propriedades de réplica você precisa configurar para dar suporte a roteamento somente leitura?

  Segurança

• Para configurar o roteamento somente leitura, usando:

  Transact-SQL

  PowerShell

  Observação
  A configuração do roteamento somente leitura não tem suporte do SQL Server Management Studio.

• Acompanhamento: Depois de configurar o roteamento somente leitura

• Tarefas relacionadas

• Conteúdo relacionado

Antes de começar

Pré-requisitos

• O grupo de disponibilidade deve possuir um ouvinte de grupo de disponibilidade. Para obter mais informações, consulte Criar ou configurar um ouvinte de 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 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.

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 é usado 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 de grupo de disponibilidade e mais informações sobre roteamento somente leitura, consulte Ouvintes de grupo de disponibilidade, conectividade de cliente e failover de aplicativo (SQL Server).

Segurança

Permissões

[Início]

Usando Transact-SQL

Para configurar o roteamento somente leitura

Observação
Para obter um exemplo de código, consulte Exemplo (Transact-SQL), posteriormente 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 CREATE AVAILABILITY GROUP Transact-SQL. Se você estiver adicionando ou modificando uma réplica para um grupo de disponibilidade existente, use a instrução ALTER AVAILABILITY GROUP Transact-SQL.

   • 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 ] ) )

     onde, 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".

     onde, 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 de conexão para a função primária, especifique ReadonlyRoutingList "server" [ ,...n ], onde 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 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 cmdlet Get-Help no ambiente do SQL Server PowerShell. Para obter mais informações, consulte Obter Ajuda do SQL Server PowerShell.

Para 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 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

[Início]

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
Com o Utilitário bcp ou o Utilitário sqlcmd, você pode especificar o acesso somente leitura a qualquer réplica secundária habilitada 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, consulte Ouvintes de 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 configuração de roteamento somente leitura, consulte O roteamento somente leitura não está funcionando corretamente.

Tarefas relacionadas

Para exibir configurações do 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 de conexão de cliente

• Criar ou configurar um ouvinte 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

[Início]

Conteúdo relacionado

• **Blogs: **

  Calculando read_only_routing_url de AlwaysOn

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

  Blogs dos engenheiros do CSS SQL Server

• **White papers: **

  White papers da Microsoft para SQL Server 2012

  White papers da equipe de consultoria do cliente do SQL Server

[Início]

Consulte também

Conceitos

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 legíveis (Grupos de Disponibilidade AlwaysOn)

Sobre Acesso de conexão de cliente a réplicas de disponibilidade (SQL Server)

Ouvintes de grupo de disponibilidade, conectividade de cliente e failover de aplicativo (SQL Server)