>> SQL Server 2022 is Cloud connected,
and there's a lot of ways why.
Learn more in this episode of Data Exposed.
[MUSIC] Hi, I'm
Anna Hoffman and welcome to this episode of Data Exposed.
Today, we are joined once again by Bob Ward and we're
continuing our SQL Server 2022 series.
I hope you're following along.
In this one, we're going to talk about
Cloud connected. What is that?
>> Yeah. Anna, great suggestion
for us to go right the Cloud connected.
In fact, remember the wheel of power?
We're going to start at the top of the wheel with Cloud connected.
Let's dive into what that really means.
>> Yeah.
>> SQL Servers had hybrid capabilities for a while but basic.
Now we're going to connect to Managed Instance to
do things like disaster recovery.
We're going to connect to Synapse for near real-time analytics.
We're going to connect to Purview
for things like policy management.
We're going to connect to Azure Active Directory
for things like multi-factor authentication.
We're always connected to Defender to
help look at things like assessments and alerts.
The engine is baked in to know this functionality,
but we need some helpers like
Azure Arc agents and the extension for
SQL Server or the self-hosted runtime to use things like Synapse.
But we're going to use things like distributed availability groups
baked into the engine to support things like Managed Instance.
Anna, what I'd like to do is dive
into more what some of the sketch looks like,
maybe even a demo today.
>> Yeah. Architectures and demo, let's do it.
>> Architectures and demo, yeah, let's do it.
Let's talk with Managed Instance.
It was actually about challenges.
Saxo Bank is one that we know
very well that's faced this challenge.
Ola works for them.
He's looked at this technology and told us, hey,
setting up and maintaining a DR site is not trivial.
SQL Server has all the capabilities you need to do this,
but you have to manage the DR site.
How about use the power of Managed Instance
now to do online failovers in the Cloud?
Of course, Managed Instance is managed by Microsoft
so that DR sites always is going to be there when you need it.
What we're providing now to start with for
general availability is the ability to easily
connect to SQL Server to
Managed Instance using distributed availability groups.
We have wizards and SMS to set this up for you.
Then if you have a disaster,
you can online failover to MI.
If necessary, in general availability,
you can offline do a disastrous restore back to SQL Server.
But also you may have seen us make this announcement.
We have a new preview experience for
an online failover like a switch role type scenario.
I'm excited see that eventually land for general availability.
Anna, look on this screen here.
One of the neatest things about this
is we're proud to announce that what's coming
is if you declare
your Managed Instance site as a disaster recovery site,
the SQL license is free.
>> Free.
>> Free, just like we do today for Azure virtual machine.
One of the things I wanted to show you though is this idea of
doing an offline DR because a lot of people have
asked me how can you restore back for
Managed Instance if it's versionless to SQL Server.
Let's look at it. Starting with today with Managed Instance,
all the Managed Instance are now compatible at
a database version level with SQL Server 2022.
It's aligned with this release.
What you can do is you could stop
your white workload in Managed Instance.
We're going to go back to SQL Server.
You could do a copy-only backup of MI into Azure.
Then you could do a restore of backup into SQL Server.
Notice here the database version numbers
are aligned between the releases, which is pretty cool.
Now of course, if you wanted to keep going here,
you would migrate your instance objects, redirect your app.
You can even reestablish this link back.
One of the things people have asked us about this though is like,
if you're going to make this compatible,
how do you get new versions or features of MI?
As long as we build features in MI
that are not changing the database version,
we can continue to iterate on MI even in this model here.
Pretty excited to see this land and
our customer is pretty excited about it as well.
You can do these restores even if
you're not using for DR purposes,
if you just want to restore MI database back to SQL Server for,
say, some compliance or use for something.
This is MI link, one of the big features of
this huge Cloud connected concept. Would you like to see it?
>> Yeah, we'd love to see.
>> Let's see it in action here. What I've got here is I've
got a SQL Server setup here, SQL 2022.
In Management Studio here,
I'm also actually connected to Managed Instance itself.
You see here I've got my connection to SQL 2022.
I've used the WideWorldImporters sample database
as a way to show off this functionality.
Let's see a little bit more about how this works.
In WideWorldImporters here, what I'll show you
here is the ability use
SSMS to use this wizard we talked about to
replicate and synchronize to Managed Instance.
I've got all my prereq setup.
What I'm going to do here is I'm going to
pick the WideWorldImporters database.
I backed it up already.
Now I need to sign into Azure and I've
shortcut some of those steps a little bit.
I need to pick the target MI that I want to replicate to.
I've got MI already deployed.
This is now compatible with SQL 2022. I'll do that.
Then here is where behind the scenes SSMS
is going to set up an AG and a DAG.
Even I don't even already have one, I can do that.
>> Wow.
>> I know. I didn't even have one,
it's building for me or I can connect to an existing one.
When I do this and run
this finished button right here, what it's going to do,
it's going to cede my database to Managed Instance
and set up the availability group and DAG.
I timelapse that a little bit.
But you can see now in SSMS,
my database in SQL 2022 is synchronized to Azure.
>> I see.
>> An AG and a DAG are already set up for me automatically.
You can see the database is online and now available inside MI.
Now I want to show you though how you can
tell where are you in terms of who's primary, who's secondary.
Using these queries here,
I can tell that I'm on a SQL Server on my primary,
and I know that because it's read/write.
Here, I'm showing you the database version that's
compatible to go back and forth with MI and SQL Server.
On the right over here, I'm using a similar query to show,
hey, am I right now?
It's read-only, which is my secondary,
and divergence are actually compatible.
Now that you see this in action here,
what I want to show you is some changes,
like I'd like to make changes to my database.
I've extended WideWorldImporters to
create a vehicles table and a stock items table,
like a cargo system.
I'll create these tables locally on SQL Server.
What I'll do is I'll also do some population of data.
These are the DDL of the scheme I'm going to create in SQL Server.
Then what I'm going to do, well,
I picked Texas cities.
>> Surprise. It is Dallas.
>> It's project Dallas.
Dallas is actually listed on there.
I'll go ahead on SQL Server,
create my tables, populate some data.
Then because I've got an AG setup,
I can easily go in and tell,
hey, on my MI system,
is the data synchronized?
This is like a cargo count query.
You can see here I'm connecting here to MI. I'll run the query.
I've got one cargo,
one from each city in Texas for my different vehicles.
>> Well, now that I've done this,
I kind of like to see if I can failover to MI.
I'm going to use the same SSMS wizard to set failover database.
In this particular case, we're going to switch
for MI to be now our primary.
I've signed into Azure as well.
I've got this already signed in,
and I'm going to go ahead and pick a planned failover,
which means I'm stopping my workload or my primary.
I could do a forced when a SQL Server wasn't running,
and I'll go ahead and clean up the AG and DAG that
SSMS already set up because I just want to move
over to MI and be able to store back later.
So in the wizard, when I hit Finish here,
what it's going to do is going to make the AG and DAG synchronous,
ensure that our changes have been synchronized,
and then we'll see MI has become our primary.
Let me finish here. This script has finished.
What I should be able to do is go over and run some
of the same scripts I did before to see the status.
You can see here now I'm going to connect to
MI and notice it's now become the read-write,
which is now my primary.
I've successfully failover online to Managed Instance, of course.
I want to show you that offline
capability to kind of restore back.
What I've done is I've already copied over
a backup from MI into Azure.
Let me show you what's going to look like to
restore this into SQL Server.
Notice that at the top up here, this is SQL.
I forgot to show you this first
because to show you the version compatibility,
this is SQL 19 at the bottom right here.
I've got that setup first.
I'm going to try to restore that copy link backup into 19 first.
Notice it failed, and it failed
because of a version compatibility right here.
That's what people have seen before.
That's because the database version in MI is higher than
the version of SQL IT
all the metadata and storage structures in the system.
The other thing I can show you this by doing
restore header only from the backup,
you can see the version is baked into the header of the backup.
That's how SQL Server knows this version is not compatible.
For SQL 2022, it's the same.
Let's go try it there.
I'll take that same backup,
I got an Azure Storage,
and I'm creating a new version of
the database because I've already got
WideWorldImporters on MySQL Server,
and I'll wait a few seconds here after the restore.
And there you go. It's all stored.
>> From MI to?
>> Back to SQL Server because of the compatibility that exists.
That's just a brief demo of how I've replicated
failover and restored that backup back to SQL Server.
>> Okay, wow, this is pretty cool.
Loved to see this, love to see
this link capability for Managed Instance.
But there's other Cloud-connected stuff, right?
>> There are, and the next part of this is Synapse,
for near real-time analytics.
Atrium is a company that uses analytics a lot,
they love to use Synapse.
It's expensive though to create ETL jobs,
copy, update it, it's out of date, it's stale.
We have something called a Synapse link for SQL,
where we're going to allow you to seed data
directly into Synapse SQL pools.
SQL Server Monitor Log changes at that point and
automatically feed those changes in the Synapse near real-time.
Let me show you a little bit architecturally for that work.
You know I like architecture diagrams.
>> Hyperfocus.
>> Hyperfocus, yeah, exactly.
On the left side here, imagine this is
your SQL Server with a database on-premises,
Azure being that divider here.
You first set up a Synapse workspace.
Then you'll go in and create what's called
SQL dedicated pools in Synapse.
You can do all this from the Synapse Azure
portal or through PowerShell.
Then you create something called a linked service for SQL.
This is the ability for you to tell what is
my SQL Server I'm going to actually connect to.
We will build what's called a control plane in
Synapse where we're going to be able to ingest data,
will also allow you to set up the self-hosted runtime
anywhere on your network that can talk to SQL Server.
This is why it's supported for Linux, too,
because even though that programs a Windows program,
you just have to know how to connect to
SQL somewhere in your network.
So that your linked service.
Then you build something called a landing zone.
This is an Azure storage account.
We're going to store data temporarily before it does in Synapse.
You also create a linked service for it.
Now at this point, nothing's really happening.
Just set yourself up for success as a one-time operation.
Then you get what's called the link connection.
A link connection tells the hosted runtime, hey,
can you go tell SQL Server there's some data you
need synchronize based on tables that were selected?
You do that from Synapse Studio,
and we'll take parquet files and take it from
SQL tables as a snapshot and feed it the landing zone.
The ingestion service picks it up from the landing zone itself,
these parquet files and turns it into actual create tables and
dedicated pools and synchronizes the data, which is great.
Now you've got this all set
up and you're synchronized to start with.
But we said you're going to do
near-real-time changes, how does that work?
Well, as you make changes in SQL Server,
just like a technology like change data capture,
it's not the exact feature,
we'll monitor for committed log changes in the server,
we'll put them in a queue that we have in memory,
and we'll feed other types of files into that landing zone.
The ingestion service understands where those are.
It takes those files,
turns those as incremental changes as inserts,
updates and deletes, feeds
those directly into Synapse dedicated pools.
That's how the overall system works.
Of course, if you're Patrick LeBlanc or Adam Saxton,
you can build a Power BI report on
top of it as well because you're in Synapse.
But it's a pretty impressive architecture,
a little bit of setup to get done.
But once you do that, the system just works on its own.
>> Pretty amazing.
>> There's more. There's security.
There's AD authentication, there's purview and defender.
people know defender already as a technology.
Let's talk about AAD and Purview.
Those are new capabilities for SQL 2022.
What companies have wanted is an alternative way to
authenticate versus just SQL and Windows Domain Authentication,
and AAD provides that.
You can use Azure RBAC to set this up.
It's going to support things like multi-factor authentication.
It uses the Azure extension we talked about.
Remember the Azure Arc agent,
an Azure Arc-enabled SQL Server.
You can do logins based on AD accounts or, Anna,
you can do users without logins,
just like you can do in Azure.
We support link servers as well.
The way this works is that the Azure extension
will take data from Azure,
put it in the Windows registry.
That way when somebody logs in with Azure Active Directory,
we can go look at the registry to know how do
we connect and authenticate to AD itself.
It's a pretty seamless system where the Azure extension is giving
metadata for SQL Server to
know how to connect to AD and authenticate.
Don't forget, SQL Server and Azure VM
also support us now in public preview.
Now the other part of the story is purview access policies.
Let's say you don't want to grant
specific logins accessing the SQL Server.
You want to go to a central place like
purview to give somebody read-writes,
modifies coming soon, or performance monitoring writes,
things you want to do outside of SQL.
The SQL Server engine has been enhanced to understand
these policies and automatically apply them to the system.
The beauty of this technology is this: I can apply this
across 50 SQL Servers in one place.
That is both AAD access and purview.
It's a pretty powerful story from a security perspective,
and I just flew through
so much stuff that SQL Server is truly a hybrid data platform.
Managed disaster recovery with Managed Instance,
near real-time analytics with Synapse,
new security concepts with things like
Azure Active Directory and central government purview,
all built into SQL Server,
all part of SQL Server 2022.
>> Wow, this is a lot,
but it really tells the story of how
SQL Server 2022 is Cloud-connected.
Bob, thanks so much for joining us
on this second episode of the series.
There's lots more to come.
Be sure to keep tuning in for this series.
For our viewers,
if you like this episode, go ahead, give it a like,
leave us a comment, and let us know what you think
of these Cloud-connected capabilities.
Check out the links in the description to learn more.
We hope to see you next time on Data Exposed.