Up next in 10
Azure SQL: What to use when by Anna Hoffman || Lightup Conference
10K views
Nov 16, 2023
In this session, you'll learn from a member of the Microsoft Product Group the different options for your SQL deployments in Azure. After this session, you should be able to: • Articulate the key differentiators between the Azure SQL deployment options (SQL Server in an Azure VM, SQL Managed Instance, SQL Database, Pools) • Explain the different service tiers within Azure SQL, and evaluate what is best for your workloads and business requirements (Serverless, Hyperscale, General Purpose, Business Critical, etc) You’ll walk away with all the resources (including instructions on how to do all the demos on your own!) Conference Website: https://www.2020twenty.net/lightup #lightup #2020twenty
View Video Transcript
0:00
Primary replica we're going to have
0:01
Tim to be running on directly attached SSD, but our data and log files are going to be
0:09
stored in locally redundant storage. That's what LRS means. So there's multiple copies of in Azure premium storage
0:15
so it's a little easier for us to get to. Then our backup files which are stored in
0:20
Azure standard storage storage, but those Azure standard storage are in globally replicated storage
0:27
which basically means just by deploying It means even if your region goes down
0:32
something catastrophic happens, you have globally redundant storage, so you can do a geo restore without any configuration
0:39
into another region in the case of an emergency. This comes with all tiers and it's something
0:44
that's really awesome in terms of disaster recovery. Now you might be wondering
0:49
okay, so if something happens and I need to fail over, what happens
0:55
Well, all of the Azure services run one on the Azure backbone
1:00
which is Azure service fabric. And so if we detect that something is up
1:05
and we need to fail you over or you want to fail yourself over basically
1:10
it's like a failover cluster instance. We'll look for a node with spare capacity
1:14
and then we'll fail you back over and will repoint you and all that jazz
1:18
but you won't have to change your connection string. Again, you just need to make sure you have retry
1:24
logic implemented in your application. So that's general purpose. Now business critical as the name
1:32
suggests is really meant more for those applications that really need low latency and the highest availability
1:40
possible and these are based on an always on availability group. So if you're familiar with this concept
1:47
basically we are going to keep three secondary replicas automatically created. They'll be ready and will actually let
1:55
you use one of those secondary replicas as a read only endpoint
1:59
So right off the bat, you deploy behind the scenes. You've got three secondary replicas and
2:04
one that you can use for reading. Now similar. Well, I guess a little different from general
2:10
purpose is on each replica. It's not just your Tim TV, but also your data and log files are
2:18
running on locally attached directly attached SSDs and similar to general purpose your backups are going to be
2:24
stored in geo replicated storage, which again is going to help you in the event of some catastrophic failure where a whole region goes down
2:34
And if a failover needs to occur, we're just going to simply repoint you to one of the secondaries and you're basically up and running and in the back end will spin up another secondary and you don't have to change your connection strings again
2:49
You just want to make sure that you have a retry logic built in
2:55
Now again, this is in my opinion, part of the power of paths
3:00
This level of availability that you get without doing anything. No configuration you just get an SLA and pretty high availability and so I wanted to show off you know how fast some of these failovers can occur So I going to switch back to my virtual machine
3:19
and just bear with me because I got a lot of screens
3:22
I'm going to pull up right now and then I will explain them
3:32
Again, I'm going to be using Azure Data Studio. I'm a big fan of the PowerShell
3:39
Notebooks, so let's see. If we can get this running. And then I will explain
3:53
Cool. Alright, so what I have on top is Azure Data Studio and on the bottom
4:02
I have two PowerShell Windows open. The left side of the screen
4:08
so just look at the left side of the screen for now. This is a general purpose database
4:13
So remember, this is like a failover cluster index. What I'm going to do is I'm going to leverage O-Stress
4:20
O-Stress is a tool that Bob Ward showed me and now I use it
4:24
all the time just to create loads and also to show off the importance of retry logic
4:29
But basically what I'm doing here, I'll go ahead and kick it off
4:33
Maybe. There we go. What I'm doing here is I'm running this command
4:39
basically connecting to my database server, select count from a table. So just returning something
4:46
And again, this is on my general purpose database, and just do this 5000 times one thread
4:53
Again, this is super easy tool to use. It's very cool. I'm a big fan
4:57
So we're just going to keep seeing those results here for my general purpose database
5:02
And this is also going to allow me to see or you to see as well if we're connected
5:08
Similarly, on the right side, everything over here is for a business critical database
5:13
So it's the same query, select count from a table, except this time it's connected to my BC database
5:20
So I'm going to go ahead and kick that off over here. So we see kind of the same thing going back
5:25
It'll just kind of flash upon our screen as they go. Now what we're going to do is we're going to force a failover
5:32
This is something you can actually do with PowerShell. It is limited. You can only do one failover every 30 minutes
5:38
but you are able to force a failover. So what we're going to do is we're going to force a failover on our business critical
5:45
force a failover on our general purpose and see how long they take. So I'm going to try to do it the same time
5:51
So immediately what you saw on really both, but more on the right side first
5:56
is that this business critical before I can even explain it, this business critical database become unavailable for us to connect to
6:04
But because we're just doing a redirection to a secondary replica, which already exists
6:09
this failover took place in like just a few seconds and it's always very exciting to me
6:15
I get pretty excited about it, but it very cool how fast it was able to fail me over Even the general purpose one was able to fail over less than 25 seconds
6:28
But what we saw is the reason it takes a little bit longer is because behind the scenes we have a failover cluster instance
6:36
So we're looking for a node with spare capacity and we're failing us over there, moving the data over there, whereas here in business critical, we just kind of have it already ready to go
6:45
Now both scenarios are pretty powerful because again I did nothing to set this up
6:51
right? It's just what I get out of the. Just from deploying I get this capability
6:56
and this power of past so pretty powerful capabilities I think. Hope this was interesting again
7:02
I'm going to share all these scripts and notebooks so you can try it for yourself
7:07
Alright, hey Anna, sorry to interrupt you. We are having only five minutes left
7:13
to complete the session. OK, I just let you know that. Thanks. Yeah
7:22
Awesome. So we have about five minutes left. I'm just going to wrap up what the final tier I
7:31
wanted to share with you is the hyperscale service tier. Now this is only available in Azure SQL database today
7:39
and basically I'm not going to get into it today. we have lots more sessions and content that I'll point you to at the end of this that really go through how this works
7:47
But it's a really amazing technology where we basically re architected SQL Server for the cloud
7:55
so we're able to scale out and similar to business critical, you can get more replicas more read replicas
8:01
but actually in business critical you just get one, whereas in hyper scale you can get up to four so you can have zero
8:09
in which case your failover will be like general purpose because you don't have a secondary ready
8:14
But if you deploy one or more replicas, secondary replicas, then you're gonna have
8:20
the capability to fail over much faster. And a big part of this is that we took the log service out
8:28
Again, I won't get into these details, but we took the log service out
8:31
and that's going to enable a lot of this to run very quickly
8:36
And we were able to eliminate some of the size of data operations that are tied to having
8:41
you know, 60 terabyte size database. So in summary, we talked about all these different service tiers
8:50
and this is just a slide that kind of compares how they perform
8:54
Now, when you see IO per second there, that's what IOPS is
8:59
The plus and the plus plus just symbolizes which one is more IOPS or IO per second
9:06
And a few other things that kind of differentiates these like you get one
9:10
readable secondary as part of your available always on availability group versus you can get up to four readable
9:16
secondaries in hyperscale. And the one thing we didn't talk about yet is serverless
9:21
This is only available in the general purpose tier of Azure SQL database
9:26
and this is really meant for workloads with intermittent or unpredictable usage So for example For example if I have a line of business application like at a bank and people only use that application during the day
9:41
what it's going to do is it's going to scale on a per second basis for the exact CPU and memory
9:47
the max of that that I need. And then we're going to use an interesting cash reclamation process to kind of bring us back down
9:54
So you'll only build for what you use. And after a set of time where I'm inactive
10:01
so I can say after an hour, actually pause the database. And during this time, I don't pay for compute
10:08
We've separated it, SQL Server shut down, I only pay for storage
10:12
So this is another really interesting use case that we're seeing a lot of interest from customers
10:17
So I definitely recommend looking more into this and we'll share some resources
10:22
The last choice is hardware. I won't spend a lot of time here due to time
10:27
but basically Gen 5 is your main option today, but you should know that in the future and as we go forward in time
10:34
we're always going to be coming out with more hardware. This isn't to make your life difficult
10:38
This is just to keep you on the latest and greatest hardware with more capabilities as they come out
10:44
Now these are all the options that we talked about today. I'm going to share this deck with you all so you can kind of digest this
10:53
I know it's a lot that you've seen today, but hopefully you're feeling like you got a better view of what's possible with Azure SQL and how all these choices are really there to make your life easier, not more difficult
11:08
I know we're about at time, but one thing you could do if you just go to the Azure portal
11:16
We now have an Azure SQL blade where you can see all your Azure SQL
11:21
whether they're a database or a virtual machine or a managed instance
11:26
they're all in one place. And we're even giving you a what to use when within the portal experience
11:33
Of course, this is taking a moment to load today. But basically, this is going to give you the same view that we've kind of been
11:40
talking about the whole session today. So I definitely recommend checking this out
11:46
Of course, portals not behaving with me today. And with that, I will put up my resources
11:55
I definitely recommend checking all these resources out. If you didn't get a chance to see them after this
12:02
you can always find me on Twitter and I can send you any links that you might need
12:06
And again, please donate. This is for a great cause. please donate in support of UNICEF
12:13
especially during these very challenging times with COVID and everything going on
12:18
And give us feedback. Let us know what you thought of the session
12:21
Again, we apologize for the technical difficulties and also give us feedback on the event
12:27
so we can make these sorts of events better as we go forward
12:31
And with that, I will stop and pass it back. Thank you all so much for your time
12:38
And I hope this was useful. Thank you