Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Links Database Forum

» Database Journal Home
» Database Articles
» Database Tutorials
MS SQL
Oracle
DB2
MS Access
MySQL
» RESOURCES
Database Tools
SQL Scripts & Samples
Links
» Database Forum
» Sitemap
Free Newsletters:
DatabaseDaily  
News Via RSS Feed


follow us on Twitter
Database Journal |DBA Support |SQLCourse |SQLCourse2
 

Featured Database Articles

MS SQL

Posted Feb 16, 2005

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

By Muthusamy Anantha Kumar aka The MAK



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]


Fig 1.7


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


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


Fig 2.0

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



MS SQL Archives

Comment and Contribute

 


(Maximum characters: 1200). You have characters left.

 

 




Latest Forum Threads
MS SQL Forum
Topic By Replies Updated
SQL 2005: SSIS: Error using SQL Server credentials poverty 3 August 17th, 07:43 AM
Need help changing table contents nkawtg 1 August 17th, 03:02 AM
SQL Server Memory confifuration bhosalenarayan 2 August 14th, 05:33 AM
SQL Server Primary Key and a Unique Key katty.jonh 2 July 25th, 10:36 AM