InfoCity
InfoCity - виртуальный город компьютерной документации
Реклама на сайте







Размещение сквозной ссылки

 

Репликация пользовательского кода в SQL Server

Перевод:Маргариты Баскаковой

Репликация стаей - таблиц помогает синхронизировать данные в нескольких базах данных. Но что относительно хранимых процедур, представлений, и пользовательских функций? Вы должны применить те же самые изменения кода на множестве серверов, которыми управляете? К счастью, есть способ синхронизировать схему статей, основанных не на таблице; а репликация исполнения хранимых процедур может обеспечить более высокую производительность, чем репликация отдельных команд при добавлении, изменении или удалении строк в таблице. Продолжайте читать, чтобы узнать, как это сделать!
Предыдущие статьи автора познакомили Вас с репликацией в Microsoft SQL Server 2000. После прочтения их, Вы должны иметь хорошее представление о концепции репликации и быть готовы изучить некоторые полезные детали. Эта статья продемонстрирует Вам, как реплицировать пользовательский код: хранимые процедуры, пользовательские функции (UDFs) и представления.

Репликация статей, основанных не на таблицах

Каждая публикация SQL Server состоит из статей. Статья - объект базы данных: таблица, представление, хранимая процедура, или UDF. Реплицирование пользовательского кода базируется на той же самой концепции, что и реплицирование таблиц; однако, есть существенное отличие.
При реплицировании статей таблицы, изменения данных немедленно читаются log reader agent и затем доставляются подписчикам с помощью distribution agent. Вы можете так настроить агентов репликации, что бы они работали непрерывно (таким образом, гарантируя более быструю доставку изменений) или периодически. По умолчанию, SQL Server использует хранимые процедуры для применения реплицируемых транзакций на подписчиках.
Когда Вы реплицируете представления, пользовательские функции (UDFs) и хранимые процедуры изменения на эти объекты не посылаются подписчикам постоянно; действительно, трудно представить среду с потребностью постоянно модернизировать пользовательский код базы данных. Вместо этого, репликация пользовательского кода способна предоставить администратору баз данных (DBA) более удобный способ, помогающий поддерживать актуальную копию всего пользовательского кода на множестве серверов. Например, предположите, что Вы - администратор базы данных, управляющий 30 серверами, которые составляют основу некоторого приложения, каждый из которых обслуживает различные пользовательские базы. Когда Вы развертываете изменения для Вашего приложения, у вас есть выбор: Вы можете запустить некоторые скрипты на 30 различных серверах, или развертывать скрипты однократно и реплицировать схему вашего пользовательского кода остальной части серверов. Если Вы похожи на автора и любите спать ночью, Вы выберите последний вариант.
Еще одно различие заключается в том, что изменения в пользовательском коде не применяются через хранимые процедуры. Вместо этого, такие изменения доставляются посредствам создания на издателе, содержащего эти изменения, моментального снимка, и затем этот снимок применяется на подписчике(ках).
Репликация хранимых процедур предлагает очень интересную возможность; Вы можете реплицировать не только схему процедур, но также и их выполнение. Фактически, репликация выполнения хранимых процедур может использоваться как альтернатива репликации таблиц. Если Вы реплицируете таблицы, log reader agent должен прочитать в журнале запись каждой модификации данных и перевести это в команду репликации. Например, если Вы выполняете публикуемую хранимую процедуру, изменяющую таблицы sales, titles и discount в базе данных PUBS, репликация разбивает это действие на три транзакции - по одной для обновления каждой таблицы.
Одна хранимая процедура может изменять несколько таблиц, значит, репликация ее исполнения может быть значительно эффективней, чем разбиение каждой команды на INSERT, UPDATE и DELETE отдельных строк и репликация изменений данных таким способом.
Следующая глава продемонстрирует Вам, как настроить репликацию хранимых процедур, представлений и UDFs (обсудив все "за" и "против" каждого способа).

Установка

Настройка репликации представлений, UDFs и хранимых процедур очень похожа на настройку репликации транзакций для статей таблицы. Пожалуйста, обратитесь к более ранним статьям автора для детализации краткого обзора установки репликации транзакций.
На шаге "Specify Articles" Мастера Создания Публикации (Create Publication Wizard) Вы можете выбрать представления, хранимые процедуры или UDFs вместо таблиц, как показано на следующем рисунке (См. Рис. 1).


Рис. 1

Обратите внимание, что к базе данных Pubs были добавлены хранимая процедура populate_discounts и UDF udf_check_business_day специально для этой статьи. Если Вы хотите опробовать примеры, показанные здесь, запустите следующий скрипт в вашей базе данных Pubs:

IF EXISTS (SELECT 1 FROM sysobjects WHERE type = 'p' AND name = 'populate_discounts') BEGIN DROP PROCEDURE populate_discounts END GO CREATE PROC populate_discounts ( @discounttype VARCHAR(45), @stor_id INT, @lowqty INT, @highqty INT, @discount INT) AS SET NOCOUNT ON BEGIN TRAN INSERT discounts ( discounttype, stor_id, lowqty, highqty, discount) SELECT @discounttype, @stor_id, @lowqty, @highqty, @discount IF @@ERROR <> 0 BEGIN RAISERROR ('did not work, please try again', 16, 1) ROLLBACK RETURN END UPDATE sales SET payterms = 'Net 120' WHERE stor_id = @stor_id IF @@ERROR <> 0 BEGIN RAISERROR ('did not work, please try again', 16, 1) ROLLBACK RETURN END COMMIT TRAN GO IF EXISTS (SELECT 1 FROM sysobjects WHERE type = 'fn' AND name = 'udf_check_business_day') BEGIN DROP FUNCTION udf_check_business_day END GO CREATE FUNCTION udf_check_business_day (@ord_num VARCHAR(15)) RETURNS BIT AS BEGIN DECLARE @business_day INT SELECT @business_day = CASE WHEN DATEPART (WEEKDAY, ord_date) NOT IN (1, 7) THEN 1 ELSE 0 END FROM sales WHERE ord_num = @ord_num RETURN @business_day END

Каждый тип пользовательского кода имеет различные опции репликации, которые Вы можете выбрать в ходе установки. Все объекты позволяют Вам включить расширенные свойства вместе со схемой статьи. Если Вы не знакомы с расширенными свойствами - это способ четко отследить специфические для приложения метаданные базы данных. При репликации представлений, Вы также можете реплицировать и триггеры, построенные на представлениях. Обратите внимание, что хотя триггеры - по существу особый случай хранимых процедур, Вы не можете явно реплицировать исполнение или схему триггеров. Хранимые процедуры позволяют Вам реплицировать каждое выполнение процедуры или только исполнение в рамках сериализуемой транзакции (serializable transaction). Все эти варианты репликации формируются, используя закладку статей "Other". Следующий рисунок демонстрирует закладку"Other" окна Stored Procedure Article Properties (См. Рис. 2).


Рис. 2

Когда Вы издаете пользовательский код, Мастер Создания Публикации (Create Publication Wizard) предупреждает Вас, что вашему приложению потребуются изменения, чтобы оно правильно работало. Для создания представлений, определяемых пользователем функций и хранимых процедур на подписчике необходимо наличие всех таблиц, представлений или UDFs, на которых базируются эти модули кода. После того, как Вы убедитесь, что все необходимые объекты существуют на подписчике (ах), Вы можете завершить работу мастера.
Настройка подписки для публикации, включающей статьи, основанные не на таблицах, идентична подписке на табличные публикации.

ПРИМЕЧАНИЕ: Пожалуйста, обратитесь к статье автора "Setting Up Transactional Replication with SQL Server" на сайте InformIT для более подробного изучения вышеизложенного.

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

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

Статьи, основанные не на таблицах, будут синхронизированы только когда Snapshot agent запущен, и инициализированы базы данных подписчика. Для этой статьи, была созданы хранимая процедура и UDF, которые Вы видели ранее, так же как представление titleview, включенное в базу данных Pubs.
Сразу после создания подписки, Enterprise Manager сообщит, что статус подписки принял значение "Pending" (как показано на следующем рисунке), потому что Snapshot agent еще не собрал снимок статей (См. Рис. 3).


Рис. 3

После того, как Вы запустите Snapshot agent, он создаст сценарии, чтобы сгенерировать реплицируемые статьи для подписчика. Эти сценарии находятся в папке снимка, которую Вы определили в процессе установки публикации. (По умолчанию, это папка - Program Files\Microsoft SQL Server\MSSQL\REPLDATA\UNC\server_name_publication_name_subscription_name). После того, как отработают log reader agent и distribution agent, SQL Server удалит сценарии снимка. Когда снимок будет доставлен, колонка Status примет значение "Active".
Хотя Вы можете запускать Snapshot agent так часто, как Вам нравится, он производит снимок изданных статей, только если подписки помечены для реинициализации. Если Snapshot agent не находит подписок, которые нужно реинициализировать, он просто возвращает сообщение, видимое в мониторе репликации (как показано на следующем рисунке): "снимок не создан, потому что нет подписок, нуждающихся в инициализации" (См. Рис 4).


Рис. 4

Вы могли подумать о том, что если Вы измените определение реплицируемого пользовательского кода, то SQL Server автоматически реинициализирует подписки, но если Вы измените реплицируемую хранимую процедуру и запустите Snapshot agent вручную - Вы получите то же самое сообщение. Кроме того, если Вы проверите код реплицируемой процедуры на подписчике, Вы заметите, что изменения там не были применены.
Чтобы повторно инициализировать подписку, Вы должны навести курсор на публикацию в Enterprise Manager, щелкнуть правой кнопкой мыши на нужной подписке и выбрать реинициализацию. Тогда SQL Server выполнит системную хранимую процедуру sp_reinitsubscription, которая в свою очередь вызовет хранимую процедуру sp_changesubstatus - эта процедура изменит колонку Status системной таблицы syssubscriptions, находящейся в базе данных издателя. Это колонка Status, которая определяет реинициализируется ли подписка. Когда Вы реинициализируете подписку, Enterprise Manager изменит значение колонки Status на "Pending", так же, как это было в момент начальной синхронизации. В следующий раз запуск Snapshot agent выдаст сообщение о том, что был создан моментальный снимок реплицируемых статей, как это показано на следующем рисунке (См. Рис. 5).


Рис. 5

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

ПРИМЕЧАНИЕ: Вы также можете реинициализировать подписчиков для проверки свойств публикации, наведя курсор на Subscriptions и выбрав Re-initialize.

Варианты выполнения хранимой процедуры

Статьи, основанные на хранимых процедурах, могут реплицироваться каждый раз, когда они выполняются на издателе или только когда они исполняются в рамках сериализуемой транзакции (serializable transaction). Если Вы вернётесь к экрану свойств публикации и попытаетесь изменить свойства статьи, Вы увидите, что Вы не можете изменить опции выполнения хранимой процедуры - они недоступны (см. рис. 6).


Рис. 6

Чтобы сделать опции выполнения хранимой процедуры доступными, Вы должны удалить все подписки на эту публикацию и удалить статью из публикации. После того, как Вы снова вернёте статью в публикацию, все опции опять будут Вам доступны.
Для чего Вам может понадобиться репликация выполнения хранимой процедуры? Пример процедуры, которым автор снабдил эту статью, наполняет данными таблицу discounts и затем обновляет связанные строки в таблице sales. Предположим, что мы реплицировали таблицы discounts и sales вместо того, чтобы реплицировать выполнение хранимой процедуры populate_discounts. И предположим, что мы выполнили процедуру следующим образом:

    EXEC populate_discounts 'great discount', 6380, 10, 90, 20

Результатом стали бы следующими три команды, переданные в базу данных дистрибутора и затем далее серверу подписчика:

{CALL sp_MSupd_sales (NULL, NULL, NULL, NULL, 'Net 120',NULL,'6380','6871','BU1032',0x10)}
{CALL sp_MSupd_sales (NULL, NULL, NULL, NULL, 'Net 120',NULL,'6380','722a','PS2091',0x10)}
{CALL sp_MSins_discounts ('great discount', '6380', 10, 90, 20.00)}

Обратите внимание, что для каждой строки, измененной в таблице sales, репликация сделает отдельный вызов процедуры sp_MSupd_sales; точно так же каждая строка, добавленная в таблицу discounts, приведёт к вызову процедуру sp_MSins_discounts. Теперь давайте посмотрим, что происходит, если мы реплицируем только выполнение хранимой процедуры populate_discounts. Сначала, как только мы выберем репликацию выполнения хранимой процедуры, SQL Server выдаст следующее разумное предупреждение (См. Рис. 7).


Рис. 7

Пока, выберите Yes, что бы продолжить. После того, как мы подписались на публикацию, содержащую процедуру populate_discounts, выполним процедуру на издателе следующим образом:

EXEC populate_discounts 'fabulous discount', 7067, 100, 1000, 25

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

{call "dbo"."populate_discounts" ('fabulous discount', 7067, 100, 1000, 25)}

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

Выполнение процедур внутри сериализуемых транзакций

Вспомните, что Вы можете реплицировать каждое выполнение хранимой процедуры или только то выполнение, которое происходит в пределах сериализуемой транзакции (serializable transaction). Сериализуемые транзакции требуют (и это не удивительно), чтоб уровень изоляции транзакции был установлен в значение SERIALIZABLE.

ПРИМЕЧАНИЕ: Пожалуйста, обратитесь к статье автора "SQL Server: Details of Locking", чтобы узнать о различных уровнях изоляции транзакций, поддерживаемых SQL Server.

Если мы реплицируем каждое выполнение хранимой процедуры, SQL Server будет делать попытку реплицировать даже такое исполнение, которое закончилось ошибкой на издателе. Например, предположим, что выполняется процедура populate_discounts с неправильным значением store id:

EXEC populate_discounts 'special special discount', 134567, 100, 1000, 25

В результате получим:

Server: Msg 547, Level 16, State 1, Procedure populate_discounts, Line 10 INSERT statement conflicted with COLUMN FOREIGN KEY constraint 'FK__discounts__stor___0F975522'. The conflict occurred in database 'pubs', table 'stores', column 'stor_id'. Server: Msg 50000, Level 16, State 1, Procedure populate_discounts, Line 24 did not work, please try again The statement has been terminated.

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

{call "dbo"."populate_discounts" ('special special discount', 134567, 100, 1000, 25)}

Кстати, этот вызов хранимой процедуры также потерпит неудачу на сервере подписчика, потому что store id = 134567 также не существует в базе данных подписчика.
Теперь посмотрим, что произойдёт, если мы хотим реплицировать только то выполнение процедуры, которое заключено в пределах SERIALIZABLE транзакции. Имейте в виду, что Вы должны будете удалить подписку, а так же статью populate_discounts из публикации прежде, чем Вы сможете настроить эту процедуру, для репликации исполнения только в рамках сериализуемой транзакции (serializable transaction). После того, как Вы настроили репликацию, попробуйте выполнить следующее:

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE GO BEGIN TRAN EXEC populate_discounts 'fabulous customer special', 114251, 50, 1000, 25 IF @@ERROR <>0 BEGIN ROLLBACK END ELSE COMMIT

Выполнение приведенного выше скрипта потерпит неудачу, потому что store id = 114251 не существует. Если Вы проверите базу данных дистрибутора - эта команда никогда не рассматривалась для репликации. Log reader agent просто сообщил, что нет транзакций, доступных репликации. Теперь выполним ту же самую процедуру с существующим store id:

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE GO BEGIN TRAN EXEC populate_discounts 'customer appreciation special', 7066, 50, 1000, 25 IF @@ERROR <>0 BEGIN ROLLBACK END ELSE COMMIT

Выполнение приведенного выше скрипта отработает на издателе и реплицируется подписчику. Таким образом, репликация исполнения процедур только в рамках сериализуемой транзакции (serializable transaction) гарантирует, что они будут выполнены на подписчике, только если они достигли цели на издателе. Имейте в виду, однако, что SERIALIZABLE уровень изоляции - самый ограничивающий способ блокировки и может иметь отрицательное воздействие на производительность вашего приложения.

Резюме

Цель этой статьи состоит в том, чтобы продемонстрировать пошаговую инструкцию применения репликации транзакций для тиражирования схемы и исполнения хранимых процедур, а так же схемы представлений и UDFs. Репликация пользовательского кода, хотя и похожа на репликацию данных таблицы, имеет существенное отличие, потому что требует переинициализации подписок. В отличие от репликации данных таблицы, схема пользовательского кода реплицируется только, когда Snapshot agent запущен и генерирует снимок для инициализации подписок.
SQL Server поддерживает репликацию исполнения хранимых процедур. При внимательном планировании, Вы можете использовать эти функциональные возможности в своих интересах, чтобы тиражировать изменения данных более эффективно, чем в случае с репликацией таблицы.


Реклама на InfoCity

Яндекс цитирования



Финансы: форекс для тебя








1999-2009 © InfoCity.kiev.ua