Table of Contents

Chapter 10

Automating Administrative Tasks

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.

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.

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.

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.

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.

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.

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:

• Name The name of the alert. This name can be any name you wish.
• Type The type of alert available. You can choose from SQL Server Event Alert or SQL Server performance condition alert.

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

• Enabled Used to specify whether the alert is disabled or enabled.
• Error Number If this option is set, then the alert will be raised when the specific error number occurs. The specific error number is selected when you click the … button.
• Severity If this option is set, then the alert will be raised when the specific severity of the error occurs. You can also choose the specific severity to occur to raise the alert.
• Database Name The name of the database SQL Server is watching to raise an alert.
• Error message contains this text This option further checks the specific text within the error messages.

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.

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:

• Execute Job Check this if you want the alert to run a job such as a program or batch file.
• Raise corresponding SNMP Trap when alert occurs This setting is automatically set for you depending on whether or not you have enabled SNMP support in SQL Server.
• Operators to notify This field is used to specify which operator will be notified when the alert is raised. The field consists of four columns in order as follows: the first one is the name of the operator, the second is for enabling E-mail notification, the third is for enabling pager notification, the fourth one is for net send notification. For more information on SQL Mail including setup instructions, see Chapter 6.
• Include alert error text in This allows you to include the error text in the different alerts such as E-mail, pager, and net send.
• Additional notification message to send to operator This allows you to send an optional message.
• Delay between responses for a recurring alert A delay can be setup between alerts being forwarded to your operators. Some events can happen many times a minute. Sending an E-mail or a page would only upset the person trying to solve the problem.

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.

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.

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:

• Name The name of the operator.
• E-mail name The name of the person to e-mail as set in the address book. You can test the E-mail by click on the Test button.
• Pager e-mail name The name of the person to page as set in the address book. You can test the pager by clicking on the Test button.
• Net send address The name of machine or user to notify. You can test the net send by clicking on the Test button.
• Pager on duty schedule The hours that the operator can be notified.

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.

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:

• Alerts that this operator will receive notifications for This setting shows the types of notifications that are used for each alert.
• Most recent notification attempts This shows the recent notifications by the different types of notifications.

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.

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.

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.

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.

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:

• Name The Name of the job
• Category You choose from the drop-down list of categories of the type of function your job is.
• Owner You choose from the drop-down list of users that are going to maintain this job.
• Description The Description of the job.

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.

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.

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

• Step Name The name of your step
• Type Choose from a drop down list of the function of this particular step.
• Database Choose from the database registered on the computer that the job will run on.
• Command This is where you type in the command which runs in this step. If you set the type of the step to Transact-SQL, then the command must be a valid TSQL statement.

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

• Output file The path of the file that stores the result of this step.
• Run as user Sets the step to run in the same permissions as the specified user.
• On success action Sets the action that will occur when this step is successful. The actions available are:
• Quit the job reporting success Stops the job after the step is successful
• Quit the job reporting failure Stops the job if the step is not successful.
• Goto the next step Jumps to the next step.

• Retry attempts The number of retries the action will attempt if the step is not successful.
• Retry interval (minutes) Sets the interval of time that must pass before another retry is attempted.
• On failure action° Sets the action that will occur when this step is not successful. The actions available are:

• Quit the job reporting success Stops the job after the step is successful
• Quit the job reporting failure Stops the job if the step is not successful.
• Goto the next step Jumps to the next step.

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.

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

• Name The space to input the name of your schedule
• Enabled You can set whether the schedule is active or inactive.
• Schedule type You can set the type of schedule you want to create with four options to choose from:

• Start automatically when SQL Server Agent starts You can set the schedule to start when SQL Server agent starts.
• Start whenever the CPU become idle This starts the job when the CPU are inactive. This allows you to perform jobs that will not affect a user’s query performance.

• One time Runs the job at a set time and date.
• Recurring Runs the job on intervals you set.

• Change Allows you to edit the schedule within the Recurring option field. If you press it, a new window pops up that allows you to set the frequency of your recurring job as shown in Figure 10-12.

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.

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:

• Upon successful completion Notifications will occur when the job is successful
• Upon unsuccessful completion Notifications will occur when the job is not successful
• Whenever the job completes Notifications will occur when the job is completed, regardless of success
• Write to Windows NT application event log Writes the status of the job to the log
• Automatically delete job Used for jobs that need to be run only once

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.

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.

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.

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.



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

4. 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.
5. 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.
6. 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.



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.

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



Figure 16: Microsoft Mail

8. 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.
9. 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

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.

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.

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.

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.

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:

• Include SQL-DMO library
• Create the SQLServer object
• Connect to the SQLServer object
• Use the SQL-DMO objects and properties. SQL-DMO contains over 40 objects and 1,000 properties used to administer SQL Server and data definition.
• Release the SQLServer object.

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.

• Alerts are responses to SQL Server error conditions. 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.
• 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.
• An alert condition is the event which causes alert to be raised.
• 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.
• 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.
• Jobs are tasks that contain steps. These steps are run at intervals (or at specific times) which help administer certain elements of the database.
• Under the General tab in the New Job Properties, there are fields that are used to name and describe a job.
• Steps are the smallest part of the job.They are often just a simple TSQL query.
• 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.
• Running the job when the CPU is idle is ideal for non-crucial operations that require a lot of CPU activity.
• After configuring and scheduling the job, you must move on the job notifications.
• If you no longer need a job, you can easily delete it in SQL Enterprise Manager.
• In order to enable SQL Server Mail, you must install MAPI. One way MAPI (Mail API) is installed is when you enable Windows Messaging.
• 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.
• 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.
• When a job fails, you can resolve it by having SQL Server run failure flow.
• Multiserver jobs are used to automate administrative tasks for a multiserver environment.
• 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.
• The Master Server is like the NT Domain Controller in a multiserver environment. Everything must go through it.
• 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.