>> You've learned a lot about Elastic Jobs,
but how do you troubleshoot when things go wrong?
Learn now in this episode of Data Exposed.
[MUSIC]
>> Hi. I'm Anna Hoffman and
welcome to this episode of Data Exposed.
Today, I'm joined by Kate Smith,
a Program Manager on the Azure SQL Team.
Thanks for joining us today, Kate.
>> Happy to be here, once again.
>> We're always happy to have you.
Today, we're continuing our series on Elastic Jobs.
Now, we've seen what are they,
the fundamentals, how to create them,
how to automate them,
and create schedules for them,
and now we're going to go through what happens when they go wrong.
Can you go ahead and take us through that?
>> Absolutely. If you recall from the previous episodes,
we had created some jobs and we ran them successfully,
but let's take a look at what happens
when we try to run them and something doesn't go as expected.
I'm going to first walk through how to do this in PowerShell.
You see here, we're going to bind our server,
get our agent, get our PowerShell demo job.
This is what we had last time.
Now, we're going to try to run it again.
So we'll go ahead and run that.
Let's have a look at some of
those executions, let's see how it's doing.
I may or may not have sabotaged
it between the last episode and this one.
Again, we have that WaitingForChildJobExecutions.
It's still going.
But I have a feeling something has gone wrong.
I sure hope it has. No, did I get rid of that query?
Let's have a look and see what's happening here.
When we look in our T-SQL Query to monitor this job,
we can see that it can't connect
to the server that it used to connect to.
It was able to connect
to the first server, but not the second one.
I wonder what happened?
I can tell you what happened is that I forgot to enable
the firewall rule on
this server to allow the Elastic Jobs to execute against it.
Why can't I find it here?
"Firewall." Yes, I forgot to enable the firewall.
You can't execute Elastic Jobs
against servers unless you've actually said,
"Allow Azure Services and Resources to Access this Server."
I have to click "Yes" and "Save" it.
It's only once you save it and allow that firewall,
and you can't create an individual rule,
it needs to be "Azure Services."
But if you don't do that,
then the Elastic Jobs agent won't be able to connect to
that server and query for the databases that are there,
and therefore won't be able to execute queries
successfully or execute the job successfully against your targets.
Let's go open up our Azure Shell again,
and let's try executing that job one more time.
Let's actually look at the past
three executions of this and see what happened.
So one that succeeded.
This one got skipped,
I think it would have failed but we
just went ahead and restarted the jobs.
So it just said, "Skip for now."
It was going to fail due to the firewall rules,
so now we're just waiting for it to realize that it's failed.
The first thing you need to know is how to do that T-SQL Query
to get those failures and see how it's doing.
Now, we should be able to execute this again.
Here is what happened. As I executed it too fast,
I fixed the problem too soon because I knew what the problem was,
and so it didn't go ahead.
When I tried to execute it again,
it didn't even try because it says it's already executing.
This is interesting.
Looks like I have a typo somewhere in my T-SQL,
because what's a "Tabe?"
Looks like maybe we have a typo to fix.
Let's investigate what it takes to see in the job.
Let's go to our job.
We have two steps here.
"Select @version" is fine,
but it looks like we have a typo.
We can't create a "Tabe R",
we need to "Table R." So we need to rebind
that value in our job.
Rebind that step to something without the typo.
Let's say sqltext equals.
We're creating "Table R", so we should have this "bR."
Awesome. Then we can go into the job and
get that step1.
We need to bind it to a variable here.
There it is, and then we can just say,
"At our job step." Then we set azSqlElasticJobStep.
Awesome. So it looks like we no longer have that typo.
Let's go ahead and
get our job back.
Let's do it again and we'll see what happens.
Hopefully, this time we will be successful.
>> Awesome Kate. This is cool.
I was pretty impressed that you didn't have
to delete the whole job and start over.
You could just fix where you made an error,
and then beat up back up and running.
>> Exactly. It's pretty straightforward,
and you can do it pretty quickly.
It's pretty nice to be able to know how to
investigate the jobs and see what the failures are.
It's cool that you can just go ahead and get
individual steps and just see what the text is.
You can see it change from typo to non-typo.
It's pretty neat.
It takes a long time to fail with the firewall rule.
Unfortunately, we have to be patient to see it succeed.
But, I don't know.
That's how I do troubleshooting.
So it does require a little bit of T-SQL,
you can do all of the job step updates.
I'm also using T-SQL,
you don't have to use PowerShell.
It's pretty well-documented on our "Documentation" page.
So it should be pretty straightforward to see how that works.
This basically the same idea you get to step out,
you can investigate, look at the job.
I can even show you what some of that looks like.
You can just look at this job,
and here we see information about it.
But there's other tables that
will also tell you about job steps and stuff like that,
and you can go ahead and rebind and
change those on the fly using T-SQL as well.
The best way to monitor and get error messages though is through
T-SQL Queries to the back-end database
to get the full text of the error message.
>> Awesome. That's cool, Kate.
I really like your blended approach.
It seems like some things you prefer to do in
PowerShell and some things you prefer to do in T-SQL,
and then there are some things that you can only do in
T-SQL like this last map that you've shown us.
>> It's interesting because I feel like it's more
realistic as to what people do.
Everyone's mixing all these technologies together.
>> Yes, they are. You got multiple windows open,
and just doing what works for you is the most important thing.
I think Elastic Jobs support a lot of
different modes, which is really cool.
>> Awesome. Well, thanks so much for being on
the show today, Kate.
If you like this video,
please like this video and subscribe to our channel,
and let us know in the comments what you'd like to see next.
Thanks for joining us on Data Exposed.
[MUSIC]