Yesterday you looked at how to program Transact-SQL using SQL Server extensions. This included looping, the IF construct, and grouping multiple SQL statements together as a unit. You examined how to use local and global variables, and you used the raiserror statement to set the error numbers or to cause messages to be returned by your T-SQL programs. You also examined cursors and how to use them to your advantage. Remember that you generally should use cursors as a last resort.
Today you will examine SQL Server Automation. Although the SQLExecutive service is considered "optional" for SQL Server (meaning that you don't need it to run queries), the functionality provided by the service is incredibly useful. Today you'll focus on how to take advantage of this service to automate scheduled tasks, examine events, and set alerts that will cause SQL Server to respond to system and database events. You also will examine integrating Windows NT Performance Monitor with the SQL Server Alert Engine, as well as setting up email integration with SQL Server.
The first question you might ask is "Why should I automate SQL Server?" As an administrator who has done his share of 2 a.m. support calls, I can tell you that it's not much fun to get up at that time of night, and anything that a database can do to help you avoid those calls is something you should try! If SQL Server is configured properly, you can set up the server so that when problems happen, the most common errors can be intercepted and resolved without making a call. You also can automate routine database maintenance, such as data loads, integrity checking, and backups.
The SQLExecutive service enables you to fully automate this maintenance using scheduled tasks. You can schedule a single Transact-SQL command, a call to a Windows NT command file, or a replication task. Replication tasks are outside the scope of this chapter and are covered tomorrow. You can create tasks that are not necessarily run on a regular schedule but are pre-configured to run when you need them. This includes any types of tasks mentioned here.
You also can configure Alerts that can respond appropriately when a particular event occurs or a specific error message is generated. The response could be to generate email, to page an administrator/operator, or to launch a task to correct a problem.
After you decide to take advantage of automating SQL Server, your next question might be whether the proper tool to use is the SQLExecutive service. SQLExecutive is much more than a simple scheduling engine; it enables you to set up responses to error conditions and to send you email (or even page you).
As a pure scheduling engine, however, you might consider using the Windows NT schedule service with the AT command. The AT command schedules commands and programs to run on a computer at a specified time and date. The schedule service must be running in order for you to be able to use the AT command.
If you ask for help on the AT command by typing
AT /?
at the Windows NT command prompt, you get the following help:
AT [\\computername] [ [id] [/DELETE] | /DELETE [/YES]] AT [\\computername] time [/INTERACTIVE] [ /EVERY:date[,...] | /NEXT:date[,...]] "command" \\computername Specifies a remote computer. Commands are scheduled on the local computer if this parameter is omitted. id Is an identification number assigned to a scheduled command. /delete Cancels a scheduled command. If id is omitted, all the scheduled commands on the computer are canceled. /yes Used with cancel all jobs command when no further confirmation is desired. time Specifies the time when command is to run. /interactive Allows the job to interact with the desktop of the user who is logged on at the time the job runs. /every:date[,...] Runs the command on each specified day(s) of the week or month. If date is omitted, the current day of the month is assumed. /next:date[,...] Runs the specified command on the next occurrence of the day (for example, next Thursday). If date is omitted, the "command" Is the Windows NT command, or batch program to be run.
To schedule an automated backup, for example, you could type this:
AT \\MYSERVER 12:00 /EVERY:M,T,W,TH,F c:\mssql\backup.cmd
This sets up a call to the backup.cmd program in the mssql folder, which can contain calls to ISQL to perform backups. This command runs every weekday at midnight.
This is certainly an option to enable you to schedule your backups. It is also a good option if you are familiar with the AT command and scheduling tasks in this way. You also could purchase the Windows NT Resource Kit and use a graphical version of the AT command. Each of these options relies on the Windows NT schedule service to perform the scheduled tasks.
The SQLExecutive service enables you to graphically set up email integration, to call Windows NT command files, or to call Transact-SQL commands. The interface is much more intuitive than trying to figure out the AT command and writing batch files.
The SQLExecutive service consists of a set of programs that work together to automate your SQL Server. The service is made up of several pieces, four of which you will look at here:
Each of these engines work together to automate your server. When an event occurs that is written to the Windows NT Application Event Log, the Event Engine captures the message and passes it to the Alert Manager. The Alert Manager determines whether the alert is one that it has been configured to watch for, and if so, it may launch a scheduled task or send an email/page. The task also may then cause a message to be written to the Event Log, and the cycle starts over again.
How you configure SQL Mail depends on which version of Windows NT you are using, as well as which mail client you have installed. For the purposes of this book, it is assumed that you are running Windows NT 4.0 with the Windows Messaging client that comes with Windows NT, the Microsoft Exchange client that comes with Microsoft Exchange Server (any version), or the Microsoft Outlook client that comes with Microsoft Office 97.
Setting up email integration with SQL Server enables you to send and receive email from SQL Server. This includes the option of having SQL Server email (or page) you when a system problem occurs, as well as the capability to send SQL Server queries through your mail system. You also can have SQL Server email reports so that you don't have to find them in a network share or even have a connection to the office to check up on your server.
To implement SQL Mail, you first must determine what kind of mail server you want to connect with. You will examine two types of mail servers here: Microsoft Mail/Internet mail, and Microsoft Exchange Server.
To implement Microsoft Mail or Internet mail (using a POP3 mail server), you first must install the Windows Messaging software on your Windows NT computer. If you aren't sure you have it installed, choose Start | Settings | Control Panel and then double-click Add/Remove Programs. Select the Windows NT Setup tab, and verify that the Windows Messaging option has been checked (see Figure 18.1). If the option has not been selected, check it now. The software then is loaded from your Windows NT CD-ROM.
Figure 18.1. The Windows NT Setup tab.
WARNING: It is imperative that you apply Windows NT 4.0 Service Pack 3 or higher before you go any further at this point. There was a problem with the mail software that shipped with Windows NT 4.0 that was fixed in Service Pack 3 that prevented mail sent by SQL Server from actually being sent from the mailbox. If you install the Windows Messaging software after you install Service Pack 3, you must reapply the Service Pack to update the messaging software. To determine whether your system has Service Pack 3 installed, choose Start | Programs | Administrative Tools | Windows NT Diagnostics and examine the Version tab. You should see the following text onscreen:Version 4.0 (Build 1381: Service Pack 3)If you don't see Service Pack 3 on this tab, you need to apply the Service Pack.
After you have the proper software installed, you must change the configuration of the MSSQLServer service. The MSSQLServer service must be running under a user account in order for the mail implementation to function properly in Windows NT 4.0. If you are using Windows NT 3.51 using the Microsoft Mail product that shipped with that version of Windows NT, you can find setup directions by searching Technet or the Microsoft Knowledge Base. That version of Windows NT has different requirements for this setup.
To create an account for the MSSQLServer service to use for mail, you should run the User Manager or User Manager for Domains utility. If you are running Windows NT Server, you should have the User Manager for Domains utility. You can find this utility by choosing Start | Programs | Administrative Tools (Common) and then clicking the appropriate option to start the utility.
After the program starts, choose User | New User to access the New User dialog box (see Figure 18.2).
Figure 18.2. The New User dialog box of User Manager for Domains.
Add an account to your system with a name that complies with your security policies. To continue with this example, create an account named SQL Mail with a password of password. Uncheck the User Must Change Password at Next Logon option and check the Password Never Expires option. Click Groups, and add the SQL Mail account to the Administrators group on the server. The Group Memberships dialog box should look like Figure 18.3.
Figure 18.3. The Group Memberships dialog box for SQL Mail.
Click OK, and you should see a New User dialog box that looks similar to Figure 18.4.
Figure 18.4. The New User dialog box for SQL Mail.
Click Add to complete the creation of this user account. The account must be a member of the Administrators group to ensure proper operation of the MSSQLServer service.
The next step is to verify that you have set up the account properly by configuring the MSSQLServer service to use the new account. Choose Start | Settings | Control Panel to access the Control Panel, and double-click the Services icon to access the Services window. Double-click on the MSSQLServer service to view the Service dialog box (see Figure 18.5).
You should see that the service is logging in under the system account (as part of the operating system). To change that, select the This Account option and click the ellipsis button to the right to view a list of user accounts. Select the SQL Mail account you just created, click Add, and then click OK to return to the Services dialog box. You should see the account listed here (as DOMAIN\SQL Mail or COMPUTER\SQL Mail, depending on your network environment). Change the password by highlighting all the asterisks in the Password field and typing the correct password (password, in the example here). Do that again in the Confirm Password dialog box. Then click OK to complete the change. You probably will see the message box shown in Figure 18.6.
Figure 18.5. The Service dialog box for the MSSQLServer service.
Figure 18.6. The Services Permissions message box.
This simply means that the SQL Mail account has been given the right to log in when running a service, such as the MSSQLServer service. Stop and start the MSSQLServer service so that it will be running under the SQL Mail account.
Now that you have the MSSQLServer service running under the user account, you can configure a mail profile for the user. Log in to the computer with SQL Server installed as the SQL Mail account you just created. Double-click the Inbox icon on your desktop, and the Windows Messaging Setup Wizard begins (see Figure 18.7).
Figure 18.7. The Windows Messaging Setup Wizard.
You can select Microsoft Mail, Internet Mail, or both. For now, you'll follow the path of the Internet Mail option. After you click Next, you are given the option of specifying whether you have a dial-up connection or a network connection to the Internet. You will use a network connection, so click Network and then Next. On the next step of the wizard, you are asked to configure the name or the Internet address of your mail server (see Figure 18.8).
TIP: If TCP/IP isn't installed, you receive an error message after clicking Next here. You first must install TCP/IP before you can configure a mail connection to the Internet.
Figure 18.8. Prompts for your name or Internet address.
You need to get the appropriate information for your mail server from your Internet mail provider. After you properly fill out this screen, click Next. Then you must specify whether you want your mail to be downloaded and sent automatically or whether you want to use the remote mail option. For SQL Server, select the Automatic option (see Figure 18.9).
Figure 18.9. Automatically downloading the mail option.
Click Next again (don't you love wizards?) and enter your email address and name as requested. Click Next and then fill in the name of your mailbox and your password provided by your Internet Service Provider (see Figure 18.10).
Figure 18.10. Using the Internet Mail option.
Next, specify the Personal Address Book for the SQL Mail account. For this example, accept the default option (see Figure 18.11).
Figure 18.11. The Personal Address Book option.
Then click Next and configure the location of your personal folders to hold messages for the SQL Mail account (see Figure 18.12). Again, accept the default location for this example.
Figure 18.12. The Personal Folders option.
Finally, after you click Next, you see the Done! dialog box (see Figure 18.13).
Figure 18.13. The Done! dialog box.
Click Finish, and you are logged in to your mail client.
You also might want to install a Microsoft Mail client. To do this, check Microsoft Mail on the first screen of the Windows Messaging Setup Wizard. You then see the dialog box in Figure 18.14 requesting that you specify the location of the Microsoft Mail postoffice. Typically, this is a network location; you need to consult your mail administrator to get the proper location.
Figure 18.14. Specifying the network location of the postoffice.
You also can use the Microsoft workgroup postoffice that ships with Windows NT. You will set up a Microsoft workgroup postoffice as an exercise at the end of this day. You then are prompted to select the mailbox for this profile (see Figure 8.15).
Figure 18.15. Selecting your mailbox.
After you select your mailbox, enter your password for this account (see Figure 18.16).
Figure 18.16. Entering your email password.
Click Next; you see the same options you saw with the Internet Mail client for a Personal Address Book (.pab file) and then a personal folder (.pst file).
After you finish with the wizard, you see the opened Mail client, as shown in Figure 18.17.
Figure 18.17. The Windows Messaging Inbox.
You might want to send a test message to yourself to make sure that the configuration is working properly. After you finish testing, you should delete all entries from your Inbox. Then, choose File | Exit and Log Off to exit the Windows Messaging client.
The next step is to set the name of the SQL Mail profile. Right-click the Inbox icon on your desktop, and choose Properties from the pop-up menu. You see the properties of your Windows Messaging profile (see Figure 18.18).
Click Show Profiles to see the names of the profiles installed for this user. You should see one entry: the Windows Messaging Settings profile. Highlight this profile and click Copy to make a copy of the profile. When prompted to name the profile, change the name to SQL MAIL. Now highlight the Windows Messaging Settings profile and click Remove.
Click Yes in the Windows Messaging confirmation dialog box, as shown in Figure 18.19. You now should see one profile: the SQL Mail profile. Click Close and then log out of Windows NT and log back in as yourself. Start up SQL Enterprise Manager, connect to your server, and right-click the SQL Mail icon (the red envelope). Choose Configure from the pop-up menu, and enter the name of the profile you created earlier (see Figure 18.20). This should be SQL Mail if you are using the same names used here.
Figure 18.18. The Windows Messaging Settings Properties dialog box.
Figure 18.19. Removing a profile.
Figure 18.20. The SQL Mail Configuration dialog box.
Click OK, right-click the SQL Mail icon again, and choose Start. Mail now is enabled for SQL Server, and you can send and receive email!
To configure SQL Server 6.5 to integrate with Exchange Server, you perform exactly the same configuration steps as you did earlier today, with a few exceptions:
To make the changes permanent, run the SQL Server Setup program. On the main options screen, click Set Server Options. While in this dialog box, check the Auto Start Mail Client option and click Change Options (see Figure 18.21).
Figure 18.21. The Select Server Options dialog box.
Enter the profile name of your mail client in the dialog box that appears. Enter the same profile name you entered in SQL Enterprise Manager and click Continue to change the option. After the email client is configured in this manner, it starts automatically each time the MSSQLServer service is started.
To send email from SQL Server, you use the extended stored procedure xp_sendmail (be careful; it's case sensitive).
WARNING: Be careful with the following code. The syntax in Books Online is incorrect--the first parameter, @recipients, is listed as @recipient in the Books Online.
xp_sendmail @recipients = recipient [; recipient2; [...; recipientn]] [, @message = message] [, @query = query] [, @attachments = attachments] [, @copy_recipients = recipient [; recipient2; [...; recipientn]]] [, @blind_copy_recipients = recipient [; recipient2; [...; recipientn]]] [, @subject = subject] [, @type = type] [, @attach_results = {`true' | `false'}] [, @no_output = {`true' | `false'}] [, @no_header = {`true' | `false'}] [, @width = width] [, @separator = separator] [, @echo_error = {`true' | `false'}] [, @set_user = user] [, @dbuse = dbname]
There are a number of options here, but you will focus on the most important ones here:
As with any stored procedure, you can pass parameters by name or by position. The following code, for example, would send you an email with a subject of test mail:
xp_sendmail @recipients = `your email', @subject = `test mail'
You also could send the message without specifying the parameters (by specifying them in order):
xp_sendmail `your email', `This is a message from SQL Server'
SQL Server can receive email as well. You can send a query to SQL Server and run the sp_processmail stored procedure to process incoming messages. If the mail message is formatted properly, the body of the message is interpreted as a query, the query is run, and the results are sent back to the user as a text file attachment.
sp_processmail [@subject = subject] [[,] @filetype = filetype] [[,] @separator = separator] [[,] @set_user = user] [[,] @dbuse = dbname]
Here,
So, if you send a message to SQL Server's mailbox with a message body of
Select au_fname, au_lname from pubs..authors
and then log in to SQL Server as SA and run the query
exec sp_processmail
Your message is processed, the query is run, and a mail message is returned to you with the results of the query as a text file attachment.
As mentioned earlier, scheduled tasks enable you to set up batch operations ahead of time and then run them later--either on a fixed schedule or on demand. Tasks typically are configured by the SA user to perform system-maintenance operations. This can include backups, checking database consistency, and reindexing tables.
Setting up scheduled tasks occurs in three steps: you create the task, set the schedule for the task, and then set task options. To begin, start Enterprise Manager if it's not already running. Then choose Server | Scheduled Tasks after you connect to your server. The Manage Scheduled Tasks dialog box appears (see Figure 18.22).
Figure 18.22. The Manage Scheduled Tasks dialog box.
To create a new task, click New Task (it's the button with the clock with the gold star on it). The New Task dialog box appears (see Figure 18.23).
Figure 18.23. The New Task dialog box.
As you can see, this is the same dialog box you saw on Day 8, "Backing up and Restoring," when you scheduled a backup task. Now examine each of the options here:
The Name field identifies the name of the task. The name must be unique among the tasks, but it isn't required to be a valid SQL Server identifier.
The Type drop-down list box specifies the type of scheduled task you are creating. There are five types of tasks you can create:
The Enabled check box specifies whether a task is allowed to run at its scheduled interval. If the check box is not enabled, a task will not run.
The Database drop-down list box specifies the database context in which a scheduled task will run. This option is available for all tasks except CmdExec tasks.
If you specify a task type of CmdExec, the Database field does not appear; instead, a new field, Process Exit Code of a Scheduled Task, appears. The default value of 0 specifies that the exit code of a Windows NT .cmd file should be 0 if the command was executed successfully.
The Command field specifies that the command that will be run when the scheduled task is to be executed. For a CmdExec task, this is the name of a program or a .cmd file, including any parameters or command-line switches you want to submit. If the task type is TSQL, the Transact-SQL command (or set of commands) goes here. The Command field can contain a maximum of 255 characters and may include multiple batches.
The Schedule section is where you specify a schedule:
The Add button creates the task as specified in this dialog box.
You'll learn about the Options button a bit later today.
The Cancel and Help buttons perform as expected here.
Select the Recurring option in the Schedule section and then click Change to modify the default schedule. The Task Schedule dialog box appears (see Figure 18.24).
Figure 18.24. The Task Schedule dialog box.
The Task Schedule dialog box enables you to specify the schedule for a task. The Occurs section specifies whether the task occurs daily, weekly, or monthly:
The Daily Frequency frame specifies at what time of day the task will run. You can set up the Occurs Every option to run a scheduled task every x number of hours or minutes. You also can specify a start time and end time in which the Every schedule option should run.
The Duration frame specifies a start date (which defaults to the current date) and an end date (or the No End Date option). If you want to have a scheduled task that runs only for some duration and then stops running, you can set the end date. When that date is reached, the task does not run again.
After you set the schedule options, you can click OK to return to the New Task dialog box. Next, click Options to display the Task Options dialog box (see Figure 18.25).
Figure 18.25. The Task Options dialog box.
The Task options enable you to specify what should happen when a scheduled task runs. The Notifications frame specifies how you are notified that a task has succeeded or failed. The first option is to specify the Email Operator. You will set up email operators later today. After you specify an email operator, you can choose to have the appropriate email sent on success, on failure, or both. You also can write to the Windows NT Event Log on success or failure. The default behavior is to write a message to the Windows NT Event Log on failure, but you probably should check the On Success option as well. If a task is important enough to schedule, it's important enough to know whether your task succeeded.
The Retries option specifies how retry attempts are handled for a task. The Retry Attempts option specifies how many times the SQLExecutive service resubmits a task if it fails to run properly. Retry Delay specifies how long (in minutes) the SQLExecutive service waits after a task fails before the task is resubmitted.
The LogReader task option enables the Replication section of this dialog box. Here, you can specify the server name and database name of the server you want to run the LogReader task against. Normally, you do not set up a LogReader task manually.
After you configure the task options, click OK and then Add to create the new task. You now see the task in the Task List window. To edit the task, highlight it and click Edit Task (the button with the clock with the magnifying glass on it), or simply double-click on a task. The Edit Task dialog box appears (see Figure 18.26).
Figure 18.26. The Edit Task dialog box.
You will notice that there's one new button here that wasn't available when creating the task: History. After you click History, you see a list of each time the task was run, whether it was successful, and any output from the command. You also see how long the command ran and which email operator was notified about the task (see Figure 18.27).
Figure 18.27. The Task History dialog box.
You can clear out this history by clicking Clear All; this removes all history of when this task ran, and success messages are removed. To exit the dialog box, click Close, and then close the Edit Task dialog box.
To delete a task, simply highlight the task in the Manage Scheduled Task window, and click Delete Task (the button with the clock with the red stripe across it).
You also can set a number of Task Engine options. In the Manage Scheduled Tasks window, click the last icon on the toolbar to the right (the one that looks like a car) to access the Task Engine Options dialog box (see Figure 18.28).
Figure 18.28. The Task Engine Options dialog box.
You can choose to limit the size of the history kept for all scheduled tasks on your SQL Server installation. There are two options here: to limit the total number of history records or to limit the maximum amount of history kept for each task. The defaults are 1000 and 100, respectively.
If you disable the Limit Size of Task History Log option, the history of your tasks is kept forever. This task history is kept in the syshistory table of the msdb database. Therefore, if you change these parameters or turn off the maximum size options, you most likely will need to increase the size of the msdb database. The task definitions are kept in the systasks table in the msdb database.
One of the most useful tasks you will create is with the Database Maintenance Plan Wizard. This wizard creates a maintenance plan for your databases and sets up one or two scheduled tasks (depending on the options you select when you run the wizard). To start the wizard, open Enterprise Manager, connect to your server, and then click the last button on the right of the toolbar (a database with a wand pointed at it). This brings up the Database Maintenance Plan Wizard introduction screen (see Figure 18.29).
Figure 18.29. The Database Maintenance Plan Wizard introduction screen.
Change the target database name to pubs and click Next. You then see two sections about the data volatility and the data growth for this database (see Figure 18.30). Select the appropriate values for this database (for pubs, use the default).
Figure 18.30. The About the Data in Your Database dialog box.
Here you decide how often certain maintenance activities, such as index reorganizations, will occur (daily or weekly). After you set the proper values, click Next to view the Data Verification dialog box (see Figure 18.31).
Figure 18.31. The Data Verification dialog box.
Here you select which type of DBCCs you want to run against the database. This includes the DBCC CHECKDB, DBCC NEWALLOC, DBCC TEXTALL, and DBCC CHECKCATALOG commands (in order as they appear in the Data Verification Tests frame). At the bottom of the window, you can specify whether you want the verifications to run daily or weekly. Select The Daily Maintenance Plan for pubs. Click Next to view the Data Optimization dialog box (see Figure 18.32).
Figure 18.32. The Data Optimization dialog box.
Here you specify which optimizations you want to run against your database. The first option runs UPDATE STATISTICS on all your tables. The second option (Reorganize Data and Index Pages) runs the DBCC DBREINDEX command against your tables. It's a good idea to change the default and set it to maintain the original free space. Be careful here; the Change Free Space To option is the opposite of fill factor; free space of 10 percent means a fill factor of 90 percent on your indexes!
WARNING: If you do choose to use the Maintain Original Free Space option, make sure that you have Service Pack 3 for SQL Server 6.5 applied. Before Service Pack 3, the program that runs for this wizard (sqlmaint.exe) had a problem and would not keep the original free space. If you don't have Service Pack 3 (or higher) applied, you should not use this option.
After you fill out your selections and click next, you see the Data Protection dialog box (see Figure 18.33).
Figure 18.33. The Data Protection dialog box.
Here you can specify that you want the wizard to configure backups for you. If you change the backups to daily, the Only Do the Backup If option becomes available. This is one of the best features of this utility; if your database is corrupt, it does not perform a backup. Check this option and click next to move to the Backup Destination dialog box (see Figure 18.34).
You have the option of specifying where the backups will be written. Click Next to see the When to Run, Who to Notify dialog box (see Figure 18.35).
Specify the time to run the daily plan. For the weekly plan, you also must specify the day of the week on which to run the plan. The maintenance program generates a report, and you can change the name and location of the report with this dialog box. You also can specify an email operator (you'll set one up shortly). Click Next to finish the wizard (see Figure 18.36).
Figure 18.34. The Backup Destination dialog box.
Figure 18.35. The When to Run, Who to Notify dialog box.
Figure 18.36. The Wizard Complete! dialog box.
You can print this dialog box to get a report of what maintenance will be done on your database at which times. Click Done to exit the wizard, and when prompted to print the results of the wizard, choose No. You then see a message asking whether you want to run the daily or the weekly plan. Choose Done to close the wizard (see Figure 18.37).
Figure 18.37. Closing the wizard.
Now, if you access the Manage Scheduled Tasks dialog box (choose Server | Scheduled Tasks from the menu in Enterprise Manager), you see two new tasks added (see Figure 18.38).
Figure 18.38. Tasks created by the Maintenance Wizard.
If you edit one of these tasks, you will see that the task is calling SQLMaint.exe with several command-line parameters. You can find additional information about SQLMaint.exe on Day 3, "SQL Server Tools and Utilities."
An Alert specifies that when a particular condition occurs, SQL Server will generate a response. This response can be an email to an operator, a page to an operator, or a task starting.
Alerts are initiated when the Event Manager component of the SQLExecutive service reads a message from the Windows NT Application Event Log. The message in the Event Log must have a source of SQL Server. The Alert manager component looks for an Alert you have configured that is associated with a message. If one exists, the appropriate response is initiated.
The first step in setting up an Alert is to configure an operator. You do this with the Manage Alerts and Operators interface. To access this dialog box choose Server | Alerts/Operators from the menu of Enterprise Manager when connected to your server. Select the Operators tab to configure operators for your server (see Figure 18.39).
Figure 18.39. The Manage Alerts and Operators dialog box.
To create a new operator, click the fire hat (how appropriate!). You see the New Operator dialog box (see Figure 18.40).
Figure 18.40. The New Operator dialog box.
To set up an operator, complete each field as described here:
After you enter the email name, you can send a test message by clicking Test. This sends an email to the operator identifying the message as a test message that is to be ignored. If the test mail fails for some reason, you should get an error here.
TIP: To check mail from SQL Server, you probably should use a separate account instead of the mail integration account you just configured. If you choose to have SQL Server process mail with the sp_processmail stored procedure, having other email in the Inbox of the SQL Server mailbox can confuse SQL Server (and hence, possibly not process your mailed-in queries).
Click OK to exit the dialog box and complete the creation of a new operator. To edit an operator after it's created, click Edit Operator (the fire hat with the magnifying glass). The dialog box that appears looks just like the New Operator dialog box. To delete the operator, highlight the operator in the dialog box and click Delete Operator (the fire hat with the x through it). Operators are stored in the sysoperators table in the msdb database.
After you successfully configure your email operator(s), you can configure Alerts. Select on the Alerts tab to view the Alerts that are configured on your server (see Figure 18.41).
Figure 18.41. The Alerts tab.
You will see that several Alerts are pre-configured on your server. These alerts are pre-configured to cover the most severe errors that can occur in SQL Server. To create a new Alert, click New Alert on the toolbar (the red exclamation point). The New Alert dialog box appears (see Figure 18.42).
Figure 18.42. The New Alert dialog box.
To set up an alert, complete each field as described here:
To create an Alert that you can test, select the Error Number option and click the button next to the Error Number field. This brings up the Manage SQL Server Messages dialog box (see Figure 18.43).
Figure 18.43. The Manage SQL Server Messages dialog box.
You can find a message in a number of ways, and you can even create a new one here. For now, enter an error number of 208 and then click Find. You should find error number 208, Invalid object name `%.*s'. An important requirement for Alerts is that the message you want to set an Alert on must appear in the Windows NT Application Event Log in order for the Alert to function. You can tell whether a message will be recorded in the Event Log by looking at the Logged column in the output. By default, this error message is not logged. Click Edit to edit this message (see Figure 18.44).
Figure 18.44. The Edit Message dialog box.
Check the Always Write to Windows NT Eventlog option so that when this error occurs, the message will appear in the Event Log. Click OK, and you see a blue check mark in the Logged field for this message. Click Select to accept this selection, which then returns you to the New Alert dialog box. Name the task (call it Object Not Found). Select the master database in the Database Name drop-down list box, and enter Oops in the Alert Notification Message to Send to Operator field. Check the Include Error Message Text in Email option. Also check the option to send email to your operator in the Operators to Notify window. The task now should look like Figure 18.45.
Figure 18.45. The Configured New Alert.
Click OK to create the task.
Now you can test the Alert you just created. Start a query window (in Enterprise Manager with the SQL Query Tool or using ISQL/w) and switch to the master database. Enter the following query:
Select * from whatever
Because you don't have a table called whatever, you should receive error 208:
Msg 208, Level 16, State 1 Invalid object name `whatever'.
When this error occurs (and you have set it as a logged error), a message should appear in your Event Log. Choose Start | Programs | Administrative Tools (Common) | Event Viewer to start the Event Viewer, and then switch to the Application Log (choose Log | Application if you are not already viewing the Application Log). Double-click the first entry with the stop sign; it should be your 208 error (see Figure 18.46).
Figure 18.46. The Event Detail dialog box.
If an error doesn't appear in the Windows NT Application Event Log, the Alert is not notified that the error has occurred. Close the Event Viewer and switch back to the Manage Alerts and Operators dialog box inside Enterprise Manager. Click Refresh, and notice that the Last Occurred value for your Alert has changed (see Figure 18.47).
Figure 18.47. The updated Manage Alerts and Operators dialog box.
Start up your mail client, and you see the message sent to you by SQL Server. You might have to choose Tools | Deliver Now to get the mail message delivered to your Inbox (see Figure 18.48).
Figure 18.48. The message from SQL Server.
Congratulations! You've successfully set up an Alert and had it send you mail.
You can configure three sets of options for Alerts by clicking the Alert Engine Options icon (the icon of the car) in the Manage Alerts and Operators dialog box (see Figure 18.49).
Figure 18.49. The Alert Engine Options dialog box.
You can configure a failsafe operator here. If something goes wrong with an Alert, you can designate an operator to receive a message about the notification failure. To do so, select an operator from the Operator to Notify drop-down list box. You also can create an operator here by selecting the New Fail-Safe Operator option, which launches the New Operator dialog box. To not specify a failsafe operator (the default), select No Fail-Safe Operator.
The bottom frame, Unhandled SQL Server--Event Forwarding Server, specifies another SQL Server in your organization that will receive events that occur on your server. The idea behind this option is that you can choose to forward events from your server to a central SQL Server system. That central SQL Server can be configured with Alerts for multiple servers, and then can be centrally controlled. This can offload a considerable amount of work to the central server; however, it also may slow down the speed of response to events on your server, because the events will need to be forwarded to the central server.
Configuring an event-forwarding server also increases network traffic to the central server as more messages are sent to that server. Therefore, you don't want to specify an event forwarding server that's already busy handling a production database.
The Forward Events for Errors with Severity of or Above option enables you to configure that only errors with a severity level of the level you specify (or higher) will be forwarded to the event-forwarding server. Only unhandled events will be forwarded; this means that if you configure an Alert locally that responds to an event, it will not be forwarded. Table 18.1 explains the severity levels.
Level | Indicates That |
Severity 0-10 | These messages are considered to be informational. |
Severity 11-16 | A user can fix the errors. |
Severity 17 | Insufficient resources, such as running out of locks. |
Severity 18 | An internal error has occurred in SQL Server. The error is non-fatal, and your connection to the server is not interrupted. |
Severity 19 | An internal, non-configurable resource has been exceeded. |
Severity 20 | A fatal error has occurred with your connection. |
Severity 21 | A fatal error has occurred in your connection that affects all connections to a database. |
Severity 22 | The table or index you are using has become corrupted. |
Severity 23 | The database you are using has been corrupted by a hardware or software problem. |
Severity 24 | Some kind of hardware-level error has occurred. |
Severity 25 | An internal system error has occurred. |
As you can see, errors with a severity level higher than 18 are really nasty errors. These errors often result in a call to Microsoft product support.
The Pager Email tab of the Alert Engine Options dialog box enables you to specify email prefix and suffix information if your email and pager integration software requires special handling. The configuration on this screen depends completely on your paging software.
After you configure your Alert Engine options, click OK to set them.
You can view the Alerts in the sysalerts table in the msdb database. The sysoperators table holds the operators you have created, and the sysnotifications table records each time an Alert notifies an operator and how the operator was notified (by email or pager).
One of the most interesting options available to you with SQL Server's integration with Windows NT Server is the capability to monitor SQL Server with the Performance Monitor utility. On Day 20, "Performance Tuning and Optimization," you will examine how to use the Performance Monitor utility to monitor key components of SQL Server. Today, however, you will examine how to use Performance Monitor to cause an Alert to occur in SQL Server.
The Performance Monitor Alert you will set is a trivial one; it will occur whenever your CPU usage exceeds 50 percent busy. To set up a Performance Monitor Alert, you first must define a SQL Server message to call. Start SQL Enterprise Manager if it's not started and connect to your server. Choose Server | Messages to view the Manage SQL Server Messages dialog box. This dialog box is the dialog box you looked at when you set up your previous Alert (on error 208). Now, you will create a new message for this test. Click New to create a new message (see Figure 18.50).
Figure 18.50. The New Message dialog box.
SQL Server enables you to create your own messages; however, these messages must start with a message number greater than 50,000. By default, you then can start creating your own messages. The first number will be 50,001. Keep the default severity level (10), and set the message text to Oh-oh, CPU is getting busy. Make sure that you check the Always Write to Windows NT Eventlog check box so that this message will be logged in the Windows NT Application Event Log. Click OK to add the message to the sysmessages system table in the master database. Click Close to exist the Manage SQL Server Messages dialog box.
Choose Server | Alerts/Operators to access the Manage Alerts and Operators dialog box. Add a new Alert as you did earlier today by clicking New Alert, and call the alert CPU is busy. Set the Alert to error number 50,001 (the message you just created), and select yourself as the operator to notify via email. Also check the Include Error Message Text in Email check box. Click OK to add the Alert.
The next step in configuring your Performance Monitor Alert is to start the Windows NT Performance Monitor application. Choose Start | Programs | Administrative Tools (Common) | Performance Monitor to start the Performance Monitor application. Choose View | Alert to change to the Alert view. Click the Plus sign on the toolbar to view the objects and counters you can add to the Alert view (see Figure 18.51).
Figure 18.51. Adding a counter to an Alert.
You will add the default counter displayed here: the Processor object, the % Processor Time counter, and instance 0. Instance 0 here means CPU number 0 (if you have more than one CPU, you will see additional numbers here). In the Alert If frame, make sure that the Over option is selected, and enter the number 50. This means that this Alert will monitor this counter and the Alert will be fired when your CPU exceeds 50 percent busy.
The magic of using the Performance Monitor occurs in the Run Program on Alert frame. Here you specify the program you want to run when the Alert occurs. You also specify whether you want the program to be called the first time the Alert is generated or every time. The program you will enter here is the sqlalrtr.exe program.
sqlalrtr -E error_number [-S server_name] [-P password] [-D database_name] [-V severity] [-T] [-M]
The -E error_number option identifies which error number will be written to the Windows NT Event Log.
The -S server_name option identifies on which server the Alert will be raised. If not specified, it is assumed that you are posting to the local server.
The -P password option is the SA password for the server to which you are posting the message. If you don't specify this parameter (and don't specify the -T parameter), the -T parameter is assumed.
The -D database_name option specifies that the Alert will identify a particular database. Some Alerts that are database specific should use this parameter to inform SQL Server which database was affected by the Alert.
The -V severity option specifies the severity level for the Alert.
The -T option specifies that a trusted connection should be made from the Performance Monitor utility. The user running Performance Monitor needs to have SA rights on your server. See Day 6, "SQL Server Login and User Security," for more information about setting up trusted connections.
The -M option enables you to send parameters for messages.
The sqlalrtr program's parameters are case-sensitive and must be entered in capital letters.
The sqlalrtr program posts a message to the Windows NT Application Event Log by logging in to SQL Server with ISQL and running the SQL Server command RAISERROR with LOG for the error number you specified in the call to the program. You cannot call ISQL directly from Performance Monitor; you must use the sqlalrtr program instead.
For your command, enter
Sqlalrtr -E50001 -P -Dmaster -V10
Click Add to complete the Alert and then Done to close the Add to Alert dialog box. That's everything you need to do to configure a Performance Monitor Alert. Choose Options | Alert to reconfigure the Alert interval (how often Performance Monitor checks for your Alert condition). The default is every 5 seconds; change that to every 1 second, and click OK to close the dialog box. Choose File | Save Alert Settings and save the Alert you configured as a file on your computer (name the file myalert.pma).
To cause the Alert to fire, choose Start | Help. This should send your CPU usage well over 50 percent. To see whether the Alert has fired, switch to Enterprise Manager, access the Manage Alerts and Operators dialog box again, and refresh the window. You will see the Last Occurred value change for the Alert you created. If you check your email client, you will find a message similar to the following in your Inbox (see Figure 18.52).
Figure 18.52. The Alert email message.
The SQLExecutive service is not required when using SQL Server, but it can dramatically increase the functionality of your server. You can set up tasks to automate functions such as backups and database integrity checks. You can set up Alerts to react when events occur on your SQL Server databases, and you can even use the Windows NT Performance Monitor tool to call Alerts in SQL Server. You also saw the power of integrating email and paging features with SQL Server to notify an administrator (or set of administrators) when an Alert occurs or when a task is executed.
When taking advantage of SQL Server integration with these features and a little bit of planning, you can dramatically improve the reliability and responsiveness of your server to problems. Planning ahead for the more common errors that can occur (and setting up tasks to respond to those errors) can dramatically reduce the unavailable times of your server and make your users much happier with the job you're doing as well.
The Workshop provides quiz questions to help you solidify your understanding of the material covered, as well as exercises to provide you with experience in using what you've learned. Try to understand the quiz and exercise answer before continuing on to the next day's lesson. The answers are provided in Appendix B, "Answers."
1A. The AT command and the Windows NT Schedule service
2A. The name of the mail profile of the Windows NT user account that is being used to run the MSSQLServer service
3A. Exec sp_processmail
4A. Two scheduled tasks (1 for the with init command, one without that runs every 3 hours)
2. Configure a Performance Monitor Alert (such as the Transaction Log for a database reaching 80 percent) and then configure an Alert in SQL Server to respond to the Performance Monitor Alert. This requires that you create a new error message and a new Alert, as well as finish integrating email with SQL Server.
© Copyright, Macmillan Computer Publishing. All rights reserved.