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,
- BI Directional - Needs to sync between both sides(HO to branch and branch to HO, Approvals)
- Sync from Branch to HO (Transactions)
- 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..?