6

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?

|improve this question
3

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.

|improve this answer
  • Hmm... I'm fairly sure there's no data which is generated on both sides, but I'll need to audit it to be sure. – Bobson Mar 19 '13 at 5:55
  • This question just popped up on my feed, and I noticed I never excepted an answer. We didn't end up setting anything like this up before I left that job, but based on what I've learned since, I think this would have been the right way to go. – Bobson Mar 17 '16 at 13:52
1

I'm not an expert in replication but I believe you want Merge replication at least in part. Transactional replication will not support your 3.1 requirement. Ie syncing the data back together. Only Merge replication is designed for that. I'm not 100% certain but I believe you can set up transactional replication for the data you only want to be read-only in the remote location and merge replication for the data you want to be able to modify in both locations.

Regardless, if communication fails then the replication transactions will be queued until communication is restored. This can be a problem if communication is down for a long time since the transactions can take up quite a bit of space if there are a lot of them. But as long as the down times (or transaction volumes) are not to large then the databases will re-sync once the communication comes back.

|improve this answer
  • So is replication set up per-table? That would be the only way I could see it allowing both types on the same database. It doesn't quite match what I was reading about it, but then I was just reading the high-level overviews. As for downtime, it's rare, but it's pretty devastating to everyone's workflow when it does happen. – Bobson Mar 19 '13 at 5:10
  • Good point on what Kenneth said about the merge for data needing to be merged together. I read 3.1 requirement a bit differently - as separate data that doesn't need to merge changes but one side having Orders and the other having Order Status. If you do mean merge in that the same table could be updated on either end - and they could be disconnected and reconnect and then do that merge of data - then Merge may be a better solution for that type of data. You can mix and match also - again learn a lot more before going down that road :-) Good luck. – Mike Walsh Mar 19 '13 at 5:52
  • Replication is broken out by publications and each publication can be a different type of replication. Within each publication are the articles. The articles are objects you want to publish. In your case the different tables. So you could create a transactional publication for certain tables and a merge publication for others. However like @Mike Walsh said replication is complicated. Either do a lot of research and testing or hire a consultant before putting anything into production. – Kenneth Fisher Mar 19 '13 at 13:46
  • Research, research, more research, and experimentation on a pair of test databases will be the order of the day. – Bobson Mar 19 '13 at 17:18
0

I think you should go for:

Transactional publication with updatable subscriptions:

The Publisher streams transactions to SQL Server Subscribers after they receive an initial snapshot of the published data. Transactions originating at the Subscriber are applied at the Publisher.

|improve this answer

Your Answer

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

Not the answer you're looking for? Browse other questions tagged or ask your own question.