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

Rahul 251

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 34,671 Reputation points MVP Volunteer Moderator

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

Could you please share with us what security concerns you have identified on SSMA?
SSingh-MSFT 16,371 Reputation points Moderator

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.

Accepted answer

0 additional answers

Your answer

Alberto Morillo 34,671 Reputation points MVP Volunteer Moderator

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

Could you please share with us what security concerns you have identified on SSMA?
SSingh-MSFT 16,371 Reputation points Moderator

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 34,671 MVP Volunteer Moderator

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.).

Rahul 251 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 34,671 Reputation points MVP Volunteer Moderator

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
Rahul 251 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?
Rahul 251 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 34,671 Reputation points MVP Volunteer Moderator

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.
Rahul 251 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
Rahul 251 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 34,671 Reputation points MVP Volunteer Moderator

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.
Rahul 251 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 34,671 Reputation points MVP Volunteer Moderator

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.
Rahul 251 Reputation points

2023-10-10T22:57:57.94+00:00

Thanks @Alberto Morillo For constant guidance and support.

I am using SSMA for the create schema from oracle to the sql server database.

The schema created by SSMA will be deployed in the azure sql database through the azure data factory (script activity).

The tables data migration will be done through the azure data factory as the azure sql database is only accessible through private link or ADF self hosted integration runtime.

Question1 - After the create schema from SSMA from oracle to sql server database , i am facing 128 issues, it difficult to rectify each issues , how to deal with this problem ?

Questions-2 The oracle packages were build very old in 2010 and it is showing a warning pre-requisites not meet during the create schema from the SSMA?

Question-3 How to create the schema for the user defined types of the oracle using SSMA ?

Overall I am facing 128 issues after the create schema from oracle to sql server , can I avoid the issues and utilize the create statements of the sql server stored procedures and deployed the create scripts in the azure sql database.

Please guide and thanks for awesome guidance.
Alberto Morillo 34,671 Reputation points MVP Volunteer Moderator

2023-10-11T03:58:09.1966667+00:00

Hi Kashish,

Question #1: Please review the assessment report that shows the percentage of objects that will be successfully converted and the specific issues that cause conversion failures. You can browse the hierarchy of objects, view the source and target code, and see the conversion messages. You can use custom node converters to fix problematic code. as explained here.

Question #2. Maybe leaving them to the final stage, then trying to use Oracle AutoUpgrade to upgrade them to a SSMA compatible version. Otherwise you may have to rewrite or refactor them.

Question #3. First, you need to identify the custom data types in your Oracle database and understand their structure and usage. You can use the SSMA for Oracle tool to view the custom data types in the Oracle Metadata Explorer pane and see their properties and dependencies. Next, you need to decide how to map the custom data types from Oracle to SQL Server. SSMA for Oracle provides some default mappings for common custom data types, such as VARRAY, REF CURSOR, and RECORD. However, you may need to modify or create new mappings for some custom data types that are not supported by SSMA or that require special handling. You can use the Type Mapping Editor in SSMA for Oracle to customize the mappings for custom data types.

Best wishes. Best of luck.
Rahul 251 Reputation points

2023-10-18T11:01:44.7066667+00:00

Hi @Alberto Morillo

Thanks for help and support.

I have deployed all the sequences of the oracle database to the azure sql database, also deployed the tables .

Question-1- Please inform how the sequences will maintain the interlink with the tables ?

Question-2 Oracle packages are converted into the Azure sql database stored procedure and packages.

How the stored procedures and packages are interlinked with the database and the application.

Will application automatically identify the stored procedures and packages ?
Alberto Morillo 34,671 Reputation points MVP Volunteer Moderator

2023-10-18T12:46:12.34+00:00

I am glad to hear that, Kashish.

Q1: When you say interlink, are you referring to what is called referential integrity? Please make sure referential integrity constraints were created based on the scripts generated by the SSMA tool.

Q2. SSMA tries to convert packages to stored procedures and functions. On the Type Mapping Editor of SSMA you may have more options available.

Q3. Stored procedures and functions are stored on the database schema and they go where ever the database go. You restore the database on another server and the stored procedures will be there. Applications just need to change the connection string and connect to the database, once the app is connected it can execute the stored procedure specifying the schema and the stored procedure name along with its parameters.

EXEC schemname.storedprocname @ParamName = @variable
Rahul 251 Reputation points

2023-10-18T17:21:18.7+00:00
Thanks a lot @Alberto Morillo

I am deploying all the components in step by step manner sequences, packages into the Azure sql database through the ADF script activity.

Question-1 The database and application will be able to identify the sql sequences and the stored procedures etc.

Question-2

SSMA creates few of the stored procedure by the oracle package name.

Code inside the above stored procedure.

EXECUTE ssma_oracle.db_clean_storage

Do we need to deploy that stored procedure in the target database-Azure sql database ?
Alberto Morillo 34,671 Reputation points MVP Volunteer Moderator

2023-10-19T03:25:56.64+00:00

Yes, consider deploying the stored procedures on the target database. Some of the Oracle stored procedures you may have to consider converting them to SQL Server functions. Some Oracle packages can be converted to .NET assemblies if you can use SSMA extension pack
Deleted

This comment has been deleted due to a violation of our Code of Conduct. The comment was manually reported or identified through automated detection before action was taken. Please refer to our Code of Conduct for more information.
Deleted

This comment has been deleted due to a violation of our Code of Conduct. The comment was manually reported or identified through automated detection before action was taken. Please refer to our Code of Conduct for more information.
Rahul 251 Reputation points

2023-10-23T21:46:25.7366667+00:00

Thanks @Alberto Morillo for constant guidance.

Questions- As represented in the below screenshot the dollar sign has been added in between the oracle package name and function/stored procedure name.

Do i need to remove the dollar sign in all the attributes where it is observed. so that application can identify the function/stored procedure/triggers accordingly.

And also in many triggers and other oracle database attributes, the dollar sign has been added , do i need to remove the dollar sign from all the places where it is observed.

Alberto Morillo 34,671 MVP Volunteer Moderator

You can create a script renaming all stored procedures and functions exactly as the application or other database objects call them. You can do that programmatically using Transact-SQL. You can use sp_rename to do that. In below example I am changing the prefix of the name of all stored procedures.

-- Declare a cursor to iterate through the list of stored procedures
DECLARE @procName nvarchar(128)
DECLARE @newName nvarchar(128)
DECLARE proc_cursor CURSOR FOR
SELECT name FROM sys.procedures
WHERE name LIKE 'SP[_]%'
-- Open the cursor and fetch the first row
OPEN proc_cursor
FETCH NEXT FROM proc_cursor INTO @procName
-- Loop through the cursor until there are no more rows
WHILE @@FETCH_STATUS = 0
BEGIN
-- Generate the new name by replacing SP_ with proc_ and converting to lowercase
SET @newName = LOWER(REPLACE(@procName, 'SP_', 'proc_'))
-- Rename the stored procedure using sp_rename
EXEC sp_rename @objname = @procName, @newname = @newName, @objtype = 'OBJECT'
-- Fetch the next row from the cursor
FETCH NEXT FROM proc_cursor INTO @procName
END
-- Close and deallocate the cursor
CLOSE proc_cursor
DEALLOCATE proc_cursor

You may not have to do this with triggers.

Rahul 251 Reputation points

2023-10-29T20:04:56.91+00:00

Thanks @Alberto Morillo for constant guidance

During oracle to Azure sql database conversion using the SSMA

There are 4 oracle packages.

Which includes functions, procedures, private package functions, private package functions etc.

Question1- Can I create the respective functions and stored procedure in Azure sql database myself including the logic same as in above functions/procedures.?

Question2- There is no Oracle package like thing in the azure sql database, so will individual separate stored procedure and functions understood by the application.

Question3-There is no PRIVATE stored procedure and functions like thing in the azure sql database, so will individual separate stored procedure and functions understood by the application.

Question-4 I am not loading the whole ssma schema only loading the required objects from the ssma schema which are used using inside the stored procdures and functions
Alberto Morillo 34,671 Reputation points MVP Volunteer Moderator

2023-10-30T14:37:11.5633333+00:00

Hi Kashish,

Q1. Usually some packages you may have to create them from scratch as CLR functions also but CLR is not supported on Azure SQL but is supported with limitations on Azure SQL Managed Instance. In general, is normal that you to recreate from scratch some stored procedures and functions. Avoid to use table variables and Common Table Expressions for large workloads.

Q2. You are correct. The closest to Oracle Packages are CLR functions but they are not supported on Azure SQL. Stored Procedures and Functions are the ones available.

Q3. Private stored procedures and private functions are subprograms that are defined within a package and are only visible and accessible within that package. SQL Server and SQL Azure do not support packages. Dynamic SQL can be used for that purpose.

Q4. Ok. You have the scripts created by SSMA and you can decide what to use on your migration plan or not.

Share via

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

0 additional answers

Your answer