>> In Azure SQL Database.
learn about all the new ways that you can use
to automate things like scaling up and down,
like T-SQL, Logic Apps and Elastic Jobs.
This week on Data Exposed SQL Insider edition.
[MUSIC].
>> Hi, I'm Anna Hoffman,
and welcome to this episode of Data Exposed,
our SQL Insiders Series.
Today I have with us a very special guest, Stephen,
who is a Cloud solution architect and a SQL insider.
Stephen, thanks so much for joining us today.
Can you kick us off by telling us a little bit about what you do?
>> Sure. Thanks, Anna. As a Cloud solution architect,
what I do is really help our customers
understand how the data services work
and how to use them to solve their problems.
>> Cool. Sounds great and
sounds a great thing to have on our show.
Today, we're going to be talking about Azure SQL Database.
Specifically, you wanted to talk about how you
can Automate scaling up and scaling down.
Before we get into it, I wanted to ask,
you why did you want to cover
this topic and what is this topic really about?
>> Thanks for that. I get a lot of
conversations with my customers.
One of the great things about
SQL Database and all the past services,
is the ability to both scale up and scale down so
that you use the right amount
of resources for the job you're doing.
We do this a lot with all of the areas around our Azure services.
But when I talk to DBAs,
it's sometimes a challenge because a lot of
them are not infrastructure people,
so they don't know PowerShell and they may not have
access to the Azure portal.
But when they're working with their SQL Databases,
there are times that they do want to have some scale control,
particularly if they have big job running or they need to
change the size of their max database size.
They want to be able to do that.
They want to be able to Automate it,
but they don't necessarily are going to have all of
the types of controls
and tools that Azure admins are going to have.
I want to focus this for the DBA
today and do everything in Transact-SQL.
>> Okay, exciting. I think our DBA are really going to like that.
Let's get right into. What do you got?
>> Sure, so just take a look at my deck here.
Everybody knows that we can scale.
There's a scale both in the DTU model and in the vCore model.
Really what this means is that I can take
a SQL Database and I can have it
small or big and I can go back and forth.
Now, what's really nice about SQL Database when it
scales is the way that it scales and so the way it works.
When I issue the scale command,
it makes the change behind the scenes.
It actually creates a brand new instance of my database,
so it goes through the whole provisioning stage.
While my database is still in use,
and then when it's ready to go,
it actually pauses the connection briefly.
It repoint from the DNS,
from my current instance to the new instance.
It detaches and reattach is the database.
There's zero data loss and then my database comes up.
It may take minutes or even hours to move a database.
But my actual downtime is usually a minute to two minutes max.
You can control all of this with Transact-SQL.
As I am working with my databases,
if I know I have periods where I need a bigger job.
The client that was the impetus for this presentation for me.
They have a batch job that runs on
weekends and they've tuned their database.
They've got the right size.
But this job is a lot heavier than their usual workload
and it was starting to spill
into the production of on Monday morning.
What they wanted to do was
automate the increase in the database so that it had
more horsepower to run the job and
then finished within the time window that had allowed it.
Then they wanted to bring things back down
again because they didn't want to pay for
a much larger instance than they
actually need for their day to day operations.
That's what I want to show today.
Why don't we just take a look at the code here?
The first thing I want to do is I want to
look at just how we can scale
out the size of the max database size for my database.
Let's just take a look at this instance
that I have here right now.
It's a clever name of autoscaledemo.
I can see here that it's a general purpose.
It's a Gen5 with two cores and it's got about 75 gigs of space.
Now what I want to do is I want to add another 50 gigabytes to
this max database size so
that I've got room for the work that I'm doing.
All I'm going to do here is to clear some variables.
I'm going to capture
the size that I want using that database property x function.
I'm going to add
my current size with 50 gigs to make it the new max size.
Then I'm going to dynamically create
an ultra Database statement that
just simply modifies the max size value.
If I take all of this and give it a run.
This one actually runs quite quickly because it's so small.
If I go back and take a look again at my sizes.
We've now added 50 gigs to that.
It's up in the max sizes there now.
We're haven't actually moved anything.
That's why the speed is so fast.
If you go and look at the database itself.
You can see it's really only using 21 max.
It's got 32 max allocated,
but by max size is no
bigger because we're not really moving anything,
there's no interruption on the database.
As a DBA, I can go in,
I can monitor my size,
I can make my changes dynamically.
I don't have to bring the instance down in any way,
and I can do it all with Transact-SQL.
Now let's take a look at actually doing this
but with changing the actual size.
>> To do that, what I need to do is I need to
change what's called the service objective for my database.
The service objective is
just the t-shirt size
setting for what size of instance I'm running.
In this case I know I'm running
a general purpose Gen5 with two cores,
and what I'd like to do is I'd like
to scale that up to four cores.
If I take a look right now,
I am running two cores,
and I'm going to run my alter database statement,
and it says successful. Which sounds great.
>> That was first.
>> Yeah, that was fast. But if we take a look,
it's still a Gen2 and if I go and see what's going on,
I actually have here and it doesn't look like it's running.
I've got a little loop that's just
querying what the current service objective is and
what we're going to see is that it's going to run here
for a minute or two and
then we're going to see a service interruption.
Actually the connection is going to be broken here.
As soon as that happens,
then I'll know that my change is taking place.
Right now everything is still running in
the background the whole new instance is being created.
It's being created as a Gen5 of four cores,
and at some point we'll stop the connection.
There we go right there.
You'll see here I've lost my connection.
If I go back and look at my service objective
, I'm now a Gen4.
Now, this was really fast because my database is almost empty.
I did that for the purpose of the video.
If you had a terabyte database that cut over could take an hour or
more depending on the size
you're moving to and depending on how much data you have.
Because it's actually going to detach the data files,
move them over, and then reconcile everything together.
The only thing I really lose are
any uncommitted transactions when the cut over takes place.
But I've done this all within Transact-SQL.
Now, for the automation standpoint,
there's lots of things I can do to automate this.
Here's one example.
I have a logic app that I've created here,
and if I go into the designer and again,
very simple logic app.
I set my recurrence so I'm going to schedule when this happens.
I create a simple SQL statement,
I do my ultra database just like I showed you in the script.
I then do my batch job or whatever it is that I needed to get
done with the process and
then at the end I just scale it back down again.
Just using Transact-SQL and with simple automation tools,
what I can do now is I can schedule my scale up,
then run what I need to do,
then schedule my scale down.
Now, because it takes
time and because there's service interruptions,
there's a couple of things I have to make sure.
One, my applications that I'm connecting to my Azure database,
I want to make sure that I'm using retry logic in those apps.
Now, that's not just because
of this that's actually best practices for
working with people as you're making sure
that the connections are always there.
The other thing is I don't want to set this up to
automatically scale up and down based on state performance.
If I want to scale on
performance then looking at SQL database server
lists or elastic pools or
some other technology is probably a better bet.
But if I know I have those jobs where I need to scale up,
do something and scale back down again,
these are really good tools.
Then the other thing that's in preview right now but is
coming up soon is I can do elastic jobs.
With these jobs, again,
always Transact-SQL, I can define a job.
I know that you've done a whole series on
this so I'm sure we can put links to those.
But I can define a job just like this one here,
put in the altar database text that we saw, set the credentials,
and then I can schedule that job all
within the elastic job scheduler.
Lots of options there for DBAs getting
back very similar to what we had
with SQL age and with automation jobs.
We can take the full advantage
of the scalability of Azure but still be in
that familiar world where the DBA feels
comfortable and where you are working in the space that you know.
I try to make sure that the community was aware of this
and can really get the best advantage from SQL database.
>> Awesome. Thanks so much, Stephen.
Actually, I love the way that you took us through this.
You showed us, Hey,
this is how you change your slow using simple,
good old T-SQL that we all know and love.
Then you said, Hey, you want to take it to the next level,
check our logic apps which are super easy to
use you can set up automation yourself.
Then you came back and said, Hey,
if you really like this T-SQL,
there's an option for you in Azure SQL database.
Like you mentioned, we don't have SQL agent jobs
in Azure SQL database but we do have elastic job.
I love how you went full circle with this.
I think it was really useful.
I learned a lot I think our viewers
probably learned a lot as well.
Do you have any tips or tricks for folks who are just getting
started with automation in Azure SQL database?
>> Yes. Make sure that you understand
the various sizes because they're all preset.
The amount of cores,
the amount of RAM you have.
There is also some limits where
the maximum amount of storage I can
have is contingent on the number of cores I have.
It's good to go in and take a look at
the scale sizes and make sure you understand them.
Just remember this is still a SQL database.
You're still tracking performance.
You still have that level of control you
have on prem you're really just
extending into a new and more exciting venue
for working with your data services.
>> Awesome. Well, thanks so much for that, Stephen.
Thanks to our viewers for tuning in.
For our viewers if you like this episode,
go ahead give it a like, leave us a comment,
and let us know how you are using automation in
Azure SQL database and we hope to
see you next time on Data Exposed.
[MUSIC]