Sql-server – What type of SQL Server Replication fits our scenario? Merge or Transactional

merge-replicationreplicationsql serversql-server-2005transactional-replication

Background

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:

  1. Connections can be slow, so we can't easily work with local copies of the code
  2. 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.

Question

Will Merge or Transactional replication serve us better?

Our requirements are:

  1. Update product data at HQ and push it to the website as read-only.
  2. Update order data on the website and push it to HQ
    1. Sync small changes from HQ back to website (order status)
  3. No disruption in availability on either end when communication is impossible
    1. 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.
  4. (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?

Best Answer

A few thoughts. It sounds to me like you may be able to get away with a couple options if replication is what you end up with. First a word of caution - Replication isn't something that should just be configured in production and enabled/used unless you have some experience with it. This becomes truer with more important workloads and busier systems. Replication can cause headaches if not properly done. I'd invest in mentoring/training/consulting to help you along the path from folks who have been burned by mistakes already.

That said. It looks to me like your requirements don't seem to have any 2-way changes that need to be synced up or merged. It looks like you have a bunch of things that need to go one directional. Orders never come in locally, only on the website. Order status never gets generated on the website, only consumed, etc.

If that is the case - this sounds like Transaction Replication to me. Merge is more for when you need to have multiple versions that can make changes and receive changes at the same time. The tired old Microsoft example still works - a bunch of sales folk all with a local app that does lead management and a big central server. They can get their leads when in the home office and get data, then they work disconnected and sync up their changes. With Merge you have to deal with conflicts (if the same data is modified in two places, who wins?) Transactional is more what is used when you have changes going in one direction (but you can have it go bidirectional here too even).

And you can set replication up to have various tables included or not included in the publication as articles.

A couple links to give you a bit more info:

Transactional Replication Overview

Merge Replication Overview

Now if you have data that can be updated on each and needs to be "merged" then merge may be a better answer. When I work with replication - I prefer to stay as close to Transactional as I can, though. It is easier to administer and there is no conflict resolution worries if you don't need it.

Depending on your volume - if it is low enough - and your latency - if some lag time is acceptable - you might also consider something more programatic or ETL like. Perhaps staging data and sending it over in batches. Sending messages across as data changes in one side, etc.

Related Question