Data migration from Oracle to SQL Server – SSMA

SSMA is the tool design by Microsoft to make database migrate process easier. I have written blog posts that discussed about SSMA here and here.

In this blog post, I am going to show you how to migrate data from Oracle to SQL Server.

Prerequisites: SSMA and it’s Extension pack must be installed in the machine before using this demo.

Implementation

Step 1: Open SSMA and create a project.

Step 2: Connect to Oracle (source server)

Step 3: On successful connection, we have to select the schema-> Objects for migration

Step 4: Connect to SQL Server. There is a tool bar below the file menu that contains Connect/Reconnect button for Oracle and SQL Server. On successful connection, we can see the SQL Server name in the SQL Server metadata explorer.

Step 5: By default, SSMA will assign database name as Oracle schema name. If we want to change the schema or database name then we have to alter the name in the schema mapping page. In this demo, I have already created a table name oracle in SQL Server.

Step 6: Convert Schema. In this step, we will create a tables from Oracle to the target SQL database. Right click on the Oracle source schema and click Covert Schema button on the tool bar.

Then, Go to the SQL Server metadata explorer and right-click on the target database and click “synchronize with Database” to apply new table schema to SQL Server.

Step 7: Click Migrate Data button on the top the tool bar.

Now, a pop-up with Oracle connection with appear and fill the password and click OK.

Now, another pop-up will appear for SQL Server connection and click OK.

This will start the data migration process. We can see what is going on in the data migration process in output window.

Conclusion

SSMA make the data migration process easier and simpler. This tool also has the lot of features such as data type mapping, Test case, Script generator and Backup manager.