>> The Azure SQL Migration Extension and
Azure Data Studio recently became generally available.
Learn all about how you can use it to
migrate SQL Server to Azure with
our Data Studio monitor in
the Azure portal and even automate all of it with scripting.
This week on Data Exposed.
[MUSIC]
>> Hi, I'm Anna Hoffman and welcome to
this episode of Data Exposed.
Today, I'm joined by Mohammed,
a program manager on the Azure SQL on SQL Server team.
Mohammed, thanks so much for joining us today.
>> Thanks for having me again Anna.
It's a pleasure to be on your show always.
>> It's great to have you back on the show.
It's been a while, but we have
some exciting news we wanted to bring you on.
For recently, there was a general availability announcement.
Can you tell us a little bit more about it?
>> Yeah sure. I'm super excited to talk about this.
We went to market and announced public preview for
Azure SQL migration extension on Azure Data Studio
last year. We came on your show and talked about it.
We are happy to announce that it is now
generally available for customers to
migrate their databases to
Azure SQL Managed Instance to
a SQL Server on Azure Virtual Machines.
All the capabilities that we've enlisted in
the recent months from assessment,
to getting recommendations, to migrations,
all of that is generally available and customers
can use it to do production migrations today.
>> Awesome, cool. I've been following along this a little and
it's been exciting to see it grow and improve over time.
But for those that might not have been following it,
can you tell us a little bit about
the Azure SQL Migration Extension.
How it works and how you might use it to migrate to Azure?
>> Yeah. Why don't I share a slide on and talk about
the capabilities that are part of the product?
As you see in my slide here,
basically the Azure SQL Migration Extension is an extension within
Azure Data Studio that you install from
the extension's gallery. Once you set that up,
you can pretty much start in doing
your end to end migration journey for your SQL Sever Databases.
We have capabilities to run
database assessments where you can go and assess
your sequence databases for readiness to
Azure SQL Managed Instances to
Azure SQL running on virtual machines.
You can also get recommendations for the right
sized SQL MI or SQL Server on Azure Virtual Machine.
This is again a recent capability
that we introduced in the product,
which will go and assess your performance
of your database workloads and give you
the right recommendations to meet
both your performance and your price requirements.
Lastly, the main purpose of
this product was to help you migrate databases. We
built in capabilities to do
minimal downtime migrations from your on-premises to Azure.
These are some of the core capabilities
that this product provides.
Just to dive in a little bit into what
the SKU recommendation or the right
size Azure recommendations does,
is it basically goes and collects
your performance data from your on-premises SQL Server,
it stores that performance data in
your local machine where you're running Azure Data Studio and
then we have a SKU document that goes
and analyzes the data to give you the right recommendations.
This recommendation will give you both
the best SKU that will meet the performance needs,
but also with the least minimal cost.
When something is suitable for Azure and we've assessed
your performance and we figured out that there are
multiple SKUs that can meet your performance,
we choose the least expensive option
to give you the recommendation.
Again, you can go and deploy that managed instance in SQL Server
or on virtual machine and use that for your migration target.
Just to recap what the product
does, end to end is basically migrating
a databases from on-premises
to a SQL Server on Azure Virtual Machines.
But you can also use this to migrate your databases from
SQL Server on Azure Virtual Machines running in
IS to SQL Managed Instance as well.
The way it does it is basically when you
have the extension running on your Azure Data Studio,
which is connecting to your source SQL Servers,
we provision a database migration service
through the virtual experience.
Again, this database migration service is
the powerhouse of that whole migration experience.
It orchestrates all the migration activities and again,
this is a shell service that gets deployed in
your Azure subscription at no cost to you.
This is a free service that's
available as part of this whole experience.
Once the database migration service
is provisioned in your subscription,
it leverages the self-hosted integration runtime,
which is an integration runtime that you
install in your on-premises environment. Which will
communicate to both your on-premises environment
and database migration service to move backups to Azure.
Once all of this is set up and
configured through the virtual and again,
this is a very seamless,
easy-to-use wizard and this whole experience
is guided through the wizard,
you can start migrating
a databases to Azure SQL Managed Instance or
a SQL Server on Azure Virtual Machines
today that is generally available.
>> Awesome, cool. One question before we
get into it, because I would love to
see this demo that I know you've prepared.
Is if you can just getting started with this,
which I think is maybe where we're going to start,
but do you need an Azure account?
>> That's a very good point. Why don't
I jump into the demo to show you that?
Once you open up Azure Data Studio and you
launch to see Azure SQL Migration Extension,
the first step is we connect
your source instance and
access all your databases. At this point,
we do not need your Azure account.
Once you choose all your databases that you want to
access and you click on "Next," it runs
a quick assessment to tell you whether these databases are
suitable to go to SQL Managed Instance
or SQL Server on Azure Virtual Machines.
You can get these recommendations and
assessment results without having
an Azure account and that's the whole purpose of it.
When you're getting started we don't want you to have
an Azure account to just be able to
do an assessment and get recommendations.
You can see here that out of
these eight databases that are on my instance,
seven are readily available to be migrated to SQL Managed Instance.
Pretty much all of them are available
for migration to SQL Server on Azure Virtual Machines.
The most recent update we've done here
is we've introduced Get Azure Recommendations as an option.
When you do this, you get a blade showcasing two options.
The first option is you can start
the performance data collection right through this wizard.
You can provide a folder location where
all the data that we collect from your SQL Server performance
gets saved on that folder and
that folder's output is what is
used to analyze the performance recommendations.
But if you've already collected this data either through
this experience or through our other tools like DMA in the past,
you can import that data as well.
I have already run performance data
collection through this research previously,
and I'm going to just import that from the folder that I have.
It's in my folder here and I'm going to click on "Start."
As you can see,
it instantly shows me a recommendation right here to tell me that
this set of databases are
ready to be migrated to a SQL Managed Instance,
Gen5 General Purpose with 4 vCores and 32 gigs of storage.
Similarly, for SQL Server on Virtual Machines,
it gives me a recommendation of an E2ads_v5 cVM with 2vCPUs.
The best part about this recommendation is that you
can click on "View Details" and you
can see descriptive information on
the recommendation reason on why you recommended
this based on the source properties that you've collected.
Also, it gives you an indicator of storage.
Now storage is important especially for SQL Server on
Azure Virtual Machines. Because when you look at
the recommendations for SQL Server on Azure Virtual Machines,
we give you the disk storage recommendations as well.
We tell you that your 10 VB needs to go to an ephemeral SSD
drive and your SQL data files can go to
a separate drive and you log files [inaudible]
drive. We give you recommendations for
each of those drives as well based on
the performance data that we've collected for your workload.
>> This is one of the new capabilities that we introduced.
Once you have your recommendations,
you can select the databases and start your migration.
>> Awesome. This is pretty cool.
One quick question.
I know you ran the performance analysis in advance,
but how long should customers run this in
the background or at once to get good recommendations?
>> That's a great question. In fact,
one that we set
as default as part of this experience is for 10 minutes,
you will have to wait for the first set
of recommendations to show up,
and that's the default experience.
But you don't have to just stop there.
If you have a longer running workload that runs for
several hours or even for a longer duration than 10 minutes,
you can continue to collect performance data through this wizard.
When you start the performance data collection
within the first 10 minutes, you get a recommendation.
But the performance data collection continues on either
until you start the migration
or you manually stop the data collection.
There's no standard rules
around this because we know every workload is different
and you might have different times and different
peak periods where you want to get your recommendations,
but by default, it shows you
the first set of recommendations within 10 minutes.
>> Awesome.
>> Let me continue on. When you
start the migration using Azure Data Studio,
you can monitor it using Azure Data Studio as well.
You can see here, I have one database migration progress,
which is what I started before this show.
I can show you that this database
is currently being migrated to Azure SQL Managed Instance.
You can see the status of
my backups that are being restored on managed instances as well.
You get a detailed view of your migration progress.
The last step in this whole migration process is obviously
performing a cutover because this is an online migration.
At some point, you need your application
to switch over the target
SQL Managed Instance at which you can
complete the cutover process. At that point,
it becomes readily available on your managed instance.
>> Got you. You can do the whole thing in Azure Data Studio.
Now, I'm assuming if you're moving to Azure,
you probably want to look at this
somehow in the Azure Portal as well.
Is there any integrations in that space?
>> Yes. You've also integrated
the same experience on the Azure Portal.
If you go to the Azure Portal and you search for your service,
you can see that experience is available here as well.
You can see the migrations that are currently in progress.
This is a same migration that I started in Azure Data Studio.
You can click on the Migration here.
You can see the same statuses.
You can perform a cutover operation,
you can delete, you can retry.
Basically, all the options that are available in
Azure Data Studio are all also available in the portal.
The migration experience in both these are quite seamless.
Except that you always have to
start the migration in Azure Data Studio,
but you can come back and monitor and do
all these operations in the portal.
>> Got you. Cool.
>> Another point that I also wanted to bring out
is as part of this experience,
we just didn't stop at Data Studio Portal,
but we also wanted to help customers migrate at scale.
Especially if they have a large number of instances or
a large number of databases because it can get tricky with the UI.
For that reason, we introduced support for PowerShell and CLI.
With CLI as an example,
here you can run end to end
migrations at scale across multiple instances,
across subscriptions, and you can fully automate this process.
As a simple example,
let me show you with
three commands how you can perform a migration.
The first step is creating a database migration service,
and I'm creating one for this demo
called data exposed migration service.
You can see how quickly I can run this whole migration.
I'm going to create the database migration service.
This is the best part of having
the portal integration that when you
execute these commands in CLI,
I can jump back to the portal and I can see if
my migration service is being created and showing up here as well.
Let me see. Give it a couple of seconds for this to complete.
Now that it's complete,
I can switch over to the portal.
I can see my migration service is created.
This is the one that we just created using CLI.
Now, the second step is obviously running the database migration,
providing their source details and your target details.
I'm migrating this to SQL Managed Instance and I'm
renaming the target database to data exposed demo DB.
Let me start the migration here. Give
it a few seconds for it to initialize and create the migration.
Again, I could monitor
the same experience using the portal again.
If I go back to the portal,
click on "Monitor Migrations" here.
That should also bring up
the same migration that I've just started.
Give it a few seconds here and that appears.
This is a migration that we just started from the CLI experience,
and you can see it's only been three seconds since we started.
You can click on it and obviously,
you can track this status.
It's currently in progress.
As the backups start coming in,
you can see the backups being restored as well.
The last step is obviously because this is an online migration,
you can also perform a cutaway using CLI.
All of these commands are baked into PowerShell and CLI
modules that you can leverage
and automate your end-to-end experience.
We've also documented
this whole automation experience. More importantly,
we provided end-to-end scenario samples.
If you are migrating to VM versus
MI and if you want to just run an assessment.
All these scenarios are documented
using our Azure samples GitHub repo.
We've introduced our GitHub repo called data migration SQL in
Azure samples where you can see
both CLI and PowerShell scenario samples,
you can go and use and try these in your own environment.
>> Awesome, cool. Then this has been great. I've learned a lot.
I have one more question
because you said this PowerShell thing could be used
at scale and obviously it's saving a lot
of clicks as we go through the different blades,
especially if we already know what we're going to do.
Could I run one command to
migrate multiple databases at this point?
>> Yeah. Basically,
if you look at some of these scripts here,
we've documented the steps on how you could run
multiple migrations or multiple databases using a single script.
Obviously, there'll be four loops.
There'll be iterative commands to do that in a loop.
But pretty much using
the same command you can run multiple migrations.
>> Awesome, cool. Well, Mohamed, this is really exciting.
It's really exciting to have you on and
analysis GA of Azure SQL Migration Extension,
which includes migrating in Azure Data Studio,
seeing the results and monitoring the portal,
and having the same command studio at
scale with PowerShell and CLI.
Are there any final tips and tricks
you have for users as they get started with this?
>> Yeah. We've had
several customers using this in their own environment and we've
had such positive feedback and we can't
wait for customers to try this in their own environments.
But more importantly, we are constantly listening.
The fact that between public preview and
now we've enhanced all these capabilities is listening
to our customers and our partners and getting
that feedback so feel free to reach out to us
either through a documentation request
or through various Microsoft channels that you communicate with.
We are constantly listening for all these channels for feedback.
Make sure you reach out to us and
we'll constantly invest in the service.
>> Awesome. Well, thanks so much Mohamed for coming on the show.
For our viewers, thanks for tuning in.
Check out the description for links on how to get started,
as well as links to the GitHub page and
other resources you might find handy.
If you like this episode, go ahead give it a like.
Leave us a comment and let us know
how many databases you plan on migrating.
We hope to see you next time on Data Exposed.
[MUSIC]