We’ve been approached by two clients in recent months to help them transition legacy Oracle systems to the Azure platform. In the previous blog, we explained how our tiered web applications make it easy to maintain the software we develop. And indeed, to migrate from one database to another. We asked Eric Wijnands to tell us about one of his current projects, moving a Banking solution from Oracle to an Azure SQL database.
Who’s Eric?
Eric has been part of BSL for more than 20 years and now works as System Architect. He’s met many of our clients, and led many BSL development projects. He has a wealth of development experience. Although he remains faithful to Windows, he’s worked on many server platforms using a range of technologies.
We asked Eric about his first impression of Azure
Learning about Azure was initially a little daunting.
Azure offers a family of managed cloud services that use the SQL Server database. There are several flavours of Azure SQL, ranging from an Azure SQL database, through Azure SQL managed instances, to a dedicated SQL Server on VM.
For BSL projects, the most relevant platform is the Azure SQL database – it’s sometimes referred to as a Platform-as-a-Service (PaaS). It supports most of the SQL Server features with which we are familiar. However, it’s a cloud service provided by Microsoft. They offer 99,995% availability, built-in backups, patching and recovery. So all the benefits of SQL Server, without the admin, or the need for an on-premise server. What’s not to like? And as a “pay-as-you-go” service, it can be instantly scaled up to meet demand.
So, what about moving SRS from Oracle to Azure?
We’ve frequently used Oracle for our developments, as it contains features that are not available in other databases. However, BSL has always been flexible, so for some clients, we’ve also built and supported SQL Server solutions for several years.
We’re familiar with both SQL dialects (Transact SQL for SQL Server, PL/SQL for Oracle). We are also aware of the different data types supported in both databases. Nevertheless, Azure SQL database service was new, so our first steps migrating SRS included setting up Azure accounts, creating simple test databases, and updating our Java DB libraries to connect with Azure SQL.
Are there any tools to help you make the transition?
We initially used a Microsoft tool (Microsoft SQL Server Migration Assistant for Oracle, or SSMA) to convert the Oracle Tables and Packages. While this successfully converted many of the data structures, we were less impressed with the code translation. Microsoft tries very hard to emulate missing Oracle features using functions. Unfortunately, the generated code is difficult to read and maintain in the future.
We then tried the SQLines open source tools. SQLines can help you transfer data, convert database schemas (DDL), stored procedures, functions, packages, triggers, views, queries and SQL scripts from Oracle to Microsoft SQL Server and Azure SQL.
With the online version of SQLines, we converted all the Oracle packages. The converted code was much more readable than that produced by SSMA. It is a more one-to-one translation of the Oracle code. Furthermore, with their SQLines Data tool, we not only transferred the data from Oracle to SQL Server but also used the scripts to re-create the tables in Azure SQL Server, including the column comments and indexes.
Were there any specific challenges?
The Oracle type ORDImage does not exist in Azure SQL Database. We use this to store binary contents (such as images) and the mime-type of the content. In Azure SQL server we created new tables, replacing this data type by two separate columns—one for the binary data, the other for the mime-type.
We optimised some data types. For example, we replaced Oracle NUMBER(1) attributes (intended to store Booleans) to the SQL Server type BIT. This change saves space and can improve performance.
When converting SP’s, what issues did you need to resolve?
We were converting about 20,000 lines of code, and just over 200 stored procedures. We had to check/edit almost every one of the methods after conversion by SQLines. Sometimes we needed to change our error processing so that our client applications can recognise the results. However, there are many other substantial differences in syntax. Both the Microsoft tool and SQLines take many of these differences into account, but our developers still have to review and modify the code after conversion.
Some of the most frequent issues we encountered are:
- Oracle automatically performs a conversion between numbers and strings, while SQL Server does not.
- Oracle makes no distinction between NULL and an empty string, while SQL Server recognises that distinction.
- XML manipulation in Oracle (XML DB) differs greatly from the techniques available within an Azure SQL database. However, using new Transact-SQL code, we could produce the same results.
In total, we needed around 12 person-days to perform these edits. We converted the entire database, sources and data in approximately 14 days.
And what’s your opinion of the Azure SQL database platform?
For Eric, his answer was short – and clear: “Using the Azure SQL Database works fine, and we can access the database easily. Once we’ve created the server and instance, we are confident that they will require little maintenance”.
Get in touch
Have you got plans to migrate your in-house system to Azure SQL database or any other databases mentioned in this article? Talk to us. Perhaps we can help. You are welcome to contact our relations manager at any time. She can set up an online call.
