Как я могу использовать транзакции, которые охватывают процедуры, связанные на нескольких серверах?
Я пытаюсь проверить предложение, которое один из наших поставщиков представил нам для доступа к своей базе данных продуктов, и оно касается запросов и транзакций, охватывающих несколько серверов. Я никогда раньше не делал этого непосредственно в базе данных, и, честно говоря, я ничего не понимаю, поэтому я пытаюсь смоделировать доказательство того, что это работает, по крайней мере, концептуально.
У меня есть два сервера SQL Server 2005. Давайте для аргументации назовем их Server1 и Server2 [удерживайте аплодисменты], каждый из которых содержит фиктивную базу данных. Фиктивная база данных на сервере Server1 называется источником, а база данных на сервере Server2 - местом назначения для простоты. Каждая база данных содержит одну таблицу с именами Input и Output соответственно, поэтому структура квази объясняется следующим образом:
- Server1.Source.dbo.Input
- Server2.Destination.dbo.Output
У меня есть хранимая процедура на Server2 с именем WriteDataToOutput, которая получает один аргумент Varchar и записывает его содержимое в выходную таблицу.
Теперь начинается хитрость:
- Я хочу создать хранимую процедуру на Server1.Source, которая вызывает хранимую процедуру WriteDataToOutput, определенную на Server2, что кажется простым шагом.
- Я хочу, чтобы этот вызов был частью транзакции, чтобы в случае сбоя вызывающей его процедуры откатывалась вся транзакция.
И здесь заканчиваются мои знания о том, что мне делать. Может кто-то указать мне верное направление? Я пробовал это в двух разных базах данных на одном сервере, и он работал нормально, что заставило меня предположить, что он будет работать на разных серверах. Вопрос в том, как мне это сделать? С чего мне начать?
Ответы 6
Вам нужно связать серверы:
http://msdn.microsoft.com/en-us/library/aa213778.aspx
@Dalin - может ли пакет DTS / SSIS запускаться из запроса / хранимой процедуры, и если да, можно ли его откатить в случае сбоя транзакции?
для шага 2 вам необходимо запустить координатор распределенных транзакций, вам также необходимо использовать SET XACT_ABORT ON, чтобы убедиться, что все откатится вам также необходимо включить RPC, который по умолчанию отключен в 2005 году и выше.
Есть целая куча вещей, которые могут укусить вас в шею
Используя связанные серверы, вы можете запускать хранимые процедуры на любом сервере в рамках одной транзакции с помощью DTC (координатора распределенных транзакций). Вы обязательно захотите провести некоторый анализ производительности. Я обнаружил, что некоторые SP, использующие ссылки, могут резко снизить производительность базы данных, особенно если вы попытаетесь объединить наборы результатов с каждого из двух серверов.
+1 Спасибо за совет, я бы посмотрел на его использование для переноса определенных финансовых данных из операционной базы данных в финансовую базу данных. Таким образом, нет необходимости в перекрестных соединениях БД.
Настройте связанный сервер, тогда вы сможете выполнять операции выбора / вставки / обновления на серверах. Что-то вроде:
INSERT INTO Server2.Destination.dbo.Output
SELECT * FROM Input
WHERE <Criteria>
Предполагается, что вы выполняете запрос от Server1.Source, поэтому вам не нужно полностью соответствовать требованиям.
Я бы вызвал хранимую процедуру, определенную на Server2.
не забывайте, что он хочет все откатить, поэтому вам нужно запустить DTC, SET XACT_ABORT ON
Как отмечали другие, я согласен с тем, что связанный сервер - лучший способ.
Вот пара указателей, которые уловили меня, когда я впервые имел дело со связанными серверами:
Если связанный сервер является экземпляром, убедитесь, что вы заключили имя в скобки. Например [ИМЯ СЕРВЕРА \ ИМЯ ЭКЗЕМПЛЯТА].
Используйте псевдоним для таблицы или представления со связанного сервера, иначе вы получите ошибку «Не удается связать составной идентификатор». Существует ограничение, состоящее из 4 частей. Например, SERVER.DATABASE.dbo.TABLE.FIELD состоит из пяти частей и выдаст ошибку. Однако
SELECT linked.FieldName FROM SERVER.DATABASE.dbo.TABLE AS linkedбудет работать нормально.
+1 Спасибо, что указали на возможные подводные камни и решения
MSDN говорит, что вы можете иметь транзакции между связанными серверами, если используете команду BEGIN DISTRIBUTED TRANSACTION.
Я помню, что у меня были проблемы, называемые хранимой процедурой на связанном сервере, но я работал над этим, а не решал.
В качестве альтернативы вы можете просто создать пакет DTS / SSIS.