Introduction
SSMA is a great tool for data migration if you plan to migrate data to SQL database without any additional cost and setup hassle. It initiates a single data migration process for each individual table. However, if you have a very large table in your database, the total data migration time will increase because the SSMA will not initiate multiple copy process for a single table.
If you have a database with similar data distribution with handful of tables holding most of the data and none of the other available migration services and tools are suitable for you, then use following hack to split your data into multiple chunks for faster data migration.
Use case
This customer wanted to migrate an Oracle database of 1 TB to Azure SQL DB and wanted to use SSMA as data migration tool for its ease of setup and networking requirements. Their application was able to afford longer downtime. They had a schema that had a single table with 80% of the data. In the first run SSMA took more than 20Hrs to complete the data migration. Upon investigation it was found that the large table took most of the time.
We decided to break this table into multiple chunks but physically breaking the table was not possible therefore we tried creating views to create logical chunks. But SSMA does not currently support data movement from views. This hack helped the customer in moving the data faster and with minimal effort.
Setup
This can be best explained using an example.
create view EMPLOYEES1 as select * from EMPLOYEES WHERE EMPLOYEE_ID < 1000000;
create view EMPLOYEES2 as select * from EMPLOYEES WHERE EMPLOYEE_ID >= 1000000 and EMPLOYEE_ID < 2000000;
create view EMPLOYEES3 as select * from EMPLOYEES WHERE EMPLOYEE_ID >= 2000000 and EMPLOYEE_ID < 3000000;
create view EMPLOYEES4 as select * from EMPLOYEES WHERE EMPLOYEE_ID >= 3000000 and EMPLOYEE_ID < 4000000;
create view EMPLOYEES5 as select * from EMPLOYEES WHERE EMPLOYEE_ID >= 4000000;
create table EMPLOYEES1 as select * from EMPLOYEES WHERE 1 = 2;
create table EMPLOYEES2 as select * from EMPLOYEES WHERE 1 = 2;
create table EMPLOYEES3 as select * from EMPLOYEES WHERE 1 = 2;
create table EMPLOYEES4 as select * from EMPLOYEES WHERE 1 = 2;
create table EMPLOYEES5 as select * from EMPLOYEES WHERE 1 = 2;
Note: - DO NOT refresh the SSMA project after the above step is complete.
Note: - Indexes and constraints on the main table should be dropped before running the following insert.
INSERT INTO [HR].[EMPLOYEES]
select * from
(select * FROM [HR].[EMPLOYEES1]
UNION ALL
select * FROM [HR].[EMPLOYEES2]
UNION ALL
select * FROM [HR].[EMPLOYEES3]
UNION ALL
select * FROM [HR].[EMPLOYEES4]
UNION ALL
select * FROM [HR].[EMPLOYEES5]) a
Conclusion
The total time taken to split, and load is 1:56 minutes. Thats a saving of 1:39 minutes against direct load (which took 3:35 minutes) from SSMA.
Considerations
If you have feedback or suggestions for improving this asset, please contact the Azure Databases SQL Customer Success Engineering Team. Thanks for your support! Note: For additional information about migrating various source databases to Azure, see the Azure Database Migration Guide.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.