>> You've now learned a lot about elastic jobs.
What you haven't seen is how to
create them using good old-fashioned T-SQL.
This week on Data Exposed.
[MUSIC].
>> Hi, my name is 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 again.
>> Excited.
>> Today we're going to continue our discussion on elastic jobs.
Now, we've talked about what these
are and what's important when you create them,
so be sure to check out those videos if you haven't seen them yet.
They'll be in the description in a link.
In this video, we're going to see how you can actually create
those elastic jobs using good old-fashion T-SQL.
>> Yeah, absolutely.
>> Why don't we just go ahead and get right into it, Kate.
>> Sure, absolutely.
Just to recap,
you might have seen our PowerShell video, but in case you didn't,
I created a resource group that
has a server that will host our database,
that hosts our elastic job agent.
Then two different target servers that we
will execute the elastic jobs against 1A and
1B or individual databases and target 1 and
2A and to exclude our databases in target 2.
We plan to target the specific databases of 1A, 1B,
and 2A and we hope to see target
2 excluded when we execute our job.
There is no T-SQL way to create
an elastic job agent through T-SQL.
We need to create it in the portal.
Let's go back to Home,
go to Elastic Job Agents. Right there.
We want to add one and we want to call it,
oh, I didn't plan this very well, I'm sorry.
We want to call it the tsql_demoagent.
Then we need it to go onto a server.
I said I already created one.
Here it is, elasticjobagentserver and then we've
got this database. Here we are.
We're creating elastic job agent called tsql_demoagent
and we're creating on the server we
created on the database that we created to host it.
Let's click "Okay".
Yeah, tsql_demoagent,
our subscription, and then that,
so let's go ahead and create.
Awesome. Now, we've created our elastic job agent in the portal.
Great. The next thing we want to do is create some credentials.
Let's cutaway and let me create the part of
credentials and then we'll come
back to it when it's time to create the target group.
I have created my credentials,
and now I'm going to go ahead and create a demo group.
I'm going to add a target group
and this one I have called TSQL_DemoGroup.
There it goes.
Then we're going to add
our server target 1 into TSQL_DemoGroup.
It is a SQL Server.
We have our refresh credential already created and
then we'll go ahead and we'll do the same thing for target 2.
That's all that's changed.
Then we want to exclude the one database.
Let's add the command to exclude
the excludable exclusion database. Right here.
Demo group exclude this particular database on this server,
that's its name, and we go ahead and execute that.
Great. If we want to view the recently created target group,
we can execute this command and see what we get.
Awesome. Now we want to see what
our target group is, and make sure that it has been created.
Here it is, there's our target group,
there's its ID and then we can even look
and drill down into the contents of that by
executing select target group members and we
get two servers that
are included and one that is excluded to the specific database,
which looks right to me.
Great. Now that we've got our target group defined,
it's time to create a job.
You will add this job,
we'll call it T-SQL Demo with that very illustrious description.
The T-SQL that we're going to be using,
I'm going to just pull that out and show it to you here,
is we're creating a table,
we'll create a table S if it doesn't already exist,
it's very important to add this IF NOT
EXISTS portion to the query because
executing this multiple times in a row could cause
a failure that would make it not item potent,
which as I have discussed in a previous video,
is essential for making elastic jobs commands work smoothly.
Let's take that T-SQL and let's put it into adding a job step.
We're going to reference the job name that already exists.
We've got our job credential.
There's the T-SQL we discussed with
properly escaped characters in our T-SQL demo group.
Awesome.
Now
we can
query our jobs and we can see that it has,
we're in version 1, it has never run, etc.
Awesome. There we did it.
We created a job and we can see it present in
our agent host database and we're ready to run them.
So stay tuned for the next video.
>> Awesome. Thanks Kate. I had
a few questions while you were going through this.
It's really interesting and it's really cool to see how
you can do in both PowerShell and T-SQL.
My first question, which is really not neither here nor there is,
when you create this,
it looks like you created at a server level to include databases,
but then you also at a database level chose to exclude databases.
Could I also create just at a database level,
an agent job or elastic job?
>> When I defined the demo group previously,
so let me bring this command backup and display it.
Here when I defined it as this command
previously to add this database,
the specific database to this group,
I could have chosen instead to put include
here and it could have been a totally different server.
You could do this with just a new server,
with just a single database that you want to
include and just include a single database. Absolutely.
>> Awesome, cool. Thanks Kate.
It's really interesting because it's
nice because it seems like we have the flexibility
to run these jobs on the exact servers and databases,
excluding or including whichever really
is the easier option. That's pretty cool.
>> Yes, it's incredibly powerful.
>> The second question I have is now we've
seen it in PowerShell and T-SQL,
are there advantages to using T-SQL
or is it more of just a preference thing?
>> I would say, yeah,
it's mostly preference thing.
The big advantage to T-SQL is if it's what you're familiar with,
if it's what you're used to doing and it's how you're used
to interacting with databases that's
available and it's easy to do that way.
It's more of a user-friendly for particularly getting
started people who aren't familiar
with or comfortable with the PowerShell portion.
>> Awesome, cool. It seems like it's really up to you.
You're going to get just about
the same functionality with a few exceptions,
like you have to create something in
the portal if you're going to use T-SQL.
>> Yes.
>> Awesome, cool. Well, thanks so much Kate for being on
the show and that's not it for elastic jobs.
Now, we have a job with a single step and in the next video,
we'll learn about how to schedule these jobs,
how to monitoring these jobs and maybe even multi-step jobs.
We'll see what we get to. Thanks so
much Kate and if you liked this video,
please like this video,
subscribe to our channel and we hope to
see you next time on Data Exposed.
[MUSIC]