Replicating Identity columns in SQL Server - Customizing replication

Wednesday Feb 16th 2005 by Muthusamy Anantha Kumar aka The MAK
Share:

MAK discusses customizing replication to make the subscription and primary databases identical in order to failover in case of a primary server failure.

When transactional replication is used for high availability purposes, such as if you want the applications to access the replicated server when the primary database server fails, one of the hurdles SQL Server database administrators face when configuring replication is tables with identity columns.

In this article, I am going to discuss how to customize the replication in order to make the subscription database look identical to the publishing database, so that when there is a failure in the primary server, it is simple to fail over to the subscription database. p>

Let us assume that the server "SQL" is the publisher with "DB1" as publishing database and server "Claire" is the subscriber with "DB1" as subscription database. Let's assume that transactional replication is setup between the servers "SQL" to "Claire" server.

In our example, the publishing database has the following tables with identity columns and Primary key and foreign key constraints.

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

When setting up the replication, the following message [Refer Fig 1.0] is displayed. The message means that the identity property will be lost in the subscriber. In addition, the constraints are lost. Basically, the schema of the subscription database looks like:

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


Fig 1.0

Let's insert some rows in the publishing database as shown below. [Refer Fig 1.1]


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

These rows would be replicated to the subscription database as shown below. [Refer Fig 1.2]


Fig 1.2

Now let's query the tables in Publisher and Subscriber as shown below [Refer Fig 1.3 and 1.4] to make sure that all of the rows have been replicated.


Fig 1.3


Fig 1.4



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]

Click for larger image

Fig 1.5

Click for larger image

Fig 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

Share:
Home
Mobile Site | Full Site
Copyright 2017 © QuinStreet Inc. All Rights Reserved