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

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



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


















Thanks for your registration, follow us on our social networks to keep up-to-date