SQL Server 大量匯入避免鏡像 SUSPEND：四大客戶端工具最佳參數設定

背景

阿里雲 RDS SQL Server 主備架構下，大量匯入若未正確指定參數，會觸發未修復的 Bug 導致鏡像狀態變為 SUSPEND。核心在於：最小日誌記錄需滿足「表鎖 (TABLOCK)」與「無複寫」條件，並顯式啟用 CHECK_CONSTRAINTS 提示。

四大工具正確參數

1. ADO.NET SqlBulkCopy

using (var blkcpy = new SqlBulkCopy(destConnString, SqlBulkCopyOptions.CheckConstraints))
{
    blkcpy.BatchSize = 2000;
    blkcpy.BulkCopyTimeout = 5000;
    blkcpy.DestinationTableName = "Person";
    blkcpy.WriteToServer(dt);
}

關鍵：SqlBulkCopyOptions.CheckConstraints（對應 CHECK_CONSTRAINTS 提示）。

2. JDBC SQLServerBulkCopy

SQLServerBulkCopyOptions copyOptions = new SQLServerBulkCopyOptions();
copyOptions.setCheckConstraints(true);  // 必須設為 true
SQLServerBulkCopy bulkCopy = new SQLServerBulkCopy(conn, copyOptions);

3. SSIS / DTS 匯入導出

• 「進階」→ 勾選 CHECK_CONSTRAINTS。
• 或儲存 SSIS 封裝 → 修改連線管理員屬性 CheckConstraints=True。

4. BCP 命令列

REM 1. 匯出
BCP testdb.dbo.person OUT "bcp_data" /t /N /U **** /P *** /S "***.sqlserver.rds.aliyuncs.com,3433"

REM 2. 匯入（關鍵 /h "CHECK_CONSTRAINTS"）
BCP testdb.dbo.person IN "bcp_data" /C /N /q /k /h "CHECK_CONSTRAINTS" /U *** /P *** /b 500 /S "***.sqlserver.rds.aliyuncs.com,3433"

5. BULK INSERT (僅適用可存取檔案系統的自建實例)

BULK INSERT testdb.dbo.person_in FROM N'D:\trace\bcp.txt' WITH (CHECK_CONSTRAINTS);

為什麼要 CHECK_CONSTRAINTS？

預設大量匯入忽略 CHECK 約束以提速，但 RDS 鏡像機制要求匯入資料經過約束驗證，否則主備資料不一致觸發 SUSPEND。顯式啟用後：

• 仍可獲得最小日誌記錄（若滿足 TABLOCK、簡單/大量記錄復原模式）。
• 資料完整性受保護，鏡像同步正常。

檢查清單

1. 目標資料庫復原模式設為 SIMPLE 或 BULK_LOGGED。
2. 匯入命令包含 TABLOCK（BCP /h "TABLOCK"、SqlBulkCopy TableLock 選項、BULK INSERT TABLOCK）。
3. 目標表無複寫發佈。
4. 所有工具均顯式啟用 CHECK_CONSTRAINTS。