Executive Summary:
The author's original solution used SSRS and SSIS to create a DBA Repository. This update adds support for SQL Server 2005 and SQL Server 2000 queries to the msdb database, support for SQL Server authenticated domains via multiple data sources and duplicate objects, support for server location, information for reports and ad-hoc queries, and an enhanced SQL Server Agent job schedule.
|
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
Prev. page
[1]
2
3
next page 
