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

Статья
04/26/2023

Применимо к:SQL Server

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

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

Примечание

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

Конфигурация SQL Server 2016 (13.x); настраивается точно так же, как и конфигурация SQL Server 2017 (14.x);.

В распределенной транзакции клиентские приложения работают с координатором распределенных транзакций Майкрософт (MS DTC или 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, доступное по адресу https://support.microsoft.com/kb/3090973.

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

Настройте группу доступности для поддержки распределенных транзакций. Разрешите группе доступности регистрировать каждую базу данных как диспетчер ресурсов. В этой статье рассказывается, как настроить группы доступности таким образом, чтобы каждая база данных могла быть диспетчером ресурсов в 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 (SP2), группу доступности для распределенных транзакций можно изменять. В версиях SQL Server 2016 (13.x); до пакета обновления 2 (SP2) потребуется удалить и вновь создать группу доступности с параметром DTC_SUPPORT = PER_DB.

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

ALTER AVAILABILITY GROUP MyaAG
   SET (
      DTC_SUPPORT = NONE  
      );

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

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

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

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

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

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

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

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

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

Экземпляр SQL Server указывается в транзакции DTC. Это может произойти, если в транзакции участвуют сразу несколько диспетчеров ресурсов либо клиент запрашивает транзакцию, которая получает статус транзакции DTC.
Клиент выполняет определенные действия в экземпляре SQL Server в рамках транзакции DTC.
Клиент запускает фиксацию или отмену транзакции DTC.
- Если клиент запускает отмену, транзакция незамедлительно прерывается.
- Если клиент запускает фиксацию, DTC начинает протокол двухфазной фиксации, отправляя всем диспетчерам ресурсов запрос о подготовке транзакции.
После того как все диспетчеры ресурсов подтвердят завершение стадии подготовки, 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 could not register with Microsoft Distributed Transaction Coordinator (MS DTC) as a resource manager for this transaction. The transaction may 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 (MS DTC) 
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. Обновите и выполните следующий скрипт, указав имя базы данных вместо имени сомнительной базы данных: