Nowadays people in IT are considering to migrate here and there, on premises to cloud sometimes cloud to on premises base on their requirements and resources. When we come to database maintenance here also we are facing the same problems. In the database world there are lot of databases available in the market based on their requirement they will move over to different plat form, the issues when then plan to move over is the data. Whatever the data they used in the past since they can't easily wipe off.
In this document I am showing you the way of migrating the Oracle 12c database to latest Microsoft 2019 SQL server without any lost, at the same when we considering the migration we have to make sure that our application also supported with Microsoft SQL server, As I am working on my own application I am pretty sure that I have written the same code to Microsoft as well.
Will start the migration, before starting this I am requesting you to these prerequisites done earlier to the migration
Oracle Database 12c Installed
Microsoft SQL Server 2019 Installed
Microsoft Data Migration Assistant tool downloaded
Please allow the following ports through your firewall 1521 - Oracle , 1443 - MS SQL
Here from my Oracle Database I have several schema, but only migrating the COREDB schema, as we know that An Oracle database contains schemas. An instance of SQL Server contains multiple databases, each of which can have multiple schemas.
Creating the COREDB database in the SQL Server
Create New database in the Destination Server
Login to the SQL Management Studio and Create the new database
Login with your server name, user credentials and create new database named COREDB
Fresh COREDB database is created, It's blank database
Will Migrate the Data from the Source Oracle database now
Open Migration Assistant tool and create a new project
Connect the Oracle database to the project using Oracle database parameters
Once it's connected it will load all user schema to the project
In the mete data explorer will confirm that all the schemas has been loaded to the project
Now select the schema which you need to migrate and select all other objects as well, if your database is connected with application sever you must select the all objects Views, Functions, Tables and etc
Now we need to connect the destination SQL server with Server credentials,
Once it's connected check with the meta data explorer on the database
Once done click on the source Oracle schema and convert the schema to SQL server
After it's finished on the destination server click the database and synchronize the schemas with source database verify that the Arrow is correctly showing from where it needs to be synchronize
Once done login to Management studio and verify that some of the system tables and schema views has been created
After verified, we have finished all the schema creation and the other part of system function views.
Now it's time to migrate the tables and data. Go to the source server schema and click on the migrate data
It will ask the Oracle and Microsoft credentials again, give the correct parameters and click on ok
After verified the source and destination server credentials, it will start the migration of the tables with the data
See the logs and report to verify is there any migration errors or failures
Login to Management studio and see the tables and data has been migrated successfully
To make sure all the data from the source server has been migrated successfully, I have ran a query on the both databases,
On the source server,
On the destination server,
Yes, I'm done. Both the results are same means its migrated succesfully.
Happy Migration!!