SQL.RU
 client/server technologies
 Главная | Документация | Статьи | Книги | Форум | Блоги | Опросы | Гостевая | Рассылка | Работа | Поиск | FAQ |

Репликация столбцов Identity в SQL Server - Настройка репликации

ПУБЛИКАЦИИ  

По материалам статьи Muthusamy Anantha Kumar: Replicating Identity columns in SQL Server - Customizing replication
Перевод Маргариты Баскаковой

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

create database DB1 go use DB1 go Create table Dept (id int identity(1,1) constraint Dept_PK Primary Key Clustered, Name Varchar(50)) go create table Emp (Id int identity(1,1) constraint Emp_PK Primary Key Clustered, Dept_id int constraint Dept_FK foreign key references dept(id), Empname varchar(50), Zipcode int, Country varchar(50)) Go

В процессе настройки репликации будет получено следующее сообщение [См. рис. 1.0]. Это сообщение означает, что свойство identity не будет сохранено на подписчике. Кроме того, ограничения целостности так же будут потеряны. По существу, схема базы данных на подписчике будет выглядеть так:

CREATE TABLE [Dept] ( [id] [int] NOT NULL , [Name] [varchar] (50)NULL ) ON [PRIMARY] GO CREATE TABLE [Emp] ( [Id] [int] NOT NULL , [Dept_id] [int] NULL , [Empname] [varchar] (50) NULL , [Zipcode] [int] NULL , [Country] [varchar] (50) NULL ) ON [PRIMARY] GO


Рис. 1.0

Давайте добавим некоторые строки в базу данных публикации как показано ниже. [См. Рис. 1.1]


Рис. 1.1

insert into Dept (Name) select 'Human Resource' insert into Dept (Name) select 'Marketing' insert into Dept (Name) select 'Finance' Insert into Emp (Dept_id,empname,zipcode,country) Select 1,'Sunny Leone',07054,'USA' Insert into Emp (Dept_id,empname,zipcode,country) Select 2,'Shu Qui',11223,'Taiwan' Insert into Emp (Dept_id,empname,zipcode,country) Select 2,'Sofie Marque',1234,'France' Insert into Emp (Dept_id,empname,zipcode,country) Select 1,'Zhang Ziyi',1234,'China' Эти строки будут реплицированы подписчику, как показано ниже. [См. Рис. 1.2]


Рис. 1.2

Чтобы удостовериться, что все строки реплицировались, давайте сделаем запрос к таблице на Издателе и Подписчике как показано ниже [См. Рис. 1.3 и 1.4].


Рис. 1.3


Рис. 1.4

[В начало]

Настройка репликации

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

[В начало]

Шаг 1

Остановите синхронизацию как показано ниже. [См. Рис. 1.5 и 1.6]


Рис. 1.5


Рис. 1.6

[В начало]

Шаг 2

Используя Enterprise Manager, установите свойство identity у таблиц "Emp" и "Dept" на подписанной базе данных как показано ниже [См. Рис. 1.7 и 1.8]


Рис. 1.7


Рис. 1.8

[В начало]

Шаг 3

Удалите индекс и добавьте необходимые ограничения как показано ниже.

Use DB1 go Drop index DEPT.Dept_PK go ALTER TABLE [Dept] ADD CONSTRAINT [Dept_PK] PRIMARY KEY CLUSTERED ( [id] ) go drop index Emp.Emp_Pk go ALTER TABLE EMP ADD CONSTRAINT [Emp_PK] PRIMARY KEY CLUSTERED ( [Id] ) ON [PRIMARY] , CONSTRAINT [Dept_FK] FOREIGN KEY ( [Dept_id] ) REFERENCES [Dept] ( [id] )

[В начало]

Шаг 4

Измените хранимую процедуру, созданную автоматически во время настройки репликации. При настраивании репликации, SQL SERVER создает три хранимых процедуры. Одну для вставки, одну для изменения и одну для удаления. В основном, мы изменяем значение параметров процедуры вставки и процедуры изменения с "Set identity_insert on" на " Set identity_insert off"
Процедура вставки для таблицы "Dept" созданная SQL Server:

create procedure "sp_MSins_Dept" @c1 int,@c2 varchar(50) AS BEGIN insert into "Dept"( "id", "Name" ) values ( @c1, @c2 ) END

Измените процедуру, используя код, приведенный ниже:

Alter procedure "sp_MSins_Dept" @c1 int,@c2 varchar(50) AS BEGIN set identity_insert Dept on insert into "Dept"( "id", "Name" ) values ( @c1, @c2 ) set identity_insert Dept off END

Процедура вставки для стаблицы "Emp" созданная SQL Server:

create procedure "sp_MSins_Emp" @c1 int,@c2 int,@c3 varchar(50),@c4 int,@c5 varchar(50) AS BEGIN insert into "Emp"( "Id", "Dept_id", "Empname", "Zipcode", "Country" ) values ( @c1, @c2, @c3, @c4, @c5 ) END

Измените процедуру, используя код, приведенный ниже:

Alter procedure "sp_MSins_Emp" @c1 int,@c2 int,@c3 varchar(50),@c4 int,@c5 varchar(50) AS BEGIN set identity_insert Emp on insert into "Emp"( "Id", "Dept_id", "Empname", "Zipcode", "Country" ) values ( @c1, @c2, @c3, @c4, @c5 ) set identity_insert Emp off END

Процедура изменения для таблицы "Dept" созданная SQL Server:

create procedure "sp_MSupd_Dept" @c1 int,@c2 varchar(50),@pkc1 int, @bitmap binary(1) as if substring(@bitmap,1,1) & 1 = 1 begin update "Dept" set "id" = case substring(@bitmap,1,1) & 1 when 1 then @c1 else "id" end ,"Name" = case substring(@bitmap,1,1) & 2 when 2 then @c2 else "Name" end where "id" = @pkc1 if @@rowcount = 0 if @@microsoftversionj>0x07320000 exec sp_MSreplraiserror 20598 end else begin update "Dept" set "Name" = case substring(@bitmap,1,1) & 2 when 2 then @c2 else "Name" end where "id" = @pkc1 if @@rowcount = 0 if @@microsoftversion>0x07320000 exec sp_MSreplraiserror 20598 end

Измените процедуру, используя код, приведенный ниже:

Alter procedure "sp_MSupd_Dept" @c1 int,@c2 varchar(50),@pkc1 int, @bitmap binary(1) as begin update "Dept" set "Name" = case substring(@bitmap,1,1) & 2 when 2 then @c2 else "Name" end where "id" = @pkc1 if @@rowcount = 0 if @@microsoftversion>0x07320000 exec sp_MSreplraiserror 20598 end

Процедура изменения для таблицы "Emp" созданная SQL Server:

create procedure "sp_MSupd_Emp" @c1 int,@c2 int,@c3 varchar(50),@c4 int,@c5 archar(50), @pkc1 int, @bitmap binary(1) as if substring(@bitmap,1,1) & 1 = 1 begin update "Emp" set "Id" = case substring(@bitmap,1,1) & 1 when 1 then @c1 else "Id" end ,"Dept_id" = case substring(@bitmap,1,1) & 2 when 2 then @c2 else "Dept_id" end ,"Empname" = case substring(@bitmap,1,1) & 4 when 4 then @c3 else "Empname" end ,"Zipcode" = case substring(@bitmap,1,1) & 8 when 8 then @c4 else "Zipcode" end ,"Country" = case substring(@bitmap,1,1) & 16 when 16 then @c5 else "Country" end where "Id" = @pkc1 if @@rowcount = 0 if @@microsoftversion>0x07320000 exec sp_MSreplraiserror 20598 end else begin update "Emp" set "Dept_id" = case substring(@bitmap,1,1) & 2 when 2 then @c2 else "Dept_id" end ,"Empname" = case substring(@bitmap,1,1) & 4 when 4 then @c3 else "Empname" end ,"Zipcode" = case substring(@bitmap,1,1) & 8 when 8 then @c4 else "Zipcode" end ,"Country" = case substring(@bitmap,1,1) & 16 when 16 then @c5 else "Country" end where "Id" = @pkc1 if @@rowcount = 0 if @@microsoftversion>0x07320000 exec sp_MSreplraiserror 20598 end

Измените процедуру, используя код, приведенный ниже:

Alter procedure "sp_MSupd_Emp" @c1 int,@c2 int,@c3 varchar(50),@c4 int,@c5 varchar(50), @pkc1 int, @bitmap binary(1) as begin update "Emp" set "Dept_id" = case substring(@bitmap,1,1) & 2 when 2 then @c2 else "Dept_id" end ,"Empname" = case substring(@bitmap,1,1) & 4 when 4 then @c3 else "Empname" end ,"Zipcode" = case substring(@bitmap,1,1) & 8 when 8 then @c4 else "Zipcode" end ,"Country" = case substring(@bitmap,1,1) & 16 when 16 then @c5 else "Country" end where "Id" = @pkc1 if @@rowcount = 0 if @@microsoftversion>0x07320000 exec sp_MSreplraiserror 20598 end

[В начало]

Шаг 5

Запустите Дистрибутора для синхронизации издателя и подписчика. [См. Рис. 1.9]


Рис. 1.9

Измените, а затем удалите на издателе какие-нибудь данные, чтобы удостовериться, что эти действия реплицируются подписчику. [См. Рис. 2.0]

update Emp set empname = 'Sophia Marque' where empname='Sofie Marque' update Emp set empname = 'Zhang Ziyi' where id=4 delete from emp where id =2


Рис. 2.0

[В начало]

Заключение

Если Вы настроите репликацию таким образом, то при отказе издателя, Вы можете переключить все приложения, обращающиеся к опубликованной базе данных, к базе данных подписчика, так как опубликованная база данных на издателе и подписная базы данных на подписчике полностью идентичны, включая свойство identity. Так как на подписной базе данных свойство identity доступно и все связи и ограничения созданы, то работа приложений будет непрерывна при обращении к базе данных подписчика.

[В начало]

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

Rambler's Top100 Рейтинг@Mail.ru  Administrator: Обратная связь 
Copyright: SQL.Ru 2000-2013