Активные вторичные реплики. Доступ только для чтения к вторичным репликам (группы доступности AlwaysOn)

Статья
10/27/2017

Возможности активной вторичной базы данных групп доступности Always On включают поддержку доступа только для чтения к одной или нескольким вторичным репликам (читаемым вторичным репликам). Доступная для чтения вторичная реплика разрешает доступ только для чтения ко всем своим базам данных-получателям. Однако доступные для чтения базы данных-получатели не переводятся в режим доступа только для чтения. Они являются динамическими. Определенная база данных-получатель изменяется по мере того, как к ней применяются изменения, вносимые в данные базы данных-источника. Для большинства вторичных реплик данные, в том числе оптимизированные для памяти устойчивые таблицы, вносятся во вторичную базу данных почти в реальном времени. Более того, полнотекстовые индексы синхронизируются с базами данных-получателями. Во многих случаях задержка данных между базой данных-источником и соответствующей базой данных-получателем находится в пределах нескольких секунд.

Параметры безопасности, которые встречаются в базах данных-источниках, сохраняются в базах данных-получателях. К ним относятся пользователи, роли баз данных и роли приложений, а также соответствующие разрешения и прозрачное шифрование данных, если оно включено в базе данных-источнике.

Примечание

Хотя в базы данных-получатели нельзя записывать данные, их можно записывать в базы данных, предназначенные для чтения и записи на экземпляре сервера, содержащем вторичную реплику, в том числе в пользовательские и системные базы данных, например в базу данных tempdb.

Always On группы доступности также поддерживают перенаправку запросов на подключение с намерением чтения к доступной для чтения вторичной реплика (маршрутизация только для чтения). Дополнительные сведения см. в статье Соединение с помощью прослушивателя со вторичной репликой только для чтения (маршрутизация только для чтения).

Преимущества

Направление подключений «только для чтения» к доступным для чтения вторичным репликам обладает следующими преимуществами:

Перераспределяет нагрузку, вызываемую некритическими рабочими процессами, с основой реплики, ресурсы которой высвобождаются для критически важных нагрузок. Если на систему возлагается рабочая нагрузка критической важности, не терпящая задержек, для нее следует использовать основную реплику.
Это позволяет повысить окупаемость систем со вторичными репликами, предназначенными только для чтения.

Кроме того, доступные для чтения вторичные реплики обеспечивают надежность операций чтения.

Автоматические временные данные статистики во вторичной базе данных для чтения оптимизируют запросы только на чтение для дисковых таблиц. Для таблиц, оптимизированных для памяти, отсутствующая статистика создается автоматически. Однако устаревшие статистические данные не обновляются автоматически. Статистику на первичной реплике будет необходимо обновить вручную. Дополнительные сведения см. в разделе Статистика для баз данных с доступом только для чтениядалее в этой статье.
Рабочие нагрузки только на чтение для дисковых таблиц используют систему управления версиями строк для удаления состязаний блокировок в базах данных-получателях. Все запросы, выполняемые к базе данных-получателю, автоматически сопоставляются с уровнем транзакций изоляции моментального снимка баз данных, даже если другие уровни изоляции транзакций заданы явно. Кроме того, все указания блокировки пропускаются. Это позволяет исключить конфликт между чтением и записью.
Рабочие нагрузки только на чтение для устойчивых таблиц, оптимизированных для памяти, выполняют доступ к данным точно таким же способом, как и при доступе к базе данных-источнику, используя скомпилированные в собственном коде хранимые процедуры или совместимость SQL с теми же ограничениями уровня изоляции транзакций. Рабочая нагрузка отчетов или запросов только на чтение, выполняющаяся на главной реплике, может быть запущена на вторичной реплике без внесения каких-либо изменений. Точно так же, рабочая нагрузка отчетов или запросов только на чтение, выполняющаяся на вторичной реплике, может быть запущена на главной реплике без внесения каких-либо изменений. Так же как и для таблиц на диске, все запросы, выполняемые к базе данных-получателю, автоматически сопоставляются с уровнем транзакций изоляции моментального снимка баз данных, даже если другие уровни изоляции транзакций заданы явно.
Операции DML допустимы для табличных переменных как в дисковых, так и в оптимизированных для памяти типах таблиц на вторичной реплике.

Предварительные условия для использования группы доступности

Доступные для чтения вторичные реплики (необходимое условие)

Администратор базы данных должен настроить одну или несколько реплик, чтобы они, при выполнении во вторичной роли, разрешали либо все подключения (для доступа только для чтения), либо только подключения с намерением чтения данных.

Примечание

Кроме того, администратор базы данных может настроить любую из имеющихся реплик доступности на исключение соединений только для чтения во время работы в первичной роли.

Дополнительные сведения см. в статье Типы клиентских подключений к репликам в группе доступности Always On.
Прослушиватель группы доступности

Для поддержки маршрутизации только для чтения группа доступности должна иметь прослушиватель группы доступности. Клиент, запрашивающий данные в режиме только для чтения, должен направлять свои запросы к данному прослушивателю, и в строке подключения клиента должно быть задано намерение приложения read-only, то есть это должны быть запросы только для чтения.
Маршрутизация только для чтения

Маршрутизация только для чтения обозначает возможность SQL Server направлять входящие запросы соединения с намерением только чтения, предназначенные для прослушивателя группы доступности, на имеющуюся и доступную для чтения вторичную реплику. Необходимые условия для использования маршрутизации только для чтения:
- Для поддержки маршрутизации только для чтения доступная для чтения вторичная реплика должна иметь URL-адрес для маршрутизации только для чтения. Этот URL-адрес задействуется, только если локальная реплика выполняется под вторичной ролью. URL-адрес маршрутизации только для чтения должен быть указан для каждой реплики отдельно (если для реплики требуется подобная маршрутизация). Все URL-адреса маршрутизации только для чтения используются для направления запросов на соединение с намерением чтения к определенной доступной для чтения вторичной реплике. Как правило, каждой доступной для чтения вторичной реплике назначается URL-адрес маршрутизации только для чтения.
- Каждая реплика доступности, поддерживающая маршрутизацию только для чтения и при этом являющаяся первичной репликой, требует наличия списка маршрутизации только для чтения. Определенный список маршрутизации только для чтения вступает в силу, только если локальная реплика выполняется под первичной ролью. Такой список должен указываться для тех конкретных реплик, для которых он требуется. Как правило, каждый список маршрутизации только для чтения будет содержать все URL-адреса маршрутизации только для чтения, причем URL-адрес локальной реплики будет идти в конце списка.
  
  Примечание
  
  Запросы на соединение с намерением чтения направляются в первую имеющуюся вторичную реплику доступную для чтения из списка маршрутизации только для чтения текущей первичной реплики. Балансировка нагрузки отсутствует.
Дополнительные сведения см. в статье Настройка маршрутизации только для чтения в группе доступности Always On.

Примечание

Сведения о прослушивателях групп доступности и дополнительные сведения о маршрутизации только для чтения см. в статье о прослушивателях групп доступности, возможности подключения клиентов и отработке отказа приложений (SQL Server).

Ограничения

Некоторые операции поддерживаются не полностью.

Как только будет открыт доступ к доступной для чтения реплике, она может начать принимать подключения к своим базам данных-получателям. Однако при наличии любых активных транзакций в базе данных-источнике версии строк не будут полностью доступны в базе данных-получателе. Любые активные транзакции, существовавшие в первичной реплике, при настройке вторичной реплики должны быть зафиксированы или откачены. До момента завершения этого процесса сопоставление уровней изоляции транзакций в базе данных-получателе — неполное, а запросы временно блокируются.

Предупреждение

Выполнение длительных транзакций влияет на количество строк, содержащих версии, для дисковых и оптимизированных для памяти таблиц.
Для базы данных-получателя с таблицами, оптимизированными для памяти, несмотря на то, что для таблиц в памяти всегда создаются версии строк, запросы блокируются до тех пор, пока не будут завершены все активные транзакции, которые существовали в первичной реплике на момент открытия для чтения вторичной реплики. Это гарантирует, что и дисковые таблицы, и таблицы, оптимизированные для памяти, будут одновременно доступны и для рабочей нагрузки отчетов, и для запросов только на чтение.
Изменение режима отслеживания измененных данных не поддерживается во вторичных базах данных, принадлежащих к доступной для чтения вторичной реплике.
- Отслеживание изменений явно отключено в базах данных-получателях.
- Режим отслеживания измененных данных можно включить в базе данных-получателе, но это не поддерживается.
Операции чтения отображаются на уровень транзакций с изоляцией моментальных снимков, поэтому очистка фантомных записей в первичной реплике может быть заблокирована транзакциями в одной или нескольких вторичных репликах. Задача очистки фантомных записей автоматически очищает фантомные записи дисковых таблиц в основной реплике, когда они более не нужны в любой из вторичных реплик. Этот процесс сходен с другим процессом, когда транзакции запускаются в первичной реплике. В крайних случаях может понадобиться завершить долго выполняющийся запрос на чтение в базе данных-получателе, который блокирует процесс очистки фантомных записей. Обратите внимание, что очистка фантомных записей может блокироваться, если вторичная реплика отключена или если перемещение данных в базе данных-получателе приостановлено. Это состояние также предотвращает усечение журнала, поэтому, если оно не проходит, рекомендуется удалить эту базу данных-получатель из группы доступности. С таблицами, оптимизированными для памяти, не возникает проблем, связанных с очисткой фантомных записей, поскольку версии строк хранятся в памяти и не зависят от версий строк в первичной реплике.
Операция DBCC SHRINKFILE на файлах, содержащих дисковые таблицы, может завершиться с ошибкой в первичной реплике, если файл содержит фантомные записи, которые все еще необходимы во вторичной реплике.
Начиная с SQL Server 2014 г. доступные для чтения вторичные реплики могут оставаться в сети, даже если основной реплика находится в автономном режиме из-за действий пользователя или сбоя. Однако в этой ситуации маршрутизация, доступная только для чтения, не работает, так как прослушиватель группы доступности также находится вне сети. Клиенты могут подключаться непосредственно к вторичным репликам, доступным только для чтения, для рабочих нагрузок, доступных только для чтения.

Примечание

При выполнении запроса к динамическому административному представлению sys.dm_db_index_physical_stats на экземпляре сервера, на котором размещена вторичная реплика, может возникнуть критическое препятствие REDO. Это связано с тем, что данное динамическое административное представление получает блокировку (IS) в указанной пользовательской таблице либо в представлении, которые могут блокировать запросы посредством потока REDO для монопольной блокировки (X) этой пользовательской таблицы или представления.

Вопросы производительности

В этом разделе рассматриваются некоторые соображения в отношении производительности баз данных-получателей с доступом для чтения

Задержка данных

Применение доступа только для чтения ко вторичным репликам полезно, если для нагрузок, связанных с операциями с ними, приемлема некоторая задержка данных. В ситуациях, когда задержка данных неприемлема, рассмотрите возможность выполнения рабочих нагрузок только чтения за счет первичной реплики.

Из основной реплики выполняется отправка журнала изменений в базе данных-источнике на вторичные реплики. На каждой базе данных-получателе выделенный поток повтора применяет записи журнала. В базе данных, доступной для чтения, каждое изменение данных не появляется среди результатов запроса до тех пор, пока запись журнала о данном изменении не будет применена к базе данных-получателю, а транзакция не будет зафиксирована в базе данных-источнике.

Это означает, что между первичной и вторичной репликами возникает некоторая задержка, обычно порядка нескольких секунд. В нетипичных случаях, например в ситуации, когда проблемы с сетью ухудшают пропускную способность, задержка может стать значительной. Задержка увеличивается из-за наличия узких мест в системе ввода-вывода и в результате приостановки движения данных. Для отслеживания приостановок перемещения данных можно использовать Панель управления AlwaysOn или динамическое административное представление sys.dm_hadr_database_replica_states .

Задержка данных для баз данных с таблицами, оптимизированными для памяти

При обращении к таблицам, оптимизированным для памяти, на вторичной реплике для рабочей нагрузки на чтение используется метка safe-timestamp для получения строк из транзакций, которые были зафиксированы до safe-timestamp. Метка safe-timestamp считается самой поздней меткой для процесса сборки мусора строк на первичной реплике. Эта метка времени обновляется каждый раз, когда число транзакций для таблиц, оптимизированных для памяти, превышает внутренний порог со времени последнего обновления. Когда самая ранняя метка времени транзакции обновится на первичной реплике, следующая транзакция DML на оптимизированной для памяти и долговечной таблице отправит эту метку времени на вторичную реплику как часть специальной записи журнала. Поток REDO на вторичной реплике обновляет метку safe-timestamp в ходе обработки этой записи журнала.

Влияние метки safe-timestamp на задержку

Для рабочих нагрузок OLTP с высокой пропускной способностью транзакции задержка будет сравнима с таблицами на диске. Ожидается, что этот случай будет типичным.
Затянувшаяся транзакция может привести к необоснованной задержке метки safe-timestamp. Такое же поведение наблюдается и при работе с дисковыми таблицами, т. к. метка времени изоляции моментального снимка определяется фиксацией старейшей транзакции.
Изменения, произведенные транзакциями на первичной реплике с момента последнего обновления метки safe-timestamp, не отображаются на вторичной реплике до следующей передачи и обновления метки safe-timestamp. Если активность транзакции на первичной реплике останавливается до превышения внутреннего порога обновления safe-timestamp, изменения, выполненные с момента последнего обновления safe-timestamp, не будут видны на вторичной реплике. Чтобы устранить эту проблему, возможно, понадобится выполнить несколько транзакций DML в фиктивной долговечной таблице, оптимизированной для памяти, на первичной реплике. Кроме того, хотя это не рекомендуется, можно форсировать отправку safe-timestamp, выполнив ручную контрольную точку.

Задержка данных при наблюдении и устранении неполадок в таблицах, оптимизированных для памяти

Узнать значение safe-timestamp можно, выполнив следующий запрос в первичной реплике

  
SELECT MAX(base_generation)   
   AS max_base_generation  
   FROM sys.dm_db_xtp_gc_cycle_stats  
GO

Также можно определить safe-timestamp, используемую на вторичной реплике, выполнив следующий запрос параллельно с активной рабочей нагрузкой.

  
SELECT begin_tsn   
   FROM sys.dm_db_xtp_transactions  
GO

Влияние на рабочую нагрузку только для чтения

При настройке вторичной реплики для доступа только для чтения, нагрузки только для чтения в базах данных-получателях потребляют системные ресурсы, т. е. ресурсы процессора и системы ввода-вывода (для дисковых таблиц), за счет потоков повтора, в особенности, если нагрузка операций чтения дисковых таблиц выполняет большой объем операций ввода-вывода. При доступе к таблицам, оптимизированным для памяти, дополнительная нагрузка на систему ввода-вывода отсутствует, поскольку все строки находятся в памяти.

Рабочие нагрузки только чтения на вторичных репликах могут также блокировать изменения с помощью языка описания данных (DDL), проводимые через записи журнала.

Даже несмотря на то, что операции чтения не вызывают совмещаемых блокировок в связи с управлением версиями строк, эти операции вызывают блокировки стабильности схемы (Sch-S), что может приводить к блокировке операций повтора, в которых применяются изменения с помощью DDL. Операции DDL включают операции ALTER/DROP для таблиц и представлений, но не для хранимых процедур. Например, рассмотрим случай удаления дисковой или оптимизированной для памяти таблицы на первичной реплике. Если поток REDO обрабатывает записи журнала, чтобы удалить таблицу, он должен получить блокировку SCH_M для таблицы и может быть заблокирован запущенным запросом к таблице. Точно так же происходит и в первичной реплике, за исключением того, что удаление таблицы выполняется в составе пользовательского сеанса, а не потоком REDO.
Имеются дополнительные блокирующие оптимизированные для памяти таблицы. Удаление собственной хранимой процедуры может стать причиной блокировки потока REDO при одновременном выполнении собственной хранимой процедуры на вторичной реплике. Точно так же происходит и в первичной реплике, за исключением того, что удаление хранимой процедуры выполняется в составе пользовательского сеанса, а не потоком REDO.

Необходимо учитывать рекомендации по построению запросов и следовать им в работе с базами данных-получателями. Например, планируйте долговременные задачи, такие как статистическая обработка данных, на периоды наименьшей активности.

Примечание

Если поток повтора блокируется запросами на вторичной реплике, возникает событие XEvent sqlserver.lock_redo_blocked .

Индексация

Чтобы оптимизировать рабочие нагрузки только чтения в доступных для чтения вторичных репликах, можно создать индексы на таблицах в базах данных-получателях. Возможность вносить изменения в схемы или данные баз данных-получателей отсутствует, поэтому следует создать индексы в базах данных-источниках и разрешить перенос изменений в базы данных-получатели с помощью процесса повтора.

Для отслеживания действий использования индекса на вторичной реплике можно создавать запросы к столбцам user_seeks, user_scansи user_lookups динамического административного представления sys.dm_db_index_usage_stats .

Статистика для баз данных с доступом только для чтения

Статистика по столбцам таблиц и индексированных представлений используется для оптимизации планов запросов. Что касается групп доступности, то статистика, создаваемая и поддерживаемая по базам данных-источникам, автоматически сохраняется в базах данных-получателях в ходе применения записей журнала транзакций. Однако рабочая нагрузка только чтения в базах данных-получателях может требовать иной статистики, чем статистика, формируемая в базах данных-источниках. Однако поскольку базы данных-получатели доступны только для чтения, данные статистики в них сохранять нельзя.

Для решения этой проблемы во вторичной реплике создается и ведется временная статистика для баз данных-получателей в tempdb. Суффикс _readonly_database_statistic добавляется к имени временной статистики. Он позволяет отличить временную статистику от постоянной, которая сохраняется в основной базе данных.

Только SQL Server может создавать и обновлять временную статистику. Тем не менее можно удалять временную статистику и наблюдать за ее свойствами с помощью тех же средств, которые используются для работы с постоянной статистикой.

Удалите временную статистику с помощью инструкции Transact-SQL DROP STATISTICS.
Наблюдение за статистикой ведется с помощью представлений каталога sys.stats и sys.stats_columns . sys_stats включает столбец is_temporaryдля указания того, какая статистика является постоянной, а какая — временной.

Не поддерживается автоматическое обновление статистики для таблиц, оптимизированных для памяти, на первичной или вторичной реплике. Необходимо контролировать производительность запросов и планов на вторичной реплике и вручную обновить статистику на первичной реплике, когда в этом возникает необходимость. Однако отсутствующая статистика автоматически создается и на первичной и на вторичной реплике.

Дополнительные сведения о статистике SQL Server см. в статье Статистика.

Устаревшая постоянная статистика в базах данных-получателях

SQL Server определяет, когда постоянная статистика базы данных-получателя устаревает. Однако изменения в постоянную статистику можно внести только через изменения в базе данных-источнике. Для обеспечения оптимизации запросов SQL Server создает временную статистику для дисковых таблиц в базе данных-получателе и использует ее, а не устаревшую постоянную статистику.

После выполнения обновления постоянной статистики в базе данных-источнике, данные этой статистики автоматически переносятся в базу данных-получатель. Затем SQL Server использует обновленные постоянные значения статистики, являющиеся более современными, чем значения временной статистики.

При отработке отказа группы доступности временная статистика удаляется во всех вторичных репликах.

Ограничения

Поскольку временная статистика хранится в базе данных tempdb, перезапуск службы SQL Server приведет к удалению всей временной статистики.
Суффикс _readonly_database_statistic зарезервирован для статистики, создаваемой SQL Server. Этот суффикс нельзя использовать при создании статистики в базе данных-источнике. Дополнительные сведения см. в статье Managing statistics on tables in SQL Data Warehouse (Управление статистикой таблиц в хранилище данных SQL).

Доступ к таблицам, оптимизированным для памяти, на вторичной реплике

На вторичной реплике допускаются только те уровни изоляции рабочей нагрузки чтения, которые разрешены на первичной реплике. На вторичной реплике сопоставление уровней изоляции не выполняется. Это гарантирует, что любая рабочая нагрузка отчетов, которая может выполняться на первичной реплике, может также выполняться и на вторичной реплике без каких-либо изменений. Это упрощает перенос рабочей нагрузки отчетов с первичной реплики на вторичную, или наоборот, если вторичная реплика недоступна.

Следующие запросы не удается запустить на вторичной реплике аналогично тому, как они завершаются с ошибкой на первичной реплике.

Для запросов, выполняющихся только для оптимизированных для памяти таблиц, поддерживаются только уровни изоляции «моментальный снимок», «повторяемое чтение» и «доступен для сериализации». Все запросы с уровнем изоляции READ COMMITTED или READ-UNCOMMITTED возвращают ошибку, если не включен параметр MEMORY_OPTIMIZED_ELEVATE_TO_SNAPSHOT на уровне базы данных.
```
SET TRANSACTION ISOLATION LEVEL READ_COMMITTED  
-- This is not allowed  
BEGIN TRAN  
SELECT * FROM t_hk  
COMMIT  
```
Сообщение об ошибке:
```
Msg 41368, Level 16, State 0, Line 2  
Accessing memory optimized tables using the CREAD_COMMITTED isolation level is supported only for autocommit transactions. It is not supported for explicit or implicit transactions. Provide a supported isolation level for the memory optimized table using a table hing, such as WITH (SNAPSHOT).  
```
Подсказки блокировки не поддерживаются в таблицах, оптимизированных для памяти. Например, все следующие запросы завершаются с ошибкой. Допускается только подсказка NOLOCK, и для нее устанавливается значение NOOP при работе с таблицами, оптимизированными для памяти.
```
SELECT * FROM t_hk WITH (PAGLOCK)  
SELECT * FROM t_hk WITH (READPAST)  
SELECT * FROM t_hk WITH (ROWLOCK)  
SELECT * FROM t_hk WITH (READPAST)  
SELECT * FROM t_hk WITH (TABLOCK)  
SELECT * FROM t_hk WITH (XLOCK)  
SELECT * FROM t_hk WITH (UPDLOCK)  
```
Для транзакций между контейнерами транзакции с уровнем изоляции сеанса "snapshot", которые обращаются к оптимизированным для памяти таблицам, не поддерживаются. Например,
```
SET TRANSACTION ISOLATION LEVEL SNAPSHOT  
-- This is not allowed  
BEGIN TRAN  
   SELECT * FROM t_hk  
COMMIT  
```
Сообщение об ошибке:
```
Msg 41332, Level 16, State 0, Line 5  
Memory optimized tables and natively compiled stored procedures cannot be accessed or created when the session TRANSACTION ISOLATION LEVEL is set to SNAPSHOT.  
```

Доступна ли для чтения вторичная реплика?	Включен ли уровень изоляции моментальных снимков или RCSI?	База данных-источник	База данных-получатель
Нет	Нет	Отсутствуют версии строки, либо 14-байтовые издержки	Отсутствуют версии строки, либо 14-байтовые издержки
Нет	Да	Версии строк и 14 дополнительных байт	Нет версий строк, но есть 14 дополнительных байт
Да	Нет	Нет версий строк, но есть 14 дополнительных байт	Версии строк и 14 дополнительных байт
Да	Да	Версии строк и 14 дополнительных байт	Версии строк и 14 дополнительных байт

Связанные задачи

См. также

Блог группы разработчиков SQL Server AlwaysOn: официальный блог группы разработчиков SQL Server AlwaysOn

См. также:

Обзор групп доступности AlwaysOn (SQL Server)
Сведения о доступе клиентского подключения к репликам доступности (SQL Server)
Прослушиватели групп доступности, возможность подключения клиентов и отработка отказа приложений (SQL Server)
Статистика

Поделиться через

Активные вторичные реплики. Доступ только для чтения к вторичным репликам (группы доступности AlwaysOn)

Преимущества

Предварительные условия для использования группы доступности

Ограничения

Вопросы производительности

Задержка данных

Задержка данных для баз данных с таблицами, оптимизированными для памяти

Влияние метки safe-timestamp на задержку

Задержка данных при наблюдении и устранении неполадок в таблицах, оптимизированных для памяти

Влияние на рабочую нагрузку только для чтения

Индексация

Статистика для баз данных с доступом только для чтения

Устаревшая постоянная статистика в базах данных-получателях

Ограничения

Доступ к таблицам, оптимизированным для памяти, на вторичной реплике

Рекомендации по планированию загрузки

Связанные задачи

См. также

См. также:

Дополнительные ресурсы