浅谈SQL Server元数据的管理与应用

大部分数据库管理员拥有某种形式的数据库元数据库，他们依赖其来跟踪范围很广的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 
)

我没有处理大量的数据：我有大概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

最后，我创建了以下存储程序，由此用任何新的数据库信息来合并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)程序包中的最后一步，而这个程序包能够组装我的存储数据库，但我选择不这样做，这是为了在我的环境里，我能密切关注围绕新的数据库创造而展开的活动。