T
hroughout this book, we have discussed administrative activities that would
best be performed during off-hours. These activities include backing up data-
bases, creating large databases, reconstructing indexes—the list goes on.
Most of these activities will need to be performed on a regular basis, not just
once. For example, you will need to back up at frequent intervals. Because most
administrators would rather not need to stand at the SQL Server to start the task in
question, SQL Server has the built-in capability to automate tasks.
The first thing we need to discuss is the basics of how automation works in SQL
Server. We’ll explain some of the basic concepts of automation and how the
SQLServerAgent service plays a part.
After we discuss the basics of automation, we will need to set up and configure
e-mail support, because SQL Server is capable of sending you e-mail when there is a
problem as long as e-mail is configured properly. Not only that, but SQL Server can
receive and process queries via e-mail, and send the result set back in an e-mail to the
sender. However, all of this can be done only when e-mail is configured.
Next we will configure operators. An operator is a person who is able to receive
messages from SQL Server via e-mail, pager, or Net Send. Configuring an operator tells
SQL Server whom to contact and when they are available.
After we have operators in place, we can start creating jobs, the heart of automa-
tion. Jobs are the activities that you need to administer, such as database backups or
index reconstruction. We will discuss each part of a job, the steps required to com-
plete the job, and the schedules that tell SQL Server when to run the job. We will also
go over the process of creating multiserver jobs, which can be created on one server
and run on multiple servers over a network.
Next we will configure alerts, which are used to warn you of problems or events
that have occurred on the server. Not only will we discuss how to configure standard
SQL Server alerts, but we will discuss the methods for creating your own user-defined
alerts to cover any possible event that may occur on your server.
After all of this, we will discuss the Database Maintenance Wizard. This special
Wizard is designed to automate all of the standard database maintenance procedures
such as backups, index reconstruction, transaction log backup, etc.
Finally, we will discuss the uses and configuration of SQL Mail. Using this tool, you
can e-mail a query to SQL Server and have it respond with a result set via e-mail. This
tool can potentially save you a lot of time and effort with reporting when used properly.
We’ll start this chapter with a discussion of the basics of automation.
2627ch17.qxd 8/22/00 11:00 AM Page 624
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
625
Automation Basics
Nearly any administrative task you can think of can be automated through SQL
Server. True, that may sound like an exaggeration, but look at the things that you can
automate:
• Any Transact-SQL code
• Scripting languages such as VBScript or JavaScript
• Operating system commands
• Replication tasks (which we’ll learn about in Chapter 27)
Some popular tasks to automate using this functionality are as follows:
• Database backups
• Index reconstruction
• Database creation (for very large databases, or VLDBs)
• Report generation
• Web-page creation (as seen in Chapter 23)
Because this functionality is so powerful, it is easy to see why you need to use SQL
Server’s automation capabilities. However, before you start to use this functionality,
you need to know how it works.
At the very heart of SQL Server’s automation capability is the SQLServerAgent ser-
vice (also referred to as the agent). In fact, automation and replication are the sole
functions of that service. This service uses three subcomponents to accomplish its
automation tasks: alerts, operators, and jobs.
Alerts: An alert is an error message or event that occurs in SQL Server and is
recorded in the Windows NT Application log. Alerts can be sent to users via
e-mail, pager, or Net Send. If an error message is not written to the Windows NT
application log, an alert will never be fired off.
Operators: When an alert is fired, it can be sent to a user. Users who need to
receive these messages are known in SQL Server as operators. Operators are used
to configure who will receive alerts and when they are available to receive these
messages.
Jobs: A job is a series of steps that define the task to be automated. It also
defines schedules, which dictate when the task is to be executed. Such tasks can
be run only one time or on a recurring basis.
AUTOMATION BASICS
Admninistering SQL
Server
PART
IV
2627ch17.qxd 8/22/00 11:00 AM Page 625
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
CHAPTER 17 • AUTOMATING ADMINISTRATION
626
These three components work together to complete the tapestry of administration.
Here is an example of what may happen:
1. A user may define a job that is specified to run at a certain time.
2. When the job runs, it fails and thus writes an error message to the Windows NT
event log.
3. When the SQLServerAgent service reads the Windows NT event log, the agent
finds the error message that the failed job wrote and compares that to the
sysalerts table in the MSDB database.
4. When the agent finds a match, it fires an alert.
5. The alert, when fired, can send an e-mail, pager message, or Net Send message
to an operator.
6. The alert can also be configured to run another job, designed to repair the prob-
lem that caused the alert.
For any of this to function, though, the SQLServerAgent service must be properly
configured. To start, the agent must be running for automation to work. There are
three ways to verify this: First, you can open Enterprise Manager, expand Manage-
ment, and notice the SQL Server Agent icon—if it is a red square, the service is
stopped; if it is a green arrow, the service is started. You can even start the service by
right-clicking the icon and selecting Start. Other methods of checking and changing
the state of the service are by using the Service Manager (which can be found in the
task tray of the Start bar) or by using the Services applet in the Control Panel.
Not only should the agent be running, but it is best to have it log on with a
domain account as opposed to a local system account, because using the local system
account will not allow you to work with other SQL Servers on your network. This
means that you will not be able to perform multiserver jobs (discussed later in this
chapter), replication (discussed in Chapter 27), or use SQL Server’s e-mail capabilities.
To make sure the agent is logging on with a domain account, you should open the
Services applet in Control Panel (if you are using Windows 2000, you will find it in
Administrative Tools under Programs on the Start menu), double-click the SQLServer-
Agent service, and select a domain account by clicking the ellipsis box next to This
Account.
Once all of this is in place, you are nearly ready to begin working with automation.
First, you should configure SQL Server to be able to send and receive e-mail.
2627ch17.qxd 8/22/00 11:00 AM Page 626
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
627
Configuring Mail Support
The services that comprise SQL Server can send and receive e-mail. Specifically, the
SQLServerAgent service works with SQLAgentMail, which the agent uses to send
e-mail to administrators when an alert has fired. The MSSQLServer service works with
SQL Mail, which the service uses to receive queries from users and reply with a result
set; it is a lot like executing a query through Query Analyzer, only via e-mail.
To configure either of these types of mail, you must have a mail account some-
where. Exchange works best because both Exchange and SQL Server are parts of the
Microsoft BackOffice family, but it is also possible to use an Internet e-mail account.
The first step in making this work is to create a mailbox on the e-mail server to
which you will be connecting. If you are using Microsoft Exchange 5.5, this is what
you do:
1. Open the Exchange Administrator from the Microsoft Exchange 5.5 group
under Programs on the Start menu.
2. From the File menu, select New User.
3. Select the user account that the SQL Server services use to log on as the Primary
Account for the mailbox.
4. Fill in the remaining information as appropriate.
If you are using Exchange 2000:
1. Open up Active Directory Users and Computers.
2. Right-click the account that the SQL Server services use to log on and select
Exchange Tasks.
3. Follow the steps in the subsequent Wizard to Mail-Enable the account.
Once you have a mailbox on the server, you need to install Microsoft Outlook so
that the server can make a MAPI connection to the server with which it will be work-
ing. The process of installing Outlook is a little outside the scope of this book, but it is
a relatively easy process driven by a Wizard.
After Outlook has been successfully installed, you need to create a mail profile for
the SQL Server account:
1. Log on to the SQL Server as the SQL Server services account.
2. Open Outlook.
3. Outlook will come up with an error message stating that it is improperly config-
ured. Click OK.
CONFIGURING MAIL SUPPORT
Admninistering SQL
Server
PART
IV
2627ch17.qxd 8/22/00 11:00 AM Page 627
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
CHAPTER 17 • AUTOMATING ADMINISTRATION
628
4. A dialog box will pop up asking you which mail transports you would like to
use. If you have an Exchange server, select it from the list. If not, you can select
Internet Mail from the list.
5. If you selected Exchange, a dialog box will appear asking you for the names of
the mailbox and e-mail server. Fill both of these in and click OK.
6. If you are using Internet Mail, you will be asked for your Internet mail account
information.
7. Outlook will now configure a series of sample messages, and the Microsoft
Office Assistant should appear (only if you opted to install it). Click the Start
Using Outlook choice.
8. Close Outlook and log off Windows.
Once Outlook is installed and you have a mail profile created, you can configure
the SQLServerAgent and MSSQLServer services to start using the new profile to send
and receive mail:
1. Open Enterprise Manager by selecting it from the SQL Server 2000 group under
Programs on the Start menu.
2. Expand your server, then Management.
3. Right-click the SQLServerAgent and select Properties.
4. In the Properties dialog box, select the mail profile that you created while
logged in as the SQL Server service account.
2627ch17.qxd 8/22/00 11:00 AM Page 628
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
629
5. Click Test to verify that the mail profile works.
6. Click OK at the bottom of the dialog box, and then click OK when asked to stop
and restart the SQLServerAgent service.
With a mail profile successfully configured, you can now create operators that will
receive e-mail from the SQL Server.
Creating Operators
Several settings need to be configured for SQL Server to be able to contact you when
there are problems. Such settings include whom to contact, when they are available,
how those people should be contacted (via e-mail, pager, or Net Send), and of what
problems should they be alerted. An operator is the object used in SQL Server to con-
figure all of these settings.
NOTE Net Send messages are messages that are sent from a source machine to a des-
tination machine, where they pop up on a user’s screen as a dialog box over all of the
open applications.
Suppose, for example, that there are several people in your company who need to
be alerted when there is a problem with SQL Server, each of them needing to be
alerted for different problems and in various ways. Your database administrator may
need to be alerted of any administration issues (for example, a failed backup or full
transaction log) via e-mail and pager. Your developers may need to be alerted to pro-
gramming issues (for example, deadlocks) via e-mail. Perhaps managers in your com-
pany need to know of other issues, such as when a user deletes a customer from a
customer database, and they want to be alerted by a Net Send message. These types of
users would be handled by creating separate operators for each and configuring the
desired settings.
Let’s configure an operator here to demonstrate:
1.Open Enterprise Manager by selecting it from the SQL Server 2000 group under
Programs on the Start menu.
2. Expand your server, then Management, then the SQLServerAgent.
3. Click the Operators icon and select New Operator from the Action menu.
4. In the Name box, enter Administrator.
CREATING OPERATORS
Admninistering SQL
Server
PART
IV
2627ch17.qxd 8/22/00 11:00 AM Page 629
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
CHAPTER 17 • AUTOMATING ADMINISTRATION
630
5. If you configured your system to use SQLServerAgent Mail, enter your e-mail
address as the e-mail name. If you did not configure your system to use e-mail,
skip this step.
6. Type the name of your machine in the Net Send box. This can be found by
right-clicking the My Computer icon on the desktop and selecting Properties,
then the Network Identification tab. The computer name is the first section of
the full computer name (before the first period). If your full computer name is
instructor.domain.com, the computer name is instructor.
7. At the bottom of the screen, select the days and times this operator is available
for notification. If a day is checked, the operator will be notified on that day
between the start and end times noted under Workday Begin and Workday End.
8. To test the operator, click the Test buttons next to each of the three notification
methods. The e-mail and pager tests will both send an e-mail, and the Net Send
test will cause a dialog box to pop up on your screen.
9. We’ll discuss the Notifications tab later; for now, click OK to create the operator.
Because operators can be made active at different times, it is possible to acciden-
tally leave a small period of time uncovered. If there is an error in that window of
time, no operator would receive the alert, because none are on duty. To avoid such a
2627ch17.qxd 8/22/00 11:00 AM Page 630
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
631
problem, you should create a fail-safe operator, which is designed to receive alerts
when no one is scheduled to be on duty. Here is how to create one:
1. In Enterprise Manager, right-click the SQL Server Agent icon under Manage-
ment and select Properties.
2. On the Alert System tab, select yourself in the drop-down list next to Fail-Safe
Operator.
3. Check the box next to Net Send so that you will receive Net Send messages as a
fail-safe operator.
4. Click OK to apply the changes.
With an operator in place, you are ready to start creating jobs to automate tasks.
Creating Jobs
A job is a series of tasks that can be automated to run whenever you need them to. It
may be easier to think of it as being somewhat like cleaning your house. Most of us
think of cleaning our house as one big job that needs to be done, but it is really just a
series of smaller tasks such as dusting the furniture, vacuuming the carpet, doing the
dishes, etc. Some of these steps need to be accomplished in succession (for example,
dusting before vacuuming); others can happen anytime (for example, the dishes don’t
need to be done before you can wash the windows).
Any job on SQL Server works in much the same way. Take, for example, a job that
creates a database. This is not just one big job with one step to accomplish before
you’re done; there are several steps that should take place. Step one would be to create
the database. The next step would be to back up the newly created database, because
it is in a vulnerable state until it is backed up. After the database has been backed up,
you can create some tables in it and then perhaps import data into those tables from
text files. Each of these tasks is a separate step that needs to be completed before the
next can be started, but not all jobs are that way.
By controlling the flow of the steps, you can build error correction into your jobs.
For example, in the create-database job listed above, each step would have simple
logic that states on success go to the next step; on failure quit the job. So if the hard disk
turned out to be full, the job would stop. If you create a step at the end of the job that
is designed to clear up some hard-disk space, you could create logic that states if step
one fails, go to step five; if step five succeeds, go back to step one. With the steps in place,
you are ready to tell SQL Server when to start the job.
CREATING JOBS
Admninistering SQL
Server
PART
IV
2627ch17.qxd 8/22/00 11:00 AM Page 631
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
CHAPTER 17 • AUTOMATING ADMINISTRATION
632
To tell SQL Server when to run a job, you need to create schedules, and you have a
lot of flexibility there. With a job that creates a database, it would not make much
sense to have it run more than once, so you would create a single schedule that will
activate the job after-hours. If you were creating a job that is designed to perform
transaction log backups, you would want a different schedule. You may want to per-
form these backups every 2 hours during the day (from 9:00
A.M. to 6:00 P. M.) and
then every 3 hours at night (from 6:00
P. M. to 9:00 A.M.). In this instance, you would
need to create two schedules, one that is active from 9:00
A.M. to 6:00 P. M. that acti-
vates the job every 2 hours and another that is active from 6:00
P. M. to 9:00 A.M. that
activates the job every 3 hours. If you think that’s fancy, you’ll love this next part.
Not only can you schedule a job to activate at certain times of the day, you can
schedule them to activate only on certain days of the week (for example, every Tues-
day), or you can schedule them to run only on certain days of the month (for example,
every third Monday). Jobs can be scheduled to run every time the SQLServerAgent
service starts up, and they can even be scheduled to run every time the processor
becomes idle.
Schedules can be set to expire after a certain amount of time, so if you know you
are going to be done with a job after a few weeks, you can set it to expire—it will
automatically be disabled (not deleted, just shut off).
You also have the capacity to be notified of the outcome of a job. On the final tab
of the Create Job dialog (which you will see very soon), you can add an operator to
the job that can be notified on success, on failure, or on completion (no matter
whether it failed or succeeded). This comes in very handy when the job you are run-
ning is critical to your server or application.
With the ability to change the logical flow of steps, schedule jobs to run whenever
you want, and have them notify you on completion, you can see how complex jobs
can become. With this complexity in mind, it is always a good idea to sit down with
pencil and paper, and plan out your jobs before creating them; it will make your job
easier in the long run.
There are two types of jobs in SQL Server, local and multiserver. Let’s look at each
of these, starting with local jobs.
Creating Local Server Jobs
Local jobs are standard jobs with a series of steps and schedules. They are designed to
run on the machine where they are created, hence the name local jobs. To demon-
strate local jobs, let’s create one that will create a new database and then back it up:
1. Open Enterprise Manager by selecting it from the SQL Server 2000 group under
Programs on the Start menu.
2. Expand your server, then Management, then SQLServerAgent.
3. Select Jobs and, from the Action menu, select New Job.
2627ch17.qxd 8/22/00 11:00 AM Page 632
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
633
4. In the Name box, type Create Test Database (leave the rest of the boxes on
this tab with the default settings).
5. Go to the Steps tab and click the New button to create a new step.
6. In the Step Name box, type Create Database.
7. Leave the type as Transact-SQL and enter the following code to create a database
named Test on the C: drive:
CREATE DATABASE TEST ON
PRIMARY (NAME=test_dat,
FILENAME=’c:\test.mdf’,
SIZE=10MB,
MAXSIZE=15,
FILEGROWTH=10%)
CREATING JOBS
Admninistering SQL
Server
PART
IV
2627ch17.qxd 8/22/00 11:00 AM Page 633
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
Không có nhận xét nào:
Đăng nhận xét