Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 General SQL Server Forums
 New to SQL Server Administration
 Replication

Author  Topic 

satya.katari89
Starting Member

9 Posts

Posted - 2013-05-31 : 01:59:05
if we are doing the transnational replication over two locations, if both the locations are parallely working and our leased line got disconnected if people are still connecting the and working on their local databaes. after some time if network connection restores.

what will happen to my primary table columns ?

Primary key column if location A : 89 and B : 89

after restoring there will be conflict ?


please kindly help me.


let me know if it is not clear.


-Satya

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2013-05-31 : 11:30:56
First of all, in transactional replication, the subscriber(s) should be treated as read only.

If you allow writes to the subscribers then you are going to have primary key conflicts.

If you use the subscriber as a DR site, then to bring the publisher back online you'll have to:
- stop replication
- manually insert new data from the subscriber to the publisher, with identity_insert on.
- inserts will need to be in proper order if there are foreign keys, or you'll need to disable constraints before inserting the data
- manually re-seed the identity columns at the publisher
- re-enable publishing
- run the snapshot agent
Go to Top of Page

satya.katari89
Starting Member

9 Posts

Posted - 2013-06-04 : 01:53:25
thanks for your reply can you please suggest me if any other option is available.


-Satya
Go to Top of Page

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2013-06-06 : 10:31:28
Might want to look at peer to peer replication.
Go to Top of Page

Hommer
Aged Yak Warrior

808 Posts

Posted - 2013-06-10 : 10:12:25
And, merge replication too.
Go to Top of Page

helpimdying
Starting Member

3 Posts

Posted - 2013-06-27 : 12:20:27
You should set the identity columns as "NOT FOR REPLICATION", I think. That way when these columns got replicated from the publisher to subscribers and vice-versa, the identity column would be set to the next value regardless of the value it had in the original table. The "NOT FOR REPLICATION" column property was created specifically for this situation, if im not mistaken.

Take a look at this article for more information: http://msdn.microsoft.com/en-us/library/aa237102%28v=sql.80%29.aspx
Go to Top of Page
   

- Advertisement -