DBA Repository – Create the Server List

In a previous entry I discussed the goals of the DBARepository.  The first thing the DBA Repository needs to meet those goals is a list of servers to monitor.

Creating the dbo.Servers Table

We need a table I’m calling dbo.Servers which will help me group servers into categories I’m interested in.  It will have some fields I’ll need to input manually, such as:

  • Is the Server Virtual?
  • Is the server using SAN disk?
  • Is the server test or production?
  • What Division/Department uses this server?
  • What DB software is installed on the server?

This table will also be periodically updated automatically with information about the software version and patch level installed to help me determine which servers are lagging behind on patches and may be a security risk.

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].[Servers](
	[ServerName] [nvarchar](128) NOT NULL,
	[ServerType] [varchar](50) NOT NULL,
	[Department] [varchar](128) NOT NULL,
	[UseDescription] [varchar](max) NOT NULL,
	[DatabaseSoftware] [varchar](128) NOT NULL,
	[SoftwareEdition] [varchar](50) NULL,
	[SoftwareProductLevel] [varchar](10) NULL,
	[SoftwareVersion] [varchar](25) NULL,
	[Processors] [tinyint] NOT NULL,
	[LicenseType] [varchar](25) NOT NULL,
	[CALs] [smallint] NOT NULL,
	[IsVirtual] [bit] NOT NULL,
	[IsSANAttached] [bit] NOT NULL,
	[GatherStatistics] [bit] NOT NULL,
	[BackupByCommvault] [bit] NOT NULL,
CONSTRAINT [PK_Servers] PRIMARY KEY CLUSTERED
(
[ServerName] ASC
))
GO
SET ANSI_PADDING OFF
GO
ALTER TABLE [dbo].[Servers] ADD CONSTRAINT [DF_Servers_Processors] DEFAULT ((0)) FOR [Processors]
ALTER TABLE [dbo].[Servers] ADD CONSTRAINT [DF_Servers_CALs] DEFAULT ((0)) FOR [CALs]
ALTER TABLE [dbo].[Servers] ADD CONSTRAINT [DF_Servers_IsVirtual] DEFAULT ((0)) FOR [IsVirtual]
ALTER TABLE [dbo].[Servers] ADD CONSTRAINT [DF_Servers_IsSANAttached] DEFAULT ((0)) FOR [IsSANAttached]
ALTER TABLE [dbo].[Servers] ADD CONSTRAINT [DF_Servers_GatherStatistics] DEFAULT ((1)) FOR [GatherStatistics]
ALTER TABLE [dbo].[Servers] ADD CONSTRAINT [DF_Servers_BackupByCommvault] DEFAULT ((1)) FOR [BackupByCommvault]
GO

Once the table is created it can be filled with data manually, all the ‘NOT NULL’ columns need to be input manually and all the ‘NULL’ columns will be updated automatically from an SSIS package.  The server’s host name is the SeverName and I’ll treat SQL Server named instances as a new server record.  So, if you have a server with a host name ‘MYSERVER’ and a named instance called ‘SQL2008’ I’d show two rows in dbo.Server with a MYSERVER and MYSERVER\SQL2008 in the SeverName column.

Gathering Server Version Information

In order to gather all the servers’ version information we need an SSIS package to loop through all the configured servers and gather some stats.  For now, I’ll just be loading the SQL Server version information, the MySQL, Oracle and Sybase queries will come at a future date.

I called the SSIS package DBARepository_GatherServerAndDatabaseConfigurations.  This package will run nightly and I’m only going to discuss the server information gathering portion in this post.  Here’s the package:

Load SQL Servers Variable Task

This task performs a simple query:

SELECT s.ServerName
FROM dbo.Servers AS s
WHERE s.DatabaseSoftware = 'Microsoft SQL Server'
AND s.GatherStatistics = 1

And saves the results in a global user variable of type ‘Object’ which we’ll use later to loop through each server:

Server Information Tasks ForEach Loop

This container is configured as a Foreach ADO Enumerator based on the SQLServers variable loaded from the Load SQL Servers Variable task.

Each loop changes the value of a global string variable called ‘SQLServer’

All tasks in the loop will use an OLE DB connection called ‘SQLServers’ whose ServerName property is updated using the SQLServer variable from the loop.

This ForEach task configuration allows me to repeatedly gather stats from each source server and drop that info in the DBARepository.

Test Source Server Connectivity

This Execute SQL task exists solely to test the server connection for each server looped.  In fact, the SQL Statement is merely a commented line of T-SQL.  The purpose of this task is to attempt to make a connection; if the source server cannot be contacted I don’t want the package to fail, I want it to be skipped, the problem logged and the package to move on to the next server.  There are two tricks that make this work:

  1. The task cannot be allowed to bubble an exception up the stack.  To stop most tasks from throwing an error you add an OnError event handler to the task, simply select the task and click the Event Handler tab and add the OnError event.  Then, in the Variables window click the gray Show System Variables button, find the Propogate variable and set its value to False.
  2. In order log the connectivity problem I created an Execute SQL task in the OnError handler that will log the issue to my favorite destination – the DBARepository.  I created a simple log table to record general SSIS package errors (such as this one) I must handle manually for later review.

Gather SQL Server Properties Data Flow

This is a simple data flow that performs an update on the appropriate server record in the dbo.Servers table.

The Source SQL Server OLEDB Source task runs this SQL Command which works on SQL 2000 – 2008:

SELECT  Edition = CAST(SERVERPROPERTY('Edition') AS varchar(50))
, ProductVersion = CAST(SERVERPROPERTY('ProductVersion') AS varchar(20))
, ProductLevel = CAST(SERVERPROPERTY('ProductLevel') AS varchar(10))
, ServerName = @@SERVERNAME

The Update DBARepository OLE DB Command transformation performs an update query shown here:

The column mapping looks like this:

Conclusion

The goal for today was to create a way to store server information and automatically gather as much server configuration information as possible.  This is accomplished by:

  • Creating the dbo.Servers table to hold the information we’re interested in
  • Manually inputting some configuration information including the servers’ host names
  • Creating an SSIS package that:
    • Find the servers we’ve configured to monitor
    • Loops through each server (when a server cannot be reached the problem is logged)
    • Queries the source server for server properties
    • Updates the DBARepository.dbo.Servers table for each server in the loop with the statistics we’ve found

7 Responses to DBA Repository – Create the Server List

  1. Pingback: SQL-ution » DBA Repository – Gather Scheduled Job Info

  2. Pingback: SQL-ution » DBA Repository – Gather Database Connection Information

  3. RR says:

    Hi Dave. Thank you for some very good work.
    I noticed that in the query above you filter for SQL servers only.

    SELECT s.ServerName
    FROM dbo.Servers AS s
    WHERE s.DatabaseSoftware = ‘Microsoft SQL Server’
    AND s.GatherStatistics = 1

    The Dashboard includes a spot for Sybase and Oracle, which we have in my shop.
    What is the best way to use DBARepository to monitor those types of Databases?
    Thank you

  4. Hi RR,
    My organization eliminated Sybase and Oracle database servers and I never got a chance to implement gathering stats for those platforms.

  5. Abu Dina says:

    Hi David,

    Excellent work!

    I’m trying to build my own DBA repository from scratch (for learning) but I have a problem nobody is able to answer online!

    I have setup my Foreach loop and I’m able to loop through my SQL Servers but I’m struggling to get the OLD DB Source to use my Dynamic connection.

    How did you do it?!

  6. Once you have the list of servers in a for each loop task you can use the server name from your looping variable in the connection expression in the OLD DB connection manager.

  7. Abu Dina says:

    Thanks David. It’s now working as expected.

    Much appreciated.

Leave a Reply

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