Replicate single table from SQL Express to SQL Enterprise 2014

by Jack Casas   Last Updated January 02, 2018 15:06 PM

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?

Answers 1

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.
January 02, 2018 14:50 PM

Related Questions

SQL Server 2008 R2 Express size

Updated June 16, 2015 12:02 PM

Mirroring and Replication

Updated November 14, 2017 08:06 AM