Hello,
You could do that using SQL server replication. The idea is to use features built in SQL server to allow synchronising databases located on different machines.
There is a lot of literature on SQL replication, do a google search and spend some time reading.
Here's a good start:
http://code.msdn.microsoft.com/windowsdesktop/SQL-Server-Express-05c73322[
^]
And here is a code sample provided by Microsoft: (in the link above)
using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Text;
using Microsoft.SqlServer.Replication;
using Microsoft.SqlServer.Management.Common;
namespace MergeAgentRMO
{
class Program
{
static void Main(string[] args)
{
SynchronizeMergePullSubscriptionViaRMO();
}
static void SynchronizeMergePullSubscriptionViaRMO()
{
string subscriberName = "WIN8CP\\SQLEXPRESS";
string publisherName = "WS2008R2_1";
string distributorName = "WS2008R2_1";
string publicationName = "TestMergePub2";
string subscriptionDbName = "TestSubDB1";
string publicationDbName = "AdventureWorksLT";
ServerConnection conn = new ServerConnection(subscriberName);
MergePullSubscription subscription;
MergeSynchronizationAgent agent;
try
{
conn.Connect();
subscription = new MergePullSubscription();
subscription.ConnectionContext = conn;
subscription.DatabaseName = subscriptionDbName;
subscription.PublisherName = publisherName;
subscription.PublicationDBName = publicationDbName;
subscription.PublicationName = publicationName;
if (subscription.LoadProperties())
{
agent = subscription.SynchronizationAgent;
agent.PublisherSecurityMode = SecurityMode.Integrated;
agent.DistributorSecurityMode = SecurityMode.Integrated;
agent.Distributor = publisherName;
agent.OutputVerboseLevel = 4;
agent.Output = "C:\\TEMP\\mergeagent.log";
agent.Synchronize();
}
else
{
throw new ApplicationException(String.Format(
"A subscription to '{0}' does not exist on {1}",
publicationName, subscriberName));
}
}
catch (Exception ex)
{
throw new ApplicationException("The subscription could not be " +
"synchronized. Verify that the subscription has " +
"been defined correctly.", ex);
}
finally
{
conn.Disconnect();
}
}
}
}
Valery.