Configurar o acesso somente leitura a uma réplica secundária de um grupo de disponibilidade Always On
Aplica-se a:
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 Always On no SQL Server usando SQL Server Management Studio, Transact-SQL ou PowerShell.
Para obter informações sobre as implicações de permitir o acesso somente leitura para uma réplica secundária e para uma introdução ao acesso de conexão, confira Sobre o 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 Always On).
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.
Permissões
| Tarefa | Permissões |
|---|---|
| Para configurar réplicas ao criar um grupo de disponibilidade | Requer a associação na função de servidor fixa sysadmin e a permissão de servidor CREATE 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. |
Como usar o SQL Server Management Studio.
Para configurar o acesso em uma réplica de disponibilidade
No Pesquisador de Objetos, conecte-se à instância de servidor que hospeda a réplica primária e expanda a árvore de servidores.
Expanda os nós Alta Disponibilidade AlwaysOn e Grupos de Disponibilidade.
Clique no grupo de disponibilidade cuja réplica você deseja alterar.
Clique com o botão direito do mouse na réplica de disponibilidade e clique em Propriedades.
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 o Transact-SQL
Para configurar o acesso em uma réplica de disponibilidade
Observação
Para obter um exemplo desse procedimento, veja Exemplo (Transact-SQL), mais adiante nesta seção.
Conecte-se à instância de servidor que hospeda a réplica primária.
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 = { NO | 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.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.
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 Tentativa de Aplicativo, consulte Using Connection String Keywords with 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
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.
Altere o diretório (cd) para a instância de servidor que hospeda a réplica primária.
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 ConnectionModeInSecondaryRolesecondary_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 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 de conexão para a função primária, especifique o 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 cmdlet Get-Help no ambiente do SQL Server PowerShell. Para obter mais informações, consulte Get Help 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
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],'UpdateAbility') = N'READ_ONLY'
Para identificar um banco de dados leitura/gravação, especifique READ_WRITE como o valor.
Related Tasks
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
Consulte Também
Visão geral dos Grupos de Disponibilidade AlwaysOn (SQL Server)
Secundárias ativas: réplicas secundárias legíveis (grupos de disponibilidade Always On)
Sobre Acesso de conexão de cliente a réplicas de disponibilidade (SQL Server)
Comentários
Em breve: Ao longo de 2024, eliminaremos os problemas do GitHub como o mecanismo de comentários para conteúdo e o substituiremos por um novo sistema de comentários. Para obter mais informações, consulte https://aka.ms/ContentUserFeedback.
Enviar e exibir comentários de