Table of Contents

Chapter 10

Automating Administrative Tasks

Certification Objectives *

From the Classroom *

Alphanumeric Paging *

Creating Alerts *

Editing Alerts *

Creating Operators *

Deleting Alerts and Operators *

Creating Jobs *

Scheduling Jobs *

Job Notification *

Deleting Jobs *

Setting Up SQL Server Agent Mail for Job Notification and Alerts *

Diagnosing and Resolving Job or Alert Failures *

Certification Objectives

Database management is much more than a full-time job. Because of this, it makes sense that the administrator will try to optimize his time by not having to constantly check the status of SQL Server and other administrative duties. This chapter will discuss SQL Server Administration and Data Management. It will pay specific attention to alerts, jobs, and operators. These are immensely helpful to the administrator, because they free up time to do other things.

Managing Alerts and Operators

Alerts and operators are one of the most important parts of SQL Server Administration and Data Management. Alerts are responses to SQL Server error conditions. You can specify the specific error conditions to which your alerts will respond. An operator is the person that the alert notifies. Database administrators use them to ensure proper response is taken when random events or conditions occur.

From the Classroom

Alphanumeric Paging

SQL Server gives you the option of paging operators by sending an E-mail. If you want to send a page to an alphanumeric pager you will need to contact your pager provider (such as Skypage, http://www.skypage.com) and ask for E-mail paging. If this service is not available in your area you can purchase gateways for most E-mail systems. For example, IKON systems (http://www.ikon.com) sells a paging gateway for Microsoft Exchange.

—David Smith, MCSE + Internet

Creating Alerts

The first thing to do before creating an alert is to make sure that the SQL Server Agent is running. SQL Server Agent is a service that runs in Windows NT which allows the database administrator to automate administrative tasks, such as running jobs and raising alerts. To make sure that SQL Server Agent is running, select Start | Settings | Control Panel. Then double-click on the Services icon that shows the screen similar to Figure 10-1. Ensure that the SQLServerAgent service is started.

Figure 1: Windows NT Services Box

If the agent is not started, click on the Startup button and select Startup Type as Automatic. Click OK. You will now see the Windows NT Services Box again. Click on Start. Once you are sure SQL Server Agent Service is running, Exercise 10-1 will show you how to create an alert.

Exercise 10-1:Creating An Alert

  1. Click on Start | Programs | Microsoft SQL Server 7.0 | Enterprise Manager.
  2. Your screen should now be similar to Figure 10-2. Expand the Microsoft SQL Servers tree and click on SQL Server Group. Your server should now be displayed. My server is called MCSD. Select your server, go to the Action menu, and select New | Alert.

fig10-2.gif (11690 bytes)

Figure 2: SQL Server Enterprise Manager

Editing Alerts

Now that you are familiar with creating an alert, you must learn to configure the alert conditions. An alert condition is the event which causes alert to be raised. Once you have add the new alert, the New Alert Properties dialog box appears as shown in Figure 10-3.

Figure 3: General Tab in the New Alert Properties dialog box

The options in the General Tab of the New Alert Properties dialog box allows you to specify the criteria for the alert to be raised. The information that must be inputted to define the alert is as follows:

When you choose SQL Server Event Alert, you can choose from the following options:

You have now defined the alert condition in which the alert is raised. Proceed to the Response tab next to the General tab in the New Alert Properties Dialog box. You should see a screen similar to Figure 10-4.

Figure 4: The Response tab of the New Alert Properties dialog box

The options in the Response tab of the New Alert Properties dialog box allow you to define the responses that the operators receive from the alerts. The information that can be entered is as follows:

Exam Watch: Adding a delay between responses for a recurring alert helps to prevent multiple notifications for the same error.

Creating Operators

For alerts to function, you need to define operators. Operators are individuals that receive notifications of a raised alert or a job status. Exercise 10-2 will show you how to create an operator.

Exercise 10-2:Creating An Operator

  1. Click on Start | Programs | Microsoft SQL Server 7.0 | Enterprise Manager.
  2. Your screen should now be similar to Figure 10-5. Expand the Microsoft SQL Servers tree and click on SQL Server Group. Your server should now be showing. My server is called MCSD. Select your server, go to the Action menu, and select New | Operator.

Figure 5: General tab of the New Operator Properties dialog box

The options in the General Tab in the New Operators Properties dialog box allow you to set general information about the operator. The data that can be inputted is as follows:

You have now defined the general information for the operator. Proceed to the Notifications tab next to the General tab in the New Operator Properties Dialog box. You should see a screen similar to Figure 10-6.

fig10-6.gif (9088 bytes)

Figure 6: Notifications tab of the New Operator Properties dialog box

The settings in the Notifications tab in the New Operator Properties dialog box allows you to view the notifications set for that operator. You cannot configure the settings in the dialog box. These settings are configured when you create a job. The only configurable setting is the Operator setting, which is available to receive notifications check box, which specifies whether or not the operator will be eligible to receive notifications. There are two fields that you should be aware of:

Deleting Alerts and Operators

You can delete your unneeded alerts and operators easily in SQL Server Enterprise Manager. To delete them, double-click on the SQL Server Agent under SQL Server. In the design pane on the right side of your screen, you will see three icons as shown in Figure 10-7.

fig10-7.gif (15745 bytes)

Figure 7: Design Pane in SQL Server Enterprise Manager

Click on the icon for Alerts if you want to delete alerts. Subsequently, click on the icon for Operators if you want to delete operators. You will now see a list of all the Alerts or Operators that are in the server. Right-click the desired alert or operator and select delete.

Managing Jobs

To a database administrator, jobs can be lifesavers because they simplify the tasks for them. Jobs are tasks that contain steps. These steps are run at intervals (or at specific times) which help administer certain elements of the database.

Creating Jobs

As with creating alerts, you must ensure that SQL Server Agent is running. If you are unclear about verifying the status of SQL Server Agent, go back to and review the section of Creating Alerts. After confirming that the SQL Server Agent is running, do Exercise 10-3. This Exercise will show you how to create a job.

Exercise 10-3:Creating A Job

  1. Click on Start | Programs | Microsoft SQL Server 7.0 | Enterprise Manager.
  2. Expand the Microsoft SQL Servers tree and click on SQL Server Group. Your server should now be showing. My server is called MCSD. Select your server and go to the Action menu and select New | Job. Your screen should now be similar to Figure 10-8.

fig10-08.gif (7053 bytes)

Figure 8: The General Tab of the New Job Properties dialog box

Now that you know how to create a job, you must be familiar with setting the properties of the job. Under the General tab in the New Job Properties, there are fields that are used to name and describe the job. The fields are as follows:

You have now set the name and description of the job. Next you will create the steps of your job. Click on the Steps tab and you should see the same screen as Figure 10-9.

fig10-9.gif (5203 bytes)

Figure 9: Steps tab in the New Job Properties

The steps tab is used to create and edit the steps of the job. Steps are the smallest part of the job.They are often just a simple TSQL query. To create a step, click on the New button. The New Job Step dialog box will pop up, as shown in Figure 10-10.

Figure 10: The New Job Step dialog box

This box sets the attributes of the new job. In the General Tab, there are a few things you must fill out:

Advanced information about the job step is set in the Advanced tab. There are also a few configuration settings that you must fill out.

Exam Watch: It might be easier to remember that Job options are for success status.

Scheduling Jobs

After configuring your job, you need to know how to schedule your job. Click on the Schedules tab in the New Job Properties Dialog Box. You should see a screen similar to Figure 10-11.

Figure 11: Schedule tab in New Job Properties dialog box

To create a new schedule, click on New Schedule. Now a New Job Schedule dialog box will pop up with the following three fields:

On The Job: Running the job when the CPU is idle is ideal for non-crucial operations that require a lot of CPU activity.

Figure 12: Edit Recurring Job Schedule

Job Notification

After configuring and scheduling the job, now you must move on the job notifications. Earlier on, you saw how to view the job notification settings. Now you get to set them. Go to Notifications tab in the New Job properties dialog box, as shown in Figure 10-13.

Figure 13: Notification tab in New Job Properties Dialog Box

In this dialog box, you can set the different notifications that can be used to notify the operator when the job is complete. These types of notifications include e-mail, page, and net send. You have to set the operators these notifications will work on and the condition under which they will be notified. The conditions are as follows:

Deleting Jobs

If you no longer need a job, you can easily delete it in SQL Enterprise Manager. In Exercise 10-4, we will delete a job.

Exercise 10-4: Deleting a task

  1. In SQL Enterprise Manager, expand SQL Server group. Click on your server.
  2. Double-click SQL Server Agent, and then click on the Jobs icon.
  3. Right click on the job you created in Exercise 10-3 and click Delete.

Setting Up SQL Server Agent Mail for Job Notification and Alerts

In order for e-mail to work for job notification and alert, you must set up SQL Server Agent mail. In order to enable SQL Server Mail, you must install MAPI. One way MAPI (Mail API) is installed is when you enable Windows Messaging. Exercise 10-4 shows how you set up MAPI and SQL Server Agent Mail for Job Notification and Alerts.

Exam Watch: MAPI must be installed for SQL Server Agent Mail to work. There are many ways to obtain it, including Windows Messaging, Microsoft Exchange, and Microsoft Outlook.

Exercise 10-4:Setting Up SQL Server Agent Mail for Job Notification and Alerts

  1. Log in as an SQL Executive Account
  2. Create a directory in your c: drive called Mail.
  3. Make sure Windows Messaging and Microsoft Mail are both installed. This can be accomplished by making sure the Mail and Microsoft Mail Post Office icons are in the Control Panel as shown in Figure 10-14.
  4. fig10-14.gif (19808 bytes)

    Figure 14: Mail and Microsoft Mail Postoffice icon in the Control Panel

  5. If one or more of the icons are not there, add them in Control Panel | Add/Remove Program | Windows NT Setup tab | Windows Messaging checkbox. Click on the Details button and make sure both Microsoft Mail and Windows Messaging are checked. If not, check them. You might be prompted for your Windows NT Server CD-ROM. Click OK.
  6. Click on Microsoft Mail Postoffice. Click on Create a New Workgroup Postoffice. The next screen will tell you to put the directory where you want the mail file stored. Add the directort to c:\Mail and click Next.
  7. The next screen will tell you to create a post office user for SQL Server to use. You should see the same screen as figure 10-15.
  8. Figure 15: Enter Your Administrator Account Details dialog box

    Type in SQL7Mail for both Name and Mailbox. Leave the default password as PASSWORD and Click OK.

  9. Now go to Control Panel | Mail. You will see a screen similar to Figure 10-16.
  10. Figure 16: Microsoft Mail

  11. Click on Add. Add Microsoft Mail and click Next. Click on the path of your postoffice. Then click Next. Under logon, set the name of mailbox as SQL7Mail. Click Next. Type in a password as PASSWORD, and click on OK. Go to the Delivery tab. Make sure you type in your password and check the Remember Password checkbox.. Click OK.
  12. Go to the SQL Server Enterprise Manager. Right-click the SQL Server Agent under your SQL Server and click Properties. You should see the SQL Server Agent Properties dialog box similar to Figure 10-17. In the General tab, under Start up, click SQLMail. In the SQL Mail Configuration dialog box, enter the profile name Windows Messaging Settings in the Profile name box

Figure 17: Server Agent Properties

Diagnosing and Resolving Job or Alert Failures

When an alert fails, the majority of the errors result from three things: the operator does not exist, MAPI is not functioning correctly, or a network error occurs.

If the user does not exist, add him or her in. For a review, go back to the section on Creating Operators. When MAPI is not functioning properly, ensure the SQLServerAgent service is running. If it is running, verify that the SQL Server Executive and the E-mail user account is valid. If a network error occurs, please contact your network administrator to restore network service.

When a job fails, you can resolve it by having SQL Server run failure flow. Failure flow is an action that runs upon a failed job. You can set it by implementing the following:

  1. Right-click on the job in Enterprise Manager and select Properties.
  2. In the Job Properties dialog box, click the Steps tab, click a step, and then click Edit.
  3. In the Edit Job Step dialog box, click the Advanced tab.
  4. In the On failure action list, click the next action to perform if the job step fails.

Automating a Multiserver Environment

Multiserver jobs are used to automate administrative tasks for a multiserver environment. In order for you to create multiserver jobs, you must first declare a master server. To implement a master server, follow these steps: start SQL Server Enterprise Manager, open up the SQL Server Group, and right click the SQL Server agent. Next, click Multi Server Administration, and then click Make this a Master. The Make MSX wizard will start, as shown in Figure 10-18. The Make MSX Wizard will prompt you for information. This information is used in order for the multi-server jobs to start including Master Server, Target Server, and Multiserver Jobs.

Figure 18: Make MSX Wizard

The Master Server is the server that sends the jobs which are going to run on multiservers, called target servers. It is recommended that you set up a Master Server operator so that they are informed of the status of the multiserver job. It is also recommended that you set a non-user server so that the target servers won’t slow down the production traffic.

The Target Servers are the servers that run the jobs. A target server can only be a part of one master server. It must also be running the same version of SQL Server as the master. If you rename your computer, you must unlist it from the multiserver session and then relist it.

Exam Watch: The Master Server is like the NT Domain Controller in a multiserver environment. Everything must go through it.

Multiserver jobs are jobs that can be run on one or more non-local SQL Servers. They are run in the same way as ordinary jobs. You can create multiserver jobs from the Multiserver Jobs option on the master server and assign them to one or more target servers.

Creating Custom Administrative Tools

In order to create your own administrative tools, you must use SQL-Data Management Objects (SQL-DMO). SQL-DMO is a 32-bit library that exposes all of the SQL Server administrative functions. In order to take advantage of SQL-DMO, you must use a programming language such as Visual C++ or Visual Basic to design a front end for the administrative tools. The basic steps for using SQL-DMO are:

Certification Summary

Automating administrative tasks saves time for the database administrator. Time can be better spent on non-day to day operations. This chapter introduces built in administrator’s tool such as alerts, jobs, and operators, and gives you an overview of custom administrative tools. These custom tools play the biggest role in optimizing the SQL Server administrator’s time.

Alerts are responses to SQL Server error conditions that are specified by the database administrator. They are used to ensure that the necessary actions are taken for error conditions that will affect SQL Server. An operator is the person the alert notifies. You can notify the operator via e-mail, net send, or via a page. Jobs are tasks that SQL Server executes in intervals or on scheduled times. They are used to perform basic administrative tasks. An operator, alert, and job can be added with the SQL Server Enterprise Manager.

Custom administrative tools are built with SQL-DMO (Database Management Object). SQL-DMO is a dynamic linked library that gives programmers access to objects and properties to administer SQL Server. In order to access this library, you need to program your own front end.

After reviewing the tools found in this chapter, you should be more aware of the options and tools available to automate normal day to day administrative tasks. Only by mastering and familiarizing yourself with these tools can you become a proficient SQL Server database administrator.

Two-Minute Drill