Эта статья посвящена анализу работы механизма MS SQL Server
2000, обеспечивающего автоматическое разбиение диапазонов
полей identity для участвующих в репликации слиянием таблиц на
издателе и подписчиках. Суть решаемой этим механизмом проблемы
состоит в том, что значения identity могут устанавливаться и
издателем и подписчиком самостоятельно. Часто, такие поля, как
identity используются в качестве внешнего ключа. Если
тиражировать эти значения средствами репликации слиянием,
может произойти нарушение ограничения внешнего ключа.
Одинаковые значения, полученные на разных серверах, после
слияния вызовут возникновение конфликта. Традиционно, проблема
identity в репликации решается путём использования для этого
опции NOT FOR REPLICATION и разделения его значений на не
пересекающиеся диапазоны для разных база данных. Ещё одним,
кардинальным путём решения этой проблемы является замена
identity на uniqueidentifier. Существует и стандартное
решение, позволяющее доверить разделение диапазонов MS SQL
Server, выступающему в роли издателя. Информация об
автоматически выделяемых диапазонах хранится в таблице
MSrepl_identity_range базы данных distribution.
Для того,
что бы издатель автоматически назначал диапазоны identity для
таблиц подписчиков и для собственных таблиц, необходимо
выполнить при создании публикации несколько очень простых
действий:
1. Включить опцию автоматического разделения диапазонов при
создании публикации. Это можно сделать в Create Publication
Wizard или используя хранимую процедуру
sp_addmergearticle.
2. Назначение начальных диапазонов
значений для издателя и подписчиков.
3. Назначение
порогового значения, которое определяет, когда должен быть
назначен новый диапазон. Порог необходим, что бы обеспечить
резерв значений для вставки, пока не будет назначен новый
диапазон. Установка слишком маленького порогового значения
может привести к большому количеству неиспользованных значений
identity. Пороговое значение должно подбираться в зависимости
от частоты вставок на подписчиках и от частоты расписания
синхронизации.
Нужно учитывать, что назначение нового диапазона для
каждого подписчика происходит в рамках сеанса репликации. Т.е.
если агент репликации слиянием не работает в непрерывном
режиме, новый диапазон будет установлен только при успешном
запуске агента. Пороговое значение должно учитывать то, что
если агент запускается редко, значений выделенного диапазона
должно быть достаточно для вставки максимально возможного
количества записей, которое вы прогнозируете в интервале
запуска агента репликации. Введение порогового значения
позволяет иметь резерв на тот случай, когда значения
выделенного диапазона закончатся между сеансами репликации. Он
нужен что бы операции вставки, т.е. выделение следующего
значения identity в рамках установленного диапазона, не
заканчивались ошибкой из-за исчерпания диапазона, который
может быть назначен только при следующем сеансе
репликации.
Проще всего рассмотреть, как это всё работает
на живом примере. Для этого, на тестовом сервере создайте три
базы данных, содержащие по две одинаковые таблицы. Ниже
представлен скрипт, исполнение которого в Query Analyzer
позволит выполнить эти предварительные шаги:
/***** Начало скрипта *****/ use master GO -- База для издателя CREATE DATABASE [identitytest] GO -- База для первого подписчика CREATE DATABASE [subscriber1] GO -- База для второго подписчика CREATE DATABASE [subscriber2] GO -- Создание таблиц SET QUOTED_IDENTIFIER ON GO SET ANSI_NULLS ON GO use [identitytest] GO -- Таблица для издания CREATE TABLE [dbo].[1testtable] ( [idrows] [int] IDENTITY (1, 1) NOT FOR REPLICATION NOT NULL , [dbname] [char] (20) NULL , [rowguid] uniqueidentifier ROWGUIDCOL NOT NULL ) ON [PRIMARY] GO ALTER TABLE [dbo].[1testtable] WITH NOCHECK ADD CONSTRAINT [PK_1testtable] PRIMARY KEY CLUSTERED ([rowguid]) ON [PRIMARY] GO ALTER TABLE [dbo].[1testtable] ADD CONSTRAINT [DF_1testtable_rowguid] DEFAULT (newid()) FOR [rowguid] GO -- Таблица аудита значений IDENTITY CREATE TABLE [dbo].[2audittable] ( [idrows] [int] IDENTITY (1, 1) NOT FOR REPLICATION NOT NULL , [dbname] [char] (20) NULL , [rowguid] [uniqueidentifier] NOT NULL ) ON [PRIMARY] GO ALTER TABLE [dbo].[2audittable] ADD CONSTRAINT [PK_2audittable] PRIMARY KEY NONCLUSTERED ([rowguid]) ON [PRIMARY] GO -- Триггер, заполняющий таблицу аудита CREATE TRIGGER [NEWROW] ON dbo.[1testtable] FOR INSERT NOT FOR REPLICATION AS INSERT INTO [2audittable] (rowguid, dbname) SELECT rowguid, dbname FROM inserted GO -- Таблицы для подписчиков полностью повторяют таблицы на издателе use [subscriber1] GO CREATE TABLE [dbo].[1testtable] ( [idrows] [int] IDENTITY (1, 1) NOT FOR REPLICATION NOT NULL , [dbname] [char] (20) NULL , [rowguid] uniqueidentifier ROWGUIDCOL NOT NULL ) ON [PRIMARY] GO ALTER TABLE [dbo].[1testtable] WITH NOCHECK ADD CONSTRAINT [PK_1testtable] PRIMARY KEY CLUSTERED ([rowguid]) ON [PRIMARY] GO ALTER TABLE [dbo].[1testtable] ADD CONSTRAINT [DF_1testtable_rowguid] DEFAULT (newid()) FOR [rowguid] GO CREATE TABLE [dbo].[2audittable] ( [idrows] [int] IDENTITY (1, 1) NOT FOR REPLICATION NOT NULL , [dbname] [char] (20) NULL , [rowguid] [uniqueidentifier] NOT NULL ) ON [PRIMARY] GO ALTER TABLE [dbo].[2audittable] ADD CONSTRAINT [PK_2audittable] PRIMARY KEY NONCLUSTERED ([rowguid]) ON [PRIMARY] GO CREATE TRIGGER [NEWROW] ON dbo.[1testtable] FOR INSERT NOT FOR REPLICATION AS INSERT INTO [2audittable] (rowguid, dbname) SELECT rowguid, dbname FROM inserted GO use [subscriber2] GO CREATE TABLE [dbo].[1testtable] ( [idrows] [int] IDENTITY (1, 1) NOT FOR REPLICATION NOT NULL , [dbname] [char] (20) NULL , [rowguid] uniqueidentifier ROWGUIDCOL NOT NULL ) ON [PRIMARY] GO ALTER TABLE [dbo].[1testtable] WITH NOCHECK ADD CONSTRAINT [PK_1testtable] PRIMARY KEY CLUSTERED ([rowguid]) ON [PRIMARY] GO ALTER TABLE [dbo].[1testtable] ADD CONSTRAINT [DF_1testtable_rowguid] DEFAULT (newid()) FOR [rowguid] GO CREATE TABLE [dbo].[2audittable] ( [idrows] [int] IDENTITY (1, 1) NOT FOR REPLICATION NOT NULL , [dbname] [char] (20) NULL , [rowguid] [uniqueidentifier] NOT NULL ) ON [PRIMARY] GO ALTER TABLE [dbo].[2audittable] ADD CONSTRAINT [PK_2audittable] PRIMARY KEY NONCLUSTERED ([rowguid]) ON [PRIMARY] GO CREATE TRIGGER [NEWROW] ON dbo.[1testtable] FOR INSERT NOT FOR REPLICATION AS INSERT INTO [2audittable] (rowguid, dbname) SELECT rowguid, dbname FROM inserted GO SET QUOTED_IDENTIFIER OFF GO /***** Конец скрипта *****/
Теперь приступим к организации репликации в рамках одного сервера между тремя базами данных. Для этого, в начале, нужно назначить издателя/дистрибутора. В представленных ниже примерах, использовался сервер с именем DBA. Вместо этого имени Вы будете указывать имя компьютера, на котором установлен Ваш тестовый сервер. На первом рисунке, средствами мастера "Configure Publishing, Subscribers, and Distribution" указываем сервер DBA, как дистрибутора.
После того, как сервер определён дистрибутором, в том же мастере указываем этот же сервер издателем.
После того, как мастер успешно закончит свою работу, в окне мастера должны появиться две новые закладки, Publication Databases и Subscribers. Если это произошло, необходимо разрешить издание базы данных identitytest, как это показано на следующем рисунке:
Следующим шагом, необходимо разрешить серверу DBA быть подписчиком для нашего сервера - издателя. Для этого, необходимо перейти на закладку Subscribers и пометить сервер DBA, как подписчика.
После успешного завершения указанных выше операций, приступим к созданию публикации. Для этого воспользуемся мастером Create Publication Wizard:
В рамках этой статьи будут представлены не все шаги
мастера. Те из шагов, где применимы предлагаемые мастером по
умолчанию значения или опции, представлены здесь не
будут.
Для публикации мы выбираем базу данных identitytest,
как это показано на следующем рисунке:
Указываем, что у нас будет репликация слиянием.
В качестве издаваемой статьи для нашей публикации указываем таблицу 1testtable и нажимаем на кнопку с многоточием, обведённую на нижеследующем рисунке красным овалом.
В раскрывшемся окне, переходим на закладку Identity Range и устанавливаем размеры диапазонов для издателя и подписчиков равными 10. В нашем случае, заведомо очень маленькое значение размеров диапазонов выбрано исключительно из соображений наглядности следующих ниже тестовых примеров. Порог переключения мы также изменим со стандартного 80% на 90%.
Для полноты понимания, давайте рассмотрим, что означают расположенные на этой закладке объекты:
Automatically assign and maintain a unique identity range
for each subscription - опция, которая заставляет SQL Server
назначать диапазоны identity издателем и для каждой подписки
выбранной статьи.
Maximum identity value - максимально -
возможное значение identity для этой таблицы.
Next range
starting value - следующее доступное начальное значение
identity.
Identity Increment - приращение, с которым будут
генерироваться значения identity.
Range size of Publisher -
размер диапазона identity, зарезервированный для операций
вставки на издателе.
Range size of Subscribers - размер
диапазона identity, зарезервированный для операций вставки на
подписчиках.
Assign a new range when this percentage of
values is used - когда вставляемые значения для этой статьи
достигают указанного тут процента от существующего диапазона
identity, SQL Server назначит новый диапазон identity.
Обратите внимание, что изменения значений, показанных на этой закладке, затрагивает все статьи, основанные на этой таблице. Все статьи для всех публикаций, которые основаны на этой таблице, будут использовать эти же свойства диапазона identity.
После завершения создания публикации, не забудьте включить логин, от имени которого стартует Ваш MSSQLServer и его агента или логин, через который будет работать репликация, в Publication Access List. Сделать это можно в свойствах публикации, как показано на следующем рисунке.
После того, как публикация настроена, можно приступить к
созданию подписок. Мы создадим две подписки, для разнообразия,
первая будет push, а вторая pull.
Для этого воспользуемся
соответствующими мастерами, также представленными в оснастках
Enterprise Manager. Поскольку тестовый сервер у нас один, он
же будет выступать и в роли подписчика.
В качестве базы данных для push подписки выбираем subscriber1.
Для простоты демонстрации работы примеров, включим агента слияния в непрерывный режим работы.
Поскольку у нас ещё пустые таблицы и они абсолютно идентичны, от первоначальной инициализации можно отказаться.
После успешного завершения мастером создания первой подписки, создаём вторую, pull подписку, воспользовавшись для этого Pull Subscription Wizard, в котором необходимо указать созданную нами ранее публикацию.
Базой данных назначения в этом случае будет subscriber2.
Как и при организации первой подписки, от первоначальной инициализации отказываемся.
Агента слияния запускаем в непрерывном режиме.
После успешного создания мастерами публикации и подписок,
можно приступать к моделированию автоматического назначения
диапазонов identity. Предварительно убедитесь, что задания для
агента слияния запущены и работают в непрерывном режиме без
ошибок.
Далее, заполним таблицу 1testtable, располагающуюся
в издаваемой базе данных identitytest, тестовыми данными.
Представленный ниже скрипт вставляет в указанную таблицу 20
записей. Запустите на исполнение в Query Analyzer этот скрипт,
подключившись к тестовому серверу.
/***** Начало скрипта *****/
use [identitytest]
GO
declare @count_insert int
SET @count_insert = 0
WHILE @count_insert < 20
BEGIN
SET @count_insert = (@count_insert + 1)
PRINT 'Вставляется ' + CONVERT(varchar(4), @count_insert) + '-я строка'
insert [1testtable] (dbname) VALUES ('identitytest')
END
GO
exec sp_adjustpublisheridentityrange identitytest
GO
/***** Конец скрипта *****/
Обратите внимание на последние сообщения, выдаваемые в окне Results Panel:
Вставляется 20-я строка Server: Msg 548, Level 16, State 2, Line 1 The identity range managed by replication is full and must be updated by a replication agent. The INSERT conflict occurred in database 'identitytest', table '1testtable', column 'idrows'. Sp_adjustpublisheridentityrange can be called to get a new identity range.
The statement has been terminated.
Во первых, настораживает то, что успешно было вставлено 19
записей, хотя размер диапазона для издателя был нами
установлен в 10 записей. Практика показывает, что при
заполнении впервые установленного диапазона identity для
издателя, реально допускается вставка удвоенного диапазона
издателя за минусом одной записи.
Сообщение об ошибке,
которое было получено при вставке 20-й строки, говорит о том,
что запись не может быть вставлена из-за превышения
установленного диапазона identity. В отличие от подписчиков,
для которых новые диапазоны устанавливаются в течении сеанса
репликации, для издателя при репликации слиянием нет
автоматического механизма установки нового диапазона после
достижения порогового значения. Администратор баз данных
должен следить за заполнением диапазона и успевать вовремя
запускать на исполнение хранимую процедуру
sp_adjustpublisheridentityrange, которая устанавливает новые
диапазоны для издателя. В конце предыдущего скрипта как раз и
вызывается эта процедура. Поэтому, при повторном его
исполнении вставка записей всё-таки произойдёт, хотя вставлено
уже будет не 19, а 9 записей, опять на одну запись меньше, чем
величина диапазона. Во всех дальнейших случаях установки новых
диапазонов для издателя, правило достижения порога будет
срабатывать именно таким образом.
Синтаксис процедуры следующий:
sp_adjustpublisheridentityrange [ @publication = ]
'publication'
[
@table_name = ]
'table_name'
[
@table_owner= ] 'table_owner'
Для других типов репликации (транзакционной и моментальными
снимками) возможно автоматическое выделение диапазонов и для
издателя, которое происходит при работе Log Reader Agent, и
для подписчиков, которое происходит при работе Distribution
Agent. Поскольку в репликации слиянием не используется Log
Reader Agent, а распределение выполняется Merge Agent,
автоматизация выделения нового диапазона для издателя
полностью ложится на плечи администратора баз
данных.
Теперь заполним аналогичной тестовой информацией
таблицу на первом подписчике. Здесь мы видим, что удачно
вставлены 9 записей и так будет во всех последующих вызовах
представленного ниже скрипта. Без ошибок будет вставляться на
одну запись меньше, чем величина диапазона.
/***** Начало скрипта *****/
use [subscriber1]
GO
declare @count_insert int
SET @count_insert = 0
WHILE @count_insert < 20
BEGIN
SET @count_insert = (@count_insert + 1)
PRINT 'Вставляется ' + CONVERT(varchar(4), @count_insert) + '-я строка'
insert [1testtable] (dbname) VALUES ('subscriber1')
END
GO
/***** Конец скрипта *****/
Для полноты картины, нам осталось заполнить тестовыми данными таблицу третьего подписчика. Выполните и этот скрипт.
/***** Начало скрипта *****/
use [subscriber2]
GO
declare @count_insert int
SET @count_insert = 0
WHILE @count_insert < 20
BEGIN
SET @count_insert = (@count_insert + 1)
PRINT 'Вставляется ' + CONVERT(varchar(4), @count_insert) + '-я строка'
insert [1testtable] (dbname) VALUES ('subscriber2')
END
GO
/***** Конец скрипта *****/
Как Вы видите из представленных в окне Results Panel
сообщений, заполнение таблицы на втором подписчике происходит
так же, как и у первого, т.е. вставляется на одну запись
меньше, чем величина диапазона. Можно было бы ожидать, что
диапазон будет изменён издателем при достижении порогового
значения, но это не происходит потому, что даже при запуске
агента слияния (Merge Agent) в непрерывном режиме, он
подключается к издателю дискретно. А поскольку мы вставляем
записи очень быстро, агент не успевает договориться с
издателем о выделении нового диапазона. Наш эксперимент можно
повторить, назначив для агента репликации расписание запуска
или переведя его в режим ручного запуска (отсутствие
расписания для задания, запускающего агента слияния). Если мы
будем вставлять 9 записей и после этого запускать сеанс
репликации агента слияния, диапазон будет изменён.
При
разработке схемы репликации с автоматическим выделением
диапазонов identity необходимо учитывать возможность
заполнения всего выделенного диапазона и рассчитывать
расписание запуска агента репликации таким образом, что бы
исключить такую возможность.
Теперь давайте сравним
значения identity, которые были получены при автоматическом
выделении диапазонов с тем, как они могли бы выделяться без
этого механизма. Для этого нам понадобится пересечение таблиц
1testtable и 2audittable объединенное по всем базам данных. В
наших примерах мы не создавали явных связей между указанными
таблицами, эта связь по полю rowguid поддерживалась логикой
работы триггера, который заполнял таблицу аудита. Ниже
представлен скрипт, который наглядно позволяет получить
необходимое нам пересечение и объединение:
/***** Начало скрипта
*****/
SELECT [identitytest].dbo.[1testtable].idrows
AS
[Диапазоны
репликации],
[identitytest].dbo.[2audittable].idrows
AS
[Порядковый
номер],
[identitytest].dbo.[1testtable].dbname
AS
[База
данных]
FROM [identitytest].dbo.[1testtable]
INNER
JOIN
[identitytest].dbo.[2audittable]
ON
[identitytest].dbo.[1testtable].rowguid
=
[identitytest].dbo.[2audittable].rowguid
UNION
SELECT [subscriber1].dbo.[1testtable].idrows
AS
[Диапазоны
репликации],
[subscriber1].dbo.[2audittable].idrows
AS
[Порядковый
номер],
[subscriber1].dbo.[1testtable].dbname
AS
[База
данных]
FROM [subscriber1].dbo.[1testtable]
INNER
JOIN
[subscriber1].dbo.[2audittable]
ON
[subscriber1].dbo.[1testtable].rowguid
=
[subscriber1].dbo.[2audittable].rowguid
UNION
SELECT [subscriber2].dbo.[1testtable].idrows
AS
[Диапазоны
репликации],
[subscriber2].dbo.[2audittable].idrows
AS
[Порядковый
номер],
[subscriber2].dbo.[1testtable].dbname
AS
[База
данных]
FROM [subscriber2].dbo.[1testtable]
INNER
JOIN
[subscriber2].dbo.[2audittable]
ON
[subscriber2].dbo.[1testtable].rowguid
= [subscriber2].dbo.[2audittable].rowguid
/***** Конец
скрипта *****/
В результате исполнения скрипта мы получим таблицу, сгруппированную по базам данных и отсортированную по identity. В первой колонке будут значения identity, полученные при автоматическом выделении диапазонов издателем. Во второй колонке будут значения identity из таблицы аудита, которые устанавливались без автоматического выделения диапазонов, как это происходит обычным образом, без репликации. В третьей колонке располагаются имена баз данных.
| Диапазоны репликации |
Порядковый номер |
База данных |
| 1 | 1 | identitytest |
| 2 | 2 | identitytest |
| 3 | 3 | identitytest |
| 4 | 4 | identitytest |
| 5 | 5 | identitytest |
| 6 | 6 | identitytest |
| 7 | 7 | identitytest |
| 8 | 8 | identitytest |
| 9 | 9 | identitytest |
| 10 | 10 | identitytest |
| 11 | 11 | identitytest |
| 12 | 12 | identitytest |
| 13 | 13 | identitytest |
| 14 | 14 | identitytest |
| 15 | 15 | identitytest |
| 16 | 16 | identitytest |
| 17 | 17 | identitytest |
| 18 | 18 | identitytest |
| 19 | 19 | identitytest |
| 21 | 1 | subscriber1 |
| 22 | 2 | subscriber1 |
| 23 | 3 | subscriber1 |
| 24 | 4 | subscriber1 |
| 25 | 5 | subscriber1 |
| 26 | 6 | subscriber1 |
| 27 | 7 | subscriber1 |
| 28 | 8 | subscriber1 |
| 29 | 9 | subscriber1 |
| 31 | 1 | subscriber2 |
| 32 | 2 | subscriber2 |
| 33 | 3 | subscriber2 |
| 34 | 4 | subscriber2 |
| 35 | 5 | subscriber2 |
| 36 | 6 | subscriber2 |
| 37 | 7 | subscriber2 |
| 38 | 8 | subscriber2 |
| 39 | 9 | subscriber2 |
Выводы
При использовании в схеме репликации слиянием механизма автоматического выделения диапазонов для полей identity необходимо учитывать следующие важные моменты:
1. Администратор баз данных должен отслеживать заполнение
выделенного для издателя диапазона и обеспечить его
своевременное переключение вызовом процедуры
sp_adjustpublisheridentityrange.
2. Расписание сеансов
репликации должно составляться так, что бы не допустить
исчерпания выделенного подписчику диапазона в перерывах между
этими сеансами.
3. Администратор баз данных должен
разработать план мероприятий по обеспечению работоспособности
базы данных подписчиков, в случае длительной потери или
перерывов связи между подписчиками и издателем, когда
выделенный издателем диапазон значений identity для подписчика
может быть исчерпан.