建立 SQL Server 資料庫與應用程式中繼資料映射庫：從命名混亂到業務可視

痛點：資料庫名稱 ≠ 業務系統名稱

在擁有數百個資料庫、數十個實例的企業環境中，DBA 面臨的共同困境：DB_FIN_001、CRM_PROD_V2、ERP_2018_Q3……命名規範五花八門，非技術人員 (IT 經理、稽核、業務主管) 完全無法對應至實際業務系統 (如「財務總帳」、「客服 CRM」、「供應鏈 ERP」)。計畫性停機通知、異動影響分析、資產盤點皆因此受阻。

解決方案：輕量級中繼資料庫 (Metadata Repository)

參考 Rodney Landrum (SQL Server Magazine 2007-2008) 的 SSIS 架構，並針對「資料庫-應用程式映射」缺口擴充兩張核心表，建立一個功能完備、維護成本低的中繼資料中樞。

核心資料模型 (兩張表)

1. Applications 應用程式主檔

CREATE TABLE [dbo].[Applications] (
    [AppID]           INT IDENTITY(154,1) NOT NULL PRIMARY KEY,
    [ApplicationName] VARCHAR(100) NOT NULL UNIQUE
);

• 集中管理所有仰賴 SQL Server 的業務系統名稱。
• 起始 Identity 154 為歷史資料保留空間。

2. Database_Applications 資料庫-應用程式關聯表

CREATE TABLE [dbo].[Database_Applications] (
    [DB_AppID]      INT IDENTITY(1,1) NOT NULL PRIMARY KEY,
    [ServerName]    VARCHAR(50) NOT NULL,
    [DatabaseName]  VARCHAR(100) NOT NULL,
    [ApplicationName] VARCHAR(100) NULL,  -- 故意非正規化，存名稱而非 FK
    CONSTRAINT UQ_Server_DB UNIQUE (ServerName, DatabaseName)
);

設計決策解析：為何選擇「非正規化」？

• 報表便利性： 單表查詢即可產出「關鍵應用程式中繼資料報表」，無需 JOIN，極大簡化 SSRS/Power BI 報表模型。
• 資料重載策略： 採「焦土政策」 (Scorched Earth) —— 除 SQL Agent Job History、Backup History 外，每日全量 Truncate & Reload。Database_Applications 保留人工維護的映射關係，避免每日重載遺失業務對應。
• 彈性： 允許 ApplicationName 為 NULL (尚未歸檔的新資料庫)，支援增發現流程。

自動化蒐集架構 (SSIS + Linked Servers)

1. 中央中繼資料庫實例： 部署單一 SQL Server 實例專職存放中繼資料。
2. Linked Servers： 建立至所有 80 個生產實例的連結伺服器。
3. SSIS 套件每日執行：
   • 並行蒐集各實例 sys.databases、sys.servers、備份歷史、作業歷史等。
   • 合併寫入中央庫核心表 (採 MERGE/UPSERT 模式)。
   • Database_Applications 僅更新 ServerName/DatabaseName 欄位，保留既有 ApplicationName。
4. 人工歸檔介面： 簡單的 Web/WinForm 讓 DBA 對照清單填寫/確認 ApplicationName。

價值實證

• 停機通知精準度 100%： 依 ApplicationName 發送給對應業務主管，不再抄送全體 IT。
• 稽核查核時間縮減 80%： 直接匯出「系統-資料庫-實例-備份狀態」矩陣報表。
• 新資料庫上線檢核： 部署腳本強制要求登錄映射，納入 CI/CD Gate。

擴充建議

• 加入 Environment (PROD/UAT/DEV)、Criticality (P1/P2/P3)、DataOwner、RecoveryModel 等治理屬性。
• 整合 sys.dm_db_index_usage_stats 監控閒置資料庫，輔助去委決策。
• 輸出至 CMDB (Configuration Management Database) 或 Data Catalog (如 Azure Purview)。