Transactional Replication

Question

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

Answer 1

 

I had 3 servers 2 transactional servers and one is main server

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

I HAD CONFIGURED TRANSACTION REPLICATION

A TO C AND B TO C

HOW TO HANDLE IDENTITY VALUES WHEN REPLICATION APPLIES

Answer 2

make a composite key(combination of key).

we do like this.

we have 30+ server replicating to 1 central server "Main" ..

for you I would go something like this:

"Server A" column1+column2 replicates to "Server C"
"Server B" column1+column2 replicates to "Server C"

where column1 represents the server name or something which belongs to server name.
column2 can be identity key on Server A and B.

on Server C dont make Column2 as identity column.


Please let me know if you require more info.

HTH.
Vinay

twitter @ThakurVinay

http://www.sqlservercentral.com/blogs/vinaythakur

---

Vinay Thakur http://vinay-thakur.spaces.live.com/ http://twitter.com/ThakurVinay

Answer 3

Make the identity property on A and B as Not for replication.

Then make the seed on A 1 (or an odd number), and an increment of 2.

Make the seed on B, 2 (or an even number) and an incrmenet of 2.

---

looking for a book on SQL Server replication? http://www.nwsu.com/0974973602.html looking for a book on SQL Server 2008 Administration? http://www.amazon.com/Microsoft-Server-2008-Management-Administration/dp/067233044X looking for a book on SQL Server 2008 Full-Text Search? http://www.amazon.com/Pro-Full-Text-Search-Server-2008/dp/1430215941

Answer 4

 duplicate post..

 http://social.msdn.microsoft.com/Forums/en-US/sqlreplication/thread/5a44573c-1dc8-4147-accf-f576367a330b

---

Lakshman

Answer 5

I second the tip given by Hilary, apart from this you should refer some of the articles http://searchsqlserver.techtarget.com/tip/0,289483,sid87_gci1266731_mem1,00.html
http://technet.microsoft.com/en-us/library/ms152543.aspx
http://technet.microsoft.com/en-us/library/ms146907.aspx

The most common problem is the jump in the value for identity columns, I would recommend to set off the identity property if feasible. Refer the article by Hilary on Identity crisis http://www.simple-talk.com/sql/database-administration/the-identity-crisis-in-replication/

Regards

---

Hemantgiri S. Goswami | http://www.sql-server-citation.com/ | http://www.surat-user-group.org/ -- Surat SQL Server User Group