Use SSRS and SSIS to Create a DBA Repository

I work as the primary DBA on a network of more than 60 SQL Server machines. When I began work on a repository project to centrally store information about each of those machines, I had two goals in mind. First, I wanted to be able to use T-SQL or a SQL Server 2005 Reporting Services (SSRS) report to quickly answer any questions about the machines. This turned out to be the loftier of my two goals. Second, I wanted a solution that I could enhance over time, by modifying the schema if necessary, to accommodate new categories of information. This second goal led me to combine the twin powers of SSRS and SQL Server Integration Services (SSIS). I wrote about my solution in “SQL Server Reporting Services” (June 2007, InstantDoc ID 95745) and “SQL Server Integration Services” (May 2007, InstantDoc ID 95385).

Readers responded so well to these articles, and my original solution has progressed enough since then, that I wrote this follow-up article. In this article, I discuss the enhancements I’ve made to the original SSIS package, as well as the new SSRS reports that I use daily.

Background

My original solution includes a core set of tables in a single SQL Server 2005 database repository called DBA_Rep. With the exception of the table called ServerList_SSIS, the DBA_Rep database is populated via an SSIS package, called DBA_ Server_Load. You can populate the ServerList_SSIS table manually, which I recommend for initial testing. Alternatively, you can use a tool such as Sqlcmd. For more information about using Sqlcmd, see “Sqlcmd” (April 2007, InstantDoc ID 95387). Figure 1 shows the Server List_SSIS table that I used SQL Server Management Studio (SSMS) to populate.

Although you might find it helpful to refer to my original articles to understand the enhancements I describe in this article, my approach here is to assume that you’re not familiar with the original solution. Thus, the code for the enhanced projects and for the DBA_Rep database schema changes includes all the code from the original solution. The downloadable code for the new solution also includes detailed instructions for configuring the updated DBA_Rep database and for deploying the SSIS and SSRS projects. (To download the new solution, go to www.sqlmag.com, InstantDoc ID 97840, and click the 97840.zip hotlink.) Within the article, I also provide tips to help you easily implement the solution. Once you have the new project up and running, you can download and use the previously published reports with the new solution. I made the following enhancements to the DBA Repository solution:

• Added support for SQL Server 2005 and SQL Server 2000 queries to the msdb database
• Added support for SQL Server authenticated domains via multiple data sources and duplicate objects
• Added support for server location
• Added information for reports and ad-hoc queries: ° SQL Server users ° SQL Server logins ° SQL Server group memberships ° Disk space allocation (the most useful enhancement by far)
• Made enhancements to the SQL Server Agent job schedule

Support for SQL Server 2005 and SQL Server 2000

My original SSIS package was limited because it wasn’t designed to take full advantage of both SQL Server 2005 and SQL Server 2000. This limitation was most evident when it came to the schema changes in the msdb database that occurred with the release of SQL Server 2005.

Among other things, the msdb database stores information about all SQL Server Agent jobs. Previous queries of the msdb database for the DBA Repository solution worked fine for SQL Server 2005 and SQL Server 2000. But I ran into a problem when I tried to include information about job schedules. In SQL Server 2005, adding the sysschedules table to query the msdb database required a change to the SSIS package. Because this table doesn’t exist in SQL Server 2000, additional steps were necessary for me to acquire the schedule information that was crucial to my DBA analysis. I had to use two separate queries that returned equivalent data to the Jobs repository table. Specifically, these queries gather job frequency information.

The two queries return the same number of columns. I was most interested in the columns regarding the schedule frequency, such as freq_type and freq_interval. As I explain later, this information is vital for producing reports that show when the jobs are scheduled to run. In addition, combining this information with the data captured from the last_run_duration field lets you isolate jobs that aren’t performing as expected for their scheduled times.

In order to segregate the SQL Server 2005 and SQL Server 2000 servers so that the queries would execute against the correct version, I had to make a few crucial changes to the flow of the SSIS package. First, I set up multiple additional data sources to support SQL Server 2005 (version 9.X) and SQL Server 2000 (version 8.X) in demilitarized zone (DMZ) and non- DMZ environments. I also set up two additional variables to support SQL Server 2005 connections. These variables, as you might recall from the original solution, are populated by a SELECT query that reads the server name from a table (ServerList_SSIS) stored in the repository database. The result is stored in an object data type variable. This variable is then converted to a string data type variable so that it can be used with the ForEach Loop container objects as well as the ServerName expression for each data source. Adding support for SQL Server 2005 and SQL Server 2000 was simply a matter of repopulating the variables at several stages during the package’s execution. The following code contains the SELECT queries that repopulate the variables:

--Run for SQL Server 2000 (version 8)
SELECT LTRIM(RTRIM(Server)) AS servername
FROM ServerList_SSIS
WHERE Connect = 1) AND (Version = 8) AND
(DMZ = 0)

--Run for SQL Server 2005 (version 9)
SELECT LTRIM(RTRIM(Server)) AS servername
FROM ServerList_SSIS
WHERE (Connect = 1) AND (Version = 9) AND
(DMZ = 0)

Next is the code to update the SQL Server version :

Update Serverlist_SSIS set version =
cast(left(dbo.SQL_Servers
.ProductVersion,1) as smallint)
From ServerList_SSIS Inner Join SQL_
Servers on ServerList_SSIS.Server = SQL_Servers
.Server

This code takes advantage of an additional column, called “version,” added to the ServerList_SSIS table. This change allows automatic qualification for the version of newly added servers.

Now let’s look at a report that uses the accumulated data for the SQL Server Agent jobs gathered from the preceding package enhancements. This report, called Job Interval, is an enhanced version of a previously published report. The new report shows job schedule information and job status (e.g., whether the job is scheduled to run, whether the job failed or succeeded). I use parameters in the actual report to filter jobs based on this type of information. For example, in Figure 2, page 16, the report shows only jobs that are scheduled to run and only those of the type backup. The term “backup” here is a subjective value; in my organization, all our backup jobs are named “backup.” Thus, my report will contain jobs that are titled with the word “backup” in their names. Using runtime parameter values to apply a filter to a report is fairly straightforward. (For information about this procedure, see “SQL Server Reporting Services,” June 2007, InstantDoc ID 95745.) The end result is that I now have one location to query all SQL Server Agent job schedules and status information, regardless of the SQL Server version. The downloadable code includes this updated report.

Continue to page 2