Configurar o acesso só de leitura a uma réplica secundária de um grupo de disponibilidade Always On

Aplica-se a: SQL Server

Por defeito, tanto o acesso de leitura e escrita como o acesso com intenção de leitura são permitidos na réplica primária, e não são permitidas ligações às réplicas secundárias de um grupo de disponibilidade Always On. Este tópico descreve como configurar o acesso de ligação numa réplica de disponibilidade de um grupo de disponibilidade Always On no SQL Server, utilizando o SQL Server Management Studio, Transact-SQL ou PowerShell.

Para obter informações sobre as implicações de ativar o acesso só de leitura para uma réplica secundária e uma introdução ao acesso de ligação, consulte Acerca do Acesso de Ligação do Cliente às Réplicas de Disponibilidade (SQL Server) e Secundários Ativos: Réplicas Secundárias Legíveis (Grupos de Disponibilidade Always On).

Pré-requisitos e restrições

• Para configurar diferentes acessos de ligação, deve estar ligado à instância do servidor que hospeda a réplica principal.

Permissões

Usando o SQL Server Management Studio

Para configurar o acesso numa réplica de disponibilidade

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

2. Expanda o nó Always On de Alta Disponibilidade e o nó de Grupos de Disponibilidade.

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

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

5. Na caixa de diálogo Propriedades da Réplica de Disponibilidade, pode alterar o acesso de ligação tanto para a função primária como para a função secundária, da seguinte forma:

   • Para a função secundária, selecione um novo valor na lista pendente Secundária legível, da seguinte forma:

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

     Apenas para leitura
     Apenas ligações de leitura são permitidas para bases de dados secundárias desta réplica. A(s) base de dados secundária(s) estão todas disponíveis para acesso de leitura.

     Sim
     Todas as ligações são permitidas nas bases de dados secundárias desta réplica, mas apenas para leitura. A(s) base de dados secundária(s) estão todas disponíveis para acesso de leitura.

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

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

     Permitir ligações de leitura/escrita
     Quando a propriedade Application Intent está definida como ReadWrite ou a propriedade de ligação Application Intent não está definida, a ligação é permitida. Ligações onde a propriedade de ligação Application Intent está definida para Apenas Leitura não são permitidas. Isto pode ajudar a evitar que os clientes liguem por engano uma carga de trabalho de intenção de leitura à réplica primária. Para obter mais informações sobre a propriedade de conexão Application Intent, consulte Usando palavras-chave de cadeia de conexão com o SQL Server Native Client.

Usando Transact-SQL

Para configurar o acesso numa réplica de disponibilidade

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

2. Se estiver a especificar uma réplica para um novo grupo de disponibilidade, utilize a instrução Transact-SQL CREATE AVAILABILITY GROUP. Se estiver a adicionar ou a modificar uma réplica de um grupo de disponibilidade existente, utilize a instrução Transact-SQL ALTER AVAILABILITY GROUP.

   • Para configurar o acesso de ligaçã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
     Não são permitidas ligações diretas a bases de dados secundárias desta réplica. Não estão disponíveis para leitura. Esta é a configuração padrão.

     READ_ONLY
     Apenas são permitidas ligações só de leitura às bases de dados secundárias desta réplica. A(s) base de dados secundária(s) estão todas disponíveis para acesso de leitura.

     TODOS
     São permitidas todas as ligações às bases de dados secundárias desta réplica, mas apenas em modo de leitura. A(s) base de dados secundária(s) estão todas disponíveis para acesso de leitura.

3. Para configurar o acesso à 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
   As conexões em que a propriedade de conexão Application Intent está definida como ReadOnly não são permitidas. Quando a propriedade Application Intent está definida como ReadWrite ou a propriedade de ligação Application Intent não está definida, a ligação é permitida. Para obter mais informações sobre a propriedade de conexão Application Intent, consulte Usando palavras-chave de cadeia de conexão com o SQL Server Native Client.

   TODOS
   Todas as conexões são permitidas aos bancos de dados na réplica primária. Esta é a configuração padrão.

Exemplo (Transact-SQL)

O exemplo seguinte adiciona uma réplica secundária a um grupo de disponibilidade chamado AG2. Uma instância de servidor autónoma, COMPUTER03\HADR_INSTANCE, é especificada para alojar a nova réplica de disponibilidade. Esta réplica foi configurada para permitir apenas conexões de leitura e escrita para a função primária e apenas conexões destinadas à leitura para a 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  

Utilização do PowerShell

Para configurar o acesso numa réplica de disponibilidade

1. Muda o diretório (cd) para a instância do servidor que hospeda a réplica principal.

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 à ligação para o papel secundário, especifique o parâmetro ConnectionModeInSecondaryRolesecondary_role_keyword , onde secondary_role_keyword é igual a um dos seguintes valores:

     AllowNoConnections
     Não são permitidas ligações diretas às bases de dados na réplica secundária e as bases de dados não estão disponíveis para acesso de leitura. Esta é a configuração padrão.

     PermitirApenasLigaçõesComIntençãoDeLeitura
     As ligações são permitidas apenas às bases 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 de cadeia de conexão com o SQL Server Native Client.

     AllowAllConnections
     Todas as ligações são permitidas para as bases de dados da réplica secundária para acesso só de leitura.

   • Para configurar o acesso à ligação para o papel principal, especifique ConnectionModeInPrimaryRoleprimary_role_keyword, onde primary_role_keyword é igual a um dos seguintes valores:

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

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

   Note

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

Para configurar e usar o provedor do SQL Server PowerShell

• Provedor do SQL Server PowerShell

Exemplo (PowerShell)

O exemplo seguinte define tanto os parâmetros ConnectionModeInSecondaryRole como 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  
  

Passos seguintes: Após configurar o acesso só de leitura para uma réplica de disponibilidade

Acesso apenas de leitura a 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 Somente leitura -K .

• Para permitir que aplicações cliente se liguem a réplicas secundárias legíveis:

Fatores que podem afetar gatilhos e empregos após um failover

Se tiver triggers e jobs que falham quando são executados numa base de dados secundária não legível ou numa base de dados secundária legível, precisa de criar scripts para que os triggers e jobs verifiquem, numa determinada réplica, se a base de dados é primária ou uma base de dados secundária legível. Para obter esta informação, utilize a função DATABASEPROPERTYEX para devolver a propriedade Updateability da base de dados. Para identificar uma base de dados só de leitura, especifique READ_ONLY como valor, da seguinte forma:

DATABASEPROPERTYEX([db name],'UpdateAbility') = N'READ_ONLY'  

Para identificar uma base de dados de leitura-escrita, especifique READ_WRITE como valor.

Tarefas relacionadas

• Configurar o roteamento de Read-Only para um grupo de disponibilidade (SQL Server)

• Criar ou Configurar um Listener de Grupo de Disponibilidade (SQL Server)

Conteúdo relacionado

• Always On: Proposta de valor do secundário legível

• Always On: Porque é que existem duas opções para ativar uma réplica secundária para carga de trabalho de leitura?

• Always On: Configurar Réplica Secundária Legível

• Always On: Acabei de ativar o Readable Secondary, mas a minha consulta está bloqueada?

• Sempre Ligado: Disponibilizar as estatísticas mais recentes no Secundário Legível, na base de dados Read-Only e no Snapshot da Base de Dados

• Always On: Desafios com estatísticas sobre a base de dados só de leitura, o instantâneo da base de dados e a réplica secundária

• Always On: Impacto na carga de trabalho principal ao executar a carga de trabalho de relatórios na réplica secundária

• Sempre Ligado: Impacto da carga de trabalho de relatórios de mapeamento no isolamento legível do secundário ao instantâneo

• Sempre Ligado: Minimizar o bloqueio do thread REDO ao executar carga de trabalho de reporte na Réplica Secundária

• Sempre Ligado: Secundário legível e latência de dados

Ver também

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