sql-server - Microsoft SQL Server 同步 SQL Server 2008 r2 ( 在本地服务器上运行) ( 运行在plesk面板服务器数据库上)

  显示原文与译文双语对照的内容
0 0

在 C# 中使用 vs 2010创建一个 Windows 应用程序,它将同步两个数据库在本地机器上的数据库和服务器上的另一个数据库。 这是我的样本代码。

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
using Microsoft.Synchronization;
using Microsoft.Synchronization.Data;
using Microsoft.Synchronization.Data.SqlServer;
using Microsoft.Synchronization.Data.SqlServerCe;
namespace ExecuteExpressSync
{
 class Program
 {
 static void Main(string[] args)
 {
 SqlConnection clientConn = new SqlConnection(@" Data Source=...; Initial Catalog= SqlServerSyncDb; Network Library=;Connection Timeout=15;Packet Size=4096;Integrated Security=no;User ID=.....;Password=....;Encrypt=no;");
 SqlConnection serverConn = new SqlConnection("Data Source=(local); Initial Catalog=SyncDB; Integrated Security=True");
 var providerl = new SqlSyncProvider("scopel", serverConn);
 var provider2 = new SqlSyncProvider("scopel", clientConn);
//corvfig
 PrepareServerForProvisioning(providerl);
 PrepareClientForProvisioning(provider2, serverConn);
//sync
 SyncOrchestrator sync = new SyncOrchestrator();
 sync.LocalProvider = providerl;
 sync.RemoteProvider = provider2;
//((SqlCeSyncProvider)sync.LocalProvider).ApplyChangeFailed += new EventHandler<DbApplyChangeFailedEventArgs>(Program_ApplyChangeFailed);
 SyncOperationStatistics syncStats = sync.Synchronize();
//print statistics
 Console.WriteLine("Start Time:" + syncStats.SyncStartTime);
 Console.WriteLine("Total Changes Uploaded:" + syncStats.UploadChangesTotal);
 Console.WriteLine("Total Changes Downloaded:" + syncStats.DownloadChangesTotal);
 Console.WriteLine("Complete Time:" + syncStats.SyncEndTime);
 Console.WriteLine(String.Empty);
 }
 static void Program_ApplyChangeFailed(object sender, DbApplyChangeFailedEventArgs e)
 {
//display conflict type
 Console.WriteLine(e.Conflict.Type);
//display error message 
 Console.WriteLine(e.Error);
 }
 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("Products", connection));
 scopeDesc.Tables.Add(SqlSyncDescriptionBuilder.GetDescriptionForTable("Orders", 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();
 }
 }
 }
}

当我在两台运行同一机器的数据库上使用相同的代码时,它运行得很完美。 但是,当我使用plesk面板服务器数据库它的错误说你的数据库未提供。 打开服务器数据库时,我看到表已经创建,但没有数据。 另外,当我试图查看表数据时,它表示"索引超出绑定 array [Microsoft.SqlServer.Smo]"。

请建议该做什么。 先谢谢了

时间:原作者:5个回答

0 0

在这个小项目中我遇到了几个问题。

  1. 闪存 2008 R2中的错误。
  2. 同步错误 [Database is not provisioned] 。
  3. 对象架构问题。

问题 1:

This problem occurred because of database version difference. My local machine Database is SQL Server 2008 r2 and plesk panel database is SQL Server 2005.

解决方案:

 Updated my local machine database to service pack 2. 

问题 2 &问题 3

 Problem 2 and 3 are related. In sql server 2008 the default object schema is"dbo" [ex. dbo.tablename] and in sql server 2005 default object is"[DATABASENAME]" [ex. TestSync.tablename considering database name is TestSync]. That's why the sync error occurred.
 So to fix the problem I tried to set the object schema manually from within the code and set `config.ObjectSchema ="dbo";` in client provisioning.
 But surprisingly it didn't work at all. The plesk panel server database was still creating its default object schema [TestSync.tablename]. When I tried debugging the code I learned that if I set the object schema to"dbo" it takes a null value and really don't know the reason for that.

解决方案

 So I set the object schema to".dbo". 

config.ObjectSchema =".dbo" ;

现在它完美地工作了。 下面是完整的工作代码。

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
using Microsoft.Synchronization;
using Microsoft.Synchronization.Data;
using Microsoft.Synchronization.Data.SqlServer;
using Microsoft.Synchronization.Data.SqlServerCe;
namespace ExecuteExpressSync
{
 class Program
 {
 static void Main(string[] args)
 {
 SqlConnection clientConn = new SqlConnection("Data Source=208.91.198.174; Initial Catalog=RANAJOYDADIGI;Integrated Security=no;User ID=RANAJOYDADIGI;Password=RANAJOYDADIGI;Encrypt=no;Integrated Security=No");
 SqlConnection serverConn = new SqlConnection("Data Source=(local); Initial Catalog=ANIRUDDHADHARTEST; Integrated Security=True");
 var providerl = new SqlSyncProvider("scopel", serverConn);
 var provider2 = new SqlSyncProvider("scopel", clientConn);
//corvfig
 PrepareServerForProvisioning(providerl);
 PrepareClientForProvisioning(provider2, serverConn);
//sync
 SyncOrchestrator sync = new SyncOrchestrator();
 sync.LocalProvider = providerl;
 sync.RemoteProvider = provider2; 
 SyncOperationStatistics syncStats = sync.Synchronize();
//Deprovision
 deprovisionServerClient(providerl);
 deprovisionServerClient(provider2);
 serverConn.Close();
 serverConn.Dispose();
 clientConn.Close();
 clientConn.Dispose();
 } 
 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("dbo.Products", connection));
 scopeDesc.Tables.Add(SqlSyncDescriptionBuilder.GetDescriptionForTable("dbo.Orders", 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);
 config.ObjectSchema =".dbo";
 if (!config.ScopeExists(provider.ScopeName))
 {
 DbSyncScopeDescription scopeDesc = SqlSyncDescriptionBuilder.GetDescriptionForScope(provider.ScopeName, sourceConnection);
 config.PopulateFromScopeDescription(scopeDesc);
 config.Apply();
 }
 }
 private static void deprovisionServerClient(SqlSyncProvider provider)
 {
 SqlSyncScopeDeprovisioning serverSqlDepro = new SqlSyncScopeDeprovisioning((SqlConnection)provider.Connection);
 serverSqlDepro.DeprovisionScope(provider.ScopeName);
 serverSqlDepro.DeprovisionStore();
 }
 }
}

最后感谢大家为你提供的帮助。

原作者:
...