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