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:
This is what the tool looks like once opened:
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:
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:
Once you’ve created a project, you’ll notice that some items on the toolbar are no longer greyed out:
Step 4: 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’ve 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 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:
You will then be prompted to enter the SQL Server details referenced earlier. Once you’ve entered the required information, click on Connect:
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.
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.
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.
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.
<< Back to Knowledge Centre
