Transactional Replication

External Since: Nov 11, 2009 Posts: 1

I had 3 servers 2 transactional servers and one is main server
i.e., Multiple Publishers and Single Subscriber

A(TRANS SERVER)
B(TRANS SERVER)
C(MAIN SERVER)

I HAD CONFIGURED TRANSACTION REPLICATION

A TO C AND B TO C

In these servers i had taken one table(emp) for testing purpose
table:emp
Create table emp(empid int primary key identity(1,1),ename varchar(30),sal
decimal(18,2))

I had created emp table all the above 3 servers

Can you suggest me

HOW TO HANDLE IDENTITY VALUES WHEN TRANSACTION REPLICATION APPLIES

External Since: Jan 16, 2008 Posts: 143

You have to do something like this.

Set Server A to have odd values, server B to have event values and
have an increment of 2.

Set up your article so that it does not drop the table is if exists
but rather to delete the data which matches the row filter.

Then set up an odd number row filter on Server A and even on Server B.

here is a sample.

use master
go
if exists (select name from sys.databases where name='ServerA')
begin
alter database ServerA set single_user with rollback immediate
drop database ServerA
create database ServerA
end
GO
if exists (select name from sys.databases where name='ServerB')
begin
alter database ServerB set single_user with rollback immediate
drop database ServerB
create database ServerB
end
GO
if exists (select name from sys.databases where name='ServerC')
begin
alter database ServerC set single_user with rollback immediate
drop database ServerC
create database ServerC
end
GO
use ServerA
GO
Create Table emp(empid int primary key identity(1,2) not for
replication ,ename varchar(30),sal decimal(18,2))
GO
declare @counter int
set @counter=1
while @counter<=1000
begin
insert into emp(ename, sal) values('test'+convert(char(4),
@counter),RAND()*100)
select @counter=@counter+1
end
use ServerB
GO
Create Table emp(empid int primary key identity(2,2) not for
replication ,ename varchar(30),sal decimal(18,2))
GO
declare @counter int
set @counter=1
while @counter<=1000
begin
insert into emp(ename, sal) values('test'+convert(char(4),
@counter),RAND()*100)
select @counter=@counter+1
end
Use ServerA
GO
sp_replicationdboption 'ServerA','Publish','True'
GO
sp_replicationdboption 'ServerB','Publish','True'
GO
sp_addpublication 'Test',@status='active'
GO
sp_addpublication_snapshot 'test'
GO
sp_addarticle 'Test', 'emp', @source_object = N'emp',
@pre_creation_cmd = N'delete', @schema_option = 0x000000000803509F,
@identityrangemanagementoption='manual',@filter_clause = N'empid %2=1'
GO
sp_addsubscription 'Test', 'ALL',@@ServerName, 'ServerC'
GO
sp_addpushsubscription_agent 'Test', @@ServerName, 'ServerC'
GO
exec distribution.dbo.sp_MSstartsnapshot_agent @@ServerName,
'ServerA','test'
GO
exec distribution.dbo.sp_MSstartdistribution_agent @@ServerName,
'ServerA','test',@@ServerName, 'ServerC'
GO
use ServerB
GO
sp_addpublication 'Test',@status='active'
GO
sp_addpublication_snapshot 'test'
GO
sp_addarticle 'Test', 'emp', @source_object = N'emp',
@pre_creation_cmd = N'delete', @schema_option = 0x000000000803509F,
@identityrangemanagementoption='manual',@filter_clause = N'empid %2=0'
GO
sp_addsubscription 'Test', 'ALL',@@ServerName, 'ServerC'
GO
sp_addpushsubscription_agent 'Test', @@ServerName, 'ServerC'
GO
exec distribution.dbo.sp_MSstartsnapshot_agent @@ServerName,
'ServerB','test'
GO
exec distribution.dbo.sp_MSstartdistribution_agent @@ServerName,
'ServerB','test',@@ServerName, 'ServerC'
GO
use ServerA
go
declare @counter int
set @counter=1
while @counter<=10
begin
insert into servera.dbo.emp(ename, sal) values('test'+convert(char(4),
@counter),RAND()*100)
insert into serverb.dbo.emp(ename, sal) values('test'+convert(char(4),
@counter),RAND()*100)
select @counter=@counter+1
end