Managing SQL Server Database and Application Metadata
I am frequently asked to translate the databases that reside on a physical server / SQL instance into their corresponding application names. Typically this need arises when preparing planned downtime notifications for servers, but it also is valuable when communicating to other IT Managers or non-technical staff in my organization. Unless you are the Database Administrator or the Application Analyst for a particular database you are usually oblivious to the naming conventions of the databases that support the applications you rely upon daily. This is why it is important to have the metadata repository in place to provide that translation when the need arises.
Most DBAs have some form of database metadata repository they rely upon to keep track of their sprawling Microsoft SQL Server environment. I built the one I used in my environment over seven years ago using linked servers and distributed database calls. It was not pretty, but it was functional. Like many IT Developers and DBAs I was proud of my creation, even with its flaws. It was slow, not as streamlined as it could be, and not as secure as it should be.
Upon reading the May and June 2007 SQL Server Magazine articles on SQL Server Integration Services (SSIS) and DBA Repositories by Rodney Landrum I knew it was time to adopt someone else's solution. It was perfect for my environment and with a few modifications was easily-adoptable. In February 2008, a follow-up article was published in SQL Server Magazine in which Rodney updated his solution. I downloaded the code, vetted it in my test environment, and quickly incorporated it into production. While generally pleased with what this solution offered, one of the aspects missing from his package was the ability to correlate databases to applications. By adding two additional tables to his solution I was able to add application metadata that existed in my "home-grown" metadata repository into the one I was now using courtesy of SQL Server Magazine.
Adding application metadata to my repository consisted of creating two tables: dbo.Applications, specifically for storing the application names for all programs relying upon SQL Server databases in my environment, and dbo.Database_Applications which holds the relationship between SQL instance, database, and application.
--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 )
You may notice that I did not normalize the dbo.Database_Applications table. If I had, I would only be storing two fields: a foreign key relating back to the table storing my application metadata, and a foreign key corresponding to my database metadata. I have my reasons:
- I'm not dealing with a large amount of data: I have roughly 800 databases in my environment hosted across 80 instances. While that is a large environment for a single DBA to support, it does not translate into a great deal of records in my metadata tables nor does it convert into great sums of bytes in the repository database.
- By including the application name in the table, instead of the primary key of the dbo.Applications table I am able to generate my key Application Metadata report with a call to only the dbo.Database_Applications table.
- My environment's SQL Metadata Repository uses a "scorched earth" process of population - with the exception of SQL Agent Job History and Backup History all other tables were being truncated and re-loaded daily. I found persisting the information in the dbo.Database_Applications table made my life much easier.
After the daily load of data from my environment I am able to get a good representation of any new databases created in my environment by utilizing the following script:
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
The results for this query provide a list of any databases created since the last time I updated my application metadata and serves not only as a notice of database creation activity across the domain, but also as a list to work from for updating the two tables in that correspond to application information. This query also serves as the dataset for a SQL Server Reporting Services report that provides me with a daily notification of new databases to my Blackberry while not in the office.
Finally, I've created the following stored procedure for populating the dbo.Applications and dbo.Database_Applications tables with any new database information. It accepts three parameters: Server, Database, and Application. If the application does not already exist in the dbo.Applications table it is added. Then a record is inserted into the dbo.Database_Applications table for the Server/Database/Application relationship.
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
While I could easily incorporate the execution of this stored procedure as the final step in the SQL Server Integration Services package that populates my repository database I choose to not do so in order to keep a closer eye on the activity surrounding new database creation in my environment.
- Download the code for the DBA Metadata Repository from SQL Server Magazine's
- May 2007 article: SQL Server Integration Services: Gather Information Automatically
- June 2007 article: SQL Server Reporting Services: Create Repository Reports
- February 2008 article: Use SSRS and SSIS to Create a DBA Repository
- Add the two tables and stored procedure from this tip to start tracking your applications that rely upon Microsoft SQL Server databases.
- Review this tip from MSSQLTips.com relating to database metadata: Collecting SQL Server Database Usage Information for Free Space and Trending
About the author
View all my tips
Article Last Updated: 2008-08-19