How to migrate a database from Oracle to SQL Server

By Annette
17th November 2021

Migrating a database is not an easy decision nor one that businesses should take lightly.

However, there are instances where migrating a database makes good business sense. In particular, 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, at WellData we would first encourage you to get in touch with our Oracle DBA team who will bring Oracle expertise to your tech teams and review your current Oracle Licensing position to ensure that your business is not paying for features that are not required.

If however, you do decide that an Oracle to SQL Server database migration is what your business requires, here is a guide for you and your team to help with a smooth transition.

 

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) have many syntactical differences.

This means in most cases you can’t simply lift the Oracle database code and copy it into SQL Server.

The key to a successful migration is to identify any migration blockers, resolve, test and finally deploy to SQL Server.

To help with the transition, Microsoft offers a tool called SQL Server Migration Assistant (SSMA). This tool analyses the Oracle database and identifies what database objects can be automatically migrated (the tool will actually convert syntax where possible) and, importantly, it flags up any migration blockers that cannot be automatically converted.

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

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.

Importantly, our recommendation is to use a test version of your Oracle database for this purpose.  Whilst 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.

Before you start, in order to generate a report you will 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 are 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 are ready to begin.

 

Getting your SSMA report

Step 1: Download the tool

As mentioned before we recommend installing the SSMA tool on a test server, and 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.

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:

This is what the tool looks like once opened:

 

Step 2: 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:

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 you are working on and which database, as shown below:

Once you have created a project you will notice that some items on the toolbar are no longer greyed out:

 

Step 3: Connect to the Oracle Database

To do this click on Connect to Oracle:

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

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:

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:

 

Step 4: connect to a SQL Server

Whilst at this point you are not actually migrating, you still need to complete this step.

To do this click on Connect to SQL Server:

You will then be prompted to enter the SQL Server details referenced earlier; once you have entered the required information click on Connect:

 

Step 5: create a report

Once you are connected to SQL Server you will 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.

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, whilst 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 because you will also receive a list of these as part of the report.

Once this process has been completed you will 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.

These are the objects that cannot be automatically migrated.  The tool also estimates how long it would take to do the manual conversion.  Obviously, this time does not include time for any testing, which would be a crucial step before migrating a production database.

 

Step 6: 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 feel free to contact WellData and we can get involved in helping with or completing the migration process for you.

Also, 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, as performance and behaviour can in some cases be very different between the two platforms.

 

If you need help with testing, feel free to contact us at WellData as we can provide a suite of testing tools as part of our migration project.

 

Above all, for any information about how to migrate your database and any help with actual migration, don’t hesitate to get in touch. 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

Share

<< Back to resources

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