Usar réplicas somente leitura para aliviar as cargas de trabalho de consulta somente leitura

Aplica-se a:Banco de Dados SQL do AzureInstância Gerenciada SQL do Azure

Como parte da arquitetura de alta disponibilidade , cada base de dados individual ou base de dados de pool elástico na camada de serviço Premium e Business Critical é automaticamente provisionada com uma réplica primária de leitura e gravação e uma ou mais réplicas secundárias de apenas leitura. As réplicas secundárias são provisionadas com o mesmo tamanho de computação que a réplica primária. O recurso de expansão de leitura permite descarregar cargas de trabalho somente leitura usando a capacidade de computação de uma das réplicas somente leitura, em vez de executá-las na réplica de leitura-gravação. Deste modo, algumas cargas de trabalho apenas de leitura podem ser isoladas das cargas de trabalho de leitura e gravação, e não afetam o seu desempenho. O recurso destina-se às aplicações que incluem cargas de trabalho somente leitura, logicamente separadas, como a análise. Nos níveis de serviço Premium e Business Critical, os aplicativos podem obter benefícios de desempenho usando essa capacidade adicional sem custo extra.

A funcionalidade de escala horizontal de leitura também está disponível no nível de serviço Hyperscale quando é adicionada pelo menos uma réplica secundária . As réplicas secundárias de hiperescala nomeadas fornecem dimensionamento independente, isolamento de acesso, isolamento de carga de trabalho, suporte para vários cenários de aumento de leitura e outros benefícios. Várias réplicas de HA secundárias podem ser usadas para balancear cargas de trabalho de leitura apenas que requerem mais recursos do que os disponíveis em uma única réplica de HA secundária.

A arquitetura de alta disponibilidade das camadas de serviço Basic, Standard e General Purpose não inclui réplicas. O recurso de expansão de leitura não está disponível nesses níveis de serviço. No entanto, ao usar o Banco de Dados SQL do Azure, réplicas geográficas podem fornecer funcionalidade semelhante nessas camadas de serviço. Ao utilizar a Instância Gerida do SQL do Azure e os grupos de failover, o ouvinte de leitura apenas do grupo de failover pode oferecer uma funcionalidade semelhante.

O diagrama a seguir ilustra o recurso para bancos de dados Premium e Business Critical e instâncias gerenciadas.

O recurso de escala de leitura é ativado por padrão nos novos bancos de dados Premium, Business Critical e Hyperscale.

Observação

O escalonamento de leitura está sempre ativado na camada de serviço Business Critical do SQL Managed Instance e para bancos de dados Hyperscale com pelo menos uma réplica secundária.

Se a cadeia de conexão SQL estiver configurada com ApplicationIntent=ReadOnly, a aplicação será redirecionada para uma réplica só de leitura desse banco de dados ou instância gerida. Para obter informações sobre como usar a propriedade ApplicationIntent, consulte Especificando a intenção do aplicativo.

Somente para o Banco de Dados SQL do Azure, se desejar garantir que o aplicativo se conecte à réplica primária, independentemente da configuração de ApplicationIntent na cadeia de conexão SQL, você deverá desabilitar explicitamente a expansão de leitura ao criar o banco de dados ou ao alterar sua configuração. Por exemplo, se você atualizar seu banco de dados da camada Standard ou de Uso Geral para Premium ou Business Critical e quiser garantir que todas as suas conexões continuem indo para a réplica principal, desative a expansão de leitura. Para obter detalhes sobre como desativá-lo, consulte Habilitar e desabilitar a expansão de leitura.

Observação

As funcionalidades do Repositório de Consultas e do SQL Profiler não são suportadas em réplicas somente leitura.

Consistência dos dados

As alterações de dados feitas na réplica primária são persistidas em réplicas somente de leitura de forma síncrona ou assíncrona, dependendo do tipo de réplica. No entanto, para todos os tipos de réplica, as leituras de uma réplica somente leitura são sempre assíncronas em relação à réplica primária. Em uma sessão conectada a uma réplica somente leitura, as leituras são sempre transacionalmente consistentes. Como a latência de propagação de dados é variável, réplicas diferentes podem retornar dados em pontos no tempo ligeiramente diferentes em relação ao primário e uns aos outros. Se uma réplica somente leitura ficar indisponível e uma sessão se reconectar, ela poderá se conectar a uma réplica que esteja em um momento diferente da réplica original. De forma semelhante, se uma aplicação alterar dados usando uma sessão de leitura e escrita no primário e lê-los imediatamente usando uma sessão de leitura única em uma réplica de leitura única, é possível que as últimas alterações não sejam imediatamente visíveis.

A latência típica de propagação de dados entre a réplica primária e as réplicas somente leitura varia no intervalo de dezenas de milissegundos a segundos de um só dígito. No entanto, não há limite superior fixo na latência de propagação de dados. Condições como alta utilização de recursos na réplica podem aumentar substancialmente a latência. Os aplicativos que exigem consistência de dados garantida entre sessões ou exigem que os dados confirmados sejam legíveis imediatamente devem usar a réplica principal.

Observação

A latência de propagação de dados inclui o tempo necessário para enviar e manter (se aplicável) registros de log para uma réplica secundária. Também inclui o tempo necessário para refazer (aplicar) esses registros de log às páginas de dados. Para garantir a consistência dos dados, as alterações não ficam visíveis até que o registro de log de confirmação de transações seja aplicado. Quando a carga de trabalho usa transações maiores, a latência efetiva de propagação de dados é aumentada.

Para monitorizar a latência de propagação de dados, consulte Monitorizar e resolver problemas de réplica somente leitura.

Conectar-se a uma réplica de leitura

Quando se habilita a expansão de leitura para um banco de dados, a opção ApplicationIntent na string de conexão fornecida pelo cliente determina se a conexão é direcionada para a réplica de escrita ou para uma réplica de leitura apenas. Especificamente, se o valor ApplicationIntent for ReadWrite (o valor padrão), a conexão será direcionada à réplica de leitura e escrita. Isso é idêntico ao comportamento quando ApplicationIntent não está incluído na cadeia de conexão. Se o valor ApplicationIntent for ReadOnly, a conexão será encaminhada para uma réplica somente leitura.

Por exemplo, a seguinte cadeia de conexão conecta o cliente a uma réplica de leitura única (substituindo os itens entre parênteses angulares pelos valores corretos para o seu ambiente e removendo os parênteses angulares):

Server=tcp:<server>.database.windows.net;Database=<mydatabase>;ApplicationIntent=ReadOnly;User ID=<myLogin>;Password=<password>;Trusted_Connection=False; Encrypt=True;

Para se conectar a uma réplica de leitura apenas com o SQL Server Management Studio (SSMS), selecione Opções de

Selecione Parâmetros de Conexão Adicionais, insira ApplicationIntent=ReadOnly e, em seguida, selecione Conectar

Uma das seguintes cadeias de conexão conecta o cliente a uma réplica de leitura-gravação (substituindo os itens entre colchetes angulares pelos valores corretos para o seu ambiente e removendo os colchetes angulares):

Server=tcp:<server>.database.windows.net;Database=<mydatabase>;ApplicationIntent=ReadWrite;User ID=<myLogin>;Password=<password>;Trusted_Connection=False; Encrypt=True;

Server=tcp:<server>.database.windows.net;Database=<mydatabase>;User ID=<myLogin>;Password=<password>;Trusted_Connection=False; Encrypt=True;

Verifique se a conexão é para uma réplica somente de leitura

Você pode verificar se está conectado a uma réplica somente leitura executando a consulta a seguir no contexto da sua base de dados. Ele retorna READ_ONLY quando você está conectado a uma réplica somente leitura.

SELECT DATABASEPROPERTYEX(DB_NAME(), 'Updateability');

Observação

Nas camadas de serviço Premium e Business Critical, apenas uma das réplicas somente leitura é acessível a qualquer momento. O Hyperscale suporta várias réplicas de leitura.

Monitorar e solucionar problemas de réplicas somente leitura

Você tem várias maneiras de monitorizar réplicas somente leitura, incluindo: VMDs, eventos estendidos e monitor de base de dados (visualização).

Quando conectados a uma réplica somente leitura, os DMVs (Dynamic Management Views) refletem o estado da réplica e podem ser consultados para fins de monitoramento e solução de problemas. O mecanismo de banco de dados fornece várias exibições para expor uma ampla variedade de dados de monitoramento.

As vistas a seguir são frequentemente usadas para monitorização de réplicas e solução de problemas.

Nome	Finalidade
sys.dm_db_resource_stats	Fornece métricas de utilização de recursos para a última hora, incluindo CPU, E/S de dados e utilização de gravação de log em relação aos limites de objetivo de serviço.
sys.dm_os_wait_stats	Fornece estatísticas de espera agregadas para a instância do mecanismo de banco de dados.
sys.dm_database_replica_states	Fornece estatísticas de estado de funcionamento da réplica e de sincronização. O tamanho da fila de redos e a taxa de redos servem como indicadores da latência de propagação de dados na réplica apenas de leitura.
sys.dm_os_performance_counters	Fornece contadores de desempenho do mecanismo de banco de dados.
sys.dm_exec_query_stats	Fornece estatísticas de execução por consulta, como número de execuções, tempo de CPU usado, etc.
sys.dm_exec_query_plan()	Fornece planos de consulta em cache.
sys.dm_exec_sql_text()	Fornece texto de consulta para um plano de consulta em cache.
sys.dm_exec_query_profiles	Fornece o progresso da consulta em tempo real enquanto as consultas estão em execução.
sys.dm_exec_query_plan_stats()	Fornece o último plano de execução real conhecido, incluindo estatísticas de tempo de execução para uma consulta.
sys.dm_io_virtual_file_stats()	Fornece IOPS de armazenamento, taxa de transferência e estatísticas de latência para todos os arquivos de banco de dados.

Observação

Os DMVs sys.resource_stats e sys.elastic_pool_resource_stats no banco de dados lógico master retornam dados de utilização de recursos da réplica primária.

Monitore réplicas somente leitura com Extended Events

Uma sessão de evento estendida não pode ser criada quando conectada a uma réplica somente leitura. No entanto, na Base de Dados SQL do Azure e na Instância Gerida SQL do Azure, as definições de sessões de Evento Estendido com escopo de base de dados criadas e alteradas na réplica primária são replicadas para réplicas de leitura apenas, incluindo réplicas geográficas, e capturam eventos nas réplicas de leitura apenas.

No Banco de Dados SQL do Azure, uma sessão de evento estendida em uma réplica somente leitura baseada em uma definição de sessão da réplica primária pode ser iniciada e interrompida independentemente da sessão na réplica primária.

Na Instância SQL Gerida do Azure, para iniciar um rastreamento em uma réplica de somente leitura, deve primeiro iniciar o rastreamento na réplica primária antes de poder iniciar o rastreamento na réplica de somente leitura. Se você não iniciar primeiro o rastreamento na réplica primária, receberá o seguinte erro ao tentar iniciar o rastreamento na réplica somente leitura:

Msg 3906, Nível 16, Estado 2, Linha 1, Falha ao atualizar o banco de dados "master" porque o banco de dados é somente leitura.

Depois de iniciar o rastreamento primeiro na réplica primária e, em seguida, na réplica somente leitura, você pode interromper o rastreamento na réplica primária.

Para eliminar uma sessão de eventos numa réplica de apenas leitura, siga estas etapas:

1. Conecte o Explorador de Objetos do SSMS ou uma janela de consulta à réplica de leitura apenas.
2. Pare a sessão na réplica somente leitura, selecionando Parar Sessão no menu de contexto da sessão no Pesquisador de Objetos ou executando ALTER EVENT SESSION [session-name-here] ON DATABASE STATE = STOP; em uma janela de consulta.
3. Conecte o Pesquisador de Objetos ou uma janela de consulta à réplica primária.
4. Elimine a sessão na réplica primária, através de selecionar Excluir no menu de contexto da sessão ou executando DROP EVENT SESSION [session-name-here] ON DATABASE;

Nível de isolamento de transação em réplicas somente leitura

As transações em réplicas somente leitura sempre usam o nível de isolamento de transação snapshot , independentemente do nível de isolamento de transação da sessão e de quaisquer sugestões de consulta. O isolamento de instantâneo usa o controlo de versão de linha para evitar cenários de bloqueio em que os escritores bloqueiam leitores.

Em casos raros, se uma transação de isolamento de instantâneo acessar metadados de objeto que foram modificados em outra transação simultânea, ela poderá receber o erro 3961, "Falha na transação de isolamento de instantâneo no banco de dados '%.*ls' porque o objeto acessado pela instrução foi modificado por uma instrução DDL em outra transação simultânea desde o início desta transação. Não é permitido porque os metadados não são versionados. Uma atualização simultânea dos metadados pode levar a uma inconsistência se misturada com o isolamento de instantâneos.

Consultas de longa execução em réplicas de leitura única

As consultas executadas em réplicas somente leitura precisam acessar metadados para os objetos referenciados na consulta (tabelas, índices, estatísticas, etc.) Em casos raros, se os metadados do objeto forem modificados na réplica primária enquanto uma consulta mantiver um bloqueio no mesmo objeto na réplica somente leitura, a consulta poderá bloquear processo que aplica alterações da réplica primária à réplica somente leitura. Se essa consulta fosse executada por muito tempo, isso faria com que a réplica somente leitura ficasse significativamente fora de sincronia com a réplica primária. Para réplicas que são potenciais destinos de failover (réplicas secundárias nas camadas de serviço Premium e Business Critical, réplicas HA Hyperscale e todas as réplicas geográficas), isto também atrasaria a recuperação da base de dados no caso de ocorrência de um failover, resultando num tempo de inatividade maior do que o esperado.

Se uma consulta de longa execução em uma réplica somente leitura causar, direta ou indiretamente, esse tipo de bloqueio, ela poderá ser encerrada automaticamente para evitar latência excessiva de dados e potencial impacto na disponibilidade do banco de dados. A sessão recebe o erro 1219, "Sua sessão foi desconectada devido a uma operação DDL de alta prioridade", ou o erro 3947, "A transação foi abortada porque o cálculo secundário não conseguiu recuperar o atraso. Tente novamente a transação."

Observação

Se receberes o erro 3961, 1219 ou 3947 ao executar consultas em uma réplica de leitura única, tenta novamente a consulta. Como alternativa, evite operações que modifiquem metadados de objeto (alterações de esquema, manutenção de índice, atualizações de estatísticas, etc.) na réplica primária enquanto consultas de longa execução são executadas em réplicas secundárias.

Dica

Nas camadas de serviço Premium e Business Critical, quando conectadas a uma réplica apenas de leitura, as colunas redo_queue_size e redo_rate na DMV sys.dm_database_replica_states podem ser usadas para monitorizar o processo de sincronização de dados, servindo de indicadores da latência de propagação de dados na réplica apenas de leitura.

Habilitar e desativar o escalonamento de leitura para a base de dados SQL

Para a Instância Gerenciada SQL, a expansão de leitura é habilitada automaticamente na camada de serviço Crítica para os Negócios e não está disponível na camada de serviço de Propósito Geral. Não é possível desativar e reativar a expansão de leitura.

Para o Banco de Dados SQL, a expansão de leitura é habilitada por padrão nas camadas de serviço Premium, Business Critical e Hyperscale. A expansão de leitura não pode ser habilitada nas camadas de serviço Básico, Padrão ou de Uso Geral. A leitura em larga escala é desativada automaticamente em os bancos de dados Hyperscale quando configurados com zero réplicas secundárias.

Para bases de dados únicas e em pool no Banco de Dados SQL do Azure, pode-se desativar e reativar o escalonamento de leitura nas camadas de serviço Premium ou Business Critical utilizando o portal do Azure e o Azure PowerShell. Essas opções não estão disponíveis para a Instância Gerenciada SQL, pois a expansão de leitura não pode ser desabilitada.

Observação

Para bases de dados individuais e bases de dados de pool elástico, a capacidade de desativar a expansão de leitura é fornecida para compatibilidade retroativa. A expansão de leitura não pode ser desabilitada em instâncias gerenciadas críticas para os negócios.

Portal do Azure

Para o Banco de Dados SQL do Azure, pode gerir a configuração de expansão de leitura no painel de base de dados Computação e Armazenamento, disponível em Configurações. Usar o portal do Azure para habilitar ou desabilitar o read scale-out não está disponível para o Azure SQL Managed Instance.

PowerShell

Importante

O módulo PowerShell Azure Resource Manager ainda é suportado, mas todo o desenvolvimento futuro é para o módulo Az.Sql. O módulo Azure Resource Manager continuará a receber correções de bugs até, pelo menos, dezembro de 2020. Os argumentos para os comandos no módulo Az e nos módulos do Azure Resource Manager são substancialmente idênticos. Para obter mais informações sobre sua compatibilidade, consulte Apresentando o novo módulo Az do Azure PowerShell.

O gerenciamento da expansão de leitura no Azure PowerShell requer a versão de dezembro de 2016 do Azure PowerShell ou mais recente. Para obter a versão mais recente do PowerShell, consulte Azure PowerShell.

No Banco de Dados SQL do Azure, você pode desabilitar ou rehabilitar a expansão de leitura no Azure PowerShell invocando o cmdlet Set-AzSqlDatabase e passando o valor desejado (Enabled ou Disabled) para o parâmetro -ReadScale. A desativação da expansão de leitura para a Instância Gerenciada do SQL não está disponível.

Para desativar a expansão de leitura em um banco de dados existente (substituindo os itens entre colchetes angulares pelos valores corretos para seu ambiente e soltando os colchetes angulares):

Set-AzSqlDatabase -ResourceGroupName <resourceGroupName> -ServerName <serverName> -DatabaseName <databaseName> -ReadScale Disabled

Para desativar a expansão de leitura em um novo banco de dados (substituindo os itens entre colchetes angulares pelos valores corretos para seu ambiente e soltando os colchetes angulares):

New-AzSqlDatabase -ResourceGroupName <resourceGroupName> -ServerName <serverName> -DatabaseName <databaseName> -ReadScale Disabled -Edition Premium

Para reativar a expansão de leitura em um banco de dados existente (substituindo os itens entre colchetes angulares pelos valores corretos para seu ambiente e soltando os colchetes angulares):

Set-AzSqlDatabase -ResourceGroupName <resourceGroupName> -ServerName <serverName> -DatabaseName <databaseName> -ReadScale Enabled

REST API

Para criar um banco de dados com a expansão de leitura desabilitada ou para alterar a configuração de um banco de dados existente, use o seguinte método com a propriedade readScale definida como Enabled ou Disabled, como na solicitação de exemplo a seguir.

Method: PUT
URL: https://management.azure.com/subscriptions/{SubscriptionId}/resourceGroups/{GroupName}/providers/Microsoft.Sql/servers/{ServerName}/databases/{DatabaseName}?api-version= 2014-04-01-preview
Body: {
   "properties": {
      "readScale":"Disabled"
   }
}

Para obter mais informações, consulte Bancos de dados - Criar ou atualizar.

Usar a base de dados tempdb numa réplica de apenas leitura

O banco de dados tempdb na réplica primária não é replicado para as réplicas somente leitura. Cada réplica tem seu próprio banco de dados tempdb que é criado quando a réplica é criada. Isso garante que tempdb seja atualizável e possa ser modificado durante a execução da consulta. Caso a sua carga de trabalho de leitura apenas dependa do uso de objetos tempdb, deverá criar esses objetos como parte integrante da mesma carga de trabalho, enquanto estiver ligado a uma réplica de leitura apenas.

Usar leitura escalável com bases de dados replicadas geograficamente

Os bancos de dados secundários replicados geograficamente têm a mesma arquitetura de alta disponibilidade que os bancos de dados primários. Se você estiver se conectando ao banco de dados secundário replicado geograficamente com a expansão de leitura habilitada, suas sessões com ApplicationIntent=ReadOnly serão roteadas para uma das réplicas de alta disponibilidade da mesma forma que são roteadas no banco de dados primário gravável. As sessões sem ApplicationIntent=ReadOnly são roteadas para a réplica primária do secundário replicado geograficamente, que também é somente leitura.

Dessa forma, a criação de uma geo-replica pode fornecer várias réplicas adicionais apenas de leitura para um banco de dados primário de leitura-gravação. Cada réplica geográfica adicional fornece um novo conjunto de réplicas só de leitura. As réplicas geográficas podem ser criadas em qualquer região do Azure, incluindo a região do banco de dados primário.

Observação

Não há round-robin automático ou qualquer outro roteamento com balanceamento de carga entre as réplicas de um banco de dados secundário replicado geograficamente, com exceção de uma réplica geográfica Hyperscale com mais de uma réplica HA. Nesse caso, as sessões com intenção somente leitura são distribuídas por todas as réplicas HA de uma réplica geográfica.

Suporte a funcionalidades em réplicas de leitura única

Segue-se uma lista do comportamento de algumas características em réplicas de leitura apenas:

• As auditorias em réplicas somente leitura são habilitadas automaticamente. Para obter mais informações sobre a hierarquia das pastas de armazenamento, convenções de nomenclatura e formato de log, consulte Formato de log de auditoria do banco de dados SQL.
• do Query Performance Insight depende de dados do Query Store, que atualmente não regista a atividade na réplica em modo de leitura apenas. O Query Performance Insight não mostra consultas executadas na réplica somente leitura.
• O ajuste automático depende do Repositório de Consultas, como detalhado no documento de ajuste automático . O ajuste automático só funciona para cargas de trabalho em execução na réplica principal.

Próximos passos

• Para obter informações sobre a oferta de hiperescala do Banco de dados SQL, consulte camada de serviço Hyperscale.