If you are looking to Migrate Oracle Databases To SQL Server 2016 using SSMA, then you have come to the right place. The Microsft SQL Server team has provided an easy to use free GUI tool to achieve this. The current version of the tool is called SSMA 7.0. Microsoft currently offers SQL Server migration assistant tool for Oracle, DB2, MySQL, Sybase, and Access. The latest version of the tool provides also supports the latest SQL Server 2016 features such as Column Store Indexes, In Memory OLTP and Temporal. SSMA 7.0 also happens to be the only tool which enables you to move to Azure SQL Database to the cloud directly. Interested? Then read along to know more about how to Migrate Oracle Databases To SQL Server 2016.
Note: If you buy something from our links, we might earn a commission. See our disclosure statement.
In addition to supporting new SQL Server 2016 features, the current version of SSMA 7.0 also fixes critical bugs. For example, converting of “character data types” for Oracle and “global settings” bug have now been fixed. To get started, follow the steps below:
We will be using the HR sample schema from the Oracle Database as my source and SQL Server 2016 as my destination. The HR schema is not visible by default in SSMA 7.0. You need to change some settings to make it visible. We will discuss this once we get into the details. Let’s look at the migration strategy first. Here are the key points to keep in mind before driving such a project.
It is recommended to treat an Oracle to SQL Server Migration as any other project. Following a standard process workflow is essential for the success of a project. Here are the most important points:
Here is the recommended process workflow:
In the beginning, accessing what’s in scope and how much code needs to be migrated is essential for the project’s success. For example,
Now that we have the project planning and expectations set out, let us focus on the SSMA tool. The device is designed to be project based. Before getting started, you would need to create a project, and store it in the default documents location. You should select the destination SQL Server version at the time of project creation. In our example, we will select “SQL Server 2016”. See below:
By default, the HR schema (Database in SQL Server world) will not be visible in the list of available databases at the source. In that case, you will need to change the following settings:
You can refer to the image on the right to follow the instructions mentioned above.
Once this step is complete, you should be ready to connect to your source database on Oracle. In our example, we are running the Oracle Express 11g for this example. Usually, installing Oracle express edition on a Windows operating system should be straightforward. However, in our case, we had to do some manual configuration to get full access to our “System” account. After assigning explicit permissions, here is what we got:
Once we did this, the rest of the process went smoothly without any hiccups. Here are the steps that you would need to follow:
Here is the step by step screenshots below:
Note: In Scenario’s where full access to the source and destination is not available, you can always save it as script and run it offline.
Once done, you can check the database migration report, which will indicate any issues or errors that might have occurred during the migration process. Here is a quick snapshot:
Since this is a demo scenario, we went ahead with the migration straightaway. However, in production scenarios, it is you would need to create a report using SSMA which will let you know how much percentage of code can be natively converted and which ones will need to be manually rewritten. Here is a sample report below:
There are many other design considerations and scenarios which are beyond the scope of this article. I hope this article on how to migrate Oracle Databases To SQL Server 2016 was helpful. If you have any comments or suggestions, please drop us a comment below in the comment section.
Comments are closed.