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

Answered SQL Server 2005 transactional replication

  • Thursday, January 03, 2013 7:07 AM
     
     
    Sign In to Vote
    0

    Dears,

    We are replicating SQL server 2005 data between one site and the main DB. whenever the replication stops or got courrpted usually we have to reinitialize the publishers from the site which means that we have to delete all the previous data from the main DB prior to uploading the site data  backdated. this works fine although it is not practical since it consumes a lot of time and it affects the systems performance (data size might reach 100 GB). my question is :

    1. Is it possible to start uploading the site data to the main DB from the point where it stopped without uploading all the data backdate ?

    2. How can we avoid the violation of primary key conflict which is caused from being sending the same records from the DB site to the main DB without deleting the main DB previously replicated records?

    regards,

    Raed Al zubaidi

    best regrads, Raed

     

All Replies

  • Thursday, January 03, 2013 12:56 PM
    Moderator
     
     Answered
    Sign In to Vote
    0

    Are you talking about merge replication or transactional? With merge there is the reinit with upload option. This will upload all the changed data on the subscriber before reinitializing it. But then you still need to send down your snapshot - which sounds painful as you are replicating a 100 G database.

    In this case you are best to backup the publisher and do a no-sync subscription. This article may also help;

    http://www.informit.com/articles/article.aspx?p=1392520

    If you are using transactional replication - you should look at the reinitialize from a backup option.

    You might be able to use the continue on data consistency error profile and then run a validation to determine how out of sync you are, and then drop individual tables out of your publication and add them back in.

    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