Configurar o acesso Read-Only em uma réplica de disponibilidade (SQL Server)

Por padrão, o acesso de leitura e gravação e de leitura intencional é permitido para a réplica primária e nenhuma conexão é permitida para as réplicas secundárias de um grupo de disponibilidade AlwaysOn. Este tópico descreve como configurar o acesso à conexão em uma réplica de disponibilidade de um grupo de disponibilidade AlwaysOn no SQL Server 2014 usando o SQL Server Management Studio, o Transact-SQL ou o PowerShell.

Para obter informações sobre as implicações de habilitar o acesso somente leitura para uma réplica secundária e para obter uma introdução ao acesso à conexão, consulte Sobre o acesso de conexão do cliente a réplicas de disponibilidade (SQL Server) e secundários ativos: réplicas secundárias legíveis (Grupos de Disponibilidade AlwaysOn).

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

Como usar 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. Essa é 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. Essa é 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 Tentativa de Aplicativo, consulte Using Connection String Keywords with SQL Server Native Client.

Usando Transact-SQL

Para configurar o acesso em uma réplica de disponibilidade

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

   • 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 = { NÃO | READ_ONLY | ALL } )

     onde:

     Não
     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. Essa é a configuração padrão.

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

     TODOS
     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:

   LEITURA/ESCRITA
   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 Tentativa de Aplicativo, consulte Using Connection String Keywords with SQL Server Native Client.

   TODOS
   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  

Como usar o PowerShell

Para configurar o acesso a uma réplica de disponibilidade

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 New-SqlAvailabilityReplica cmdlet. Ao modificar uma réplica de disponibilidade existente, use o Set-SqlAvailabilityReplica cmdlet. Os parâmetros relevantes são os seguintes:

   • Para configurar o acesso à conexão para a função secundária, especifique o ConnectionModeInSecondaryRole parâmetro secondary_role_keyword, onde 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 em que a propriedade Application Intent está definida como ReadOnly. Para obter mais informações sobre essa propriedade, consulte Using Connection String Keywords with 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 à conexão para a função primária, especifique ConnectionModeInPrimaryRoleprimary_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 Tentativa de Aplicativo, consulte Using Connection String Keywords with 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 Get-Help cmdlet no ambiente do SQL Server 2014 PowerShell. Para obter mais informações, consulte Get Help SQL Server PowerShell.

Para configurar e usar o provedor do SQL Server PowerShell, consulte o Provedor do SQL Server PowerShell.

O exemplo a seguir define tanto os parâmetros ConnectionModeInSecondaryRole quanto ConnectionModeInPrimaryRole como 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

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

• Ao usar 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	Ligação
  	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 essas 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.

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)

Conteúdo relacionado

• Always On: proposta de valor da secundária legível

• Always On: por que há duas opções para habilitar uma réplica secundária para a carga de trabalho de leitura?

• Always On: configurar uma réplica secundária legível

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

• Always On: 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

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

• Always On: impacto na carga de trabalho primária ao executar a carga de trabalho de relatório na réplica secundária

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

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

• Always On: réplica secundária legível e latência de dados

Consulte Também

Visão geral dos Grupos de Disponibilidade AlwaysOn (SQL Server)
Secundários Ativos: Réplicas Secundárias Legíveis (Grupos de Disponibilidade AlwaysOn)
Sobre Acesso de conexão de cliente a réplicas de disponibilidade (SQL Server)