Replicating Identity columns in SQL Server - Customizing replication

February 16, 2005

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

Let's insert some rows in the publishing database as shown below. [Refer 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]

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.