DBA Repository – Find the Databases, Part 1

In a previous entry I demonstrated how I created a server list and gathered information about those servers.  Now the DBA Repository needs to maintain a list of databases and some information about those databases.

Creating the dbo.Databases table

My intention with the dbo.Databases table is to keep a current record of all the databases I’m supporting and a few interesting properties about those databases.  My goal is not to keep a historical record of all databases so if one of my users deletes a DB I want it out of the repository, I’m only keeping track of what I’m currently supporting.

Here’s the table I created:

USE [DBARepository]
GO
 
SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
SET ANSI_PADDING ON
GO
 
CREATE TABLE [dbo].[Databases] (
      [ServerName] [nvarchar](128) NOT NULL
    , [DatabaseName] [nvarchar](128) NOT NULL
    , [DatabaseDescription] [varchar](max) NULL
    , [CompatibilityLevel] [tinyint] NULL
    , [RecoveryModel] [nvarchar](60) NULL
    , [Status] [nvarchar](60) NULL
    , [LastFullBackup] [datetime] NULL
    , [LastDifferentialBackup] [datetime] NULL
    , [LastTransactionLogBackup] [datetime] NULL
    , CONSTRAINT [i01_Databases] PRIMARY KEY CLUSTERED ([ServerName] ASC, [DatabaseName] ASC)
    )
GO
 
SET ANSI_PADDING OFF
GO
 
ALTER TABLE [dbo].[Databases] WITH CHECK ADD CONSTRAINT [FK_Databases_Servers] FOREIGN KEY ([ServerName]) REFERENCES [dbo].[Servers] ([ServerName]) ON UPDATE CASCADE ON DELETE CASCADE
ALTER TABLE [dbo].[Databases] CHECK CONSTRAINT [FK_Databases_Servers]
GO

You probably noticed the dbo.Databases table is lacking the size of the DBs.  I plan to handle that in a separate table because I do need historical data for trend analysis and that configuration will be handled in another blog entry.

Gathering the Databases and their Properties

We need an SSIS package that can loop through all the server and find the DBs existing on those servers.  Fortunately, in DBA Repository – Create the Server List I’ve already created a package that loops through the servers, we just need to gather the databases and their information while were at the those source servers.

Here’s a screenshot of the DBARepository_GatherServerAndDatabaseConfigurations package:

The Gather Database Information data flow, Email New Databases loop and Remove Deleted DBs dataflow will perform the tasks required to find, update, and delete DB information as well as notify a DBA team of new DBs

Gather Database Information Data Flow

Here’s the steps in the Gather Database Information data flow:

Find all Databases

The first step is going to find all databases in the server we’re connected to, it’s just an OLE DB source using this command as the dataset:

DECLARE @ServerVersion varchar(18)
SET @ServerVersion = CAST(SERVERPROPERTY('ProductVersion') AS varchar(20))
SET @ServerVersion = LEFT(@ServerVersion, CHARINDEX('.', @ServerVersion) - 1)</p>
-- Gathering DB info on SQL 2000 and earlier DBs
IF CAST(@ServerVersion AS tinyint) &lt;= 8
    SELECT  ServerName = @@SERVERNAME
          , DatabaseName = d.[name]
          , CompatibilityLevel = d.cmptlevel
          , RecoveryModel = CAST(DATABASEPROPERTYEX(d.[name], 'Recovery') AS nvarchar(60))
          , [Status] = CAST(CASE WHEN DATABASEPROPERTYEX(d.[name], 'Status') &lt;&gt; 'ONLINE'
                                 THEN DATABASEPROPERTYEX(d.[name], 'Status')
                                 ELSE CASE DATABASEPROPERTYEX(d.[name], 'Updateability')
                                        WHEN 'READ_ONLY' THEN DATABASEPROPERTYEX(d.[name], 'Updateability')
                                        ELSE DATABASEPROPERTYEX(d.[name], 'Status')
                                      END
                            END AS nvarchar(60))
          , lf.LastFullBackup
          , ll.LastTransactionLogBackup
          , ld.LastDifferentialBackup
    FROM    [master].dbo.sysdatabases AS d
            LEFT JOIN (SELECT   DatabaseName = b.database_name
                              , LastFullBackup = MAX(b.backup_finish_date)
                       FROM     msdb.dbo.backupset AS b
                       WHERE    b.[type] = 'D'
                       GROUP BY b.database_name) AS lf ON d.[name] = lf.DatabaseName
            LEFT JOIN (SELECT   DatabaseName = b.database_name
                              , LastTransactionLogBackup = MAX(b.backup_finish_date)
                       FROM     msdb.dbo.backupset AS b
                       WHERE    b.[type] = 'L'
                       GROUP BY b.database_name) AS ll ON d.[name] = ll.DatabaseName
            LEFT JOIN (SELECT   DatabaseName = b.database_name
                              , LastDifferentialBackup = MAX(b.backup_finish_date)
                       FROM     msdb.dbo.backupset AS b
                       WHERE    b.[type] = 'I'
                       GROUP BY b.database_name) AS ld ON d.[name] = ld.DatabaseName
 
-- Gathering DB info on SQL 2005 and later DBs
IF CAST(@ServerVersion AS tinyint) &gt; 8
    SELECT  ServerName = @@SERVERNAME
          , DatabaseName = d.[name]
          , CompatibilityLevel = d.[compatibility_level]
          , RecoveryModel = CAST(d.recovery_model_desc AS nvarchar(60))
          , [Status] = CAST(CASE WHEN d.state_desc &lt;&gt; 'ONLINE' THEN d.state_desc
                                 ELSE CASE WHEN d.is_read_only = 1 THEN 'READ_ONLY'
                                           ELSE d.state_desc
                                      END
                            END AS nvarchar(60))
          , lf.LastFullBackup
          , ll.LastTransactionLogBackup
          , ld.LastDifferentialBackup
    FROM    [master].sys.databases AS d
            LEFT JOIN (SELECT   DatabaseName = b.database_name
                              , LastFullBackup = MAX(b.backup_finish_date)
                       FROM     msdb.dbo.backupset AS b
                       WHERE    b.[type] = 'D'
                       GROUP BY b.database_name) AS lf ON d.[name] = lf.DatabaseName
            LEFT JOIN (SELECT   DatabaseName = b.database_name
                              , LastTransactionLogBackup = MAX(b.backup_finish_date)
                       FROM     msdb.dbo.backupset AS b
                       WHERE    b.[type] = 'L'
                       GROUP BY b.database_name) AS ll ON d.[name] = ll.DatabaseName
            LEFT JOIN (SELECT   DatabaseName = b.database_name
                              , LastDifferentialBackup = MAX(b.backup_finish_date)
                       FROM     msdb.dbo.backupset AS b
                       WHERE    b.[type] = 'I'
                       GROUP BY b.database_name) AS ld ON d.[name] = ld.DatabaseName

The query determines the version of the server and runs the appropriate query to find the databases and the properties I’m interested in gathering.

Find New or Updated Databases

I used a Slowly Changing Dimension task to split the data set into two groups, those records that have changed and those that are new.  The Slowly Changing Dimension task is simple to configure using it’s configuration wizard.  I configured the task to used the ServerName and DatabaseName as the Business Key columns and set the rest of the columns as changing attributes.  When the task compared the queried data to the data in the DBARepository it will determine where to put each row as output, to an updated row or a new row.

Handling Updated Rows

Rows found that currently exist but have new property values are sent down the Changing Attributes Output path.  I send them to a OLE DB Command task to perform a parameterized update query on each record individually

Once you’ve input the SQL Command you can perform the column mapping to the question marks in the command.

Handling New Rows

Any database that’s been newly added will branch down the New Output path.  I plan to email the DBA team when a new DB is found and I need to land the new rows in an OLE DB destination.  To do this I use a Multicast transform to make an in memory copy of the new record output.  On copy is sent to an OLE DB Destination mapped to the DBARepository’s dbo.Databases table, the other copy is sent to a Recordset Destination.

Load New DBs to RAM for Email

The Recordset Destination is a great tool that allows data sets from a data flow to be passed back to a control flow and used later.  All that’s needed is a user variable of type Object created at the proper scope, in this case I created a package level variable called NewDatabases.

Then configured the Recordset Destination to dump to the NewDatabases variable.

Now that we’ve found new DBs and updated any changed DB properties we only need to send the DBA team notification and delete any DB information in the repository that no longer exist on the servers.

Email New Databases Loop

This is a simple Foreach Loop container using the Foreach ADO Enumerator type and the NewDatabases variable we filled earlier and the collection to loop through.

I load a couple columns from the recordset into variables I’ll use in the message body of the email.

Inside the loop is a Send Mail task that simply generates an email for each new database found.  I build each email primarily using expressions so I can dynamically control the To and CC lines as well and the Message.  The variables I use in the ToLine and CCLine properties pull information from an SSIS configuration so my test server emails only me while the production server emails the appropriate contacts.   The MessageSource property expression builds the body of the email and it uses the data from NewDatabaseServer and NewDatabaseDatabase variables we gathered in the Foreach loop to help create the body text.

The last step is deleting database records from the DBARepository when the database no longer exists on a server.  That’ll need to go in another post since this entry is far longer than I intended and the Remove deleted DBs Data Flow has a lot of steps to describe.  We’ll save that for Part 2.

2 Responses to DBA Repository – Find the Databases, Part 1

  1. Awesome solution. I like the updateability.

    Rodney at SQLMag.com

  2. Pingback: SQL-ution » DBA Repository – Gather Database Size Info

Leave a Reply

Your email address will not be published. Required fields are marked *