SQL Server 資料庫與應用程式對應關係的元資料管理實踐

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

DBA 面對數百個資料庫時，常被問：「DB_FIN_01 到底支援哪個應用系統？」若無元資料對應表，溝通成本極高，尤其在計畫停機通知、異動影響分析、非技術主管報告場景。

解決方案：建立輕量級元資料庫

作者利用 連結伺服器 + 分散式查詢 建立中央元資料庫，運行 7 年穩定。後參考 Rodney Landrum (SQL Server Magazine) 的 SSIS + DBA Repository 方案重構，並補強「資料庫-應用程式」對應關係。

核心結構：兩張表

-- 應用程式主檔
CREATE TABLE dbo.Applications (
    AppID INT IDENTITY(154,1) PRIMARY KEY,
    ApplicationName VARCHAR(100) NOT NULL
);

-- 資料庫-實例-應用程式 對應關係
CREATE TABLE dbo.Database_Applications (
    DB_AppID INT IDENTITY(1,1) PRIMARY KEY,
    ServerName VARCHAR(50) NOT NULL,
    DatabaseName VARCHAR(100) NOT NULL,
    ApplicationName VARCHAR(100) NULL  -- 故意非正規化，存名稱而非 FK
);

非正規化設計理由

1. 資料量小 (~800 DB, 80 實例)，非正規化無效能壓力。
2. 單表即可產出核心報表，免 JOIN Applications。
3. 採「焦土政策」每日全量重載 (除 Job/備份歷史)，保留 ApplicationName 免除重載時 FK 維護。

自動化發現新資料庫腳本

SELECT D.[Server], D.DatabaseName
FROM dbo.Databases D
LEFT JOIN dbo.Database_Applications DA
  ON D.DatabaseName = DA.DatabaseName AND D.[Server] = DA.ServerName
WHERE DA.DB_AppID IS NULL
ORDER BY D.[Server], D.DatabaseName;

• 結果即為「上次同步後新增但未登記應用名稱的資料庫」。
• 可做成 SSRS 報表 → 訂閱推送至 BlackBerry/郵件，DBA 不在辦公室也能掌握新資料庫動態。

合併新資料庫對應關係的預存程序

CREATE PROCEDURE dbo.pAdd_Application
    @ServerName VARCHAR(50),
    @DatabaseName VARCHAR(100),
    @ApplicationName VARCHAR(100)
AS
BEGIN
    -- 更新已存在但 ApplicationName 為 NULL 的記錄
    UPDATE dbo.Database_Applications
    SET ApplicationName = @ApplicationName
    WHERE ServerName = @ServerName
      AND DatabaseName = @DatabaseName
      AND ApplicationName IS NULL;

    -- 若 Applications 無此應用名稱則新增
    IF NOT EXISTS (SELECT 1 FROM dbo.Applications WHERE ApplicationName = @ApplicationName)
    BEGIN
        INSERT INTO dbo.Applications (ApplicationName) VALUES (@ApplicationName);
        PRINT 'Added new Application: ' + @ApplicationName;
    END

    -- 回傳最新對應記錄
    SELECT ServerName, DatabaseName, ApplicationName
    FROM dbo.Database_Applications
    WHERE ServerName = @ServerName
      AND DatabaseName = @DatabaseName
      AND ApplicationName = @ApplicationName;
END;

運維建議

• 將 pAdd_Application 整合進 SSIS 套件最後一步，實現全自動同步。
• 保留人工覆核機制：DBA 確認新資料庫業務歸屬後再執行，避免誤標。
• 擴充欄位：Environment (Prod/UAT/Dev)、Owner、Criticality、RPO/RTO 等治理屬性。

元資料管理不是寫文檔，而是建立可查詢、可自動化、可整合報表的資料資產。從兩張表開始，解決「資料庫屬於誰」的原始問題。