>> After you've deployed and verified your deployment
of Azure SQL Database or Azure SQL Managed Instance,
the next thing you might do is configuring
your server or your Managed Instance.
>> You follow the choices in a previous video [inaudible] have
made to deploy an Azure SQL Database.
There were several choices to deploy Managed Instance.
I thought I might even find interesting about
how to compare configuring
a Managed Instance or an Azure Database or
logical server with configuring a SQL Server.
How do you compare and trust these things?
Well, first of all, consider Managed Instance.
Remember, this is a SQL instance.
Things you're familiar with,
like the sp_configure stored procedure,
even trace flags, configuring things like tempdb,
the sizes of model and so forth,
how many different error logs you get to cycle through,
edition which, in this particular case,
is like your service tier or
your slow options or even networking your space management.
All of those tasks at the instance level are available to you.
In some cases, there are some limitations.
For example, you don't get to restart your Managed Instance,
so you can't pick a configuration option that requires a restart.
Although what we believe in many cases,
you don't even really need those options
when using a Managed Instance system.
That's an example of things you would
go through to configure a Managed Instance.
What about configuring a database?
Well, first of all, you might need to configure
databases within a Managed Instance.
What you mean you've configured the actual database
in Azure SQL Database,
and the most popular way to do that that
anyone knows up is ALTER DATABASE.
There's certain options that you're going to get.
For example, you can do ALTER DATABASE options for
files and their sizes with MI.
But in Azure database, you only have one file,
so you don't configure files directly with ALTER DATABASE.
There are certain set options used to do it like setting
checksum or various properties of your database.
Those option exists both for MI and Azure Database.
Let me stop and pause something to give you a tip.
If you look on our documentation for ALTER DATABASE,
you're going to love the interface because at the top,
we're going to give you a choice
of what is your deployment option?
Is it a SQL Server?
Is it a Managed Instance, or is it a database?
As you pick those options,
we will pivot and show you
the syntax and examples that apply to those choices.
Many of you've seen, we've introduced something called
ALTER DATABASE SCOPED CONFIGURATION
for things like MaxDegreeOfParallelism.
All those options exist for you there,
both for MI and for DB.
You might find it interesting,
aside from network configuration
and different space management choices,
which we'll talk about in a second,
that Azure Database has some interesting properties.
For example, we offer stale page detection
for certain concurrency and integrity scenarios,
and we turn that on for your database.
There are certain default options that we put on for
an Azure SQL Database that you may be familiar with.
For example, read committed snapshots on
by default to actually help with locking scenarios.
Azure databases, and even in Managed Instance,
only support the full recovery model.
That's on by default and cannot be changed.
But we're going to light up checksum for integrity.
We're going to turn on Query Store,
which we'll talk about in other modules for performance.
We'll even put on by
default Transparent Data Encryption
and the new feature called ACCELERATED_DATABASE_RECOVERY.
These are the default options that are
nice for you that we did light
up for you to give you that more managed experience.
Now, I thought you might find
interesting that if you're configuring
a SQL Server with a tool like SQL Server Configuration Manager,
you might be used to some things like
starting and stopping the service or turning on
lock pages in memory with certain options or
instant file initialization or even file stream,
we restrict those choices from you.
Now, some things like Locked Pages in Memory,
we're studying for the future,
should we enable it by default?
Do we even need to do that in our infrastructure?
But Ana mentioned about server collation.
You can pick that in NMI deployment,
but you can't change it past that point,
so it's important you make
that decision upfront of what you're going to use.
All these other options like ALTER SERVER CONFIGURATION,
where you might use it for like NUMA deployments and so forth.
None of those tech things make sense in
a PaaS environment so we don't turn
on and don't allow.T-SQL Syntax.
Now, you may use to things like mixed-mode security
or log on auditing with SQL Audit or Server Proxy,
none of those things really apply
or allowed to be configured a Managed Instance,
but you have to ask yourself, does it matter?
Mixed Mode is on by default because you have to
supply a SQL login when you deploy,
you're going to see in the security model,
we're going to lie to do Active Directory authentication,
so you're going to get that world together and log on
auditing is done in SQL audit for a SQL database.
But you're going to find out that we're going to give
you some options in Managed Instance.
They're going to feel more like a SQL Server.
If you think about these all combined,
you're going to perform some of the same tasks you're used to in
configuring a Managed Instance like you do in
SQL Server with some differences.
You're going to configure databases
very similar like you use in SQL Server.
But there are some things at the instance level with tools like
SQL Server Configuration Manager that
just don't make sense in the Azure world.
Now, we mentioned before in this configuration task,
you may have to consider things like space management.
We thought we'd take a second and explain
some of the differences between
space management in Azure SQL Managed Instance
and database versus SQL Server.
For example, when you picking
the storage capacities for
Managed Instance to phase on deployment option you picked,
you're picking the max storage for the entire instance,
not for the database,
so vCores can actually affect this and in case of business
critical in order to meet
our SLAs and because you're using local storage,
you actually have a little less space than you
would have in a general-purpose scenario.
The database that are created use model,
but the models are slightly larger and they're
configurable in these scenarios with Managed Instance.
You can alter the size and number of files,
but you don't place files and
physical locations in Azure Managed Instance.
But you have to ask yourself, does it matter?
If you have the performance you need,
the various IOPS options and latencies,
placing files in physical storage are
actually one thing that we're doing to abstract to you.
It's one of the benefits of Platforms as a Service.
Now, many of you have used SQL for a while,
are used to running out of space.
An 1105 error, which the most common error in the world
to run a space in database, it still applies.
But what if you rent out a space for
the entire Instance, as I told you,
is one of the ways of configuring your Managed Instance.
You get a slightly different error to say, "Hey,
you've run out of the max capacity for Managed Instance.
You've got to actually give it more space."
Now, one of the interesting aspects
of using Managed Instance is that
databases for general purpose or on Azure Storage, not local.
As it turns out, there are some options where you can increase
the size of your files and those Azure storage options,
and get better performance depending on that size you're picking.
What about Azure SQL databases?
Is it similar or different?
One difference that I've noticed when I'm looking at it is,
I'm always looking at things like sys.
databases for max size of my files.
When you deploy an Azure SQL database,
you're going to pick something called data max size.
That is the maximum possible size of
a single database file that applies to Azure SQL database.
But what you might find a little bit interesting is,
if you pick a really large size,
we don't deploy a file immediately of that size,
we may grow into that.
The max size in
a catalog view may not be exactly that data max size,
but it'll grow into that size.
Hyperscale, as we've talked about,
that architecture is a lot different.
You didn't pick a size.
Hyperscale, just create the database of
40 gigs and just grows itself automatically.
Again, you're going to see in
the architecture that grow is not going
affect you because we're using Azure storage for your databases,
but a series of caches to support
performance in each part of the tiered architecture of Hyperscale.
Now, transaction log maximums
can be a little bit different in this space.
They are actually 30 percent above
your data max size and that's the max you need.
Now, people have said to me when I've showed then this is,
[inaudible] this is a problem.
What if my transaction log
starts growing out of control? Guess what?
We regularly backup your logs
in this full recovery model to truncate them,
and we use accelerated database recovery
so we're not beholden to active transactions,
and you don't even run into these transaction log scenarios
that grew out of control.
Those are some interesting details that you'll consider as you're
choosing Managed Instance and Azure SQL database,
and when you're thinking about it from
a space mentioned for perspective.
We talked about configuring instances,
and databases, and space management.
Perhaps the last topic to consider about
just a configuration after deploy is, your network architecture.
>> When you start to think about how you're going to connect
to your SQL Database or your Azure SQL Managed Instance,
there are few options that you have available.
We thought that, there's a big impact on what this decision is,
so we wanted to make sure we brought it up.
Now, what you'll see in the top-left of your screen is
the logical database server that
you would connect to for Azure SQL database.
It looks slightly different for Managed Instance.
But if you look at the DNS hierarchy,
you can also see things like the region that it's in,
sometimes, which control ring and some IP address.
Now, when you try to connect to SQL from, let's say,
a virtual machine or your local machine, by default,
what's going to happen is,
you're going to go through a gateway.
This is called the proxy approach.
That basically means we're going to go to the gateway.
Once everything's okay and you're authenticated,
then the gateway is going to point you to
the Azure SQL database that you basically logged into.
Now, the other option is redirect.
In redirect, there's really two phases.
First, what we'll do is we'll go to
the gateway and do the handshake.
If it comes back okay,
the gateway will return how to connect to that database.
We can actually redirect
all future queries on that connection directly to the database.
Now, you might be wondering,
why do I care and what's the impact?
But using redirect, since
we're not going to the gateway every single time,
is actually going to allow us to
get much less latency in our requests.
While proxy is the default option,
redirect can be a more performance solution.
The reason we don't do this by default is because it
does require that you open up a few additional ports.
This is something you'll have to talk to
your network or security people to make sure
that this is okay and that you are going to be able to
connect if you switch to the redirect method.
Hopefully, in this video, you've seen how to configure
your Managed Instance and your logical database server,
including your databases in either of those deployment options,
and including some things like how to deal with
space management and your connectivity to SQL.
Stay tuned for the next video.