0

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?

Thanks!

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 '15 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... – LowlyDBA Jul 26 '17 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? – Xingzhou Liu Jul 26 '17 at 10:00
3

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.

Thanks

1

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.

0

by using 3rd-Party tools like

Open-Soure SymmetricDS

Commercial SQL Data Compare

0

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 '16 at 14:47
  • @datagod Yes it can synchronize data as well – Endi Zhupani Aug 25 '16 at 15:14

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.