Перенос нагрузки только для чтения на вторичную реплику в группе доступности Always On

  • Статья

Область применения: SQL Server

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

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

Примечание.

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

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

Льготы

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

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

  • Это позволяет повысить окупаемость систем со вторичными репликами, предназначенными только для чтения.

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

  • Автоматические временные данные статистики во вторичной базе данных для чтения оптимизируют запросы только на чтение для дисковых таблиц. Для таблиц, оптимизированных для памяти, отсутствующая статистика создается автоматически. Однако устаревшие статистические данные не обновляются автоматически. Статистику на первичной реплике будет необходимо обновить вручную. Дополнительные сведения см. в разделе Статистика для баз данных с доступом только для чтениядалее в этой статье.

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

  • Рабочие нагрузки только на чтение для устойчивых таблиц, оптимизированных для памяти, обращаются к данным точно таким же способом, как и при доступе к базе данных-источнику, используя скомпилированные в собственном коде хранимые процедуры или совместимость SQL с теми же ограничениями уровня изоляции транзакций (см. статью Уровни изоляции в компоненте Database Engine). Рабочая нагрузка отчетов или запросов только на чтение, выполняющаяся на главной реплике, может быть запущена на вторичной реплике без внесения каких-либо изменений. Точно так же, рабочая нагрузка отчетов или запросов только на чтение, выполняющаяся на вторичной реплике, может быть запущена на главной реплике без внесения каких-либо изменений. Так же как и для таблиц на диске, все запросы, выполняемые к базе данных-получателю, автоматически сопоставляются с уровнем транзакций изоляции моментального снимка баз данных, даже если другие уровни изоляции транзакций заданы явно.

  • Операции DML допустимы для табличных переменных как в дисковых, так и в оптимизированных для памяти типах таблиц на вторичной реплике.

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

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

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

    Примечание.

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

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

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

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

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

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

  • Маршрутизация только для чтения

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

    • Для поддержки маршрутизации только для чтения доступная для чтения вторичная реплика должна иметь URL-адрес для маршрутизации только для чтения. Этот URL-адрес задействуется, только если локальная реплика выполняется под вторичной ролью. URL-адрес маршрутизации только для чтения должен быть указан для каждой реплики отдельно (если для реплики требуется подобная маршрутизация). Все URL-адреса маршрутизации только для чтения используются для направления запросов на соединение с намерением чтения к определенной доступной для чтения вторичной реплике. Как правило, каждой доступной для чтения вторичной реплике назначается URL-адрес маршрутизации только для чтения.

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

      Примечание.

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

    Дополнительные сведения см. в статье Настройка маршрутизации только для чтения в группе доступности (SQL Server).

Примечание.

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

Ограничения

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

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

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

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

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

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

    • Отслеживание изменений явно отключено в базах данных-получателях.

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

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

  • Операция DBCC SHRINKFILE на файлах, содержащих дисковые таблицы, может завершиться с ошибкой в первичной реплике, если файл содержит фантомные записи, которые все еще необходимы во вторичной реплике.

  • Начиная с SQL Server 2014 (12.x), доступные для чтения вторичные реплики могут оставаться в сети, даже если основная реплика находится в автономном режиме из-за действия пользователя или сбоя, например синхронизация была приостановлена из-за команды пользователя или сбоя, или реплика разрешает состояние из-за автономного подключения WSFC. Однако в этой ситуации маршрутизация, доступная только для чтения, не работает, так как прослушиватель группы доступности также находится вне сети. Клиенты могут подключаться непосредственно к вторичным репликам, доступным только для чтения, для рабочих нагрузок, доступных только для чтения.

Примечание.

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

Performance Considerations (Приложения-функции Azure. Рекомендации по производительности)

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

В этом разделе.

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

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

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

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

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

В SQL Server 2014 (12.x) были особые рекомендации по задержке данных в активных вторичных файлах— см . статью SQL Server 2014 (12.x). Активные вторичные реплики: доступные для чтения вторичные реплики. Начиная с SQL Server 2016 (13.x) нет особых соображений по задержке данных для оптимизированных для памяти таблиц. Ожидаемая задержка данных в таблицах, оптимизированных для памяти, сопоставима с задержкой в таблицах на диске.

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

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

Рабочие нагрузки только чтения на вторичных репликах могут также блокировать изменения с помощью языка описания данных (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 и установить параметр на уровне базы данных MEMORY_OPTIMIZED_ELEVATE_TO_SNAPSHOT на значение ON. Например:

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

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

  • В случае если таблицы хранятся на дисках, доступные для чтения вторичные реплики могут потребовать наличия свободного места в базе данных tempdb по двум причинам:

    • Применение уровня изоляции моментальных снимков приводит к копированию версий строк в базу данных tempdb.

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

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

    Данные версий строк не формируются в базах данных-источниках. Вместо этого версии строк создаются базами данных-получателями. Тем не менее управление версиями строк увеличивает объем хранения данных как в базах данных-источниках, так и в базах данных-получателях.

    Добавление данных управления версиями строк зависит от настройки уровня изоляции моментальных снимков или уровня изоляции моментальных снимков с чтением зафиксированных данных (RCSI) в базе данных-источнике. В следующей таблице описано поведение управления версиями в базе данных-получателе, доступной для чтения, с различными настройками для таблиц на диске.

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

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

См. также

См. также

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