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

Por padrão, tanto o acesso de leitura-gravação quanto o acesso de tentativa de leitura são permitidos para a réplica primária e nenhuma conexão direta é permitida com as réplicas secundárias de um grupo de disponibilidade AlwaysOn. Este tópico descreve como configurar o acesso de conexão em uma réplica de disponibilidade de um grupo de disponibilidade AlwaysOn no SQL Server 2012 usando SQL Server Management Studio, Transact-SQLou PowerShell.

Para obter informações sobre as consequências de habilitar o acesso somente leitura para uma réplica secundária e para uma introdução a acesso de conexão, consulte Sobre Acesso de conexão de cliente a réplicas de disponibilidade (SQL Server) e Secundárias ativas: réplicas secundárias legíveis (Grupos de Disponibilidade AlwaysOn).

• Antes de começar:  

  Pré-requisitos e restrições

  Segurança

• Para configurar o acesso em uma réplica de disponibilidade usando:  

  SQL Server Management Studio

  Transact-SQL

  PowerShell

• Acompanhamento: depois de configurar o acesso somente leitura para uma réplica de disponibilidade

• Tarefas relacionadas

• Conteúdo relacionado

Antes de começar

Pré-requisitos e restrições

• Para configurar um acesso de conexão diferente, deverá estar conectado à instância de servidor que hospeda a réplica primária.

Segurança

Permissões

Tarefa	Permissões
Para configurar réplicas ao criar um grupo de disponibilidade	Requer a associação à função de servidor fixa sysadmin e a permissão de servidor CREATE AVAILABILITY GROUP, a permissão CONTROL 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.

[Início]

Usando o SQL Server Management Studio

Para configurar o acesso em uma réplica de disponibilidade

1. No Pesquisador de Objetos, conecte-se à instância de servidor que hospeda a réplica primária e expanda a árvore de servidores.

2. Expanda os nós Alta Disponibilidade AlwaysOn e Grupos de Disponibilidade.

3. Clique no grupo de disponibilidade cuja réplica você deseja alterar.

4. Clique com o botão direito do mouse na réplica de disponibilidade e clique em Propriedades.

5. Na caixa de diálogo Propriedades de Réplica de disponibilidade, você pode alterar o acesso de conexão para as funções primária e secundária, da seguinte forma:

   • Para a função secundária, selecione um novo valor na lista suspensa Secundária de Leitura, da seguinte forma:

     • Não
       Nenhuma conexão de usuário é permitida para bancos de dados secundários desta réplica. Eles não estão disponíveis para acesso de leitura. Esta é a configuração padrão.

     • Tentativa de leitura somente
       Somente conexões somente leitura são permitidas para bancos de dados secundários desta réplica. Os bancos de dados secundários estão disponíveis para acesso de leitura.

     • Sim
       Todas as conexões são permitidas para os bancos de dados secundários desta réplica, mas somente para acesso de leitura. Os bancos de dados secundários estão disponíveis para acesso de leitura.

   • Para a função primária, selecione um novo valor na lista suspensa Conexões na função primária, da seguinte forma:

     • Permitir todas as conexões
       Todas as conexões são permitidas com os bancos de dados na réplica primária. Esta é a configuração padrão.

     • Permitir conexões de leitura/gravação
       Quando a propriedade Application Intent está definida como ReadWrite ou não está definida, a conexão é permitida. Conexões em que a propriedade de conexão Application Intent é definida como ReadOnly não são permitidas. Isto pode ajudar a impedir os clientes de conectarem uma carga de trabalho com intenção de leitura à réplica primária por engano. Para obter mais informações sobre a propriedade de conexão Application Intent, consulte Usando palavras-chave da cadeia de conexão com o SQL Server Native Client.

[Início]

Usando Transact-SQL

Para configurar o acesso em uma réplica de disponibilidade

Observação
Para obter um exemplo desse procedimento, 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 de um grupo de disponibilidade existente, use a instrução ALTER AVAILABILITY GROUP Transact-SQL.

   • Para configurar o acesso de conexão 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 ( ALLOW_CONNECTIONS = { NO | READ_ONLY | ALL } )

     onde:

     • NO
       Nenhuma conexão direta é permitida para bancos de dados secundários desta réplica. Eles não estão disponíveis para acesso de leitura. Esta é a configuração padrão.

     • READ_ONLY
       Somente conexões somente leitura são permitidas para bancos de dados secundários desta réplica. Os bancos de dados secundários estão disponíveis para acesso de leitura.

     • ALL
       Todas as conexões são permitidas para os bancos de dados secundários desta réplica, mas somente para acesso de leitura. Os bancos de dados secundários estão disponíveis para acesso de leitura.

3. Para configurar o acesso de conexão 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 ( ALLOW_CONNECTIONS = { READ_WRITE | ALL } )

   onde:

   • READ_WRITE
     Conexões em que a propriedade de conexão Application Intent é definida como ReadOnly não são permitidas. Quando a propriedade Application Intent está definida como ReadWrite ou não está definida, a conexão é permitida. Para obter mais informações sobre a propriedade de conexão Application Intent, consulte Usando palavras-chave da cadeia de conexão com o SQL Server Native Client.

   • ALL
     Todas as conexões são permitidas com os bancos de dados na réplica primária. Essa é a configuração padrão.

Exemplo (Transact-SQL)

O exemplo a seguir adiciona uma réplica secundária a um grupo de disponibilidade denominado AG2. Uma instância de servidor autônoma, COMPUTER03\HADR_INSTANCE, é especificada para hospedar a nova réplica de disponibilidade. Essa réplica foi configurada para permitir apenas conexões de leitura-gravação para a função primária e para permitir apenas conexões de tentativa de leitura para uma função secundária.

ALTER AVAILABILITY GROUP AG2 
   ADD REPLICA ON 
      'COMPUTER03\HADR_INSTANCE' WITH 
         (
         ENDPOINT_URL = 'TCP://COMPUTER03:7022',
         PRIMARY_ROLE ( ALLOW_CONNECTIONS = READ_WRITE ),
         SECONDARY_ROLE (ALLOW_CONNECTIONS = READ_ONLY )
         ); 
GO

[Início]

Usando o PowerShell

Para configurar o acesso em uma réplica de disponibilidade

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

1. Altere o diretório (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 acesso de conexão para a função secundária, especifique o parâmetro ConnectionModeInSecondaryRole secondary_role_keyword, em que secondary_role_keyword é igual a um dos seguintes valores:

     • AllowNoConnections
       Nenhuma conexão direta é permitida com os bancos de dados na réplica secundária e os bancos de dados não estão disponíveis para acesso de leitura. Essa é a configuração padrão.

     • AllowReadIntentConnectionsOnly
       Conexões são permitidas somente com os bancos de dados na réplica secundária onde a propriedade Application Intent está definida como ReadOnly. Para obter mais informações sobre essa propriedade, consulte Usando palavras-chave da cadeia de conexão com o SQL Server Native Client.

     • AllowAllConnections
       Todas as conexões são permitidas com os bancos de dados na réplica secundária para acesso somente leitura.

   • Para configurar o acesso de conexão para a função primária, especifique o parâmetro ConnectionModeInPrimaryRole primary_role_keyword, em que primary_role_keyword é igual a um dos seguintes valores:

     • AllowReadWriteConnections
       Conexões em que a propriedade de conexão Application Intent é definida como ReadOnly não são permitidas. Quando a propriedade Application Intent está definida como ReadWrite ou não está definida, a conexão é permitida. Para obter mais informações sobre a propriedade de conexão Application Intent, consulte Usando palavras-chave da cadeia de conexão com o SQL Server Native Client.

     • AllowAllConnections
       Todas as conexões são permitidas com os bancos de dados na réplica primária. Essa é a configuração padrão.

   Observação
   Para exibir a sintaxe de um cmdlet, use o cmdlet Get-Help no ambiente do SQL Server 2012 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

Exemplo (PowerShell)

O exemplo a seguir define os parâmetros ConnectionModeInSecondaryRole e ConnectionModeInPrimaryRole para AllowAllConnections.

Set-Location SQLSERVER:\SQL\PrimaryServer\default\AvailabilityGroups\MyAg
$primaryReplica = Get-Item "AvailabilityReplicas\PrimaryServer"
Set-SqlAvailabilityReplica -ConnectionModeInSecondaryRole "AllowAllConnections" ` 
-InputObject $primaryReplica
Set-SqlAvailabilityReplica -ConnectionModeInPrimaryRole "AllowAllConnections" ` 
-InputObject $primaryReplica

[Início]

Acompanhamento: depois de configurar o acesso somente leitura para uma réplica de disponibilidade

Acesso somente leitura para uma réplica secundária legível

• 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.

• Para permitir que aplicativos clientes conectem-se a réplicas secundárias legíveis:

  	Pré-requisito	Link
  	Verifique se o grupo de disponibilidade tem um ouvinte.	Criar ou configurar um ouvinte de grupo de disponibilidade (SQL Server)
  	Configurar o roteamento somente leitura para o grupo de disponibilidade.	Configurar o roteamento somente leitura para um grupo de disponibilidade (SQL Server)

Fatores que podem afetar gatilhos e trabalhos depois de um failover

Se você tem gatilhos e trabalhos que podem falhar ao executar um banco de dados secundário não legível ou em um banco de dados secundário legível, você precisa executar o script dos gatilhos e dos trabalhos para verificar em uma réplica determinada se o banco de dados é primário ou secundário legível. Para obter estas informações, use a função DATABASEPROPERTYEX para retornar a propriedade Updatability do banco de dados. Para identificar um banco de dados somente leitura, especifique READ_ONLY como o valor, da seguinte maneira:

DATABASEPROPERTYEX([db name],’Updatability’) = N’READ_ONLY’

Para identificar um banco de dados leitura/gravação, especifique READ_WRITE como o valor.

[Início]

Tarefas relacionadas

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

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

[Início]

Conteúdo relacionado

• AlwaysOn: Proposição do valor da réplica secundária legível

• AlwaysOn: Por que não há duas opções para habilitar uma réplica secundária para a carga de trabalho de leitura?

• AlwaysOn: Configurando a réplica secundária legível

• AlwaysOn: Eu acabei de habilitar a réplica secundária legível, mas minha consulta está bloqueada?

• AlwaysOn: Disponibilizando as estatísticas mais recentes na réplica secundária legível, no banco de dados somente leitura e no instantâneo do banco de dados

• AlwaysOn: Desafios das estatísticas no banco de dados somente leitura, no instantâneo do banco de dados e na réplica secundária

• AlwaysOn: Impacto na carga de trabalho primária quando você executar a carga de trabalho do relatório na réplica secundária

• AlwaysOn: Impacto do mapeamento da carga de trabalho do relatório na réplica secundária legível para o isolamento do instantâneo

• AlwaysOn: Minimizando o bloqueio de thread REDO ao executar a carga de trabalho do relatório na réplica secundária

• AlwaysOn: Réplica secundária legível e latência de dados

[Início]

Consulte também

Conceitos

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)