Replicating Identity columns in SQL Server - Customizing replication - Page 2

Customizing replication

Let's walk through the steps to establish the replication property and the constraints on the subscription database so that both the publishing database and the subscription database are identical, so that when there is a failure in the publisher, we can switch to the subscriber.

Step 1

Stop distribution as shown below. [Refer Fig 1.5 and 1.6]

Step 2

Enable identity property on the subscription database on both the Emp and Dept tables using Enterprise manager as shown below [Refer Fig 1.7 and 1.8]

Step 3

Drop the index and add the necessary constraints as shown below.

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]
	)

Step 4

Update the stored procedure created by the replication setup. When setting up replication, SQL server creates three stored procedures. One for insert, one for update and one for delete. Basically we are updating the insert and the update procedure with "Set identity_insert on" and "Set identity_insert off"

Insert procedure for the table "Dept" created by SQL Server

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

Update the procedure using the code below

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

Insert procedure for the table "Emp" created by 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

Update the procedure using the code below

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

Update procedure for the table "Dept" created by 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

Update the procedure using the code below

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

Update procedure for the table "Emp" created by SQL Server

create procedure "sp_MSupd_Emp" 
 @c1 int,@c2 int,@c3 varchar(50),@c4 int,@c5 varchar(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

Update the procedure using the code below

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

Step 5

Start Distributor to synchronize the publisher and the subscriber. [Refer Fig 1.9]

Do some deletes and updates to the publisher to make sure that it is replicated to the subscriber. [Refer Fig 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

Conclusion

By this customized replication, when the publisher fails, you can point all of the applications
accessing the publishing database to the subscriber's subscription database, since Publishers
publishing database and the subscriber's subscribing database look alike, including the identity
property. Since identity property is enabled in the subscription database and all the constraints are re-created in the subscription database, applications work seamless when pointing to subscription database.

» See All Articles by Columnist MAK