>> You've learned what Elastic Jobs are, but, in this demo,
you will see how to actually go about
creating them using PowerShell,
this week, on Data Exposed.
[MUSIC]
>> Hi, I'm Anna Hoffman.
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.
>> I'm excited to be here.
>> Today we're continuing our series on Elastic Jobs.
We've talked about what are Elastic Jobs,
as well as some of the important things you
need to consider when you create Elastic Jobs.
But now we're going to dive into how do you
actually do this and how do you do it with PowerShell.
Now, Kate, can you start by telling us
why someone might want to do this with PowerShell?
>> Sure. You can
create Elastic jobs both using T-SQL and PowerShell,
but PowerShell has the advantage of being more scriptable,
all the advantages that come with PowerShell.
There are some advantages to when you create the credentials,
creating them through PowerShell because then you
can create them through the Elastic Jobs agent.
You'll see that when I do the demo,
or we'll talk about it when I do the demo.
Then, all of a sudden, the credentials that
you create to connect to your target
are associated directly with the Elastic Jobs agent,
and not with just the general server.
They're just associated, coupled tightly there.
>> Awesome. Well, I know you have
a lot that you have planned to show us,
so let's get right into it.
>> Cool. Before we dive into actually doing the demo,
I've created some resources that we will be using for the demo.
I created a resource group that will hold
all of the servers and
databases that we're going to be using for this demo.
This is a server that will host the Elastic Jobs agent.
A database,
which that database will then host the Elastic Jobs agent.
I created a server and
a database that will host the Elastic Jobs agent,
but I haven't created the agent yet.
Then I created two other servers,
elasticjobstarget1 and elasticjobstarget2.
Target 1 has two databases on it that we will
target to run our Elastic Jobs against,
and the Target 2 server has
one database that we plan to run our jobs against,
and one that we want to exclude.
I'll show you how to exclude a database from
a server when we get to that.
What I have here is just a simple T-SQL that just says,
basically, our job is
just going to simply create a table called T. That's it.
It's going to be simple table.
To make it item potent,
I have done this IF NOT EXISTS SELECT part.
That way, if we run this again and again,
we won't fail because the table already exists.
We would just create the table when it doesn't already exist.
We know what our job is going to
do and we have targets for the job.
The next thing we need to do
is we're going to do this in PowerShell.
Let's open up our Cloud Shell.
We already have a database created to host our Elastic Jobs agent.
The first step is to bind that database to a variable.
Then once that's bound,
we can then use that database as the target of
creating our Elastic Jobs agent.
I've named it ps_demoagent,
which is basically the PowerShell agent
for this demo. It takes a bit.
My next step is to create credentials.
What I think I'm going to do is I'm going to
just do that off video, or whatever.
Here we have our Elastic Jobs agent has been created. Here we are.
We've got our resource group, our server name,
our database name, our agent,
and it is ready to go.
The next step is to create credentials,
which I'm going to do off-screen.
I'll go create those credentials and then we'll come back
and add our target groups and create the job.
Now, I've created the credentials.
I have them created,
so I'm now going to create a target group.
I take the job agent and I create
a new target group with the name PowerShell demo group.
Now, the next thing I want to do
is to bind to that group to a variable,
which is pretty straightforward.
Target group is now bound.
Then I can use that to add the servers that I want.
Now, I can add this server keeping in mind that this credential
and username already exists on the target server.
Here we are. We've got that working.
We've got that refresh credential
added and the server name added to our group.
Now we go on and we're going to do
the same exact thing
for the second server that I created,
elasticjobstarget2.
Let me pull up the command here.
Then we need to actually exclude;
there was one database we were going to
exclude from that second server.
We issue this command,
which calls out that same server,
but the database that we want to exclude, and exclude it.
Now we've created target group PS_DemoGroup,
and it exists on our job agent.
Now we need to create a job.
Let's go back to our job agent and create a new SQL Elastic Job.
We're going to name it ps_demo because it's a PowerShell demo.
That job is going to need some SQL text.
We talked about what that SQL text would be at the beginning.
We're just going to bind it here,
should look pretty familiar.
Then we add a single job step to the job.
We're going to call it step1.
The job will have only one step.
It will use our job credential and the text will be sqlText1.
I do need to fix the name of this,
however, to be PS_DemoGroup.
Now we've created a job that has one step,
it's on this group, it's using a job credential,
and this is the text. So there you have it.
We have used PowerShell to create an Elastic Jobs agent.
We have created the agent,
we created the credentials off-screen,
and then we created the target group and the jobs.
>> Thanks, Kate. This has been really great.
I really like how,
with just a few simple PowerShell commands,
you're really able to get this thing up and running.
Now, I noticed that you labeled this Step 1.
Is it possible to have multiple steps?
How would I go about doing that?
>> You would basically do what I
just did with the Elastic Jobs step.
You would just call it step2.
Let's add some ad hoc SQL text here.
Now we've got two steps.
>> Got it. I see that it has a step ID,
so there's some order that's obtained in this job.
>> Yes. Exactly. We've got step ID1,
step ID2, step1, step2,
both in the same job.
Exactly. They're using the same credential.
>> Awesome. Now, you mentioned they're using the same credential,
what if I wanted to use
a different credential or I
needed to use a different credential for different step?
>> That's very easy to do. You just have to create
that credential on all your targets as before,
make sure that it has the permissions to do what it
is you're trying to do in that job step,
and then just follow the same steps.
It's just adding a second credential or an nth credential,
as it were, and just
using that when you create the step or add the step to the job.
>> Awesome. Well, thanks so much for showing us this today, Kate.
In the next video, I think we're going to dive in how
to do the exact same thing, but using T-SQL.
>> Yes. Absolutely.
>> Awesome. Then, in a future video,
we're going to dive into how you actually go
about scheduling and monitoring these things.
So we still have a lot of exciting things to cover.
>> Yes. Absolutely.
>> Thanks for joining us today.
We hope you enjoyed this episode. Leave us a comment.
Leave us a like to say what you want to see you next time.
We hope to see you on Data Exposed.
[MUSIC]