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

Настройка распределенных транзакций для группы доступности Always On

  • Статья

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

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

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

Примечание.

Sql Server 2016 (13.x) с пакетом обновления 2 и более поздних версий обеспечивают полную поддержку распределенных транзакций в группах доступности. В SQL Server 2016 (13.x) с пакетом обновления 1 и более ранних версиях распределенные транзакции между базами данных (то есть транзакция с использованием баз данных в одном экземпляре SQL Server) с участием базы данных в группе доступности не поддерживаются. Sql Server 2017 (14.x) не имеет этого ограничения.

В SQL Server 2016 (13.x) действия по настройке аналогичны sql Server 2017 (14.x).

В распределенной транзакции клиентские приложения работают с координатором распределенных транзакций (MSDTC или DTC) для обеспечения согласованности транзакций в нескольких источниках данных. DTC — это служба, доступная в поддерживаемых операционных системах Windows Server. Для распределенных транзакций DTC выступает как координатор. Экземпляр SQL Server, как правило, служит диспетчером ресурсов. У каждой базы данных, которая не входит в группу доступности, должен быть собственный диспетчер ресурсов.

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

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

Необходимые компоненты

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

  • Все экземпляры SQL Server, участвующие в распределенной транзакции, должны быть SQL Server 2016 (13.x) или более поздними версиями.

  • Группы доступности должны работать в Windows Server 2012 R2 или более поздних версиях. Для Windows Server 2012 R2 необходимо установить обновление в KB3090973.

Создание группы доступности для распределенных транзакций

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

Вы можете создать группу доступности для распределенных транзакций в SQL Server 2016 (13.x) или более поздних версиях. Чтобы создать группу доступности для распределенных транзакций, включите в определении группы доступности DTC_SUPPORT = PER_DB. Представленный ниже скрипт создает группу доступности для распределенных транзакций.

CREATE AVAILABILITY
GROUP MyAG
WITH (DTC_SUPPORT = PER_DB)
FOR DATABASE DB1,
    DB2 REPLICA
ON 'Server1' WITH (
   ENDPOINT_URL = 'TCP://SERVER1.corp.com:5022',
   AVAILABILITY_MODE = SYNCHRONOUS_COMMIT,
   FAILOVER_MODE = AUTOMATIC
),
'Server2' WITH (
   ENDPOINT_URL = 'TCP://SERVER2.corp.com:5022',
   AVAILABILITY_MODE = SYNCHRONOUS_COMMIT,
   FAILOVER_MODE = AUTOMATIC
);

Примечание.

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

Изменение группы доступности для распределенных транзакций

Вы можете изменить группу доступности для распределенных транзакций в SQL Server 2017 (14.x) или более поздних версиях. Чтобы изменить группу доступности для распределенных транзакций, включите DTC_SUPPORT = PER_DB в скрипт ALTER AVAILABILITY GROUP. Скрипт в данном примере изменяет группу доступности, позволяя ее поддерживать распределенные транзакции.

ALTER AVAILABILITY GROUP MyaAG
SET (DTC_SUPPORT = PER_DB);

Примечание.

В SQL Server 2016 (13.x) с пакетом обновления 2 и более поздних версий можно изменить группу доступности для распределенных транзакций. Для версий SQL Server 2016 (13.x) перед пакетом обновления 2 необходимо удалить и повторно создать группу доступности с параметром DTC_SUPPORT = PER_DB .

Чтобы отключить распределенные транзакции, используйте следующую команду Transact-SQL:

ALTER AVAILABILITY GROUP MyaAG
SET (DTC_SUPPORT = NONE);

Распределенные транзакции: технические понятия

Распределенная транзакция охватывает две базы данных или больше. Как диспетчер транзакций, DTC координирует транзакцию между экземплярами SQL Server и другими источниками данных. Каждый экземпляр ядра СУБД SQL Server может работать в качестве диспетчера ресурсов. После настройки группы доступности в DTC_SUPPORT = PER_DB в качестве диспетчеров ресурсов могут выступать и базы данных. Дополнительные сведения см. в документации MSDTC.

Транзакция с двумя или несколькими базами данных в отдельном экземпляре компонента Database Engine, по сути, является распределенной транзакцией. Экземпляр управляет распределенной транзакцией на внутреннем уровне, для пользователя она действует как локальная транзакция. SQL Server 2017 (14.x) поддерживает все транзакции между базами данных в DTC, если базы данных находятся в группе доступности, настроенной даже DTC_SUPPORT = PER_DB в одном экземпляре SQL Server.

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

  • Фаза подготовки

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

  • Фаза фиксации

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

Подробные инструкции

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

  1. Экземпляр SQL Server указывается в транзакции DTC. Это может произойти, если в транзакции существует несколько диспетчеров ресурсов или если клиент запрашивает повышение транзакции до транзакции DTC.
  2. Клиент выполняет определенные действия в экземпляре SQL Server в рамках транзакции DTC.
  3. Клиент запускает фиксацию или отмену транзакции DTC.
    • Если клиент запускает отмену, транзакция незамедлительно прерывается.
    • Если клиент запускает фиксацию, DTC начинает протокол двухфазной фиксации, отправляя всем диспетчерам ресурсов запрос о подготовке транзакции.
  4. После того как все диспетчеры ресурсов подтвердят завершение стадии подготовки, DTC сообщает всем диспетчерам ресурсов о том, что транзакция зафиксирована. Если по какой-то причине подтверждение не поступает, элемент управления времени разработки прерывает транзакцию.

Результаты настройки группы доступности для распределенных транзакций

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

  • Экземпляр сервера SQL Server.
  • База данных в группе доступности, настроенная для распределенных транзакций.
  • Служба DTC — также может быть диспетчером транзакций.
  • Другие источники данных.

Для участия в распределенных транзакциях экземпляр SQL Server входит в DTC. Обычно экземпляр SQL Server закручивается с DTC на локальном сервере. Каждый экземпляр SQL Server создает диспетчер ресурсов с уникальным идентификатором диспетчера ресурсов (RMID) и регистрирует его в DTC. В конфигурации по умолчанию все базы данных в экземпляре SQL Server используют один и тот же RMID.

Если база данных находится в группе доступности, копия базы данных чтения и записи базы данных или первичная реплика может перейти к другому экземпляру SQL Server. Чтобы обеспечить поддержку распределенных транзакций во время такого перемещения, каждая база данных должна выступать как отдельный диспетчер ресурсов и иметь уникальный номер RMID. При наличии DTC_SUPPORT = PER_DBгруппы доступности SQL Server создает диспетчер ресурсов для каждой базы данных и регистрируется в DTC с помощью уникального RMID. В этой конфигурации базы данных является диспетчером ресурсов для транзакций DTC.

Внимание

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

Enlist operation failed: 0x8004d101(XACT_E_TOOMANY_ENLISTMENTS). SQL Server couldn't register with Microsoft Distributed Transaction Coordinator (MSDTC) as a resource manager for this transaction. The transaction might have been stopped by the client or the resource manager.

Дополнительные сведения о распределенных транзакциях в SQL Server см. в разделе "Распределенные транзакции"

Управление неразрешенными транзакциями

Результат активных транзакций, существующих во время изменения RMID, не может быть восстановлен после отработки отказа. Это связано с тем, что для присоединения и восстановления используются разные экземпляры SQL Server RMID. RMID может измениться в следующих случаях:

  • Изменение DTC_SUPPORT для группы доступности.
  • Добавление базы данных в группы доступности и удаление из нее.
  • Сброс группы доступности.

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

В новом журнале ошибок SQL Server есть запись, как показано в следующем примере:

Microsoft Distributed Transaction Coordinator (MSDTC)
failed to reenlist citing that the database RMID does
not match the RMID [xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx]
associated with the transaction.  Please manually resolve
the transaction.

SQL Server detected a DTC/KTM in-doubt transaction with UOW
{yyyyyyyy-yyyy-yyyy-yyyy-yyyyyyyyyyyy}.Please resolve it
following the guideline for Troubleshooting DTC Transactions.

В предыдущем примере показано, что DTC не удалось повторно заручиться базой данных из новой первичной реплики в транзакции, созданной после отработки отказа. Экземпляр SQL Server не может определить результат распределенной транзакции, поэтому он помечает базу данных как подозрительную. Транзакция помечается как единица работы (UOW) и обозначается с помощью GUID. Чтобы восстановить базу данных, зафиксируйте транзакцию или выполните отказ вручную.

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

Ручная фиксация или откат транзакции могут негативно сказаться на приложении. Убедитесь, что данная операция соответствует требованиям вашего приложения.

Выполните только один из приведенных ниже сценариев.

  • Чтобы зафиксировать транзакцию, обновите и выполните следующий скрипт, заменив yyyyyyyy-yyyy-yyyy-yyyy-yyyyyyyyyyyy на UOW сомнительной транзакции из полученного ранее сообщения об ошибке и выполнив следующий код:

    KILL 'yyyyyyyy-yyyy-yyyy-yyyy-yyyyyyyyyyyy' WITH COMMIT;

  • Чтобы выполнить откат транзакции, обновите и выполните следующий скрипт, заменив yyyyyyyy-yyyy-yyyy-yyyy-yyyyyyyyyyyy на UOW сомнительной транзакции из полученного ранее сообщения об ошибке и выполнив следующий код:

    KILL 'yyyyyyyy-yyyy-yyyy-yyyy-yyyyyyyyyyyy' WITH ROLLBACK;

После фиксации или отката транзакции базу данных можно перевести в режим онлайн, используя ALTER DATABASE. Обновите и выполните следующий скрипт, указав имя базы данных вместо имени сомнительной базы данных:

ALTER DATABASE [DB1] SET ONLINE;

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

Связанный контент