>>Hi, there!
I'm Michael Otey, I'm a SolarWinds contributor
and President of TECA Inc.
And in this video we're going to look
at scheduling SQL Server jobs using SQL Agent.
So what are we gonna cover?
Well, first we're gonna look at the different components
that make up SQL Agent.
Next, we're gonna look at starting the SQL Agent service
using SQL Server Configuration Manager,
although you can also start at using Windows services,
but since it's a SQL component,
we'll use the configuration manager.
Next we'll look at actually creating SQL Agent jobs.
We'll look inside at a SQL Agent job,
look at creating job steps, job steps basically contain
the actionable items that the agent jobs will perform.
They are basically the executibles that are going to happen.
They could be scripts, they could be T SQL scripts,
PowerShell scripts, they could be SISS packages,
they could be a number of different things.
And then, after we've created those job steps,
we'll look at setting up a job schedule
so the job could run at recurring basis
at pretty much whatever interval we want to set it at.
So what does SQL Agent jobs consist of?
Well, first, there's the job itself,
and the SQL Agent job is basically a specified action
or it can be a series of actions,
you can have multiple job steps within a single job
that the SQL Agent will execute.
Those job steps you can set up dependencies
upon the different steps
to where if a preceding step fails,
you can cancel the job or you could continue
on to the next step.
I'll cover more about that when we go through
a quick demo of it in just a second.
SQL Agent jobs also consists of schedules.
Basically, the schedule defines when that job will run.
Multiple jobs can all run on the same schedule,
you don't have to have them set up at different
times of anything.
Job can also be run in response to alerts.
Basically a SQL Agent alert is an automatic response
to some sort of system event.
So you can kick off your jobs in response to an event.
For instance, if the CPU agent goes idle,
you can run a job then when the system is quiet.
And then there's also operators.
SQL Agent jobs have operators,
and an operator is basically somebody who's responsible
for the maintenance on your SQL Server instance.
You're gonna pretty much notify that operator,
say on the success or failure of a job,
or you could send them an email or a fax,
or you could send them a net send type of message.
Anyway, these are the different SQL Agent components.
When you get ready to start SQL Agent,
you can do it in a couple different ways,
but SQL Server configuration manager
is the most straightforward way.
Basically, open up configuration manager,
you'll see the SQL Server agent listed
in the configuration manager.
By default it is not started automatically,
it's in a manual mode like you see here.
To start it, simply right click on it
and select start from the context menu.
If you want to go ahead and set it up to automatic,
which most SQL Server shops would want to do,
double click on it, go into the properties,
and change the start mode.
I'll show you how to do that live in just a second, here.
Once the agent is started, you can create new jobs
by using SQL Server Management Studio.
Basically go down and open the SQL Server agent node,
right click on it, and you'll go step through
a new job wizard, like you see here.
And with a job, basically, the first thing
is to set up a job name
and then you're going to go the steps tab,
which you can see here onto the right side of the screen,
and you'll define some agents or some actions
that this job step is gonna take.
For instance, on this screen you can see that we're
performing a back-up database command
for the AdventureWorks database,
so that's a T SQL command.
We could've also run a number of different things
like a PowerShell script, or a CommandShell script,
or we could've launched one of the replication actions,
or we could've launched
a SQL Server integrations services package.
We could've done a lot of different actions here.
But in this case, it's a T SQL command, and you can see
we can have multiple job steps within a given job.
Once you've set up those job steps,
the next thing is to go ahead and set up a schedule.
And schedule is one of the next pages that you could see
on your new job wizard, your new job dialogue.
And when you set up that schedule, you can see here
that you give the schedule a name,
you can set the schedule time,
there's once only, there's recurring,
in response to an event.
But in this case it's gonna be a recurring schedule.
You set up the frequency of that schedule,
how often it's gonna run.
Once a week, daily, whatever.
Then you set up whatever time it's going to run,
and basically once you've set up schedule
then the SQL Agent will automatically launch your jobs
in response to that schedule.
So that's kind of a quick overview of the SQL Agent.
Let's go in and have a look, hands on,
and see about creating an actual job.
So to get started working with SQL Agent jobs,
we first need to make sure that the SQL Agent is running.
And we can do that with SQL Server Configuration Manager,
like you see here.
Here you see SQL Server Agent is running,
when to start and stop it,
we can right click and use the context menu.
If we want to configure it we can double click it
and we can change the account that it logs on with.
We can also change the start mode,
right now it's set at manual, we can set it to automatic,
to cause it to start whenever SQL Agent
and the system starts
and on the advanced tab we can turn on and off
error-reporting and customer feedback.
But that service tab in the start mode is probably
the most important thing you'd want to change here.
In this case, you probably do want SQL Agent
to run automatically whenever the system starts.
Once SQL Server Agent is running,
you can work with it by using SQL Server Management Studio,
and you'll see a SQL Server Agent node.
If the agent service is active,
there will be a little green arrow here on the node.
If you expand it, you can look at the jobs that are there,
this is a set of default jobs that come with SQL Server.
If we want to go ahead and create a new job,
we'd right click on the job node,
and we'd say new job.
This will open up the new job dialogue.
We'll give it a name and we'll call it My Job.
And we can give it a description, and we'll say,
this is an example job, and that's probably good.
And the important part is to set up the job steps
for the job.
And the job steps are basically the actions
that are gonna be performed.
Initially there won't be any, so it'll be blank,
and here we'll set up the first step,
and this will be, we use the database backup,
so let's go call it Database Backup,
giving it a step name.
This is gonna be a T SQL command,
we're gonna go ahead and run it under this current context
and we'll say, backup database AdventureWorks 2012,
as the database that I have out there.
If we wanted to say, open a set of SQL commands,
we could use the open button to do that
and it would open a SQL file.
We could copy and paste, we could parse the command
using this button to make sure that the command is valid
and the syntax is good.
And over on the advanced tab,
we might have multiple job steps.
And if we did, we might want to control what happens
after a successful action or after a failure action.
So after a successful one we could quit
and report it as a success,
put it as reported as failure,
or most likely you'd wanna go ahead
and go onto the next step.
Likewise, on a failure, you'd probably wanna quit
and report it as a failure,
but you could also go onto the next step
or even report it as a success if you wanted to.
So now once you've set up your job steps,
you basically have created the actions
that you want this job to perform.
And as you can see here,
we can have multiple job steps in this case.
This job is a simple one, with only one.
But we could have multiples.
And next we are gonna go ahead and create a schedule for it.
And likewise, at first, there are no schedules
so we'll go ahead and create one.
And we'll call this one My Schedule.
And for schedule types, we can have it as recurring,
or we can have it whenever SQL Agent starts,
whenever the CPU becomes active,
or as a one-time job.
Pretty much for regular jobs
you'll want to set it to be a recurring type of job.
Then you'll set up the frequency.
Is it gonna be weekly, daily, monthly?
If it's gonna be daily, when is it gonna recur on?
In this case, it's set up to run weekly on Sundays.
What time is it gonna run?
And is there a duration for the job?
So that pretty much has created the schedule.
So now we have created a new job,
and that job's pretty much ready to run.
And you can see it down here.
And if we wanted to control that job,
we could start it at a given job step,
we could look at it's history,
well since it's just created right now
it's not gonna have any history.
But if we were, if we would run it,
that's where we would see that.
And if we want to look at the different job statuses
that are out there,
we could open up the job activity monitor
and you could see the status
of some of the jobs that have run.
And that's the end of this presentation
about creating SQL Agent jobs and scheduling them.
Thank you for watching.