主页 / dba / 问题 / 105965
Accepted
Emad Farrokhi
Asked: 2015-07-05 08:01:22 +0800 CST

在 SQL Server 中同步两个数据库

  • 772

我有两个 SQL Server 数据库。一个是客户端（Windows 应用程序），第二个是服务器。我想每隔一段时间（例如每 2 分钟！）同步这两个数据库。

我已经了解了不同的同步方式，例如复制、时间戳、使用触发器的日志表、Microsoft Sync Framework 等等。

实际上，我不喜欢使用可能是黑盒（如复制）的同步方法，因为我不希望在更新 SQL Server 特定表并将它们与服务器同步时被阻止。

  1. 在这种情况下，您认为我应该使用哪种方法？请记住，每隔几分钟我必须从客户端向服务器发送几个表更改，并从服务器获取两个表更改。

  2. 我发现了一种奇怪但新的方法。是否有可能我在客户端记录所有已执行的（针对特定首选的）存储过程，并将它们与.sql文件中的参数一起发送到服务器并在那里执行它们？服务器上也会发生同样的情况并发送到客户端。你认为这是一个简单而有用的方法吗？

  3. 如果可以的话，请向我推荐任何有用的方法。太感谢了。

编辑：请记住，这是一个实时同步，这使它变得特别。这意味着当客户端用户使用表时，与服务器的同步过程必须每隔几分钟发生一次，因此不必锁定任何表。

sql-server data-synchronization

5 个回答

  1. Best Answer

    好吧，我可能不明白，但我试着回答它。

    您说您需要一个经常运行的高性能解决方案（至少所有 2 分钟），并且您需要一个很好的方法，该方法应该快速而无需锁定。但是您不想要黑盒系统。

    与在数百万安装中使用并获得良好结果的黑盒系统不同，您尝试再次发明轮子并构建自己的解决方案？嗯，听起来有点奇怪。

    其实这些都是我的建议。

    1. 复制即使你说你不会使用它。这是您可以使用的最简单和最好的解决方案。复制易于设置，复制速度快，您不必再次发明轮子。如果您只是对锁定感到奇怪，您可以尝试将 设置ISOLATION LEVEL为READ_COMMITTED_SNAPSHOT. 你可以在这里阅读更多关于它的信息。这将使用您的 tempdb 的一部分，但您的表始终是可读写的，并且复制可以在后台工作。

    请参见下面的示例：

    ALTER DATABASE yourDatabase SET ALLOW_SNAPSHOT_ISOLATION ON
ALTER DATABASE yourDatabase SET READ_COMMITTED_SNAPSHOT ON
    1. CDC（变更数据捕获）也可以是一种解决方案。但是这种方式你需要自己构建几乎所有东西。而且我的经历CDC在某些情况下可能很脆弱。CDC将捕获监视表上的所有数据（您需要手动指定每个监视表）。INSERT之后，您将获得,UPDATE或之前的值和之后的值DELETE。CDC将保留这些信息一段时间（您可以自行指定）。该方法可能是CDC在您需要监视的某些表上使用并将这些更改手动复制到其他数据库。顺便说一句，CDC在后台也使用 SQL Server 复制。;-) 你可以在这里阅读更多关于它的信息。

    警告：CDC不会意识到DDL-changes。这意味着，如果您更改表并添加新列，CDC将监视该表但忽略对新列的所有更改。实际上它只记录NULL为之前的值和之后的值。您需要在DDL-Changes 更改为监视表之后重新初始化它。

    1. 您上面描述的方式类似于使用 SQL Server Profiler 捕获工作负载并在另一个数据库上再次运行它以进行某些基准测试。那么它可以工作。但是副作用太多的事实对我来说有点太沉重了。如果您在客户端上捕获过程调用，您会怎么做。之后在您的主数据库上运行相同的命令，因为它不同步？该过程可能会运行，但它可能会删除/更新/插入您的客户端中不存在的行。或者你如何用一个原则处理多个客户。我认为这太棘手了。在最坏的情况下，你可能会破坏你的诚信。
    2. 另一个想法可能是基于应用程序或使用触发器。取决于您要同步多少表。您可以将所有更改写入单独的暂存表并在所有 x 分钟内运行 SQL Server 代理作业，以将暂存表中的这些行与您的主表同步。但是，如果您尝试同步（例如）150 个表，这可能会有点繁重。你会有很大的开销。

    好吧，这些是我的 2 美分。希望您有一个很好的概述，也许您找到了一种适合您的解决方案。

    • 14

  2. 我将尝试在这里列举一些我认为的优点和缺点的选项：

    1. SQL Server 复制- 这是用于此任务的最佳和最优化的本机 SQL Server 工具。但是有几个问题： a．对于您的所有客户端，无论它们是否是 SQL Express 数据库，您都需要 SQL Server CAL 许可证。使用按处理器许可可以避免这种情况。湾。您无法按照此处同步 SQL CE 客户端。C。SQL Express 或 LocalDB不能充当发​​布者或分发者，因此您对客户端对复制过程的控制较少。

    2. Microsoft Sync Framework - 在我看来更适合较小的移动应用程序数据库。它向您的数据库中添加了相当多的表，并且不如复制那么有效。由于它是作为组件在 SQL Server 外部实现的，因此配置起来会更加困难。我没有使用它的经验，只是尝试过并决定不使用它。

    3. 数据库更改跟踪。它是一个内置的 SQL Server 函数，可为您进行更改跟踪，包括插入、更新和删除。其他一切，例如发送和应用更改、解决冲突等，您都必须自己编写代码。

    4. Rowversion（时间戳）列如果您禁止所有删除（不同步已删除记录） - 您可以仅基于 rowversion 信息实施您自己的解决方案。SQL Server 复制也使用 Rowversion 列，因此无论如何您都需要添加它们。

    5. Ionic 的回答中提到的 CDC - 我没有使用它的经验，因为它仅在 Enterprise 或 Developer 版本中可用。

    6. 使用您自己的技巧来记录已执行的存储过程 - 很大程度上取决于您的数据库应用程序的性质。但是当程序变得有点不同时，你可能会得到一大堆数据。你会如何处理冲突？

    从您的问题来看，您似乎只需要同步几个表而不是整个大型数据库。为此，您应该比您在问题中指定的更详细地分析您的需求，例如：

    • 可以删除，然后会发生什么？
    • 冲突是否会发生，如何预防以及如何解决？
    • 我将如何处理表结构更改？
    • ...

    如果您最终发现，删除和冲突不是您的问题，并且您的结构不会发生太大变化，您可以考虑编写自己的逻辑，但它很容易增长到 1000 行代码。

    • 9

  3. 谢谢大家的反馈。

    我成功地解决了同步过程，方法是捕获执行的存储过程不是一堆而是一个一个，这在我的情况下效果很好。由于完整性和一切都经过仔细考虑，该系统到目前为止一直在实时运行。

    • 2

  4. 迟到的答案，但它可能有助于线程访问者

    我在尝试跨不同服务器分发数据时遇到了类似的挑战，并通过使用第三方工具（用于模式更改的Diff和用于数据更改同步的DataDiff）和遵循自动化流程所需的 PowerShell 脚本来解决它：

    #check for the existence of the Outputs folder
function CheckAndCreateFolder($rootFolder, [switch]$Outputs)
{
$location = $rootFolder

#setting up location 
if($Outputs -eq $true)
{
    $location += "\Outputs"
}

#if the folder doesn't exist it will be created
if(-not (Test-Path $location))
{ mkdir $location -Force:$true -Confirm:$false | Out-Null }

return $location
}

#root folder for the schema sync process
$rootFolder = "SchemaSync"

#schema output summaries location 
$outsLoc = CheckAndCreateFolder $rootFolder -Outputs

#ApexSQL Diff location, date stamp variable is defined, along with tool’s parameters 
$diffLoc   = "ApexSQLDiff"
$stamp = (Get-Date -Format "MMddyyyy_HHMMss") 
$Params = "/pr:""MyProject.axds""    /out:""$outsLoc\SchemaOutput_$stamp.txt"" /sync /v /f" 
$returnCode = $LASTEXITCODE

#initiate the schema comparison and synchronization process
(Invoke-Expression ("& `"" + $diffLoc +"`" " +$Params))

#write output to file
"$outsLoc\SchemaOutput_$dateStamp.txt"

#schema changes are detected
if($returnCode -eq 0)
{
"`r`n $returnCode - Schema changes were successfully synchronized" >> 

}
else
{
#there are no schema changes
if($returnCode -eq 102)
{
"`r`n $returnCode - There are no schema changes. Job aborted" >> 
}
#an error is encountered
else
{
"`r`n $returnCode - An error is encountered" >> 

#output file is opened when an error is encountered
Invoke-Item "$outsLoc\SchemaOutput_$stamp.txt"
}

}

    该方法计划两个数据库之间的比较并实时同步发现的更改。以下是一些提供分步说明的文章：

    https://solutioncenter.apexsql.com/automatically-compare-and-synchronize-sql-server-data/ https://solutioncenter.apexsql.com/how-to-automatically-keep-two-sql-server-database-同步模式/

    • 0

  5. 几年前我问过这个问题，我终于解决了，并在许多项目中使用了该解决方案

    1. 如果您的表完全相同（无论是否相同服务器），请使用跨国复制。设置非常简单，初始设置不会超过一个小时。但是，它可能需要一些技巧来更好地调试和理解您可以在许多网站上找到的机制。

    2. 如果你的目标表比你的源表有更多的列，我绝对推荐触发器，除非你在一秒钟内有这么多事务或者你的服务器很弱。

    我在几个同步项目中使用了触发方法，它就像一个魅力。

    CREATE TRIGGER [dbo].[Delete_UNIT] ON [DB2].[dbo].[UNIT]
AFTER DELETE
AS
BEGIN
DELETE S
FROM CRM.dbo.UNIT S
INNER JOIN Deleted D ON S.Unit_Code = D.Unit_Code 
END



CREATE TRIGGER [dbo].[Insert_UNIT] ON [DB2].[dbo].[UNIT]
AFTER INSERT
AS
BEGIN
INSERT INTO CRM.dbo.UNIT(Unit_Code, Unit_Name)
SELECT Unit_Code, Unit_Name
FROM inserted
END


CREATE TRIGGER [dbo].[Update_UNIT] ON [DB2].[dbo].[UNIT]
AFTER UPDATE
AS
BEGIN
UPDATE CRM.dbo.[UNIT] SET
      CRM.dbo.[UNIT].Unit_Code = S.Unit_Code
     ,CRM.dbo.[UNIT].Unit_Name = S.Unit_Name
FROM DB2.dbo.[UNIT] S
INNER JOIN Inserted I ON S.Unit_Code = I.Unit_Code
WHERE CRM.dbo.[UNIT].Unit_Code = S.Unit_Code 
END
    • 0

相关问题