How can I combine two databases on two different servers?
I have two different databases: the client file is bound from .MDF
to the server .\SQLEXPRESS
. The wizard runs on a server on another computer called COMPUTER_NAME
.
I want to combine them using C # to run a file .SQL
. I'll paste my code below for reference, but basically my problem is that if I connect to the server with
string sqlConnectionString = @"Server=.\SQLEXPRESS; Trusted_Connection=True";
Then I cannot find the database on COMPUTER_NAME
. And if I use
string sqlConnectionString = @"Server=COMPUTER_NAME; Trusted_Connection=True";
It will look for my file .MDF
on the C: drive COMPUTER_NAME
, not the local machine.
How can I connect to both of these databases on different servers?
Additional Information:
SQL script I am using. This worked great when both bases were on the same server, but I can't do it 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 this:
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 whole process happens in .SQL
or in C#
if it has the desired effect.
Thanks in advance for any recommendation or recommendation.
source to share
Linking servers will help you access data at the same time, if required. If you want to merge data together, I would suggest you check sp_generate_merge to pull the data into a merge script for you (very handy for moving data around). See also my question about generating merge data here .
source to share
Ok, I had to throw out the whole thing completely .MDF
. Instead of binding and re-binding the database from .MDF
I just created the database.
Here's my code to initialize a local database on a 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 for syncing 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
source to share