Many nifty tools are available to help you monitor your SQL Server environment. However, every tool, no matter how effective, seems to include a hefty price tag. Monitoring software for just one SQL Server can cost thousands of dollars. And monitoring tools sometimes come with agents that you must install on your SQL Server and that traditionally consume as much as 3 percent to 5 percent of the server's processing power. In addition, many tools provide much more functionality than you really need. At the children's hospital where I work, our DBA group manages 8 SQL Servers and more than 250 databases. In our situation, where patient information is accessed 24 x 7, 365 days a year, we must have a common method of accessing information about the health of our SQL Server environment from anywhere on the network. Ironically, the method we've found most useful for retrieving current and historical performance data isn't in a pricey monitoring package but comes from tools readily available within SQL Server, Windows .NET Server, and Microsoft Office XP. For example, on my office computer, I added an active channel that links to the hospital's SQL Server Web site. Microsoft Active Channel technology is integrated with Internet Explorer (IE) to "channel" browser data to a client's desktop without user intervention. Active Channel technology integrates with an active Windows desktop so you can view Web pages as desktop backgrounds or screen savers, for example. So if I have an active desktop, I can use our Web site as my screen saver. When I get to my office in the morning, I can view the results of database backups, read messages from last night's Database Consistency Checker (DBCC) operations, see which databases are growing fastest, and view a variety of performance graphs on the Web—before I even log in.
Let's see how you can implement the solution our DBA group uses to view current and historical SQL Server performance data through a graph on a Web page, as Figure 1 shows. First, let's look at how you can monitor your environment by logging System Monitor data from Win.NET Server to SQL Server. Then, I'll show you how we render the data to a dynamic Web page by using Microsoft Access data access pages. To implement the techniques I describe in this article, you need SQL Server 2000, Office XP with Access installed, an IIS 5.0 or 4.0 Web server, Win.NET Server or Windows XP, and IE 4.01 or later. You also need an Active Directory (AD) or Windows NT 4.0 domain so that you can use domain user accounts for trusted connections or Windows authentication. For simplicity, all the client computers you use should also be part of the domain.
Before you can view your SQL Server performance data, you must first create in Win.NET Server the System Monitor counter logs that collect the performance data, then send it to any SQL Server on your network. If you don't have Win.NET Server (I used Release Candidate 1—RC1), you can use XP to walk through the System Monitor steps in this article. [Editor's Note: See Correction for information about how a Windows XP Service Pack 1a (SP1a) bug affects the CounterDetails table.] Both Win.NET Server and XP can store System Monitor data in a unique format on SQL Server. However, Windows 2000 and NT can store performance-related data only to a file. Win2K uses System Monitor to log data to a binary or text file, and NT uses Performance Monitor to log data to a binary file. Later, I briefly describe the format we use and show you how to transform the data from the performance tables and optimize data retrieval by using a stored procedure. Then, I show how you can use the data in an Access data access page to graph your data points. After you've created your data access page, you can save the page to your Web server and view your data from the Web.
Note that if you're trying to keep costs low, you can use Microsoft Data Engine (MSDE) to log your performance data to SQL Server. You can install MSDE as many times as you want once you've purchased a SQL Server or Office product that contains MSDE. Although Enterprise Manager doesn't come as part of MSDE, you can use an Enterprise Manager installation to connect to a local or remote installation of MSDE. MSDE can use a maximum of two processors, and databases on MSDE can grow to a maximum of 2GB. MSDE also has scheduling and replication capabilities that you can use on many single-use SQL Servers. Finally, at the time I wrote this article, you could use Win.NET Server RC1 for free for 360 days. So, the software that you use for this monitoring solution is essentially free—but only for a time.
Configuring System Monitor
To begin, you need to create System Monitor logs in either Win.NET Server or XP to record performance information to a SQL Server database. On either OS, you also need to use the Control Panel's Administrative Tools to create a system Data Source Name (DSN) in the Data Sources (ODBC) applet that connects to a database where you'll store performance data. We created a database called DBADMIN on a remote SQL Server called SQL2KDEV1 to store this data.
To follow our example, create the DBADMIN database on the SQL Server you want to use; the SQL Server installation and database can exist locally or remotely. Then, create a system DSN that points to the DBADMIN database and uses either Win.NET Server or XP running System Monitor to log performance information. Create the DSN by using a trusted connection to the DBADMIN database in which the trusted user has database owner (DBO) rights. We created our DSN, SQLPerfData, on our Win.NET Server, which is named WINNET, by using a trusted connection to the DBDMIN database on SQL2KDEV1 through our test domain's Q\administrator account. We used this account for simplicity, but be aware that it's not a best practice. For simplicity and testing purposes, make sure the domain user account you're working with is also the DBO of the DBADMIN database. Within the DSN, select the DBADMIN database as the default database. Test the DSN to verify that it works properly.