Blog
Azure SQL database migration | Migratie naar Azure
Azure SQL database migration | Migratie naar Azure

Oracle migration to Azure SQL database – Part 2

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.

Find out more?

Innovate, achieve more with better software.
Get in touch, no-strings attached.

Two BSL anniversaries in May, time for a party

Two BSL anniversaries in May, time for a party

Two BSL anniversaries this week. Senior developer Peter Verbaan has been a key resource within BSL for 15 years, and front-end developer Roderick Gadellaa for 5 years. Reason enough for a party, you might say, and that’s something we love at the “Bright Side of Life.” For now, Corona makes this impossible, so it’s an online drink and a virtual hug.

Designing a simple phone app for 1888 information numbers

Designing a simple phone app for 1888 information numbers

1850 BV has asked us to design and develop a new simple phone app for Android and iOS, designed to help people who have difficulty using modern smartphones. After all, not everyone is excited about all the hundreds of features provided by a typical smartphone. It’s hard for people who lack the technological skills to figure all this stuff out. And or course, many simply find tiny screens challenging.

Early to market? Try developing a Minimum Viable Product

Early to market? Try developing a Minimum Viable Product

Entrepreneurs frequently contact us with ideas for their ground-breaking app, web-service, or web application. Their market research completed, business plan ready, financial backing sorted. They want to start, and get their product to the market as quickly as possible. This is when we introduce the concept of an MVP or Minimum Viable Product.

The Entoen Nu app makes history lessons easy

The Entoen Nu app makes history lessons easy

Do you sometimes have trouble with Dutch history? The EnToen Nu app is intended for those interested in Dutch culture and history, particularly children from grade 5 up to and including secondary school. It can be used at school, and at home. We’ve recently updated the app, adding support for the latest version of the Canon of the Netherlands.

SRS: New Job Model and Access Governance Foundation

SRS: New Job Model and Access Governance Foundation

Since developing the original Signature Registration System (SRS) on behalf of a large international bank we’ve made many changes. Migrating SRS to an Azure SQL cloud service is our current focus, but earlier this year we worked on two other infrastructure changes: Introducing NJM (New Job Model) and AGF (Access Governance Foundation) support in SRS.

Oracle migration to Azure SQL database – Part 1

Oracle migration to Azure SQL database – Part 1

Is Azure the flavour of the month? We’ve been approached by two clients in recent months to help them transition legacy Oracle systems to an Azure Cloud database. We’ve been building tiered database solutions for many years, with Front-end web applications calling web-services that use Oracle databases for storage. Just how easy is it to migrate these applications to Azure?

BSL creates a new Corporate site for WCC Group

BSL creates a new Corporate site for WCC Group

The WCC Group is a leading provider of advanced solutions for Public & Private Employment Services and ID/Security government agencies. Two years ago we designed and developed a dedicated Careers site to help find the specialized personnel needed to support their rapid expansion. This year they asked us to create a new, modern, responsive Corporate site.

Bright Side of Life Director – Martyn Simpson – Part 2

Bright Side of Life Director – Martyn Simpson – Part 2

In his previous blog, Martyn introduced us to his time working for Computervision/CIS, creating custom solutions for their clients. Experiences that he uses daily within BSL. After leaving CV, he continued to travel, working firstly with a company you’ve maybe never heard of (although you’ve probably used their products). And then with a company that you all know: Apple Computer.

Bright Side of Life Director – Martyn Simpson

Bright Side of Life Director – Martyn Simpson

So rounding off our current series of Bright Stories, it’s the turn of Martyn, our director. Martyn created the Bright Side of Life more than 25 years ago, and he’s been developing software since 1977. It’s not easy to condense 43 years of software development into a single blog, so in this first part, we find out just why he became a software developer, with some “Lessons Learned” on the way.

Front-end Developer – Roderick Gadellaa

Front-end Developer – Roderick Gadellaa

Continuing our “Bright Stories”, this week we turn our attention to Front-end Developer, Roderick Gadellaa. Someone with a very sweet tooth, and who enjoys solving design and UX problems. You’ll frequently find him on Twitter, tweeting about design changes and concepts. Here at BSL, Roderick’s enthusiastic whistling and singing are part of our soundscape!

PA / Customer Relations Manager – Caroline Bos

PA / Customer Relations Manager – Caroline Bos

As part of our “Bright Stories” series, it’s now the turn of Caroline, PA / Customer Relations Manager at BSL. We tell you why her varied background, outgoing personality, and talent for organization make her ideal for the role. Connecting and communication with people is in her DNA! Having worked nearly 8 years at BSL, she plays a pivotal role in our organization.

Software Developer – Jurjen Schröder

Software Developer – Jurjen Schröder

Our “Bright Stories” campaign is your chance to get to know some of the talented individuals that make up the Bright Side of Software. This week, the spotlight is on one of our software developers: Jurjen Schröder. An opportunity to find out more about Jurjen, and at the same time discover why he keeps some very unusual housemates!

Agile working is a success even during Corona

Agile working is a success even during Corona

Agile working is ideal for teams working remotely. Although we’ve used Agile for several years, there’s always room for improvement. Flexibility is part of this approach. Each client has their preferences, and so we refine our working methods to suit each customer. Fortunately, this type of refinement is more or less built-in to Agile: The Sprint Retrospective…

NBD’s LiteRom and Uittrekselbank online websites refreshed

NBD’s LiteRom and Uittrekselbank online websites refreshed

Agile development is part of our culture. During two recent Sprints, BSL delivered new versions of NBD’s Literom and Uittrekselbank online web services. LiteRom contains thousands of book reviews, as well as many articles about authors. Uittrekselbank subscribers can access thousands of extracts about books, as well as detailed author profiles. Find out more in our blog…

Working from home, but staying connected

Working from home, but staying connected

We’re working from home as much as possible due to the Coronavirus (COVID-19). We’re doing all we can to keep Business as Usual. Technical innovation is part of our DNA, and so we’re perhaps finding this easier than others. Nevertheless, we are having to change the way we work in response to these challenges. Our blog tells you about some of the steps we are taking.

Eric Wijnands, Designer and Developer, with a love of Jazz

Eric Wijnands, Designer and Developer, with a love of Jazz

BSL has always looked for designer and developer candidates who have their own stories to tell. Rounded individuals who offer more than just IT skills. Our work frequently begins with understanding the work carried out by our clients. So we supply inventive teams that deliver usable, productive solutions – not just code. Offering designers and developers with wide interests outside of IT.

Long-term relationships brings rewards to clients

Long-term relationships brings rewards to clients

2019 is a year where our long-term relationships with clients have kept us extremely busy. BSL provides highly skilled teams that complement our clients’ needs. As a result, we respond quickly to new demands. Designing, developing and delivering unique applications. This year we’ve worked on many new developments for existing clients.

Moscow performance for Marc Stoffels

Moscow performance for Marc Stoffels

Before Marc Stoffels made the move into IT, he was an actor. One of the theatre companies where he played, Het Houten Huis was recently invited to bring their performance to Moscow. A trip to Moscow is an experience in itself, and BSL didn’t want to deprive him of the opportunity. But with one condition – we asked him to write a blog about his trip!

Conversational Design helps increase conversion rates

Conversational Design helps increase conversion rates

Many websites ask visitors to share their contact details. And for many years the best anyone could come up with is a contact form. There are ways to make such forms more inviting, but a contact form shares many characteristics with a nightclub bouncer – it keeps people out. So we’ve started using Conversational Design UI’s. A technique that puts website visitors at their ease…

Don’t know your MVP from your SUV, read on…

Don’t know your MVP from your SUV, read on…

At the Bright Side of Life it’s in our blood to help clients to think about their needs, and to work with them to define the features needed in a first release. Going for an all singing and dancing first release is rarely the right approach. Focus on what you need – an MVP demonstrating great value to your users. It will save you money, gain insights into your users’ needs, and help to create a better product.

Summer Event – BSL and the Soldier of Orange

Summer Event – BSL and the Soldier of Orange

Our summer event this year was an opportunity for Martyn (Director, BSL) to finally experience a little Dutch culture. He’s not yet ready for the full Marco Borsato/Andre Hazes experience. So on June 6, we tore ourselves away from our work, and stood in the sunshine outside the office, waiting for a coach. A coach that was going to take us all to Katwijk, and the Soldier of Orange!

Keylane Careers – a Recruitment Challenge

Keylane Careers – a Recruitment Challenge

Recruiting talent for IT service providers is not easy. Good developers and designers are scarce. BSL uses our own website and several social platforms to find new talent. But when you’re a multinational, looking to recruit talent across Europe, you need to “Think Different”. And that’s just what the Keylane Careers team has been doing.

BSL team at the NN Rotterdam Marathon 2019

BSL team at the NN Rotterdam Marathon 2019

In February, BSL received an invitation to take part in a charity Business Run during the NN Rotterdam Marathon on 7 April. Participants could sign up for a 10K (1/4 marathon), with the goal of raising money for a good cause – the Linda Foundation. This charity helps make life a little brighter for less fortunate children in the Netherlands.

Oracle – MySQL Bootcamp in Dusseldorf (the boys on tour)

Oracle – MySQL Bootcamp in Dusseldorf (the boys on tour)

BSL has a lot of experience with Oracle databases and has been an Oracle partner for years. We were recently invited by Oracle to a MySQL Bootcamp held in Dusseldorf. The purpose of the Bootcamp was to learn more about the MySQL Enterprise edition, the commercial edition of MySQL. Two of our developers want to share their experience…

Will the proposed ‘EU Copyright Directive’ endanger the Internet?

Will the proposed ‘EU Copyright Directive’ endanger the Internet?

While the Brexit soap has all our attention, the EU is working away on their own controversial proposal: their EU Copyright Directive. This directive will endanger the Internet. A claim made by many journalists and technical gurus, including Tim Berners-Lee and Jimmy Wales.

Mandate fraud costs Pathé more than US$ 22 million

Mandate fraud costs Pathé more than US$ 22 million

Pathé recently lost millions in an internet scam (named Mandate fraud) that targeted their Dutch office. Several emails, apparently sent from the personal account of the Pathe CEO asked the Dutch office to wire the money in four tranches to a bank account based in Dubai. SRS software developed by BSL could have helped prevent the fraud.

Front-end Developer with Marketing skills – Gabriella Pap

Front-end Developer with Marketing skills – Gabriella Pap

We’d like to welcome Gabriella, the newest addition to our team. She’s working as a junior Front-end Developer, but will also be putting her online marketing skills to good use. Gabriella wants to become a Full-stack developer. Her new colleagues at the Bright Side of Life will be very happy to help her reach this goal.

A look back at BSL in 2018

A look back at BSL in 2018

The year is almost over, so time to look back at 2018. It’s been a good year, with many new orders and several new clients. We’ve also welcomed a number of new colleagues. And our workload was so great that we collaborated for the first time with a third-party developer. He helped us deliver two new web applications over the summer.

Pulse – a new Responsive web application from BSL

Pulse – a new Responsive web application from BSL

Staying up-to-date with the latest news is important in almost any business. But it’s essential for PwC consultants, who need the latest news to better serve their clients. BSL developed Pulse, a custom-made information platform to aid them, and we recently delivered a new Pulse responsive web application.

Rare Care project through the eyes of a Front-end Developer

Rare Care project through the eyes of a Front-end Developer

Because we frequently develop custom software at BSL, we often become closely involved with our clients and their goals. Our developers recently completed the RareCare.World website. Here, you can read about how one of our front-end developers experienced working on the project.

4

Previous

5

Next

Any questions?

Do you want to get an estimate for developing your custom software. Or advice on creating new websites, or integrating web applications with existing systems. We're happy to answer any questions you may have, no strings attached. Give us a call, or get in touch at sales@brightside.nl!

    Get in touch

      Contact BSL
      Contact BSL