比較五種異構資料庫同步方案,推薦使用 SQL Server 2005+ 內建異質複寫將 Oracle 資料同步至 SQL Server,應用端僅連 SQL Server,享有高查詢效能、多表 JOIN、無需安裝 Oracle Client、Oracle 故障不影響讀取等優勢。
五大方案比較
| 方案 | 優點 | 缺點 |
| 1. 雙連線各自操作 | 簡單 | 無法跨庫 JOIN、每台機需裝 Oracle Client |
| 2. 同義詞查詢 Oracle | 可跨庫 JOIN、免裝 Client | 效率極低 (拉全表再篩選)、Oracle 掛掉全掛 |
| 3. 直連 Oracle + 同義詞備份 + 異常切換 | 平常高效、故障可用備份 | 程式複雜、同步非即時 |
| 4. 自寫同步服務 | 完全控制 | 開發難度高、維護成本大 |
| 5. SQL Server 異質複寫 (推薦) | 零代碼、高效能 JOIN、免 Client、Oracle 故障不影響讀取 | 資料非即時 (依快照排程) |
實作步驟 (SQL Server 2005/2008/2012 通用)
- 安裝複寫元件:SQL Server 安裝精靈勾選「複寫」。
- Oracle 端建立複寫帳號:授予
CREATE PUBLIC SYNONYM、DROP PUBLIC SYNONYM、CREATE PROCEDURE、CREATE SEQUENCE、CREATE SESSION、CREATE ANY TRIGGER、CREATE TABLE、CREATE VIEW、表空間 UNLIMITED QUOTA、目標表 SELECT 權限。
- SQL Server 機器安裝 Oracle Client 10g+(建議 10g 即使 Oracle 是 9i),重啟 SQL Server 服務。
- 設定 SQL Server Agent 服務帳號:建立 Windows 帳號加入本機管理員,設定 Agent 服務以此帳號啟動。
- 設定散發者:SSMS → 複寫 → 設定散發 → 指定快照資料夾共享路徑。
- 新增 Oracle 發行集:右鍵「本地發行集」→ 新增 Oracle 發行集 → 輸入 Oracle 連線資訊 → 選「快照式發行」 → 勾選目標表 → 勾選「立即建立快照」與「排程快照代理程式」 → 快照代理程式安全性選「代理程式服務帳號」 → 命名 (如 Test1)。
- 新增訂閱:右鍵「本地訂閱」→ 新增訂閱 → 選 Test1 → 推送訂閱 → 目標資料庫 (如 MIS) → 代理程式排程「持續執行」 → 初始化「立即」 → 完成。
- 驗證同步:在 Oracle 異動資料,等待排程週期 (如 1 分鐘) 後查詢 SQL Server 目標表確認同步。
監控與維護
- SSMS → 本地發行集 → Test1 → 右鍵「檢視代理程式作業狀態」監控快照/散發代理程式執行情況。
- 快照資料夾需給 Agent 服務帳號讀寫權限。
- 大量資料初始化建議離峰時段執行。