Home > SSIS, SSMA > Data migration from Oracle to SQL Server – SSMA

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.

pic1

Step 2: Connect to Oracle (source server)

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

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

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

pic5

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.

pic7

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

pic6

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.

pic8Conclusion

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.

Advertisements
  1. No comments yet.
  1. January 26, 2014 at 1:47 am

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: