Automating Administration Topics in this Tutorials
- Automation overview
- Setting up SQLMail
- Setting up operators
- Jobs, events and alerts
- Types of job
- Creating user-defined error messages
- Setting up alerts
- Multi-server jobs
Automation Overview
Design Goal: Easier Administration
One of SQL Server design goals has always been to make life easier for
administrators. This includes a number of features:
- Integration with Mail. You can set up operators with email or
pager addresses that can be automatically sent messages when a problem
occurs.
- Automated Jobs. You can set up jobs that are scheduled to run at
regular intervals. These jobs can be made up of multiple steps with
different steps being run based on the success of failure of previous steps.
- Alerts. These can be set up to detect foreseen problems, such as
disk-full errors. Alerts can run jobs and contact operators through email.
Set Up SQLMail
Mail Overview
Microsoft SQL Server 2000 has been designed to integrate with MAPI
(Messaging Application Programming Interface) email applications. Microsoft
Exchange is the recommended mail application for this purpose.
You can set up Microsoft SQL Server as a Mail Client so that mail messages can
be sent directly from the database server when an event happens.
Some applications of this feature might be:
- A trigger on a sales table that sends an email message to the relevant
salesman if a big order has been made.
- A procedure that sends an email message to an administrator with a list
of current users.
- Email messages to the server, requesting information can be returned to
the users every day.
- Email messages to operators when a back-up fails.
SQL Mail Architecture
When you configure a network enabling SQL Server to talk to Microsoft Exchange
you need to have a Mail client on the SQL Server machine set up to 'talk' to
Exchange.
Configuring a Microsoft Exchange profile for SQL Server
Before you can configure SQL Server to be mail-enabled you have to set up a
profile on Microsoft Exchange for SQL Server itself or more specifically, the
domain account under which the MSSQLServer service is running.
To do this you must:
- Create an account for both SQLServerAgent and MSSQLServer (you can
create separate accounts for each of them if you prefer) to run under an
account with administrative rights on the domain
If you are doing this in Windows 2000 with Exchange Server
installed you will be given the opportunity to create an email account for the
new user too.
In the example below the user SQLAgent has been created for this purpose.
Once this has been done, you must perform the following steps to configure a
Microsoft Exchange profile for that account:
- Log onto the SQL Server machine itself as the Windows domain account you
have set up for the MSSQLServer and SQLServerAgent services
The system should configure Internet Explorer if it is the
first time you have logged on as this account.
- Right-click Microsoft Outlook and set the profile to use the
mailbox you have set up
- Exit Outlook and log out of Windows and back in again as
Administrator
Now you need to go into SQL Server and configure SQLMail.
- In Enterprise Manager, find and expand the Support Services
folder
- Right-click SQLMail and choose Properties
- In the Mail Profile dialogue box, select or type MS Exchange
Settings
- Click Test and if it worked, Click OK
This has set the mailbox for the MSSQLServer (xp_sendmail)
service. Next you must do the same thing for SQLServerAgent.
- Expand the Management folder and right-click the SQLServer Agent
service
- In the dialogue box enter the MS Exchange Settings profile as
before
- Right-click SQLServerAgent and choose Start
