Abstract

Full text

Turn on search term navigation

Headnote

Applications: Microsoft MSXML 3.0+, Microsoft Active Server Pages, Microsoft SQL Server 7.0/2000 Operating System: Microsoft Windows

In this month's article "Manage application configuration settings with XML and XPath," we demonstrate a technique whereby you can store mission-critical settings in external XML files. As we show you, this solution allows you to leverage the full arsenal of XML-related technologies bursting onto the scene in today's rapidly evolving IT landscape. We also promote using Session variables instead of Application variables for maintaining these settings across the lifetime of your users' Web sessions. To make this approach more useful, it would be nice to be able to monitor multiple databases for open connections. This way, you could determine, for example, when your user pool had successfully transitioned from one server to another as described previously. In this article, we'll show you how to develop just such a utility for Microsoft SQL Server.

What we'll cover

We'll start by briefly recapping the most likely scenarios in which you might utilize a tool such as the one we'll build. Then, we'll examine the code necessary to implement the database session monitor for SQL Server. Finally, we'll consider one or two related points about the use of the utility. So, without any further ado, let's dig right in.

Why monitor?

One of the advantages to the session-based technique we proposed for XML configuration settings is the ability to dynamically redirect connecting users to a new database at will. Enterprise applications often come with a 99.999 percent uptime requirement, making it difficult or impossible to impose any maintenance window on a database server. One solution to this problem involves clustering for redundancy; however, this solution comes with a hefty price tag. Instead, with as few as two servers, you can utilize this technique to solve the problem.

Here's another situation you might encounter. How often have you taken a big application development project through multiple phases involving development, staging, and then production servers only to worry later that some of your test users might still be connecting to and using the staging server? Of course, you can ask them, but end users aren't usually good about realizing when a problem like this arises. You can use the utility page we'll be building shortly to see quite clearly how many people are connected to which servers, no matter how large the development team or how expansive the server farm.

How to monitor

Our session monitoring page will use the same XML configuration file from the accompanying article. Because there are no limitations imposed by XML, this file could contain hundreds of servers, if your organization were large enough to justify that. Or, with a few modifications to the file format, you could organize servers into logical groupings-development, staging and production; east coast, west coast; users, managers, executives or whatever organizational model makes sense for you. For our purposes, we'll examine just the simple case shown in the XML file you're already familiar with.

With those introductory words out of the way, let's jump right into the utility page itself. Type (or download from our ftp site) the code from Listing A into your favorite editor and save the file as dbsessions.asp (or substitute any other filename you like). Let's take a closer look now at how the process works.

Getting the XML settings

The utility is pretty straightforward. Initially, after performing some standard Web page fare (such as establishing style settings), we declare the variables we'll need. Then, we instantiate ADO Connection and Recordset objects along with the DOMDocument object we'll use to access the XML file. Just as before, we set the latter's async property to False and then invoke the Load method to read in our settings file.

If there's no parsing error with the XML file, then we need to determine the database connections to monitor. Unlike our previous XPath expression, the expression we'll use this time,

returns multiple nodes, one for every element in the XML source file. We'll use a simple For Each loop to traverse this collection, and for each one, we'll display its attributes.

Listing A:

Figure A:

In addition, we'll use the values stored in each element's attributes to open an ADO Connection to the Master database on the specified server. Why the Master database and not the database indicated by the Ca t a Iog attribute? For the simple reason that connecting to the database itself would tack an additional connection onto the session count. One side note: This technique presumes that the account and password coded into the XML file can log into the Master database!

Determining the number of sessions

Once connected, an uncomplicated SQL query will show us how many open sessions are connected to a particular database. The sysprocesses table will, for a specified d bi d, show us how many sessions are open. To determine the database id value, we can carry out an additional query of the sysdatabases table. For the Northwind database, for example, the query ends up looking like this:

At this point, all that remains is to display the value at the appropriate point on the page, close up our loops, and then exit gracefully. A sample run of the utility, detailing the number of sessions open in each of our four databases, is shown in Figure A.

Summary

In this article, we've taken the technique of utilizing XML configuration settings files one step further. We've shown you a solution you can begin using immediately to make the task of monitoring open database sessions less tedious. And, since the approach uses XML and XPath, two of the fastest growing technologies, you can be sure that your solution won't be antiquated by this time next year!

Keep an eye on open database sessions with ASP and XML

Content area

Abstract

Full text