(parts whirling)
- Welcome to Microsoft Mechanics.
Coming up with your running SQL Server on premises
and looking to move to Azure,
we're going to review your best options
for assessment and migration.
So today I'm joined by database expert
and engineering lead Bob Ward,
welcome to Microsoft Mechanics.
- Thanks Jeremy, great to be here.
- The SQL Server is one of the most popular
relational databases in use on-premises today.
Now what are you seeing as some of the motivation right now
to move to Azure?
- Yeah Jeremy, it's just the next logical step,
whether you need to increase your capacity,
take advantage of built-in high availability
and disaster recovery
or reduce your operational costs.
You know in fact you can bring your existing SQL licenses
to the Cloud with Azure Hybrid Benefit.
- Right, and with that benefit,
you can actually bring your on-premises licenses into Azure
to save on subscription costs as well so,
what are some of the recommended approaches then
if you want to actually bring your SQL databases into Azure?
- You know, your options fall into three main categories
which really depends on what you're trying to do.
On the one hand, you have the option to lift and shift
your SQL Servers to Azure VMs
instead of your own infrastructure.
Now here, we manage the infrastructure,
you manage the OS and the SQL instance
which of course could contain many databases.
It's a really great option if you need to
migrate your databases fast or have specific dependencies
you need to maintain such as distributed transactions
or running SQL Server Databases on LINUX.
- Right, we recently did a step-by-step show
with Jeff Woolsey on the topic of server migration
for the VMs themselves,
coming into Azure which you can check out
at aka.ms/MechanicsAzureMigrate.
With this option, you still have to take care
of managing the infrastructure,
kind of as you move your SQL Server into Azure.
- You know Jeremy, that's right
but you get a lot of benefits
from hosting SQL Server databases in Azure VMs.
You know for example,
you get automated updates and backups
and you could take advantage of Azure storage
which gives you higher availability and performance.
Now, the next option if your application relies
on SQL instance-level capabilities
like cross database queries and SQL CLR,
is to migrate to Azure SQL Managed Instance,
which is a fully-managed SQL Server.
And the good news is, with Azure SQL MI,
there's almost no code changes
so you have almost near a 100% compatibility.
Now here, you manage the instances
while we'll manage the infrastructure
and the operating system.
And the final option is Azure SQL Database
which is the best for modern database applications
and give you maximum PaaS-managed capabilities
including Serverless for autoscale
and Hyperscale for your most demanding, critical workloads.
And here, we're going to manage the infrastructure,
the operating system and the instance
while you focus on the database.
- Okay and this really isn't an either/or decision as well.
So for example,
you might want to shift and lift your databases into Azure
purely for expediency
but you might also decide later to move
into Managed Instances or PaaS Databases.
- We've seen a lot of customers do this, for example,
H&R Block is a good customer example.
Once the physics of moving your database to Azure is done
it can be easier at that point to reassess your requirements
and potentially go down that managed database path.
- So what do you need to do then
to migrate to a Managed Instance or to Azure SQL database?
- Well, there are two main tools you can find
from Azure Migrate.
The first is Data Migration Assistant or DMA.
This helps you migrate by detecting compatibility issues
that can impact database functionality in your new version
of SQL Server or Azure SQL.
And it helps you right-size your deployment
for the appropriate Azure service tier.
The second is the Data Migration Service itself, or DMS.
Now this tool is going to give you a holistic view
of your data state after your initial assessment
and is used for live migration for zero downtime.
- All right, but let's make all of this real.
So can you show us how you might perform
a migration end-to-end?
- Sure, definitely let's do that.
I have here a Parts Unlimited application
that I want to migrate.
It's currently running on Windows Server.
It's also accessing SQL Server database,
which is used to manage my orders for all my parts.
So in fact, I can hop over
to the popular tool Management Studio
and you'll see here, my Parts Unlimited database
on my on-prem SQL 2017 machine
that we want to migrate to Azure.
And to do that,
I'll need to determine if it's ready for migration
and make a decision on what path to take.
So first I'm going to use the DMA tool
to assess my database.
Now this tool can be downloaded
from the Microsoft Download Center for free,
at aka.ms/DMAdownload.
But here I've already got my tool downloaded
and installed on my desktop.
So I'm going to open DMA and create a new assessment.
So I'll need to kind of give it a project name
so I can save it for later.
I'll choose the assessment type,
which in this case is Database Engine.
I'll choose the source server type,
which we can choose from SQL Server or AWS
and the target server type,
In this case,
I can choose from all the options I mentioned earlier,
but in this case, I'll select Managed Instance.
Here, I'll click create.
And this is going to go inspect the SQL Server instance
and database for Azure readiness.
We're looking for things like feature parity,
compatibility level, and breaking changes.
Now to run this, it's going to take a few moments,
but here is a report that I've already completed,
that includes all my databases for my instance.
I'll click on SQL Server feature parity,
and this can tell me what I need to know
at the instance level, including any migration blockers.
Then I'll click into compatibility issues
and I can see tabs for all possible compatibility levels
for all databases.
And here are two breaking changes for the HR database
that may not block my migration,
but I should address these post migration.
- Okay, so how did people think about the
compatibility tabs that we just saw?
- Jeremy, a compatibly level is very important.
It's associated with each database.
It allows your application to rely on the behavior
of a version of SQL Server
associated with that compat level.
So when you migrate, we maintain that level.
Now you can learn more about DB compat
@aka.ms/dbcompat.
- All right, so now you've got your DMA report.
It's great if you have a single database
that you might want to migrate,
but what if you have quite a few different databases?
What's the best way then to look at the assessments
and kind of do all of that across the larger estate?
- Sure.
Now I can upload the database assessments
to the Azure Migrate service
using the upload to Azure Migrate button
for a consolidated view of the results.
And by the way, if you have a large number of databases,
you can use PowerShell for that.
And you can go to aka.ms/DMAPowerShell to learn more.
Let me show you how this will look like in Azure Migrate.
Here you can see a summary view of databases assessed
from my on-premises environment.
I can drill into my assessed database instances
and see the readiness status
of the instance of my SQL Server we just assessed,
which is around 88.9% ready.
Now I'll drill in further to see the databases
and any possible blockers or breaking changes.
Here I can see my databases, the size,
any blocker that prevents migration to Azure SQL MI
or breaking changes.
In my case, I just want to move the Parts Unlimited DB,
which has no blockers or breaking changes,
and it's ready to migrate to Manage Instance.
And I'll retain the 140 compat level,
of my database after migration.
This is great, because I don't need to worry about
any schema or application changes,
except for a connection string as I migrate to Azure SQL MI.
- Okay, so, but before we do that,
how do I figure out the right compute and storage level
for my database in Azure?
- Sure.
So if you aren't sure what size instance
you want to provision to,
that's where DMA comes in again.
Now it has two steps to do this.
It's going to collect performance data,
and then analyze the data for the optimal target size.
Here I need to navigate to my DMA folder
and we have a PowerShell script
that can be used to collect the performance data.
Now I've run this script already and you can see here,
that it recommends Compute and the estimated cost per month.
And if I apply my SQL Server license,
which we call Azure Hybrid Benefit,
it will save me up to 55%,
and in my case it's around 40%.
Now if you provide the Azure subscription, region,
and related details you see here in these fields,
you'll be able to generate a provisioning script
with this button.
And you'll use this to provision this Manage Instance,
so it's ready to receive your databases.
- Okay, so you create the managed instance.
Now what's the next step?
- Well, now that I've provisioned my managed instance
you can see it's picked the appropriate pricing tier
from what I used in my assessment.
I want to go into the Database Migration Service or DMS,
and I'll create a new project to migrate my SQL Database.
Now here it needs a name.
Like before I'll pick a source type of SQL Server.
My target type, which again is Manage Instance.
I'll choose a type of activity,
and in this case, I want to choose an online data migration.
Now I can just hit save, then hit create and run.
This is going to launch a wizard
to configure everything needed to start replicating
our server data into Azure.
Now, first on the source side,
I need to provide a source SQL Server instance name.
Now I'm going to use authentication type,
I'll use SQL Authentication,
then put in a username with sufficient privileges
and a password.
And in the connection properties,
I'll keep the encryption settings
and check trust server certificate,
and then I'll hit save.
Now on the target side, I need to enter an app ID,
which is a service principal used by DMS
to access resources.
I'll need a key with the app ID.
I'll choose my subscription.
Then importantly, select the Azure SQL Managed Instance
we generated earlier.
I'll put in my username and password and hit save.
Now with select databases
I can migrate the entire instance,
but I just need to choose my own database
for Parts Unlimited within the instance
to migrate in my case.
In Migration settings, I'll add the network share location,
where the backups are stored.
- Okay, I see.
So you're actually uploading those backups
from a local network share into Azure Storage?
- Right, now let me provide a Windows account and password
that DMS can use to access that network share.
I'll need to pick my Azure Storage account
and I'll expand advanced settings.
In here, I'm going to change the database name,
so I can kind of do that right here,
then click save.
And I'm going to add an activity name
in case I want several of these running.
This can be anything I can choose as a name.
And at this point I can run the migration.
- Right, this is probably going to take a while,
depending on the size of the database
and the bandwidth and kind of all the physics involved,
right?
- Yeah, exactly.
But here's, what's important.
My app and database are still online during all this.
That's kind of why we call it online live migration.
Now to save time, we already have a migration running,
which you can see here in DMS.
So I'll click into Parts Unlimited.
We'll see, it's now running.
And I can see a sequence of log backups
that are all restored.
And this process runs continually,
until you're ready to do that cut-over.
Now when you're ready to do the migration cut-over,
make sure you stop incoming traffic to your database.
Then, you need to perform a final log backup,
called a tail-log backup in Management Studio,
kinda like we see here.
Then moving back to DMS,
we'll see after I hit refresh,
it's already uploaded the backup file that I just did
and it's already been restored.
Now I can do the migration cutover.
I'll need to confirm, I'll hit apply
and once it completes the database is running in Azure.
- All right, so now you've shut down
your production service,
performed your final kind of delta backup.
It's ready then to take on the production workload,
but how does the app then know how to
and where to talk to the database
now that it's running in Azure?
- Jeremy, it's one of the great stories
about this migration.
So once you've migrated your app
and one that uses this underlying database,
using the App Service Migration Assistant,
you just have to change the connection string
to redirect it to the database,
in Managed Instance in Azure.
So you can go into edit and search,
open up the App Service Editor here,
redirect the connection string of the app
to the new Managed Instance name and boom.
Now you can see my app is running in the Cloud
and connected to Managed Instance.
- So in this case, you actually chose to migrate
your SQL Database into Azure SQL Managed Instance.
But what would that process look like
if you had chosen to migrate to Azure SQL Database?
- Jeremy great question.
The workload is a little different,
but you still get to use the same DMS service
migrating SQL Server databases to Azure SQL DB.
Now additionally,
you'll first need to apply database schemas
before you do the migration.
But in fact, we documented this whole process here
at aka.ms/AzureSqlMigrate.
- So once you're in Azure,
you can also do lots more
in terms of performance and security.
So for example, in Azure Security Center,
you can monitor the health and security posture
of your databases,
whether they're in VMs or Managed Instances or PaaS.
And this also includes mitigating security threats
with Advanced Threat Protection services.
- [Bob] As you go to Azure SQL DB as a native PaaS service,
one of the unique things is
how you can visualize your query performance
and tune your indexing.
Here you can drill in
and get recommendations for performance.
You know we use machine learning behind the scenes
to look at your indexes
and determine the right optimizations for performance.
We even have options even to automate this entire process.
- Thanks so much for joining us today, Bob,
and really going through
all of our different migration options
for moving your SQL Databases into the cloud.
Now if you'd like to learn more,
you can also download our Azure Database Migration guide
at datamigration.microsoft.com.
And of course you can check out the rest
of our Azure Migration series
at aka.ms/MechanicsAzureMigrate.
Thanks for watching,
we'll see you next time.
(upbeat music)