Transactional Replication

[SATISH]

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

[Hilary Cotter]

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

[Hilary Cotter]

here is an example. Basically you have to seed your identity values with odd
or even values. Note that for servera I am using off values, and for serverb
I am using even.

I then set up my publication so that I will delete all data which matches
the row filter. I am filtering on odd values on server a and even values on
server b.

This way I don't need to do any schema chanages.

"SATISH" <SAT...@discussions.microsoft.com> wrote in message
news:D051F1E4-1B8E-4961...@microsoft.com...

[Paul Ibison]

I would probably change this to use a composite primary key. One part of the
composite key will refer to the originating publisher. That way you don't
need to bother about conflicting identity ranges, especially if you want to
add more subscribers at some later stage.
HTH,
Paul Ibison