How to merge two databases on two different servers?

Question

I have two different databases, the client one is attached from a .MDF file to a .\SQLEXPRESS server. The master one is running on a server on another computer called COMPUTER_NAME.

I want to merge these using C# to run a .SQL file. I'll paste my code below for reference, but basically my problem is that if I connect to the server using

 string sqlConnectionString = @"Server=.\SQLEXPRESS; Trusted_Connection=True";

Then I can't find the database on COMPUTER_NAME. And if I use

 string sqlConnectionString = @"Server=COMPUTER_NAME; Trusted_Connection=True";

It will look for my .MDF file on the C: drive of COMPUTER_NAME, not the local machine.

How can I connect to both of these databases on different servers?

Additional info:

The SQL script I'm using. This worked perfectly back when both the databases were on the same server, but I can't do that anymore.

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

-- update the client from the master 
MERGE [ClientDB].[dbo].[table] trgt
using [MasterDB].[dbo].[table] src
ON trgt.id = src.id 

WHEN matched AND trgt.lastmodified <= src.lastmodified THEN -- if master row is newer 
   UPDATE SET trgt.[info] = src.[info], ...                 -- update the client 

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

WHEN NOT matched BY target                                  -- insert rows added by master 
   THEN INSERT ( [info], ... ) VALUES (src.[info], ... ); 


-- close all connections to database
ALTER DATABASE ClientDB SET SINGLE_USER WITH ROLLBACK IMMEDIATE; 

-- detach database
EXEC sp_detach_db 'ClientDB', 'true'; 

And I run it using C# like so:

 string sqlConnectionString = @"Server=.\SQLEXPRESS; Trusted_Connection=True";

 string script = File.ReadAllText(Environment.CurrentDirectory + @"\MergeTotal.sql");
 SqlConnection conn = new SqlConnection(sqlConnectionString);

 IEnumerable<string> commandStrings = Regex.Split(script, @"^\s*GO\s*$",
                            RegexOptions.Multiline | RegexOptions.IgnoreCase);

 conn.Open();
 foreach (string commandString in commandStrings)
 {
     if (commandString.Trim() != "")
     {
         using (var command = new SqlCommand(commandString, conn))
         {
             command.ExecuteNonQuery();
         }
     }
 }

I don't care if the entire process happens in the .SQL or in C# so long as it has the desired effect.

Thanks in advance for any guidance or recommendations.

Answer 1

Linking the servers would help you to be able to access the data simultaneously, if that's the requirement. If you're looking to merge data together, though, I'd suggest you check out sp_generate_merge to pull the data into a merge script for you (very handy for moving data). See also my question on generating merge data here.

Answer 2

Okay, I had to completely throw out the whole .MDF thing. Instead of attaching and reattaching the database from an .MDF, I just set up the database.

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