萬盛學電腦網

您现在的位置: 萬盛學電腦網 >> 程式設計 >  >> 資料庫 >> MS SQL 資料庫

建立 SQL Server 中繼資料庫:將資料庫對應至應用程式名稱

背景動機

DBA 常被問:「這個資料庫支援哪個應用程式?」在規劃停機通知、與非技術主管溝通時,若無對應關係,溝通成本極高。資料庫命名慣例往往無法直觀反映業務應用,因此需要一個中繼資料庫來維護「伺服器/實例 → 資料庫 → 應用程式」的對應關係。

解決方案演進

作者最初自建中繼資料庫,使用連結伺服器與分散式查詢,運作七年但效能、安全性皆有不足。參考 Rodney Landrum 於《SQL Server Magazine》發表的 SSIS 與 DBA 知識庫架構後,採用其方案並擴充應用程式中繼資料。

核心資料表設計

Applications 表:儲存所有依賴 SQL Server 的應用程式名稱

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

Database_Applications 表:維護伺服器、資料庫、應用程式三方關係

CREATE TABLE [dbo].[Database_Applications] (
    [DB_AppID] INT IDENTITY(1,1) NOT NULL,
    [ServerName] VARCHAR(50) NOT NULL,
    [DatabaseName] VARCHAR(100) NOT NULL,
    [ApplicationName] VARCHAR(100) NULL,
    PRIMARY KEY (DB_AppID)
);

設計取捨: 未正規化(未建立 FK 指向 Applications),原因:

  • 資料量小(約 800 資料庫、80 實例),非正規化不影響效能。
  • 直接查詢 Database_Applications 即可產生主要報表,無需 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 報表,並透過訂閱推送至行動裝置。

自動同步預存程序

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,以便密切監控新資料庫建立活動。

copyright © 萬盛學電腦網 all rights reserved