SQL Server Agent

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'