How to Migrate an Oracle Database to SQL Server

By Annette


Migrating a database is not an easy decision, nor one that businesses should take lightly. However, there are instances where Oracle to SQL Server migration is the chosen path. Whether you’re looking to migrate your organisation’s Oracle Database to Azure SQL or server-based SQL Server, the steps below could help you to understand the scope of the project and get started.

Why migrate your database?

When enterprises wish to migrate a database from Oracle to SQL Server, the most common reasons are:

  • Infrastructure consolidation
  • Tech stack standardisation
  • Licensing costs
  • Shortage of in-house skills

If your company is considering migration for either of the last two points, but you aren’t sure how to migrate your oracle database to an SQL Server, we would first encourage you to get in touch with our Oracle DBA team. They can complete an Oracle Database health check and review your current Oracle Licensing position and ensure that your business isn’t paying for features that aren’t required.

Oracle to SQL Server migration challenges

Migrating is not just a copy-and-paste exercise. As much as Oracle and SQL Server are both highly commonly used database servers, there are some significant differences between the two platforms. Most notably, T-SQL (the language that applications use to interact with databases) are very different in their implementation.

It is impossible to just lift the Oracle Database code into SQL Server.

How to migrate data from Oracle to an SQL Server

The key to a successful migration is to identify any migration blockers, resolve, test and finally deploy to SQL Server. Below, we’ll cover how to migrate your data from Oracle to an SQL Server, starting with the information you’ll need to get together and the steps needed to get your SQL Server Migration Assistant (SSMA) report.

Warning: we recommend completing these steps in a testing environment first

The first step to take for a successful migration is then to use the SSMA tool to generate a report on what migration blockers may be present in your Oracle Database. Our recommendation is to use a test version of your Oracle Database for this purpose. Although the tool won’t make any changes and shouldn’t affect the performance, it is always safer not to be working on a production database unless absolutely necessary.

While it doesn’t need to be the server that holds the Oracle Database, it may be faster to execute if the tool and the database are together.

Step 1: Gather important information

Before you start, in order to generate a report, you’ll need the following information:

  • Oracle server name
  • Oracle server port
  • Oracle SID
  • Username to connect to the Oracle Database
  • Password for the username to connect to the Oracle Database
  • The SQL Server name
  • The SQL Server port
  • The name of the database that you’re migrating to – this is usually a new database and the tool will create it for you
  • How you wish to connect. By default, this is Windows authentication, but can be changed to use SQL Server credentials. You can also encrypt the connection, but this will reflect how your business connects

Once you have this information, you’re ready to begin.

Step 2: Download the SSMA tool

To help with the transition from Oracle to SQL, Microsoft offers a tool called SQL Server Migration Assistant (SSMA). This tool does the following:

  • Analyses the Oracle Database
  • Identifies what database objects can be automatically migrated (the tool will actually convert syntax where possible)
  • Flags up any migration blockers that cannot be automatically converted

These blockers would require further investigation or code rewriting to make them SQL Server-compatible.

SSMA can be downloaded from here: https://www.microsoft.com/en-us/download/details.aspx?id=54258

Once downloaded, click on the shortcut to the tool that looks like this:

Microsoft SQL Server Migration Assistant for Oracle shortcut

This is what the tool looks like once opened:

Microsoft SQL Server Migration Assistant tool screenshot

Step 3: Create a project

Before you can go any further with the process, you need to create a new project. To do this ,click on File -> New Project, as shown below:

Microsoft SQL Server Migration Assistant for Oracle New Project dropdown

Enter a relevant project name and select the folder in which you want to save it.

As a best practice, we suggest you create a new folder for each project and name the project based on which environment and database you’re working on, as shown below:

Microsoft SQL Server Migration Assistant for Oracle New Project name, location, and migration information form

Once you’ve created a project, you’ll notice that some items on the toolbar are no longer greyed out:

Microsoft SQL Server Migration Assistant for Oracle toolbar after inserting migration information

Step 4: Connect to the Oracle Database

To do this, click on Connect to Oracle:

Microsoft SQL Server Migration Assistant for Oracle Connect to Oracle button

You will be asked to Connect to Oracle and will need to insert all the details relating to how you connect. Once you’ve populated all the information in this box, click on Connect:

Microsoft SQL Server Migration Assistant for Oracle form for specifying parameters for new connections

At this point, the tool connects to Oracle and prompts you with a list of all schemas available to you.

Select the one you wish to migrate and click on OK.

Please note that SYS and SYSMAN are automatically selected and cannot be removed from the selection:

Microsoft SQL Server Migration Assistant for Oracle filter objects selection screen

The tool will then load all the objects.

You can track what is loading on the bottom left and see how much has been downloaded by reading the percentage on the bottom right. Loading objects can take a while, so be prepared, as it will usually take the time it would take to make a cup of tea:

Microsoft SQL Server Migration Assistant for Oracle download progress screen

Step 5: Connect to a SQL Server

While at this point you’re not actually migrating, you still need to complete this step.

To do this, click on Connect to SQL Server:

Microsoft SQL Server Migration Assistant for Oracle Connect to SQL Server button and metadata explorer

You will then be prompted to enter the SQL Server details referenced earlier. Once you’ve entered the required information, click on Connect:

Microsoft SQL Server Migration Assistant for Oracle connect to SQL Server screen

Step 6: Create a report

Once you’re connected to SQL Server, you’ll need to select the source Oracle Database and the destination SQL Server database, and then click on Create Report.

Remember, you are not actually doing the migration, you just want to know what issues you might find if you were to try to migrate it.

Microsoft SQL Server Migration Assistant for Oracle create report for Oracle to SQL Server migration

The tool will now start executing the process to identify migration blockers; because it needs to load all objects and consider how to convert them, this can take a while.

Some environments take less than an hour, while others take up to 6 hours; this is all dependent on the number of objects in the database. As a rough guide, one object takes between 5 and 10 seconds to process.

When you click on Create Report, you may have a box presented to you with “Operation Requisites Not Met”. This will give you a list of objects that are immediately unable to be converted.

Select Continue, as you will also receive a list of these as part of the report.

Microsoft SQL Server Migration Assistant for Oracle warning screen

Once this process has been completed, you’ll be presented with a report listing all objects and whether they can be automatically migrated or not.

Below is a sample report from the tool. You can see that there are a lot of red crosses at the database level, the object type level and if you drill down at each individual object.

Microsoft SQL Server Migration Assistant for Oracle migration assistant

Step 7: Investigation, migration and testing

At this point, each object that can’t be automatically migrated would need thorough investigation as to how to migrate it with minimum impact on any applications that are using the database. To date, at WellData, we have not found any object that can’t be migrated, although sometimes it can be quite a complicated process.

If you get to this point and need some help, please contact WellData and we can get involved in helping with or completing the migration process for you.

There might be instances where the SSMA tool will be able to automatically migrate all of your database code to SQL Server. However, even in this case, it is critical to ensure that thorough testing is carried out to confirm there are no issues with data integrity and performance. This is because performance and behaviour can in some cases be very different between the two platforms.

Start your database migration today

If you need help with understanding how to migrate data from Oracle to an SQL Server, and testing this in a development or production environment, feel free to contact us at WellData. We can provide a suite of testing tools as part of our migration project.

Our DBAs can bring over two decades of independent expertise to your migration project, from strategy and planning to migration and operations.

 

Get in touch

<< Back to Knowledge Centre

Related Technologies:


Join Our Newsletter

Contact Email  *
First Name 
Last Name 
*Required Fields
I agree to the  Privacy Policy and  Terms of Use 

Here's what other people think

Google Rating
5.0
High standard, professional service. I've worked with Welldata for a number of years and always found their DBA's to be knowledgeable and proactive. Throw any question at them and it will be resolved, it gives real peace of mind knowing you have a partner you can rely on from emergencies to general questions. They also like a challenge and we do throw a few curved balls at them.read more
Paul Miller
Paul Miller
12:34 27 Aug 21
The support I receive from Well Data is extremely responsive. Richard Parsons is my contact and keeps me informed daily on the health of our SQL Databases. Whenever I ask for assistance from Well Data it is quick and professional.read more
Kay Riley
Kay Riley
13:41 19 Aug 21
Really know their stuff and their response times are very good. They provide us with a vital service.
Andy Cole
Andy Cole
13:36 19 Aug 21
The guys at WellData really know their stuff. We wanted a quick route to doing something complex and they sorted it for us very quickly and to a high standard. Very professional, no ego, just excellent and to the point consultancy.read more
Steve Goacher
Steve Goacher
11:31 14 Nov 19
I have worked with the WellData team for many years now and they have always delivered.I would highly recommend them to any business looking for best-value outsourced Expert DBA’s.Being able to offer a rolling contract on the basis that if they don’t deliver you can leave shows their confidence and commitment.Their professionalism and knowledge is second to none.read more
Petro Bartoszyk
Petro Bartoszyk
08:51 18 Oct 19
A very dedicated team of extremely professional and knowledgeable experts
Giulia Iannucci
Giulia Iannucci
14:08 16 Nov 18
We use Welldata for SQL support and are very happy with their service.
David Sadler
David Sadler
09:40 15 Nov 18
Really know their stuff and their response times are very good.
Andy Cole
Andy Cole
11:45 06 Nov 18
Our partnership with WellData has enabled us to resolve critical database problems. Their attitude and service makes them a natural extension to our own technology team, which extends our capability in a critical area of our business.read more
Steve Fenton
Steve Fenton
10:56 05 Nov 18
Having worked with the team at Welldata for approaching 17 years, my company and I have always found them professional, reliable and generally a great firm to work with.read more
John Lynes
John Lynes
14:00 04 Oct 17
WellData provide an excellent level of support and their team are very knowledgeable and always go the extra mile. I would wholeheartedly recommend them.read more
Stefan Parrott
Stefan Parrott
13:12 19 Sep 17