How to merge and synchronize SQL Server Database Files?

Question

I'm building a C# program for windows tablets that are synchronized with a database. They will each have their own local .MDF SQL Server database which they interact with using SQL Server Express.

However the users will take the tablets out of an internet connection and make changes. Then when one reconnects to the network which contains a "master copy" of the DataBase, I want to synchronize their .MDF database with that. Then replace the database files of the computer and the tablet with the newly synchronized one.

I have a uniqueidentifier column, and a datetime of when that row was last changed, so if there were conflicts I'd just take the most recent change.

I've read some literature on this, but I'd love to see an explicit example or tutorial of how to do it. I know what I want is Merge Replication, and that Microsoft Sync Framework seems to have the functionality I want. I just am struggling to implement it. Alternatively, feel free to recommend a different tool to do it.

Thanks in advance!

Maurer

are you familiar with the following terms Disconnected RecordSet, Delta's I would do some research / googling on how to sync database when offline etc.. – Barnie

No, I wasn't, thank you very much! Part of the struggle when googling this stuff for the first time is I don't know all the right keywords. – Maurer

Answer 1

I know this isn't much of an answer but I'm sure this is done with Microsoft Sync Framework. I'm also quite sure you can easily sync a db from a tablet even though it was offline. The only limitation could be the lack of live sync for which you'd need to use Azure services, WebSockets, Apache MQ or whatever. Apologies for lack of more info, I'd post it as a comment but don't have enough points.

Mailbox

Answer 2

if it's a Windows Store App (WinRT), you have to use the Sync Framework Toolkit .

if plain Windows app (WPF, WinForms, etc...), yes, you can use Sync Framework.

the idea for both is that your Windows app uses a local database (SQL CE, LocalDB, SQLite, etc...) for CRUD that you occasionally sync with a central server when you have connection (via WCF, like this)

Covalence

Answer 3

MS Merge Replication is complex, but based on your use case you will not need most of the functionality. I will warn you ahead of time that it is known to have problems. It's not very reliable in the Enterprise. It works but it just requires baby sitting. If you don't mind the service calls, then proceed. If you want a robust sync system (for your use case) then you are better off rolling your own using audit tables. It's not that difficult to do; especially since you are already using guids as your primary keys. You would also benefit from using sequential guids. Random guids will fragment your clustered index and your database performance will suffer greatly. If you need help with implementation, reach out to me. I've successfully done this for a client. They process over a million records per day without a single issue. If you still want to use MS Merge Replication I can do that for you as well.

Beckner

Interesting, I haven't heard of audit tables. I'll check it out, thanks. Do you also have any thoughts on the Sync Framework? – Maurer

Honestly I would stay away from the MS Sync frameworks completely for this use case. If you just want to replicate data, you can do it with audit tables and a service to process pending changes. It takes a bit more work up front but you will save you lots of service calls in the end. – Beckner

Please the "create table" statement for one your tables. I will generate a sample for you. – Beckner

Answer 4

Most existing tools like Microsoft Sync and Merge-Replication ended seeming likebeing way too much overkill and being more hassle than they'd be worth.

This is my SQL Script to attach the databases

CREATE DATABASE LocalDatabase  
ON (Filename = 'C:\ProgramData\Clayton\Database.mdf')
   , (Filename = 'C:\ProgramData\Clayton\Database_log.ldf') 
FOR ATTACH;
GO

EXEC sp_addlinkedserver @server='Server'

Then to sync the databases

-- update the client from the master 
MERGE [LocalDatabase].[dbo].[tableName] trgt
using [Server].[ServerDatabase].[dbo].[tableName] src

ON trgt.id = src.id 

WHEN matched AND trgt.lastmodified <= src.lastmodified THEN 
  -- if the master has a row newer than the client
  -- update the client                       
  UPDATE SET trgt.[allColumns]      = src.[allColumns],
             trgt.[id]              = src.[id], 
             trgt.[lastmodified]    = src.[lastmodified] 

-- delete any rows added by a client 
WHEN NOT matched BY source 
THEN 
  DELETE 

-- insert any rows added by the master 
WHEN NOT matched BY target 
THEN 
  INSERT ( [allColumns], 
           [id], 
           [lastmodified]) 
  VALUES (src. [allColumns], 
          src.[id], 
          src.[lastmodified]); 


-- now we update the master from the client
-- Note:
-- because the serverDB is a linked server 
-- we can't use another MERGE statement, otherwise
-- we get the error: "The target of a MERGE statement 
-- cannot be a remote table, a remote view, or a view over remote tables."

UPDATE
    serverDB

SET 
    [allColumns]        = [localDB].[allColumns],
    [id]                = [localDB].[id], 
    [lastmodified]      = [localDB].[lastmodified] 

FROM 
     [Server].[ServerDatabase].[dbo].[tableName] serverDB

INNER JOIN
     [LocalDatabase].[dbo].[tableName] localDB

-- update where the id is the same but the client is newer than the master

ON serverDB.id = localDB.id 
       AND localDB.lastmodified >= serverDB.lastmodified

Maurer