>> In a previous video,
you saw how to use Data Migration Assistant
to assess your on-prem data estate.
In this video, you'll see some new capabilities around using
the Command-Line tools and
Azure Migrate to assess multiple instances and more.
This week, on Data Exposed.
[MUSIC].
>> Hi, I'm Anna Hoffman,
and welcome to this episode of Data Exposed.
Today I'm joined by Rajesh Setlem,
Program Manager on the Azure SQL team.
Thanks for being with us today.
>> Thanks Anna. Thanks for having me.
>> Almost a year ago,
Raj and I got on the show and we
talked about Data Migration Assistant.
We did all of this using a AGUI.
But now, there are new capabilities - is that what's going on?
>> Yeah. That's right, Anna. It's been a year since
the last video was published.
This was about running Data Migration Assistant through the UI,
integrating those capabilities into Azure Migrate,
and then there's been a lot of requests from customers
where they wanted to do work through Command-Line arguments,
and that's what we want to talk about in this video.
>> Awesome. Cool. I know a lot of customers feel more
comfortable with Command-Line and maybe they
feel like they can do more with Command-Line.
I'd love to just get right into it and see what it can do.
>> Exactly. Okay, let me go ahead and start sharing my screen
and then show you the capabilities in action. Sure, thanks Anna.
I just want to give
a quick introduction of the tool that we are talking about,
and this is Data Migration Assistant.
Also referred to as DMA,
it's a popular tool that customers
use when they want to migrate to Azure.
They want to assess their on-premise instance,
see if it is ready for a target Azure SQL instance,
and then identify if there are any migration issues,
migration blockers, any behavioral changes,
any breaking changes and go from there.
Here's the link that I was talking about,
where in the past what we had done is,
we had released this capability where you can
assess the data estate using Data Migration Assistant,
using AGUI, and then upload the results to Azure Migrate.
As you can see, a couple of
prerequisite steps that we have to do here is,
you create a project,
you add the Data Migration Assistant as a tool,
and then you assess
the SQL server instance and
then upload the results back into Azure Migrate.
Once you create the assessment,
you would have a option to say "Upload to Azure Migrate",
the box right here, and then you would start seeing
the assessment results in the Azure portal.
What we're going to do today now is,
do all of these capabilities using GUI.
Because as we can anticipate,
if someone wants to access a data estate,
you wouldn't have a couple of a handful instances.
You would have tens of
SQL Server instances or even more than that.
The goal here is to,
how do you automate all of that into a Command Line,
and then scale to your data estate needs.
So Data Migration Assistant comes with
a Command-Line tool called DmaCmd.exe.
We're going to look at
the parameters that are exposed for DmaCmd.exe,
and how do we leverage those capabilities.
If I go into my DMA install,
I have this Command-Line interface called DmaCmd.exe.
I can do a quick help on it.
It will give you the parameters
and the actions that are supported by it.
As you can see, it's a pretty rich tool.
All the capabilities that you can do from the UI
can very well be done using the Command-Line options.
For the purpose of today's call, what we will do is,
let me go into a folder that I created for the demo,
and then I'll show you how do you run this command
using the "CLI" command.
What I'm trying to do here is,
I'm using the DmaCmd.exe,
I'm passing in an argument called "assess",
and then I'm passing in an assessment configuration file.
Essentially, all the things that you would have done in
the UI is actually bundled up into the XML file.
Let's take a quick look at what the XML looks like.
Then I also wanted to call out that we
will get this published into
a doc link and then have linked up into
the video so that you don't have to manually type any of this,
can just copy the sample from the doc page.
As you can see,
you start with an assessment name.
That's what I want to give a name to the assessment.
I want to assess my source platform,
which is SQL On-prem.
In the target platform,
in this case, for the purpose of this demo I want to showcase,
let's say I want access for Managed SQL server,
which is Managed SQL Instance.
Had it been in SQL DB,
you would say Azure SQL database and we would have all of
that information in the doc page on
what are the target platform which is supported.
Then here are a couple of examples where I'm showing,
in this case I have two instances,
a SQL 2016 instance,
and then a SQL 2017.
What I really quickly want to show you is,
in the first instance I want to assess all the databases in it.
While on the second instance,
I just want to pick and choose two databases.
This is what happened and this is my laptop,
so I don't have tens of SQL server instances,
but essentially have two instances,
a bunch of databases on first one, on the second one,
I just want to pick and choose the two databases.
Then once you specified the databases,
then we have the option to include all the results
where the command will output the results.
There are couple of options, essentially three options.
One is, you can output the result into a .dmf file,
you can open up in the program called DMA GUI.
The other one is you can open it up in a JSON file,
and then I can also save it into a CSV file,
which I'll show you once we're on the command.
Then if you want to, whatever the result,
is set at at true, and essentially,
and this is all the capabilities exposed by DMA.
You can do compatibility issues
or you can do feature parity issues.
Then Cloud environment, if we
wanted to do public clouds, you do Azure,
and the advantage with the capability with upload to
Azure Migrate is we also support
GovClouds and also Sovereign Clouds like China and Germany.
Then you specify the subscription ID,
the Azure Migrate, project name, resource group name.
You ask for the authentication ID, authentication pop-up shop,
and then the Azure authentication tenant ID,
which is essentially an Azure Active Directory tenant ID.
The way you'll get there is,
to get the authentication ID,
if you go to Azure portal
and then click on "Azure Active Directory",
you would see the tenant ID information
right there in the overview page.
As you can see, this is the tenant ID information that shows up.
You copy those information,
and then the last important element to this XML is,
you want to set the "Enable assessment,
upload Azure Migrate" tool and then you're good to go.
Let's go ahead and run this command.
I'll wait for the pop-up to show up.
>> While this is running Rajesh,
so what we're doing is we're actually doing the assessment,
but first you have to authenticate and then
we can start the assessment or why do you have to authenticate?
>> That's a good question, Hannah. So the reason
why we have to authenticate is since we said,
we want to upload the results to Azure Migrate,
we just want to make sure are able to
authenticate into Azure at all in the first place,
and then we would start the assessment of the databases,
and that's the reason why we first authenticate
and then the assessment kicks off in the background,
while it starts going after
all the databases that you specified in the XML file.
>> Got you. That makes sense because in
the GUI you do it step by step by step,
but here you get to do at all once.
>> Exactly. Authentication is something that we want to capture
that information right before we do the assessments so
that you get authenticated and then once the results are ready,
we upload it back to Azure Migrate.
So I'm just waiting for the pop-up to show up,
for the multi-factor authentication to go ahead.
While this is going on, this will take couple of minutes.
Since I had close to 20 all databases in both instances,
it will take few minutes.
What I would show you is how
the output would look like once the command succeeds,
once the command succeeds,
what you would see is you would see the number of
databases that were assessed
the number of instances that were assessed,
and then also specific information
around whether the upload
to Azure Migrate actually completed or not.
So here, as you can see,
the uploading started and then the uploading completed,
and then a quick statistics of
all the instances and databases that were uploaded.
Let's go back into Azure Migrate,
if you're not familiar with Azure Migrate,
so this is a one-stop solution for all your migration needs,
and it supports both servers
and databases and web applications as well.
So what we're going to do here is,
if you navigate into Azure
migrate and then click on "Assess and Databases",
you would see an option to add your assessment tool,
and then you would land up into the speech.
Once the upload completes,
you would see data for those instances here.
As you can see, we had
assessed two instances and that's what it shows up.
Let's get into those instances and get
a high-level view of what is happening to those instances.
How ready are those instances to migrate to Azure SQL?
As you can see, in 2016 instance,
there's this 90 percent ready that are 20 databases.
I know for a fact that a couple of databases which are
not ready and that's why I chose 90 percent ready.
But for the other one, it's 100 percent ready,
and if you also want to get into the next level of details,
we also show those level of details once you double-click into it.
As we said, we wanted to target SQL Managed Instance,
this is the target in Azure where we want us as
four and then as you can see, we have a column.
It says, is it ready for SQL DB or is it ready for SQL VM.
As we can see, we saw that in
the earlier screen that there were two databases
where they were not ready for SQL Managed Instance,
and these are the two databases
where we have some migration blockers.
It's not ready for managed instance,
but it is completely ready for SQL VM.
So you get that holistic view of what's happening
at instance level and you can do this for the entire data estate.
If I can get quickly, go back into the other instance,
everything looks good here.
I want to go to assess for target
MI and then there are no blockers.
I can easily migrate it to SQL managed instance.
So the next obvious question that customers would have is,
once I get this view here,
and this helps at a head of
a database group who wants to know what is the readiness state off
his or her data with
SQL server instances for
all the application that the team supports.
Once you have this details,
you would want to get into,
double-click into what are
the actual migration blockers for those SQL server databases.
As I said, once the command runs,
it outputs the data into three formats.
So this is one of the format which is a.DMA file.
You can open it up in DMA.
As you can see in the archive database,
you have couple of migration blockers,
one is a FileStream file group,
which is not supported yet in MI and then the other one have
a table with a FileStream column
and that's why it's flagging it out.
Similarly for WideWorldImporters, I manually
created a migration blocker by creating
an additional lock file which is not supported in MI.
As you can see, you get the richness
of detailed view of
what are the migration blockers at a database level,
but you also get
the overall readiness status of the entire data estate.
>> Awesome, thanks Rajesh. That was really cool.
What I really liked is how you were able to put in a bunch of
servers and analyze a lot of databases and servers at one time.
I don't think that's something you can easily
do with the GUI today. Is that right?
>> It can be done in the GUI,
but then it's very painful process where you have
to connect every instance add a bunch of databases,
select or deselect the data versus
that you want to go through and it's not scalable
once you get into tens and probably
start getting into 30 or 40 or
even 50 or a 100 instances of SQL Server,
it becomes even more difficult to do all of that in the GUI.
>> Yeah, definitely. You've shown us a lot,
and I think a lot of people are going to
want to go get their hand on this.
So we'll definitely put resources in the description.
But if you had to give our viewers a few tips,
what advice would you give?
>> That's a good question Hannah. So what
we definitely recommend is,
we don't want customers to provide all the SQL Server instances,
if they have a hundreds of SQL Server instances,
we don't want them to provide all those instances in
one assessment configuration file and then
start assessing because that wouldn't scale.
Typically, what the recommendation is scope
to an application a bunch of sequence of instances,
maybe tens of SQL Server instances that
works, and then go from there.
The idea is to batch up those assessments into
finite sets and then
probably that does input in XML
file and then start the assessments.
That's one recommendation, the other recommendation is,
as you can see in the demo, what we did was
we assessed for SQL Managed Instance,
but also customers or scenarios where you would want
your sole SQL Server instance to be assessed
for SQL Managed Instance and SQL DB as well.
So you would want to know what is the readiness status
for both the target platforms in Azure.
In those cases, the recommendation
is create a separate Azure Migrate project
for MI and one for DB so that you don't override those results.
The last piece of advice, we just again,
based on performance is,
when assessments run, it is CPU
bound because it connects to a source instance,
connects to a source database and then tries to extract
a dackpack of the database and then does all the validations,
trying to figure out what are the features,
blockers and all sorts of things.
So which is CPU bound, essentially,
the recommendation is, run
the scripts from a machine where there's enough number,
of course, probably eight vCore is a good start,
and then probably you might have to scale it up based on
the number of instances or the databases that we have.
Those are the basic recommendations
for running assessments at scale.
>> Okay. Those are some good tips.
If I understand correctly,
your first tip is,
even though I have the power now to
analyze hundreds of SQL Servers at once,
due to many reasons,
you said lets scope it to an application,
and that makes it easier,
maybe even in your migration project then you
can see for this application,
when is it ready to go.
Similarly, if you're between MI&DB,
if you create different projects because
otherwise they're going to overwrite themselves.
>> Yep, that's right.
>> Then the third one, maybe relates to the first one too,
in that, this does take CPU,
so either you need to not assess so many,
or make sure you're on a machine
that can assess as many as you need.
>> yep, and it is proportional to the number of database objects.
So if you have a lot schema,
it does nothing to do with the database size,
its directly proportional to the schema size of the database,
depending on the number of schema objects,
the assessment time is also proportional to that,
and then you need to have enough CPU capacity
to handle all of that.
>> Got you, awesome.
Well, thanks for joining us today, Rajesh.
I personally learned a lot.
I think our viewers have to,
we're going to include lots of
resources in the description for you to go learn
more and we'd love to hear from you to
see what your experience is working with this,
and we hope to see you next time on Data Exposed.[MUSIC]