Azure SQL Design Decisions Part 1

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.
00:00
Welcome to Lesson 3.4 of Module 3
00:00
of this class titled Microsoft Azure Architect Design.
00:00
Here are the objectives that we'll
00:00
be covering in this lesson.
00:00
We'll start out by covering
00:00
Azure SQL design decisions
00:00
from a scalability perspective.
00:00
Then we'll move on to cover
00:00
design decisions from an availability perspective.
00:00
Finally, we'll cover Azure SQL design decisions
00:00
from a monitoring perspective.
00:00
Let's get right into this.
00:00
Let's talk about scalability
00:00
and some of the information that we
00:00
need to know when designing for
00:00
scalability of Azure SQL Database.
00:00
The compute size can go up to
00:00
80 virtual cores regardless
00:00
of the deployment model that we have.
00:00
Whether that single database elastic pool
00:00
or Managed Instance,
00:00
we can go up to 80 virtual cores.
00:00
Now also the paint on the tiers that we
00:00
select and how we've specified that.
00:00
When it comes to the size of our database,
00:00
for single database and elastic pool database,
00:00
we can go up to four terabytes if we're deploying
00:00
the general purpose or business critical service tiers.
00:00
If we're deploying the IPO scale tier,
00:00
we can go up to a 100 terabytes.
00:00
When it comes to the Managed Instance,
00:00
the maximum size of the database is
00:00
eight terabyte and there has to
00:00
be the general proposed service tier.
00:00
For the Business Critical
00:00
Service Day of Managed Instance,
00:00
we can only go up to four terabytes.
00:00
When it comes to designing for vulnerability in Azure,
00:00
what you're looking at on the screen,
00:00
are the different options or
00:00
the different features that are available for
00:00
Azure SQL that can help us with that design
00:00
for business continuity and disaster recovery.
00:00
For example, let's start with automatic backup.
00:00
Automatic backups are enabled by default.
00:00
As Azure SQL is a managed service,
00:00
this is enabled for us,
00:00
automatically taken and it's
00:00
not something that we can disable.
00:00
This protects against things
00:00
like accidental corruption of deletion,
00:00
where we can do point-in-time restore,
00:00
at database states,
00:00
at the previous point.
00:00
Also the backups that are taking are protected
00:00
in that they're stored on geo-redundant storage,
00:00
which means storage that is spread
00:00
across different regions in Azure.
00:00
The backup happen transparently in
00:00
the background without impacting on performance.
00:00
Full backups are taken every week,
00:00
differential backups happens twice a day
00:00
>> every 12 hours,
00:00
>> transaction log backups occur every 5-10 minutes.
00:00
The default retention period
00:00
for the backups is seven days,
00:00
but we can extend this,
00:00
we can configure this.
00:00
For the general purpose and
00:00
business critical service tier,
00:00
we can extend this up to 35 days retention period,
00:00
so that if there were to be
00:00
an accidental corruption or deletion,
00:00
we can restore back
00:00
>> provided we get to it within 35 days,
00:00
>> otherwise the entire gets deleted.
00:00
We can use these backups to restore
00:00
our database to a point in time and the past,
00:00
provided we do that within the retention period.
00:00
We can also use it to restore
00:00
a deleted database within the retention period.
00:00
If a database is accidentally deleted, that fine.
00:00
However, we cannot use these to
00:00
restore data if the server is deleted.
00:00
If an Azure SQL database,
00:00
which is the administrative container
00:00
for our databases and elastic pools,
00:00
if that is deleted we cannot
00:00
use this automatic backups to restore that.
00:00
When it comes to long-term backup retention,
00:00
this is something that we can use as
00:00
an extension of the automatic backups.
00:00
If we have different compliance requirements to meet,
00:00
we can extend our retention for up to 10 years.
00:00
This backups are also going to be stored on
00:00
geo-redundant storage of
00:00
read accesses geo-redundant storage,
00:00
we can enable long-term retention for single database,
00:00
for elastic pool databases.
00:00
It's not yet available for Managed Instances,
00:00
it can be used to restore if the server is deleted.
00:00
That's one of the good use cases of these,
00:00
if you delete a server
00:00
either accidentally or maliciously,
00:00
you can go to your long-term backup retention
00:00
because that's copied off to
00:00
another storage and then you can
00:00
restore your databases back from there.
00:00
There's also a feature of
00:00
Azure SQL called Active geo-replication.
00:00
Because while backups are great,
00:00
they're great for disaster recovery,
00:00
it doesn't help us with downtime during
00:00
unplanned or temporary planned failures.
00:00
What do I mean by that?
00:00
Let's say you wanted to do
00:00
some application upgrades and
00:00
you need to be able to failover,
00:00
if there were to be a confusion,
00:00
or there were to be an issue, something goes wrong.
00:00
That's where backups may not be great for that,
00:00
where replication can be
00:00
a useful technology for us to use.
00:00
What Active geo-replication does is it
00:00
leverages the always on technology of SQL server.
00:00
It asynchronously replicates committed transaction
00:00
on a primary database to a secondary database.
00:00
Essentially we have primary and secondary database
00:00
that stays in sync.
00:00
We can have up to
00:00
four secondary databases for each primary database.
00:00
If for any the reason our primary database
00:00
fails or it needs to be taken offline,
00:00
we can initiate a failover to any
00:00
of our secondary databases where
00:00
the applications are and
00:00
we can promote them actually to the primary.
00:00
Because the secondary databases are readable,
00:00
they can also be used to offload
00:00
read only workloads like reporting jobs.
00:00
Once you configure Active geo-replication
00:00
and you enable these,
00:00
you can actually take advantage
00:00
of your replicated databases,
00:00
the secondary ones, and
00:00
then run your reporting against them.
00:00
Active geo-replication is not yet
00:00
supported by Managed Instance.
00:00
Finally, when it comes to availability,
00:00
lets talk about something called auto-failover group.
00:00
Now auto-failover group, think about
00:00
it as an extension of Active geo-replication.
00:00
Active geo-replication helps us
00:00
to replicate our databases,
00:00
but the thing is when it comes time
00:00
to initiate a failover,
00:00
that still has to be done by us,
00:00
we still needs to get involved.
00:00
Now we can automate that to
00:00
some other process that we have,
00:00
but that still something that needs to come from us.
00:00
What active failover group
00:00
does is it adds an extension to that,
00:00
and it helps us to do transparent fail
00:00
over of a database to the secondary database.
00:00
Auto failover group is supported by Managed Instance,
00:00
so that's great we can use that in this scenario.
00:00
Let's talk about monitoring of Azure SQL.
00:00
When we talk about monitoring of
00:00
Azure in general or any service in Azure,
00:00
we're mainly referring to three main things.
00:00
We're talking about metrics.
00:00
Now, metrics are defined as numerical values that
00:00
describe some aspect of
00:00
a system at a particular point in time.
00:00
What does that mean?
00:00
What that means is information about
00:00
the service that's emitted by the Azure platform.
00:00
For example, what's the CPU utilization?
00:00
What's the memory utilization?
00:00
What's the storage utilization?
00:00
Those information that emitted
00:00
like from the hypervisor level or
00:00
from the Azure platform in this case,
00:00
those are referred to as metrics,
00:00
they describe the state of
00:00
a system at that point in time.
00:00
We can go ahead and view this information
00:00
about metrics of Azure SQL databases.
00:00
In the metrics explorer,
00:00
this is not something that we need to enable once
00:00
we deploy a service metrics are
00:00
automatically emitted
00:00
>> every one minute for most metrics,
00:00
>> but in some cases it can be every five minutes.
00:00
Then we have the activity logs,
00:00
and activity logs they provide
00:00
insight into subscription level events.
00:00
What does that mean?
00:00
We're talking about
00:00
administrative event or administrative operations.
00:00
Who did this action?
00:00
When did they do this action?
00:00
What action did they do?
00:00
Those are administrative activities
00:00
also coming from the Azure platform.
00:00
When it comes to activity logs,
00:00
there's nothing that we need to do.
00:00
Also, it's automatically emitted by the Azure platform,
00:00
so we don't need to do anything.
00:00
In this case, we can just
00:00
explore each using the activity logs explorer.
00:00
Then we come diagnostic logs.
00:00
Now diagnostic logs referred to
00:00
events that are occurring
00:00
within a system or within a service.
00:00
While metrics, are coming from
00:00
>> within the platform itself,
00:00
>> diagnostic logs are coming from
00:00
within the Azure SQL service itself.
00:00
In other words, what exactly at
00:00
the different operations that are
00:00
going on within the servers,
00:00
and that's where we can get a lot of
00:00
insight into our databases.
00:00
When it comes to diagnostic logs,
00:00
it's something that's not enabled by default,
00:00
as I mentioned, it's something that we have to enable.
00:00
We have to enable this in
00:00
Azure SQL or Azure SQL Managed Instance.
00:00
Now when we go to enable these,
00:00
we have the option to send
00:00
this data to three destinations.
00:00
It has storage account,
00:00
event up on Log Analytics Workspace.
00:00
They are different use cases
00:00
for these different destinations.
00:00
For example, if you're going to be sending
00:00
this information to Azure storage accounts,
00:00
the main use case for this is archiving.
00:00
You wants to take all these diagnostics information
00:00
and then archive them.
00:00
You could collect them using
00:00
certain third party information,
00:00
or you could just store them at a very low price.
00:00
When you enable diagnostic settings,
00:00
you can configure the retention period
00:00
for this information in the Azure Storage account.
00:00
The other option is Azure Event Hub.
00:00
Now, the many use cases for Azure Event Hub is
00:00
to integrate this information that's coming
00:00
from within Azure SQL service with
00:00
custom monitoring solutions or hot pipelines.
00:00
What that means is that we can do near
00:00
real-time analytics and insight into our Azure service,
00:00
and Event Hub allows us to be able to do this.
00:00
Also, in this case,
00:00
when we enable this option,
00:00
we can configure the retention period
00:00
where we can define how
00:00
long the data is going to be stored in Azure Event Hub.
00:00
The third option is Log Analytics workspace.
00:00
This option is mainly for the use case of getting
00:00
intelligent monitoring or intelligent insight
00:00
out of this information.
00:00
Azure Log Analytics is another service in
00:00
Azure where you have like
00:00
this Log Analytics workspace where
00:00
we can send this information into
00:00
and then we can use something called
00:00
the Kusto query language to query
00:00
all this information that's inside this workspace.
00:00
Then we can gain insights and
00:00
useful information on trends out of this data
00:00
>> that we're just passing into the workspace.
00:00
>> We can either go down the path of building out
00:00
the queries and building out a report by ourselves,
00:00
or we can go down the path of Azure SQL Analytics.
00:00
Azure SQL Analytics is
00:00
a very ready-made available solution in
00:00
Azure that can layer on top of
00:00
the Log Analytics workspace to extract insight about
00:00
Azure SQL service from
00:00
information that's in the Log Analytics workspace.
00:00
That does not build in all these queries and
00:00
reports and all these things from scratch by ourselves,
00:00
we could just leverage the solution.
00:00
There's a difference in this case because
00:00
the retention period is not something that
00:00
we configure when we enable this option,
00:00
the retention period is based on
00:00
what is configured for the workspace itself,
00:00
which the default for
00:00
Azure Log Analytics workspace is 31 days,
00:00
but it's something that can be extended to
00:00
730 days in Log Analytics
00:00
based on the option that's configured there.
00:00
I'll go ahead and stop this recording,
00:00
this video for now, and I'll resume
00:00
>> the next video with the other information
00:00
>> about Azure SQL database Design Decisions. See you.
Up Next