Synchronizing two SQL Server database tables with the most recent changes to both tables

Question

I'm very new to SQL, but I'm coding a C# program that interacts with a database. There's a master database on my computer, and there's a client database on my tablet.

When I re-connect my tablet to the network, I want it to merge the two databases, so that changes made on the tablet are pushed to the master, and changes made to the master are pushed to the client.

Here's my code to initialize the local database on the tablet:

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

EXEC sp_addlinkedserver @server='Server'

Here's my code to synchronize the two databases:

-- update the client from the master 
MERGE [LocalClaytonDB].[dbo].[tableName] trgt
using [Server].[Clayton].[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].[Clayton].[dbo].[tableName] serverDB

INNER JOIN
     [LocalClaytonDB].[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

Also, I'm slightly worried about what would happen if this script failed partway through the process, and also just for anything I might be missing.

I'd love it if someone could look over this and point out any shortcomings. As I said, this is the first real SQL I've written and my program is going to have to rely on it.

Answer 1

How often would you be writing to the master? And what purpose is Master serving in your setup?

Merge or peer-to-peer replication is one solution to keep both databases in sync but keep in mind they can be time consuming to troubleshoot and adds another extra thing in the mix to worry about.

Typically this would be done by updating records based on ID and last_updated key on tables. Merge statements can be a cause for race conditions where multiple users try and run the merge at the same time. Doing these operations with individual statements may keep you more clarity on the different operations that are happening on the db and keep things simple.

Delete: I would do this first by marking the records as 'soft-delete' (can be bit value) on master. Then running a sql server job on a schedule to remove all the records from tables which are marked for deletion.

Insert: Pull new records down from master based on the primary key value being higher than what's in the client.

Update: Based on the last_update field. This will solve the issue of updating tables on the remote server as well.