To automate administration:
•Establish the administrative responsibilities or server events that occur regularly and can be administered programmatically.
•Define a set of jobs and alerts.
•Run the SQL Server Agent service.
SQL Server Automated
Administration
SQL Server Agent Service
SQL Server Agent supports features allowing the scheduling of periodic activities on Microsoft SQL Server, or the notification to system administrators of problems that have occurred with the server.
The SQL Server Agent components that implement this capability are:
•Jobs
Defined objects consisting of a one or more steps to be
•Alerts
Actions to be taken when specific events occur, such as a specific error, errors of certain severities, or a
database reaching a defined limit of free space available. The alert can be defined to take such actions as sending an e-mail, paging an operator, or running a job to address the problem.
•Operators
People identified through their network account or e-mail ID who can address problems with the
Job Name
Every job must have a name.
Job names must be unique only if they originate from the same server.
A job name can be no more than 128 characters.
Job Category
Job Owner
The job owner is the individual who creates a job or for whom the job is created.
By default, SQL Server Agent populates the owner information with the SQL Server login ID of the job creator.
Job Description
A job can be run by other users on both the local and remote computers running SQL Server.
By writing a description at the time a job is created, the job owner can ensure that other users will understand quickly the purpose of the job.
Implementing Jobs
• Administrative tasks can be automated using SQL Server Agent jobs.
• A job can be defined one time but can be run many times.
•Jobs can be written to run on the local server or on multiple servers.
•To run jobs on multiple servers, you must set up at least one master server and one or more target servers.
•Are errors and messages, or events that are generated
by SQL Server and entered into the Microsoft Windows NT application log.
•SQL Server Agent reads the application log and
compares events to alerts that you have defined.
Setting up a SQL Server database alert
1. On the View menu, click Alert.
2. On the Edit menu, click Add to Alert....
3. In the Object list, click a SQL Server object, and then click a counter, on which the alert will be based, from the Counter box.
4. Under Alert If, click Over or Under, and then enter a
threshold value. The alert will be generated when the value for the counter is over or under the threshold value
(depending on whether Over or Under was selected).
•Allows SQL Server to send and receive e-mail by establishing a client connection with a mail server.
•SQL Server 7.0 uses two services to handle mail.
? SQL Mail.
? SQL Server Agent Mail.
•MS SQL Server processes mail for all of the mail stored procedures.
•SQL Mail establishes a simple MAPI connection with a
mail host.
•SQL Agent Mail establishes either a simple or extended
MAPI connection.
•Both SQL Mail and SQL Agent Mail can connect with
MS Exchange Server, Windows NT Mail, or a POP3
server.
•SQL Mail stored procedures can manipulate data,
process queries received by e-mail and return the result
set by creating a reply e-mail.
•SQL Agent Mail can be configured to send an e-mail
MAPI (Messaging Application Program Interface) is a Microsoft Windows program interface that enables
1. You to send e-mail from within a Windows application and attach the document you are working on to the e-mail note.
2. MAPI-compatible applications typically include a Send
Mail or Send in the File pulldown menu of the application.
This sends a request to a MAPI server.
3. MAPI consists of a standard set of C language
Developers who are using Microsoft's Active Server Page (ASP) technology access the MAPI library by using Microsoft's Collaboration Data Objects (CDO).
SQL Server and Mail Integration
Microsoft SQL Server provides a set of extended stored
procedures that allow SQL Server to operate as a workgroup post office for a MAPI-enabled e-mail system.
The computer running SQL Server must be set up as an e-mail client. SQL Server Enterprise Manager is used to assign an e- mail account and password to the SQL Server installation.
The mail component of SQL Server can then be enabled to be started automatically when the SQL Server Agent
service is started.
Alternatively, the mail component can be started and
stopped at will using either SQL Server Enterprise Manager, or the xp_startmail, xp_stopmail, and xp_sendmail
?When the mail component of SQL
Server is running, it can be used to:
Send e-mail from Transact-SQL batches, scripts,
stored procedures, and triggers using xp_send_mail. The e-mail can be:
Message strings.
The result set of a query.
A Transact-SQL statement or batch to execute.
E-mail can be read using:
?sp_processmail
?A combination of xp_findnextmessage ?xp_readmail,
The messages sent to SQL Server typically contain a Transact-SQL statement or batch to be executed.
The statement is executed and the result set is returned as a reply e-mail with an optional CC: list.
SQL Server events and alerts can be combined with SQL Mail functionality to build a system in which a server
running SQL Server can e-mail or page the relevant
Configuring a SQL Mail Profile
When configured, mail profiles are specific to the Microsoft
Windows NT user domain account that is activated when a user ogs on to Windows NT successfully. SQL Mail must have a mail profile created in the same user domain account or context that is used to start SQL Server. When a mail stored procedure is
executed, SQL Mail looks for the defined mail profile in the domain account that triggered it.
If you plan to use mail stored procedures you must: •Have a mail server that is simple MAPI-compliant.
How to configure a mail profile (Windows NT)
To configure a mail profile
1. Logon to the Microsoft Windows NT server specifying the name and password used to start SQL Server services for that server.
3. On the server running Microsoft SQL Server, start the mail client using the newly created mail profile. 4. Send a message addressed to the same profile name
to ensure the mail client, mail profile, and e-mail provider are working properly. If your e-mail message does not appear, you may need to force mail synchronization by going to the Tools menu, and then clicking Deliver Now.
To set up SQL Mail
How to set up SQL Mail (Enterprise Manager)
1. Expand a server group; then expand a server.
2. Click Support Services, right-click SQL Mail, and then click Properties.
3. In Profile name, select the mail profile that you configured for SQL Mail.
Configuring a SQL Agent Mail Profile
If you plan to send notifications to operators by e-mail or pager, you must:
•Have a mail server that is simple or extended MAPI-compliant.
•Configure a mail profile for SQL Server Agent to use to connect to your mail server.
How to set the mail profile for SQL Server Agent (Enterprise Manager)
To set the mail profile for SQL Server Agent
1. Right-click SQL Server Agent; then click Properties.
2. In the SQL Server Agent Properties dialog box, on the
General tab, under Mail session, click a profile in the Mail profile box. If no profiles are listed, enter the name of the
How to set up SQLAgentMail (Enterprise Manager)
1. Expand a server group; then expand a server.
2. Expand Management, right-click SQL Server Agent, and then click Properties.
3. If you have configured a mail profile using a different domain account than the one used to Microsoft® SQL Server™, click
This Account, and then enter the Windows NT account name
and password used to create the mail profile for SQLAgentMail.
4. In Mail profile, select the mail profile you created for SQLAgentMail.
Sharing a Profile with SQL Mail
SQL Mail and SQLAgentMail sessions may be configured to use the same Windows NT domain user account. When using the same
domain account, SQL Mail and SQLAgentMail can share the same mail profile using a common mailbox.
Microsoft® SQL Server™ uses two separate mail sessions:
•The MSSQLServer service uses a mail session that is referred to as SQL Mail.
•SQL Server uses this mail session when your database applications execute the xp_sendmail extended stored procedure to send a
message or query result set to a recipient.
