Migrating to Open-Source Database

Large Companies which have many databases each for different products/customers or use cases need many servers to install the database software. In addition, each database can be set up as a cluster of servers or have many replication instances. Using commercial databases whose license is calculated by the number of CPUs can cost a lot and impact company profit dramatically. In the Big Data Era where numbers of users and data grow dramatically, there is a need for additional HW resources which impact Commercial databases costs even more.

In recent years many startups and companies moved to open-source databases such as MySQL or PostgreSQL which reached their maturity and support many critical transactions systems.

Companies like Facebook, booking.com are using huge MySQL clusters to support their million users.

15 years ago, there were few reliable databases, and most companies used commercial databases.

In order not to “fall” technologically the companies choose to spend a lot of money for each license. Most of the development was done in the database using Stored Procedures, Functions, etc. because of the rapid development and release to production. Evidently, these systems consist of dozens of procedures and functions, dozens of views and many UDT (User-defined types).

Database Migration challenges 

Commercial databases usually contain lots of stored procedures, in most places the people who developed the code work elsewhere or have retired, and even before thinking about refactoring there is a big headache on how to reverse engineer the code. 

Thinking of “turn stones” and then immediately you think, “works? Do not touch”.

The fear of changing technology to innovate.

Making such a decision in a critical system that handles a mass number of transactions.

Open-Source Database Alternatives 

MySQL – is a very reliable and fast database, well-known, enterprise companies have dozens of production systems based on it. Administratively it is very rich (permissions, backups, replications, cluster) however, its database code capabilities are not as good as commercial databases or does not exist (like Table functions or UDT (User-defined types). Although not every feature exists or behaves differently there is an appropriate solution to bridge the disadvantage.  Good solution as OLTP database (does not support parallel query). 

Postgres – A very stable, fast database, rich with functionality and code layer (almost like commercial databases if not more) very well maintained, as evidence new versions are released frequently by adding features and bug fixes, it is excellent for databases support UDT, Table functions, parallel queries and more can serve as OLTP and OLAP (DWH). It has excellent performance, Lock management because of its architecture. 

What to do?

The world today is replete with reliable relational databases (MySQL, Postgres, etc.) that have undergone a “Baptism of fire” in the world’s largest companies. This is a guarantee that they are reliable and not because We say so. By choosing such a database, you can start considering 

  1. Moving to the cloud (RDS\ Virtual machine)
  2. Migrate also to Linux Operating system Instead commercial Operating system.

Now you say “Well, you discovered America, I know that, how do you convert all the code?”

Before that, we need to know how to choose a database.

How to Migrate to Open-Source Database

  1. Installation of AWS Schema conversion. The tool’s capability is to create the objects in the new database assuming it is installed in RDS. In general, the versions installed in RDS are not fully identical to the versions installed on-premise Therefore, not all components are properly converted. There are cases where the code is not fully converted because there are functions that do not exist or exist, but the tool does not know about them.

In our experience, if a procedure or function has not been fully converted by the tool, manual conversion or partial conversion can take anywhere from a few minutes to several hours.

  1. The tool produces a report that indicates which objects have passed completely, partially, or not at all. At the same time, the tool’s ability is to compare (What if? Scenario) the conversion to several kinds of databases. This ability allows you to choose a database more intelligently rather than emotionally.
  2. After the conversion, the data can be transferred to the new database by AWS Database Migration Service (DMS). It has the option to replicate (continuously) the data in the new environment because it knows how to detect changes by CDC.
  3. Checking the correctness of the converted code and comparing the result of the code in the existing system

Connecting the application to the new database and testing.

Most systems use ODBC, JDBC, OLEDB (very old systems that need to be painted blue and thrown into the sea) and ADO.NET.

In most cases, this is a change of adding the reference, changing classes and connection string.

How long does this adventure last?

As mentioned, it depends on the number of objects, however, in very large systems it is possible to start with one medium model that can reflect the duration of development of the whole system.

Conclusion 

SeaData has an experienced team with Oracle, SQL Server, PostgreSQL, and MySQL that can migrate the databases very fast and save migration costs. In the end the cost of the migration you can gain back in 1 year using the commercial database license.

Another benefit that we add to the process is optimizing the code and the database model (adding missing indexes, partitions and more) so at the end, you have a better and faster open-source database.

Contact us today and get a migration cost estimate within 5 days.