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:
- 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.
