将数据从本地SQL Server数据库复制到Azure SQL Server数据库

I have two SQL Server instances, one being an on-premise SQL Server and the other being Azure SQL Server instance. Some of the tables in the Azure SQL Server database have a few columns which contain data from the on-premise SQL Server database (although the table schemas are different).

我有两个SQL Server实例,一个是内部部署的SQL Server,另一个是Azure SQL Server实例。 Azure SQL Server数据库中的某些表包含一些列,其中包含来自内部部署SQL Server数据库的数据(尽管表模式不同)。

We need to make sure that whenever new entries are added into the on-premises SQL Server database, the corresponding entries should get inserted in the Azure SQL Server database as well.

我们需要确保每当将新条目添加到本地SQL Server数据库时,相应的条目也应插入Azure SQL Server数据库中。

What is the best way to do this?

做这个的最好方式是什么?

1 个解决方案

#1

You can create your own code using Sync Framework to specify what specific tables you want sync.

您可以使用Sync Framework创建自己的代码,以指定要同步的特定表。

using (SqlConnection sqlServerConn =
    new SqlConnection(LocalSQLServerConnectionString))
{
    using (SqlConnection sqlAzureConn =
        new SqlConnection(RemoteSQLAzureConnectionString))
    {
        DbSyncScopeDescription myScope =
            new DbSyncScopeDescription(scopeName);
        DbSyncTableDescription Customer =
            SqlSyncDescriptionBuilder.GetDescriptionForTable("SalesLT.Customer", sqlServerConn);

        DbSyncTableDescription Product =

            SqlSyncDescriptionBuilder.GetDescriptionForTable("SalesLT.Product", sqlServerConn);

        // Add the tables from above to the scope
        myScope.Tables.Add(Customer);
        myScope.Tables.Add(Product);

The next section of code sets up the local on-premise SQL Server for provisioning. If the SQL Server already contains the table schemas and data then what does it have to do? The Synchronization Framework uses both databases as data storage to store configuration information, and state information about the current status of the synchronization. So the provisioning creates tables on your local SQL Server to store this information.

下一部分代码设置本地内部部署SQL Server以进行配置。如果SQL Server已经包含表模式和数据,那么它必须做什么?同步框架使用两个数据库作为数据存储来存储配置信息,并使用有关同步当前状态的状态信息。因此,配置会在本地SQL Server上创建表以存储此信息。

// Setup SQL Server for sync

SqlSyncScopeProvisioning sqlServerProv =
    new SqlSyncScopeProvisioning(sqlServerConn, myScope);
if (!sqlServerProv.ScopeExists(scopeName))

    // Apply the scope provisioning.
    sqlServerProv.Apply();

The next section of code does the same thing for the remote SQL Database server. However, it also creates the schemas data tables that it is going to synchronize too, based on the local SQL Server scope. Here is what the code looks like:

下一部分代码对远程SQL数据库服务器执行相同的操作。但是,它还会根据本地SQL Server范围创建要同步的模式数据表。这是代码的样子:

// Setup SQL Database for sync

SqlSyncScopeProvisioning sqlAzureProv =
    new SqlSyncScopeProvisioning(sqlAzureConn, myScope);
if (!sqlAzureProv.ScopeExists(scopeName))

    // Apply the scope provisioning.
    sqlAzureProv.Apply();

To synchronize the databases just run the console application like this:

要同步数据库,只需运行控制台应用程序,如下所示:

SyncConsole.exe –setup

Database setup just needs to happen once, however you will might want to synchronize the database multiple, because of this the code is split into two different sections one for setup and one for synchronization.

数据库设置只需要发生一次,但是您可能希望同步多个数据库,因此代码被分成两个不同的部分,一部分用于设置,另一部分用于同步。

The code synchronizing the data is just as simple. Here is what it looks like:

同步数据的代码同样简单。这是它的样子:

using (SqlConnection sqlServerConn = new SqlConnection(LocalSQLServerConnectionString))

{
    using (SqlConnection sqlAzureConn = new SqlConnection(RemoteSQLAzureConnectionString))
    {
        SyncOrchestrator syncOrchestrator = new SyncOrchestrator
        {
            LocalProvider = new SqlSyncProvider(scopeName, sqlAzureConn),
            RemoteProvider = new SqlSyncProvider(scopeName, sqlServerConn),
            Direction = SyncDirectionOrder.UploadAndDownload
        };
         syncOrchestrator.Synchronize();
    }
}

In the synchronization code we create two connection and instantiate a sync orchestrator, telling it that we want to upload and download the data. This is considered bi-directional synchronization, writes in either SQL Database or SQL Server to be moved to the other.

在同步代码中,我们创建了两个连接并实例化了一个同步协调器,告诉它我们要上传和下载数据。这被认为是双向同步,在SQL数据库或SQL Server中写入以移动到另一个。

To synchronize the databases just run the console application like this:

要同步数据库,只需运行控制台应用程序,如下所示:

SyncConsole.exe –sync

Once the synchronization has completed, we can query the SQL Database and see that the data in is there.

同步完成后,我们可以查询SQL数据库并查看数据是否存在。

To see a full example of how to do it, please visit this article.

要查看如何执行此操作的完整示例,请访问此文章。