Learn more about SQL Server tools

mssqltips logo

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories


Managing SQL Server Database and Application Metadata

By:   |   Last Updated: 2008-08-19   |   Comments (2)   |   Related Tips: More > Database Administration


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] 
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) 

--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 
   INSERT INTO dbo.Applications (ApplicationName)  
   VALUES (@ApplicationName) 
   PRINT 'Added new Application:  ' + @ApplicationName + ' to Applications table' 
   SELECT * FROM dbo.Applications WHERE ApplicationName = @ApplicationName 

--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.

Next Steps

Last Updated: 2008-08-19

next webcast button

next tip button

About the author
MSSQLTips author Tim Ford Tim Ford is a Senior Database Administrator with MindBody.

View all my tips
Related Resources

Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Email me updates 

Signup for our newsletter
 I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.


Wednesday, August 27, 2008 - 8:18:24 AM - jubdavis Back To Top

What parameters are needed to execute the pAddApplication proc?


It's looking for ServerName, DatabaseName and ApplicationName.

Wednesday, August 20, 2008 - 12:19:47 AM - ALZDBA Back To Top

Are you also challenging your metadata to the actual usage ?

(by sampling sysprocesses (sql2000) or using a login trigger on sql2005/2008)

I'm using this proc on sql2000 for license checking and connection tracking:

/* ALZDBA dd 20080715
  Inventory active connections for License checking.
use master
if not object_id('dbo.sp_DBA_ConnectionTracker') is null
   drop procedure dbo.sp_DBA_ConnectionTracker
Create procedure dbo.sp_DBA_ConnectionTracker
/* will be executed regularly by a sqlagent job */
Set nocount ON

if object_id('dbo.T_DBA_ConnectionTracker') is null
 print 'Table [T_DBA_ConnectionTracker] Created';
 CREATE TABLE [dbo].[T_DBA_ConnectionTracker](
  [hostname] [varchar](128) NOT NULL,
  [program_name] [varchar](128) NOT NULL,
  [nt_domain] [varchar](128) NOT NULL,
  [nt_username] [varchar](128) NOT NULL,
  [loginame] [varchar](128) NOT NULL,
  [dbid] smallint not null,
  [tsRegistration] datetime not null default getdate(),
  [tsLastUpdate] datetime not null default getdate()
   ) ;
 Create clustered index clX_DBA_ConnectionTracker on [dbo].[T_DBA_ConnectionTracker] ([tsRegistration]);
 Create index X_DBA_ConnectionTracker on [dbo].[T_DBA_ConnectionTracker] ([loginame]);

/* Update registered connections */
update T
 Set [tsLastUpdate] = getdate()
from T_DBA_ConnectionTracker T
inner join sysprocesses P
 on  P.hostname     = T.hostname
 and P.program_name = T.program_name
 and P.nt_domain    = T.nt_domain
 and P.nt_username  = T.nt_username
 and P.loginame     = T.loginame
 and P.dbid         = T.dbid
 and P.loginame not like '%\ServiceSQL%'; -- exclude SQLServer service accounts
/* Register new connections */
insert into T_DBA_ConnectionTracker (hostname, program_name, nt_domain, nt_username, loginame, dbid)
Select distinct rtrim(P.hostname), rtrim(P.program_name), rtrim(P.nt_domain), rtrim(P.nt_username), rtrim(P.loginame), isnull(dbid,-1)
from sysprocesses P
Where not exists ( Select *
     from T_DBA_ConnectionTracker T
       Where  P.hostname     = T.hostname
       and P.program_name = T.program_name
       and P.nt_domain    = T.nt_domain
       and P.nt_username  = T.nt_username
       and P.loginame     = T.loginame
       and P.dbid         = T.dbid ) ;


 Select *
 from dbo.T_DBA_ConnectionTracker
 Order by hostname, program_name, nt_domain, nt_username, loginame



/* Install corresponding job */
  DECLARE @ReturnCode INT   
  SELECT @ReturnCode = 0    
IF (SELECT COUNT(*) FROM msdb.dbo.syscategories WHERE name = N'[Uncategorized (Local)]') < 1
  EXECUTE msdb.dbo.sp_add_category @name = N'[Uncategorized (Local)]'
IF (SELECT COUNT(*) FROM msdb.dbo.sysjobs WHERE name = N'DBA_ConnectionTracker') > 0
  PRINT N'The job "DBA_ConnectionTracker" already exists so will not be replaced.'

  -- Add the job
  EXECUTE @ReturnCode = msdb.dbo.sp_add_job @job_id = @JobID OUTPUT , @job_name = N'DBA_ConnectionTracker', @owner_login_name = N'sa', @description = N'To support Connection Inventory for License check.', @category_name = N'[Uncategorized (Local)]', @enabled = 1, @notify_level_email = 0, @notify_level_page = 0, @notify_level_netsend = 0, @notify_level_eventlog = 2, @delete_level= 0
  IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

  -- Add the job steps
  EXECUTE @ReturnCode = msdb.dbo.sp_add_jobstep @job_id = @JobID, @step_id = 1, @step_name = N'ConnectionTracker', @command = N'exec sp_DBA_ConnectionTracker', @database_name = N'master', @server = N'', @database_user_name = N'', @subsystem = N'TSQL', @cmdexec_success_code = 0, @flags = 0, @retry_attempts = 0, @retry_interval = 1, @output_file_name = N'', @on_success_step_id = 0, @on_success_action = 1, @on_fail_step_id = 0, @on_fail_action = 2
  IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
  EXECUTE @ReturnCode = msdb.dbo.sp_update_job @job_id = @JobID, @start_step_id = 1

  IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

  -- Add the job schedules
  EXECUTE @ReturnCode = msdb.dbo.sp_add_jobschedule @job_id = @JobID, @name = N'EveryMinute', @enabled = 1, @freq_type = 4, @active_start_date = 20080715, @active_start_time = 0, @freq_interval = 1, @freq_subday_type = 4, @freq_subday_interval = 1, @freq_relative_interval = 0, @freq_recurrence_factor = 0, @active_end_date = 99991231, @active_end_time = 235959
  IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

  -- Add the Target Servers
  EXECUTE @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @JobID, @server_name = N'(local)'
  IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

GOTO   EndSave             

Learn more about SQL Server tools