Sign in
 
Home20122008 R2200820052000LibraryForums
SQL Server >
> 
> 
SQL transactional replication

Respondida SQL transactional replication

  • Thursday, November 15, 2012 1:12 AM
     
     
    Sign In to Vote
    0

    Guys,

    i am a bit confused here after a month of testing. 

    Scenario:

    Primary SQL server 2008 in location A, act as publisher configured for Transactional Replication

    Subscriber SQL server 2008 in location B, act as subscriber.

    Everything is working and replication is working, however when we test disaster recovery scenario, we took a snapshot of the DB on subscriber, restore it as a new DB and pointed our application to it, this is where things went south.

    Application has errors left and right on primary and foreign key constrains. 

    My questions is: is there a way to avoid this? or is my path to this scenario completely off base? My goal is to have a read only subscriber untill primary site dies, that's where we simply shift applications to the stand by location and things should work. (at least that works in mYsql world).

    please let me know your experiences and point me in the right direction.

    thanks!

     

All Replies

  • Thursday, November 15, 2012 11:23 AM
     
     
    Sign In to Vote
    0

    Primary & foreign constraints error : it seems to have not selected the option of replicating Primary & foreign key constraints. Enable the option & reinitialize the replication and perform the disaster scenario again.

    Second to have read only copy in subscriber, you can log shipping also with stand by mode.

    Regards,
    Rohit Garg
    (My Blog)
    This posting is provided with no warranties and confers no rights.
    Please remember to click Mark as Answer and Vote as Helpful on posts that help you. This can be beneficial to other community members reading the thread.

     
  • Thursday, November 15, 2012 3:31 PM
    Moderator
     
     Answered
    Sign In to Vote
    0

    When you replicate all tables the pk and fk relationships should not be replicated by default. If you are getting these messges, it is because you decided to replicated these relationships.

    The question is why is the app complaining. The only way it could be complaining is because the data is not consistent on both sides. Perhaps you ran with the continue on data consistency error profile or you did a  no sync subscription.

    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

     
  • Thursday, November 15, 2012 5:49 PM
     
     
    Sign In to Vote
    0

    do primary keys fields should be set to " NOT FOR REPLICATION  " in this scenario ?


     
  • Thursday, November 15, 2012 5:51 PM
    Moderator
     
     
    Sign In to Vote
    0
    Yes!

    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

     
  • Thursday, November 15, 2012 6:52 PM
     
     
    Sign In to Vote
    0

    thanks Cotter!

    let me re-do this whole thing and see what happens

     
  • Friday, November 16, 2012 12:37 PM
     
     
    Sign In to Vote
    0

    Why are you taking the snapshot of the subscriber .Better you take the snapshot of the publisher .The reason why you are getting the ref int errors might be when you stop replication the data between Pub and sub might not be in sync ...

    I would suggest you to use Mirroring with replication .This is what we do in our environment 

    Server A : Principal and publisher 

    Server B : Subscriber 

    Server C Distributor 

    Server D : Principal and Mirror 

    If A goes down D comes up ....

    In case your subscriber goes down , then backup publisher >> restore on subscriber without replication >> Drop subscription >> recreate subscription without initializing it ..

    Reference : http://msdn.microsoft.com/en-us/library/ms151799.aspx

    Regards

    Abhay Chaudhary OCP 9i, MCTS/MCITP (SQL Server 2005, 2008, 2005 BI) ms-abhay.blogspot.com/


    • Edited by Abhay_78 Friday, November 16, 2012 12:37 PM
    •