Usar réplicas somente leitura para descarregar cargas de trabalho de consulta somente leitura

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

Como parte da arquitetura de alta disponibilidade, cada banco de dados único ou banco de dados de pool elástico na camada de serviço Premium e Business Critical é provisionado automaticamente com uma réplica primária de leitura-gravação e uma ou mais réplicas secundárias somente 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. Dessa forma, algumas cargas de trabalho somente leitura podem ser isoladas das cargas de trabalho de leitura-gravação e não afetam seu desempenho. O recurso destina-se aos aplicativos que incluem cargas de trabalho somente leitura separadas logicamente, como análises. 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.

O recurso de expansão de leitura também está disponível na camada de serviço Hyperscale quando pelo menos uma réplica secundária é adicionada. As réplicas nomeadas secundárias de hiperescala fornecem dimensionamento independente, isolamento de acesso, isolamento de carga de trabalho, suporte para vários cenários de expansão de leitura e outros benefícios. Várias réplicas de HA secundárias podem ser usadas para cargas de trabalho somente leitura de balanceamento de carga que exigem mais recursos do que os disponíveis em uma réplica de HA secundária.

A arquitetura de Elevada Disponibilidade dos escalões de serviço Básico, Standard e Fins Gerais não inclui réplicas. O recurso de expansão de leitura não está disponível nessas camadas de serviço. No entanto, ao usar o Banco de Dados SQL do Azure, as réplicas geográficas podem fornecer funcionalidade semelhante nessas camadas de serviço. Ao usar a Instância Gerenciada SQL do Azure e grupos de failover, o ouvinte somente leitura do grupo de failover pode fornecer funcionalidade semelhante, respectivamente.

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

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

Nota

A expansão de leitura está sempre habilitada na camada de serviço Business Critical da Instância Gerenciada SQL 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=ReadOnlyo , o aplicativo será redirecionado para uma réplica somente leitura desse banco de dados ou instância gerenciada. Para obter informações sobre como usar a propriedade, consulte Especificando a intenção do ApplicationIntent 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 na cadeia de conexão SQL, você deverá desabilitar explicitamente a expansão de leitura ao criar o banco de ApplicationIntent 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.

Nota

Os recursos do Repositório de Consultas e do SQL Profiler não são suportados em réplicas somente leitura.

Consistência de dados

As alterações de dados feitas na réplica primária são mantidas em réplicas somente 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 à principal. 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. Da mesma forma, se um aplicativo alterar dados usando uma sessão de leitura-gravação no primário e lê-los imediatamente usando uma sessão somente leitura em uma réplica somente leitura, é possível que as alterações mais recentes 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 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.

Nota

A latência de propagação de dados inclui o tempo necessário para enviar e persistir (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 monitorar a latência de propagação de dados, consulte Monitorar e solucionar problemas de réplica somente leitura.

Conectar-se a uma réplica somente leitura

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

Por exemplo, a cadeia de conexão a seguir conecta o cliente a uma réplica somente leitura (substituindo os itens entre colchetes angulares pelos valores corretos para seu ambiente e soltando os colchetes angulares):

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

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

Selecione Parâmetros de conexão adicionais e digite 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 seu ambiente e soltando os colchetes angulares):

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

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

Verifique se uma conexão é com uma réplica somente leitura

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

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

Nota

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

Monitorar e solucionar problemas de réplicas somente leitura

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 exibições a seguir são comumente usadas para monitoramento de réplicas e solução de problemas:

Nome	Propósito
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 refazer e a taxa de refazer servem como indicadores de latência de propagação de dados na réplica somente 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.

Nota

Os sys.resource_stats e sys.elastic_pool_resource_stats DMVs 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 eventos estendidos

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

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.

Para soltar uma sessão de evento em uma réplica somente leitura, siga estas etapas:

1. Conecte o Pesquisador de Objetos do SSMS ou uma janela de consulta à réplica somente leitura.
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. Solte a sessão na réplica primária, selecionando 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 só de leitura utilizam sempre o nível de isolamento da transação do instantâneo, independentemente do nível de isolamento da transação da sessão e de quaisquer sugestões de consulta. O isolamento de instantâneo usa o controle de versão de linha para evitar o bloqueio de cenários em que os leitores bloqueiam gravadores.

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, "A transação de isolamento de instantâneo falhou 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. Tal não é permitido porque os metadados não têm a mesma versão. Uma atualização simultânea de metadados pode levar a inconsistência se misturada com isolamento de instantâneo."

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

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 o 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 de HA de hiperescala e todas as réplicas geográficas), isso também atrasaria a recuperação do banco de dados se ocorresse um failover, causando um 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."

Nota

Se você receber o erro 3961, 1219 ou 3947 ao executar consultas em uma réplica somente leitura, tente 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.

Gorjeta

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

Habilitar e desabilitar a expansão de leitura para o Banco 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, Standard ou de Uso Geral. O escalamento horizontal de leituras é desativado automaticamente nas bases de dados do Hyperscale configuradas com zero réplicas secundárias.

Para bancos de dados únicos e em pool no Banco de Dados SQL do Azure, você pode desabilitar e rehabilitar a expansão de leitura nas camadas de serviço Premium ou Business Critical usando 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.

Nota

Para bancos de dados únicos e bancos de dados de pool elástico, a capacidade de desabilitar a expansão de leitura é fornecida para compatibilidade com versões anteriores. 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, você pode gerenciar a configuração de expansão de leitura no painel Banco de dados Computação + armazenamento , disponível em Configurações. Usar o portal do Azure para habilitar ou desabilitar a expansão de leitura não está disponível para a Instância Gerenciada SQL do Azure.

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 -ReadScale parâmetro. A desativação da expansão de leitura para a Instância Gerenciada 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

API REST

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 readScale propriedade definida como ou Disabled, como Enabled 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 o tempdb banco de dados em uma réplica somente leitura

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

Usar escalabilidade horizontal de leitura com bancos de dados replicados 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 réplica geográfica pode fornecer várias réplicas adicionais somente leitura para um banco de dados primário de leitura-gravação. Cada réplica geográfica adicional fornece outro conjunto de réplicas somente 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.

Nota

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 recursos em réplicas somente leitura

Segue-se uma lista do comportamento de algumas funcionalidades em réplicas só de leitura:

• A auditoria em réplicas só de leitura é ativada 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.
• O Query Performance Insight depende de dados do Repositório de Consultas, que atualmente não controla a atividade na réplica somente leitura. O Query Performance Insight não mostra consultas executadas na réplica somente leitura.
• O ajuste automático depende do Repositório de Consultas, conforme detalhado no papel 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 de hiperescala.