none
Database Reapplication between headquarter and 1 site

    Question

  • Dear,

    Can  some one advise me and give best recommendation.

    Currently at our headquarter we are having a main database and all branches are connected to it.

    Now we are going to open new site and we wanted this branch to have its own database and the same data should be reapplication between HQ .

    What ever there is a new update or insert in new site it should replicate to HQ and if there is insert or update in HQ the data should replicate to New site Database.

    In this Scenario what Setup I should build.

    The current setup is Sql server 2008 r2 Mirroring in HQ .

    Which is the best practices and also suggest HA and fail over scenario. 






    • Moved by Kalman Toth Monday, April 29, 2013 1:08 PM Not db design
    Monday, April 29, 2013 9:09 AM

Answers

  • Now we are going to open new site and we wanted this branch to have its own database and the same data should be reapplication between HQ .

    What ever there is a new update or insert in new site it should replicate to HQ and if there is insert or update in HQ the data should replicate to New site Database.

    In this Scenario what Setup I should build.

    Hi Zameer,

    I think merge replication is OK.

    Merge replication typically starts with a snapshot of the publication database objects and data. Subsequent data changes and schema modifications made at the Publisher and Subscribers are tracked with triggers. The Subscriber synchronizes with the Publisher when connected to the network and exchanges all rows that have changed between the Publisher and Subscriber since the last time synchronization occurred.

    If you use SQL Server Mirror, data flows from principal database to mirror database, not reciprocate.

    Merge replication: http://msdn.microsoft.com/en-us/library/ms152746.aspx.

    Thanks.


    Maggie Luo
    TechNet Community Support

    Tuesday, April 30, 2013 8:35 AM
    Moderator

All replies

  • Your case merge replication will work..

    Refer this website.

    http://www.codeproject.com/Articles/28951/SQL-Server-2005-Merge-Replication-Step-by-Step-Pro


    Regards http:\\sqldbatask.blogspot.com MCTS Please mark answered if I've answered your question and vote for it as helpful to help other user's find a solution quicker

    Monday, April 29, 2013 12:37 PM
  • Now we are going to open new site and we wanted this branch to have its own database and the same data should be reapplication between HQ .

    What ever there is a new update or insert in new site it should replicate to HQ and if there is insert or update in HQ the data should replicate to New site Database.

    In this Scenario what Setup I should build.

    Hi Zameer,

    I think merge replication is OK.

    Merge replication typically starts with a snapshot of the publication database objects and data. Subsequent data changes and schema modifications made at the Publisher and Subscribers are tracked with triggers. The Subscriber synchronizes with the Publisher when connected to the network and exchanges all rows that have changed between the Publisher and Subscriber since the last time synchronization occurred.

    If you use SQL Server Mirror, data flows from principal database to mirror database, not reciprocate.

    Merge replication: http://msdn.microsoft.com/en-us/library/ms152746.aspx.

    Thanks.


    Maggie Luo
    TechNet Community Support

    Tuesday, April 30, 2013 8:35 AM
    Moderator
  • Hi Maggie,

    Thanks for your reply.

    ok I agree with Merge replication.

    In this scenario i need to have 1 distribution database, 1 publisher and 1 subscribe.

    Can you explain me in some deeper.

    Tuesday, April 30, 2013 9:19 AM
  • Hi Zameer,

    You need a distributer, a publisher and a subscriber. Merge replication is designed to enable changes to be made at both the publisher and subscriber by default. The merge engine then exchanges all changes between the publisher and subscriber during each cycle of the agent.

    Refer to setup merge replication: http://technet.microsoft.com/en-us/library/aa337473(v=sql.105).aspx.

    Thanks.


    Maggie Luo
    TechNet Community Support

    Friday, May 03, 2013 9:42 AM
    Moderator
  • I concur that merge is probably the best choice. You should use a local distributor on the publisher as there is not much contention with the publisher and distributor with merge replication.

    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

    Friday, May 03, 2013 11:31 AM
    Moderator