Automatically Sync SQL Databases across two computers

Question

I am using a cloud backup/sync service (SpiderOak) which automatically Syncs folders across several computers / devices.

I am trying to figure out a way to automatically sync all my databases across my work computer and personal laptop, without actually needing to backup/restore from one instance to the other.

So what I am thinking of is to create a new sql instance on my laptop which is identical to my work desktop instance, then to pick both SQL Server directories in Program Files to sync with each other using SpiderOak (the whole root SQL Server folders).

Will this be enough for my two instances to Sync with each other? Meaning if I create a new database on my computer at work, will I see this database on my laptop when I open SQL Server Database Management Studio?

I am almost sure if databases already exist they will sync with each other (since the root folders contain the mdf & ldf files - but correct me if I am wrong). however, I am not sure if a new database will be created if it doesn't already exist on one of the machines.

Is there any other folders that I need to sync other than the ones I specified already?

@RageCompex because I use SQL Express on my development machine and I use it to locally test my local software before deployment. — Wassim Taher, Dec 20 '14 at 18:11
You could use the Microsoft Sync Framework, as long as you have 2 connection strings :) Would you like an answer about that? — CularBytes, Dec 20 '14 at 18:17
@RageCompex if this works for MS SQL Server 2008 then yes please do so and I will accept that as an answer. I hope you make it clear because there's many articles online about Microsoft Sync Framework but they aren't straight to the point. Thanks — Wassim Taher, Dec 20 '14 at 20:00

score 2 · Accepted Answer · edited May 23 '17 at 12:30

You could use Sql Sync Framework, you can download it here some more readfood It works for Sql Server 2005 Download and import references and include with the default ones:

using System.Data.Sql;
using System.Data.SqlClient;
using Microsoft.Synchronization;
using Microsoft.Synchronization.Data;
using Microsoft.Synchronization.Data.SqlServer;
using System.Diagnostics;
using System.Reflection;
using System.Net;

Than the actual code:

private void SyncTables()
{
    SqlConnection ConStringOnline = new SqlConnection("connstring");
    SqlConnection ConStringOffline = new SqlConnection("connString");
    SyncOrchestrator sync = new SyncOrchestrator();
    sync.Direction = SyncDirectionOrder.Download; //or DownloadAndUpload
    //the 'scope1' is important, read more about it in the articles
    var provider1 = new SqlSyncProvider("scope1", ConStringOnline);
    var provider2 = new SqlSyncProvider("scope1", ConStringOffline);

    PrepareServerForProvisioning(provider1);

    PrepareClientForProvisioning(provider2, ConStringOnline);


    sync.LocalProvider = provider2;
    sync.RemoteProvider = provider1;

    sync.Synchronize();

}

private static void PrepareServerForProvisioning(SqlSyncProvider provider)
{
    SqlConnection connection = (SqlConnection)provider.Connection;
    SqlSyncScopeProvisioning config = new SqlSyncScopeProvisioning(connection);
    if (!config.ScopeExists(provider.ScopeName))
    {
        DbSyncScopeDescription scopeDesc = new DbSyncScopeDescription(provider.ScopeName);
        scopeDesc.Tables.Add(SqlSyncDescriptionBuilder.GetDescriptionForTable("TABLENAME", connection));

        config.PopulateFromScopeDescription(scopeDesc);

        config.SetCreateTableDefault(DbSyncCreationOption.CreateOrUseExisting);
        config.Apply();
    }
}

private static void PrepareClientForProvisioning(SqlSyncProvider provider, SqlConnection sourceConnection)
{
    SqlSyncScopeProvisioning config = new SqlSyncScopeProvisioning((SqlConnection)provider.Connection);
    if (!config.ScopeExists(provider.ScopeName))
    {
        DbSyncScopeDescription scopeDesc = SqlSyncDescriptionBuilder.GetDescriptionForScope(provider.ScopeName, sourceConnection);
        config.PopulateFromScopeDescription(scopeDesc);
        config.Apply();
    }
}

The downside of using Sync Framework: It is a pain in the a** to add these prerequisites to your application before publishing, no problem if you just use an application for yourself or for your company, but when you would like to publish it online it is a bit harder. I already had a topic about that

However, when using tools like InnoScript, you can install the prerequisites easily while installing the application. Here is how.

Now for the ScopeName: It is important that you don't use twice the same name, I believe. I had multiple tables so I just named them scope1,scope2,scope3,scope4. Apparently Sync Framework does the rest of the work for you. It also automatically adds _tracking tables to your database, this is just metadata to store information to synchronize properly.

Automatically Sync SQL Databases across two computers

1 Answers1

Linked