Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Scripts          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

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


Last Updated: 2008-08-19


get scripts

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


go

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


Learn more about SQL Server tools