>> In the last episode,
you learned about elastic jobs
and why you might consider using them.
In this episode, Kate's going to dive into
the two fundamental concepts you need to
understand before you create elastic jobs.
This week on Data Exposed.
[MUSIC]
>> Hi, welcome to this episode of Data Exposed. I'm Anna Hoffman.
Today, I'm joined again by Kate Smith,
PM on the SQL DB team. Welcome, Kate.
>> Yeah. Thank you for having me back.
>> Happy to have you back. On the last episode,
we talked about elastic jobs and more of
an intro of when you might use them and where they're supported,
and we definitely recommend you go watch
that video before this one.
In this one, Kate's going to tell us
about some of the fundamental concepts.
So Kate, I believe you mentioned there
are two fundamental concepts.
Can you tell us a little more
about what those are and what they mean?
>> Sure. So there's two pieces of groundwork that
it's helpful to understand it before you dive
into trying to create elastic jobs.
The first concept has to do with the way you structure
the T-SQL scripts that run when you execute your elastic jobs.
That concept, the name for it is idempotence.
In essence, the script must be idempotent,
it must be able to run on
a repeated basis without causing problems.
So and by problems,
I mean that if you are adding a column to a table,
you don't want to add
that column and then accidentally run the script a
second time and try and add that column again, things like that.
Then the other concepts is having an understanding of
the complicated credential system that's
used to authenticate and run elastic jobs.
Yeah. So those are the two concepts.
>> Awesome. Thanks, Kate. First one,
you're talking about idempotence, it makes sense.
We want to make sure we're not creating
extra columns or that thing,
and I'm guessing maybe I can write some extra T-SQL to do that.
How do you advise people make sure this doesn't happen?
>> Absolutely. So it's a simple modification basically to
a T-SQL script where
you are creating something or you're updating something,
you basically can just do a check,
making sure that the table you
are adding columns to doesn't already have that column,
making sure that column isn't already created.
So you can do is to existence check,
or if you're doing a stats update,
you can even check for
when the last time the stats were updated before you
go off to update them to make sure that they weren't
updated just 20 minutes ago that they were updated,
within the amount of time that you
want for the period of your elastic job.
So if you're trying to update stats once a day,
you want to make sure your stats are at least,
23 hours old before you execute this basically.
So you just check for the condition.
Does it exist or is it
basically stale enough before you go off and execute the T-SQL?
So it's just a modification of the front or
basically wrapping your T-SQL
before you execute and queued it a barrier.
>> Awesome. Yeah. That makes sense.
It seems like these simple checks
are like the little bit of extra effort
that it might take to put these in is really going
to save us in the long run.
So that's awesome.
If we move to the second one,
you started talking about credentials,
and I don't know much about this.
So can you tell us a little more how you might go
about setting this up or what it means or anything like that?
>> Yeah. So I think the first thing to understand is
that when you're running elastic jobs,
you have a database setup
that has the host of the elastic job agent,
and that is where the jobs are running from,
and those jobs need to authenticate to your target.
So basically, the things
that you're connecting to run your elastic jobs against.
So it makes sense that you would need to
have credentials to connect to those targets.
But the way we do this is to keep those credentials secure,
we create those same credentials on the elastic jobs agent host.
That keeps them secure, their storage credentials,
they're safely stored there.
So any credentials that you need to run
that T-SQL over here or to connect over here,
you need like basically mirror it on
the host database and then
configure the elastic jobs to use
those credentials when connecting to execute.
>> Where is this host running?
>> Yeah. Sure. So it's often on
a separate server or separate database.
It would be in its own database,
often in a different server
than any of the things that you're targeting.
So it is running in Azure on just some other server.
It can be any general-purpose database.
Does not need to be fancy-schmancy or anything like that.
It basically stores the job,
connects to the targets, and then executes.
>> Got it. Okay. So we need the host to have access, makes sense.
Are there other credentials that we need to think about?
>> So there's two basic types of credentials.
One, we call it the refresh credential.
You can configure elastic jobs to
run against every database on a server.
So basically, when you add new databases to that server,
those databases would automatically be picked up and
become part of the targets for any elastic jobs you create.
So this is a really streamlined and powerful way of just,
you target that server and every database
gets hit unless you explicitly exclude it.
So that's nice for our maintenance purposes
that you don't have to go and
keep adding new databases to
some master list or anything, it just does it.
The thing is, in order to support that,
you have to have a credential that allows you to connect to
that server and list out all of the databases on that server.
So we call that the refresh credential.
It refreshes the set of databases that it's targeting in a server.
So when you create this,
there's a elastic job agent,
you also have to create this.
We call it master cred or refresh
credential because it's basically
what's used to refresh those jobs.
It's the same credential used for every server that you target.
It's not stored separate for target,
it's stored for that job.
So yeah.
>> Got you.
>> That's the refresh credential.
Then there's another credential,
we call it the job credentials.
So every elastic job can have one or more steps.
Each of those steps runs with
a specific credential that is defined.
So in those targets,
there's a credential that has to have
the permissions to execute that T-SQL,
if you are connected directly to that database,
what credentials do I need to execute this T-SQL statement?
So every job has one or more steps.
Each step corresponds to a specific set of
T-SQL and a specific credential.
>> Got you. Okay. So setting up these credentials,
it seems like it can take some time,
but I believe you actually have a blog post where you
walk us through how to go and get these setup?
>> Absolutely. Yeah. It's not that hard.
I think that it's
basically just doing something on the target and then doing it
on the elastic job agent host machine and going back and
forth to make sure that you have
all the credentials created and that
everything can authenticate correctly.
>> Yeah. Got you.
Awesome. Cool. Well, thanks Kate for joining us today.
We learned about the fundamental concepts,
talked about idempotence as well as credentials.
If you enjoyed this video,
we encourage you to like it and subscribe
because Kate did one earlier,
and I believe we have a few more plan, is that right Kate?
>> Yes, we do. We've just started to scratch the surface.
>> Yeah. So be sure to tune in
next when we talk about part 3 for elastic jobs. Thank you.
[MUSIC]