locked
Merge replication RRS feed

  • Question

  • I am trying to do merge replication.In my case

    publisher-sql server 2008 standard

    subscriber-sql server 2008 express

    I want to replicate data between these two.I am able to setup merge replication. But in subscriber the database was there with lot of content. in publisher the same db and table structure was there with no contents.When i did merge replication i found that all table contents became blank in subscriber where i had lot of data.I was expecting that the data will replicate from subscriber also.But the blank rows are replicated to subscriber and all my initial data get deleted in subscriber.(in merge replication two side sync should happen as i see in microsoft site).but i had problem of replicating blank rows.How i can avoid it? I have all database in pc with sql server express which i dont want to shift to sql server standard. How i can manage merge replication successfully in my scenario?

    Thank u..

    Thursday, September 13, 2012 5:44 AM

Answers

All replies

  • By default Merge Replication starts with a snapshot of the publication database objects and data and applies it to subscribers.  Then regular bi-directional synchronization occurs after initialization.

    I believe what you should do is backup the express database and restore it to the standard instance, then create the publication and subscription.


    Brandon Williams (blog | linkedin)

    • Proposed as answer by Shulei Chen Wednesday, September 19, 2012 9:07 AM
    • Marked as answer by Shulei Chen Thursday, September 20, 2012 10:22 AM
    Thursday, September 13, 2012 7:42 AM
  • Thank u for your reply. But is there any other method in SQL server to prevent snapshot of the publication database objects and instead start bi-directional synchronization directly.

    Because taking backup has to be done manually which is little irritating. So any other method to avoid it?

    Thursday, September 13, 2012 7:57 AM
  • Try a no-sync subscription, and then use sp_addtabletocontents on the subscriber to push the subscriber data to the publisher.

    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

    • Proposed as answer by Shulei Chen Wednesday, September 19, 2012 9:07 AM
    • Marked as answer by Shulei Chen Thursday, September 20, 2012 10:22 AM
    Thursday, September 13, 2012 1:06 PM
    Answerer
  • Thank u Hillary Cotter..

    How i can do no-sync subscription in merge replication?

    Thursday, September 13, 2012 2:17 PM
  • This is a good question. When you create your subscription deselect the option to intialize, or use the sync_type option of none in sp_addmergesubscription.This is not recommended and you might end up with an error depending on the complexity of your publication.


    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

    • Proposed as answer by Shulei Chen Wednesday, September 19, 2012 9:07 AM
    • Marked as answer by Shulei Chen Thursday, September 20, 2012 10:22 AM
    Thursday, September 13, 2012 2:45 PM
    Answerer
  • I would rather suggest go with Brandon , doing something unknown might push you to unknown issues.

    Restore subscriber DB at publisher and setup merge replication.


    Regards JakDBA Please Mark this as Answered if this solves your issue or satisfied with the response.

    Sunday, September 16, 2012 3:27 AM
  • If i take backup from subscriber  and restore in the publisher then when i setup merge replication the new rows will be keep on adding in subscriber(the db keeps on updating). So if i do merge if i do restore and then replication there is a chance of loosing some rows in subscriber.How to avoid it?
    Saturday, November 3, 2012 4:54 AM
  • Duplicate rows will be logged as conflicts. If they are primary key conflicts you will need to remove them from the publisher, let the deletes replicate through the topology and then enter them back into the publisher again.

    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

    Monday, November 5, 2012 2:19 PM
    Answerer