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:-
Initialize a project
Connect to source ( Oracle 12)
Build a local metabase that comprises of oracle object you wish to move
Create a conversion Report ( not mandatory!- but tells you before-hand any blockers)
Convert to target ( azure sql db) and start schema conversion
During this process you will actually create schema in target ( target metabase)
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.
Synchronize the source and target
Save and deploy your Project
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:-
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!!