Introduction

SQLCentric is a piece of software that makes it easy to report on the status of the SQL servers in a domain. The company claim is that when SQL is the center of your universe you need SQLCentric. The company that makes the software is Pearl Knowledge Solutions. SQLCentric provides a solid foundation to monitor one or more SQL Servers.

Although the current version may not have an extensive number of features, working with the company leads me to believe the next release will add even more monitoring capabilities.

Once the software is implemented it provides a single web page overview to see basic information concerning the status of the SQL servers in an environment.

Environment

The environment I had to work with was pretty extensive in terms of the number of servers but I was limited by the types of systems I could work with. The main SQLCentric server was a Windows 2000 server computer with SQL 2000 and IIS 5.0. I was able to monitor a number of SQL 2000 installations. The operating systems that SQL 2000 was installed on included Windows 2000, Windows 2003 and Windows XP. In addition, to testing the servers in a single domain I was able to connect to servers in another domain.

The cross domain connections were achieved using pass through authentication. I had a domain account to test the monitoring of servers on my local network but I needed to create an account with the same username and password on the computers I wanted to monitor in another domain. In the appendix of the user manual there is a discussion of the rights needed in order to be able to use the product. I set all the rights needed on the main computer where I installed the application but on the remote computers the account I used was a local admin only. These settings we sufficient to allow monitoring of the computers.

Installation

The installation of the product was not quite as straight forward as I had hoped. This means that I could not just run the install program and be done with things. It really helped to read the quick start document available with the download (sqlcentricquickstart.doc). If I had read this document at the very beginning I would have known that every element of the registration screen needs to be filled in exactly as provided by the company and not just the license key.

During my first try I typed in 10 for the number of licenses instead of 010. Once everything was specified correctly the product installed without issue.

I did have an issue with the (IWAM..) user account and the permissions but that was because of an issue on my system. The event log told me that the permissions on the account were invalid, upon further investigation I found the account disabled. The only reason I bring this up is that while having these issues I wrote the company and Robert Pearl wrote me back almost immediately with suggestions and troubleshooting help. It should be noted at this point that Robert had no idea I was reviewing the product for SQLServerCentral.Com. As you will quick learn during the rest of this article the support I received in getting the product up and working was amazing.

Using SQLCentric

As I was working with the product I would say there are three areas that need to be dealt with in getting everything setup correctly. At this point I would be hard pressed to say whether the following sections belong in the installation section or the section on using the product. I have decided to include everything outside the actual running of the install program to be part of how to use the product.

The first step in using the product is to add some servers to monitor. This can be done in a couple of ways. There is a bulk method of loading servers, a self discovery method and the ability to add them one at a time. I used the individual method of loading the servers. This was done using the administration screen which is one of the two options in the program menu for SQLCentric (SQLCentric_Admin, see below).

Once the Admin tool has been selected the following screen is used to administer the computers to be monitored.

The Get Servers option is used to bulk load the servers that can be monitored. This button uses the fact that SQL server broadcasts its existence on the network. I used the Servers button to add the detail for each server I wanted to monitor. The Server Detail screen allows one to control the server individually. Once there are servers to be monitored the servers button brings up the server list.

Even though a server is in the list it does not mean that it must be monitored. It is possible to have all the servers listed but only select a few to be monitored. It is also possible to group servers. Each server can belong to only one group. Also, the product will monitor the free space on the SQL servers but I only used it to monitor 2 volumes. I was under the impression that you can select the volumes but I did not experiment with this option.

Pearl Knowledge Solutions Update: While Bruce may have used it with servers that have two volumes, SQLCentric can and does monitor and alert on ALL logical drives/volumes. I have clients with clusters and SAN, that have 8 volumes, for example, and it show status for all of them. See the image below:

Once the administration is done it is time to have the product monitor the servers. This is done through the SQLCentric application. This application is listed in Program files as shown above. I was confused when I launched the application (not the SQLCentric_Admin application but the SQLCentric application) and nothing happened. I launched it again, nothing. I looked in task manager and saw the program running. I then remembered that it was advertised as a web application so I went and checked IIS on the server and sure enough the following web was installed and I could see the following page.

This page is one of two overview screens that can be used to monitor the status of the servers. The other page would be a group's overview that allows one to see an overview of the server groups created by the user. The group's overview bubbles up any error conditions so if one server in the group has a red status in a category the whole group will have a red status light. Each of the items on the web page allows the user to drill further down. For example, clicking on the red SQL Agent Status light for szabob will show you that the SQL Agent service is not running. If a job fails the Job Status light will appear red. This red light only appears for jobs that are enabled.

Pearl Knowledge Solutions Update: As far as the job status goes, you can see the status of ALL jobs enabled or disabled on the drill-down jobs status page (even states whether job is enabled or disabled). Bruce's issue, which is true, that jobs disabled do not show up as a failure on the main status page,nor is the user alerted. This did not seem to be something that many testers wanted. If a job is disabled, it's status is usually not checked.

Some of the neat features were that if I clicked on the server name I was brought to an overview screen where I could see the version of SQL and I could also check the logs for the SQL server. If you click on the DBStatus light all the databases on the server are listed. Clicking on the database name displays the schema as shown in the following screen shot.

This provides decent online documentation for databases that are in production. The e-mail alerts are also available if an SMTP server is available, which can be installed with IIS. The e-mail alerts will be sent each time the SQLCentric application is run and a failed status is encountered.

It is probably worth noting here the sequence of events to get SQLCentric up and running. As discussed here SQLCentric has an administration piece to setup all the monitoring and alerts. The SQLCentric application then acquires all the information and loads a database. The web site then queries the database and extracts the information in an organized format. The SQLCentric application is not scheduled to run in order to keep the information up-to-date the application would have to be scheduled using Windows scheduler or some other scheduling type program. I did not experiment much with scheduling but felt that it would be easy enough to setup and administer, as the only thing to do would be to schedule the SQLCentric application to run at regular intervals.

Pearl Knowledge Solutions Update: Indeed, to make the scheduling option more readily available to the user, we plan to have the SQLCentric admin interface manage the implementation of the schedule.

Support

As mentioned earlier the support I received was top-notch. Robert Pearl wrote me a number of times asking how things were going. Occasionally, I mentioned an issue I was having and it was always followed with an e-mail highlighting some things I might want to try. The e-mails also were within hours of being sent, not the next day as one might expect. At one point I wrote him and asked how he knew I was reviewing the software and he said he did not even know it. He said this is the level of support all his customers get, it was phenomenal.

Conclusions

This product reminds me of an application that I always wanted to write but never get around to doing. I know that SQL provides an ability to gather information about various aspects of the server and its databases through SQL-DMO and information in various system tables. I have always thought it would be nice to collect this information to provide an interface to view the status of a SQL environment. This product summarizes the state of one or more SQL servers in an easy to read format. The drill down options allow in-depth access to each aspect being monitored.

I think one or two servers can be monitored easily through enterprise manager. Sometimes access to a SQL server for monitoring provides an operator or junior administrator with more access than needed. This product gets around this by allowing one to see the status of SQL servers without the need for access to the server directly.

There are some limitations with the product. I would like to see the status of jobs regardless if they are enabled or disabled. At the same time I would like to see an incremental status of jobs. If a job failed but then I inspected it I would like to be able to reset the time at which I checked the server and only have it bring back failures after a given time.

Pearl Knowledge Solutions Update: Please see MaxJobAlerts feature below

I can see this product meeting the monitoring needs for some small to medium size businesses. It may be useful in larger organizations but the need for the product to access operational procedures might lead a larger company to go in a different direction.

Ratings

I will rate each of the following using a scale from 1 to 5; 5 being the best and 1 being the worst. Comments are in the last column.

Product Information

Web Site: http://www.pearlknows.com/id1..html

Developer: Pearl Knowledge Solutions

Pricing: Contact

Pearl Knowledge Solutions for pricing.

Pearl Knowledge Solutions Comments: I do appreciate the time Bruce has taken to review the product, and it was nice working with him. . I'd like to mention some of the upcoming features in our my next release:

SQLCentric will add two new optional status counters that will monitor the performance of your SQL Servers.

• BCH (Buffer Cache Hit Ratio) Monitor! Here, the user can set the sampling interval as well as the alert threshold.
• Drill down on the MCache page, and see exactly how much memory is being used by SQL Server, and statistical details on your Buffer and Procedure Cache.
• SQLCentric will have the ability to monitor for blocked processes, and alert the user to which spid is being blocked and by which spid.
• Drill-down to the SysProc page, and you will see all the blocked processes and the TSQL of the lead blocker causing the contention, at the time it was detected.

One of the things we struggled with is to somehow limit the number of job alerts generated, without degrading the integrity of the monitoring system. If a failure is detected an alert will be sent. Now, we have decided to introduce some intelligence into the job alert system:

• MaxJobAlerts will not only allow the user to set the number of times to be alerted for a particular failed job; it will reset the alert for the following run time of the job.

SQLCentric historical reporting and quick reports module, will give the user in-depth reporting capability on the state of their SQL Server infrastructure.

• For historical reporting, the user will be able to select their servers, statistical measures, and date parameters, to generate trend analysis output based on the run-time of the SQLCentric monitoring system - with limited graphing capability.
• Quick reports will let the users generate a real-time snapshot of active monitored servers, for such things as server uptime, sql uptime, sql jobs, etc.