Azure SQL Design Decisions Part 2

Video Activity
Join over 3 million cybersecurity professionals advancing their career
Sign up with
Required fields are marked with an *
or

Already have an account? Sign In »

Time
14 hours 28 minutes
Difficulty
Intermediate
CEU/CPE
15
Video Transcription
00:00
>> Hello Cybrarians. Welcome to lesson 3.5 of Module 3
00:00
of this course titled Microsoft Azure Architect Design.
00:00
We'll continue from where we left off in
00:00
>> the last video.
00:00
>> We'll carry on talking about SQL
00:00
in Azure but from a performance perspective.
00:00
This goes beyond just Azure SQL database.
00:00
We'll actually be covering the different SQL options
00:00
in Azure from a performance perspective.
00:00
Then we'll cover Azure SQL from a security perspective.
00:00
Finally we'll cover Azure SQL from a cost perspective.
00:00
Let's get into this. Let's talk about
00:00
SQL in Azure design decisions
00:00
as it relates to performance.
00:00
In this particular slide,
00:00
I wouldn't just be covering Azure SQL I'll also be
00:00
extending this discussion to
00:00
SQL in a virtual machine in Azure,
00:00
because there are setting
00:00
concentrations that we have to
00:00
have when it comes to performance.
00:00
When it comes to Azure SQL
00:00
and Azure SQL Managed Instance,
00:00
we want to select the right tier for our workload.
00:00
You know how we have the different tiers.
00:00
Depending on the deployment model that you have,
00:00
you have different tiers where
00:00
we have the general purpose,
00:00
you have the business critical,
00:00
and then you have the hyperscale.
00:00
Just ensure that you select
00:00
the right tier for your workload.
00:00
Also it's a good practice to deploy
00:00
your database workload as close
00:00
as possible to the application
00:00
and the client that uses it.
00:00
This is to avoid as much as possible network latency.
00:00
When it comes to SQL Server in Azure Virtual Machines,
00:00
just continue to use
00:00
the same database performance tuning options that are
00:00
applicable to SQL Server in an on-premises environment.
00:00
By the way, this is not just only for
00:00
SQL Server in Azure Virtual Machines,
00:00
T=this also applies to Azure SQL.
00:00
Best practices when it comes to
00:00
performance tuning for SQL still applies.
00:00
Factors such as the size of
00:00
the Virtual Machine that you're deploying, SQL too,
00:00
the configuration of the data disks,
00:00
whether you're using standard disks or premium disks,
00:00
definitely have an impact if you're using
00:00
SQL Server in Azure Virtual Machine.
00:00
One of the things that I want to call out when
00:00
>> it relates
00:00
>> to the data disks of the Virtual Machine that's
00:00
running SQL in Azure is that you
00:00
can configure caching for the disks.
00:00
Now, here's the best practice.
00:00
Read-only caching configured on
00:00
disks that hosting your data.
00:00
When it comes to the disk that hosting
00:00
your logs, enable no caching.
00:00
In other words, there's no caching
00:00
for disk hosting the logs,
00:00
read-only caching for disk hosting the data.
00:00
This would give you the best performance for
00:00
SQL Server in Azure Virtual Machines.
00:00
Now let's talk about security.
00:00
When it comes to security,
00:00
there are multiple layers.
00:00
Defense in data is always a good strategy,
00:00
and that is what is covered on the slide.
00:00
You have security as it relates to identity and access,
00:00
security as it relates to
00:00
data protection, network security,
00:00
monitoring and logging which we talked
00:00
about in the previous video,
00:00
and then other things like
00:00
security management and ensuring
00:00
that you have visibility into what's going on.
00:00
I'll just emphasize some key best practices
00:00
in the upcoming slides.
00:00
As it comes to network security,
00:00
you want to use IP firewall rules
00:00
or vNet service endpoint to restrict network access.
00:00
Azure SQL unlike Azure SQL Managed Instance,
00:00
is applied within the virtual network,
00:00
which means that it can be
00:00
reached directly over the Internet.
00:00
Now, we can use something called under
00:00
the firewall and virtual networks
00:00
configuration of that service.
00:00
We can restrict which IP addresses can
00:00
connect to Azure SQL over the network.
00:00
We can also use something called
00:00
vNet service endpoint to
00:00
ensure that connectivity can only be
00:00
made to the platform SQL from
00:00
services or resources that are
00:00
running within an isolated network.
00:00
IP firewall rules can be configured at
00:00
the server level or at a database level in some cases.
00:00
When it comes to access management,
00:00
you want to use role-based access control
00:00
to restrict management access.
00:00
If you think about it, for example,
00:00
you want to be able to restrict,
00:00
we're able to get to
00:00
the service and make configuration changes.
00:00
Also when it comes to
00:00
the databases that are running within the server,
00:00
databases supports two types of authentication,
00:00
SQL Authentication which is the default,
00:00
and Azure AD Authentication.
00:00
We can actually integrate Azure AD directly with
00:00
a SQL databases so that we
00:00
manage identity from a central point,
00:00
that also has the added advantage of being able to
00:00
use things like multi-factor authentication.
00:00
Database authorization can be
00:00
assigned using Transact-SQL.
00:00
When it comes to data protection,
00:00
Transparent Data Encryption is enabled by default,
00:00
so that means your data is encrypted at rest.
00:00
Now, we can enhance
00:00
the encryption by using our own keys.
00:00
The default encryption that's enabled
00:00
the keys are automatically managed by
00:00
Microsoft or maybe for compliance reasons
00:00
we wants to be the ones managing the key.
00:00
We can go ahead and integrate
00:00
Key Vault that we talked about in previous lessons.
00:00
We can integrate Key Vault with
00:00
Azure SQL and be able to use our own keys.
00:00
As it relates to threat protection,
00:00
this is talking about identifying
00:00
the different attacks or
00:00
different anomalies that may be going on within SQL.
00:00
We can enable a service
00:00
called Advanced Threat Protection,
00:00
which is going to analyze
00:00
a SQL logs and look for anomalies
00:00
or indicators of compromise or indicators of attacks.
00:00
It's going to help us to detect
00:00
unusual behavior and potentially
00:00
harmful attempts to exploit our databases.
00:00
Data protection mentioned this earlier,
00:00
Transparent Data Encryption is enabled by
00:00
default and we can enhance it using our own keys.
00:00
When it comes to costs with Azure SQL design,
00:00
we need to understand what exactly
00:00
are we charged for when we use this service.
00:00
Here's what we're charged for.
00:00
We're charged for the compute and
00:00
that includes the memory that we are using.
00:00
We pay for that when we select
00:00
the different service tier that we want to use.
00:00
We are also charged for the storage which in
00:00
the case of using the vCore purchasing model,
00:00
we have more flexibility in controlling that.
00:00
We're charged for the backup storage.
00:00
If we're going to be doing
00:00
backup so the backup data stored,
00:00
there's going to be a cost to that.
00:00
Then we're also charged for
00:00
long-term backup retention storage.
00:00
That's going to be stored in an Azure storage account,
00:00
and that's going to incur certain costs.
00:00
As we've discussed earlier,
00:00
there two purchasing models,
00:00
the vCore-based and the DTU-based model.
00:00
When it comes to billing options,
00:00
we can either pay-as-you-go.
00:00
In other words, you just pay
00:00
per hour for what you're using,
00:00
or in the case of serverless
00:00
you're even paying per second.
00:00
We can do something called a Reserved Instance.
00:00
Reserved Instance means if you know that this is
00:00
a database workload that's not going anywhere,
00:00
it's a database workload that's going to be
00:00
online for a long period,
00:00
you probably want to pay for it
00:00
or I agree to reserve it upfront,
00:00
and that's going to give you potentially
00:00
up to 28 percent savings if you
00:00
reserve for up to three years.
00:00
Pay-as-you-go, you pay per
00:00
hour for the computes that you're using.
00:00
One year or three years reserved means you pay per
00:00
year with 18 percent or 28 percent savings.
00:00
Now, Microsoft has introduced more flexibility to
00:00
where you can reserve the instances
00:00
but pay monthly, so that's good.
00:00
If you are using the vCore purchasing model,
00:00
we have the option of using hybrid benefit,
00:00
which allows us to be able to use
00:00
our SQL Server licenses in Azure.
00:00
That's going to result in significant savings also.
00:00
If you want to get up to 86 percent cost savings we can
00:00
use a combination of hybrid benefit
00:00
we used in our existing SQL Server licenses,
00:00
which Reserved Instances and that can result
00:00
in significant cost savings.
00:00
That's it for this particular lesson.
00:00
I hope you've enjoyed
00:00
it and I'll see you in the next lesson.
Up Next