Managing SQL Server Database and Application Metadata

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

Problem

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.

Solution

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.

Next Steps

Download the code for the DBA Metadata Repository from SQL Server Magazine's website.
- 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

Tim Ford is a Senior Database Administrator with MindBody.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips

Article Last Updated: 2008-08-19

Comments For This Article

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

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

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
go
if not object_id('dbo.sp_DBA_ConnectionTracker') is null
begin
drop procedure dbo.sp_DBA_ConnectionTracker
end
go
Create procedure dbo.sp_DBA_ConnectionTracker
as
begin
/* will be executed regularly by a sqlagent job */
Set nocount ON

if object_id('dbo.T_DBA_ConnectionTracker') is null
begin
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]);
end

/* 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

*/
end

/* Install corresponding job */
BEGIN TRANSACTION
DECLARE @JobID BINARY(16)
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.'
ELSE
BEGIN

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

END
COMMIT TRANSACTION
GOTO EndSave
QuitWithRollback:
IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION
EndSave: