Best way to sync remote SQL Server database with local SQL Server Compact database?

Question

I realise this is a much discussed topic but all the suggestions I see seem to involve direct access to the SQL Server which in our instance is not ideal.

Our scenario is a remote SQL Server database with (say) 100 tables. We are developing a lightweight desktop application which will use an SQL Server Compact database and sync a subset of (say) 20 tables with the remote server periodically.

I would like to have control over how the replication occurs because speed is a major issue for us since the remote server is 1000's of miles away.

Also I don't need to sync all the records in each table - only those relevant to each user.

I quite like the SQL Merge facility however it requires that the client be connected to the remote SQL Server. This is currently not possible and we were thinking of interfacing to the remote server through a web service accessed through our application or some other method.

Any suggestions welcome.

UPDATE

Just to clarify, internet connection will be intermittent, that's the main reason why we need to sync the two databases.

Answer 1

The fact that you are using a compact db for the client puts some pretty heavy limitations on you for available options in this scenario.

Given the limitations and the performance requirements you desire, you could consider implementing a service-based http endpoint to keep the desired tables in sync. If your architecture allows for it, doing so asynchronously would boost performance significantly but again, it may not even be viable depending on your architecture.

Something else to consider is using web sockets rather than standard http connections for a web service like mentioned above. That way you could keep the clients synced real-time, since web sockets are true fully duplex real-time connections. The major catch with this is you would either have to ensure all clients are web-socket compliant or provide a fall-back to emulate a websocket connection with an emulation framework for clients that aren't up to par.

Not sure if this helps any.

Answer 2

You have the choice of both Sync Framework (requires more coding and has some other limitations) or Merge Replication, both work over http/https) - see this blog post for a comparision: http://blogs.msdn.com/b/sqlservercompact/archive/2009/11/09/merge-replication-vs-sync-services-for-compact.aspx