>> The latest release of SSMA includes a ton of new features.
In this episode, we'll dive into the new conversion reports,
this week on Data Exposed.
[MUSIC]
>> Hi, I'm Anna Hoffman and
welcome to this episode of Data Exposed.
Today, we're continuing our migrating to
SQL series and we have two very special guest joining us today.
The first is Alexandra and she's been on the show before,
but Alexandra, thanks so much for joining us.
Can you tell us a little bit what you do?
>> Hi, Anna. Hi, everybody. Happy to be back.
I'm Alexandra and I'm a Program Manager in
the amazing team looking over heterogeneous migration.
The focus for us is to build the right experience and tool
for customers looking to migrate from DB2,
Sybase, MySQL, Access,
and of course, Oracle to Azure SQL and SQL Server.
>> Awesome. We are excited to
have you on the show and I think you're
exactly the right person to be talking about
SSMA which we'll get into in a moment.
We also have Steven joining us
today for the first time on Data Exposed.
Steven, thanks for joining us.
Can you tell us a little bit about what you do?
>> Sure. Thank you for having me.
My name is Steven and I'm a Software Engineer here at
Microsoft and of the many great tools
that Microsoft provides for database migration,
I work on something called SSMA,
which has been discussed on the show before.
Recently, I've been working on modernizing
our reporting experience and
that's what I hope to show to you all today.
>> Awesome, great. I think we have two great guests today.
Let's get right into it.
I wanted to start by talking about SSMA
reports and asking you Steven why are these reports important?
>> Sure. Typically,
when you have a database working really well on a source like say
MySQL or Oracle and you're looking to
migrate to a Cloud say SQL Server.
You want to make sure that everything that
used to be working is going to
continue working the way you expect when you're now on SQL Server.
One of the first steps our customer typically
takes is they run an assessment report which should give
a detailed breakdown of what SSMA
will automatically convert and
what will require some manual intervention.
Someone will have to go in and assist in the migration process.
That's why it's important.
It's typically the first step and it provides a big oversight.
>> Awesome, cool. Yeah, it sounds like
a very important piece if you're going to do this migration to
understand like how the conversion is going to
go and what you might need to address.
Alexandra, this has been around for a long time.
I'd love to get your take on what existed or used to exist
and some of the challenges that lead
towards some enhancements we're going to talk about.
>> Well, definitely.
In order to do that, we are going to take
you a little bit in the past.
This is already a familiar interface for people watching our show.
We made a little bit of context switching.
Instead of HR database,
we are looking at MySQL that secular.
We don't want to be boring for you guys so
we thought about changing that.
How do we get to reports from here? That's pretty simple.
You either have this amazing button,
on the top of the screen or as always,
right-click, "Create Report" is going to save the day.
We're just going through this objects and at
the end you will have this amazing report,
which is not that amazing, actually,
because you have these tables.
Now, out of this report,
users were really interested in
the navigation structure where we have
a pretty comprehensive tree
displaying the parents for the child, and also the child.
For example, here I have procedure as a parent,
and then I have all the procedures in the comforted objects.
Another area of interest for
our user is the lower part of the pane.
Errors were often interpreted
as exceptions within the migration process.
That's not quite true.
Errors are not exceptions at all.
Errors are code or objects
that we could not automatically convert,
and that would need some manual intervention from you.
It can be either rewriting that function
completely or certain parts of that code.
You can go through all of the errors that we have here.
Now, in terms of conversion,
would this block the deployment of my project?
The answer is no.
You can deploy your project even if
not everything is automatically converted.
The piece of code that we couldn't automatically
convert is commented out in the SQL.
You can address it either conversion tab,
and we really prefer that you do that or even
later after you deploy your project.
The next section is warnings.
What are warnings about?
Warnings are things that we could automatically
convert but you might want to
review that conversion and make sure it lands
with the expectations that you have from that feature.
That's another section that you may want to review
and it may require a little bit
of input if you'd like to change
the way we have converted those objects.
Last but not least is info.
If during the conversion,
in order to support certain functionality
or simulate certain functionalities on the target,
we add a column or we add more details regarding for example
here the conversion to numeric or
the string literally was actually converted to numeric literals.
These are information that you may want to review for
FYI and make sure that you know about it,
if you want to have a look later on.
Then we have the big centerpiece here.
Customers usually were looking at the first part of the row
and these were total number of objects,
the automatic percentage conversion,
and also the number of objects that were not converted.
There's a lot of valuable information inside these charts.
We would like to display that in
a user-friendly format that could be leveraged
by our customers looking for those two things,
the manual effort and the quick wins on the automation.
With that being said, I'm going to store this picture in
the SSMA Museum and then going to pass it over to
Steven to show you what we are building for the future.
>> Sure thing. Thank you, Alexandra.
Now that you've seen what the reports used to look like,
let's show you the next generation,
the brand new and improved SSMA reports.
When you first open up a report,
the first thing you're going to be greeted with are
these nice charts in the center of the screen.
We're going to go over what this means.
On the left-hand side, we have object conversion,
which breaks down the objects
categorized by the highest severity of issue in that object.
For example, we have an object that let's say has info,
it has a warning and it has an error.
This will be classified as an error object
because that's the highest level of severity.
As you can see right here in our database,
we have eight automatically converted objects.
We've got 12 objects that have an info message attached to them,
only an info message.
We have one object where the highest conversion message
attached is a warning and
14 objects over here have errors attached to them.
Now on the right-hand side,
however, we have a little different view.
We have manual conversion time.
This is a breakdown
of essentially how much time you'll spend in each category.
For example, what we do is
we estimate if you're a frequent user of SSMA,
you know that we provide rough estimates.
It's not exact, but it's generally a way that you can
approach and then find out where the majority of
your issues are prior to an hour estimate.
In this case, we can see that about 63 percent of
our time or 13 hours will be spent on errors,
whereas only 7.5 hours will be spent on warnings.
If we click on the Warning section of the pie chart,
you'll notice that we are now navigating to
the Warning tab in the messages pane.
Likewise, if we were to click on the Info,
we'll be navigated to the Info section.
What a user may want to do in a case when
they have a lot of error objects,
in this case it's not too many.
User may wish to filter by clicking on the tab.
They can filter in ascending or descending order.
We will just keep it right now in descending order.
That right there may help you determine where you
want to start and where you want to begin your migration journey.
These are the first two pie charts
scrolling down a little further,
we see these bar charts.
These bar charts show the children of the object.
In this case we're dealing with the database,
the children of this database are functions,
procedures, tables, and views.
This provides us an overview of what is going on inside of tables,
views, procedures, and functions.
At a first glance, you might look at procedures and
say you got a lot of problems and procedures,
100 percent have errors, that's really bad.
But a further look, we'll show you
that there's only three objects.
If we say, let's look at tables,
we could say, eight objects.
We have more objects with errors in
the tables category and so that's where I'd like to begin.
I can navigate to the tables category by clicking on "Tables."
I will now be brought to the tables object which
shows a very similar overview to what I just previously seen.
Let's say I want to drill down even further.
Let's say I'm only concerned with our error objects.
I can now filter through these error objects and see
this conversion message has an estimated six hours and I can see
which tables inside this tables folder
have this conversion message attached to it.
In this case, I see that there's
roughly two hours or two conversion messages inside this table.
I can click on this and I will be navigated
directly to the code where the conversion happened.
As you can see on the left-hand side,
we have the MySQL or the older database,
and we have our SQL Server on the right-hand side.
As you can see, there's
this big block of code that's commented out.
That is what we were unable to
convert and would require manual intervention.
We also said that we had two errors on
this object and you can see that down here, we have two errors.
We can cycle through these errors by
using these navigation buttons.
As you can see, we were on
the previous error once we have dealt with
addressed or just acknowledged the first error,
we can now move along to the second error.
With that being said,
that is typically how a user would start to analyze
their database and start to figure
out where they may want to deal with.
Another notable feature that we did add
and I will bring some attention to is the Tree View.
Now we display estimated hours next to the object name.
This can be helpful just
in showing you where you might want to focus your efforts.
Like I mentioned before,
we only have three procedures, 1.5 hours.
Not a very big deal compared
to the problems and I'm going to have in tables.
That is a brief introduction to
our latest assessment reports and that's how you use it.
>> Wow, that's really cool.
I like how you guys showed us where you
were and now where you've gone and it's really amazing.
You have drilled downs,
you have more insights in the time,
you have more insights in the solution.
I also loved the debugging way of looking at errors,
how you go through each of them, so really cool.
Definitely something I think
viewers are going to want to check out.
Alexandra, I had one question for you.
SSMA is a suite of a bunch of tools.
Is this something that's going to be available across all of them?
>> Oh yes and yes.
The codebase is basically the same so we will expose
all this nice rich pie charts in all versions of the tools.
Customers who are looking at the total effort,
don't be scared,
you can automate part of
that effort by leveraging the Fix Advisor tool in SSMA.
If you have the same error in multiple objects,
you just need to fix it twice and then the Fix Advisor,
he's going to pick that up for you
and reduce that time considerably.
>> Wow, that's pretty cool and that's also a nice tip.
Because even myself, I was like,
oh man that's a lot of time.
If you know how to spin fixing it.
But it's nice to know that if you fix it once.
What was that code, you said the Fixer Tool?
>> Fix Advisor.
>> That's a data machine learning device functionality.
It's pretty cool.
>> Awesome, cool. Well, this has been an awesome episode.
I personally learned a lot.
I know our viewers are going to learn a lot.
Thanks so much Alexandra and
Steven for coming on. This is really great.
Love the demo, loved going to see
the museum and then leave the museum behind.
To our viewers, thank so much for joining us.
If you liked this video or are starting to play with SSMA,
especially this new conversion report,
then please leave us a comment.
Let us know what you think,
like the video, subscribe to our channel,
check out the whole series on migrating to
SQL and we hope to see you next time on Data Exposed.
[MUSIC]