>> You've learned a lot about elastic jobs so far.
Continue your learning in this episode of Data Exposed.
[MUSIC]
>> Hi. I'm Anna Hoffman,
and welcome to this episode of Data Exposed.
Today, I'm joined once again by Kate Smith,
a Program Manager on the Azure SQL Team.
Thanks for being with us today.
>> Happy to be here.
>> Today, we're continuing our series on elastic jobs.
Now, we've had quite a few videos to understand what these are,
the fundamentals around them,
and how to even create them.
Now, once we've created them,
there are some other things we can do.
Why don't you show us some of those things, Kate?
>> Absolutely. So if you recall last couple of videos,
we defined and created a series of elastic jobs.
Just to recap quickly,
we've got a server here that host
the elastic jobs agent in this database,
and then we've got two target servers, target 1,
target 2, with two databases in each.
1A and 1B are both included as targets of our elastic job.
2A is also included,
but to exclude should not be hit as a target from the elastic job.
So first, I'm going to show you how to do this in PowerShell.
The first thing we need to do is pull up our Cloud Shell here,
and then we need to get the job in order to run it.
So there's a couple of steps.
Awesome. So you see here,
we've opened up our Cloud Shell.
First, we're going to just bind to
that server that hosts the elastic job agent.
Then the next step is quite simple.
We will just bind the job agent
so that we can pull the job out of it,
and just to refresh everyone's memory,
we defined two different jobs,
and when I do this,
you can see them both listed here.
There's a PowerShell demo and a T-SQL demo.
For the purposes of PowerShell,
we will pull out the PowerShell demo job and bind it,
and then in order to run it, it's quite simple.
All we have to do is just say,
"Start AzSqlElasticJob" and that will start it.
Now, as you can tell,
like, "Sure, it's running",
but how do we know it's doing?
How do we know if it's doing anything?
So I'm just going to come out here and I will
get some of the job executions,
and here you see, this is the job we just started
and it is waiting for itself to finish.
I don't know exactly how long that takes,
but if you recall,
the definition of that job was simply to create a table
called T in some of our target servers.
So over here, if we go over to our target server,
target server 1 for example,
we can see the elastic jobs data and target
2A would have created to the table T. Let's go
back and see if we got
the table T created in the one that we were trying to exclude,
should not be there, and thankfully, it is not.
Let's try that again with the other target server.
Just to verify that the job worked correctly,
we go to target 1A and we execute this query,
and indeed, T was created,
and the same for 1B.
So it looks like our definition of
our target group worked exactly as expected.
We got both the databases in target server 1 and
only the non-excluded database and target server 2. Awesome.
>> So back in the Cloud Shell,
we would expect that this would now say completed, right?
>> Exactly right, Anna.
As you can see, we can come over and connect to the database that
hosts the elastic job agent and execute this simple query,
and we can see that in fact,
our most recent execution of this did
succeed and it did just fine.
It actually succeeded on
both target 1 twice and on target 2 once, those two servers.
This is exactly what we expected.
So that is perfect.
We can see it in T-SQL by querying that table.
We can come back over here to see.
It still says it's waiting,
but I think it's just cleaning some things up.
I don't think it's actually
still waiting because it sure looks complete to me.
So that is how we can schedule
an ad hoc execution of a job and monitor that execution.
So now, let's say we want to run this every day.
You would do this if you were adding new databases to
these target servers all the time and wanted to
make sure that they always had this table.
This table was a dependency.
Instead of manually creating it,
you could just have an elastic job, add it.
So once a day starting today, enable the schedule.
All we have to do is this.
As you can see here,
we created this job execution and we're waiting
for the child jobs to complete.
Now, because I've bound this as a variable,
it won't show us updated information,
it won't update on the fly.
So we need to just try again,
and look, here, we can see that it succeeded.
Awesome. So we can head over to the databases themselves and
query first the databases that we were targeting.
Let's start with the server 1 and start with the target 1A.
So we should see that table T
has in fact been created on this database,
and in database B,
we should see, again,
table T has been created.
If we go to the other database,
here we are connected to
the exclude database so we should not see any table T,
and in fact, we do not.
But if we go to
the database 2A which should have been included as a target,
there it has been created.
Awesome. Our job succeeded and it did exactly what
we wanted. That's great.
So the next thing we want to do
is try to enable this job to execute every single day.
So we can just set it
to a daily interval
every one day starting today and enable that schedule.
Then just like we had with the job executions,
we need to rebind this to see the new information.
So if we rebind it and look for job,
there we will see that it is recurring
every one day and it is in fact enabled.
One of the cool things that we can do if we want is,
actually, we don't need this anymore.
One of the cool things we can do it in addition to looking to
see that that job is enabled to recur, through PowerShell,
we can also actually look at the elastic jobs agent,
and we can see in our list of
jobs here that it has now been enabled to
recur every day and it will run
every single day against these targets, which is pretty neat.
So that is how, in PowerShell,
you can configure a job,
run a job, monitor a job,
and also set and
see the recurrence of the job as needed in PowerShell,
and you can verify that both
through PowerShell and through the portal.
>> Awesome. [inaudible] Thanks for showing us that.
It seems like with just a few commands, you can create a job,
and then with really just one or two commands,
you can automate the whole thing.
>> Absolutely, absolutely, it sure can.
Even better, you don't have to do it in PowerShell.
You can do it in T-SQL.
You want me to show you?
>> Yeah, please.
>> All right, let's do it.
Over here in T-SQL,
we want to connect to
the database that hosts our elastic job agent host.
So here, we are connected,
and then all we need to do
is we're looking for the job T-SQL demo.
It should have been started,
and that was pretty simple.
Now, maybe we want to monitor that.
So let's try it.
Hey, it look like it succeeded. That's even better.
Step 1, so it's not complete because it has two steps.
It has to go through
and get the list of databases from the server,
and then it gets to do the next step.
But let's go see.
Here we are in 2A, we want to see,
did our table S get created because we were creating
different tables for the different jobs
so that we could tell apart,
and there it was created.
Awesome. Let's see about the other target.
Also, it was created. That's awesome.
So now, we can go ahead and we can execute this and we can see,
yeah, it succeeded. We're happy.
Great. Now, why don't we try to schedule that
to recur for every single day starting today?
It's pretty much the same exact idea as what we did in PowerShell.
It's just slightly different syntax.
Okay. So we ran it once,
we saw it succeeded, we verified
that it did what we expected it to do.
Now, I wanted to schedule this to run
every single day starting today.
So we just execute this command here,
and happily, it goes on and it looks like it succeeded.
How can we verify that?
Well, we could go back to the portal and refresh our view here,
and here, we see that indeed,
we updated the T-SQL demo to run everyday,
and that schedule is now recurring.
Awesome. So we have
successfully run job using PowerShell and T-SQL,
monitor it to make sure that it was doing well,
and then set up a recurring schedule for each of them.
>> Awesome. Cool. Thanks for sharing that with us, Kate.
It seems very straightforward and simple,
but this is something that could save
a lot of people a lot of time
and a lot of stress depending on how you set up these conditions.
I'm sure it can get pretty complicated as far as scheduling goes.
>> Yeah. You could do a lot of really cool things with it.
So it's a powerful tool, absolutely.
>> Awesome. Well, thanks so much for being on the show today,
Kate, and thanks to our viewers for watching.
Let us know what you think by liking
or commenting on this video and we hope to
see you in the sixth part of this series on elastic jobs.
Thanks for joining us on Data Exposed.
[MUSIC]