Репликация SQL Server 2005/2008. Сборник статей от сообщества SQL.RU
Под общей редакцией А. Гладченко и В. Щербинина.
Москва. ЭКОМ Паблишерз, 2008Г. 288 страниц. ISBN: 978-5-9790-0086-2. Книга уже в продаже.
Эта книга - сборник статей, которые посвящены ключевым для понимания репликации SQL Server моментам. Кроме переводов наиболее интересных зарубежных авторов, являющимисяхся признанными во всём мире специалистами, в книге вы найдёте ряд статей участников сообщества
SQL.RU, которые многие годы помогаю решать разнообразные проблемы на технических форумах этого замечательного интернет - ресурса. Если во время прочтения книги у вас возникнут сомнения или вопросы по поводу изложенных тут материалов, смело обращайтесь к авторам статей на форумах SQL.RU, они с удовольствием вам помогут.
В сборник включены переводы и статьи следующих участников сообщества SQL.RU:
Дмитрий Артёмов,
Александр Волок,
Александр Гладченко, Ильдар Даутов, Григорий Кoрнилов, Алексей Ковалёв, Наталья Кривонос,
Ян Либерман,
Ирина Наумова и
Владислав Щербинин. Причём, на момент написания книги, Александр Гладченко, Ян Либерман и Ирина Наумова являлись SQL Server
MVP.
Книга в первую очередь ориентирована на администраторов баз данных, которые собираются углубить свои познания в репликации SQL Server. Назначение этой книги состоит в том, чтобы предоставить читателям набор апробированных в течение нескольких лет рецептов по использованию и настройке репликации в SQL Server.
ЗАКАЗАТЬ
Обсудить книгу можно тут: http://www.sql.ru/forum/actualthread.aspx?tid=643700
Пример статьи из сборника:
Репликация программируемых объектов БД в SQL Server 2005
По материалам статьи Байя Павлиашвили (Baya Pavliashvili) «Replicating Code Modules with SQL Server 2005».
Перевод Ирины Николаевны Наумовой
В предыдущих статьях из этой серии рассказывалось о том, как организовать в SQL Server 2005 репликацию статей представляющих собой таблицы. Как и предыдущие версии, SQL Server 2005 предоставляет возможность репликации модулей кода: хранимые процедуры, представления (включая индексированные представления) и пользовательские функции (UDF). В этой статье дается краткий обзор репликации программируемых объектов и даются рекомендации по использованию репликации таких модулей.
Настройка репликации программируемых объектов.
Репликация программируемых объектов настраивается также как и репликация таблиц. Публикация в качестве статей может содержать таблицы, индексированные представления, представления, пользовательские функции и хранимые процедуры. Причины, по которым программируемые объекты могут быть добавлены в репликацию, приведены в таблице ниже.
|
Тип статьи
|
Причина
|
|
Представление
|
Таблицы, на которых базируется представление, должны существовать на подписчике. Однако эти таблицы могут не участвовать в репликации.
|
|
Индексированное представление
|
Таблицы, на которых базируется представление, должны существовать на подписчике. Однако эти таблицы не участвуют в репликации.
На серверах – подписчиках должна быть установлена версия SQL Server 2000 и выше. Все подписчики должны использовать SQL Server в редакции Enterprise Edition.
|
|
Хранимые процедуры, определяемые пользователем функции
|
Все объекты, упомянутые в хранимой процедуре или пользовательской функции должны существовать на подписчике. Однако, эти объекты могут не участвовать в репликации.
|
Для добавления в репликацию представлений, пользовательских функций и хранимых процедур можно воспользоваться мастером создания публикации. После того как вы откроете публикацию нужного типа для издаваемой базы данных, можно приступить непосредственно к работе со статьёй. На следующем рисунке продемонстрировано добавление к публикации представления, индексированного представления, хранимой процедуры и пользовательской функции.
Рис.1.
Чтобы выбрать опции для каждой добавляемой в публикацию статей, нужно нажать кнопку Article Properties. Можно выбрать несколько опций для каждого типа реплицируемых программируемых объектов. Также можно реплицировать схему представлений, индексируемых представлений и пользовательских функций. Для хранимых процедур предусмотрена дополнительная гибкость – помимо их определения вы можете реплицировать и их выполнение. В таблицу ниже сведены опции, доступные для настройки при репликации программируемых объектов.
|
Тип статьи
|
Опция/значение
|
Описание
|
|
Представления
|
Copy User Triggers: True or False
|
Для реплицируемого представления создается триггер в базе данных подписчика, если он есть на издателе.
|
|
Представления, индексированные представления, хранимые процедуры, определяемые пользователем функции.
|
Copy Extended Properties: True or False
|
На подписчике создаются расширенные свойства реплицируемого представления.
|
|
Представления, индексированные представления, хранимые процедуры, определяемые пользователем функции.
|
Destination Object Name/Destination Object Owner
|
Вы можете создать реплицируемый объект с тем же именем что и на издателе и владельцем объекта или с другим именем и/или владельцем.
|
|
Представления, индексированные представления, хранимые процедуры, определяемые пользователем функции.User Defined Function
|
Action if name in use: Keep existing object unchanged OR Drop existing object and create a new one
|
Запомните, если сохраняете существующий объект, определение этого программируемого модуля на издателе и подписчике может быть разным.
|
|
Представления, индексированные представления, хранимые процедуры, определяемые пользователем функции.
|
Create Schemas at Subscriber: True or False
|
Определяет, должен ли выполняться оператор CREATE SCHEMA на подписчике, если там нет схемы объекта.
|
|
Хранимые процедуры
|
Replicate: Stored procedure definition only; Execution of the stored procedure; Execution in a serialized transaction of the SP.
|
Определяет, должно ли реплицироваться выполнение хранимой процедуры.
Запомните, оператор ALTER PROCEDURE будет реплицировать изменения схемы даже для публикаций, в которых реплицируется выполнение хранимой процедуры, таким образом, изменения в определении хранимой процедуры будут всегда доставлены подписчику (если только вы явно не задали репликацию изменений схемы).
|
Запомните: Некоторые опции для статьи нельзя изменить после создания публикации. Например, опция Procedure Replicate не может быть изменена на Stored Procedure Definition, изменить её можно только на Execution Of The Stored Procedure. Чтобы изменить эту опцию, можно удалить статью и добавить ее заново, а затем уже изменить опцию. Так что перед настройкой репликации, определите заранее какие опции вам нужно будет установить.
Как только Вы установили свойства для каждой статьи, можно создать снимок для публикации немедленно и/или создать расписание для запуска Snapshot Agent. Следующим шагом необходимо определить параметры настройки безопасности для Snapshot Agent и Log Reader agent, проанализируйте то, что должен сделать мастер, и нажмите кнопку Finish, чтобы создать публикацию.
Мастер создания публикации предоставит данные о каждом типе программируемых объектов, которые вы пытаетесь реплицировать. Однако, публикация может быть создана даже если на подписчике нет объектов, на которые она использует.
Процесс создания подписки для публикации программируемых объектов идентичен процессу репликации табличных статей. Если любой из объектов, на которые ссылается копируемый модуль кода, отсутствует на подписчике, агент распределения уведомит вас об ошибке, но подписка будет создана. Сообщения об ошибках в работе агента распределения помогают понять причины проблем, потому что с помощью этих сведений можно определить закончившуюся ошибкой команду (см. следующий рисунок).
Рис.2.
Примечание: Ошибки, возникающие при репликации программируемых объектов, обычно легко обнаружимы. Таких ошибок можно избежать, если внимательно читать экраны мастера и выполнить все требования.
Следующий сценарий создает публикацию, в которую входят хранимая процедура, представление, индексированное представление и пользовательская функция:
exec sp_addpublication @publication = n’pub_name’,
@description=N’Transactional publication of database ’’AdventureWorksDW’’ .’,
@sync_method = N’concurrent’, @retention = 0, @allow_push = N’true’,
@allow_pull = N’true’, @allow_anonymous = N’true’,
@enabled_for_internet = N’false’, @snapshot_in_defaultfolder = N’true’,
@compress_snapshot = N’false’, @ftp_port = 21, @ftp_login = N’anonymous’,
@allow_subscription_copy = N’false’, @add_to_active_directory = N’false’,
@repl_freq = N’continuous’, @status = N’active’,
@independent_agent = N’true’, @immediate_sync = N’true’,
@allow_sync_tran = N’false’, @autogen_sync_procs = N’false’,
@allow_queued_tran = N’false’, @allow_dts = N’false’, @replicate_ddl = 1,
@allow_initialize_from_backup = N’false’, @enabled_for_p2p = N’false’,
@enabled_for_het_sub = N’false’
GO
--Добавление статьей в публикацию репликации транзакций
--Определяемая пользователем функция:
exec sp_addarticle @publication = n’pub_name’,
@article = N’udfMinimumDate’, @source_owner = N’dbo’,
@source_object = N’udfMinimumDate’, @type = N’func schema only’,
@description = N’’, @creation_script = N’’, @pre_creation_cmd = N’drop’,
@schema_option = 0x0000000008000001, @destination_table = N’udfMinimumDate’,
@destination_owner = N’dbo’, @status = 16
GO
--Хранимая процедура:
exec sp_addarticle @publication = n’pub_name’,
@article = N’update_factFinance’, @source_owner = N’dbo’,
@source_object = N’update_factFinance’, @type = N’proc exec’,
@description = N’’, @creation_script = N’’, @pre_creation_cmd = N’drop’,
@schema_option = 0x0000000008000001, @destination_table = N’update_factFinance’,
@destination_owner = N’dbo’, @status = 0
GO
--Индексированное представление:
exec sp_addarticle @publication = n’pub_name’,
@article = N’View_DimCustomer_Young’, @source_owner = N’dbo’,
@source_object = N’View_DimCustomer_Young’, @type = N’indexed view schema only’,
@description = N’’, @creation_script = N’’, @pre_creation_cmd = N’drop’,
@schema_option = 0x0000000008000001, @destination_table = N’View_DimCustomer_Young’,
@destination_owner = N’dbo’, @status = 16
GO
--Представление:
exec sp_addarticle @publication = n’pub_name’,
@article = N’vTimeSeries’, @source_owner = N’dbo’,
@source_object = N’vTimeSeries’, @type = N’view schema only’, @description = N’’,
@creation_script = N’’, @pre_creation_cmd = N’drop’,
@schema_option = 0x0000000008000001, @destination_table = N’vTimeSeries’,
@destination_owner = N’dbo’, @status = 16
GO
Изменение схемы репликации
Вспомните, в предыдущей версии SQL Server для того, чтобы определения программируемых объектов передались подписчику, нужно было запустить агента создания снимка. В SQL Server 2005 это уже не так: репликация передает операторы ALTER VIEW, ALTER FUNCTION, ALTER PROCEDURE и ALTER TRIGGER подписчику в реальном времени. Опция репликации триггеров уже не является единственной возможностью для статей соответствующих типов, но она по-прежнему позволяет копировать триггеры, определенные на таблице или представлении в публикуемой базе данных. Запомните, что нельзя реплицировать DDL триггеры (триггеры языка определения данных).
Давайте рассмотрим репликацию изменения индексируемого представления. Я создал очень простой пример индексируемого представления на издателе с помощью следующих команд:
CREATE VIEW [dbo].[View_DimCustomer_Young]
WITH SCHEMABINDING
AS
SELECT CustomerKey, GeographyKey, FirstName, LastName, BirthDate
FROM dbo.DimCustomer
WHERE (BirthDate > CONVERT(SMALLDATETIME, ’1/1/1980’, 101))
GO
CREATE UNIQUE CLUSTERED INDEX [ix_DCY_CustomerKey] ON [dbo].[View_DimCustomer_Young]
(
[CustomerKey] ASC
)WITH (PAD_INDEX = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF)
ON [PRIMARY]
Это представление возвращает несколько столбцов таблицы DimCustomer для записей клиентов, которые родились после 1 января 1980 года. Я добавил это представление в публикацию репликации транзакций и создал для нее подписку на другом сервере.
Теперь давайте изменим представление, чтобы оно возвращало клиентов, родившихся после 1 января 1978 года. Для этого я выполнил следующий код:
ALTER VIEW [dbo].[View_DimCustomer_Young]
WITH SCHEMABINDING
AS
SELECT CustomerKey, GeographyKey, FirstName, LastName, BirthDate
FROM dbo.DimCustomer
WHERE (BirthDate > CONVERT(SMALLDATETIME, ’1/1/1978’, 101))
Теперь, если я в контексте базы данных распространителя выполняю системную хранимую процедуру sp_browserplcmds, я найду там команду ALTER VIEW, которая предназначена для передачи подписчику.
Репликация программируемых модулей особенно полезна, если Вы работаете в мультисерверной среде и распределяете прикладную нагрузку на несколько серверов с идентичными представлениями, пользовательскими функциями и хранимыми процедурами. Вместо того, чтобы применить изменения схемы на нескольких серверах, можно просто выполнить эти изменения на одном сервере-издателе, после чего эти изменения будут растиражированы для всех подписчиков. В предыдущих версиях SQL Server нужно было запустить Snapshot агента, который бы создал моментальный снимок, с помощью которого изменения схемы доставляются подписчикам. В версии SQL Server 2005 изменения схемы будут доставляться также как изменения данных. Это позволяет значительно упростить и ускорить задачи развертывания приложений.
Иногда может понадобиться, чтобы изменения схемы не передавались пока не отработает Snapshot агент. Например, для того чтобы проверить как передаются изменения хранимой процедуры на один из серверов перед развертыванием этих изменений на все промышленные сервера, вы можете отключить передачу изменений схемы в опциях подписчика диалогового окна свойств публикации, показанного ниже.
Рис.3.
Репликация индексируемых представлений как таблиц
Репликация индексированных представлений осуществляется также как репликация таблиц; в этом случае, SQL Server создает таблицу на подписчике, которая содержит те же данные, что и индексированное представление на издателе. Данные изменяются в индексированном представлении на издателе и передаются в таблицу на подписчике. Обратите внимание, что таблица, на которой основано индексированное представление, не обязана существовать на подписчике.
Репликация индексированных представлений как таблиц может быть полезна, когда на подписчике необходимо только лишь подмножество данных таблицы. Например, если на подписчике нужна информация только о клиентах, дата рождения которых больше 1.1.1998 , можно настроить репликацию индексированного представления, приведенного ниже, используя его в качестве статьи, вместо того чтобы копировать всю таблицу dimCustomer.
Для того чтобы выполнять репликацию индексированного представления как таблицы, нужно изменить параметр @type системной процедуры sp_addarticle. По умолчанию этот параметр установлен в значение N’indexed view logbased’. Например, следующий код добавит индексированное представление View_DimCustomer_Young в существующую публикацию как таблицу.
exec sp_addarticle
@publication = N’publication_name’,
@article = N’View_DimCustomer_Young’,
@source_owner = N’dbo’,
@source_object = N’View_DimCustomer_Young’,
@type = N’indexed view logbased’,
@description = null,
@creation_script = null,
@pre_creation_cmd = N’none’,
@schema_option = 0x0000000008000001,
/* table name doesn’t have to be the same as view name */
@destination_table = N’View_DimCustomer_Young’,
@destination_owner = N’dbo’
GO
После настройки репликации индексированного представления как таблицы, операторы INSERT, UPDATE и DELETE, выполненные на представлении на издателе, будут реплицированы в таблицу на подписчике.
Репликация выполнения хранимых процедур
Таким же образом можно настроить репликацию выполнения хранимых процедур, что очень полезно при больших изменениях в имеющихся данных, и при условии, что данные на подписчике и издателе идентичны. Что произойдет, если выполнение оператора UPDATE затрагивает 1000 строк реплицируемой таблицы? По умолчанию SQL Server трансформирует одну команду UPDATE в выполнение хранимой процедуры репликации 1000 раз. Этот вариант хорош тем что каждое выполнение хранимой процедуры репликации затрагивает только одну строку что не вызывает большое количество блокировок/подтверждений на подписчике.
Но что произойдет, если ваша хранимая процедура выполняет изменения, затрагивающие миллион строк в нескольких таблицах? Ваша база данных распределения будет расти экспоненциально, и время задержки репликации может стать недопустимо большим. Перед тем как передать эти команды подписчику SQL Server должен прочитать их из таблицы msrepl_commands базы данных распределения; Агент - чистильщик распределителя занимается удалением транзакции для этих таблиц, когда они уже были переданы подписчику. Если таблица msrepl_commands содержит несколько миллионов строк, чтение и удаление данных из этой таблицы будет выполняться очень медленно. Кроме того, передача больших изменений при использовании табличной статьи, оказывает большую нагрузку на сеть.
Репликация выполнения хранимых процедур предлагает более эффективную альтернативу стандартному методу, потому что при этом выполняется одна и та же хранимая процедура, и на издателе и подписчике, уменьшая, таким образом, сетевой трафик и число команд в базе распределения. Если нужно передать изменения 50 миллионов строк, и известно, что издатель и подписчик идентичны, было бы более эффективно включать в репликацию выполнение хранимой процедуры, которая осуществляет эти изменения.
Другой альтернативой при репликации больших изменений одной таблицы является репликация таблицы путем выполнения одинакового оператора UPDATE на издателе и подписчике (Загляните в первую статью из этой серии, если Вы хотите узнать как это можно сделать). Репликация выполнения хранимой процедуры может быть лучшим выбором, однако, это справедливо обычно для массовых изменений в нескольких таблицах.
Примечание: Если вы выполняете хранимую процедуру внутри явной транзакции, эта транзакция должна быть завершена перед тем как процедура будет реплицирована.
Например, предположим что у нас есть хранимая процедура, которая изменяет некоторое количество строк в таблице factFinance базы данных AdventureWorksDW:
CREATE PROC update_factFinance (
@PercentChange NUMERIC (3,2),
@OrganizationKey TINYINT,
@TimeKey INT)
AS
/*
Изменяем количество выданных ключей
*/
UPDATE factFinance
SET amount = amount * @PercentChange
WHERE OrganizationKey = @OrganizationKey
AND TimeKey = @TimeKey
Создадим публикацию на основе репликации выполнения хранимой процедуры. Каждый раз, когда мы выполняем процедуру на издателе, агент распределения будет передавать подписчику команду, подобную этой:
{call "dbo"."update_factFinance " (1.10, 3, 32)}
Запомните, что репликация будет просто передавать эту команду, репликация не будет проверять, затрагивает ли выполнение команды какие-либо строки на издателе и подписчике. Таким образом, для того чтобы обеспечить целостность данных на издателе и подписчике, необходимо перед использованием репликации выполнения хранимой процедуры удостовериться в том, что данные на подписчике и издателе идентичны.
Репликация выполнения хранимых процедур внутри сериализуемой транзакции.
Хранимую процедуру можно включить в репликацию в качестве статьи, только если она выполняется внутри сериализуемой транзакции. Это требует соблюдения двух условий, обеспечить которые необходимо перед тиражированием процедуры подписчику:
- Уровень изоляции транзакций у подключения, в котором выполняется хранимая процедура, должен быть установлен в SERIALIZABLE.
- Необходимо выполнять процедуру внутри явной транзакции, используя операторы BEGIN TRANSACTION / COMMIT TRANSACTION.
Если хотя бы одно условие не будет выполнено, выполнение хранимой процедуры не реплицируется. Помимо этих обязательных условий, также необходимо использовать опцию SET XACT_ABORT ON. Использование этой опции гарантирует, что изменения, внесенные транзакцией, внутри которой выполняется хранимая процедура, будут автоматически отменены, если возникнут ошибки времени выполнения.
Репликация выполнения хранимых процедур внутри сериализуемой транзакции – это рекомендуемая опция, когда необходимо поддержать целостность данных на издателе и подписчике. Почему? В каждой хранимой процедуре содержится несколько явных или неявных транзакций. Вы можете столкнуться с ситуацией когда некоторые транзакции внутри хранимой процедуры завершаются успешно, а другие с ошибкой. Если Вы заставляете SQL Server реплицировать каждое выполнение хранимой процедуры, тогда даже то выполнение, в котором транзакции завершаются ошибками, будет отправлено подписчику. Уровень изоляции транзакций - SERIALIZABLE, является самым жестким уровнем изоляции, гарантирующим, что блокировки будут установлены на всех таблицах, которые использует хранимая процедура. Блокировки будут удерживаться до тех пор, пока транзакция не будет завершена. Поэтому, использование в репликации выполнение только в пределах сериализуемой транзакции, дает гарантию того, что процедура успешно завершит работу на издателе, и только потом будет послана подписчикам.
Давайте воспользуемся процедурой update_factFinance чтобы продемонстрировать как мы можем реплицировать ее выполнение внутри сериализуемой транзакции. Выполним следующий код:
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
SET XACT_ABORT ON
BEGIN TRAN
EXEC update_factFinance 1.10, 3, 32
COMMIT
Однако, следующая команда не будет реплицирована, потому что она не включена в явную транзакцию:
EXEC update_factFinance 1.10, 3, 32
Теперь мы завершим выполнение процедуры, чтобы продемонстрировать значение установки XACT_ABORT. Я изменяю тип данных столбца amount таблицы factFinance на SMALLINT, вместо INT, выполняя следующую инструкцию:
Максимальное значение для типа SMALLINT – 32768; умножаем максимальное значение столбца на 1.15 чтобы результат превысил 32768, таким образом, следующее выполнение процедуры update_factFinance, приведет к ошибке:
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
/* CORRECT setting*/
SET XACT_ABORT ON
BEGIN TRAN
EXEC update_factFinance 1.15, 3, 32
COMMIT
Результат таков:
Msg 8115, Level 16, State 2, Procedure update_factFinance, Line 10
Arithmetic overflow error converting expression to data type smallint.
Транзакция отменена, и выполнение хранимой процедуры не передано подписчику.
Далее, выполним тот же набор команд, отменив установку XACT_ABORT:
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
/* НКОРРЕКТНАЯ УСТАНОВКА! Это сделано только в демонстрационных целях! */
SET XACT_ABORT OFF
BEGIN TRAN
EXEC update_factFinance 1.15, 3, 32
COMMIT
Выполнение хранимой процедуры завершится с той же ошибкой что и в первый раз. Однако при проверке базы данных распределителя, мы найдем следующую команду, посланную подписчику:
{call "dbo"."update_factFinance" (1.15,3,32)}
Выполнение процедуры передано подписчику даже при условии того, что на издателе оно завершилось с ошибкой. Это приведет к тому, что агент распределения завершит работу с ошибкой. И что еще более важно, может нарушить целостность данных на подписчике и издателе. Поэтому всегда используйте опцию SET XACT_ABORT ON при репликации выполнения хранимых процедур.
Вывод
Данная статья рассказывает о том, как выполняется репликация хранимых процедур, представлений и пользовательских функций в SQL Server 2005. По сравнению с другими версиями, возможно, самым важным усовершенствованием является возможность применить изменения на подписчике без запуска агента создания снимка.
Эта серия статей познакомила вас с репликации транзакций в SQL Server 2005. Построенная на твердой основе предыдущих версий, репликации транзакций в SQL Server 2005 – это зрелая технология, которая способна поддержать приложения класса предприятия. Подобно любой технологии, репликация работает хорошо, если используется к месту и решает присущие ей задачи. Убедитесь, что изучили тонкости работы репликации, и постарайтесь очень тщательно спланировать внедрение решений с ее использованием.