>> Learn how the latest updates to SSMA have enabled you to do
automatic partition conversions with
SSMA this week on Data Exposed.
[MUSIC].
>> Hi, I'm Anna Hoffman.
Welcome to this episode of Data Exposed.
Today I have two very special guests with us.
The first is Alexandra.
Alexandra can kick us off by just
explaining to our viewers a little bit about what you do.
>> Hi Anna. Hi everybody.
My name is Alexandra Ciortea,
and I'm a Program Manager in Azure Data.
In my role, I'm helping customers migrate from
heterogeneous spaces to Azure SQL.
In order to streamline that migration process,
we are trying to build the right tools and
the right support for our customer, and partners.
>> Awesome. Thanks Alexandra.
That's going to be great to
have you because we're going to be talking
about migrating to SQL and some of the things to consider.
Now we also have Xiao with us.
Xiao, can you tell us a little bit about what you do?
>> My name is Xiao Yu.
I've been in Microsoft for about five years.
I'm in Azure Data.
Currently, I'm in a team called SQL Server Migration Assistant.
It helps customers to automate
their database migration from competing platform to SQL Server.
>> Awesome, great. We have a great episode planned today
continuing along our many series of migrating to SQL.
Today we're going to be talking about something
relatively new to SSMA,
or SQL Server Migration Assistant.
Alexandra, can you tell folks a little bit about what's new,
and why it's something that you have decided to invest in.
>> The latest feature is actually
the automation conversion for partition tables.
We've enabled this for our customer as it was one of
the pinpoints for heterogeneous migrations.
Customers usually should invest a lot of time in
developing a partition strategy.
Understanding which files should go where,
what is the partition function they should use,
and doing this manually at
migration time requires a lot of efforts.
Our latest SSMA feature is going to
automate that for you, and even more,
give you the opportunity to simulate
Oracle partition functions in SQL Server.
We all know we don't have a one-to-one mapping
between Oracle partition types in SQL Server partition functions,
so we were able to cover that gap.
But to give you more details about the implementation,
and about the experience,
I'm going to pass the mike to my colleague Xiao,
who is going to walk you for this experience.
>> Just as Alexandra talked about,
there's a big difference between
Oracle partition table and SQL server partition table.
On Oracle side, is supposed three particular strategies,
Branch partition, List partition, and Hash partition.
But on the SQL server side,
it only supports the Range partition.
We need to do a map to convert
from these three different strategies
to the Range partition on a SQL server side.
Here I will do a demo on how we do it in SSMA.
The first thing we need to do is to do the tablespace mapping.
Tablespace is on the Oracle side to
logically partition data in different tablespaces.
We don't have the tablespace on SQL server side.
A SQL server side it supports the file groups,
which basically doing the same logically
partition all the data into multiple file groups.
We can do the mappings on the schema levels,
or on the table level.
For this demo, I have already
created several partition tables here.
The first of all, we need to do the mapping on the schema level.
Here, we can see all the tablespaces on the Oracle side.
I'll do the mapping here.
Mapping from Tablespace 1 to File Group 1,
Tablespace 2, to File Group 2, and so on.
>> If you don't want to go for this experience,
and do this mapping,
will you by default map them to primary?
If this is something that you are okay with as a customer,
you can also go ahead with the default.
>> Yeah, after doing the mapping,
we can apply the mapping here,
and then it will automatically apply to all the tables.
If we go to the tables,
you can see the mappings already mapped,
and all of them are doing the same by that.
After that, we can actually kick off the conversion.
We just select this six partition table.
We convert a schema.
Here on the SQL server side, for this partition,
tables are converted, we can take
a closer look on how we do the conversion.
The model symbols one is the Range partition
because SQL Server supports Range partitions.
So we pretty much doing the same.
Previously, it is a Range partition based on the date time.
Any value less than 2018,
April 1, will map to the first tablespace,
and any value less than 2019 will map to second tablespace,
and for '20, it will map to Tablespace 3.
What we do on a SQL server side is here we
create a partition function and
putting all the values as rest of values,
convert it into a SQL server date-time,
convert all the values there.
Then we create a partition scheme.
Apply all these partition functions
into three different partition groups.
Note that on the SQL server issued provided
the default part of the rest of the value.
On Oracle side, you don't need to do that.
We provide the primary four groups for the rest of the value.
After that, this is a pretty simple range partition conversion.
As I mentioned earlier,
oracle also supports list partition and
hash partition which SQL server doesn't support,
so how do we handle that?
Let's take a look at how we
handle the conversion for the hash partition.
On Oracle side, it can do a partition based on any colon.
Like for this one, you do a hash on
the ID column and the particular into three,
store them into three separate tablespaces.
What we can do on the SQL Server side is pretty much the same.
The first thing is that we need to create it a computed colon,
which can mimic the behavior on Oracle side to do the hash.
Like here we do a computed colon.
The hash value on the ID,
and the molded by three,
this will generate the value from 0-2, three values.
Then similarly we do
the partition function based
on the range between the values less than one,
between 1 and 2, and the values larger than two,
and then we do the partition scheme over this particular function,
mapping them into three different filegroups.
In this case, we can just mimic the behavior of
a hash partition, like this.
This is how we handle the hash partition.
Also, Oracle supports least partition table,
and pretty much the same.
We can see how the Oracle doing for the list partition.
It just lists a few values for
each partition and store them into the tablespace.
What do we do on the SQL server side is
also we create a computed colon and use a case link condition.
Map different list values into
simple integer value starting from 0-2,
and then create a partition function based on the range.
Any value less than one,
value between 1 and 2 and a value larger than two,
and then using this particular function
to generate a partition scheme,
map them into three different filegroups.
In this case, we can map
the hash partition into SQL Server range partition.
After checking all this one,
we can just synchronize the partition table on a SQL Server side.
Here we've deployed
all these six partition tables onto SQL Server.
Yeah, here the deployment is complete.
We can check on the SQL Server-side.
As we can see that,
all these tables are existing here.
We can just check randomly,
pick this one, we check the properties.
From the storage, we can see that this one is
a partitioning tables into four different partitions.
This is pretty much my demo.
>> Cool. That's really Xiao.
Not only can SSMA handle what you might think is like
a more simple conversion of a range partition,
but it can also support
these hash partitions and enlist partitions,
and if I'm understanding correctly,
you don't really have to do anything.
It's able to generate all the T-SQL code to do
that conversion and mimic
those types of partitions in your SQL database.
That's pretty cool. One question I had is will
SSMA also create the database files for this?
>> No. SSMA only creates the filegroups,
and after the conversion is done
and the table is deployed to the SQL Server side,
user need to manually create
their database files and attach it to the filegroup.
>> That's a good call-out for people to
remember once they do this conversion.
Cool, awesome. Well, thanks so much, Xiao.
This is really interesting.
As people start to play with this,
I have a few questions.
The first question, maybe this is an easy one, is,
how do people get access to this new capability that's in SSMA?
>> Their automatic partition conversion is going to be
included in the version 8.20 of SSMA.
It's free to download.
You just need to access the link in our resource groups,
and then you can just start testing it.
>> Easy enough. Just make sure you're running
on the latest version of SSMA,
at least as of today.
Then my final question is,
as people start working with this,
you mentioned one call-out
or got-you to remember about the database files,
are there any other tips or tricks you have
for users as they start getting started with
automatic partitioning or even SSMA in general?
>> I think it would be really interesting for them to revalidate
their workloads and see if the partition,
the original partitioning strategy is still valid for
the data type they want data and
data type they want to migrate to SQL Server.
If you're going to move all your data or just part of that data,
and adjust to that decision.
There's also the possibility to modify it any time,
the function and the partition scheme within SQL Server,
so you have full control over what you want to achieve,
and you are free to contribute to that,
alter it or create another one.
>> Awesome, cool. I think that's great advice.
Don't just blindly convert everything.
Make sure what you're converting,
what you're moving and the decisions you're keeping with are still
the right decisions for
your application or for your database. I love that.
Alexandra and Xiao, thanks so much for joining us on the show.
For our viewers, we're going to put
some links in the description for you to learn more,
and try this out and even follow along
this many series we're doing on migrating to SQL.
If you like this video, go ahead and give it a like,
give us a comment and let us know what you think
of SSMA and the latest capabilities,
and we hope to see you next time on Data Exposed.
>> Thank you very much, Anna,
and thank you for having us.
[MUSIC]