How to Migrate Oracle Databases To SQL Server 2016 Using SSMA 7.0

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.

Migrate Oracle Databases To SQL Server 2016

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:

• Download SQL Server Migration Assistant from the Microsoft Download Site.
• Download the Oracle Client Tools if you have not already.
• We have used the Oracle Database Express Edition 11g for our migration example.

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.

• Every scenario is unique and different. Do not all migrations are equal
• Technicalities apart, Not all customers will want to work the same way
• The business cases will differ. Not all clients want the same conclusion or outcome
• Work with your stakeholders to develop a mutually agreeable approach and plan

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:

• Define
• Assess
• Design
• Migrate
• Validate
• Test &
• Deploy

Here is the recommended process workflow:

Source: Microsoft

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,

• How long can you afford your systems to be down?
• Is this a part of a bigger database consolidation project?
• Performance expectations of the client after when Oracle Database is successfully migrated to SQL Server.
• Cost Estimation is critical
• Find out the inventory of the current environment. Use the Microsoft Assessment and Planning Toolkit (MAP) to achieve this.
• Not all Enterprise Customers would allow you to run either MAP toolkit or SSMA for Oracle. In that case, what are the alternatives that need to be planned?
• Capacity planning of the new hardware on the target system. For example, CPU, Storage, RAM. Learn more about SQL Server Disk Configuration best practices.
• What collations will be used on SQL Server and data access patterns (For example, Cursor, Paging, Collection, etc.)
• Prepare a Rollback and Mitigation strategy.
• Post Migration, validate the database objects, row count, and security validation.
• Perform UAT (User Acceptance Testing)
• Deployment

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:

• Click on tools > Project Settings
• Click on the General tab
• Under “Mode” select “Loading System Objects.”
• Check on HR schema and then click on “Apply” & “Ok.”

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:

• Connect to your Oracle Server with the provided credentials. In our case, we would be using the System account.
• The initial connection takes a couple of seconds to complete. Once done, expand your server name, expand schema and select the HR schema check box.
• Next, you can connect to your SQL Server as the destination. Create the target database beforehand to simplify the process.
• Once both the Source and Destination databases are connected, click on “Create Schema” on the database of origin. It creates a compatible schema for SQL Server.
• On the destination SQL Server, click on “Synchronize with Database.”
• On the Oracle HR Schema, Click on “Migrate Data.”
• Type in the password for both Oracle and SQL Server again and proceed.

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:

Conclusion

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.