Partilhar via

Descarregar carga de trabalho somente leitura para réplica secundária de um grupo de disponibilidade Always On

Aplica-se a:SQL Server

Os recursos secundários ativos dos grupos de disponibilidade Always On incluem suporte para acesso somente leitura a uma ou mais réplicas secundárias (réplicas secundárias legíveis). Uma réplica secundária legível pode estar no modo de disponibilidade de confirmação síncrona ou no modo de disponibilidade de confirmação assíncrona. Uma réplica secundária legível permite acesso somente leitura a todos os seus bancos de dados secundários. No entanto, os bancos de dados secundários legíveis não são definidos como somente leitura. São dinâmicos. Um determinado banco de dados secundário é alterado à medida que as alterações no banco de dados primário correspondente são aplicadas ao banco de dados secundário. Para uma réplica secundária típica, os dados, incluindo tabelas otimizadas para memória durável, nos bancos de dados secundários estão em tempo quase real. Além disso, os índices de texto completo são sincronizados com os bancos de dados secundários. Em muitas circunstâncias, a latência de dados entre um banco de dados primário e o banco de dados secundário correspondente é de apenas alguns segundos.

As configurações de segurança que ocorrem nos bancos de dados primários são mantidas nos bancos de dados secundários. Isso inclui usuários, funções de banco de dados e funções de aplicativos, juntamente com suas respetivas permissões e criptografia de dados transparente (TDE), se habilitada no banco de dados primário.

Observação

Embora não seja possível gravar dados em bancos de dados secundários, você pode gravar em bancos de dados de leitura-gravação na instância do servidor que hospeda a réplica secundária, incluindo bancos de dados de usuário e bancos de dados do sistema, como tempdb.

Os grupos de disponibilidade Always On também suportam o redirecionamento das solicitações de conexão com intento de leitura para uma réplica secundária de leitura (roteamento somente leitura). Para obter informações sobre o roteamento de leitura única, consulte Usar um ouvinte para ligar-se a uma réplica secundária Read-Only (roteamentoRead-Only).

Benefícios

Direcionar conexões somente leitura para réplicas secundárias legíveis oferece os seguintes benefícios:

  • Transfere as cargas de trabalho secundárias somente leitura da réplica principal, conservando os seus recursos para cargas de trabalho de missão crítica. Se tiver uma carga de trabalho de leitura crítica ou uma carga de trabalho que não pode tolerar latência, deverá executá-la no primário.

  • Melhora o retorno sobre o investimento para os sistemas que hospedam réplicas secundárias legíveis.

Além disso, secundários legíveis oferecem um suporte robusto para operações de leitura apenas, conforme segue:

  • Estatísticas temporárias automáticas em bases de dados secundárias legíveis otimizam consultas apenas de leitura em tabelas baseadas em disco. Para tabelas com otimização de memória, as estatísticas ausentes são criadas automaticamente. No entanto, não há atualização automática de estatísticas obsoletas. Você precisará atualizar manualmente as estatísticas na réplica primária. Para obter mais informações, consulte Estatísticas para bancos de dados do Read-Only Access, mais adiante neste tópico.

  • As tarefas de leitura exclusiva para tabelas baseadas em disco usam a versão de linha para remover a contenção de bloqueio nos bancos de dados secundários. Todas as consultas executadas nas bases de dados secundárias são automaticamente mapeadas para o nível de isolamento de transação de instantâneo, mesmo quando outros níveis de isolamento de transação são definidos explicitamente. Além disso, todas as dicas de bloqueio são ignoradas. Isso elimina a contenção leitor/escritor.

  • Cargas de trabalho somente de leitura para tabelas com otimização de memória e duráveis acessam os dados exatamente da mesma maneira que são acessados no banco de dados primário, usando procedimentos armazenados nativos ou interoperabilidade com SQL com as mesmas limitações de nível de isolamento de transação (consulte Níveis de isolamento no Mecanismo de Banco de Dados). A carga de trabalho de relatórios ou consultas somente leitura em execução na réplica primária pode ser executada na réplica secundária sem exigir alterações. Da mesma forma, uma carga de trabalho de relatório ou consultas somente leitura em execução em uma réplica secundária podem ser executadas na réplica primária sem exigir alterações. Semelhante às tabelas em disco, todas as consultas realizadas nos bancos de dados secundários são automaticamente mapeadas para o nível de transação de isolamento instantâneo, mesmo quando outros níveis de isolamento de transação são definidos explicitamente.

  • As operações DML são permitidas em variáveis de tabela, tanto para tipos de tabela baseados em disco como otimizados para memória, na réplica secundária.

Pré-requisitos para o Grupo de Disponibilidade

  • Réplicas secundárias legíveis (obrigatórias)

    O administrador de base de dados precisa configurar uma ou mais réplicas para que, ao serem executadas no papel secundário, permitam todas as conexões (só para leitura) ou apenas conexões de intenção de leitura.

    Observação

    Opcionalmente, o administrador da base de dados pode configurar qualquer uma das réplicas de disponibilidade para excluir ligações só de leitura quando funcionar na função primária.

    Para obter mais informações, consulte Sobre o acesso de conexão de cliente a réplicas de disponibilidade (SQL Server).

    Advertência

    Somente réplicas que estão na mesma compilação principal do SQL Server serão legíveis. Consulte Noções básicas de atualização contínua para obter mais informações.

  • Ouvinte do grupo de disponibilidade

    Para oferecer suporte ao roteamento somente leitura, um grupo de disponibilidade deve possuir um ouvinte de grupo de disponibilidade. O cliente somente leitura deve direcionar suas solicitações de conexão para esse ouvinte e a cadeia de conexão do cliente deve especificar a intenção do aplicativo como "somente leitura". Ou seja, devem ser solicitações de conexão com intenção de leitura.

  • Roteamento somente leitura

    O roteamento somente leitura refere-se à capacidade do SQL Server de rotear solicitações de conexão entrantes de intenção de leitura, que são direcionadas a um escutador de grupo de disponibilidade, para uma réplica secundária disponível e legível. Os pré-requisitos para roteamento somente leitura são os seguintes:

    • Para oferecer suporte ao roteamento somente leitura, uma réplica secundária legível requer uma URL de roteamento somente leitura. Essa URL entra em vigor somente quando a réplica local está sendo executada sob a função secundária. A URL de roteamento somente leitura deve ser especificada em uma base de réplica por réplica, conforme necessário. Cada URL de roteamento somente leitura é usado para rotear solicitações de conexão com intenção de leitura para uma réplica secundária legível específica. Normalmente, a cada réplica secundária legível é atribuída uma URL de encaminhamento apenas para leitura.

    • Cada réplica de disponibilidade que deve oferecer suporte ao roteamento em modo de leitura quando é a réplica primária precisa de uma lista de roteamento em modo de leitura. Uma determinada lista de roteamento somente leitura entra em vigor somente quando a réplica local está sendo executada sob a função principal. Essa lista deve ser especificada réplica a réplica, conforme necessário. Normalmente, cada lista de roteamento somente leitura conteria todas as URLs de roteamento somente leitura, com a URL da réplica local no final da lista.

      Observação

      As solicitações de conexão cujo objetivo é a leitura podem ser distribuídas entre réplicas de forma balanceada. Para obter mais informações, consulte Configurar o balanceamento de carga em réplicas somente leitura.

    Para mais informações, veja Configurar o Roteamento Read-Only para um Grupo de Disponibilidade (SQL Server).

Observação

Para obter informações sobre os ouvintes de grupos de disponibilidade e sobre o roteamento em modo de leitura, consulte Ouvintes de Grupos de Disponibilidade, Conectividade de Cliente e Failover de Aplicação (SQL Server).

Limitações e Restrições

Algumas operações não são totalmente suportadas, como se segue:

  • Assim que uma réplica legível estiver habilitada para leitura, ela poderá começar a aceitar conexões com seus bancos de dados secundários. No entanto, se existirem transações ativas em um banco de dados primário, as versões de linha não estarão totalmente disponíveis no banco de dados secundário correspondente. Todas as transações ativas que existiam na réplica primária quando a réplica secundária foi configurada devem ser confirmadas ou revertidas. Até que esse processo seja concluído, o mapeamento de nível de isolamento de transação no banco de dados secundário está incompleto e as consultas são temporariamente bloqueadas.

    Advertência

    A execução de transações longas afeta o número de linhas versionadas mantidas, tanto para tabelas baseadas em disco quanto com otimização de memória.

  • Num banco de dados secundário otimizado para memória, embora as versões de linha sejam sempre geradas para tabelas otimizadas, as consultas são bloqueadas até que todas as transações ativas que existiam na réplica primária, quando a réplica secundária foi habilitada para leitura, estejam completas. Isso garante que as tabelas baseadas em disco e com otimização de memória estejam disponíveis para a carga de trabalho de relatórios e consultas somente leitura ao mesmo tempo.

  • O controle de alterações e a captura de dados de alterações não são suportados em bancos de dados secundários que pertencem a uma réplica secundária legível:

    • O controle de alterações é explicitamente desabilitado em bancos de dados secundários.

    • A Captura de Dados de Alteração não pode ser habilitada somente em um banco de dados de réplica secundário. A Captura de Dados de Alteração pode ser habilitada no banco de dados de réplica primária e as alterações podem ser lidas nas tabelas CDC usando as funções no banco de dados de réplica secundária.

  • Como as operações de leitura são mapeadas para o nível de transação de isolamento por instantâneo, a limpeza de registros fantasmas na réplica primária pode ser bloqueada por transações em uma ou mais réplicas secundárias. A tarefa de limpeza de registros fantasmas limpará automaticamente os registros fantasmas para tabelas baseadas em disco na réplica primária quando eles não forem mais necessários para nenhuma réplica secundária. Isso é semelhante ao que é feito quando você executa transações na réplica primária. No caso extremo no banco de dados secundário, necessitarás interromper uma consulta de leitura de longa duração que está a bloquear a eliminação de registos fantasmas. Observe que o processo de limpeza fantasma pode ser bloqueado se a réplica secundária for desconectada ou quando a movimentação de dados for suspensa no banco de dados secundário. Os registros fantasmas usam espaço físico em um arquivo de dados, isso pode causar problemas de reutilização de espaço, consulte a limpeza de fantasmas para obter mais informações. Esse estado também impede o truncamento de log, portanto, se esse estado persistir, recomendamos que você remova esse banco de dados secundário do grupo de disponibilidade. Não há nenhum problema de limpeza de registos fantasma com tabelas otimizadas para memória porque as versões de linha são mantidas na memória e são independentes das versões de linha na réplica primária.

  • A operação DBCC SHRINKFILE em arquivos que contêm tabelas baseadas em disco pode falhar na réplica primária se o arquivo contiver registros fantasmas que ainda são necessários em uma réplica secundária.

  • A partir do SQL Server 2014 (12.x), réplicas secundárias legíveis podem permanecer online mesmo quando a réplica primária está offline devido a uma ação do usuário ou uma falha, por exemplo, a sincronização foi suspensa devido a um comando do usuário ou uma falha, ou uma réplica está resolvendo o status devido ao WSFC estar offline. No entanto, o roteamento somente leitura não funciona nessa situação porque o ouvinte do grupo de disponibilidade também está offline. Os clientes devem conectar-se diretamente às réplicas secundárias de leitura para cargas de trabalho somente leitura.

Observação

Se consultar a vista de gerenciamento dinâmico sys.dm_db_index_physical_stats numa instância do servidor que esteja a hospedar uma réplica secundária legível, poderá encontrar um problema de bloqueio de REDO. Isso ocorre porque essa vista de gestão dinâmica adquire um bloqueio IS na tabela ou vista de utilizador especificada, que pode bloquear solicitações de um processo REDO para um bloqueio X nessa tabela ou vista de utilizador.

Considerações sobre desempenho

Esta seção discute várias considerações de desempenho para bancos de dados secundários legíveis

Nesta secção:

Latência de dados

Implementar acesso só de leitura às réplicas secundárias é útil quando as suas cargas de trabalho em modo de leitura conseguem tolerar alguma latência dos dados. Em situações em que a latência de dados é inaceitável, considere executar tarefas de leitura apenas na réplica principal.

A réplica primária envia registros de log de alterações no banco de dados primário para as réplicas secundárias. Em cada banco de dados secundário, um thread de refazer dedicado aplica os registros de log. Em um banco de dados secundário de acesso de leitura, uma determinada alteração de dados não aparece nos resultados da consulta até que o registro de log que contém a alteração tenha sido aplicado ao banco de dados secundário e a transação tenha sido confirmada no banco de dados primário.

Isso significa que há alguma latência, geralmente apenas uma questão de segundos, entre as réplicas primária e secundária. Em casos incomuns, no entanto, por exemplo, se problemas de rede reduzirem a taxa de transferência, a latência pode se tornar significativa. A latência aumenta quando ocorrem gargalos de E/S e quando a movimentação de dados é suspensa. Para monitorizar a movimentação de dados suspensos, pode utilizar o Painel Always On ou a vista de gestão dinâmica sys.dm_hadr_database_replica_states.

Latência de dados em bancos de dados com tabelas com otimização de memória

No SQL Server 2014 (12.x), havia considerações especiais sobre latência de dados em secundários ativos - consulte SQL Server 2014 (12.x) Ative Secondaries: Readable Secondary Replicas. A partir do SQL Server 2016 (13.x), não há considerações especiais sobre latência de dados para tabelas com otimização de memória. A latência de dados esperada para tabelas com otimização de memória é comparável à latência para tabelas baseadas em disco.

Read-Only Impacto da Carga Laboral

Ao configurar uma réplica secundária para acesso de leitura apenas, as cargas de trabalho de leitura apenas nos bancos de dados secundários consomem recursos do sistema, como CPU e entrada/saída (para tabelas em disco) de processos de recuperação, especialmente se as cargas de trabalho de leitura apenas sobre tabelas em disco forem altamente intensivas em entrada/saída. Não há impacto de E/S ao acessar tabelas com otimização de memória porque todas as linhas residem na memória.

Além disso, cargas de trabalho em modo somente leitura nas réplicas secundárias podem bloquear alterações na linguagem de definição de dados (DDL) ao serem aplicadas por meio de registos de log.

  • Embora as operações de leitura não usem bloqueios compartilhados devido ao versionamento de linhas, essas operações usam bloqueios de estabilidade de esquema (Sch-S), que podem bloquear operações de repetição que estão aplicando alterações DDL. As operações DDL incluem ALTER e DROP de tabelas e visualizações, mas não DROP ou ALTER de procedimentos armazenados. Assim, por exemplo, se tu eliminares uma tabela baseada em disco ou otimizada para memória, no ficheiro principal. Quando o thread REDO processa o registo de log para eliminar a tabela, deve adquirir um bloqueio SCH_M na tabela e pode ser bloqueado por uma consulta em execução que acede à tabela. Este é o mesmo comportamento na réplica primária, exceto que a eliminação da tabela é feita como parte de uma sessão de usuário e não como parte de uma thread REDO.

  • Há bloqueio adicional nas Tabelas Memory-Optimized. Eliminar um procedimento armazenado nativo pode causar o bloqueio do thread REDO se houver uma execução simultânea do procedimento armazenado nativo na réplica secundária. Este é o mesmo comportamento na réplica primária, exceto que a eliminação do procedimento armazenado é realizada como parte de uma sessão do utilizador e não como parte de um thread de REDO.

Esteja ciente das práticas recomendadas em torno da criação de consultas e exercite essas práticas recomendadas nos bancos de dados secundários. Por exemplo, agende consultas de longa duração, como agregações de dados durante períodos de baixa atividade.

Observação

Se um thread de refazer for bloqueado por consultas em uma réplica secundária, o sqlserver.lock_redo_blocked XEvent será gerado.

Indexação

Para otimizar cargas de trabalho somente leitura nas réplicas secundárias legíveis, convém criar índices nas tabelas nos bancos de dados secundários. Como não é possível fazer alterações de esquema ou de dados nos bancos de dados secundários, crie índices nos bancos de dados primários e permita que as alterações sejam transferidas para o banco de dados secundário por meio do processo de refazer.

Para monitorar a atividade de uso do índice em uma réplica secundária, consulte as colunas user_seeks, user_scans e user_lookups do modo de exibição de gerenciamento dinâmico sys.dm_db_index_usage_stats .

Estatísticas para bancos de dados do Read-Only Access

As estatísticas sobre colunas de tabelas e exibições indexadas são usadas para otimizar os planos de consulta. Para grupos de disponibilidade, as estatísticas criadas e mantidas nas bases de dados primárias são automaticamente persistidas nas bases de dados secundárias ao aplicar os registos do log de transações. No entanto, a carga de trabalho somente leitura nos bancos de dados secundários pode precisar de estatísticas diferentes daquelas criadas nos bancos de dados primários. No entanto, como os bancos de dados secundários são restritos ao acesso somente leitura, as estatísticas não podem ser criadas nos bancos de dados secundários.

Para resolver esse problema, a réplica secundária cria e mantém estatísticas temporárias para bancos de dados secundários no tempdb. O sufixo _readonly_database_statistic é anexado ao nome das estatísticas temporárias para diferenciá-las das estatísticas permanentes que persistem do banco de dados primário.

Somente o SQL Server pode criar e atualizar estatísticas temporárias. No entanto, você pode excluir estatísticas temporárias e monitorar suas propriedades usando as mesmas ferramentas que você usa para estatísticas permanentes:

  • Exclua estatísticas temporárias usando a instrução DROP STATISTICS Transact-SQL.

  • Monitore estatísticas usando sys.stats e sys.stats_columns exibições de catálogo. sys_stats inclui uma coluna, is_temporary, para indicar quais estatísticas são permanentes e quais são temporárias.

Não há suporte para atualização de estatísticas automáticas para tabelas com otimização de memória na réplica primária ou secundária. Você deve acompanhar o desempenho das consultas e os planos na réplica secundária e atualizar manualmente as estatísticas na réplica primária quando necessário. No entanto, as estatísticas ausentes são criadas automaticamente na réplica primária e secundária.

Para obter mais informações sobre estatísticas do SQL Server, consulte Estatísticas.

Nesta secção:

Estatísticas permanentes obsoletas sobre bases de dados secundárias

O SQL Server deteta quando as estatísticas permanentes em um banco de dados secundário estão obsoletas. Mas não podem ser feitas alterações às estatísticas permanentes, exceto através de alterações na base de dados primária. Para otimização de consultas, o SQL Server cria estatísticas temporárias para tabelas baseadas em disco no banco de dados secundário e usa essas estatísticas em vez das estatísticas permanentes obsoletas.

Quando as estatísticas permanentes são atualizadas na base de dados primária, são automaticamente transferidas para a base de dados secundária. Em seguida, o SQL Server usa as estatísticas permanentes atualizadas, que são mais atuais do que as estatísticas temporárias.

Se o grupo de disponibilidade falhar, as estatísticas temporárias serão excluídas em todas as réplicas secundárias.

Limitações e Restrições

  • Como as estatísticas temporárias são armazenadas em tempdb, uma reinicialização do serviço SQL Server faz com que todas as estatísticas temporárias desapareçam.

  • O sufixo _readonly_database_statistic é reservado para estatísticas geradas pelo SQL Server. Não é possível usar esse sufixo ao criar estatísticas em um banco de dados primário. Para obter mais informações, consulte Statistics.

Acessando tabelas com otimização de memória em uma réplica secundária

Os níveis de isolamento de transação que podem ser usados com tabelas com otimização de memória em uma réplica secundária são os mesmos da réplica primária. A recomendação é definir o nível de isolamento no nível da sessão como READ COMMITTED e definir a opção no nível do banco de dados MEMORY_OPTIMIZED_ELEVATE_TO_SNAPSHOT como ON. Por exemplo:

ALTER DATABASE CURRENT SET MEMORY_OPTIMIZED_ELEVATE_TO_SNAPSHOT=ON  
GO  
SET TRANSACTION ISOLATION LEVEL READ COMMITTED  
GO  
SELECT SUM(UnitPrice*OrderQty)   
FROM Sales.SalesOrderDetail_inmem  
GO

Considerações sobre planejamento de capacidade

  • No caso de tabelas baseadas em disco, réplicas secundárias legíveis podem exigir espaço no tempdb por dois motivos:

    • O nível de isolamento de instantâneo copia versões de linha em tempdb.

    • Estatísticas temporárias para bancos de dados secundários são criadas e mantidas em tempdb. As estatísticas temporárias podem causar um ligeiro aumento no tamanho do tempdb. Para obter mais informações, consulte Estatísticas para bancos de dados do Read-Only Access, mais adiante nesta seção.

  • Quando você configura o acesso de leitura para uma ou mais réplicas secundárias, os bancos de dados primários adicionam 14 bytes de sobrecarga em linhas de dados excluídas, modificadas ou inseridas para armazenar ponteiros para versões de linha nos bancos de dados secundários para tabelas baseadas em disco. Essa sobrecarga de 14 bytes é transferida para os bancos de dados secundários. À medida que a sobrecarga de 14 bytes é adicionada às linhas de dados, podem ocorrer divisões de página.

    Os dados da versão de linha não são gerados pelos bancos de dados primários. Em vez disso, os bancos de dados secundários geram as versões de linha. No entanto, o versionamento de linha aumenta o armazenamento de dados nos bancos de dados primário e secundário.

    A adição dos dados da versão da linha depende da configuração de nível de isolamento de instantâneo ou RCSI (isolamento de instantâneo confirmado por leitura) no banco de dados primário. A tabela abaixo descreve o comportamento do controle de versão em um banco de dados secundário legível em diferentes configurações para tabelas baseadas em disco.

    Réplica secundária legível? Isolamento por instantâneos ou nível de isolamento RCSI ativado? Base de Dados Primária Base de Dados Secundária
    Não Não Sem versões de linha ou sobrecarga de 14 bytes Sem versões de linha ou sobrecarga de 14 bytes
    Não Sim Versões de linha e sobrecarga de 14 bytes Sem versões de linha, mas sobrecarga de 14 bytes
    Sim Não Sem versões de linha, mas sobrecarga de 14 bytes Versões de linha e sobrecarga de 14 bytes
    Sim Sim Versões de linha e sobrecarga de 14 bytes Versões de linha e sobrecarga de 14 bytes

Tarefas relacionadas

Conteúdo relacionado

Ver também

Visão geral dos grupos de disponibilidade Always On (SQL Server)
Sobre o acesso de conexão de cliente a réplicas de disponibilidade (SQL Server)
Ouvintes de Grupos de Disponibilidade, Conectividade de Clientes e Failover de Aplicações (SQL Server)
Estatísticas

  • Last updated on