30 - Proactive Network Administration

• How to proactively manage your network - Learn how to watch key indicators to help uncover problems before they impact the user community. Learn about the various monitoring tools available with Windows NT Server and the BackOffice suite.

• How to use Windows NT Performance Monitor - Learn how to use the Performance Monitor to create charts, store information in log files for later analysis, and set alerts on important counters that will reflect the status of your servers and their applications.

• How to use SQL Mail and SQL Alerts to receive e-mail or pages when an error occurs - Explore the capabilities of the SQL Server Alert mechanism and the use of SQL Mail to notify operators by e-mail messages or by paging.

Proactive Network Administration - An Approach

How to Watch Your Network and Servers

Windows NT Server includes a powerful tool called the Performance Monitor. This general purpose monitoring utility can provide performance information on the core services of Windows NT Server and BackOffice components that have been added to a server. Information can be stored in a log file and used to create reports or graphs using tools like Microsoft Excel.

Several BackOffice components have their own monitoring tools and utilities as well. For example, Exchange Server includes the capability to establish both Server Monitors and Link Monitors. Server Monitors watch the status of server-based services to ensure that they are still running. Link Monitors are used to "bounce" a special type of message, known as a ping message off a remote messaging system at another site. If the return message is not received, the link between your server and the remote site may be down.

See "Monitoring Your Site," (Chapter 15)

SQL Server has a powerful alerting mechanism that monitors the event log for error conditions. If a specified error condition is detected, an e-mail message is sent to a selected operator, or to an e-mail mailbox set up for a pager. Using third-party software not included with BackOffice, the pager mailbox can be monitored for incoming messages, and one or more pagers can be contacted with an appropriate message. You can use the tools in SQL Server to define one or more operators and assign responsibility for specific error conditions during selected hours of the day and days of the week. Procedures for using this capability are provided in the section "Using SQL Mail and Alerts to Monitor SQL Server" later in this chapter.

Systems Management Server (SMS) includes a software-based protocol analyzer capable of capturing the packets sent over the network and displaying them for analysis. In addition to this basic function, it also has a display that shows the level of broadcast and multicast traffic, percent of bandwidth utilization, and other useful statistics. Even if you do not want to engage in sophisticated protocol analysis, this can be a useful tool for monitoring your network. In addition, it is possible to use new counters in the Performance Monitor that are added when the Network Monitor is installed. These let you use the Performance Monitor to chart, log, or set alerts on network-specific activity not associated with any particular server.

See "Using the Network Monitor," (Chapter 27)

Windows NT Server also includes an SNMP service that can be used in conjunction with an SNMP console application, such as HP's OpenView or Sun's NetManager, to monitor the status of your servers and other network components. The SNMP service is not covered in this book, but if your organization is already using an SNMP console, you should investigate the use of the Windows NT SNMP service to integrate the monitoring of your servers into your existing tools.

How Do You Know There's a Problem?

One of the best indications that you have a problem, which is exploited by the Server Monitor in Exchange Server, occurs when one or more services stop running. Under ordinary circumstances when you start the services on a server, they start running and just keep going. There are a number of reasons why they might stop, almost all of which represent problems. Any kind of accidental power outage is one obvious source of an unintentional service stoppage. A service can also stop due to a lack of resources on the server such as disk space or RAM.

If you accept as a given that all servers will have an Uninterruptible Power Supply (UPS), then you will have eliminated most of the accidental power outages. It is still possible, however, that the server will become unplugged from the UPS, or that the backup power offered by the UPS is not sufficient to last through an entire power outage. This will cause an orderly shutdown of the server, provided that the UPS settings are properly configured. At any rate, the fact that services have stopped, even though the situation may be temporary, is a noteworthy event that is sure to impact users. If you are at least among the first to be aware and are already working on fixing the problem when the first phone calls come in, your proactive approach will be paying some dividends.

In the case of resource shortages, you should have an opportunity to get an earlier warning. Ideally, an alerting mechanism would signal that the resource was in short supply. An event is triggered in the event log when the hard disk space on a server falls below a certain threshold. The event is usually raised when there is still time to do something about it unless someone is actively copying massive files to the server's hard disk. This is a good example of an alert that can be set to help identify a general problem that can impact all services and users across the board and should certainly be watched. You can use the Free Megabytes counter on the Logical Disk object to set an alert in the Performance Monitor.

An indication of potential networking problems occurs when e-mail messages are not getting through the path between one server and another, or between a server and a remote messaging system, for example a system on the Internet. This is the principle behind the Link Monitors in Exchange Server, and these can be useful tools even if you do not use Exchange Server for your messaging system. This type of mechanism will detect a downed router, for example.

You can also use counters like the I/O - transactions/second counter of the SQL Server object to watch for sustained performance at a very high rate, which can indicate the need to offload some of the work on an additional system or to provide more processing power on the original system. In addition, if the level falls below expected norms during business hours, you may have a problem. With this type of counter, you will need to use judgment to determine if you have a potential problem, indicated by a sustained value that is out of the expected range, or if you have an aberrant spike value that can be safely disregarded.

One type of error that is not detectable with any of the mechanisms available in the Microsoft BackOffice product is a network card or other similar component that has gone bad and is producing bad network packets. Occasionally, a network adapter malfunctions in such a way that it floods the network with malformed packets. The Network Monitor, a software-based protocol analyzer will not be able to detect these because the bad packets will be discarded by elements of the protocol stack. A hardware device capable of detecting these problems is a worthwhile investment. Relatively inexpensive LAN monitoring devices can be used to augment the upper layer analysis offered by the Network Monitor. This often is a less expensive solution than a dedicated hardware-based protocol analyzer capable of detecting and analyzing problems across the network spectrum.

Establish Benchmark Values

Using the Performance Monitor

The Performance Monitor offers three main capabilities: charting, logging, and alerts. Procedures for all three functions are presented in this section. A chart provides a real-time graph of the item being monitored. It can be presented as a line graph, or as a histogram style bar chart that changes at each interval you have specified new values should be plotted. You can monitor the local computer or select a computer from over the network to monitor. You can then choose from a number of objects and associated counters that are available to monitor. For example, the Server object offers the Total Bytes per Second counter, which measures the total number of bytes sent or received by the server service on a computer and provides a basic measure of how busy the server is. There are options for the chart including a legend at the bottom showing the counters being plotted, grid lines, and a value bar showing the maximum, minimum, average, and last value for a counter.

Log files can be created that capture the values of the same objects available for charting. By placing the information in a log file, you can view the information later using the Chart option with data coming from the log file, or you can export the file in tab or comma delimited format and create your own graphs or reports using a tool like Microsoft Excel or Microsoft Access. You can set the interval at which values are recorded in the log so that it is possible to use a frequent sample interval to closely watch a counter for a short period of important activity or an infrequent interval, say every 15 minutes, to gather information about a counter throughout the day.

Alerts provide the capability to watch any of the counters available for the value to rise above or fall below a specified threshold value. If an alert is triggered, a program can be run the first time or every time. In addition, you can select options to write an entry in the event log when an alert is fired, and/or to send a network message with the content of the alert. You can also set an option that causes the Performance Monitor to switch to the Alert View if an alert is triggered. The alert view will display a report showing up to 1,000 alerts that have been triggered.

An entire manual in the Windows NT Resource Kit is devoted to learning how to use the Performance Monitor. This manual outlines in detail an approach to performance tuning, a different focus from that presented here. The manual is written by Russ Blake, the designer of the Performance Monitor, and is the authoritative work on the subject. It is worthwhile reading for any Windows NT or BackOffice administrator. This section presents an overview of the Performance Monitor and discusses how it can be used as a tool for proactive server management.

Charts

To create a chart using Performance Monitor, follow these steps:

1. Start Performance Monitor.

2. Click the View a Chart toolbar button, or choose View, Chart from the menu.

3. Click the Add Counter toolbar button, or choose Edit, Add to Chart from the menu. The Add to Chart dialog box appears (see fig. 30.1).

   Fig. 30.1 - This dialog box is used to select the computer, object(s), and counter(s) you want to monitor on this chart.

4. If you want to chart the counters from another computer, click the ellipsis button (...) at the right of the Computer text box. The Select Computer dialog box appears (see fig. 30.2).

   Fig. 30.2 - The Select Computer dialog box is used to select a computer whose object counters you want to view on the selected chart.

5. You can enter the name of the server you want to monitor in the text box, or browse domains for the server you want in the Select Computer box. If you enter the name manually, be sure to precede the name with a double backslash (\\). Click OK.

6. The Add to Chart dialog box now reflects the computer you selected. Using the Object drop-down list box, select an object you want to monitor. After selecting an object, select one or more counters in the Counter scrolling list box. You can hold down Ctrl and click new counters to add to your selection. Click Add when you are satisfied with your selection.

   Almost all servers will have some objects, such as the Server object. Others will be added as products are installed on the server. For example, when you install SQL Server, approximately eight objects and their associated counters are added to the list of available objects for that server. The exact list depends on the server-based applications you have installed and the installation options you selected.

7. The choices you selected will be added to the chart, and the Add to Chart dialog box will still be left open. You can repeat step 6 as often as you want to keep adding objects and counters to the chart. Click Done when you have added all the counters you want to view.

8. Charts are most effective if the counters you add all share a similar scale. In other words, if you are viewing one counter whose values vary from 0 to 100, such as a percent counter, and another counter whose values vary from 0 to 10,000, the line representing the percent counter is likely to appear nearly flat. You can use the scale button on the Add to Chart dialog box to change the scale of a particular item. Double-click the item whose scale you want to change in the legend at the bottom of the chart. This opens the Edit Chart Line dialog box (see fig. 30.3).

   Fig. 30.3 - The Edit Chart Line dialog box is used to change the scale or appearance of a chart line in a Performance Monitor chart.

9. When you have added all the counters you want to watch, you can use the highlight feature to help follow a single counter more easily. Highlight one of the counters in the legend at the bottom of the chart and press Ctrl+H. The line corresponding to the counter you selected will be displayed with a wide white line. You can use the mouse or the arrow keys to select a different counter to change the highlighted line.

10. You can also set a number of options that will change the appearance of your chart. Select Options, Chart from the menu. The Chart Options dialog box appears (see fig. 30.4).

    Fig. 30.4 - The Chart Options dialog box is used to set options that change the appearance of the chart being viewed.

11. The Chart Options dialog box allows you to add horizontal or vertical grid lines to your chart. You can select the type of chart you want (a graph or a histogram) and the scale of the vertical axis. You can also select whether the legend and value bar should be displayed at the bottom of the chart. Click OK when you are satisfied with your choices.

12. You can select other options directly from the Options menu. You can toggle the display of several elements on or off using this menu. The menu and title (together), the toolbar, and the status bar can be toggled in this manner. You can also toggle the display of the menu and title bar by double-clicking in the body of the chart.

13. When you have set up a chart whose settings you would like to save, you can store the settings for the chart in a file. Choose File, Save Chart Settings. In the resulting save dialog box, enter the name of the chart settings file you would like to use. The extension for these files is PMC by default.

Logs

To log information on the performance of your server for later analysis, follow these steps:

1. Start Performance Monitor.

2. Click the View Output Log File Status toolbar button, or choose View, Log from the menu.

3. Click the Add Counter toolbar button, or choose Edit, Add to Log. The Add to Log dialog box appears (see fig. 30.5).

   Fig. 30.1 - The Add To Log dialog box is used to select the computer and object(s) for which you want to store information in a log file.

4. If you want to log the counters from another computer, click the ellipsis button (...) at the right of the Computer text box. The Select Computer dialog box appears.

5. You can enter the name of the server you want to monitor in the text box, or browse domains for the server you want in the Select Computer box. If you enter the name manually, be sure to precede the name with a double backslash (\\). Click OK.

6. The Add to Log dialog box now reflects the computer you selected. Using the Objects drop-down list box, select an object you want to monitor. You can hold down Ctrl and click new objects to add to your selection. Click Add when you are satisfied with your selection.

   When you are creating a log, all counters for a particular object are stored. Therefore, you do not have to select the counters you want to log.

7. The choices you selected will be added to the log and the Add to Log dialog box will still be left open. You can repeat step 6 as often as you want to keep adding objects to the log. Click Done when you have added all the objects you want to log.

8. You can set a number of options for the log file you are creating. Choose Options, Log from the menu. The Log Options dialog box appear (see fig. 30.6).

   Fig. 30.6 - The Log Options dialog box is used to enter a name for the log file you are creating and to set the interval at which counter values are stored in the log file for the objects that have been selected.

9. The Log Options dialog box allows you to enter a name for the log file. You can also set the interval at which counter values are stored in the log file for the objects that have been selected. Most importantly, you can use the Start Log button to begin capturing information. The Status box in the upper right of the window changes from Closed to Collecting, and the file size gradually grows at the interval shown in the Log Interval box.

10. You can select other options directly from the Options menu. You can toggle the display of several elements on or off using this menu. The menu and title (together), the toolbar, and the status bar can be toggled in this manner. You can also toggle the display of the menu and title bar by double-clicking in the gray area at the top of the log display.

11. When you have set up a log whose settings you would like to save, you can store the settings for the log in a file. Select File, Save Log Settings from the menu. In the resulting Save dialog box, enter the name of the log settings file you would like to use. The extension for these files is PML by default.

If you want to use Microsoft Excel to graph the information you have already captured in a log file, follow these steps:

1. Start the Performance Monitor.

2. Choose Options, Data From. The Data From dialog box appears (see fig. 30.7).

   Fig. 30.7 - The Data From dialog box is used to select the source of information for charting. It can be used to display information that has been saved in a log file.

3. Click the ellipsis button to browse for the log file you want to use. When you have selected the file, click OK to close the Data From dialog box.

4. Now you can add counters to the chart in much the same manner as you would if the data were coming from current activity, however only those objects you saved in the log file will be available. Add the items in which you are interested to the chart.

5. Choose File, Export Chart from the menu. The Performance Monitor - Export As dialog box appears (see fig. 30.8).

   Fig. 30.8 - The Export As dialog box is used to specify the delimiter that will separate values in the export file and the file's name and path.

6. Select the Comma option button in the Column Delimiter box in the lower right corner of the dialog box. Select Export CSV Files in the List Files of Type drop-down list box. Use the Drives and Directories controls to select the location in which you want to store the file, and enter a file name. Click OK. The export file will be created. You can close the Performance Monitor until you are ready to use it again.

7. Start Microsoft Excel.

8. Choose File, Open. The Open dialog box appears (see fig. 30.9).

   Fig. 30.9 - The Open dialog box in Microsoft Excel can be used to open an export file created by the Performance Monitor.

9. Select Text Files in the Files of Type drop-down list box. Use the file browsing controls to find your export file and highlight it. Click Open.

10. The initial display may be difficult to read if the column widths are too narrow. Position the mouse cursor over the line between columns in the lettered column headers. The cursor changes to a line with arrows pointing in each direction. Double-click the left mouse button to auto size the column to fit the data.

11. Select the information you want to graph and proceed as you would with any Excel chart. You may need to delete extraneous rows or columns, or copy selected portions of the data to another location on the spreadsheet to create the chart you desire (see fig. 30.10).

    Fig. 30.10 - An Excel chart created from Performance Monitor information.

12. Save or print the chart as desired. Exit from Excel.

Alerts

To set alerts that will notify you when a performance monitor counter crosses a threshold value, follow these steps:

1. Start Performance Monitor.

2. Click the View the Alerts toolbar button, or choose View, Alert from the menu.

3. Click the Add Counter toolbar button, or choose Edit, Add to Alert. The Add to Alert dialog box appears (see fig. 30.11).

   Fig. 30.11 - The Add to Alert dialog box is used to select a counter and then to enter the threshold value that will trigger an alert.

4. If you want to set alerts on counters from another computer, click the ellipsis button (...) at the right of the Computer text box. The Select Computer dialog box appears.

5. You can enter the name of the server you want to monitor in the text box, or browse domains for the server you want in the Select Computer box. If you enter the name manually, be sure to precede the name with a double backslash (\\). Click OK.

6. The Add to Alert dialog box now reflects the computer you selected. Using the Object drop-down list box, select an object you want to monitor. After selecting an object, select one of the counters in the Counter scrolling list box. Then enter the threshold value in the Alert If box that will trigger the alert. Select the Over or Under option button in the Alert If box. Click Add when you are satisfied with your selection.

7. The choices you selected will be added to the list of Alerts and the Add to Alert dialog box will still be left open. You can repeat step 6 as often as you want to keep adding alerts to the list. Click Done when you have added all the alerts you want to monitor.

8. You can set a number of options for the alerts you are monitoring. Choose Options, Alert. The Alert Options dialog box appears (see fig. 30.12).

   Fig. 30.12 - The Alert Options dialog box is used to specify the actions that should occur when an alert is triggered.

9. The Alert Options dialog box allows you to specify what should happen if an alert is triggered. You can cause the Performance Monitor to switch to the Alert view (from a Chart or Log view). An event can be written to the Applications event log if you want, and you can also send a network message to a user name from the Windows NT Server account database for your domain.

10. You can select other options directly from the Options menu. You can toggle the display of several elements on or off using this menu. The menu and title (together), the toolbar, and the status bar can be toggled in this manner. You can also toggle the display of the menu and title bar by double-clicking in the gray area at the top of the Alert View display.

11. When you have set alerts whose settings you would like to save, you can store the settings in a file. Choose File, Save Alert Settings. In the resulting save dialog box, enter the name of the alert settings file you would like to use. The extension for these files is PMA by default.

Using SQL Mail and Alerts to Monitor SQL Server

Setting Up SQL Mail

If you intend to use the Exchange Client for Windows NT as the MAPI provider for SQL Mail, do not set up the Microsoft Mail client included with Windows NT Server. Doing so prevents SQL Mail from logging on to the Exchange Server mailbox.

Setting Up the Windows NT Mail Client

To set up the Windows NT Mail Client, follow these steps:

1. Double-click the Mail icon in the Main program group. The Microsoft Mail client program starts and displays the Welcome to Mail dialog box (see fig. 30.13).

   Fig. 30.13 - The Welcome To Mail dialog box allows you to create a new Workgroup Postoffice, or select an existing postoffice.

2. If you do not yet have an e-mail postoffice of any kind and you intend to use the Workgroup postoffice feature shared by Windows NT Server version 3.x, Windows NT Workstation version 3.x, and Windows for Workgroups, select the option to Create a New Workgroup Postoffice and continue with step 3. Otherwise, select the option to Connect to an Existing Postoffice and continue with step 6. Click OK.

3. A warning dialog box appears advising you that there should only be one Workgroup Postoffice within a workgroup. If you are sure that a postoffice does not already exist, click Yes to create a new one. The Create Workgroup Postoffice dialog box appears (see fig. 30.14).

   Fig. 30.14 - The Create Workgroup Postoffice dialog box allows you to enter or browse for the location of the new workgroup postoffice you are about to create.

4. Enter or browse for the location for the new Workgroup Postoffice and click OK. A dialog box appears for you to enter the details for the administrator account (see fig. 30.15).

   Fig. 30.15 - The Enter Your Administrator Account Details dialog box is used to enter the name, mailbox name, and password for the administrator's account for this postoffice.

5. Enter the details for the account and click OK. You should use the name of the SQL Server service account you are using to run SQL Server under. The mail account you are creating will be associated with the SQL Server service account. Click OK.

6. A dialog box informing you that the Workgroup Postoffice has been created appears. It also reminds you to share the directory you just created (see fig. 30.16). Click OK. The Mail Sign In dialog box appears. Skip to step 8.

   Fig. 30.16 - The Mail dialog box informs you that the Workgroup Postoffice has been created and reminds you to share the directory so that other users can access the postoffice.

7. Continuing from step 2, The Connect to Postoffice dialog box should be visible. Enter the network path (\\servername\sharename) to your Microsoft Mail postoffice. Usually the sharename is maildata. Check with the Microsoft Mail administrator if you do not know the location of your postoffice. Click OK. The Mail Sign In dialog box appears.

8. Enter the mailbox name and password for the mailbox that will be used by SQL Mail. Click OK. The new mailbox is opened, and your e-mail account is ready for use by SQL Mail. Continue with the next procedure to configure SQL Mail.

Setting the Option to Start SQL Mail Automatically

You can manually start SQL Mail using the xp_startmail extended stored procedure. It is much easier, and more reliable, to automatically start SQL Mail whenever SQL Server starts. To configure SQL Server to automatically start SQL Mail, you must use the SQL Server Setup program. You can view SQL Server options, and change most of them, using the SQL Enterprise Manager, but this is an exception.

After you have created and tested the mailbox, you are ready to use the SQL Server Setup program to configure the use of SQL Mail. To set up SQL Mail, follow these steps:

1. Run the SQL Server Setup program.

2. After the Welcome dialog box, the Microsoft SQL Server Options dialog box appears. Select the option button labeled Set Server Options. Click Continue. The Select Server Options dialog box appears (see fig. 30.17).

   Fig. 30.17 - The Select Server Options dialog box is used to select options for SQL Server, such as the Auto Start Mail Client option.

3. Click the check box to Auto Start Mail Client. Then click the Mail Login button. The Mail Login dialog box appears (see fig. 30.18).

   Fig. 30.18 - The Mail Login dialog box is used to tell SQL Server which mailbox to use for SQL Mail tasks.

4. Enter the Mail Login Name and the password; confirm the password. The first time you run this procedure, be sure to leave the check box at the bottom of the dialog box selected. This copies information from the registry to configure the mail client. Click Continue.

5. SQL Server configures the mail client and presents a dialog box with an Exit button. Click Exit.

Creating User-Defined Error Messages

1. Start the SQL Enterprise Manager and connect to the SQL Server of your choice.

2. Click the SQL Query Tool toolbar button, or choose Tools, SQL Query Tool.

3. Select the master database in the DB drop-down list box. Click the Query tab if it is not already selected.

4. User-defined error messages are created with the sp_addmessage system procedure. User-defined messages must begin at error number 50001. You can give your message a severity level of 1 through 25. Only a system administrator can define a message with severity 19 through 25, which should be used very carefully because this level represents a serious error that can shut down SQL Server. An informational message has a severity of level 10. Enter a command something like this (see fig. 30.19):

   sp_addmessage 50001, 10,
       'DataSrv needs attention. Error = 123.'

   Fig. 30.19 - The SQL Query Tool being used to create a user-defined error message.

5. Execute the query. You error message is added to the sysmessages table.

6. To test your message, you can use the RAISERROR Transact-SQL statement. You may want to create a stored procedure called test_alert, which you can use to test your newly defined error message and again to test your alert after it is defined in an upcoming section. Enter a procedure similar to the one depicted in figure 30.20.

   Fig. 30.20 - A procedure being defined that can be used to test the user-defined error message and alerts.

7. Execute the query. You can then open a new query with the New Query toolbar button and simply enter the command test_alert. This should cause an entry to be placed in the Applications log on the server if you specified the WITH LOG option when you created the test_alert procedure as it was shown in the example.

8. You are now ready to define Alerts and assign them to operators.

Defining SQL Server Alerts

To define a new SQL Server alert, follow these steps:

1. Start the SQL Enterprise Manager and connect to the SQL Server of your choice.

2. Click the Manage Alerts toolbar button, or choose Server, Alerts from the menu.

3. Click the Alerts tab if it is not already visible. The alerts that have already been defined are displayed. Click the New Alert toolbar button, the one at the far left of the toolbar. The New Alert dialog box appears (see fig. 30.21).

   Fig. 30.21 - The New Alert dialog box is used to define the properties of a new alert.

4. Enter a name for the new alert. The name you choose is arbitrary, but should be descriptive of the condition that causes it to fire. For the example, you might choose a name like "Demo Alert - Information Only".

5. You can either raise the alert based on a specific error message number or on a certain severity level. For this example, select the Severity option button and select severity 010 - Information from the drop-down list box.

6. The Database Name and the Error Message Contains String are optional constraints you can use to further narrow the situations that will cause this alert to fire. They are not used in this example.

7. In the Response Definition box, you can select a task to be executed by the SQL Executive. In this case, you will only use SQL Mail to notify an administrator, but you could define some action to attempt an automatic repair. This could, in turn, send a message based on success or failure. Leave the Task to Execute set to No Task, and enter a message in the box labeled Alert Notification Message to Send to Operator. The message should reflect the nature of the alert.

8. Select the appropriate check boxes to indicate how the message should be sent. You can include the message in an e-mail or pager notification.

   
   To use pager notification, you need to purchase a third-party software application that can initiate a page based on the receipt of e-mail in a mailbox. Several such packages exist.
   

9. Click OK to define your new alert. You are ready to continue with the next procedure.

Defining SQL Server Operators

1. If you have not already done so, start the SQL Enterprise Manager and connect to the SQL Server of your choice.

2. Click the Manage Alerts toolbar button, or choose Server, Alerts from the menu.

3. Click the Operators tab if it is not already visible. The operators who have already been defined will be displayed. Click the New Operator toolbar button, the fourth button from the left depicting a hat. The New Operator dialog box appears (see fig. 30.22).

   Fig. 30.22 - This dialog box is used to define a new operator, create a schedule for pager responsibilities (if any), and assign alerts.

4. Enter a name for the operator. The name does not need to match an e-mail ID or a Windows NT account ID.

5. In the E-Mail Notifications box, enter the e-mail alias that will receive notifications for this alert. This should be the name of a SQL Server administrator, not the name of the SQL Mail account.

6. If you have set up paging software, enter the name of the pager account.

7. Click the Test button for either type of notification if you want to send a test message to the recipient you have defined to ensure they can receive these notifications.

8. For pager notifications only, you can define a schedule during which this operator will be "on call." You can define a specific time period for any selected weekdays and separate times for Saturday or Sunday if either or both are selected.

9. In the Alerts Assigned To This Operator box at the right of the dialog box, select the type of notification that will be sent for each defined alert (if any).

10. When the schedule and the alerts have been selected, click the Send Mail button. This automatically composes a mail message informing the operator of their new responsibilities (see fig. 30.23).

    Fig. 30.23 - Clicking the Send Mail button creates an e-mail message notifying the operator of the new responsibilities assigned to him or her.

11. Click OK to finish defining the new operator. You can select the Alerts tab again to see check marks in the e-mail or pager columns indicating that the alert has been assigned to at least one operator.

Testing Your SQL Server Alert Definitions

1. If you have not already done so, start the SQL Enterprise Manager and connect to the SQL Server on which you defined the alert.

2. Click the SQL Query Tool toolbar button, or choose Tools, SQL Query Tool from the menu.

3. To test your message, you can use the RAISERROR Transact-SQL statement. You may use the test_alert procedure if you defined one in the preceding example.

4. Enter a RAISERROR statement (refer to fig. 30.20) or simply enter the command test_alert if you defined this procedure as described in the section "Creating User-Defined Error Messages" earlier in this chapter. Execute the query. This should cause an entry to be placed in the SQL Server error log and an entry in the Applications event log on the server if you specified the WITH LOG option. It should also cause an e-mail and/or pager notification to be sent to the account(s) you specified in the operator definition.

5. You can view the SQL Server error log in the SQL Enterprise Manager by choosing Server, Error Log from the menu (see fig. 30.24). Start the mail client on an appropriate machine that has a profile created for the operator who should receive the notification messages. Request delivery of new messages, and you should see the notification message from SQL Mail.

Fig. 30.24 - The SQL Server Error Log reflecting a user-defined error condition.

From Here...

You are ready! You have learned the basics and then some. Continue to explore Microsoft BackOffice, its tools, and its services. It is a rich product that has great depth. The more you investigate, the more ways you will discover to exploit its features and capabilities. If you would like to review some of the products you have learned about, consult the following list.

• For information on the Web server component of BackOffice, Internet Information Server, see Chapter 10, "Preparing for Internet Information Server."

• For information on the e-mail and groupware component of BackOffice, Exchange Server, see Chapter 12, "Understanding Exchange Server."

• For information on the database component of BackOffice, SQL Server, see Chapter 17, "Understanding SQL Server."

• For information on the host connectivity component of BackOffice, SNA Server, see Chapter 21, "SNA Server Preparation and Installation."

• For information on the systems management component of BackOffice, Systems Management Server (SMS), see Chapter 25, "Preparing for SMS."

Table of Contents

29 - Building Applications with Microsoft BackOffice

Appendix A - SNA Server Preparation Forms