Suppose that you hire a new DBA and assign him or her to a
large-scale SQL Server infrastructure that consists of more than 100 servers divided not only by purpose and criticality but
also by geography. This new DBA would likely spend the entire
first week getting to know the lay of the land—using SQL
Server Management Studio (SSMS) to connect to each SQL
Server machine, one by one, to gather essential information
such as version, edition, server configuration, existing databases,
and scheduled backup jobs. A daunting task indeed.
I learned early in my career that spending time up front
to automate otherwise manual and time-consuming tasks
can preserve your sanity. I therefore developed a fairly simple
solution that connects to each available SQL Server machine,
pulls information into a central repository database, and feeds
the combined data to a report for DBAs
and other IT staff to use. In this article I describe the solution I used. (Note that the solution I offer is
intended to enhance, not replace, a preexisting full monitoring
and alerting solution.)
Tool Time
Several tools are available to the intrepid DBA setting out to discover the uncharted server landscape. For example, you can use
the Microsoft SQL Server Health and History Tool (SQLH2;
available at http://www.microsoft.com/downloads/details.aspx?familyid=eedd10d6-75f7-4763-86de-d2347b8b5f89&di
splaylang=en) to populate a repository database. However, this
tool is outdated and not very flexible. (For more information
about SQLH2, see "SQL Health and History Tool," November
2006, InstantDoc ID 93544.)
For my project, I employed the command-line tool
Sqlcmd, SQL Server Integration Services (SSIS), a SQL Server
database for the repository called DBA_
Rep, and SQL Server 2005 Reporting
Services (SSRS). (For more information
about Sqlcmd, see the Web-exclusive
sidebar "Sqlcmd," http://www.sqlmag.com,
InstantDoc ID 95387; for related articles
about SQL Server 2005 and SSIS, see the
Learning Path.) In this article, I explain
how to use Sqlcmd and SSIS to construct
and populate the DBA_Rep repository
database. In a later article I'll explain how
to build and deploy the three SSRS reports
designed to deliver the data from this
repository. (Note: This article makes several
nonstandard recommendations, such as
querying system tables directly, employing
pseudo temp tables, and using xp_cmdshell
to run commands. If you stringently adhere
to best practices and use only supported
techniques, you'll need to withhold your
judgment temporarily—until you see that
the nonstandard methods I use are efficient
and aren't detrimental.)
Create the Repository Database
Now that you have a list of servers to use as
input, you might wonder how you can use
that input directly in an SSIS package. But
don't get ahead of yourself—first, you must
store the information somewhere. As most
DBAs know, the best place to store a list of data is in a table. Before we examine
the SSIS package,
let's take a look at
the database that will
be the repository for
the SSIS load.
The table that will store the list of servers on the network from the Sqlcmd command is called ServerList_SSIS. In addition to this table, six other
base tables in the
DBA_Rep database
store data to give a DBA enough basic
information at a glance to answer some fundamental questions about the state of each
server. These tables are SQL_Servers, Database_Info, Databases, Backup_History, Jobs,
and Jobs_Archive. Each of these six tables
holds specific information about each SQL
Server instance. Web Listing 1 (http://www.sqlmag.com, InstantDoc ID 95385) contains
each table's schema; this listing also serves as
the script to build the database for the SSIS
package to populate the database. To run
this script successfully, you need to create
a blank database called DBA_Rep in your
SQL Server 2005 instance. After you create
the DBA_Rep database, the script that Web
Listing 1 contains will create the necessary
tables in the database.
The table ServerList_SSIS is empty,
waiting to be filled with server names.
Although I didn't automate this process, you
can use SSIS techniques similar to the following techniques that I discuss to easily do
so. I used the special stored procedure xp_
cmdshell to run the Sqlcmd shell to return
the server list. Assuming that you've run the
script to create the DBA_Rep database that
contains the ServerList_SSIS table, you can
easily use an Insert Into statement with the
Sqlcmd /Lc command to insert the server
data. In SSMS, open a new query window
and enter the following command:
USE DBA_Rep
GO
Insert Into ServerList_SSIS
Exec xp_cmdshell ‘sqlcmd /Lc'
At this point you need to consider the
following caveats. First, the result set returns
NULL records. The table can accommodate
NULL records, and the SSIS package's logic
will in turn filter out these rows. You could
build in logic to take care of the NULL
values before the insertion, but I chose to
do it as part of the SSIS package. Because
this table has no defined indexes that require
unique values, truncating the table also
ensures that no duplicate rows occur each
time the Sqlcmd /Lc command loads the
table. You also need to ensure that xp_cmdshell is configured with the proper permissions to execute. By default, xp_cmdshell is
disabled in SQL Server 2005; you can use
the SQL Server Surface Area Configuration
tool to enable xp_cmdshell.
Obtain the Data
The information that's most important to
DBAs on a day-to-day basis is server information, such as version (e.g., SQL Server
2005 or 2000), service pack level, edition (i.e.,
Standard or Enterprise), and default collation.
Fortunately, SQL Server 2005 and 2000 store
this information in handy system tables. My
solution queries each server and stores this
information in the SQL_Servers table.
Database information is just as important or even more important than server
information; crucial database information
includes the two tables Databases and Database_Info. The Databases table simply holds
the name of the server and the name of the database. Database_Info holds details, such
as size, file location, and recovery model
for each database. You can use the Server
field to join the Database_Info table to the
Databases table. (For ease and simplicity, I
used the Server field for joins rather than a
key field or a server ID field that I would
need to manually update. Although this
solution wasn't ideal, it was sufficient for my
purposes.)
The final three tables store information
about SQL Server Agent jobs and database
backups, which I believe is the most important information for any DBA to have. For
example, knowing which jobs are failing
and need attention is imperative when
you're working with hundreds or even tens
of database servers and databases. Jobs often
fail—and because most jobs perform routine
full and transaction log backups if they fail,
response must be swift. The Backup_History table holds detailed information about
backups that occurred in the past n days.
I've found that 5 is a good number of days
of history to analyze.