What is it like to change the underlying database on a significant real-life project?

Question

Have you ever seen a significant non-theoretical project where the underlying database engine had to be changed? Was it a major undertaking, taking months of work, or was it conceived and done in a day/week?

I have seen a few online posts/messages conveying one to write database-handling code in such a way as to allow an easy change of the underlying database engine, perhaps by modifying a few key files and without changing the "rest of the code". The underlying theme is that "what if" one has to stop using MySQL and start using Oracle? Or PostgreSql? Or.. I don't know, MongoDB, NoSQL, flatfile, BerkeleyDB?

Did that ever happen on a real-life project where you did have to change the underlying database engine? If so, did the existing codebase allow to do so easily?

Does such thing happen often in real-life scenario? I haven't seen it myself. I am also pretty sure that large companies will not bother changing their database (think of Facebook deciding to change their main application database engine), and if they do it will probably a major major undertaking.

In the end I wonder if one can safely write their code as if they are married to the current database of choice - for example, using mysqli_query() all throughout their codebase for MySQL.

Answer 1

I've only seen this kind of change-out occur once, from an Access database to a SQL Server database. It was very nearly seamless, and produced benefits far beyond the effort required to make the change-out.

It's more common to change out the ORM than it is the database. If you're changing the database, ORM's can provide a buffer zone, because many of them use drivers to communicate with the database, rather than communicating with the database directly. If the SQL is ANSI compliant, such a changeout can be relatively seamless, as you can simply change the driver.

The bigger problem arises when vendor-specific SQL is used. The lure of better performance often outweighs the seemingly intangible benefits of keeping your system ANSI SQL compliant, and once you go down that road, you lock yourself into that specific vendor. Changing the database becomes much more difficult because you then have to rewrite all of the vendor-specific queries.

Answer 2

I’ve done this in a microservice context: for performance reasons the team decided to exchange one NoSQL database for another. We were able to perform this change within one two-week sprint. This was largely possible because of good separation of concerns: the code responsible for storing and reading the data was well separated from business logic, we had separate classes for business objects and persistence / DAO, etc. Another factor was that the DB was used just for storing and reading entries based on some simple criteria and we did not have to use very advanced features which tend to differ more between databases than basic query/insert operations. And of course we didn't have any stored procedures.

On the other hand, I've seen systems with really large and complex databases where the DB code was scattered all around the codebase and which I can hardly imagine being able to change the DB engine within any short period of time. One possibility to handle such a situation is to look for pieces of data which are mostly independent from others. Such fragments might be possible to separate into a different DB, and step by step migration might become possible. But it would certainly not be a simple task or one which could be done overnight.

Answer 3

What is it like to change the underlying database on a significant real-life project?

It depends on the project.

Most applications don't have the database at it's core, the application just needs to persist data. A database is chosen because... that's what a database does. The choice of the database is most of the time based on price/free, experience of the team with previous databases, SQL vs NoSQL, etc. With this kinds of applications the developers make a modest job of isolating the persistance layer and so the specifics of the database leak into the application. Depending on the size of the leak, moving to another database can take weeks or months.

Then there are applications that carefully isolate the database implementation from the rest of the code. Long ago I've worked on a product that could run with any relational database. We were using Hibernate and carefully wrote the code to not depend on any specific features. We often changed the database implementation for each client. Oracle, MySQL, Postgres, didn't matter. Changing it took a couple of hours.

And then I worked on apps that were just thin layers over a MS SQL Server. 99% of the application was in the form of stored procedures. Time to change the database engine? Years!

So it really depends. If you need to change your database look at your specific app. Do a thorough analysis, do an estimate, and see what number you end up with. Asking strangers on the internet will not help in this case :)

Answer 4

Been there, done that.

BTrieve -> Access -> Interbase -> Oracle during the course of a single project.

The first two only during the prototyping phase. Interbase lasted a few months into main development until we hit a few serious limitations (20 years ago it may be better now) then we jumped to Oracle. The transition wasn't all that painful because it was early in the project and Inter base didn't have very much proprietary stuff to trap us (stored procedures had to be written as DLLs). Also, Oracle donated a few days of one of their consultants to help us.

Years later I worked on a mainframe decommissioning project. DB2 -> Oracle but that was mostly ground up rewriting.

The main issues you will encounter will probably be more with the non SQL Database features like stored procedures which can be implemented very differently from DB to DB. Try shifting PL-SQL to C#... Compared to that, changing SQL syntax and deleting a few /RULE/ comments is nothing.

I take the view that you assume that you are not going to switch databases unless you are using something experimental or non-mainstream. The lack of real world examples here is a good indication of how uncommon it is. On that basis, making provision for something that may never happen and crippling your application in the process doesn't make sense.

Take advantage of everything your database gives you. Plan the database at least as thoroughly as your application. It will probably outlive the application. Learn how to tune SQL and do it when you have to.

Answer 5

I've seen one large real-world project that tried to change from an Oracle database to some IBM big-data non-relational database. The project took over a year, went massively over budget (we're talking millions here) and ultimately failed.