In our current setup, we have a single SQL Server 2005 instance on our remotely hosted web server. We additionally have another (non-MSSQL) database which we use for our POS system, which automatically updates the web server when things (such as product information) changes. This has two problems:
- Connections can be slow, so we can't easily work with local copies of the code
- Sometimes we can't reach the web database (even though the site still works), which causes the POS system to hang.
The solution I'm working towards is setting up a second SQL Server instance (2005 or 2008) locally at the corporate headquarters, directing the POS system at it, and using some form of Replication to sync changes between them. However, I can't tell whether we should use merge replication or transactional.
Will Merge or Transactional replication serve us better?
Our requirements are:
- Update product data at HQ and push it to the website as read-only.
- Update order data on the website and push it to HQ
- Sync small changes from HQ back to website (order status)
- No disruption in availability on either end when communication is impossible
- i.e. The HQ can still access and update our copy, and the website can update its, and when communication is restored, changes will be synced.
- (Optional) An easy way to make a copy of the HQ database with the replication removed, for code testing purposes.
Which one is the better fit for our requirements?