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.
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
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.
The company I work for, developes a tool called xSQL Comparison Bundle which includes both data and schema compare software.
