SQL Server後設資料的管理與應用

iSQlServer發表於2009-03-31
SQLServer

大部分資料庫管理員擁有某種形式的資料庫後設資料庫，他們依賴其來跟蹤範圍很廣的Microsoft SQL Server環境。我利用連線的伺服器和分散式資料庫訪問來建立一個已經在我的環境中使用了七年的後設資料庫。它不是漂亮的，但它是功能性很強的。跟很多IT開發者和資料庫管理員一樣，即使它有自身的不足我還是為自己的創造感到驕傲。它很慢，不像它可以的那樣最新型，也不像它應該的那樣安全。 

自從讀了2007年5月和6月Rodney Landrum在SQL Server雜誌上發表的關於SQL Server整合服務(SSIS)和資料庫管理員知識庫(DBA Repositories)的文章，我知道是時候採取別人的解決方法了。這對於我的環境來說是完美的，而一些改動也是容易採納的。2008年2月，一篇後續文章在SQL Server雜誌上發表，在這篇文章裡，Rodney更新了他的解決方法。我下載了程式碼，在我的測試環境裡稽核，並迅速把它納入產品中。當大家普遍地為這個解決方法所提供的而感到高興時，在它包中缺少的一方面是把資料庫關聯到應用程式的能力。通過在他的解決方法中增加兩張額外的表，我可以在我的“土生土長”後設資料庫中增加應用程式後設資料到我現在使用的SQL Server雜誌的方法中。

增加到我資料庫中的應用後設資料包括建立兩張表：dbo.Applications，專為儲存所有程式的應用名稱，而這些程式在我的環境中依賴於SQL Server資料庫，還有

dbo.Database_Applications，它儲存SQL 例項、資料庫和應用程式之間的關係。

Applications Table　
CREATE TABLE [dbo].[Applications] 
( 
[AppID] [int] IDENTITY(154,1) NOT NULL, 
[ApplicationName] [varchar](100) NOT NULL, 
)
Database_Applications Table　
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 
)

你可能注意到，我沒有規範化dbo.Database_Applications表。如果我規範化，我會只儲存兩個區域：一個與儲存我的應用後設資料的表有關的外來鍵，和一個與我的後設資料庫相對應的外來鍵。我有自己的原因：

我沒有處理大量的資料：我有大概800個資料庫，這些資料庫在我的環境裡釋出80個例項。雖然這對於一個資料庫管理員來說是個很大的環境，但是它既不轉變成在我的後設資料表裡的大量紀錄，也不轉變成資料庫的巨大位元組。

不是通過dbo.Applications表的主鍵，而是包含表中的應用程式名，我可以通過只訪問dbo.Database_Applications表產生我的主要應用程式後設資料報告(key Application Metadata report)。

我的環境中的SQL後設資料庫使用“焦土政策”人口處理方法，除了SQL Agent Job History和Backup History，其他的表都被每天刪除和重新載入。我發現在dbo.Database_Applications表中儲存資訊可以使我的生活變得很容易。每日從我的環境中載入資料後，我可以通過以下指令碼得到在我的環境中產生的任何新的資料庫的良好的陳述。

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

這個查詢的結果提供任何資料庫的清單，這些資料庫產生於上次我更新應用後設資料和伺服器時，它不僅是跨域的資料庫建立活動的通知，也是致力於更新兩個資料庫來符合應用程式資訊的資料清單。這個查詢也適合SQL Server Reporting Services報告的資料表，而當我不在辦公室時，SQL Server Reporting Services報告也為我提供了一個新的資料庫到我的黑莓(BlackBerry)的日常通知。

最後，我建立了以下儲存程式，由此用任何新的資料庫資訊來合併dbo.Applications表和dbo.Database_Applications 表。它接受三個引數：伺服器，資料庫和應用程式。如果應用程式已經不存在於dbo.Applications表中，它就會被補充。然後一個記錄被插入到伺服器/資料庫/應用程式關係中的dbo.Applications表。 


CREATE PROCEDURE [dbo].[pAdd_Application] @ServerName varchar(50),
@DatabaseName varchar(100), @ApplicationName varchar(100) 
AS 
--Add any new databases created, but not recorded in the repository, to the repository 
UPDATE dbo.Database_Applications 
SET ApplicationName = @ApplicationName 
WHERE ServerName = @ServerName 
AND DatabaseName = @DatabaseName 
AND ApplicationName IS NULL 
--Determine if there is already an application for this database in the repository, 
if not, then add it 
IF (SELECT COUNT(*) FROM dbo.Applications WHERE ApplicationName = @ApplicationName) = 0  
BEGIN  
INSERT INTO dbo.Applications (ApplicationName)  
VALUES (@ApplicationName)  
PRINT 'Added new Application: ' + @ApplicationName + ' to Applications table'  
SELECT * FROM dbo.Applications WHERE ApplicationName = @ApplicationName  
END  
--List the new record in the repository  
SELECT ServerName, DatabaseName, ApplicationName  
FROM dbo.Database_Applications  
WHERE ServerName = @ServerName  
AND DatabaseName = @DatabaseName  
AND ApplicationName = @ApplicationName

雖然我可以很容易地把這個儲存程式的執行整合為SQL Server整合服務(SSIS)程式包中的最後一步，而這個程式包能夠組裝我的儲存資料庫，但我選擇不這樣做，這是為了在我的環境裡，我能密切關注圍繞新的資料庫創造而展開的活動。

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/16436858/viewspace-582432/，如需轉載，請註明出處，否則將追究法律責任。

相關文章