Sync data from local db to Central SQL Server

Question

I have a requirement to sync local db data with central SQL server. The remote users (mostly around 10 people) will be using laptop which will host application and local db. The internet connection is not 24x7. During no connectivity, the laptop user should be able to make changes in local db and once the connection is restored, the data should be synced with central SQL server automatically. The sync is just usually data updates. I have looked at options Sync framework and Merge replication. I can’t use sync framework as I am not C# expert. For Merge replication, additional hardware is required I believe which is not possible. The solution should be easy to develop and maintain.

Are there any other options available? Is it possible to use SSIS in this scenario?

Steeplejack

Are you referring to a local installation of SQLServer on each individual users machine? Or the actual 'localdb' ((localdb)\V11.0) that is accessible for each user on the main server? – Exuberate

I am referring to SQL Server express on a laptop. – Steeplejack

Have you looked at learn.microsoft.com/en-us/azure/app-service-mobile/…? Although the name is mobile services, it might be suitable for your use case – Handgrip

Service Broker might be a choice for you. Store your data locally as you currently have architected. Provide a button that says Sync now (or a trigger when it's inserted/updated) which encapsulates the data into XML messages and drops them into a service broker queue. SB will try to talk to the central server when it has connectivity, otherwise it'll time out and try again. – Rheology

Answer 1

I would use Merge replication for this scenario. I'm unaware of any "additional hardware" requirements.

SSIS could do this job but it does not give you any help out-of-the-box - you would be reinventing the wheel for a very common and complex scenario.

Lilongwe

Answer 2

an idea... Idea requires an intermediate database (exchange database). On the exchange database you have tables with data for each direction of synchronization. And using change tracking on exchange db, and central.

On the local database side could mean rows with flags:

• row is created on local db
• row comes with exchange db
• row required resynchronisation (when is updated, ect.)

Synchronisation localdb-exchange db. When synchronizing, first send the data in localdb (marked as created locally or required resynchronisation), later download the data from exchange db (marked by change trancking as changed).

Synchorisation beetween exchange db and central db is simply, basen on change tracking with the database engine.

About Change Trancking here!

Lindseylindsley