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:
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.
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:
The following window will be displayed:
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:
Creating an alert is a multi-step process. Here’s how to do it.
Step 1 – General Info for the new alert
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
Click on the New... button. This will assist you in creating a new step for this job.
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 theauthor.
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 tohttp://forums.isaserver.org/ultimatebb.cgi?ubb=get_topic;f=2;t=009775 and post a message.