>> You may have heard of the Tester functionality in
SSMA for SQL Server On-prem.
But did you know we recently released
a new version which allows you to use the Tester in
an easier way and supports targets like
Azure SQL Database and Azure SQL Managed Instance?
Learn more in this episode of Data Exposed.
[MUSIC]
>> Hi. I'm Anna Hoffman,
and welcome to this episode of Data Exposed.
Today, we're going to be talking about
validating your migrated objects with SSMA,
and I've brought some very awesome guests on today,
Alexandra and Xiao, members of the Azure SQL product group.
Alexandra, can you kick us off by
telling us a little bit about what you do?
>> Sure, Anna. Thanks for having us.
My name is Alexandra Ciortea and I'm
a Program Manager in Azure SQL,
helping customer transition from
other relational database system to Azure SQL and SQL On-prem.
>> Awesome. Thanks.
Happy to have you here again.
This is second episode.
In the past episode, we talked about SSMA.
So definitely, you could want to
watch that episode before watching this one.
Today, we also have Xiao with us.
Xiao, can you tell us a little bit about what you do?
>> Hello, everyone. My name is Xiao.
I'm a Software Engineer,
and I have been working in Microsoft for about five years.
What I have been working on is an amazing tool called
SSMA which is for SQL Server Migration Assistant.
It helps the user to automate
the data migration from foreign platforms to SQL Server.
Building and supporting this tool is a fun and challenging task.
I do enjoy working in the team.
>> Awesome. We're happy to have you here.
It seems like we really have the right people on
the call today to talk about SSMA.
Alexandra, for those of
our viewers that didn't watch the SSMA overview,
which we will put a link to in
the description so you can check it out,
but can you just give us an overview of
why SSMA is such an important tool
and maybe a summary of
what we talked about it in the last episode?
>> Sure, Anna.
Under the SQL Server Migration Assistant umbrella,
we actually have five tools that would support and
automate migration and data movement from
DB2, Oracle, MySQL, Sybase ASE,
and access to Azure SQL or SQL On-prem.
We've covered in the previous video
the steps that you'd need to do
from conversion to data movement,
but we didn't look into the post-validation tasks.
Since we've just released SSMA 8.19,
I think we're just in time to review
those features and make them public for our users.
>> Awesome. I would love to learn more about these capabilities.
SSMA seems like an awesome tool.
Got to see it in the last episode.
But I'm curious, once we migrate our data and our objects,
how do we test them and can we do that using SSMA?
>> Well, that is the question of the day.
Before we had the functionality in the tool,
it was called Tester.
We still call it Tester,
we didn't change that, don't worry.
But it was basically available just
for conversions targeting SQL On-prem,
and the reason behind it is that in
the setup process we had to deploy some databases
and some objects that would be valid just for SQL On-prem.
We always try to improve this experience for
our customer and offer as many features as we can.
So we've sit down and develop the piece of code that would allow
you to have the Tester functionality
for both Azure SQL DB and SQL MI.
On top of that, we've also improved the setup experience.
Right now, you don't need any extensions or anything else to
be deployed by yourself on the SQL Server side.
>> Awesome. Cool.
Today, we're going to get to look at how that works?
>> Yes. Xiao is going to walk us through
the validation journey and show
us what you can do in the new version of SSMA.
>> Great.
>> Sure. From the previous video,
you should have an idea how to migrate your databases using SSMA.
Once the migration completes,
the first thing you may be wondering is that,
how do you validate data and objects?
Now, I will do a demo of
this cool feature in the SSMA called Tester.
Basically, Tester is a component in SSMA.
It helps the user to verify that
the converted objects work the same
way and that the data was transferred properly.
Let me share my screen.
Here is the SSMA project,
which is from Oracle to Azure SQL Database.
I have already converted all objects and transferred the data,
everything is good to go.
How to validate the data transfer is the first demo I will show.
We'd take one table, it's called Regions,
we can check on the Oracle side. Run this query.
It contains four rows,
and then we can do the same on SQL Server side. Run the query.
It contains four rows too.
This is a pretty simple example.
But imagine some table may contain millions or billions of rows,
which is impossible for you to compare row by row manually,
thus, we need to use this Tester tool to
help you automatically validate the tables.
First, we can go to a Tester.
We create our test cases.
Here, we can add a new test case,
provide the test case name as TestTable. We go next.
Here, from the tree,
we select the tables we want to validate as Regions.
We go next. This view is for the affected tables.
As this table is just a simple table,
there's no affected objects.
We can go next directly.
This is the order of course. We go next.
This view is the overview of the test case, everything looks good.
We finish. A test case is created.
Then what we need to do is just run the test case.
From here, you provide credential on source slide.
Click "Connect". Do the same on target side.
Then I will run the Test Case on
both source and target and compare the data.
We can check the reports here.
It shows everything is a success.
We can do the detail.
It shows result sets are fully equal.
This is for the test case on table level,
so that we can compare the transferred data.
Also how about the objects
from the Oracle side migrated to the SQL Server side.
We can do another demo on procedure.
Let's check at procedure called Add Region.
From the content, you can just see
that this procedure is pretty simple.
Just insert a new row to the Region's table,
and we convert it similar on the SQL Server site.
One thing, we need to mention that
if we want to test this procedure,
we need to inject a field test data into the regions table.
How do we roll back
this test data after the test case run completes?
There's another cool feature that we need to utilize,
which is called the Backup Management.
Before doing the test on this procedure,
we can do a backup on the Source table.
From here, you can select the Tables as a Region,
and then we do backup.
Similarly, we do the same on the Target site.
Select the Regions table,
and then we do a Backup.
In this case, the original data will be stored.
Whatever test data we injected from the procedure,
we can roll back to the original data
so the production table will not be changed.
After that, we can create a new Test Case.
At the Test Case named as Test Procedure.
We go next. From this view,
we choose the procedure that we want to test at the region.
From here, you can change the core values.
We provide that as five. Region name as Oceanic.
We're good to go.
Then we go Next.
From here at this procedure call,
we'll insert a value into the Regions table,
so the Regions table should be selected.
There are some other properties that we can change.
For this one, Comparison mode.
Currently, it is Changes only,
which means it only compares the data injected to the table.
We can also do the Full mode,
which will compare all the data from the table.
Let's do this mode first.
This is the order of a course.
We just call this procedure once,
so there's only one here.
We go Next. This is overview.
We go Finish, and we run this Test case.
Provide the credential on source.
Do the same on Target.
>> So while this is running, and it looks like it
will be running really fast.
This is really interesting to me because, one,
it seems like the test cases are super easy to set up.
It's super easy to set all these things up.
Two, it seems like the test cases you're creating are being saved,
so you could use those test cases, again, potentially.
I guess the question I have is,
can you use these test cases,
say, for other projects,
or other things that you're doing,
and are the results saved somewhere after you do this?
>> Yes. All the test cases you created
will be saved along with your SSMA project.
If you want to share the test case without a project,
you can just copy over
the Test case folder to another project folder,
then it will be loaded from there.
After the test case for the procedure down,
we can do a report,
and then check the results.
It shows the results sets are fully equal.
Also, we can compare from the Oracle and the SQL Server site.
We'll run this query again.
We can see one row is inserted here.
We can do the same thing on the SQL Server site.
You can see a new row is also inserted here.
If this procedure works good,
we can roll back the tables that we just changed.
We go to the tester from an Oracle site.
We do restore. It already
has recognized there's a backup on this table.
You check state. It shows this line is ready to do the restore,
and we do the restore.
Also doing the same on the SQL Server site.
You check the state of this one,
it shows tables differ from the backup version,
and we do restore.
We can verify that data on Oracle side.
It looks like the data got roll back,
and then we do the same on SQL Server side.
>> Cool.
>> The data is going to go back too. Yeah.
>> This is really interesting because you didn't have to
restore the whole database,
or backup the whole database.
It can be a quick operation, is that valid to say?
>> Yeah. You just need
to backup all the affected tables in advance,
and then you can restore all the tables that you've changed.
>> Awesome. Cool. Well, this has been a really interesting demo.
I really enjoyed learning more about SSMA,
and this validation seems really easy to use,
and it's nice that you can store the results,
and you can re-use your test cases.
We're going to provide some links
to learn more in the descriptions.
We hope you'll go try it out and give us some feedback,
because this a relatively new capability.
If you liked this video,
please like this video, leave us a comment,
subscribe to our channel,
and we hope to see you next time on Data Exposed.
[MUSIC]