DBA 常被問:「這個資料庫支援哪個應用程式?」在規劃停機通知、與非技術主管溝通時,若無對應關係,溝通成本極高。資料庫命名慣例往往無法直觀反映業務應用,因此需要一個中繼資料庫來維護「伺服器/實例 → 資料庫 → 應用程式」的對應關係。
作者最初自建中繼資料庫,使用連結伺服器與分散式查詢,運作七年但效能、安全性皆有不足。參考 Rodney Landrum 於《SQL Server Magazine》發表的 SSIS 與 DBA 知識庫架構後,採用其方案並擴充應用程式中繼資料。
CREATE TABLE [dbo].[Applications] (
[AppID] INT IDENTITY(154,1) NOT NULL,
[ApplicationName] VARCHAR(100) NOT NULL,
PRIMARY KEY (AppID)
);
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),原因:
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,以便密切監控新資料庫建立活動。