live_tv
Livestream Starting Soon
00
Hours
:
00
Minutes
:
00
Seconds
Up next in 10
Learn Azure SQL Database through Demos by Anna Hoffman || SQL Server Virtual Conference
Oct 30, 2023
Slides are very informative and useful, but sometimes you just want to see how it works! In this fast-paced, demo-heavy session, you'll first see how to deploy and configure Azure SQL Database. You'll then see some examples of the capabilities and tasks related to security (including Private Link), performance (including Automatic Tuning), availability (including auto-failover groups), and more. Even better, you'll walk away with all the materials you need to run through the demos yourself!
About Speaker
Anna is a Data & Applied Scientist on the SQL Engineering team. Coming recently from AI Engineering, she's pursuing her efforts to democratize AI through data applications.
Conference Website: https://www.2020twenty.net/sql-server-virtual-conference/
C# Corner - Community of Software and Data Developers
https://www.c-sharpcorner.com
#noslide #SQL #azure #demo #conference #sqlserver
Show More Show Less View Video Transcript
0:00
Hi, everyone. I'm really excited to be here
0:05
I have a lot to get through today and I have zero slides
0:09
This is the closest thing to a slide you're going to see. I thought I'd try something new
0:14
For better or for worse, I've never done this before. What we're going to do is we're going to do
0:18
a session all about Azure SQL Database with no slides. We're going to walk through deploy and configure
0:25
and then we're going to go through security, performance, and availability. I'm going to give you all the resources
0:30
mainly these two resources at the bottom so that you can actually go do every single one of the demos I'm doing
0:36
today in your own environment for free using Microsoft Learn. If you look here, what you're seeing is Microsoft Learn
0:44
This is a learning path that Bob Ward and I created last year. We actually just updated it so you can be
0:49
sure we're always keeping it up to date. But this is basically Azure SQL fundamentals
0:54
We're going to go through a few, actually a lot of demos
0:58
as many demos as I can fit in the time that we have today on these topics
1:02
and then you'll have the resources to go do them all yourself
1:06
That being said, we're going to get started right away. Again, this is going to be a fast-paced session
1:12
I hope you'll enjoy it. We'll take questions as they come in if we can
1:16
or if not, I'll stick around and be sure to answer them on the chat. The first thing is this is the Azure portal
1:23
I think you've been around today, so you've seen this before. What I want to call your attention to is this Azure SQL blade
1:30
Now, if I click Azure SQL, really cool, I'm going to be able to see all my Azure SQL resources in one place
1:37
Whether they're databases, got a lot of databases, logical servers, virtual machines
1:43
elastic pools, manage instance, all in one place. That's awesome. Now, what we're going to do today is we're going to focus on SQL database
1:50
You can see I went to the plus sign and I can have some options around what I'm going to create
1:56
We're just going to do a single database now, but know that you can explore these other options later
2:01
When I hit create, I'm going to be brought to a screen that maybe you all have
2:05
seen before when you go deploy any resources in Azure. It's going to ask for my subscription
2:09
a resource group, a database name. I'm going to call it AdventureWorks
2:13
and I'm going to create a new logical server. New logical server, we're going to call it
2:20
AWServer0129 to go with the date. We'll actually use one I created yesterday
2:28
but for deployment purposes, let's deploy new one. I'm typing live and you guys know how that might go well or not
2:36
Nope, didn't go well. Let's try again. Basically, I'm picking the server admin login
2:44
This is basically like the sysadmin for my Azure SQL database logical server
2:48
Different from a SQL Server, a logical server just manages a lot of the metadata and
2:53
some of the security things around your Azure SQL databases associated with a logical server
2:59
You need something to log into. I have a username, picture region, going to click okay
3:04
It's just going to validate that, and then this should be okay
3:08
There is no database on this server yet. I think that will be fine
3:12
Now next, I'm going to choose not to use an elastic pool, but an elastic pool is a way to cost optimize and
3:19
resource share if you have lots of databases. That is an option here if you want it
3:24
But we're going to dive into compute and storage. Now here, there are a lot of options
3:29
Try not to get overwhelmed. We're going to walk through just a few of them today. The first is general purpose
3:34
If you're familiar with SQL Server, which I'm guessing at this conference you are
3:38
this is behind the scenes going to deploy what's very similar to a failover cluster instance
3:44
We're going to see in the availability section that I have today
3:48
what that looks like and how you can almost tell that when you do something like failover
3:54
The other service here I want to call your attention to is the business critical service tier
3:58
Now behind the scenes, if you want to relate it to SQL Server, when you deploy this tier
4:02
we're basically going to deploy an always-on availability group for you, meaning you have three secondary replicas at any given time
4:09
and one of them we're even going to let you read from. Now, Hyperscale is our newest service tier
4:15
but even still, it's not that new. One of the great things about Hyperscale is that you can scale up to
4:22
theoretically, as big as you need to. We support up to 100 terabytes
4:27
but in reality, that's just a number. One of the great things about this is you can deploy
4:34
secondary replicas as you need them, up to four of them, and you can use all of them for readable workloads
4:40
That's really interesting. It's also going to help with failover times, but we'll get to that in the availability section
4:45
So let's take a closer look at general purpose. Now, once you've chosen general purpose, you have a few options in here
4:54
The first is on compute tier. You can either pick provisioned compute, which is the standard for all these other service tiers
5:01
And what provisioned compute means is basically you're saying, OK, I want two virtual cores or two V cores at any given moment
5:09
Whether or not I use them, I'm reserving them, so they're mine. Now, the serverless model is a little different because you're saying, hey, I want, let's say, a min of, let's say we want a max of eight V cores and a min of one V cores
5:24
And why this is interesting is because serverless is going to take care of scaling you on a per second basis between these min and max based on what you actually need
5:35
And we're going to bill you based on what you actually use. So this is a really great way to cost optimize as well
5:42
And especially for like dev test workloads or workloads that aren't running all the time
5:46
this can be really beneficial. Another reason for that is because you can set an auto pause delay
5:52
So in this case, you could set it to one hour. So if people aren't using this on the weekends or at night after one hour of inactivity
6:00
this is actually going to shut down. We're going to separate the compute from the storage and we'll only bill you for the storage
6:06
So this can be a way to get great cost savings with serverless
6:11
So we'll reference serverless in the performance section as well. But for this, we're just going to use provision
6:17
All right. So hopefully people are following so far. I'm going to click apply and I'm going to go to the next blade of deployment
6:24
And this is where you're going to select your networking options. Now, the default is no access
6:30
In my experience, unless you're really about to go build something really big and you really know what you're doing
6:38
then you can use the no access, but it just means you're going to have to configure it later because what good is a database with no access
6:46
Now, the option that I recommend if you're trying to get started and just make things easy as far as connectivity goes is to use the public endpoint
6:54
Now, with the public endpoint, you have two additional options. you can opt to allow Azure services and resources access to this server
7:02
This is going to basically allow other things in Azure like Azure Data Factory or Azure Functions to
7:08
easily connect or already be accepted to be able to connect to your Azure SQL database server
7:14
Then the current client IP address is basically going to add a firewall rule for wherever I'm deploying from
7:21
which is also going to make things easier for me in the future However if you want the most secure way of connecting then you go towards private endpoint I want to show you just one example for why I say that
7:35
Let's take a look at this PowerShell window. What I'm using here is NSLookup
7:45
This is just a way to look at the publicly available DNS hierarchy
7:49
for different servers or websites. I'm pinging a server that is using the public endpoint
7:56
I'm pinging the server, you're always going to get this IP address back from an Azure service
8:02
But what I'm also seeing is some information about where this database server is located
8:06
I see control ring for East US 2, so that's the region it's in
8:10
I'm seeing another public IP address. I'm seeing information about the data slice
8:16
Some information that I don't necessarily want to be publicly available. Now, what we can do is if we enable private link
8:25
which we will, but I enabled it on one yesterday, the result of that is going to be very different
8:31
What we're seeing now is, of course, we're still seeing this Azure IP address. You're always going to see that for any Azure service
8:38
But under this other section, what you're seeing is this alias which you actually can't connect to
8:43
a private IP address which isn't going to be useful unless you have
8:47
access to the virtual network that this is in, which is unlikely from a public standpoint
8:52
and then just the database again. This is a much more secure looking DNS hierarchy
8:58
which reveals much less sensitive information. That's one reason you might pick to go towards private endpoint
9:05
Now, if you want to set this up, it's really actually not that hard as long as you
9:09
have all the connectivity things in place, assuming you have that. I have other sessions on that if you want to learn about that
9:15
But basically, I'm going to call this AWServers0129-1. PE for private endpoint
9:22
Now you can see this is for a SQL Server resource, but we have private endpoint available for lots of other Azure services
9:29
This is not something that's unique to SQL. It's just great for SQL as well
9:35
I'm going to deploy this into a virtual network, actually the same virtual network that my virtual machine is in
9:41
so that I can easily connect to it. You can also integrate it with a private DNS zone
9:46
hit okay, and you're good to go. Really easy to set up private endpoint as long as you have your connectivity things in place
9:55
Now in additional settings, I have the ability to select a data source
9:59
So I can either choose to not just start with a blank database
10:04
I can pick an existing backup that I have from any server that I have access to
10:10
And then I can also choose a sample, which is the AdventureWorks sample by default
10:14
And this is what I run all my samples on. Everything in Microsoft Learn is around AdventureWorks
10:19
so this is going to be great for all the demos. You can see the database collation rule and how it's
10:23
set because it's already set for this database. Finally, you can configure Azure Defender for SQL
10:29
That's something we're going to take a look at in the security part. Remember, we're going to talk about deploying configure
10:34
then security, performance, and availability. We got a lot to do and we've done a lot already
10:40
Now, I can go ahead and click Review and Create, and I can go ahead and start to deploy this
10:46
Now, we'll see this deploying and it'll take a little while to deploy
10:50
but I wanted to show you one I've already deployed. This is my resource group of things for today
10:55
You can see yesterday on January 28th, I had deployed basically the same thing with the same private endpoint and the same database
11:02
You can see some of those things there. I also configured auditing, but we'll get to that in a bit
11:07
Let's talk about connecting to this thing. I'm going to switch to SSMS, wonderful tool we all know and love
11:15
I'm going to go ahead and click Connect so you can see what it was like for me to connect to this
11:19
You can see I just put the full logical server name, and I can choose to connect to Azure Active Directory
11:26
if I've enabled an Azure Active Directory admin on the server and created those types of users
11:33
When I first deployed, I would just connect using my Cloud Admin account
11:38
Since I've already connected, I'm going to close out of that part
11:42
but I am going to show you how to create an Azure Active Directory admin
11:46
Back in my resource group, I can select the AWServer0128. You see a lot of information here
11:54
but down on the left side, there's an option for Active Directory admin
11:58
Here's where you can see I've set myself as the admin. I'm not going to go into this because it shows a bunch of
12:03
people's PII or personally identifiable information, but you can see that I've set myself as the admin
12:10
one thing to note here is you can only have one. So that's something to keep in mind. It could be
12:15
a group, but you can only have one kind of account listed here. I also just wanted to touch on the
12:21
backups that your backups are taken care of automatically for you just by deploying. I can
12:26
do point in time restore up to seven days ago, and I can optionally configure long-term retention
12:32
whether it's weekly, monthly, or yearly, and see information about the pricing tier. So it's all
12:37
here. All right. So we talked about a lot of stuff so far. Hopefully it's going okay. Let me see if
12:44
I can see any comments. Let's see. Yes, this session is going to be a lot of learning. Simon's right
12:54
All right. We're going to keep going. So we talked about deploying configured. Now I want to get into
12:59
security. So from this logical server, how do you get to the database? You can click SQL databases
13:04
and then we can navigate to this database. Again, this is the same database
13:08
that we basically just deployed. Now we're talking about security now. So I'm going to go down to the left
13:15
and you're going to see this security section of items. The first one is auditing
13:20
And what I've done is you can enable it on the database level
13:24
or you can enable it on the server level. So I enabled it on the server level
13:29
to point to a storage account. So that's where my XEL files are going to go
13:33
or I can connect it to a log ytics account, which I've also set up so I can use Kusto query language
13:40
or KQL to do some querying of auditing and the audit logs
13:45
and see kind of who's accessed what. This is a really rich tool. I definitely recommend spending some time and learning about it
13:51
and I'll share a resource for that. Okay, so since I have auditing set up, I'm going to go back
13:59
Let's see, my screen froze. That's unfortunate. Okay, it's okay. We're rolling with it
14:07
Maybe. Give me one moment. Hmm. Never had that happen before
14:21
Let me see. One second, folks. Let me just get that up
14:29
You know, you always see the new things that happen to your demos
14:36
They only happen live. Like, I don't think I've had a virtual machine freeze on me in a very long time
14:42
But let's see if we can get it back up, and then I will share that out with you all
14:50
All right. Just my session expired while I was using it That what happened Okay let me share that out again Sorry about that folks Hopefully you can see my screen Awesome
15:06
Back to figuring this out. We're back in our database, looking at the security section
15:13
I showed you that we set up auditing at the server level
15:17
Now, the next option we have is data discovery and classification. Now, this is something available for SQL Server on-prem
15:24
using SSMS but for Azure SQL database, it's built right into the portal
15:28
We even have automatic recommendations, which I've gone ahead and applied. I want to bring your attention to two recommendations
15:36
One was customer first name, and it automatically picked this up as name info and
15:42
confidential GDPR type of sensitivity label. Then we also have the same here for last name
15:49
That's data discovery and classification. Uh, next I might say, you know, those first name, last name, I also want to mask that data. So what
15:57
that means is when someone who doesn't have access to view the data sees that data, they're just going
16:03
to see it as a bunch of X's. And you can see, I have other options here around credit card value
16:08
email value, or I can even do a custom string type of value. We're going to keep it as, um
16:14
the default. And I added those for first name and last name. Um, I also wanted to know before I get
16:20
into an example that TDE, Transparent Data Encryption, is on by default
16:25
with service managed keys for Azure SQL Database. But if you want to do bring your own key
16:31
you can enable that at the server level. Let's take a look at a hopefully cool example
16:38
I'm going to bring my friend Bob Ward. He gets to be a lot of my demos because he really likes the Cowboys
16:45
so I like to use him in another sense where he likes the Titans
16:49
which is who I'm a fan of. Let's take a look at this table
16:53
I have this table, you can see their first and last names. The reason I can see it is because I'm connected using an admin account
17:00
You all noticed I selected myself as the Azure AD admin, and I'm connecting using Azure AD, so I can see this
17:07
What I want to show you is how we can create a new user. We're going to create a user called Bob with the password called GoTitans
17:15
Now, this is a little different from what you may have seen before, because I'm actually going to do it in the context of the database
17:21
I'm just going to create him right here. Now, similarly, if I wanted to use Azure AD authentication to AdBob
17:27
I could do the same thing except the new verbiage that you don't have on-prem
17:31
I believe you don't, is from external provider. That's how you're going to add those new Azure AD accounts
17:39
I'm going to go back here. I'm going to create a user. This is just, oh, he already exists, so I'm not going to create him again
17:45
And then what I'm going to do is make sure he has access to read and write data
17:50
Before you do this, they don't have access to that. It's just like SQL here
17:55
Now, Bob doesn't have that many privileges, so he shouldn't be able to see this data that we've added dynamic data masking to
18:02
And sure enough, you can see first name, last name are X'd out. And perhaps maybe you would consider saying
18:07
you should probably track middle name as well, but let's just leave it like this
18:11
Now, if for whatever reason I wanted him to be able to see this data, I could use the words grant unmasked
18:19
So that's basically going to allow him to see this data and you can see that here
18:24
Now he can see it. I'm using these execute as to execute as Bob
18:28
Finally, if we decide that Bob no longer needs access to this or we don't want him to access people's names
18:35
I can simply use the revoke unmasked. So you have grant unmask and revoke unmask in order to do that
18:43
So that's an interesting exercise. Now, since we used the storage account
18:47
we can actually merge the audit files and take a look at what we just did
18:52
So I'm going to, instead of getting the log files from local, like you would with SQL Server
18:57
I'm going to get them from that blob storage account that I connected them to
19:01
So let's see. So once it loads, I should be able to see my subscriptions
19:05
and select that storage account where the SQL DB audit logs are stored
19:12
Select the server, select the database. We'll just grab stuff from today and we'll take a look
19:23
Okay, so a lot to sort through here. There's definitely better ways of doing this
19:28
but what I'm going to do is I'm just going to show you an example
19:33
So I found one of these queries from Bob. You can see the query here
19:39
I know it's really small. Executed as user Bob. So this is when he doesn't have access and it's masked
19:44
Now, what I want to call out here is that because we use data classification to classify
19:51
first and last name as sensitive data, we're actually going to see that here in the bottom
19:56
under data sensitivity information. We're going to be able to see basically everything that we put in
20:05
So it's a medium sensitivity level. We labeled it as confidential GDPR, and it's also labeled as a name, which you would see
20:14
somewhere in here. I think I'm missing it. But yeah, so this is a great way to not only audit your data, not only classify your data
20:23
not only mask your sensitive data, but kind of have it all together so you can get this
20:28
full picture of who's accessing sensitive data and who can see sensitive data if they are accessing it
20:35
That's a fun example. Related to security, I want to show you another thing
20:41
This is a fun thing. Bob and some security folks taught me how to do this
20:46
What we're going to do is we're going to see how well Azure Defender does
20:51
Now Azure Defender is a capability for SQL that includes something called advanced threat protection
20:58
What advanced threat protection does is it tries to monitor against any potential attacks that
21:03
happen and notify you very quickly. What I'm going to do is I'm just going to invoke
21:09
this by doing a new database engine query. I'm not going to go through all the details of this one
21:15
because I don't think it's worth the time. Like I said, I have all of the
21:23
I can't type and talk if it works. I have all of the code in order to do this on your own in the Microsoft Learn Learning Path
21:34
I'm going to connect like that. I'm going to run this query
21:39
Hopefully, that should trigger a SQL injection attack. What I'm going to do is..
21:46
Let me just close that. Okay. Okay. Sorry. I'm going to close this one
21:53
Let's go back to the portal and let's see where we might see this
21:58
I'm going to refresh this page. On the left-hand side, again, I'm in the same place under security
22:08
I'm going to go click Security Center. Now, what we're going to see is that we do have
22:14
this potential SQL injection. Now, it's registering a count of two because I tried one this morning to make sure it worked
22:20
but we can click into this. This is part of Azure Defender for SQL We can see 10 so this is on not my time zone but I believe this is on GMT time zone There was indeed an attempt at this And you can even click into it and see the status
22:38
So you can see that it's active. I can also choose to dismiss it. I can see what query was considered the vulnerable statement
22:45
Why we think it was the cause. Investigation tips to learn more. I can take action and learn more about this
22:53
I can even trigger a logic app in the future to kind of respond to these sort of situations
22:59
So there's a lot of control I have here. And I don't know if you guys notice how fast those notifications came up
23:06
It's really almost immediate. And you can set up even like text alerts or email alerts for this as well
23:12
So that's potential SQL injection part of advanced threat or sorry, advanced threat protection, which is part of Azure Defender for SQL
23:20
Now, there's one other piece of Azure Defender for SQL, and that's vulnerability assessment
23:27
What this does, I'm going to click into this. What this does is it's basically going to scan your database once a week
23:35
If you have this configured, it's going to scan once a week for a number of tests based
23:39
on various benchmarks that we have available. You can see this most recent test was ran not too long ago, but if I look at the past
23:49
excuse me if I look at the past tests I can see the different tests that are there and I can go
23:56
into more details about them but what's more interesting to me right now is the tests that
24:00
failed so we can check minimal set of principles should be members of fixed high impact database
24:06
rules so I can say oh what does that mean and sure enough I can get a description and basically what
24:12
this one is saying is saying hey you should set an initial baseline and once you set a baseline
24:17
what we're going to do is every time we scan, we're going to check to see if anything has changed
24:22
And if something's changed, we're going to let you know. So this is just another way that we're
24:27
making your database more secure. And you can see the benchmark reference here. This one is for FedRAM
24:33
You can see the rule that triggered it. You can see remediation. You can see the results
24:38
So lots of other things. I can improve this as a baseline. So really awesome, really highly
24:45
advanced stuff here happening in the Security Center for Azure SQL, and there are a lot more advances that are coming here in
24:52
this space across all of Azure leveraging through the Security Center. Let me see if I have some questions
25:05
What if you have blocks of T-SQL and a procedure that gets uncommented for debugging
25:12
Paul, thanks for your question. I'm not exactly sure what you're talking about
25:17
I'm guessing you're talking related to the advanced threat protection, but if you add some follow-up there
25:23
I'll be sure to check back later. Take a look at time, take a look at our list
25:29
Okay, cool. We're doing okay. Next, we've seen Deploy and Configure, we've seen Security
25:34
Now, we're going to take a look at some performance topics. What I'm going to do is I'm going to switch to SSMS
25:41
And I have some new queries or views that I think you're going to enjoy
25:46
So what I'm going to do first is I'm going to use Ostrass to kick off a workload
25:51
So I'm basically going to kick off this workload. It's running. And now I can explain to you what's happening
25:58
Okay, so on this top view, what you're seeing is a join result of DM exec requests and DM
26:06
exec sessions, which are things you've probably seen before and are familiar with
26:10
Then on the bottom is a new one to Azure SQL database, which is DMDB resource stats
26:17
That's going to show you things like the average CPU percent. Now, this workload is running and what I want you to notice is
26:24
that there are a lot of weights happening with SOS scheduler yield and a lot of
26:29
things that are runnable that aren't necessarily running. It means maybe our performance could be better
26:35
If I run this again, we can even see that our CPU is maxing out
26:41
This database is a two vCore database. Potentially, we might want to do some investigating
26:47
if increasing CPU could help us in this situation. Now we can still see that, again
26:53
a lot of things are runnable. There's one more query here that I had commented out
26:58
and this one is really useful if you have a serverless database
27:02
This where status visible online is going to help you track which of your serverless cores are actively being used
27:11
If you deploy between two to eight cores, it'll tell you potentially during this heavy workload that maybe it's using all of
27:18
those as opposed to just one when it's more slower or less workload
27:24
Let's see if that workload has completed. It usually takes about a minute or two
27:29
Looks like it's still running. Now, once this workload completes, we have a few options to go and investigate it
27:36
One of those options is the query store. Now, this is something you have on-prem
27:40
and this is turned on by default in Azure SQL database. For example, you can go look at the top resource-consuming queries
27:47
you can go look at the query weight statistics and see that most of them are coming related to CPU
27:53
and then you can even see things related to the various queries
27:57
Now, we're not going to dive too deep into that today, but I just wanted to really stress how important that is
28:03
In this situation, we've decided that this is a CPU-related issue. For this workload, we want to scale up
28:11
Scaling up is easy. I'm going to kick it off, and then I'm going to explain to you what these are
28:17
This first result is going to show you some of the limits that you have
28:22
It's going to show you the slow or the service level objective. You can also see some information to that with this service objective database property as well
28:31
So you can see it's a general purpose, gen five, that's the hardware and it's two vCore
28:36
So I can see the CPU limit is two. I can see other limits related to memory, max size, max log rate, max IO
28:44
All that information is here. And then the final thing that I ran was this alter database
28:49
Now, if you want to scale up, you can use the portal, you can use PowerShell, or you
28:54
can use T-SQL. So here we're using T-SQL to basically scale up to a 8vCore database
29:02
And now if I want to check the status of that operation, I can run this
29:11
Select all from SysDM operation status. And I can see all the things that are completed or running
29:19
And it looks like this one right now has completed this one
29:24
It just completed moments ago. What we're going to do now is we're going to go back to our monitoring resources
29:36
We're going to kick off this workload again. Note that last time it took one minute and 43 seconds
29:43
We're going to kick that off. Now it's kicking off. We can run this again
29:47
What we see immediately is that we're seeing a lot more running tasks as opposed to runnable
29:56
Now, there's still a few, so maybe you could make the argument that we could scale up
29:59
more. But what we're really seeing is that a lot of these tasks are able to run. So we can see
30:06
there's less weights, even some of it has completed. We even see our average CPU down here is lower
30:12
than it was before, you know, it didn't immediately skyrocket to 99%. Now, again, we can see by these
30:18
runnables that it's possible, let's see again, that we will hit pretty close to our limits
30:24
But it also looks like this workload has already completed. So let's go check. Sure enough, it took
30:29
about half as long, maybe less than that, to run since we scaled up. So this is just one option you
30:37
have for performance. And remember, with serverless, you can set up a min and a max and auto scale to
30:43
what you need. So you don't have as much delay as you have here. Now, one other performance thing I
30:49
wanted to show you before we move into availability is the performance dashboard. So this is the
30:56
performance dashboard. This is our next section. It tells you a lot of things here
31:01
You can dive into your query performance insight and filter on different things
31:07
You can also take a look at something like recommendation. This is something specific to Azure SQL database
31:14
It's giving you recommendations based on its ysis of your workload on what indexes maybe you should
31:20
create or drop or parameterize. Now, if I click into one of these
31:24
I can see how much disk space it's going to need. I can see the index type
31:28
I can even view a script if I want to implement this myself, or I can just say to apply it
31:34
Now, you also notice this automatically create index in the future. What does that mean? If you go to automatic tuning
31:41
now this is one of the great things you only get in Azure SQL Database, you can actually turn this on
31:46
When it thinks there's a good opportunity, it will wait for a time of low resource usage
31:51
it will apply the create index, And then it will monitor to make sure it actually improved your performance
31:57
And if it didn't, it will drop the index or kind of roll back whatever it did
32:01
But if it did, that's great. So this is a great way to kind of help, especially if you're managing a lot of databases, this
32:06
can help you in just keeping some of them, you know, automatically tuned
32:11
So that's performance. Now, the last one I have, and I think we still have some time, is related to availability
32:21
So let's take a look. And I know I said no slides and I don't have slides, but I have one sample from the high availability by service tier that I wanted to show you
32:31
Now, just to remind you that for general purpose, when we look at the underlying architecture, this is similar to a failover cluster instance
32:39
So what that means here in this case is that on your primary replica, you have your tempDB running on directly attached SSD
32:47
However, your data and log files are stored separately in Azure Premium Storage
32:53
What this means is when a failover needs to occur, what we do is we look for a node with spare capacity
33:00
we fail you over, and then we reattach this primary replica to your data and log files Then we redirect the pointers and get you back up and running You still don have to change connection strings or anything like that but this process could take a few seconds Business critical on the other hand if you recall from the beginning
33:23
we said when you deploy this, you just get an always on availability group behind the scenes
33:27
So you get three secondary replicas, one which you can read from. Now on each replica
33:33
you have, of course, TimpDB, but you also have the data and log files. So one of the things that
33:39
this means is when a failover has to occur, or if you force a failover, we simply just change the
33:44
pointers to one of the secondaries we already have running. So we can fail you over really fast. So
33:49
that's one of the great things about business critical and general purpose is you can fail over
33:53
really fast without a ton of downtime, and you don't really have to change anything as long as
33:58
you have retry logic and resiliency built into your applications. Now, Hyperscale is the last one, and its failover time is kind of a mix of the two
34:10
that we talked about earlier, depending on how many readable replicas you have
34:15
So if you have more than one replica, we're going to be able to fail you over quicker
34:20
whereas if you have no replicas, it's going to take a little bit longer, a little bit more like general purpose
34:25
So I thought a great example, and maybe this is something you've seen from me before, but this is just one I really like
34:32
We're going to do this faster failovers exercise. So we're going to compare the failover time for a business critical database versus a general purpose database
34:42
So as we look here, we have on the right, we have a business critical database
34:48
And on the left, we have a general purpose database. Now, down at the bottom, I want you to notice I'm going to use Ostrus, and I'm going to run
34:59
the same query. They're basically the same AdventureWorks. It's basically the same AdventureWorks database
35:04
It's just two separate databases. One is a general purpose. One is a business critical
35:09
And so we're basically just querying for availability. So for example, on the business critical database, as long as you see 847, it means this database
35:17
is available, right? We're just retrying, just hitting it repeatedly. Same thing on the left, except we're hitting a general purpose database
35:24
You also see 847, but for a general purpose database. Now, what I'm going to do is I'm going to force a failover, which you can do with PowerShell
35:33
I'm going to force a failover on both of these at approximately the same time, and I want
35:39
us to observe how long these failovers take. Oops. Okay, so I'm going to kick it off and hopefully we'll see how it goes
35:49
Okay, so immediately I kicked them both off. Immediately you saw the business critical database become unavailable and the general purpose database followed
35:58
Now, before I could even finish saying that, this business critical database has already gone back to being available
36:04
You can see just how fast this always on availability group behind the scenes
36:09
and architecture that you all understand is helping us make these failovers
36:14
higher and keep this database available more often Now with general purpose it takes a few seconds longer In my experience it still takes less than 30 seconds but it takes a few seconds longer because we are like a failover cluster instance
36:29
looking for a no spare capacity, failing you over and then changing all the pointers there
36:35
That's a really fun one. I'm glad that it went on well
36:39
I see there's a question that says, automatic tuning for standard edition
36:43
In Azure SQL database, we don't have this idea of additions in the way that
36:49
you might be familiar with SQL Server. Now, some things are available in SQL Server 2019
36:56
However, this create and drop index capabilities are only available in Azure SQL database today
37:04
That's just one of the benefits because you've given Azure control of the server and the infrastructure
37:12
we have access to more telemetry so we can do machine learning behind the scenes to actually, you know, figure out what indexes should be
37:19
created and dropped. So hopefully that helps answer that question. How can the index recs be pushed
37:27
back into source control? That's a great question. I'm not exactly sure how you might automate that
37:33
process. I'm not sure how you would, I think you can access those recommendations outside of that
37:40
portal view that we saw, but you know, you might have to build something a little custom to kind of
37:44
Oh, when we get a recommendation, maybe trigger doing that and get it back into our source control
37:52
Okay, so great questions. Now, the final demo that I have, and I know we're just about at time, is about failover groups
38:00
And I'm going to keep this really brief. In the exercises, you'll get to go much deeper
38:09
But basically what I've done is I've set up an auto failover group
38:13
and the primary is in East US and the secondary is in West US. Now your options are geo-replication
38:21
or auto-failover groups. Today we're looking at auto-failover groups, but geo-replication is also
38:26
a thing. Now the great thing about auto-failover groups, let me just kick off this app. Let's see
38:36
Let's just restart this and then hopefully it has started when we come back. Now what we have is
38:42
the difference between failover groups and georeplication is with auto failover groups
38:47
we give you two endpoints, a read-write endpoint and a read-only endpoint. So what this means is
38:54
that when a failover occurs, you don't have to update your connection strings. We're going to
38:59
take care of that for you. So you can failover faster with less downtime and without having to
39:04
kind of re-modify any connection strings. So that's a really great benefit. You just connect
39:11
to the read-write or the read-only and we take care of which one we point you to
39:16
So I want you to notice right now East US is in the primary role. And let's take a look at my application
39:22
So what this application is doing is it's connecting to the read-write endpoint and trying to insert a row
39:29
And then it connecting to the read endpoint and trying to read that row So we can see right now it successful for both Now what I going to do is I going to initiate a failover
39:41
You can see what's going to happen is this DR server is going to move into the primary
39:47
You get this nice little graphic of the data moving. Like I mentioned, I don't have to change these endpoints
39:55
I don't have to update any of this or anything like that
39:58
it's just going to work in my application. Now in my application, we can take a look and what we
40:05
we will see one of two things. Either we'll have a delay in the, the pot, the failing over of the
40:13
we'll have a delay in when we see these results, or we'll see some failures. Now it looks like this
40:19
failover might've, looks like it's still happening. So we should see some failures, but it looks like
40:24
instead, it's just going to freeze. So that's, that's what we have. And now you can see here
40:30
that the DR server is in the primary role. Now, my guess is that the failover of disconnectedness
40:36
happened so quickly that this application was able to basically just pause and retry and we
40:42
didn't even see any failovers. We can fail back and you might see some failover. Sometimes what
40:48
it says. It says insert was failed and then it goes back to being able to connect. So see, you see
40:55
the insert is failing as this failover is occurring because it's taking a few seconds longer
41:00
and then it will, in theory, go back to being able to connect once the failover has completed
41:08
So that's also a really interesting exercise. One you'll also have access to on the Microsoft
41:14
learn page. We'll see it looks like it's still failing over and now it's completed
41:19
So we should be able to see some successes soon. But while that's going through, let's talk about
41:24
what we've learned today. So, or what you've seen today, which has been a lot. So you saw
41:30
Azure SQL database. We talked about deploying it. We talked about security, performance
41:36
and availability through lots of different demos. And I guess this one's just not going to work for
41:42
me today but that's okay. Remember, I want you to remember these resources here
41:47
Just these two resources, this is all you need. Azure SQL Fundamentals is going to get you to
41:53
that Microsoft Learn learning path where you can try every single one of the demos that you just saw today and you can
41:58
also learn and spend some more time soaking them in. Then Azure SQL YouTube, this is our YouTube channel
42:05
I'm just going to do a tiny plug because that's what we do these days
42:10
But this is our Azure SQL YouTube channel. If you want to stay up to date, we go live every Wednesday
42:14
We also release lots of episodes. But if you want more on this topic
42:18
we have Azure SQL for Beginners, which is 61-bit videos from Bob and I
42:23
that goes through the content you just got a glimpse of, and the Azure SQL Bootcamp
42:27
which is four longer videos going through the same content. Depending on how you like to learn
42:32
these resources are available to you