Power, Productivity, and Performance too — Optimize Your SQL Server with sqlSen
In this section, we take a brief look at how you use sqlSentry via the Console. Our goal is to give you a sense of how it looks and feels. Details are left to the “Test Results” section of the article, which comes later. Our focus here is on how to use the Console to view information and configure options.
Becoming Familiar with the Console
Most of the time spent with the sqlSentry Console will be to see what is going on with your servers. The Console packs a huge amount of information in a very small space, and it may take some time to get used to locating all the information available.
To keep it simple, let’s begin by looking at the Console with as little information as possible on it. (Note: All screen shots in this review have been reduced in size in order to fit this Web page. Because of this, the screens you see here may appear a little crowded. This is not a true reflection of the screens you will see on your desktop.)
On the left pane you see the Navigator. As you can guess from its name, this is how you move about the major features of the sqlSentry Management Console. In the above screen, none of the major nodes under the Navigator have been expanded.
On the right pane, you currently see what is called the “All Devices” view for a single day in time, a global calendar showing all failures and long-running events across the enterprise by default (this can be adjusted via the Filter pane). For the moment, let’s just say that when you click on any of the objects in the Navigation Pane, than a related pane will appear on the right that is related to the object selected in the Navigator. We will talk more about the Calendar View a little later.
Let’s begin by looking at each of the major nodes in the Navigator Pane.
The first node is “Devices.” This node includes all connections managed by sqlSentry, which includes both SQL Server and Task Scheduler connections.
In the screen shot above, we see that the “Devices” node is broken down into “Computers” and “Unassigned,” and that “Computers” is broken down into separate computers (by name) that may be running SQL Server, Task Manager, or both. In the above example, two computers — dfasqladgts2 and dfasqltest2 — are being watched by sqlSentry.
When you open up a specific computer, you can see the Jobs, Maintenance Plans, Alerts, DTS Packages, SQL Server Agent Log, and a Reporting Services node if Reporting Services is detected. This is where you can view what is going on in each of these areas. You can also create or modify any of these. You can drill-down into each of these nodes as necessary.
A large part of sqlSentry is its comprehensive notification ability. Notifications, which are configured in the Actions and Settings Pane, include two parts:
- Conditions describe the various states of a job, task, performance counter, audit condition, or other event monitored by sqlSentry. When a particular event occurs, you can specify that a particular action occur. Examples of some possible events in sqlSentry include if an event starts, completes, succeeds, fails, conflicts, queues, blocks; or if a performance counter reaches some threshold; or if an audit condition occurs.
- Actions are what happen when a particular condition occurs. Actions can include the execution of a job, the execution of a process, the execution of Transact-SQL, kill a task, log to disk, log to database, log to the Event Log, send an e-mail, or send a page.
Alerts can be easily and quickly configured. In the case of e-mail notifications, e-mails can be sent to individuals or groups. As has already been mentioned before, e-mail notifications do not use SQLMail or MAPI, making e-mail notification easy to set up.
One of the main reasons you might use a “Device” node is to view your SQL Servers and Task Schedulers to help you better identify and resolve resource collisions, such as finding out if any scheduled SQL Job conflicts with any Scheduled Tasks on the same physical computer. And if you run multiple SQL Server instances on the same physical server, sqlSentry will highlight any cross-instance collisions.
The next node is the “SQL Servers” node (see below). In many ways it allows you to do much the same that you can do with the “Devices” node, but from a perspective much like Enterprise Manager in SQL Server 2000. Many DBAs find that they use this node when actually adding, editing, and deleting Jobs, Alerts, Maintenance Plans, and so on. Task Manager administration is not available from this node.
Next is the “Custom Event Views” node (see below). This allows you to create custom views of the events you are most interested in. While sqlSentry has many predefined views you can use, you may have a specific need. For example, let’s say that you want to see a view of all the DTS jobs for all of your SQL Server across the entire enterprise, or you may want to see a view of all the backup jobs for all remote SQL Servers, or whatever your needs are. Custom views can be designated as “local,” which means that only the user who created it can view it, or “shared,” which means that all users can access the view.
Next is the “Event Chains” node (see below). This option allows you to create complex jobs on a single server, or across servers, that are dependent on each other. For example, if you need Job A on Server Alpha to run, then you need Job B on Server Bravo to run, but only if Job A on Server Alpha was successful, then you can create an Event Chain within sqlSentry to accomplish just this. Windows Tasks may also be chained.