SQL Server Agent Advanced Service Configuration

SQL Server Agent service has a run-time dependency on the corresponding SQL Server Database Engine instance process. There are several reasons for this. SQL Server Agent uses Database Engine for its metadata store and leverages the SQL Server security mechanism for user authentication.

Note

From this point on, any SQL Server Agent-related Registry flag mentioned will be located under the implicit common Registry hive \HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\<instance_name>\SQL Server Agent\.

Tips and Tricks

SQL Server Database Engine and SQL Server Agent are closely related and can be configured to monitor each other's state and to restart each other if needed, such as upon abnormal termination. Both configuration settings are exposed on the General Page of the SQL Server Agent Properties dialog and are stored in the Registry as RestartSQLServer and MonitorAutoStart. If upon startup the SQL Server Agent service is instructed to monitor Database Engine service, it creates a special monitoring thread that continuously polls Database Engine status through a common shared memory object. The process of Database Engine monitoring SQL Server Agent is more convoluted. SQL Server Database Engine does not know it is supposed to monitor SQL Server Agent until the xp_sqlagent_monitor extended stored procedure is invoked on SQL Server Database Engine by SQL Server Agent, essentially telling it to monitor itself.

Upon initial installation, the SQL Server Agent service is in a disabled state. Therefore, to start SQL Server Agent, the Windows server administrator must manually enable the service, using the Service Control Manager or SQL Server Configuration Manager. As soon as SQL Server Agent is started, it loads metadata from the SQL Server store and subsequently starts running tasks, monitoring, and responding to alerts.

The SQL Server Agent component is Off-By-Default (OBD). This means all the extended stored procedures and some stored procedures are not accessible while the service is not running. Upon startup, the SQL Server Agent service enables its own OBD component, called Agent XPs, via the sys.sp_configure system configuration procedure when it first connects to SQL Server and turns the extended stored procedures back on. SQL Server Agent turns itself off upon shutdown by calling the same procedure.

Tips and Tricks

While the SQL Server Agent OBD component is off, any of the metadata configuration procedures, such as sp_add_job, sp_add_schedule, sp_add_alert, and so on, can still be invoked. In other words, it is possible to fully configure SQL Server Agent in a shutdown state so when it comes back up, it starts functioning right away. The only stored procedures truly blocked from being invoked are the ones that require communication to the running Agent service, such as sp_msx_set_account.

In a MSX/TSX distributed execution environment, the target server determines whether it needs to download jobs from the master and to which master server it needs to connect via the MSXServerName Registry flag. The RegularMsxConnections registry flag tells SQL Server Agent to use either integrated security or obtain credential information for SQL Authentication from the SQL Server Database Engine Credentials Store. These two settings are typically not set by the user directly; they are created during the enlistment process. Several other related Registry flags may need some tweaking. The MSXDownloadBatchSize Registry setting determines the number of download instructions to the cache at one time, and MSXPollInterval defines how frequently the target server needs to connect to the master server to check for pending updates.

SQL Server Agent can be configured to send out email notifications for certain types of alerts (there is more on alerting in the next chapter). For this reason, SQL Server Agent has its own MAPI-based mailing component. Unlike SQLMail, which can use only Extended MAPI, SQL Server Agent can use Simple MAPI if Extended MAPI is not available. In SQL Server 2005, SQL Server Agent can optionally use the new SQL Server mailing solution (based on the SMTP protocol) called Database Mail. If SQL Server Agent is configured this way through UseDatabaseMail and DatabaseMailProfile, Registry flags will call the msdb.dbo.sp_send_dbmail stored procedure for mail delivery. However, when SQL Server is down and the stored procedure unavailable, SQL Server Agent uses cached profile and account information and sends mail via a low-level API layer, using the same basic infrastructure (including a failover mechanism) as the DatabaseMail90.exe process when it is running. Mail in this case is still sent without logging in the msdb.dbo.sysmail_mailitems table.

SQL Server Agent service account needs to be a member of the sysadmin fixed server role on its corresponding instance of SQL Server.

Warning

Starting with SQL Server 2005, SQL Server Agent can no longer use SQL Authentication when connecting to SQL Server. Therefore, if before the upgrade SQL Server Agent was configured to use @host_login_name and @host_login_password configuration parameters, it will no longer be able to connect to SQL Server and will fail to start. The parameters are left on the interface for backward compatibility reasons but no longer serve any useful purpose. The same is true for the @regular_connections parameter.

On the positive side, the SQL Server Agent service account no longer needs to be a member of the local Administrators group on the box. The SQL Server development team did a lot of work to remove this requirement and lower the potential security vulnerability of the system as a whole by lowering the minimum account privileges. To accomplish this goal, storage of the proxy and master server account credentials had to be removed from LSA. Starting with the SQL Server 2005 release, SQL Server Agent completely relies on the SQL Server Database Engine credential store to handle storage of this confidential information.