08-08-2012, 05:03 PM
data definition language (DDL)
data definition language (DDL) .pptx (Size: 4.09 MB / Downloads: 49)
SQL SERVER AGENT
Like Microsoft Windows, SQL Server includes a facility to automate tasks.
This facility involves three main parts:
The SQL Server Agent Service,
SQL Server Agent Jobs, and
the msdb database.
SQL Server Agent is a set of software components on the server and a service for each instance of SQL Server installed.
On SQL Server 2000, if the only instance is installed the service is called SQLAgent. For every instance after that, it's called SQLAgent$InstanceName.
For SQL Server 2005 and higher, the default instance is called SQL Server Agent, and for each named instance installed after that it is called SQL Server Agent (InstanceName).
CONFG. SQL SERVER AGENT SERVICE
From the General Properties sheet, you can see the current state of the service.
You can configure both the SQL Server and SQL Server Agent to automatically restart if they stop unexpectedly.
Make sure you check the two top checkboxes:
‘‘Auto restart SQL Server if it stops unexpectedly’’
‘‘Auto restart SQL Server Agent if it stops unexpectedly’’
The Service Control Manager will watch both of these services and automatically restart them if they fail.
Advanced Properties
In the Advanced Properties sheet, you can enable event forwarding, which will redirect SQL Server events to a different server.
To configure this, enable the checkbox next to “Forward events to a different server” and then select an available server or instance from the drop-down list.
Once this is configured, you can also determine what type of events will get forwarded.
Unhandled events are those that do not have alerts defined by the Agent system, or you can select all events.
You can also decide to forward events with a minimum severity level.
From this window, you can also define the CPU idle threshold. This can be useful if you have any job schedules that define the job should be run when the CPU is idle, such as backing up the transaction log. In this case, the default values indicate that CPU usage must fall below 10 percent for 10 minutes. You can adjust this as necessary to meet your performance needs.
Alert System Properties
If you want your operators to receive alert notifications by email, you should enable this feature. You can also decide if you are going to use the Database Mail feature or the SQLMail feature.
Although you may have many mail profiles in Database Mail, SQL Server Agent will use only one profile. Choose the mail system and profile.
If you are using SQLMail, you can test the MAPI connectivity, and allow sent messages to be saved in the Sent Items folder of the Microsoft Outlook profile.
The second section is for pager e-mails. If your pager system requires special control characters in the To:, CC:, or Subject: line, you may add those characters here in front of the item (prefix) or after the item (suffix). As you make changes, you can see the effect in the small box below your data-entry section. You may also choose to include or exclude the body of the e-mail for pagers by indicating your selection in the appropriate checkbox.
Agent Connection Properties
If you need to connect to an instance of SQL Server that uses a non-standard connection property, you can enter an alias used by the SQL Server to allow the Agent service to establish and maintain a connection.
You can also specify whether you require the Agent service to use Windows authentication or SQL authentication. If you select SQL authentication, you must provide a valid login and password for an account that is a member of the sysadmin fixed server role.