Power, Productivity, and Performance too — Optimize Your SQL Server with sqlSen

Architecture

sqlSentry is an n-tiered, .NET 2.0 application that is composed of three components:

• sqlSentry Console (thin client).
• sqlSentry "Server" (Middleware service).
• sqlSentry Database (SQL Server 2000 or SQL Server 2005).

These components can all run on the same physical SQL Server, or all run on separate physical servers.

Let's take a more in-depth look at each of these components:

sqlSentry Console

Interaction with sqlSentry is done through the sqlSentry Console. This thin client is generally installed on servers running the sqlSentry "Server" component, along with all DBA workstations. When it is first installed, it mirrors your current Enterprise Manager or Management Studio groups and servers, so minimal initial configuration is required. The console is used to interact with the "Server" service and the SQL Server database.

sqlSentry "Server" (Middleware Service)

This component runs as a Windows service and can be used to monitor up to a total of about 100 SQL Servers or 100 Task Schedulers, depending on the load and hardware used. If you have more than 100 SQL Servers or Task Schedulers you want to monitor with sqlSentry, then you will need more than one instance of the sqlSentry "Server" service running. This component does most of the heavy lifting and communicates with the sqlSentry Management Console, sqlSentry SQL Server Database, and to monitored SQL Servers and Task Managers. More specifically, it collects event history, status, and performance information from monitored servers, sends notifications, and more. If multiple Server services are used they will automatically cluster together to provide virtually linear scalability and extreme fault tolerance. If any Server fails, the others automatically pick up the load in a matter of seconds, ensuring nothing is missed and notifications are always sent.

sqlSentry SQL Server Database

This is a single database that stores sqlSentry event metadata and historical information. Only one database is needed for an entire enterprise.

No Agents Needed

sqlSentry does not use agents on monitored servers to communicate with SQL Servers and Task Managers. Instead, the sqlSentry "Server" service is used to communicate with SQL Server and Task Managers directly through your network using their native language, resulting in reduced overhead and management tasks.

Configuration Options

Because of the way sqlSentry is designed, it can be configured many different ways, depending on the number of SQL Servers or Task Managers you want it to monitor. In very small shops, you might be able to install all the components of sqlSentry on one of your preexisting SQL Servers.

More typically, though, you will want to dedicate a server just for the purpose of running sqlSentry. For example, you might install sqlSentry's Console, "Server" service, and SQL Server database on a single server. Or, if you want to minimize the use of SQL Server licenses, install the Console and "Server" service on a single server, and the sqlSentry database on one of your current production SQL Servers. Either of these options should be able to handle up to 100 monitored SQL Servers or Task Managers.

If you have more than 100 monitored SQL Servers or Task Managers, then you will need to consider having multiple servers dedicated to the sqlSentry "Server" service. Even if you expand past a single "Server" service, you will still only need one sqlSentry SQL Server database, as it is designed to scale out as you scale out sqlSentry over multiple physical servers.

No matter how you configure your sqlSentry "Server" services or the sqlSentry database, you can install the Console on any servers or workstations you need. These will always connect to the single sqlSentry database, and allow DBAs to manage your SQL Servers from sqlSentry.

Installation and Configuration

Before I begin a look at the steps required to get sqlSentry up and running in your enterprise, I want to note that sqlSentry does not install anything into SQL Server, other than a database. SQL Server is not altered in anyway.

Installing and configuring sqlSentry, even for a company with many SQL Server or Task Managers to be monitored, is not a hard or long task. Here's how you might approach such an installation.

• Determine the appropriate component architecture. In other words, decide where the sqlSentry components will be run. For example, for a typical installation, you might decide to install all three sqlSentry components on a single dedicated SQL Server box.
  
  Once you have determined the architecture, you are ready to install the software. In our example, we will install all three components on a single dedicated SQL Server.
• Next, set up the DBA who will be using sqlSentry, along with any groups you might want to make administrative tasks easier.
• Next, set up some global configuration settings. This step includes such things as telling sqlSentry about your SMTP server, configuring types of notifications, and so on.
• Next, tell sqlSentry what connections or objects you want to watch (monitor). These can include:
  1. SQL Server Agent Job Source.
  2. Maintenance Plan Source.
  3. SQL Server Agent Log Source.
  4. SQL Server Agent Alert Source.
  5. DTS Package Source.
  6. Reporting Services Source.
  7. Windows Task Scheduler Source.

At this point, sqlSentry has been installed and configured, and it is ready to begin using to manage your jobs, set up alerts, and so on.