I am trying to implement sql server database replication between 2 branch servers to a Head Office Server.

My application is a distributed one, the main application is hosted on head office which is controlling the masters and final approvals. The branch servers are located on two other countries, which are used to enter daily transactions.

Since internet bandwidth is too slow, I am planning to run the replication only on off hours (ie.. night 12 AM to Morning 8 AM). During business hours it is difficult to synchronize. All tables are designed such a way to validate and avoid duplication or other errors.

Also there are chances of internet outage for couple of days.. may be up to a week.

There are three type of tables,

  1. BI Directional - Needs to sync between both sides(HO to branch and branch to HO, Approvals)
  2. Sync from Branch to HO (Transactions)
  3. Sync from HO to Branches (Masters)

When I configure replication, I am confused between different types of replication such as snapshot, transactional and merge replication.

Can anybody suggest which one is the best method for my model

I am also facing some issues with Primary key and foreign keys lost after configuring replication.. Any idea why this is happened..?

  • What version and edition of SQL Server? – HandyD Feb 20 '19 at 5:15
  • SQL Server 2017 Standard Edition 64 Bit – George Thomas Feb 20 '19 at 6:00
  • If you have to sync between both sides, it seems that merge replication is your only way – Denis Rubashkin Feb 20 '19 at 7:01
  • I am using merge for few tables, but there are few transaction tables which are being entered from 2 branches and I need to replicate both records to HeadOffice DB.. ow to handle this...? – George Thomas Feb 21 '19 at 8:41

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.