ISA Server & SQL Server – Brothers in Arms

ISA Server & SQL Server – Brothers in Arms – Part 2 by Alin Selicean

By Alin Selicean

In part 1 of this two part article on use ISA Server and SQL logging we looked at some basic SQL database concepts and how they apply to ISA Server logging. In this, part 2, of the series, I discuss with you the specifics of monitoring an ISA Server database, creating an alert, and configuring an action in response to an alert.

Monitoring ISA Server database

Monitoring a database means, amongst other things, monitoring space usage and preventing the database from growing in an out-of-control manner. This is done by using a combination of alerts (things that are triggered when an error occurs), jobs (which are executed either manually, programmatically or scheduled) and operators (which are notified either by mail, pager or net send command). We’ll discuss how to:

Create an operator

Create an alert

Create a job to be used as a response to the error by the alert

To successfully make use of these features SQL Server Agent service must be running.

Creating an operator

Operators are notified using various means when a certain condition is reached. We’ll create an operator to be notified via email when a certain condition (log full and disk full) relating to our ISA Server database is reached.

NOTE

:
Email notifications can only be sent if SQL Server has been previously configured to send email notifications or to use a MAPI profile. If you have doubts or this feature was not configured, jump to step Creating an alert.

To define an operator, follow these steps:

Start Enterprise Manager (Start, Programs, Microsoft SQL Server, Enterprise Manager)

Expand Microsoft SQL Servers, then expand SQL Server Group

Expand your server

Expand Management

Right-click on Operators and select New Operator…

The following window will be displayed:

Enter your name in the Name textbox

In the E-mail name, enter your email address. Important !! SQL Server cannot resolve aliases so use a fully qualified email address.

Click on Test button to have SQL Server send you a test message. If everything’s setup correctly, you should shortly receive an email message from the SQL Server.

Click on OK

That should complete the steps for creating an operator.

Creating an alert

Alerts are very useful when you want to monitor specific conditions on SQL Server and, in some situations, to automatically respond (that is to take some actions) if the error that triggered the alert can be automatically handled. Log full and disk full are 2 typical examples of this kind of errors (with some exceptions, which are beyond of the scope of this article). To understand how SQL Server processes errors, here’s a brief description of the process:

SQL Server encounters an error which is setup to be logged (that is to be recorded in the Windows Application Log

Windows Application Log records the message and forwards it to SQL Server Agent

SQL Server Agent checks to see whether an alert has been setup for that specific error. If yes, performs the actions as per alert definition.

Creating an alert is a multi-step process. Here’s how to do it.

Start Enterprise Manager

Expand Microsoft SQL Servers, then expand SQL Server Group

Expand your server

Expand Management

Expand SQL Server Agent

Right-click Alerts and select New Alert… option and follow the steps below

Step 1 – General Info for the new alert

Enter a meaningful name for the alert you are creating

Select the type as SQL Server event alert

Check the box that says Enabled

In the Error Number text box enter 9002 as the error code to be watched. A description will appear in the right side of the text box (FYI – you can use the “…” button to browse through all the errors defined in SQL Server. We’re interested for the moment on only 9002 error)

In the Database name combo-box select your ISA Server database

Click on Apply to conclude the first step

Next step will be to define the response for this error (9002).

Step 2 – Defining the response for the alert

In the response tab, check the Execute job checkbox and then select (New job) in the combo-box. A new set of windows will appear which will help us configure the job to be run when the alert is fired.

Step 2.1 – Defining the job details

Enter a meaningful name for the job in a manner that will help you later to link the job with the alert (i.e. “Truncate ISAServer db tran log – alert resp job”)

Check the Enabled checkbox

Select a Category (optional)

Select the Owner of this job. This setting will affect the permissions the job will get when run. You should always select the login account used to connect to the database from ODBC (see Nathan’s article

http://www.isaserver.org/tutorials/How_to_setup_SQL_Logging_in_ISA_Server.html).

Enter a Description (optional).

Move to the Steps tab

Click on the New… button. This will assist you in creating a new step for this job.

Enter a meaningful name for this job step in the Step Name: textbox.

Leave the type as Transact-SQL Script (TSQL)

Leave the database as master

Enter the command as shown in the picture above

Click on Parse to validate your code (a window will popup saying that everything’s ok)

Move to the Advanced tab

Review the settings and it should be OK to accept the defaults. Click on OK to close New Job Step window and return to the New Job Properties window.

If your server has not been configured to use a MAPI profile for both SQLServer and SQLServerAgent services, jump to the next section. If it was configured, perform the following actions to configure the job you are creating to notify yourself when it’s been ran.

In the New Job Properties dialog box, move to the Notifications tab. Check the E-mail operator: and select from the combo-box the operator you created earlier. In the next combo-box, select when you want to be notified, as it best suite your needs. The options are shown below. I would recommend selecting Whenever the job completes because it is useful for the admin how often this job is launched.

This step will complete the overall step of defining a job as a response for a specific error. Click on Apply and then on OK to close New Job Properties window and return to the alert properties window.

One last setting and we’re done. We have configured a notification to be sent when the response job is launched by the alert. However, it is useful to also have a notification from the alert itself being triggered.

To have the alert send an email to a designated operator check the checkbox beside the desired operators in the list, as seen in the picture below.

Click on OK to close the window and this concludes our article.

For the purpose of this article, only the basic settings were discussed and included in our scenario. Other methods of managing ISA Server database might be available. Business might affect the availability of certain settings, especially in terms of authentication. Usually, setting the automatic growth feature should provide a secure and sufficient mechanism for controlling database growth. Unfortunately, this only provides a one-direction management: upwards. Sometimes, you might need to also come down from where you climbed. The alert and the job defined in this article provides one simple way to achieving this.

In the upcoming articles, more aspects of these tasks will be covered as well as other important tasks. Feel free to send any comments to the

author.

Additional information about SQL Server are available at www.microsoft.com/sql.

We hope you enjoyed this article and found something in it that you can apply to your own network. If you have any questions on anything discussed in this article, head on over to

http://forums.isaserver.org/ultimatebb.cgi?ubb=get_topic;f=2;t=009775 and post a message.