Microsoft SQL Server 中的數據同步指南

在隔離系統之間共享相關信息對組織來說變得越來越重要，因為它可以提高數據的質量和可用性。 在許多情況下，擁有一個在多個目錄服務器中可用且一致的數據集很有用。 這就是為什麼了解執行 SQL Server 數據同步的常用方法很重要的原因。

數據的可用性和一致性可以通過數據複製和數據同步過程來實現。 數據複製是為了容錯或改進可訪問性而創建數據庫的一個或多個冗余副本的過程。 數據同步是在兩個或多個數據庫之間建立數據一致性的過程，並通過後續的持續更新來保持這種一致性。

在許多組織中，跨不同系統執行數據同步既可取又具有挑戰性。 我們可以找到許多需要執行數據同步的用例：

• 數據庫遷移
• 信息系統之間的定期同步
• 將數據從一個信息系統導入另一個
• 在不同階段或環境之間移動數據集
• 從非數據庫源導入數據

數據同步沒有唯一的方式或一致同意的方法。 這項任務因情況而異，由於數據結構的複雜性，即使是乍看之下應該很簡單的數據同步也可能變得複雜。 在實際場景中，數據同步由許多複雜的任務組成，可能需要很長時間才能執行。 當出現新需求時，數據庫專家通常必須重新實現整個同步過程。 由於沒有標準的方法來做到這一點，除了複製之外，數據同步的實現很少是最佳的。 這導致難以維護和更高的費用。 數據同步的實現和維護是一個非常耗時的過程，它本身可以是一項全職工作。

我們可以手動實現數據同步任務的架構，可能使用 Microsoft Sync Framework，或者我們可以從用於管理 Microsoft SQL Server 的工具中已經創建的解決方案中受益。 我們將嘗試描述可用於解決 Microsoft SQL Server 數據庫上的數據同步的最常用方法和工具，並嘗試給出一些建議。

基於源和目標的結構（例如，數據庫、表），當結構相似或不同時，我們可以區分用例。

來源和目的地具有非常相似的結構

當我們在軟件開發生命週期的各個階段使用數據時，經常會出現這種情況。 例如，測試環境和生產環境中的數據結構非常相似。 常見的需求是比較測試和生產數據庫之間的數據，並將生產中的數據導入測試數據庫。

來源和目的地具有不同的結構

如果結構不同，同步會更複雜。 這也是一個更頻繁重複的任務。 一種常見的情況是從一個數據庫導入另一個數據庫。 最常見的情況是一個軟件需要從另一個公司維護的另一個軟件中導入數據。 通常，導入需要按計劃自動運行。

使用的方法取決於個人喜好和您需要解決的問題的複雜性。

不管結構有多相似，我們都可以選擇四種不同的方式來解決數據同步問題：

• 使用手動創建的 SQL 腳本進行同步
• 使用數據比較方法進行同步（僅當源和目標具有相似結構時才能使用）
• 使用自動生成的 SQL 腳本進行同步 - 需要商業產品

源和目標具有相同或非常相似的結構

使用手動創建的 SQL 腳本

最直接和繁瑣的解決方案是手動編寫 SQL 腳本進行同步。

優點

• 可以通過免費和開源 (FOSS) 工具執行。
• 如果表有索引，它會非常快。
• SQL 腳本可以保存到存儲過程中，或作為 SQL Server 的作業定期運行。
• 可以用作自動導入，即使是在不斷變化的數據上。

缺點

• 創建這樣的 SQL 腳本非常繁瑣，因為每個表通常需要三個腳本： INSERT 、 UPDATE和DELETE 。
• 您只能同步可通過 SQL 查詢獲得的數據，因此無法從 CSV 和 XML 文件等來源導入。
• 很難維護——當數據庫結構發生變化時，需要修改兩三個腳本（ INSERT 、 UPDATE ，有時還有DELETE ）。

例子

我們將在具有列ID和Value的表Source和具有相同列的表Target之間進行同步。

如果表具有相同的主鍵，而目標表沒有自增（標識）主鍵，則可以執行以下同步腳本。

 -- insert INSERT INTO Target (ID, Value) SELECT ID, Value FROM Source WHERE NOT EXISTS (SELECT * FROM Target WHERE Target.ID = Source.ID); -- update UPDATE Target SET Value = Source.Value FROM Target INNER JOIN Source ON Target.ID = Source.ID -- delete DELETE FROM Target WHERE NOT EXISTS (SELECT * FROM Source WHERE Target.ID = Source.ID)

使用數據比較方法

在這種方法中，我們可以使用工具來比較源數據和目標數據。 比較過程生成 SQL 腳本，將源數據庫中的差異應用到目標數據庫中。

有許多用於數據比較和同步的程序。 這些程序大多使用相同的方法。 用戶選擇源數據庫和目標數據庫，但其他選擇可能是數據庫備份、帶有 SQL 腳本的文件夾，甚至是與源控制系統的連接。

以下是使用數據比較方法的最流行的工具：

• SQL Server 的 dbForge 數據比較
• RedGate SQL 數據比較
• Apex SQL 數據差異

第一步，讀取數據，或者僅讀取來自源和目標的較大數據的校驗和。 然後執行比較過程。

這些工具還提供了額外的同步設置。

我們需要設置以下數據同步所需的配置選項：

同步鍵

默認情況下，使用主鍵或UNIQUE約束。 如果沒有主鍵，可以選擇列組合​​。 Sync 鍵用於將源行與目標行配對。

表配對

默認情況下，表按名稱配對。 您可以更改此設置，並根據自己的需要將它們配對。 在 dbForge 數據比較軟件中，您可以選擇 SQL 查詢作為源或目標。

同步過程

確認後，該工具會比較源數據和目標數據。 整個過程包括下載所有源數據和目標數據，並根據指定的標准進行比較。 默認情況下，比較同名表和列中的值。 所有工具都支持映射列名和表名。 此外，還可以排除IDENTITY （自動增量）列或在比較值之前進行一些轉換（圓形浮點類型、忽略字符大小寫、將NULL視為空字符串等）優化數據下載。 如果數據量很大，則只下載校驗和。 這種優化在大多數情況下很有幫助，但執行操作的時間要求會隨著數據量的增加而增加。

在下一步中，有一個帶有生成遷移的 SQL 腳本。 該腳本可以保存或直接運行。 為了安全起見，我們甚至可以在運行此腳本之前進行數據庫備份。 ApexSQL Data Diff 工具可以創建在選定數據庫上運行腳本的可執行程序。 該腳本包含需要更改的數據，而不是如何更改它的邏輯。 這意味著腳本無法自動運行以提供重複導入。 這是這種方法的最大缺點。

優點

• 不需要高級 SQL 知識，可以通過 GUI 完成。
• 您可以在同步之前直觀地檢查數據庫之間的差異。

缺點

• 這是商業產品的高級功能。
• 傳輸大量數據時性能會下降。
• 生成的 SQL 腳本僅包含差異，因此不能重複用於自動同步未來的數據。

您可以在下面看到這些工具的典型 UI。

與自動生成的 SQL 同步

這種方法與數據比較方法非常相似。 與前一種方法相比，唯一不同的是沒有數據比較，生成的SQL腳本不包含數據差異，而是同步邏輯。 生成的腳本可以很容易地保存到存儲過程中，並且可以定期運行（例如，每晚）。 此方法對於數據庫之間的自動導入很有用。 該方法的性能遠優於數據比較方法。

通過自動生成的 SQL 進行同步僅由 SQL Database Studio 提供。

SQL Database Studio 提供了與數據比較方法類似的接口。 我們需要選擇源和目標（數據庫或表）。 然後我們需要設置選項（同步密鑰、配對和映射）。 有一個用於設置所有參數的圖形查詢構建器功能。

優點

• 不需要高級 SQL 知識。
• 您可以非常快速地在 GUI 中設置所有內容。
• 生成的 SQL 腳本可以保存到存儲過程中。
• 可用作自動導入 - 作為 SQL Server 的作業。

缺點

• 這是商業產品的高級功能。
• 同步前無法手動檢查差異，因為整個過程是一步執行的。

性能基準

測試用例

兩個數據庫（A 和 B），每個數據庫包含一個包含 2,000,000 行的表。 這些表位於同一 SQL Server 上的兩個不同數據庫中。 該測試涵蓋兩種極端情況：1）源表包含所有 2,000,000 行，而目標表為空。 同步需要提供很多INSERTS 。 2) 源表和目標表包含 2,000,000 行。 區別僅在一排。 同步只需要提供一個UPDATE 。

RedGate 數據比較需要 3 個步驟：

• 比較
• 生成腳本
• 在目標數據庫上運行腳本

ApexSQL 數據差異需要 2 個步驟：

• 比較
• 生成腳本和運行腳本一步到位

SQL Database Studio 一步完成整個同步。 以下是同步時間，以秒為單位。 在標有“單個步驟”的列中是上面列出的同步步驟的持續時間。

越低越好。

相同的測試，但數據庫位於不同的 SQL 服務器上，它們沒有通過鏈接服務器連接。

越低越好。

概括

從結果來看，很明顯 RedGate 和 Apex 並不關心數據庫是否在同一個 SQL Server 上，因為同步算法不依賴於 SQL Server。 SQL Database Studio 使用 SQL Server 的原生函數； 因此，當數據庫在同一台服務器上時，結果會更好。

源和目標具有不同的結構

還有一些情況是一張寬表必須同步到許多相關的小表中。

此示例由一個寬表 SourceData 組成，該表需要同步到小表Continent 、 Country和City中。 該方案如下。

SourceData 中的數據可能如下圖所示。

使用手動創建的 SQL 腳本

腳本同步大陸表

INSERT INTO Continent (Name) SELECT SourceData.Continent FROM SourceData WHERE (SourceData.Continent IS NOT NULL AND NOT EXISTS (SELECT * FROM Continent tested WHERE tested.Name =SourceData.Continent )) GROUP BY SourceData.Continent;

腳本同步城市表

INSERT INTO City (Name, CountryId) SELECT SourceData.City, Country.Id FROM SourceData LEFT JOIN Continent ON SourceData.Continent = Continent.Name LEFT JOIN Country ON SourceData.Country = Country.Name AND Continent.Id = Country.ContinentId WHERE SourceData.City IS NOT NULL AND Country.Id IS NOT NULL AND NOT EXISTS (SELECT * FROM City tested WHERE tested.Name = SourceData.City AND tested.CountryId = Country.Id) GROUP BY SourceData.City, Country.Id;

這個腳本比較複雜。 這是因為必須找到Country和Continent表中的記錄。 此腳本將缺失的記錄插入City並正確填寫ContryId 。

如果需要，也可以以相同的方式編寫UPDATE和DELETE腳本。

優點

• 您不需要任何商業產品。
• SQL 腳本可以保存到存儲過程中或作為 SQL Server 的作業定期運行。

缺點

• 創建這樣的 SQL 腳本既困難又復雜（對於每個表，通常需要三個腳本—— INSERT 、 UPDATE和DELETE ）。
• 很難維護。

使用外部工具

這種同步（寬表到許多相關表）不能用數據比較方法完成，因為它側重於不同的用例。 由於數據比較方法會生成一個帶有要插入數據的 SQL 腳本，因此它無法直接在相關表中查找引用。 因此，無法使用使用此方法的應用程序（dbForge Data Compare for SQL Server、RedGate SQL Data Compare、Apex SQL Data Diff）。

但是，SQL Database Studio 可以幫助您自動創建同步腳本。 在下圖中，SQL Database Studio 中有一個名為 Editor for Data Synchronization 的元素。

Editor 看起來像著名的 Query builder 並且以非常相似的方式工作。 每個表都需要有定義的同步鍵，但表之間也有定義的關係。 在上圖中，還有用於同步的映射。 在列列表（圖像的下部）中，有表City的列（對於其他表，它是類似的）。

列

• Id — 此列未映射，因為它是主鍵（自動生成）。
• CountryId — 此列定義為表的引用。
• 名稱- 此列從源表（寬表）中的 City 列填充。

選擇列CountryId和Name作為同步鍵。 同步鍵是一組唯一標識源表和目標表中的行的列。 您不能使用主鍵Id作為同步鍵，因為它不在源表中。

同步後，表如下所示：

在上面的示例中，有一個寬表作為源。 當源數據存儲在幾個相關的表中時，還有一個常見的場景。 SQL Database Studio 中的關係不是使用外鍵定義的，而是使用列名定義的。 這樣，也可以從 CSV 或 Excel 文件導入（文件被加載到臨時表中，並從該表運行同步）。 擁有唯一的列名是一種很好的做法。 如果這不可能，您可以為這些列定義別名。

優點

• 輕鬆快速地創建
• 易於維護
• 可以保存到存儲過程中（存儲過程與稍後在編輯器中打開同步所需的數據一起保存）

缺點

• 商業解決方案

比較解決方案

數據同步由一系列INSERT 、 UPDATE或DELETE命令組成。 有多種方法可以創建這些命令的序列。 在本文中，我們研究了創建同步 SQL 腳本的三個選項。 第一個選項是手動創建所有內容。 它是可行的（但需要太多時間），它需要對 SQL 的複雜理解，並且難以創建和維護。 第二種選擇是使用商業工具。 我們查看了以下工具：

• SQL Server 的 dbForge 數據比較
• RedGate SQL 數據比較
• Apex SQL 數據差異
• SQL 數據庫工作室

前三個工具的工作方式非常相似。 他們比較數據，讓用戶分析差異，並可以同步選擇的差異（甚至自動或從命令行）。 它們有利於這些使用場景：

• 由於各種錯誤，數據庫不同步。
• 在環境之間傳輸數據時，您需要避免複製。
• 需要 Excel 或 HTML 格式的數據比較報告。

每個工具都因某種原因而受到喜愛：dbForge 具有出色的 UI 和許多選項，ApexSQL 的性能優於其他工具，而 RedGate 是最受歡迎的。

第四個工具 SQL Database Studio 的工作方式略有不同。 它生成包含同步邏輯而不是更改的 SQL 腳本。 性能也很棒，因為所有工作都直接在數據庫服務器上完成，因此不需要在數據庫服務器和同步工具之間進行數據傳輸。 此工具適用於以下用例：

• 數據庫具有不同結構的自動數據庫遷移
• 導入多個相關表
• 從外部源導入 XML、CSV、MS Excel