SQL Server 資料庫與應用程式對應關係管理：建立元資料倉庫實現業務視角的資產目錄

痛點：技術名稱 vs 業務語言

DBA 看到 DB_FIN_2023、DB_HR_PROD，業務主管只關心「財務系統」、「人資系統」。溝通斷層導致：

• 計畫停機通知無法精準告知影響業務範圍
• 非技術人員無法理解資源佔用報表
• 新建資料庫未同步更新業務對應關係

解決方案：應用程式元資料倉庫

參考 Rodney Landrum《SQL Server Magazine》DBA Knowledge Base 系列，擴充兩張核心表：

表 1：應用程式主檔 dbo.Applications

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

表 2：資料庫-應用程式對應 dbo.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)
);

設計取捨：環境約 800 DB / 80 實例，資料量小，反正規化換取查詢簡潔（無需 JOIN 即得業務名稱）。

自動化發現新資料庫

每日元資料同步後，執行：

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 報表，每日推送至管理者
• 觸發郵件/Teams 通知 DBA 確認歸屬
• 作為合規稽核依據

維護存儲程序：dbo.pAdd_Application

CREATE PROCEDURE dbo.pAdd_Application
  @ServerName VARCHAR(50),
  @DatabaseName VARCHAR(100),
  @ApplicationName VARCHAR(100)
AS
BEGIN
  -- 更新現有記錄的業務名稱
  UPDATE dbo.Database_Applications
  SET ApplicationName = @ApplicationName
  WHERE ServerName = @ServerName
    AND DatabaseName = @DatabaseName
    AND ApplicationName IS NULL;

  -- 新應用程式寫入主檔
  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

整合建議

• 可納入 SSIS 套件最後一步自動執行，但作者選擇手動觸發以保持對新資料庫創建活動的感知
• 搭配 sp_help_revlogin 同步登入 SID，構建完整「伺服器-資料庫-登入-應用」四維元資料模型
• 擴充欄位：Environment (Prod/UAT/Dev)、Criticality (P1/P2/P3)、OwnerTeam、RPO/RTO

價值總結

從「管理資料庫」進化到「管理業務資產」，讓 DBA 與業務、管理層說同一種語言，提升溝通效率與決策品質。