>> Learn how you can connect to SQL Server 2016 to
Azure SQL Managed Instance using the new link capability.
This is going to enable real time data replication and read
scale out plus minimum downtime migrations.
This week on data exposed.
[MUSIC]
>> Hi, I'm Anna Hoffman and welcome to
this episode of data exposed.
Today I'm joined by Dani from the SQL Managed Instance team.
Dani, thanks so much for joining us today.
>> Thank you. Nice being in your show Anna.
>> It's great to have you on the show.
Last time we were in person, which was fun,
but it's still great to have you on
from all the way across the world, essentially.
But today we're going to be talking about
some new things in Azure SQL Managed Instance.
Can you give us a little bit of an overview of what
you're going to tell us and show us today?
>> Most certainly, I'm going to talk about connecting SQL Server
2016 to Azure SQL Managed Instance using Managed Instance Link.
I have prepared a couple of slides for this purpose.
We're going to learn about near
real time data replication and read scale out enabled by the link.
We're going to experience how it actually works,
going to see some demos
and then we're going to understand the tech
behind it and what does it mean to all of you guys.
>> Awesome. Well, we've got a lot to do,
so let's get right into it.
Can you start by telling us
what is the link feature for Azure SQL Managed Instance?
>> Most certainly. The link enables hybrid connectivity between
between SQL Server and Managed Instance
using always on technology,
it empowers you to use Azure at
your own pace, so you do not need to migrate to Azure.
You can connect your existing SQL Server to Azure and
use your data with all Azure services using the link.
While doing that, you can modernize your workloads
by getting the best from
Azure for your existing SQL Server workloads.
Then if and when you're ready,
link provides the best minimum
downtime migration solution to Azure.
>> Cool. This sounds pretty exciting.
Wonderful. How does it work?
>> Yeah. Essentially we establish
a link between SQL Server and Managed Instance.
We replicate data using
the always on technology, then you can the source,
the original data in SQL Server is your
Read_Write database and then replicated database
in Managed Instance is read only so you can use it
to offload your read only workloads for
applications or to use a myriad
of Azure services on your data which is replicated to the Cloud.
Once when ready. If you want to,
you can migrate to the Cloud,
but even without migrating to the Cloud,
you can keep the link running for weeks and months at a time.
>> Awesome. Here we go. Let's take a look.
>> Let's see it in action.
>> You're reading my mind, Dani.
>> What I have here on the screen is I have SQL Server 2016 and,
of course, data exposed database on it.
Then I have Managed Instance with no databases on it.
Here. I'm going to show that I'm running
SQL Server 2016 with Service pack 3,
and then we need our Azure connect feature pack.
For this purpose, I'm using the standard edition of SQL Server,
although I could use also enterprise and developer additions.
Next, what I want to do is this
is very specific to SQL Server 2016.
We only need to install
the Windows Server Failover cluster module.
We do not need to configure the Windows Server Failover cluster,
nor do I need to have multiple node cluster.
This is only such that I can enable the always on
in the sequel server which you see here on the screen.
Now those are the prerequisites to to get me going.
We've built in a super easy to use wizard.
You right click on the database,
you go replicate database,
you go Next, we check for some requirements that are
needed and then I need to select
the database that I want to replicate to the Cloud.
Then I need to log into Azure.
It's going to ask me for my credentials.
I'm going to provide that.
Then I need to select my subscription,
select my resource group,
and then a specific Managed Instance
I'm going to replicate my database to,
once that is done, I have advanced settings.
You really do not need to be next burden always on technology.
You can just for all intents and purposes,
click on "Next" or customize these settings here.
When you're done, we're going to display the number of
steps that we're going to perform for you,
and then we're going to create
an availability group for you and distributed availability group,
which is used to connect with Managed Instance.
Then I'm going to run this query to check on the seeding progress.
We're seeding the database over to Managed Instance,
and that's your initial data being moved to Managed Instance.
If I refresh the Managed Instance side,
I will see that database is now being in
restoring state and Managed Instance while it is seeding.
I will refresh my query again to check for the seeding status.
Because my database is small,
it has completed the seeding.
That means that my DB now has been
copied over entirely to Managed Instance and
any changes that I make are now
being automatically moved to Managed Instance.
I'm going to now check the updateability of my database in
the SQL Server and of course it is read
right because it is my primary database.
Now I'm going to create a table on my database and I'm going to be
inserting in a loop
some data to my primary database in SQL Server.
Now I'm going to go to Managed Instance.
Let's check that I'm executing this in Managed Instance.
Then let me check the updateability state
of the replicated database in Managed Instance.
It is read only so this DB that I've replicated is read only.
Now all the changes,
all the data that I'm inserting are now being
transported near real time to Managed Instance.
I will be refreshing
this query and you can see the numbers change.
As I in the loop update the data on the SQL Server 2016,
these numbers are automatically being copied to Managed Instance.
That said, that's our short demo.
It really shows us how we can move our data near
real time from SQL Server to
Managed Instance while remaining running on the SQL Server.
>> Thanks Dani. It's super cool to see how that works in action,
how you get this read only endpoint out of all of this
just from doing all that.
I'd love to understand a little bit more.
You mentioned the distributed availability group.
Can you mention a little bit more about how that works?
>> Sure. We're using the always on technology,
and with that stack,
we're using distributed availability group.
What it does, it essentially helps you
connect two availability groups together so we create
one availability group on SQL Server and then
Managed Instance already has its own availability group,
and then we're using Distributed AG to bridge the two.
What is really cool about release is that what are released
is that with Azure Connect feature pack with SQL Server 2016,
we have enabled distributed availability groups functionality
for standard editions as well.
Typically this functionality is only present
in enterprise and developer editions,
but now we have extended the stack to
the standard edition to allow this to be used with 2016.
What is important to note for 2016 is that you
need to just install Windows Server Failover cluster module.
You do not need to configure
your Windows Server Failover cluster and you do not
need to have multiple nodes,
Windows Server Failover cluster.
We just need that module to enable always on on the SQL Server.
Other than that, just
install the Azure Connect feature pack and you're good to go.
>> Awesome. From a networking perspective,
is there anything complicated you have to do with SQL Server
2016 to connect to Azure SQL Managed Instance?
>> It's not that complicated.
We just need some ports to be opened.
We're using database mirroring port 5022,
and that needs to be opened inbound on the SQL Server.
On the other end, of course,
the same port 5022 needs to
be opened inbound for Managed Instance.
Then we also need the port range 11000
to almost 12000 for data communication.
In addition, in order to connect SQL Server and Managed Instance,
you need to have a VPN to Azure.
There are different options,
so either Azure site-to-site
or point-to-site or Azure ExpressRoute,
which is the most performant VPN to the Cloud.
This is to connect SQL Server on-premises
or in data center with Azure.
On the other hand, you could also use SQL Server
2016 in a VM in Azure.
If you're using that,
then you just need to use the Global VNet Peering to connect
the two VNets or
deploy the VM in the same VNet as Managed Instance.
>> Got you. That makes sense.
Then you mentioned,
this can be great for using as
a read-only or disaster recovery situation.
What if I wanted to migrate?
Everything is being replicated,
can I just migrate over?
>> Yeah. You can keep running the link
for weeks and months at a time without migrating to the Cloud,
but then in leveraging the best of the Cloud.
But then when you're ready to migrate to the Cloud,
this solution offers
the best possible minimum downtime migration experience
compared to all our other tools that we have in Azure.
Before migrating, you would of course test your workload.
You would test your data performance in the Cloud.
Then when ready,
always-on allows you to use a Failover,
and Failover is almost instantaneous.
Your database on Managed Instance becomes Read_Write.
You would repoint
your application connection string to Managed Instance.
Then you're up and running solely in the Cloud.
>> Nice. Awesome.
>> Let me talk a bit more about different migration scenarios.
You can consolidate and deconsolidate your workloads in the Cloud.
What I have here shown is the consolidation scenario.
Let's say you have a very large SQL Server
2016 and has been growing for years.
You have lots of data on it.
You just been upgrading the hardware,
and it's super difficult to manage being so large.
Maybe you have a need to have a couple of
Managed Instances geographically dispersed in different regions.
With the link, because the link is database-based,
then you can deconsolidate
and replicate your data to multiple Managed Instances.
On the other hand, if you have multiple SQL Servers,
and maybe you have smaller workloads
and want to consolidate in Managed Instance,
you can also use that as shown with this other picture.
Now, I also have a demo to actually show all these in action.
This is a short demo of how to
perform a minimum downtime migration to
Azure and to show you how fast and how instantaneous this is.
Once again, we're going to use SMS,
going to check the database updateability
on Managed Instance or replicate databases read-only.
The data is still being replicated.
I right-click on the "DB".
I execute "Failover Database",
get into our "Wizard",
select my "Database" again.
I need to sign in to Azure,
in order to be able to perform this action. I click, "Next".
Then we have two types of Failover,
which is Planned and Forced Failover.
Planned is the best possible data option.
Then Forced is if I have a problem.
Then I have an option to delete Availability and
Distributed Availability Groups as a maintenance option.
Then I click on the "Next".
All these steps are being executed for me.
As quick as you've seen it,
I've successfully migrated this database to the Cloud.
Now, let's check the updateability state of this database.
It should be Read_Write. Yes, it is.
Now, my database is fully migrated to Managed Instance.
I can repoint my application connection string
to it and start running in the Cloud solely.
>> That's awesome. I guess it happened so fast.
Does it happen so fast
because all the data's already there so we just
have to do some magic to switch everything over? What happened?
>> Correct. All the data is already there.
It's being transported automatically near real-time.
Any time you have a new commit on SQL Server 2016,
it is updated instantaneously
in near real-time to Managed Instance,
so with all the data being in Managed Instance,
we just do a Failover and essentially transition to MI.
That's why it is instantaneous.
>> Awesome. Cool. Thanks so much, Dani.
I learned so much during this episode.
I see we have this fancy QR code to learn more, I'm guessing.
But do you have any final tips and tricks
for folks just getting started with this?
>> Yes. To get started with this,
you would need SQL Server 2016 Enterprise,
Developer or Standard editions.
You would need to install Service Pack 3,
and then our Azure Connect feature pack,
which enables this wonderful functionality.
You need to run your SQL Server on Windows Server.
You need to have
the Windows Server Failover Cluster module installed,
but you do not need to have multiple node fill or a cluster.
You need to use the latest version of SMS,
which at this point in time is 18.12.
Then you need to have appropriate connectivity.
If you have all these pre-requirements set,
then it's just easy to set up the link.
If you want to learn more,
jump on to the website,
and read more about it. That's it.
>> Awesome. Thanks so much,
Dani. This has been really useful.
I learned a lot about SQL Server 2016,
how I can connect it to Azure SQL Managed Instance using link.
I think a lot of our customers and viewers are going to like this.
If you like this video,
go ahead, give it a like.
Leave us a comment, and let us know,
if you're using it or you have any feedback.
We hope to see you next time on Data Exposed
[MUSIC]