Azure SQL Overview

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:01
>> Hello Cybrarians.
00:01
Welcome to Lesson 3.1 of Module 3 of this course
00:01
titled AZ-301: Microsoft Azure Architect Design.
00:01
Here are the learning objectives for this video.
00:01
We'll start out by covering
00:01
SQL options in Azure so that we're familiar with
00:01
the range of options that are available from
00:01
migrating or implementing SQL workloads in Azure.
00:01
We'll then cover some considerations that will help
00:01
us to choose the right option for our use case.
00:01
We'll go over Azure SQL Deployment Models
00:01
of single database and Elastic Pool.
00:01
We'll cover the logical construct referred to
00:01
as Azure SQL Server and what it does.
00:01
We'll cover Azure SQL
00:01
Purchasing Models and Service Tiers,
00:01
which determines what we're paying
00:01
for and how we pay for the service.
00:01
Finally, we make a mention of
00:01
other Azure relational database options.
00:01
Let's get into this. Let's talk
00:01
about SQL options in Azure.
00:01
If you're looking to deploy SQL in Azure,
00:01
there are two main categories that are available to us.
00:01
The first category is Infrastructure as a Service.
00:01
Second category is Platform as a Service.
00:01
Now, under the Infrastructure as a Service category,
00:01
that's something that many of us are already used to.
00:01
It's SQL installed on a virtual machine.
00:01
That means we have operating system-level access
00:01
>> and we have full application control.
00:01
>> The good thing in Azure is that we do not need to do
00:01
the deployment from scratch by ourselves.
00:01
There are templates that are available in
00:01
Azure or images that are available in Azure,
00:01
that have SQL preinstalled on them.
00:01
We could simply deploy those images
00:01
and we'll end up with a virtual machine
00:01
that has SQL installed.
00:01
Then under the Platform as a Service category,
00:01
we have two options.
00:01
We have the Azure SQL Database option.
00:01
Azure SQL Database it shares
00:01
a common code base with
00:01
the latest stable version of SQL Server.
00:01
Actually, most of the standard SQL language,
00:01
the query processing database management features are
00:01
identical in Azure SQL Database.
00:01
Now I want you to notice I said most,
00:01
I didn't say all.
00:01
Not all of the features are available,
00:01
but most of them are available.
00:01
But with that loss of features,
00:01
we gain certain capabilities also.
00:01
For example, we do not have to
00:01
manage the operating system,
00:01
we don't have to manage the application itself,
00:01
the SQL application in terms
00:01
of patching and actually a lot of
00:01
the other operational components like
00:01
backup are also taken care of to an extent.
00:01
The other option that we have is something called
00:01
Azure SQL Managed Instances,
00:01
which is something that actually very similar to
00:01
the functionalities or capabilities of SQL
00:01
on a virtual machine or SQL on a physical machine.
00:01
The only exception, the only difference
00:01
is we have those Platform as a Service benefit.
00:01
It's sort of like the best of both worlds.
00:01
We get the benefit of Platform as
00:01
a Service in terms of managed service,
00:01
Database as a Service but we also
00:01
get the benefits that in terms of feature set,
00:01
it's very close to SQL Server on a virtual machine.
00:01
Now, when it comes to choosing
00:01
the right option for you or for your application,
00:01
it really depends on what you're looking to do.
00:01
For example, SQL on a Virtual Machine,
00:01
you have full control of
00:01
the SQL Server engine. That is great.
00:01
If you're deploying an application that
00:01
way you need full control of SQL Server engine,
00:01
you probably want to go for
00:01
that option because you do not have
00:01
full control in the other scenarios.
00:01
You have different degrees of control,
00:01
but not full control.
00:01
When it comes to SQL Managed Instance,
00:01
if you're going to be involved in
00:01
a lift-and-shift scenario,
00:01
you're going to be lifting a SQL Server
00:01
that existed on-premises on
00:01
a virtual machine and you're looking to transition
00:01
into that Platform as a Service, database
00:01
as a service model to gain some of those benefits
00:01
of built-in backups and built-in patching and recovery.
00:01
Azure SQL Managed Instance is a good one for you.
00:01
Because the feature set,
00:01
as I mentioned, is very,
00:01
very close to normal SQL Server
00:01
that's under your control.
00:01
It's also running the latest stable
00:01
database engine of SQL.
00:01
It got private IP address within
00:01
an Azure virtual network so it's great,
00:01
it's the best of both worlds.
00:01
One of the things I really want to call out
00:01
is that there are certain features that
00:01
are available in Azure SQL Managed Instance that are
00:01
not available in Azure SQL Database.
00:01
For example, support for
00:01
Common Language Runtime CLR and change data capture,
00:01
those capabilities are not available in Azure SQL.
00:01
But if you need those capabilities,
00:01
they are available in Azure SQL Database
00:01
but it's a Platform as a Service.
00:01
When it comes to Azure SQL Database,
00:01
the most commonly used SQL Server features
00:01
are available on it.
00:01
It's got those Platform as
00:01
a Service benefits like built-in backups,
00:01
patching, and recovery,
00:01
it's running the latest stable version of SQL.
00:01
It's got all those other advantages
00:01
that's already built into that.
00:01
In terms of limitations.
00:01
We also have to pay attention to this.
00:01
When it comes to SQL Server on a virtual machine,
00:01
what you're seeing is you're going
00:01
to be taking on the operation or
00:01
workload of managing your own backup and your patches,
00:01
you're going to be taking on
00:01
that responsibility of implementing
00:01
AI availability by yourself.
00:01
You have to manage all those downtime and failover
00:01
with whatever technology or
00:01
whatever process that you want to use to manage that,
00:01
but that's on you to manage that.
00:01
When it comes to Azure SQL Managed Instance,
00:01
even though it's very close to the feature sets
00:01
that you have in SQL Server on
00:01
a virtual machine or a physical machine,
00:01
there are still some features that are not
00:01
available so be aware of that.
00:01
Also, compatibility with SQL Server version
00:01
can be achieved only by using
00:01
database compatibility level so you have to
00:01
update that using T-SQL
00:01
essentially or you have to manage that.
00:01
With regards to Azure SQL Database,
00:01
migration from SQL Server
00:01
might be very difficult so there are
00:01
certain functionalities or features that
00:01
if you're using them on your SQL Server
00:01
on-premises at the moment that may be difficult to
00:01
move over to Azure SQL if
00:01
those features are not available.
00:01
For example, I mentioned CLR earlier,
00:01
Common Language Runtime,
00:01
that's not supported with Azure SQL Database.
00:01
No guaranteed exact maintenance time
00:01
so it's nearly transparent.
00:01
Private IP addresses cannot be assigned.
00:01
You can lock it down in terms of firewall rules
00:01
and making it available to only your virtual network,
00:01
but it doesn't have that private feel to its weight.
00:01
It's living within a virtual network
00:01
that's isolated from everything else.
00:01
Let's talk about Azure SQL Deployment Models.
00:01
Now, there are two main deployment models
00:01
for Azure SQL Database.
00:01
There's the single instance deployment model
00:01
and what this means
00:01
is it's a database with
00:01
its own set of isolated resources.
00:01
In other words, the compute resource,
00:01
the storage resource,
00:01
it's isolated to this instance.
00:01
Then we have the Elastic Pool Deployment Model,
00:01
where a group of databases
00:01
share the same set of resources.
00:01
In this case, I can see that there's like
00:01
four virtual cores and the amount of RAM and that
00:01
all pooled together and shared
00:01
by this pool of databases so
00:01
that if one of them is not consuming that resources,
00:01
the others can take advantage and use that.
00:01
When we go to
00:01
the Microsoft Azure platform
00:01
to deploy an Azure SQL Database,
00:01
one of the things that we'll need to create is
00:01
something called a logical server.
00:01
Now I want to take some time to
00:01
explain this because there
00:01
can be a bit of confusion here.
00:01
When we talk about Logical Server in Azure,
00:01
I want you to think about
00:01
it as an administrative container.
00:01
Do not think about
00:01
Azure SQL Logical Server
00:01
the same way you think of a server with resources,
00:01
with compute, and storage, and memory.
00:01
That's not what we're referring to here.
00:01
It's just a logical construct for administration.
00:01
This is not where you specify or
00:01
defined compute and any of those other stuff.
00:01
This is not the place that you do that.
00:01
Now after you have your logical server,
00:01
which is just administrative container,
00:01
which is where your namespace
00:01
comes from, that network connectivity,
00:01
that's where that comes from,
00:01
then you can deploy your SQL Databases or your
00:01
Elastic Pools within
00:01
that logical administrative construct.
00:01
Now you can mix and match,
00:01
you can deploy SQL Databases, Elastic Pools.
00:01
You can actually deploy
00:01
SQL Data Warehouses within an Azure SQL Logical Server,
00:01
and it contains all of that.
00:01
Some more information about the Azure SQL Logical Server.
00:01
It's the parent resource for databases.
00:01
It acts as a central administrative point for
00:01
multiple single or pooled databases.
00:01
So that's where you configure your logins,
00:01
your firewall rules,
00:01
your threat protection policies, your failover groups.
00:01
You configure these on that logical server.
00:01
That's also where the namespace comes from.
00:01
So you address the namespaces
00:01
that comes from the logical server,
00:01
and then you specify
00:01
your individual database or
00:01
your Elastic Pool that's residing in that.
00:01
Let's talk about Purchasing
00:01
Models and Service Tiers also.
00:01
When it comes to purchasing Azure SQL,
00:01
there're different options that we have.
00:01
There's the DTU option,
00:01
which is like the legacy option.
00:01
DTU is referred to as a Database Transaction Unit sort
00:01
of like this blend of CPU and memory and storage IO.
00:01
This is not the model that you typically use;
00:01
going forward, you see one of the reasons for that.
00:01
The newer purchasing model that we have is
00:01
something called the vCore purchasing model.
00:01
This is something that's very much
00:01
similar to what we are familiar with,
00:01
so you're defining virtual cores.
00:01
The main differences between DTUs and
00:01
vCores is that with DTU,
00:01
you scale compute, which is your
00:01
high-end memory and storage together,
00:01
which is not something that's always great.
00:01
The reason why that's not great is, for example,
00:01
let's say you're running out of
00:01
compute resources and you need to have them or compute.
00:01
What that means is that you're going to incur
00:01
extra storage cost whether you need it or not.
00:01
But with vCore, you have
00:01
more control as to how you scale,
00:01
you have more flexibility,
00:01
you can decide to scale
00:01
compute different from scaling storage.
00:01
When it comes to these deployment models,
00:01
they have different service tiers and
00:01
the service tiers essentially defines
00:01
the amount of resources or
00:01
the specification of the resources
00:01
that will be available to you.
00:01
For DTU, it has three service tier: the basic,
00:01
the standard, and the premium.
00:01
For vCores it has three service tiers,
00:01
the general purpose, the business critical,
00:01
and the hyperscale and this provides varying levels
00:01
of resources to databases.
00:01
One other thing that I want to highlight is that
00:01
when it comes to virtual core model,
00:01
it supports something called Hybrid Benefit.
00:01
Hybrid Benefit is where you can take
00:01
your existing SQL Server licenses
00:01
and then you can get a discount for them in Azure.
00:01
So you can reuse your existing licenses
00:01
because at the end of the day it shows virtual core,
00:01
which is what SQL licensees are based on anyway.
00:01
Then also we have this model called serverless,
00:01
which is currently in preview,
00:01
which is billed per second on virtual cores used.
00:01
Think about you're going to be paying per second.
00:01
If you're not using the database,
00:01
if it's not in use it can pause the database,
00:01
and then if the request come in,
00:01
it's going to load the database
00:01
and then you're going to carry on being billed.
00:01
That's currently in preview in
00:01
case you want to test that out.
00:01
Finally, let's talk about,
00:01
let's make mention of
00:01
the other relational database options
00:01
that are available in Azure.
00:01
We have Platform as a Service,
00:01
PostgreSQL, we have Platform as a Service,
00:01
MySQL, and Platform as a Service,
00:01
MariaDB that's available on the Azure platform.
00:01
If you're bent more
00:01
towards using open-source database engines,
00:01
those options are also available for us.
00:01
That's where I'll end this lesson,
00:01
I'll see you in the very next lesson where
00:01
we'll carry on our conversation on
00:01
Azure SQL and we'll talk about
00:01
migrating data into Azure SQL.
Up Next