We have a small branch app that stores some data in a SQL Express Database.

We want to have an almost real time copy of this table in our headquarters, were we have SQL Enterprise 2014. I say almost, because it doesn't matter if there is a 5 minute delay or something like that.

The main thing is that it's able to synchronise after a network disconnection between branch and headquarter.

Any solution that does not requiere licensing costs for the branch office?

  • See this and this - if the publisher is Express, though, you'll need some other way of replicating changes since there is no agent (maybe just use a trigger, depending on the frequency of the data, or external tools like Windows scheduled task, Powershell, etc). – Aaron Bertrand Jan 2 '18 at 14:58

You can use SQL Server Merge Replication for this. The SQL Enterprise instance would be the publisher, but Merge Replication can synchronize data both ways. So changes made at the subscriber would be replicated to the publisher.

If you configure this to be a Push Subscription the agents will all run at the central instance, and you can schedule them with SQL Agent.

  • David Browne, our setup is just the reverse of this. We have a MS SQL Standard on our site and would like this to replicate MS SQL (Express?) database on Ubuntu 18. Will this work similarly to Jack's scenario? – Dawoodjee Aug 15 '19 at 9:35

SQL 2014 Express does actually support transactional replication, but only as the subscriber, so that's not going to work here.

Does the table have a built-in last_modify_date field that is reliably updated by the application when it inserts/updates rows?

If so, I would just write a custom job (maybe an SSIS package in a scheduled job on the headquarters server) that checks for changed rows and updates/creates the corresponding rows in the headquarters copy.

If the vendor application doesn't do that, then you'd have to do one of two things:

  1. Add triggers to the table to update a new modifydate field, and use the above functionality
  2. Write a more complicated process that either copies the entire table each time, or compares the entire table to look for changes.

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.