>> You've probably heard of SSMA for migrating from
non-SQL objects to SQL Server or Azure SQL.
In this episode, learn with me as we dive into the tool,
this week on Data Exposed.
[MUSIC]
>> Hi. I'm Anna Hoffman and
welcome to this episode of Data Exposed.
Today, I'm joined by Alexandra,
a Program Manager on the Azure SQL Team focusing on migrations.
Alexandra, thanks for joining us today.
Can you tell us a little bit about what you do?
>> Anna, thanks for having me.
My name is Alexandra Ciortea,
and I'm a Program Manager in
Azure SQL Team focused on heterogeneous migration and helping
customers move their databases from
other relational products to Azure SQL or SQL On-Prem.
>> Thanks. Awesome. We are happy to have you here.
Today, we're going to be talking
about migrations, like I mentioned.
But you work in the heterogeneous space,
so that's moving from non-SQL Server to SQL Server and Azure SQL.
I'm really interested to
understand what are some of the challenges that
people face when they try to do this and how do they address them?
>> Customers usually are challenged on the data migration,
schema conversion and post-migration validation task.
That's a really coherent challenge
that we see across our customers.
In order to address these issues,
we recommend using SQL Server Migration Assistant,
a suite of five tools targeting migrations coming from DB2,
Access, Sybase ASE, Oracle,
and MySQL to SQL Server or Azure SQL DB.
We try to automate as much as possible from
the migration journey and make it easy for our customers.
>> Awesome. Cool. Actually, I've heard a lot about this tool,
SSMA, some people call it.
I'm sure you guys call it that all the time.
But I'd love to understand a little bit
more like, how do I use it?
How does it work? Can I see it in action? That sort of thing.
>> Sure. Let me walk you through
the migration journey and I'm going to share my screen.
In order to start the migration project,
the first thing you need to do is
basically initialize that project in SSMA.
I'm going to create a project,
call it Live Demo. Why not?
I'm going to choose a migration to Azure SQL database.
It usually takes a few seconds for the project to be initialized.
The next natural step is to connect to your source.
In this demo, I'm going to use an Oracle 11.2 version and
I'm just going to provide the password for that environment.
When it comes to our connectivity to Oracle,
we support different drivers.
Oracle data provider for .NET,
OLE DB provider, and ODBC driver.
The connection modes we support are Standard mode, DNS Name mode,
and also Connection String mode if you want
to build the connection string yourself.
Once you have initialized your connection to Oracle server,
we discover the Oracle version and
also load the user schema available.
For today's demo, we are going to go with the classical HR schema,
and we are going to load that for our conversion.
The load time for user schema depends
on the complexity and number of
objects that you have in your environment,
and it also depends on the version of Oracle you use as a source.
>> Cool. Right now in the background,
it's analyzing that schema?
>> Exactly. It's analyzing the system views from
Oracle and we are using queries to retrieve the information.
That is going to help us build a local Metabase in
SSMA and reproduce the structure of
the objects that you have in your Oracle source.
>> Awesome.
>> Now, it's done. I can see my Oracle server and I
can also have a look on
the schemas that I've basically discovered.
The one that I would like to convert,
it's called HR schema.
The next step in the conversion process
would be to create a conversion report.
The report itself doesn't do the conversion on your target.
It's just parsing and converting objects to be able
to offer some statistic regarding
the conversion ratio and possible issue that you may see.
For each heavy object available in the Metabase,
we are going to create an HTML file
and we'll be able to render that,
even use it later on if you'd like to review it.
This is the time it takes to generate the report.
Once the conversion report is generated,
you can navigate through the schema objects.
The level of information we show is
different depending on the object that you are looking at.
At the schema level,
we are looking at total conversion ratio,
which was 100 percent.
Then we'll also have some statistic regarding the object type and
how many objects we converted
successfully and how many objects reported errors.
In the lower part of the pane,
we have three sections: "Errors", "Warnings", and "Information".
Errors usually cover migration issues
that we couldn't automatically fix in SSMA,
and that you would need to manually address.
In the Warning section,
we have objects or data types that we were able to automatically
convert that may behave actually different in SQL Server.
An example from that space is the number data type.
We don't have a corresponding data type
in SQL Server for a direct mapping,
so we have converted that to float.
In the pane for "Procedures" and object containing the SQL code,
you can also have a side-by-side comparison.
This will give you an overview of
the possible migration issues that you may encounter.
Once you are happy reviewing the report,
you can proceed with the schema conversion.
Generating the report is not a prerequisite to convert the schema,
but it's something that you
can generate without connecting to your target.
The next step for me is to connect to my Azure SQL database to
be able to actually convert the schema.
I'm just going input the password for that.
Hopefully, I'll get it right. Okay.
Now, I'm loading the objects from
my Azure SQL database inside my local Metabase in the tool.
The database that we're going to use today,
is an empty Azure SQL database called HR DB.
Here, I don't have any other schemas except the default schemas.
At this point in time,
we are going to convert
the schema and the difference
between this action and the previous action,
is that all that converted code
is going to be inserted in the target Metabase.
If I'm just going to expand here,
you see I have a new entry,
it's the HR schema.
For this HR schema,
I'll have the tables and
the store procedures from my Oracle source.
In the lower pane, I will have an overview,
and we're going to walk you through
the warnings information messages.
If you want to uncheck
information messages and just
review the warnings, you can do that.
Another cool feature of SSMA,
is the fact that you can compare
the original PSQL and the translated T-SQL code.
There are a lot of things that you can set
in terms of conversion and the type mapping.
You can do columns step mapping,
local variables step mapping,
and we have a lot of other options at the project level as well.
Please explore all these five methods
and customize it for your own migration.
You can make changes to your schema.
You can even add objects if you want here.
Change the script, and
after you are done and happy with the final version,
you are ready to move the data.
But even before moving the data,
we need to synchronize
the local Metabase with our Azure SQL database target.
In order to do this,
I'm just going to use the synchronized Database,
and then I'll have a comparison also here in terms
of the object differences between these two,
my local Metadata and my target,
which is my Azure SQL DB.
I don't have any tables here at this point,
so I'm just going to deploy all my converted objects to
Azure SQL DB. I'm just going to do it.
>> It's a two-step process.
You convert and then when you're
happy with the conversion that happened,
then you can basically choose to deploy.
Once you convert, you're not
automatically deploying. Am I getting that right?
>> Yes, you are. That's right.
Once you convert, you basically convert
your local Oracle objects to
your SQL local objects, SQL local Metadata.
Both of these are Metadata.
I think that's a very good point Anna because you can work
with these projects in offline mode as well.
Once you have loaded the meta information within the tool,
you can save the project,
open it later on,
and work on creating
conversion reports or work on converting the schemas.
You don't have to be connected to
your source and target all the time.
We just need this local meta information.
We call them Metabase.
Now, we're just waiting for the deployment to
finish to our Azure SQL DB.
Once that is done,
we can proceed with the data movements step.
At this point, we are all set up and the schema has been deployed,
so we will just move the data.
In order to move the data,
you can use SQL Server Migration Assistant client,
or you can generate an SSIS package.
You use your own SSIS package or ADF data copy activity.
For medium and larger databases,
we recommend using ADF
data copy activity because you can customize
the parallelism and you can also
customize the right batch size on SQL Server's side.
For today's demo, I'm going to use SSMA to move the data,
and then I'm going to open a new connection to
the source and the target to be able to initiate data movement.
Now that everything has been deployed,
we are just going to check it in
SSMS and make sure the tapers are there.
I'm connected to my Azure SQL DB,
HR DB, and I'm going to expand the table section.
We can find here the tables
coming from Oracle originated in Oracle.
Now, let's see if there is something in this, table.
Remember guys, we just did schema conversion,
so right now if we are going to do a select on this table,
the output is empty because we did not migrate the data yet.
What options do we have for the data migration?
We can initialize the data migration from within SSMA.
We can run a server-side data migration
using stored procedures and SQL agent to
connect directly to Oracle and pull the data into
SQL server or we can use Azure Data Factory.
We can leverage the copy activity that will allow for
automatic partitioning and configure
the batch right size on SQL Server side.
For today's demo, we are going to walk
through the data movement from SSMA.
We've already converted the schema now,
we are going to migrate the data.
In order to migrate the data,
you'd need to connect to your source and target system.
Let's see if I had that correctly typed.
Now we are all connected.
We are preparing the table for the actual migration,
starting the migration engine,
and at the end,
we're going to have a nice report that we can share with you.
In this report, you can review the total number of rows,
how many were successfully migrated,
and the duration for each and every activity.
Now let's go back to Management Studio and let's rerun this query.
We have the Oracle data
migrated within SQL Server and ready for you to use.
Another great feature of SSMA is actually
the possibility to test and validate migrated objects.
But for this feature,
stay tuned for the next episode.
>> Awesome. Thanks so much,
Alexandra, this has been really useful.
I just had one follow-up question.
You showed how we can basically convert everything,
our schema, our objects,
even migrate our data.
But what if I have some ad hoc SQL or Oracle in this case queries,
can I convert those queries as well,
or is that what the tester you just mentioned more or?
>> Sure. We can definitely convert statements as well.
We have here a statement note,
which you can use to add your statement.
You can type your statement or you can use SSMA in console mode to
load the statements from
the SQL file and we'll be able to comfort them as well.
>> That's pretty cool. I can
basically import all the ad hoc queries I do on
a regular basis and you guys will help me figure
out what those should be in SQL.
>> Definitely.
>> Awesome.
>> Whatever we've done for the stored code,
we will also do in terms of conversion
and reports for the ad hoc statements as well.
>> Awesome. Great. Well, thanks so much, Alexandra.
This has been really insightful for me.
As someone who had never seen SSMA from core,
I'm super impressed with all the capabilities that are
built into this and I'm
really looking forward to getting hands-on.
We will provide some links in the description
so you guys can learn more about SSMA.
Alexandra gave us a little teaser.
In the next episode,
we're actually going to be covering this tester that you can
use after you've migrated your objects and your data.
Stay tuned. Thanks for joining us.
If you liked this episode,
please give it a like, subscribe to our channel.
Leave us a comment and let us know what you're doing with SSMA.
We hope to see you next time on Data Exposed.
[MUSIC]