SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Administration
 Replication
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

satya.katari89
Starting Member

India
9 Posts

Posted - 05/31/2013 :  01:59:05  Show Profile  Reply with Quote
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

USA
5071 Posts

Posted - 05/31/2013 :  11:30:56  Show Profile  Visit russell's Homepage  Reply with Quote
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

India
9 Posts

Posted - 06/04/2013 :  01:53:25  Show Profile  Reply with Quote
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

USA
5071 Posts

Posted - 06/06/2013 :  10:31:28  Show Profile  Visit russell's Homepage  Reply with Quote
Might want to look at peer to peer replication.
Go to Top of Page

Hommer
Aged Yak Warrior

766 Posts

Posted - 06/10/2013 :  10:12:25  Show Profile  Reply with Quote
And, merge replication too.
Go to Top of Page

helpimdying
Starting Member

Brazil
3 Posts

Posted - 06/27/2013 :  12:20:27  Show Profile  Reply with Quote
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
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.09 seconds. Powered By: Snitz Forums 2000