Moving from ORACLE to SQL server - SSMA!! is here

Recently for a POC I used SSMA ( SQL server migration assistant) to move my Oracle data over to Azure SQL surprisingly it was super easy!! thanks to Microsoft. I would like to share some challenges and high level steps I encountered during this experiment.

SSMA is a suite of 5 tools designed to help migrations from Oracle, Access, DB2, MySQL and Sybase to Microsoft SQL. In particular, SSMA for Oracle lets you quickly convert Oracle database schemas to SQL Server schemas, upload the resulting schemas into SQL Server and migrate data from Oracle to SQL Server.

Some challenges our client face during migrations especially moving from 1 enterprise grade database to other are schema conversion, post-migration validation and lot of uncertainty during the actual migration process. SSMA puts all these to rest, its seamless, user friendly ( love its GUI) and smart.

For oracle it supports different drivers like Oracle data provider for .NET,

OLEDB Provider and ODBC driver.

Some high level steps encountered are:-

1. Initialize a project

2. Connect to source ( Oracle 12)

3. Build a local metabase that comprises of oracle object you wish to move

4. Create a conversion Report ( not mandatory!- but tells you before-hand any blockers)

5. Convert to target ( azure sql db) and start schema conversion

6. During this process you will actually create schema in target ( target metabase)

7. Cool features here compare your PLSQL and TSQL code side by side and the ability to add additional objects in target. Any errors here indicate that these are not supported and you would have to manually change.

8. Synchronize the source and target

9. Save and deploy your Project

10. Actually movement of data can be done via the same GUI or you could leverage SQL server agent jobs or ADF( copy activity).

Wasn't that quick!? download the out the latest tool here https://techcommunity.microsoft.com/t5/microsoft-data-migration/bg-p/MicrosoftDataMigration

More on prerequisites and instructions for installing the SSMA for Oracle client can be found here:-

https://docs.microsoft.com/en-us/sql/ssma/oracle/installing-ssma-for-oracle-oracletosql?view=sql-server-ver15

For more details on actual migration process check out this YouTube video from Microsoft:-

Watch the episode of Data Exposed with Alexandra Ciortea -> https://www.youtube.com/watch?v=gw_z65mSWgM

Love to hear your comments...Happy blogging!!