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 complete an oracle database health check to review your current Oracle Licensing position and 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.
<< Back to Knowledge Centre
