DBA Repository 2010

I first shared my DBA Repository solution back in 2007. Since then, I've received many email messages with feedback and requests, which has left me both honored and chagrinned. I'm honored that many DBAs have incorporated this solution into their own environments, often tweaking, tuning, and ultimately making it their own. I'm chagrinned because I've been remiss in updating the DBA Repository lately. I've now remedied that situation by making the following enhancements:

In case you're unfamiliar with the DBA Repository, at its heart lies a SQL Server Integration Services (SSIS) package that gathers information DBAs can use to assess and report on the SQL Server machines they manage. For example, information about server names, SQL Server editions and service pack levels, database names and sizes, user and login permissions, and SQL Server Agent jobs is gathered. This data is stored in a central database named DBA_Rep. You can find more information about the original solution and its initial updates in the articles listed in the Learning Path box.

Resiliency for Offline Servers

When I first built the DBA Repository, I was under the gun to get a solution working quickly. My main goal was to use the repository as a documentation source for myself and the other DBAs I worked with. Although I was concerned with error handling within the SSIS package, I didn't spend as much time as I could have on it. When I executed the package with a scheduled SQL Server Agent job, I soon found out that a server was offline when the job ran, raising an error. Because the package's processing was synchronous (i.e., completed one step at a time), all the information that would have been gathered after the error occurred was lost. I had to resolve this problem before a successful run of the repository package could be performed.

There's a property named MaximumErrorCount whose value you can modify to accommodate a higher error threshold for an SSIS package. By default, this property's value is set to 1, which means that one error is the maximum number allowed. When this threshold is reached, all processing stops at that point. As a short-term solution, I changed this property's value to a higher number.

Most objects in SSIS packages include the MaximumErrorCount property. As the package grew larger, I found myself adjusting this property for each object, which quickly became tiresome. At the time, I also configured a simple SMTP object in the Event Handler that would email me the name of the offline server, but this didn't prevent the entire package from failing.

I've now devised a solution that provides resiliency for offline servers. The solution is fairly simple but effective. Instead of expecting that all the servers will be available, I added a ForEach Loop container that attempts a connection with each server before the data collection begins.

The list of target servers is created with a T-SQL query that pulls data from a table named ServerList_SSIS table. In the original solution, this table contained five fields: one varchar field to store the server name and four smallint fields (Connect, Version, DMZ, and LocationID) to control the flow of processing within the SSIS package. To gain the resiliency I needed, I added another field named Online to this table. The Online field is used to check each server in the server list and report whether it's online or offline to the package and ultimately to you. It also prevents the package from accumulating connection errors.

Figure 1: New process for checking whether servers are online (click to enlarge)

Figure 1 shows the Loop for Server Checks section I added to the SSIS package to make an initial spin through all of the non-demilitarized zone (non-DMZ) servers to make sure they're online.

The logic in this section is simple. The Check Servers Execute SQL task executes a single statement against all the servers (one server at a time) in the populated global variable SRV_Conn. (Two variables—SRV_Conn and SQL_RS—are passed to the ForEach Loop container and populated via Execute SQL tasks. The process is described in detail in "SQL Server Integration Services".  If the server is available and returns a result, the Check Servers task succeeds and passes the value to the Update Server List Success task. If the server is offline, an error occurs and the workflow tells the Check Servers task to execute the Update Server List Failure task.

Figure 2: Parameter mapping for the Update Server List Success and Update Server List Failure tasks (click to enlarge)

Both the Update Server List Success and Update Server List Failure tasks execute a T-SQL UPDATE statement to update the ServerList_SSIS table. The UPDATE statement sets the Online field to either a 1 for success or 0 for failure. As Figure 2 shows, the UPDATE statement uses the value of the current SRV_Conn variable by means of parameter mapping in the Update Server List Success or Update Server List Failure task.

With the mapping established, all that is required is to use the variable via a question mark (?) in the UPDATE statement:

UPDATE ServerList_SSIS  SET Online = 1  WHERE Server = ?

After all the servers have been queried, the Loop for Server Checks section completes execution and passes the workflow simultaneously to two additional Execute SQL tasks: Send Mail of Offline Servers and Load All Online Servers to Variable. As the name suggests, the Send Mail of Offline Servers task queries the ServerList_SSIS table for the freshly updated list of unavailable servers and sends a notification email to the specified DBA using the system stored procedure msdb..sp_send_dbmail. In the previous version of the DBA Repository SSIS package, the DBA received a separate email notification for each offline server. In the updated SSIS package, the DBA receives one email for each server group (DMZ and non-DMZ) when there are failures.

Listing 1 shows the T-SQL code that's executed for the Send Mail of Offline Servers task.

Listing 1: Code That Sends a Message About Offline Servers

IF (SELECT count(*) FROM dba_rep..ServerList_SSIS WHERE Online = 0) > 0BEGINEXEC msdb..sp_send_dbmail  @profile_name = 'Notifications',  @recipients = '[email protected],  @subject = 'DBA Repository Offline Servers' ,  @query = 'SET NOCOUNT ON;    PRINT ''OFFLINE SERVERS @ '' ;  SELECT GETDATE();   PRINT ''-----------------------------'';   SELECT Server FROM ServerList_SSIS WHERE online = 0' ,  @execute_query_database =  'DBA_Rep' ,  @query_result_width = 40,  @query_result_header = 0END

Notice that the message will go out only when there is one or more offline servers discovered. The logic to send a message only when servers are offline is accomplished by an IF (BEGIN…END) construct around the sp_send_dbmail stored procedure.

SQL Server 2008 Support

The SSIS package needs information about the SQL Server version that's installed on each machine being polled because certain sections within the package are version dependent. For example, the job scheduling section needs to know which SQL Server version is running because, in msdb, the schema for storing the job scheduling information is different in SQL Server 2000 than in SQL Server 2005 and SQL Server 2008. In addition, with the introduction of parallel processing in the DBA Repository solution, the version information is even more important because some data is gathered simultaneously for all servers, regardless of version, while other data is selectively gathered based on the version.

Previously the logic for distinguishing SQL Server versions was controlled by a rudimentary string parsing routine. The string parsing routine grabbed the first character of the Product_Version field in the SQL_Servers table. (This DBA_Rep database table contains the information gathered from the online servers.) The Product_Version field was character-based, holding values such as 9.00.4035.00. The string parsing routine then compared that character to a value—8 for SQL Server 2000 (which is version 8.x) and 9 for SQL Server 2005 (which is version 9.x) —by making a numeric comparison such as

WHERE value > 8

This string parsing routine doesn't work with SQL Server 2008 because its version number is 10.x. Grabbing a single character from a value such as 10.0.1600.22 would result in comparing the character of 1 instead of 10.

Admittedly, this was an oversight in my original solution. Fixing this problem so that the SSIS package would work on SQL Server 2008 machines involved modifying the SQL_Servers table and updating the SSIS package.

Modifying the SQL_Servers table. I needed a new field for driving the package flow. SQL_Servers is the first table to be populated in the package and it held the original Product_Version field, so it made sense to add the new field to that table. The new field, Product_Version_Int, holds the integer value of the SQL Server version and has a data type of smallint.

The Product_Version_int field is populated when the package executes the Load Server Info object. The value for this field is derived from the code

@@microsoftversion / POWER(2, 24)

When this code is executed against a server with a SELECT statement, it returns the value of 8 for SQL Server 2000, 9 for SQL Server 2005, and 10 for SQL Server 2008. With the version information captured in this manner, there's no need to parse strings of text.

Figure 3: New process for obtaining the SQL Server version (click to enlarge)

Updating the SSIS package. To update the SSIS package to use the Product_Version_Int field, I added the Update ServerList_SSIS Product Version task. Figure 3 shows this task, which is annotated to explain the flow. This figure also shows the query that updates the Version field in the ServerList_SSIS table with the value in Product_Version_int.

Listing 2 also shows this query in case you'd like to inspect it more closely.

Listing 2: UPDATE Statement for ServerList_SSIS

UPDATE serverlist_SSIS   SET version = SQL_Servers.Product_Version_IntFROM ServerList_SSIS INNER JOIN SQL_Servers ONServerList_SSIS.Server = SQL_Servers.Server

A sample query that uses the updated Version field is shown in Listing 3. In this example, the query is selecting servers running SQL Server 2005 or later.

Listing 3: Sample Query Using the Updated Version Field

SELECT LTRIM(RTRIM(Server)) AS servernameFROM ServerList_SSISWHERE (Connect = 1) AND (Version >=9)  AND (DMZ = 0) AND ONLINE = 1

Parallel Processing

Figure 4: Executing tasks sequentially in the original SSIS package (click to enlarge)

In the original solution, each task executed sequentially, as Figure 4 shows. (The one exception was the initial truncation of tables.)

This design worked well enough for 30 or fewer servers but eventually showed kinks in the armor when more servers had to be polled, especially when the polling took place over slower network links. To improve performance, changes need to be made.

Figure 5: Parallel processing of tasks in the updated SSIS package (click to enlarge)

In addition to consolidating some data flows, I decided to incorporate parallel processing in parts of the SSIS package. Fortunately, like the other modifications, it was fairly easy. Instead of using individual ForEach Loop containers for the data flows that gathered and consolidated information from each server, I grouped all the data flows into one ForEach Loop container. Figure 5 shows the result.

Prior to the modification, it took an average of 3 minutes for the SSIS package to poll 31 local and remote SQL Server instances. (These servers represent a small subset of a much larger infrastructure.) I was hoping that parallel processing would reduce the execution time by at least 20 percent.

The Test

With all three modifications complete, I was ready to test the DBA Repository 2010 against the original one for comparison purposes. I first tested the original solution. While watching the execution in design mode, I saw the inefficiencies exacerbate. For 31 servers, it took 2 minutes and 44 seconds to complete on the first run.

With fingers crossed, I then executed the updated package. As might be expected with any design modification, there were small glitches that had to be overcome. I'll spare you the details, but suffice it to say that two new variables and connections to support parallel processing were required. The variables and connections were added because at some stages in the execution, the SSIS package is polling the SQL Server 2005 and SQL Server 2000 servers at the same time, while at other stages, the package is polling them separately. If the variables' values change in the package, which they do many hundreds of times, they need to be isolated in their own ForEach Loop container. Otherwise, queries can suffer from syntax errors due to differences between the schemas of the various SQL Server versions.

After making the necessary tweaks, I put DBA Repository 2010 to the test again. The results did indeed meet my expectations. The time it took to poll the same 31 servers for the same information was 1 minute and 41 seconds. I was hoping for a 20 percent improvement in processing time but realized an improvement of more than 35 percent on the first execution. Over several executions I noticed time variances, but for the most part, the times consistently showed more than a 20 percent improvement.

It is important to note that while the performance increase is worthwhile, the updated SSIS package is only incorporating limited parallel tasks. It's possible to further parallelize individual tasks, and I welcome you to do so using the techniques covered herein. However, there's a point where contention for local object resources (table locking, for example) becomes an issue. Adding more parallel tasks would also require additional variables and ForEach Loop containers to accommodate the simultaneous processing.

How to Use DBA Repository 2010

If you'd like to try DBA Repository 2010, you can download it by clicking the Download the Code Here button near the top of the page. If this is your first installation of the DBA Repository, you also need to download the code from "Use SSRS and SSIS to Create a DBA Repository" and follow the installation instructions in the Code_Install_Instructions.txt file to create the DBA_Rep database.

After the DBA_Rep database is created, execute the DBA_Repository_Update_2010.sql file included in the download for this article. The DBA_Rep database will now contain all the tables you need to use the DBA Repository 2010, including the ServerList_SSIS table and the Server_Location table. There are only a few other steps you need to take to ensure a successful execution of the SSIS package:

After these preparations, you can run the SSIS package. When it finishes, you'll find numerous tables containing the information that it gathered. You can then use SQL Server Reporting Services (SSRS) to query that repository data and create customized reports. The "Putting the Data to Use with Reporting Services" section in the article "Use SSRS and SSIS to Create a DBA Repository" contains an example of how to create a report about SQL Server drive space. This report indicates which drives are potentially getting low on free space on each server. It also specifies the database file sizes on each drive on each server.

The Next Update

The modifications I made to my original solution have added much needed functionality, and it's my hope you'll find DBA Repository 2010 useful. At the very least, I hope that some of the techniques I discussed will prove helpful to you.

With these updates out of the way, I'm going to start looking at ways to improve the analysis and reporting of the collected information. Who knows, I might even build a data mining solution with Key Performance Indicator (KPIs) and trending for data growth over time. Just as when I first sat down three years ago to put this solution together, the ideas for improvement seem limitless. Feel free to email me with any ideas you have for modifying or expanding it.