SQL Server Agent is the driving force behind database automation. It is responsible for processing alerts and running scheduled jobs. When alerts are triggered and when scheduled jobs fail, succeed, or complete, you can notify SQL Server operators. Operator notifications are also processed through SQL Server Agent.
You can use SQL Server Management Studio to access resources related to SQL Server Agent by completing the following steps:
Start SQL Server Management Studio. In Object Explorer view, connect to the server instance of your choice, and then expand the server’s SQL Server Agent folder. (The SQL Server Agent must be running to expand the related node.)
You should see entries for Alerts, Operators, and Jobs. Select one of these entries in the left pane to display its properties in the right pane, as shown in Figure 15-3.
Any jobs or alerts that are shaded are configured but not enabled. Double-click an alert, operator, or job entry to access its associated Properties dialog box.
SQL Server Agent service executes scheduled jobs, triggers alerts, and performs other automated tasks. Each SQL Server Database Engine instance has its own SQL Server Agent service. You can control the related service (SQLServerAgent or SQLAgent$instancename) just as you do the SQL Server service. For SQL Server Agent to work properly, you should configure the SQL Server Agent service to run automatically. The startup account used by the SQL Server Agent service determines access permissions for SQL Server Agent. If the startup account does not have appropriate permissions, SQL Server Agent will not run properly. In most cases, you will want to use a Microsoft Windows domain account that is a member of the sysadmin role. This ensures that SQL Server Agent can generate alerts, run jobs, and restart services, as necessary.
To configure the SQL Server Agent service complete the following steps:
In SQL Server Configuration Manager, select the SQL Server 2005 Services node in the left pane to see the related SQL Server services in the right pane.
Right-click the SQL Server Agent service for the Database Engine instance you are configuring and select Properties.
SQL Server Agent can run using a built-in system account or a designated Windows account:
Choose Built-in Account to use one of the built-in system accounts as the startup account. The drop-down menu gives you three options: Local System, Local Service, or Network Service. Local System grants access to the local system and certain system-wide privileges, such as Act As Part Of The Operating System. Local Service grants access to the local system as a regular service account. Network Service grants access to the local system and allows SQL Server Agent to access the network, such as would be necessary to connect to remote systems.
Choose This Account to control permissions and privileges using a Windows account. Type the user name and password of a Windows domain account. You can also click Browse to search for an account using the Select User Or Group dialog box.
If you changed the service account, you must stop and then start the service. Do this by clicking Restart. If the service is stopped already, click Start instead.
On the Service tab, Start Mode should be set to Automatic. If it is not, click in the Start Mode drop-down list and select Automatic.
Click OK.
The SQL Server Agent service sends alerts and notifications through e-mail messages. Two options are allowed. You can use Database Mail or SQL Mail (which has been deprecated). When you initially set Database Mail to send alerts and notifications, you configure one or more databases as mail hosts and define client settings so that users and applications, such as SQL Server Agent, can send SMTP e-mail messages through your organization’s SMTP mail server. To use Database Mail for SQL Server Agent alerts and notifications, you must:
Configure msdb as a database mail host.
Designate a profile for this database.
Grant profile access to the SQL Server Agent service account.
SQL Mail configures SQL Server Agent as a mail client to send mail by using the Messaging Application Programming Interface (MAPI) through your organization’s MAPI-compliant mail server. To use SQL Mail with SQL Server Agent, you must configure SQL Mail, create a MAPI profile on the system (such as an Outlook mail profile), and then modify the SQL Server Agent Properties so that the SQL Mail profile is enabled for the MAPI profile.
You designate the SQL Mail profile by completing the following steps:
Right-click the SQL Server Agent entry in the SQL Server Management Studio Management folder, and then select Properties.
On the Alert System page, select Enable Mail Profile.
Select SQL Mail as the Mail System, and then select the appropriate mail profile. If you want to test the configuration, click Test.
Click OK.
You can configure SQL Server Agent to restart the SQL Server and SQL Server Agent services automatically if they stop unexpectedly. Configuring automatic restart of these services is a good idea because it may keep you from getting paged if the server stops for some reason at 3:00 A.M. on a Tuesday morning.
To configure automatic service restart, complete the following steps: