So I've got a hosting package from hostgator with an MS SQL database and a local SQL Express 2012 database running. I'm trying to investigate syncing the two together, two-way syncing, and I'm unable to use the SQL replication tools in the SQL management studio, can I use the replication tools? - or do I need to use another tool?


Sorry if this is vague, please let me know and I can clarify.

  • Sorry, I've reposted here, it appears to be the right place but if not please let me know!
    – A bit new
    Jun 15, 2015 at 20:25
  • This is a duplicate of this question but I hesitate to vote to close as this has garnered better answers at this point... Jul 26, 2017 at 9:21
  • Quick check, are you unable to configure the publisher on SSMS (Standard edition host) or the Express subscriber host? Or both? Also by "unable to use" did it throw an error or is just disabled? Jul 26, 2017 at 10:00

4 Answers 4


SQL Server replication, creates jobs which runs through SQL Server Agent, but because SQL Server Agent doesn't run with SQL Server Express Edition, Replication also can not run and the feature is not added in the edition and you can not implement replication. So if you need to sync both databases you need to upgrade your database to standard edition or developer edition, write lengthy query and schedule using PowerShell, or use some third party SW.



One way synchronization is easily achievable from SQL Server Standard (as Publisher) and above to SQL Server Express (as a subscriber) using push distribution.

SQL Agent on the Express subscriber is not necessary in this scenario, as SQL Agent on the standard ed. publisher applies changes to the Express instance when push is configured.

Two way simultaneous replication is more difficult without a third party tool, but there are some options available:

(1) Upgrade both to SQL Server Standard or above, then configure transaction publication on one server and an updatable subscription at the other server.

Works by replicating transactions against publisher to subscriber by applying transaction logs to subscriber. Transactions made against the subscriber are applied to the publisher rather than directly to the subscriber. Those changes are then replicated back to subscriber through one way transactional replication from the publisher. You also need to configure conflict resolution policies, and some options related to mode of operation. Not the most efficient implementation of two way sync, but it works and is built in to mssql.

(2) Upgrade express edition to SQL Server standard or above, then use merge replication. This approach is problematic for large tables and low latency requirements, b/c synch process requires the distributor to send full snapshots to peers after it performs merge and conflict resolution. Works fine if not slightly better for smaller tables that you can sync on a set schedule. is also super easy to maintain,. Conflicts are resolved based on policies you configure.

(3) Use either Peer to Peer replication (both servers must run Enterprise) or set both servers as publisher and subscriber to move changes from one server to the other. In both cases, you'll need to code your own conflict resolution logic. For Peer to Peer replication, conflicts must be resolved before changes are committed against either server, otherwise one of the servers will go down when conflicts are detected pending admin action. P2P is a true multiway sync, b/c each peer node functions as both subscriber, publisher, and distributor. Fast, but missing crucial conflict resolution and convergence code.

MSSQL has no complete multiway replication solution like Oracle symmetric replication. Built in tools are available to handle some common scenarios, so some of these may server your needs.

As for your SSMS problem, can you be more specific? It could be a configuration issue thats keeping you out of ssms. Even on the Express edition, you should still be able to configure it as a subscriber.


by using 3rd-Party tools like

Open-Soure SymmetricDS

Commercial SQL Data Compare


The company I work for, developes a tool called xSQL Comparison Bundle which includes both data and schema compare software.

  • Does this software also synchronize data between to servers?
    – datagod
    Aug 24, 2016 at 14:47
  • @datagod Yes it can synchronize data as well Aug 25, 2016 at 15:14

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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