在两台计算机之间自动同步SQL数据库 [英] Automatically Sync SQL Databases across two computers

问题描述

我正在使用云备份/同步服务(SpiderOak)，该服务会自动在多台计算机/设备之间同步文件夹.

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.

所以我在想的是在笔记本电脑上创建一个与工作桌面实例相同的新sql实例，然后在Program Files中选择两个SQL Server目录以使用SpiderOak(整个根SQL)相互同步.服务器文件夹).

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).

这足以使我的两个实例彼此同步吗?这意味着如果我在工作中的计算机上创建一个新数据库，那么当我打开SQL Server Database Management Studio时会在笔记本电脑上看到该数据库吗?

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?

我几乎可以确定数据库是否已经存在，它们将彼此同步(因为根文件夹包含mdf和ldf文件-如果我错了，请更正我).但是，我不确定如果其中一台计算机上不存在新数据库，是否会创建该新数据库.

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?

推荐答案

您可以使用Sql Sync Framework，也可以下载 readfood 它适用于Sql Server 2005下载并导入参考，并包含默认参考:

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;

比实际代码:

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();
    }
}

使用Sync Framework的弊端:在发布前将这些先决条件添加到您的应用程序中是一件很痛苦的事情，如果您只是为自己或公司使用应用程序，那没问题，但是当您想要在线发布它有点困难.我已经有与此相关的主题

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

但是，在使用 InnoScript 之类的工具时，您可以安装必备软件在安装应用程序时很容易.这就是方法.

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

现在输入 ScopeName :重要的是，不要使用相同的名称，这是很重要的.我有多个表，所以我只将它们命名为 scope1，scope2，scope3，scope4 .显然，Sync Framework为您完成了其余工作.它还会自动将 _tracking 表添加到您的数据库中，这只是用于存储信息以正确同步的元数据.

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.

这篇关于在两台计算机之间自动同步SQL数据库的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！