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 *
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.
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
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
Figure 2: SQL Server Enterprise Manager
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.
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
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.
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:
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.
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.
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
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.
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.
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
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:
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
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
Figure 14: Mail and Microsoft Mail Postoffice icon in the Control Panel
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.
Figure 16: Microsoft Mail
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:
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 wont 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 administrators 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 administrators 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