>> Learn how SQL Server 2022 is
a Cloud-connected database this week on Data Exposed.
[MUSIC]
>> Hi, I'm Anna Hoffman and
welcome to this episode of Data Exposed.
Today I'm joined once again by Bob Ward.
Bob thanks so much for joining us again today.
>> Anna, excited always to be on your show and
certainly excited to talk about this topic, SQL Server.
>> Exactly, yeah. Today we're going to be
continuing our discussion on SQL Server 2022.
If you haven't seen the first episode of this series,
I encourage you to go watch it.
We'll put a link to it in the description.
But Bob, we're going to be continuing our discussion.
We've seen an overview of SQL Server 2022.
I'm so excited to dive into some of these details.
What are we going to be talking about today?
>> Thanks, Anna. In the first episode,
we really just tried to give you kind of
a broad overview of what we're trying to do with this release,
with SQL Server 2022.
What I thought would make sense today
is dive a little bit deeper into
this first area that I call Cloud-connected or Azure enabled.
I'd like to explain over
the next few minutes, what do we mean by that?
Why would you do that and what are some
of the scenarios we're trying to solve for
our customers with connecting SQL Server to the Cloud?
>> Awesome. That sounds great.
Let's get right into it.
>> Let's do it. I just want to remind everybody though
the overarching thing we're doing with SQL Server 2022.
The first three areas are really
what we're going to focus on today and that is
connecting your SQL Server with services like Managed Instance for
disaster recovery or analytics with
Synapse or Purview for security.
But don't forget, and you're going to see this in
future episodes in Data Exposed and other things
that we'll be talking about this release that we continue to make
big investments into security performance and
availability for SQL Server and the fact that SQL Server
2022 continues on this journey of
this consistency of Edge to the Cloud,
whether it be our engine, our tools, or our languages.
But let's talk specifically about
these three areas: Managed instance, Synapse and Purview.
First of all, today Anna,
somebody can actually set up
a disaster recovery site and
their own data center with SQL server.
They can use things like distributed availability groups.
They can even do that in Azure.
But it's a lot of time and effort and configuration to
make that work plus the disaster recovery site,
they have to do all the management of that.
Would it be cool if you could actually link
your SQL Server to Azure SQL Managed Instance,
which is a Platform as a Service?
What we would like to be able to show
customers to do is that now you can
easily take SQL Server through tools like Management Studio,
point them to a deployed Azure SQL Managed Instance
and say I'd like to attach my SQL Server to that,
like to seed my database,
like an availability group.
But I'd like to make this like
a distributed availability group to
Managed Instance but can you do all the work for me?
Can you just make all this configured for me?
Wouldn't that be nice?
That's what we're trying to do in the background.
Then of course, you can actually use
that managed instance for read scale purposes.
But here's the cool thing, Anna,
and this is probably going to blow your mind.
You could take a backup from
Azure SQL Managed Instance and for the first time,
restore that back to SQL Server.
If you look at the diagram here,
it really is disaster recovery in the Cloud,
which you could actually set up an online failover.
Let's say you had a problem with your SQL Server.
You could say, hey, I'd like to fail us over to
Managed Instance because you've been updating that for me.
Now I've got platforms of service behind
the scenes and they complete Azure infrastructure,
powering everything I need.
I can start using that as my primary and then when I'm ready,
I actually could move back to my SQL server or the
interim I could take a backup of Managed Instance database
and restore it to a SQL Server to look at things
temporarily while my Managed Instance is acting as my primary.
I'm really excited to set up this for customers
and see this in action because it's
going to make disaster recovery a lot easier for them.
>> Yeah. This is amazing.
It is blowing my mind.
I feel like this is one of those things that would be
awesome to see it to believe it.
Is there something you can show us today, Bob?
>> You want a demo on your show?
>> Yeah.
>> I think we can do that. I think we can do a demo.
Here's something that we recorded that shows the experience.
Let's just unpack it as we're going through this.
We record these same things sometimes Anna,
because it's time-lapsed because it
could take a while to show everything.
But here's a SQL Server I'm connected
to and I've got the WideWorldImporters database.
You can see I'm showing my server name,
which is a local server name.
I'm showing it to rewrite.
That's my primary and I'm showing some data.
It's like an e-mail campaign we're adding to WideWorldImporters.
What I'm going to use now is
a wizard to attach myself to Managed Instance.
Now I've already done that process here and
through that process I point my Azure subscription.
Here's my target at Managed Instance I want to
go to and we see that database.
Look what happened behind the scenes.
When we attach this,
we automatically configure an availability group
and a distributed availability group for you behind the scenes,
and connect that to Managed Instance.
You can see your own Managed Instance.
This is what I've already deployed.
I've deployed a business critical to your Managed Instance,
which by the way has its own availability group behind the scenes.
I've told SQL Server,
connect to that and just start taking changes to that.
You can see here in Management Studio,
I've got the same database.
In fact, one cool way is to look at queries side-by-side.
On the left is the query to
SQL Server and on the right is the query to Managed Instance.
You can see it's read-only because I've
set it up as my replica automatically.
It's got the same set of
e-mail addresses for my campaign on both systems.
What if I made a change?
I'm going to make a change to the local SQL Server,
and because I've set this up as a DR site,
I should be able to go into the Managed Instance
as you can see in a comparison and see both rows.
Automatic changes have taken place.
Things are incrementally changing for me
because it is my disaster recovery site.
Well, that's pretty cool to see how that actually works.
But what if I wanted to failover?
What if I had a problem on my main system,
I could hit this migrate section.
By the way, many screens may
change as we're building and working through the release.
But here what I'm going to do,
I'm going to sign this Anna Hoffman.
I like using your account all the time, Anna.
I'm going to sign in as Anna Hoffman and I'm actually going to go
into my Managed Instance and say,
that's what I'd like to failover to.
I'd like to do something called a planned failover
where the last few changes are going to get synchronized.
I'm going to [inaudible] my workload.
Say I want to do a planned failover.
Just like you would do in a distributed availability group,
we're going to go ahead and fail over to that Managed Instance.
Now behind the scenes, because I said planned,
we're going to make sure data gets synchronized,
the last few changes,
and then we're going to let SQL Server
connected to Managed Instance make it work.
Now, I think what I'd like to do is insert
data into that new primary and see if it exists.
Can you see now here that the Managed Instance is
rewrite and that new row shows up.
That's pretty cool. Now we did say it would be
nice to take a backup of
managed Instance and restore it to SQL Server.
Customers have asked for a while.
Can I do something like that?
Is it possible that I could back up
my Managed Instance and restore it to SQL Server?
Well, there's a copy only backup feature in Managed Instance.
So I can take this database with that change, I can back it up.
Then what I'd like to do maybe is take that backup in the Cloud,
go to my local SQL Server,
it could be a different SQL Server that I'm using,
and restore that backup into the local SQL Server.
I'll use a different name here to avoid
confusion with the original database I had.
I'm pointing to Azure blob storage
where the database exists, I restore it.
What I expect to see is that new row show up.
Remember I inserted a new row in
my e-mail campaign when Managed Instance was was my primary.
Let's go take a look and see what that
looks like in the local SQL Server.
This is the local SQL Server. I'm going to run a query.
It is that new backup and sure enough,
there is that row showing up.
For the first time, Anna,
we're restoring from Managed Instance back to a SQL Server.
That is going to be a sticker shock for many people.
>> Wow. I mean, this is just amazing.
What I also love is that we're able to use T-SQL for a lot of us,
we're going to use SSMS,
the tools we already know how to use them.
It's really easy to set up and it goes full circle.
Thanks so much for showing us this.
I know there's other stuff I feel greedy
asking for more after seeing this one.
What else is there?
>> Yeah, no, I get it. I think
that's going to be a powerful feature for a lot of
people that are struggling to set up
disaster recovery or maintaining it.
We're going to make it a lot easier to do that.
Think of this scenario Anna,
today you've got operational data in your SQL Server environment.
I say operational, you're always changing
it like OLTP-type things.
But you always do an analytics on that and there's
all sorts of solutions people have built to do this.
But Azure Synapse Analytics is becoming
a very popular way and method to do analytics in the Cloud.
I might like to do analytics,
and if you see right here, let me back up just for a second here.
I might want to take my SQL Server and
use Azure setup analytics with ETL.
It's usually daily,
it's hard to build, it's hard to refresh.
It's like a copy process.
I could do this, I could take
my SQL pools in my warehouse for analytics,
and take SQL Server data,
and just refresh on a daily basis.
I could do Power BI on top of that.
But would it be neat if I could do this?
What if I could change this to go into SQL Server and
say here's some tables in my database.
I want you SQL Server to monitor
changes from the transaction log like change feeds.
Take those and incrementally populate that into my warehouse.
Now my Power BI reports can just be refreshed and
I can see almost near real-time of data.
I've offloaded my analytics.
I've reduced the need for ETL and I'm using the Power Synapse.
Of course Anna, a lot of people talk about
synapse and they're like what else can it do for me?
Remember that it supports doing things in
Azure Data like for unstructured or semi-structured storage.
Now you could join that data together or even
take advantage of things like Spark or
Azure Machine Learning to do machine
learning on top of this data that you just fed
in from SQL Server and
then the great integration of Power BI on top of that.
Think about now having SQL Server as
another source of data for your synapse workspace.
But in a much easier way setup to just feed changes into Synapse.
Very excited to see this happen,
a lot of people are struggling to figure
out how they integrate these worlds together.
We're going to make that link happen in
a very seamless fashion and quite frankly,
continue on the path of, you've seen it, from
other link announcements for Synapse Cosmos DB,
data verse and those type things.
Now one of the area we talked about was Purview.
Purview, exciting new technology for
Azure that we've recently announced.
Think about capturing things from your data estate,
things like the schema of my tables,
classification data from my tables.
I want to do that across a ton of assets in my enterprise,
both in the Cloud and on-premises.
With SQL server, we're going to integrate with
Azure Purview in a way that's going
to make that simple for you to do.
Then even take it a step further because
Purview can be a governance mechanism for
you to set up authorization and access to resources.
Imagine a world where you can set up
a central authorization scheme to decide
who are my performance monitor people in my organization.
I want to have a central way and
a consistent way of setting that up across different assets.
I want to further that least-privileged concept,
but I don't want to do it
myself across all these things like SQL Server,
can't Purview just handle that
and push those things down to SQL and tell it,
Hey, when somebody logs in,
this is the authorization I've set up in Purview.
You just work with that in my organization.
Managed Instance Disaster Recovery,
Synapse Link for Analytics,
and Purview for Security and Governance.
Three big areas we're working on.
There's probably a few other things you want to know about.
I had to throw these things in because
these are things that people have asked us
for about connecting further with
the Cloud and that's Azure Active Directory authentication.
Think of this scenario today, you want to go into
SQL Server in Azure Virtual Machine,
but you want to use that Windows authentication mechanism,
but you don't want to set up a domain controller.
Now you can just use Azure AD directly from a SQL Server.
It could be on-premises or it could be an Azure Virtual Machine.
Excited to see that one land
because several customers have said
to us that's just really important to us.
Because guess what, they're centralizing
authentication in Azure Active Directory.
Azure Defender's been a popular technology
now branded Microsoft Defender for SQL.
We were going to make sure that we continue down
this path of making sure SQL Server is part of this,
so you can be proactive about what
you're doing for vulnerability assessments.
To look at things like
injection attacks or attacks on your database,
injection alerts, alerts on your system.
You want to use this across your SQL data state.
We talked about Azure SQL Edge to Cloud SQL Server
2022 will also be part of that story with Microsoft Defender.
That's a pretty good look, Anna. I think what we have in
terms of making SQL Server a Cloud-connected database,
Disaster Recovery, Analytics,
Security, Authentication, and Defender.
Excited to see how this
is going to really help customers make their life
easier and do things in
more new and efficient ways with the Cloud.
>> Awesome, yeah. I mean, this has just been
amazing and it's cool to see that
Azure connectivity and Azure
enabled side of this version of SQL Server,
it's almost introducing this new version of hybrids.
I'm excited to see
opportunities that this opens for our customers as well.
For people who are just so
excited like I am and want to get hands-on,
can you remind us how we can get started with SQL Server 2022?
>> Absolutely. We got a couple of links
for you here to look out on the screen.
We'll post those as well for you.
Use this first one to get
more information just about the release itself.
We're starting out as we do with
any SQL Server release in a preview fashion.
If you want to sign up and start using
these technologies and working directly with us in engineering,
the second is a link to get into our early adopter program.
Then finally, Microsoft Mechanics,
this is a way for you to look at from
the mechanic's point of view world,
let me see and learn further and deeper about SQL Server 2022.
Anna, you know SQL Server is my jam and I'm
super-excited that SQL Server 2022 has been announced,
and I look forward as we move towards finally launching
this in calendar year 2022 to see how customers react to this,
how they use it, and
just to keep the message of SQL Server going.
Pretty excited to see it happen.
>> Awesome. Well, thanks so much Bob for
coming on the show and sharing all this with us.
It's been really exciting.
To our viewers, we'll have all those links in
the description as well as a link to this playlist,
so you can follow along the SQL Server 2022 series.
This is the second episode.
There will be more to come.
You'll definitely want to follow along with that.
Be sure to like this video, leave us a comment,
and let us know what you think of
the Azure Connected Capabilities in SQL Server 2022.
We hope to see you next time on Data Exposed.
[MUSIC]