SQL Server 2005 BI 專案實戰心得：磁碟、架構、版本、備份與網路五大避坑指南

前言：BI 專案的隱形冰山

SQL Server 2005 Integration Services (IS) 與 Analysis Services (AS) 專案，處理的是百萬級甚至億級資料量，ETL 執行時間以小時計算。一個微小的基礎設施決策失誤，可能導致任務在 99% 進度時因磁碟滿而失敗、或因記憶體洩漏導致服務掛起。本文提煉實戰五大核心教訓，助您避開「低級但致命」的坑。

一、C 盤空間：不只是作業系統的家

1. 實戰慘案

• 建議 30GB C 盤，實際安裝 Win2003 + VS2005 + SQL2005 + Office 佔 15GB，「似乎夠用」
• IS 執行快速載入 (Fast Load) 產生大量臨時檔案寫入 `%TEMP%` (預設 C 盤)
• 導入 2000 萬筆資料，前 1999 萬順利，最後 1 萬因 C 盤剩餘 < 100MB 而失敗
• 被迫中途停機、重新分區、重裝環境，損失數天工期

2. 根因分析

• IS `Fast Load` / `BULK INSERT` / `BCP` 皆依賴暫存目錄 (`TEMP`/`TMP` 環境變數)
• AS 處理維度/量測聚合時，中間結果可能溢寫至磁碟
• SQL Server 錯誤日誌、備份檔、追蹤檔預設皆落 C 盤

3. 解決方案

二、多維架構設計：一個錯誤的維度表毀掉整個 Cube

1. 實戰慘案

• Cube 處理卡在某分區 2+ 小時無進展，被迫終止
• 檢查發現：事實表同時被設計為維度表，聚合設計產生笛卡兒積爆炸
• 重新設計 Cube (移除錯誤維度、正確關聯) → 處理時間 2小時 → 5 分鐘

2. 核心原則

• 事實表 ≠ 維度表：事實表存業務度量 (可加性)，維度表存描述屬性 (可切切)
• 星狀結構優於雪花：維度適度反正規化，減少 JOIN、利於聚合索引
• 聚合設計：依查詢模式設計聚合，避免全維度交叉聚合
• 分區策略：依時間/業務鍵分區，支援增量處理、平行處理

3. 團隊審查機制

• Cube 設計評審會議：架構師 + DBA + 業務分析師共同把關
• 檢查清單：維度鍵唯一性、屬性關聯正確性、聚合設計合理性、分區對齊
• 實習生/初級成員產出必經資深審查 (曾遇 5-6 層 View 套疊導致效能災難)

三、SQL Server 版本差異：開發版 vs 企業版的記憶體行為

1. 觀察現象

• 開發版：處理完資料後記憶體持續佔用，不釋放，需重啟服務
• 企業版：處理完記憶體乾淨釋放，「戰場打掃乾乾淨淨」

2. 技術背景

• 企業版支援 鎖定頁面記憶體 (Locked Pages in Memory)、資源調控器、大頁面支援
• 開發版功能相同但授權限制，記憶體管理積極性較低
• 官方文件未明確說明此差異，但多位 MVP 與微軟工程師私下確認

3. 建議

生產/預發/效能測試環境務必使用企業版授權。 開發版僅限開發人員本機開發測試。版本差異導致的效能不可重現，是最浪費排查時間的坑。

四、備份與高可用：單點故障的代價

1. 實戰慘案

• ETL、Cube 處理、應用程式全跑在單一實體機
• 無備份、無備援機器 (理由：資料量大、流程複雜)
• 機器執行中突然無回應 (非藍屏、非重啟、純凍結)
• 重啟進入登入介面後卡死 1 小時 → 判定為 SQL Server 執行資料庫修復 (一致性檢查)
• 團隊深夜加班白等 1 小時，任務嚴重延期

2. 架構重構

3. 關鍵認知

• 備份不是目的，可還原才是目的：定期演練還原、驗證 RTO
• 單點 = 風險：任何關鍵路徑單點都要有 Plan B
• 串行任務 = 風險放大器：拆解為可平行、可獨立重試的工作單元

五、網路環境：不可控的外部攻擊面

1. 實戰慘案

• 企業內網管理鬆散，未打補丁機器成「肉雞」掃描攻擊內網
• 兩台協同處理資料的資料庫伺服器突然無法互訪 (防火牆/ARP 攻擊/廣播風暴)
• 任務中斷，排查網路耗時數小時

2. 防禦體系

• 隔離區域：資料庫叢集部署於專屬 VLAN/子網，前置防火牆
• 僅開放必要埠：TCP 1433 (或自訂) 僅對應用伺服器 IP 開放
• 主動防護：IPS/IDS、端點防護 (EDR)、定期掃描修補
• 熔斷機制：發現異常流量，一鍵斷開上游連結 (拔路由器網線/關閉防火牆規則)
• 管理通道隔離：RDP/SSH 走獨立管理網段，跳板機堡壘機審計

六、硬體配置：AS/IS 的真實需求

七、總結：從「救火」到「防火」

BI 專案的成敗，往往不在複雜的 MDX/ETL 邏輯，而在基礎設施的五大支柱：

1. 磁碟：給足空間、分離 I/O、監控告警
2. 架構：星狀模型、正確維度、聚合設計、分區對齊
3. 版本：企業版授權、功能對齊、效能基準一致
4. 備份：多層備份、異地離線、定期演練、任務解耦
5. 網路：隔離、最小開放、主動防護、熔斷預案

每一個最小處理單元可能耗時 1 小時。任何環節出錯 = 白白浪費 1 小時 + 排查時間。前置投入 1 天基礎設施驗收，換來專案期 0 意外中斷，ROI 無可估量。