Data migration from oracle to azure sql database through Azure data factory

Question

Data migration from oracle to azure sql database through Azure data factory

Kashish 186

Hi,

I am performing the data migration through azure data factory for the below database.

Source database- oracle database.

Target database- azure sql database.

Total tables- 80

Empty tables Structure- I created through the auto create functionality of the ADF.

For constraints, indexes, foreign keys, primary keys -

I have created the scripts separately for all the 80 tables for the constraints, indexes, foreign keys and primary keys.

I cannot use the SSMA tool as per security concerns, so manually creating the sql scripts is the fine approach to do ?

Alberto Morillo 31,456 Reputation points MVP

2023-09-12T17:47:52.6233333+00:00

Could you please share with us what security concerns you have identified on SSMA?
ShaktiSingh-MSFT 9,651 Reputation points Microsoft Employee

2023-09-14T05:03:34.5066667+00:00

Hi
Kashish •,

Following up to see if the below suggestion by Community MVP @AlbertoMorillo was helpful. If this answers your query, do click Accept Answer and Up-Vote for the same. And, if you have any further query do let us know.

Alberto Morillo 31,456 Reputation points MVP

2023-09-12T17:47:52.6233333+00:00

Could you please share with us what security concerns you have identified on SSMA?
ShaktiSingh-MSFT 9,651 Reputation points Microsoft Employee

2023-09-14T05:03:34.5066667+00:00

Hi
Kashish •,

Following up to see if the below suggestion by Community MVP @AlbertoMorillo was helpful. If this answers your query, do click Accept Answer and Up-Vote for the same. And, if you have any further query do let us know.

Answer 1

Alberto Morillo 31,456 MVP

Database Schema Conversion Toolkit is an extension available on Azure Data Studio for converting Oracle database schemas to Azure SQL that can really help you in this migration. The toolkit can help you migrate the full table definitions (columns, indexes, primary key, foreign key, unique and check constraints) and all associated programming objects (procedures, triggers, etc.).

Kashish 186 Reputation points

2023-09-13T15:58:10.9866667+00:00

Thanks @Alberto Morillo

For the database schema both tools you suggested -SSMA and Azure data studio are good.

Please inform incase we wish to only use the ADF (private link connection to database) tool , since it is approved to use within our team.

Question-Through sql scripts of indexes, constraints, primary key and foreign key can we match the all the attributes of the oracle database tables into the azure sql database tables?
Alberto Morillo 31,456 Reputation points MVP

2023-09-13T17:24:21.5733333+00:00

You won't have an exact match of the schemas between the Oracle database and the Azure SQL, that is the reason you need the tools provided to the conversion of the schemas. Once this is done, you can use the following documentation of Azure Data Factory to guide you about moving and transforming the data to the Azure SQL database new schema,

https://learn.microsoft.com/en-us/azure/data-factory/connector-oracle?tabs=data-factory

https://learn.microsoft.com/en-us/azure/data-factory/tutorial-data-flow
Kashish 186 Reputation points

2023-09-15T06:00:40.2433333+00:00

Thanks @Alberto Morillo

I checked within the team we could not use SSMA, Azure data studio at the moment within our team, as we don't want to expose the data on the above tool except ADF.

Question-Please confirm through sql scripts of indexes, constraints, primary key and foreign key can we match all the attributes of the oracle database tables into the azure sql database tables?
Kashish 186 Reputation points

2023-09-15T06:03:52.9966667+00:00

Thanks @Alberto Morillo @Sh

I checked within our team we cannot use SSMA and Azure data studio at the moment.

Question-Please confirm through sql scripts of indexes, constraints, primary key and foreign key can we match all the attributes of the oracle database tables into the azure sql database tables?

I have created manually all the sql scripts for indexes , constraints, primary keys and foreign keys.

Empty table schema structure I am creating through the auto create functionality of the Azure data factory

We wanted to be sure of the approach we finalise for the production environment.
Alberto Morillo 31,456 Reputation points MVP

2023-09-15T14:04:56.0033333+00:00

If you have already taken in consideration that data types of Oracle won't match exactly the Azure SQL data types and that some Oracle collations are not compatible with Azure SQL or may present you some data migration issues like Oracle binary collations, if you have considered that then go ahead with your approach. Azure SQL Collation is not case sensitive by default, take that in consideration also.

Have you considered conversion of existing Oracle (Java) packages also?

Is your organization using user defined data types, that won't be migrated by Azure Data Factory.

Make sure to establish data validation procedures for validating data after migration.
Kashish 186 Reputation points

2023-09-20T14:19:41.0166667+00:00

Thanks @Alberto Morillo

We are trying to get the tool - SSMA and Database schema conversion toolkit for Azure data studio installed within my team.

Please inform is there any other tool/ Tool list which could be helpful to convert the oracle objects compatible to azure sql database.

We will try which from the tool list which tool we can get installed easily within our team.

Tools I am using right now.

Oracle Database-(Oracle SQL Developer)

Azure SQL database

Azure Data Factory
Kashish 186 Reputation points

2023-09-20T21:24:30.7366667+00:00

@Alberto Morillo Please guide for above query.

Thanks for constant guidance and support
Alberto Morillo 31,456 Reputation points MVP

2023-09-21T05:02:23.63+00:00

I am sorry for my late response, sometimes work and meetings do not allow me to provide a faster response.

SSMA and Database schema conversion toolkit are the only tools I know. SSMA is my preferred choice because all the knowledge it has accumulated over the years since it was released. That knowledge that SSMA has embedded is missing on Azure Data Factory.
Kashish 186 Reputation points

2023-09-29T10:00:28.0033333+00:00

@Alberto Morillo

Thanks Alberto for the awesome guidance.

I have got the SSMA tool installed on my machine.

The azure sql database is only accessible with the private link and public access is not feasible as per azure sql database blueprints

The azure sql database is only accessible through ADF through azure private link.

Option-1 Can I utilize the SSMA to generate the equivalent scripts from the oracle database and I can run those scripts through the ADF script activity ?

Option-2 Can I utilize the SSMA to generate the equivalent scripts from the oracle database and publish on the sql server on premises database.

And from on prem sql server database I can get the equivalent sql scripts and run those equivalent sql scripts through ADF script activity to the azure sql database.
Alberto Morillo 31,456 Reputation points MVP

2023-09-29T14:02:37.2733333+00:00

Yes, you can use SSMA to create scripts. After you have converted Oracle schemas to SQL Server, you can load the resulting database objects into SQL Server. You can either have SSMA create the objects, or you can script the objects and run the scripts yourself or from Azure Data Factory. Here you will find how to do that.

Data migration from oracle to azure sql database through Azure data factory

0 additional answers

Activity