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

» Database Journal Home
» Database Articles
» Database Tutorials
MS Access
SQL Scripts & Samples
» Database Forum
» Slideshows
Free Newsletters:

News Via RSS Feed

Rss Feed

Database Journal |DBA Support |SQLCourse |SQLCourse2

Featured Database Articles


Posted Feb 16, 2005

Replicating Identity columns in SQL Server - Customizing replication

By Muthusamy Anantha Kumar aka The MAK

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
use DB1
Create table Dept (id int identity(1,1) 
  constraint Dept_PK Primary Key Clustered,
Name Varchar(50))
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))

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:

	[id] [int] NOT NULL ,
	[Name] [varchar] (50)NULL 

	[Id] [int] NOT NULL ,
	[Dept_id] [int] NULL ,
	[Empname] [varchar] (50) NULL ,
	[Zipcode] [int] NULL ,
	[Country] [varchar] (50) NULL 

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

MS SQL Archives

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