SQL Server 2008 資料庫同步方案深度解析：Linked Server 與 SQL Agent Job 實作

同步架構概覽

跨伺服器資料同步常見做法為：建立 Linked Server 連結遠端資料庫，再透過 SQL Server Agent Job 排程執行同步邏輯。本文詳細說明 Linked Server 的建立/移除指令、參數意義，以及 Job 逐步建立流程。

一、Linked Server 核心系統預存程序

1. 移除連結伺服器登入對應：sp_droplinkedsrvlogin

EXEC sp_droplinkedsrvlogin 'DBLink', NULL;

參數說明：

• @rmtsrvname：連結伺服器名稱。
• @locallogin：本地登入帳號，NULL 表示所有登入。

2. 移除連結伺服器：sp_dropserver

EXEC sp_dropserver 'DBLink', 'droplogins';

droplogins 參數會一併刪除相關登入對應。

3. 建立連結伺服器：sp_addlinkedserver

EXEC sp_addlinkedserver
    @server = 'DBLink',
    @srvproduct = '',
    @provider = 'SQLOLEDB',
    @datasrc = '10.0.222.12';

關鍵參數：

• @provider：OLE DB 提供者（SQL Server 使用 SQLOLEDB）。
• @datasrc：遠端伺服器 IP 或名稱，可加執行個體名 Server\Instance。
• @catalog：預設資料庫名稱（選填）。

4. 建立登入對應：sp_addlinkedsrvlogin

EXEC sp_addlinkedsrvlogin
    @rmtsrvname = 'DBLink',
    @useself = 'false',
    @locallogin = NULL,
    @rmtuser = 'ILM',
    @rmtpassword = '111111';

@useself = 'false' 表示使用指定的遠端帳密；@locallogin = NULL 套用至所有本地登入。

二、SQL Agent Job 同步作業建立步驟

1. 展開 SQL Server Agent → 右鍵 作業 → 新增作業。
2. 一般 頁面：輸入作業名稱、擁有者、類別、描述。
3. 步驟 頁面：新增步驟
   • 步驟 1：建立/驗證 Linked Server 連線（執行上述 sp_addlinkedserver 等指令）。
   • 步驟 2：執行實際同步 T-SQL（如 MERGE、INSERT/UPDATE/DELETE 配合 OPENQUERY 或四段式名稱）。
4. 排程 頁面：新增排程，設定頻率（每日、每週、每月）、間隔、生效日期。
5. 警示/通知：依需求設定失敗郵件通知。
6. 完成後，作業即會依排程自動執行；亦可右鍵 啟動作業 立即測試。

三、實務建議與注意事項

• Linked Server 連線資訊建議集中管理，避免硬編碼在多個作業中。
• 同步邏輯請採用 MERGE 或 UPSERT 模式，確保冪等性。
• 大量資料同步時，考慮分批次、啟用 BULK INSERT 或 SqlBulkCopy 提升效能。
• 監控作業歷程，設定失敗重試次數與間隔。
• 生產環境務必先在測試環境驗證同步邏輯與效能。