(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)
