SQL Server Agent – iSqlPlus

What is the SQL Server Agent?

SQL Server Agent uses SQL Server to store job information
A job is a specified series of actions that can run on a local server or on multiple remote servers
Jobs can contain one or more other job steps
SQL Server Agent can run on a job on a schedule
To a response or a specific event or manually
You can avoid repetitive tasks by automating those tasks
SQL Server Agent can record the event and notify you via email or pager
You can set up a job using the SQL Agent for SSIS packages or for Analysis Services
More than one job can run on the same schedule, and more than one schedule can apply to the same job

Alerts

An alert is an automatic response to a specific event. Which you can you define the conditions under which an alert occurs fires

Operators

An operator is the contact person for alerts via the following:

E-mail
Pager (through e-mail)
net send

Security for SQL Server Agent Administration

SQL Agent uses the following msdb database roles to manage security:
SQLAgentUserRole
SQLAgentReaderRole
SQLAgentOperatorRole

SQL Agent Multi Server Administration

Using Master/Target servers to manage many jobs at once

Backup database using SQL Agent

create database Library
go

--Drop table   [dbo].[Books]

use Library
go

create table Books
(BooksID int primary key identity (1,1),
Tilte varchar (100))

--First, a full database backup job captures the creation of table books (start time manual) 11:30 pm
--Each 30 seconds, activity is occuring against the table books (inserts).  As activity occrs, a scehduled backup job is captuing those modifications to the
--database (the insert)
insert into Books values ('Dracula') 

--a scheduled transaction log captures the next inserts (job executes after each 40th second)

WAITFOR DELAY '00:00:15';

--a scheduled transaction log captures the next inserts (job executes after each 40th second)

insert into Books values ('Mody Dick') 

--a scheduled transaction log captures the next inserts (job executes after each 40th second)

WAITFOR DELAY '00:00:30';
insert into Books values ('Macbeth')

--a scheduled transaction log captures the next inserts (job executes after each 40th second)

WAITFOR DELAY '00:00:15';
insert into Books values ('The Martian')

--a scheduled transaction log captures the next inserts (job executes after each 40th second)

WAITFOR DELAY '00:00:05';
insert into Books values ('Train Robbery')

--a scheduled transaction log captures the next inserts (job executes after each 40th second)

WAITFOR DELAY '00:00:03';
insert into Books values ('Godfather')

--a scheduled transaction log captures the next inserts (job executes after each 40th second)

WAITFOR DELAY '00:00:20';
select * from books

--RESTORE HEADERONLY FROM DISK = 'C:\FullBackups\labrary.bak'

To create a SQL Server Agent job

In the Object Explorer, click the plus sign to expand the server where you want to create a SQL Server Agent job.
Click the plus sign to expand SQL Server Agent.
Right-click the Jobs folder and select New Job….
In the New Job dialog box, on the General page, modify the general properties of the job. For more information on the available options on this page, see Job Properties – New Job (General Page)
On the Steps page, organize the job steps. For more information on the available options on this page, see Job Properties – New Job (Steps Page)
On the Schedules page, organize schedules for the job. For more information on the available options on this page, see Job Properties – New Job (Schedules Page)
On the Alerts page, organize the alerts for the job. For more information on the available options on this page, see Job Properties – New Job (Alerts Page)
On the Notifications page, set actions for Microsoft SQL Server Agent to perform when the job completes. For more information on the available options on this page, see Job Properties – New Job (Notifications Page).
On the Targets page, manage the target servers for the job. For more information on the available options on this page, see Job Properties – New Job (Targets Page).
When finished, click OK.