Настройка распределенных транзакций для группы доступности 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), предшествующих Service Pack 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 информирует все диспетчеры ресурсов о необходимости зафиксировать транзакцию. Если что-либо препятствует успешному подтверждению, 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, используемый для регистрации, и 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;

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

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

  • Last updated on