Oracle to SQL Server Migration - Exploring the process-contd.

Continuing my previous article to explore next steps….

• I installed SSMA – SQL Server Migration Assistant tool on my system and also spun up an EC2 with SQL Server 2019 edition with security group settings to allow my laptop to connect to it.

• Then created a database called HR which will house all schema objects and data from Oracle database and a user called MigrationAdmin with admin rights.

• To make sure the new user can login, I tried to connect to the SQL locally in an RDP session, it refused to connect.  After some research, I found that SQL in EC2 accepts only Windows Authentication. I had to change it to accept SQL authentication also.

• At this point of time, HR database does not have any user objects yet.

•  From SQL Server Migration Assistant, connect to Oracle and SQL Server instances. Now it is like a bridge connecting two worlds. Things get more interesting now. 😊

• After establishing a connection to Oracle EC2 instance, it does a permission check on all objects in Oracle database, so migration of all schema objects can be done without breaks.

• So, I had to give the above permissions to the hr user. In the next screen, select HR schema to migrate from the list of schemas in addition to the default selections.

After successful connection in SSMA to Oracle, I could navigate through different objects and see the procedure definitions.

• Now, connect to SQL Server:

• Click Convert Schema to see the schema is being migrated!
• After it is complete, just do a count check of object types in Oracle and SQL Server.

Here, HR schema in Oracle has 34 objects in all while the same in SQL Server has 38 objects.

19 Constraints in SQL tally with 19 indexes in Oracle. Default constraint seems to be an exception.

The difference is in number of triggers. Oracle has two triggers whereas SQL has four!

Oracle Triggers:

SQL Triggers:

• Now let’s click Migrate Data!

Hey, data migration is done! Now, lets check row counts on both sides! As soon as data migration is complete, it pops up with Data migration report with row counts of migrated Oracle tables.

Does it match with row counts from SQL Server?

Yes, they do! 😊

I am happy that I was able to do this with two EC2 instances on my laptop.

Happy Learning!