Инициализация вторичной реплики группы доступности Always On с помощью автоматического заполнения

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

В SQL Server 2012 и 2014 единственным способом инициализации вторичной реплики в группе доступности SQL Server Always On является использование операций резервного копирования, копирования и восстановления. В SQL Server 2016 появилась новая функция для инициализации вторичной реплики — автоматическое заполнение. Автоматическое заполнение использует транспортный поток журнала для потокового резервного копирования с помощью VDI на вторичную реплику для каждой базы данных группы доступности, применяющую настроенные конечные точки. Эту новую функцию можно использовать во время первоначального создания группы доступности или при добавлении базы данных в группу доступности. Автоматическое заполнение доступно во всех выпусках SQL Server, поддерживающих группы доступности AlwaysOn, и может использоваться как с традиционными группами доступности, так и с распределенными группами доступности.

Безопасность

Разрешения безопасности зависят от типа инициализируемой реплики.

• Для традиционной группы доступности разрешения должны быть предоставлены группе доступности на вторичной реплике после ее присоединения к группе доступности. В Transact-SQL используйте команду ALTER AVAILABILITY GROUP [<AGName>] GRANT CREATE ANY DATABASE.
• Для распределенной группы доступности, в которой создаваемые базы данных реплики находятся на первичной реплике второй группы доступности, дополнительные разрешения не требуются, так как она уже является первичной. Однако если во второй группе доступности присутствует только одна реплика, предоставьте разрешение CREATE ANY DATABASE вторичной группе доступности. В противном случае автоматическое заполнение значений может завершиться ошибкой.
• Для вторичной реплики во второй группе доступности в составе распределенной группы доступности необходимо использовать команду ALTER AVAILABILITY GROUP [<2ndAGName>] GRANT CREATE ANY DATABASE. Эта вторичная реплика будет заполняться данными из первичной реплики второй группы доступности.

Влияние производительности и журнала транзакций на первичную реплику

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

• размер базы данных составляет 5 ТБ;
• скорость сетевого подключения — 1 Гбит/с;
• расстояние между двумя сайтами — 1600 километров.

Если доступна полная пропускная способность, то сеть со скоростью сетевого подключения 1 Гбит/с может предоставить устойчивую пропускную способность в 125 МБ/с. В этом примере для автоматического заполнения может потребоваться чуть более 11 часов. На практике процесс автоматического заполнения выполняется медленнее, так как на больших расстояниях качество сигнала снижается и канал используется совместно с другими ресурсами в сети. Во время заполнения журнал транзакций в базе данных на первичной реплике продолжает расти. Он не может быть усечен вплоть до завершения автоматического заполнения этой базы данных. Затем журнал транзакций может быть усечен с помощью резервной копии журнала транзакций.

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

Для автоматического заполнения можно использовать сжатие, но оно отключено по умолчанию. При включении сжатия снижается пропускная способность сети и, возможно, ускоряется процесс, но компромисс достигается за счет издержек на дополнительный процессор. Чтобы использовать сжатие во время автоматического заполнения, установите флаг трассировки 9567 — см. раздел Настройка сжатия для группы доступности.

Разметка диска

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

В SQL Server 2017 корпорация Майкрософт рекомендует использовать одни и те же пути к данным и файлам журналов во всех репликах, входящих в группу доступности. Но при необходимости можно использовать разные пути. Например, в кроссплатформенной группе доступности один экземпляр SQL Server находится в Windows, а другой — в Linux. Пути на различных платформах будут разными. SQL Server 2017 поддерживает реплики группы доступности на экземплярах SQL Server с различными путями по умолчанию.

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

Основной экземпляр Путь к данным по умолчанию	Вторичный экземпляр Путь к данным по умолчанию	Основной экземпляр Расположение исходного файла	Вторичный экземпляр Расположение целевого файла
c:\data\	/var/opt/mssql/data/	c:\data\	/var/opt/mssql/data/
c:\data\	/var/opt/mssql/data/	c:\data\group1\	/var/opt/mssql/data/group1/
c:\data\	d:\data\	c:\data\	d:\data\
c:\data\	d:\data\	c:\data\group1\	d:\data\group1\

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

Основной экземпляр Путь к данным по умолчанию	Вторичный экземпляр Путь к данным по умолчанию	Основной экземпляр Расположение файла	Вторичный экземпляр Расположение файла
c:\data\	c:\data\	d:\group1\	d:\group1\
c:\data\	c:\data\	d:\data\	d:\data\
c:\data\	c:\data\	d:\data\group1\	d:\data\group1\

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

Основной экземпляр Путь к данным по умолчанию	Вторичный экземпляр Путь к данным по умолчанию	Основной экземпляр Расположение файла	SQL Server 2016 Вторичный экземпляр Расположение файла	SQL Server 2017 Вторичный экземпляр Расположение файла
c:\data\	d:\data\	c:\data\	c:\data\	d:\data\
c:\data\	d:\data\	c:\data\group1\	c:\data\group1\	d:\data\group1\

Чтобы вернуться к поведению для SQL Server 2016 и более ранней версии, установите флаг трассировки 9571. Сведения об установке флагов трассировки см. в разделе DBCC TRACEON (Transact-SQL).

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

Для создания группы доступности с помощью автоматического заполнения используется Transact-SQL или среда SQL Server Management Studio (SSMS 17 или более поздней версии). Чтобы воспользоваться мастером групп доступности в среде SSMS, выполните следующие инструкции. Когда вы дойдете до шага 9, то увидите, что автоматическое заполнение — это самый первый, используемый по умолчанию вариант.

В следующем примере создается группа доступности с автоматическим заполнением с помощью Transact-SQL. См. также раздел Создание группы доступности (Transact-SQL). Для включения заполнения во вторичной реплике параметр SEEDING_MODE установлен равным AUTOMATIC. По умолчанию используется поведение MANUAL, которое присуще версиям SQL Server вплоть до SQL Server 2016. Это поведение включает резервное копирование базы данных в первичной реплике, копирование файла резервной копии на вторичную реплику и восстановление из резервной копии WITH NORECOVERY.

CREATE AVAILABILITY GROUP [<AGName>]
  FOR DATABASE db1
  REPLICA ON N'Primary_Replica'
WITH (
  ENDPOINT_URL = N'TCP://Primary_Replica.Contoso.com:5022', 
  FAILOVER_MODE = AUTOMATIC, 
  AVAILABILITY_MODE = SYNCHRONOUS_COMMIT
),
  N'Secondary_Replica' WITH (
    ENDPOINT_URL = N'TCP://Secondary_Replica.Contoso.com:5022', 
    FAILOVER_MODE = AUTOMATIC, 
    SEEDING_MODE = AUTOMATIC);
 GO

Установка параметра SEEDING_MODE на первичной реплике во время выполнения инструкции CREATE AVAILABILITY GROUP ни на что не влияет, так как первичная реплика уже содержит основную копию базы данных, доступную для чтения и записи. SEEDING_MODE будет применяться только в том случае, если другая реплика была сделана первичной и была добавлена база данных. Режим заполнения можно изменить позднее — см. подраздел Изменение режима заполнения реплики.

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

Локальной реплике доступности для группы доступности "AGName" не было предоставлено разрешение на создание баз данных, но параметр SEEDING_MODE был установлен равным AUTOMATIC. Используйте ALTER AVAILABILITY GROUP ... GRANT CREATE ANY DATABASE, чтобы разрешить создание баз данных, которые заполняются первичной репликой доступности.

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

После соединения предоставьте группе доступности разрешение на создание баз данных в экземпляре вторичной реплики SQL Server. Для работы автоматического заполнения группе доступности необходимо предоставить разрешение на создание базы данных.

Совет

Когда группа доступности создает базу данных во вторичной реплике, она задает sa (учетную запись с идентификатором безопасности 0x01) в качестве владельца базы данных.

Чтобы изменить владельца базы данных после того, как вторичная реплика автоматически создает базу данных, используйте ALTER AUTHORIZATION. См. раздел ALTER AUTHORIZATION (Transact-SQL).

В следующем примере это разрешение предоставляется группе доступности с именем AGName.

ALTER AVAILABILITY GROUP [<AGName>] 
    GRANT CREATE ANY DATABASE
 GO

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

Проверка автоматического заполнения

В случае успешного выполнения базы данных автоматически создаются на вторичной реплике с одним из следующих состояний:

• SYNCHRONIZED (синхронизировано), если вторичная реплика настроена как синхронная и данные синхронизированы.
• SYNCHRONIZING (синхронизируется), если вторичная реплика настроена с асинхронным перемещением данных или настроена с синхронным, но еще не синхронизирована с первичной репликой.

В дополнение к динамическим административным представлениям, описанным ниже, запуск и завершение автоматического заполнения можно увидеть в журнале SQL Server.

Объединение резервного копирования и восстановление с автоматическим заполнением

Можно объединить традиционные операции резервного копирования и восстановления с автоматическим заполнением. В этом случае сначала следует восстановить базу данных на вторичной реплике, включая все доступные журналы транзакций. Затем нужно включить автоматическое заполнение при создании группы доступности, чтобы "догнать" базу данных вторичной реплики, как если бы была восстановлена резервная копия заключительного фрагмента журнала (см. раздел Резервные копии заключительного фрагмента журнала (SQL Server)).

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

Для добавления базы данных в группу доступности с помощью автоматического заполнения используется Transact-SQL или среда SQL Server Management Studio (SSMS 17 или более поздней версии). Если вторичная реплика использовала автоматическое заполнение, когда она была добавлена в группу доступности, никакие дополнительные действия не требуются. Если во вторичной реплике использовались операции резервного копирования, копирования и восстановления, сначала измените режим заполнения (см. следующий подраздел), а затем при добавлении базы данных воспользуйтесь инструкцией GRANT — см. раздел Добавление базы данных в группу доступности.

Изменение режима заполнения реплики

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

ALTER AVAILABILITY GROUP [AGName]
  MODIFY REPLICA ON 'Replica_Name'
  WITH (SEEDING_MODE = AUTOMATIC)

Чтобы отключить автоматическое заполнение, используйте значение MANUAL.

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

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

ALTER AVAILABILITY GROUP [AGName] 
    DENY CREATE ANY DATABASE
GO

Мониторинг автоматического заполнения

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

• Журнал SQL Server, как уже описано
• Динамические административные представления
• Таблицы журнала резервного копирования
• Расширенные события

Dynamic Management Views (Динамические административные представления)

Существует два динамических административных представления для мониторинга заполнения: sys.dm_hadr_automatic_seeding и sys.dm_hadr_physical_seeding_stats.

• sys.dm_hadr_automatic_seeding содержит сведения об общем состоянии автоматического заполнения и сохраняет журнал для каждого заполнения (было ли оно завершено успешно). В столбце current_state будет указано значение COMPLETED или FAILED. Если указано значение FAILED, используйте значение в failure_state_desc для диагностики проблемы. Чтобы увидеть, что пошло не так, вам может потребоваться объединить его со значением в журнале SQL Server. Это динамическое административное представление заполняется на первичной и всех вторичных репликах.

• В sys.dm_hadr_physical_seeding_stats отображается состояние операции автоматического заполнения по мере ее выполнения. Как и в sys.dm_hadr_automatic_seeding, будут возвращены значения для первичных и вторичных реплик, но журнал сохраняться не будет. Значения приводятся только для текущего выполнения и не сохраняются. Необходимые столбцы включают start_time_utc, end_time_utc, estimate_time_complete_utc, total_disk_io_wait_time_ms, total_network_wait_time_ms и столбец failure_message, если операция заполнения завершается сбоем.

Таблицы журнала резервного копирования

Автоматическое заполнение также помещает данные в таблицы msdb, где хранится журнал для резервного копирования и восстановления. На вторичной реплике, которая получает автоматическое заполнение, столбец physical_device_name таблицы backupmediafamily содержит идентификатор GUID для его значения, а соответствующая запись в backupset носит имя первичной реплики для server_name и machine_name.

Расширенные события

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

CREATE EVENT SESSION [AlwaysOn_autoseed] ON SERVER 
    ADD EVENT sqlserver.hadr_automatic_seeding_state_transition,
    ADD EVENT sqlserver.hadr_automatic_seeding_timeout,
    ADD EVENT sqlserver.hadr_db_manager_seeding_request_msg,
    ADD EVENT sqlserver.hadr_physical_seeding_backup_state_change,
    ADD EVENT sqlserver.hadr_physical_seeding_failure,
    ADD EVENT sqlserver.hadr_physical_seeding_forwarder_state_change,
    ADD EVENT sqlserver.hadr_physical_seeding_forwarder_target_state_change,
    ADD EVENT sqlserver.hadr_physical_seeding_progress,
    ADD EVENT sqlserver.hadr_physical_seeding_restore_state_change,
    ADD EVENT sqlserver.hadr_physical_seeding_submit_callback
    ADD TARGET package0.event_file(
        SET filename=N'autoseed.xel',
        max_file_size=(5),
        max_rollover_files=(4)
        )
    WITH (
        MAX_MEMORY=4096 KB,
        EVENT_RETENTION_MODE=ALLOW_SINGLE_EVENT_LOSS,
        MAX_DISPATCH_LATENCY=30 SECONDS,
        MAX_EVENT_SIZE=0 KB,
        MEMORY_PARTITION_MODE=NONE,
        TRACK_CAUSALITY=OFF,
        STARTUP_STATE=ON
        )
GO

ALTER EVENT SESSION AlwaysOn_autoseed ON SERVER STATE=START
GO

В таблице ниже перечислены расширенные события, связанные с автоматическим заполнением.

Имя	Описание
hadr_db_manager_seeding_request_msg	Заполнение сообщения запроса.
hadr_physical_seeding_backup_state_change	Изменение состояния на стороне резервного копирования при физическом заполнении.
hadr_physical_seeding_restore_state_change	Изменение состояния на стороне восстановления при физическом заполнении.
hadr_physical_seeding_forwarder_state_change	Изменение состояния на стороне сервера пересылки при физическом заполнении.
hadr_physical_seeding_forwarder_target_state_change	Изменение состояния на стороне цели сервера пересылки при физическом заполнении.
hadr_physical_seeding_submit_callback	Событие обратного вызова при фиксации физического заполнения.
hadr_physical_seeding_failure	Событие сбоя физического заполнения.
hadr_physical_seeding_progress	Событие хода выполнения физического заполнения.
hadr_physical_seeding_schedule_long_task_failure	Событие сбоя длительной задачи с расписанием физического заполнения.
hadr_automatic_seeding_start	Происходит при отправке операции автоматического заполнения.
hadr_automatic_seeding_state_transition	Происходит при изменении состояния операции автоматического заполнения.
hadr_automatic_seeding_success	Происходит при успешном выполнении операции автоматического заполнения.
hadr_automatic_seeding_failure	Происходит при сбое операции автоматического заполнения.
hadr_automatic_seeding_timeout	Происходит при истечении времени ожидания операции автоматического заполнения.

См. также

ALTER AVAILABILITY GROUP (Transact-SQL)

CREATE AVAILABILITY GROUP (Transact-SQL)

Руководство по мониторингу и устранению неполадок в группах доступности AlwaysOn