Migrating more than 250 SQL Server databases to Postgres

Written by: Cristian Córdova

The last Q of 2023, our DevOps team along with the engineering and product team of our client, Zinkee, embarked on a quite big challenge. Migrate more than 250 SQL Server databases in AWS and GCP to Postgres in AWS RDS.

The problems

After a joint analysis, we reached the conclusion that the architecture at that time had several problems due to SQL Server:

• License payment for each instance of SQL Server to take advantage of the server resources.

• Limitation of 100 databases per instance.

• High complexity to connect with third-party analysis tools or non-Microsoft proprietary.

• Limitation to use certain data types due to the engine's null or low compatibility.

• Very high cost in the Cloud.

Given all these problems, it was decided that the best solution was to proceed with a migration to Postgres. Not only did it solve all these problems but there was the possibility of improving the current performance of the platform.

Execution plan

Organizing a task of this magnitude is very complicated, as it is only possible if all the members of the teams participating in this challenge are committed and in constant communication.

Before carrying out the migration, a strong refactor of various development-level components had to be done, since without this the migration could not be carried out.

After this, a series of automations had to be created that would allow the creation of automated tasks capable of obtaining the SQL Server databases, generating the migration tasks, and subsequently loading the adapted database to its corresponding location in Postgres.

Our DevOps team generated all the necessary scripts to carry out these tasks. With the support of the AWS DMS service, we were able to generate hundreds of migration tasks simultaneously.

Each of these tasks obtained a database from a customized source in SQL Server, loaded it into the DMS service with all the necessary configuration, and prepared the destination. Creating the database and the necessary extensions so that, in the end, the DMS task could successfully restore the migrated databases.

Migration process

With everything ready, it was decided to carry out several partial migrations in order to quickly detect and correct errors and also not affect the service.

In order to carry this out, several databases hosted in GCP had to be migrated to AWS, parts of the infrastructure had to be adapted, and the state of the infrastructure had to be constantly monitored in order to act before any problems arose.

After intense weeks of work together with the engineering team of Zinkee, we finally carried out that migration without any serious issues. The migration had been successfully completed.

Time to measure results

A prudent time after the migration and with sufficient data to assess the state of the platform, we have obtained unbeatable data that reflect the effort and work carried out by everyone.

Better management of resources (CPU) of the database servers:

Improvement in the response time of Zinkee's applications:

Significant reduction of costs in AWS:

Conclusions

The migration has been a complete success. Helping our clients to solve such big challenges is largely what defines us as a DevOps Partner. A team dedicated to your infrastructure, working hand in hand and day by day with your company's teams.

💙 Kudos to the entire Zinkee team for the great work and the huge challenge we have solved together.