萬盛學電腦網

您现在的位置: 萬盛學電腦網 >> 程式設計 >  >> 資料庫 >> SQL Server 教學

雙資料庫即時同步方案:利用 xp_trace 追蹤 SQL 陳述式重播

需求情境

雙伺服器熱備援:主資料庫故障時,備援機修改電腦名稱即可頂替。核心挑戰在於「即時同步資料」。發行-訂閱複寫會導致訂閱端遺失觸發器、主鍵等物件,切換時仍需手動重建,操作繁瑣。

核心思路:追蹤並重播 SQL 陳述式

SQL Server 提供擴展預存程序 `xp_trace_*` 系列,可建立追蹤佇列,過濾特定資料庫、應用程式、SQL 關鍵字,擷取所有 DML/DDL 陳述式,寫入本地表,再傳送至備援伺服器執行,達到邏輯層面的即時同步。優點:支援所有資料類型 (含 Image)、保留完整結構物件。

關鍵 xp_trace 程序

  • `xp_trace_addnewqueue`:建立追蹤佇列,設定緩衝區大小、事件類別等。
  • `xp_trace_seteventclassrequired`:指定追蹤事件類別 (如 41 = SQL 批次完成)。
  • `xp_trace_setappfilter`:依應用程式名稱過濾。
  • `xp_trace_setdbidfilter`:依資料庫 ID 過濾。
  • `xp_trace_settextfilter`:依 SQL 關鍵字過濾 (如 `INSERT`、`UPDATE`)。
  • `xp_trace_setqueuedestination`:設定輸出目的地 (資料表、檔案等)。
  • `xp_trace_startconsumer`:啟動消費者執行緒寫入目的地。

BCB 5.0 (C++Builder) 實作範例

bool TForm1::BuildTrace(int DBID,
                        AnsiString AppFilter,
                        AnsiString SQLFilter,
                        AnsiString DstTable,
                        int &TraceHandle)
{
    char tempBuf[512];
    Query1->Close();
    Query1->SQL->Clear();
    TStrings *pSQL = Query1->SQL;

    pSQL->Add("USE master");
    pSQL->Add("DECLARE @queue_handle int");
    pSQL->Add("DECLARE @column_value int");
    // 事件類別位元遮罩: 67108864|1|512|1024|10384 (依需求調整)
    pSQL->Add("SET @column_value = 67108864|1|512|1024|10384");
    pSQL->Add("EXEC xp_trace_addnewqueue 1000,5,95,90,@column_value,@queue_handle OUTPUT");
    pSQL->Add("EXEC xp_trace_seteventclassrequired @queue_handle, 41, 1");

    wsprintf(tempBuf, "EXEC xp_trace_setappfilter @queue_handle, '%s', NULL", AppFilter.c_str());
    pSQL->Add(AnsiString(tempBuf));

    wsprintf(tempBuf, "EXEC xp_trace_setdbidfilter @queue_handle, %d", DBID);
    pSQL->Add(AnsiString(tempBuf));

    wsprintf(tempBuf, "EXEC xp_trace_settextfilter @queue_handle, '%s', NULL", SQLFilter.c_str());
    pSQL->Add(AnsiString(tempBuf));

    wsprintf(tempBuf,
        "EXEC xp_trace_setqueuedestination @queue_handle, 4, 1, NULL, '%s'",
        DstTable.c_str());
    pSQL->Add(AnsiString(tempBuf));

    pSQL->Add("EXEC xp_trace_startconsumer @queue_handle");
    pSQL->Add("SELECT @queue_handle QueueHandle");

    try {
        Query1->Open();
        TraceHandle = Query1->FieldByName("QueueHandle")->AsInteger;
        return true;
    } catch(...) {
        return false;
    }
}

後續處理流程

  1. 應用程式定期讀取目的地資料表 (或檔案) 中的新 SQL 陳述式。
  2. 透過連結伺服器或遠端連線,在備援資料庫執行這些陳述式。
  3. 需處理交易一致性:建議在主庫以顯式交易包裹業務邏輯,追蹤到 `BEGIN TRAN` / `COMMIT` 成對重播。
  4. 錯誤處理:重播失敗需警報、重試、人工介入。

優缺點評析

優點缺點
不依賴複寫,目標端保留完整物件`xp_trace` 為未文件化/已棄用 API,未來版本可能移除
可攔截所有 SQL (含 DDL、動態 SQL)效能開銷大,高負載系統可能影響主庫吞吐
支援 Image/TEXT 等大型資料型別需自行實作佇列讀取、重播、錯誤處理、監控機制
邏輯層面同步,不受物理結構差異影響無法保證嚴格 ACID (非同步、可能丟失、重複執行)

現代替代方案

  • Always On 可用性群組 (SQL Server 2012+):同步/非同步複寫、自動容錯移轉、可讀次要複本。
  • 資料庫鏡像 (2005-2016,已棄用但仍支援):高安全模式同步。
  • 異動複寫 / 合併複寫:成熟、可過濾、支援結構同步 (含觸發器、索引)。
  • Change Data Capture (CDC) + 自訂 ETL:擷取變更資料流,彈性高。
  • Azure SQL / Managed Instance 異地複寫:雲端原生 HA。
copyright © 萬盛學電腦網 all rights reserved