Compartilhar via


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.

Ícone de seta usado com o link Voltar ao Início[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.

Ícone de seta usado com o link Voltar ao Início[Início]

Usando Transact-SQL

Para configurar o acesso em uma réplica de disponibilidade

ObservaçãoObservaçã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

Ícone de seta usado com o link Voltar ao Início[Início]

Usando o PowerShell

Para configurar o acesso em uma réplica de disponibilidade

ObservaçãoObservaçã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çãoObservaçã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

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

Ícone de seta usado com o link Voltar ao Início[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

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.

Ícone de seta usado com o link Voltar ao Início[Início]

Tarefas relacionadas

Ícone de seta usado com o link Voltar ao Início[Início]

Conteúdo relacionado

Ícone de seta usado com o link Voltar ao Início[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)