The Heterogeneous Database Migration

At 5 PM, my CTO gave me a task.

We had to migrate our databases at DaanVeda from Azure to GCP.

I thought I knew how this would go.

Export the database. Move the backup. Import it into the new cloud. Done.

It sounded easy because, at first, it actually was.

Then came one small detail:

We were not moving from MSSQL to MSSQL anymore.

We were moving from Azure SQL using MSSQL to PostgreSQL on GCP Cloud SQL.

That one change turned a normal cloud migration into a completely different problem.

The Plan That Died at 5 PM

Before this, I knew one reliable way to move an MSSQL database.

You can export it as a .bacpac file, store it locally or in Azure Blob Storage, and import it into another SQL Server database. A BACPAC contains the database schema and table data, so it is very useful when the database engine stays the same.

Microsoft documents this flow for moving databases between SQL Server and Azure SQL systems.

Our first option was to use MSSQL on GCP Cloud SQL.

Technically, that would have kept the migration simple.

Financially, it did not make sense for us. After checking the expected cost, we decided to use PostgreSQL on Cloud SQL instead.

Now the .bacpac approach was gone.

PostgreSQL cannot simply restore an MSSQL BACPAC. The two databases speak different dialects, use different data types, and represent some schema features differently.

I had migrated data between two databases before.

But I had never migrated production databases between two different database engines.

The Night of 40 Open Tabs

The conversation happened at around 5 PM.

At 6 PM, I started researching.

I kept going until 6 AM.

Documentation. YouTube videos. GitHub repositories. Old blog posts. Stack Overflow answers from many years ago.

Every article made the migration sound straightforward:

1. Convert the schema.
2. Transfer the data.
3. Change the connection string.
4. Celebrate.

But every tool had conditions written somewhere deep in the documentation.

Supported source. Supported destination. Supported database version. Full load. Change data capture. Network access. Data-type limitations.

That night taught me the first lesson:

A tool supporting MSSQL and PostgreSQL does not automatically mean it supports your exact MSSQL-to-PostgreSQL path.

Day 1: GCP DMS

My first choice was obvious.

The destination was GCP, so I tried Google Cloud Database Migration Service.

Keeping everything inside one cloud sounded clean. No additional provider. No extra moving parts.

But the migration path did not fit.

Google DMS supports several homogeneous migrations and selected heterogeneous migrations. At the time of our work, Azure SQL using MSSQL to Cloud SQL for PostgreSQL was not the path I needed.

I still spent the day exploring it, checking options, and trying to make the pieces connect.

They did not.

Day 1 was gone.

It felt like a wasted day, but it removed one wrong path from the list. That matters when you are working under pressure.

Day 2: pgloader Looked Like the Answer

On Day 2, I moved to pgloader.

pgloader is a popular open-source tool for loading data into PostgreSQL. It can also transform some schemas and data types while moving data from another database.

This looked exactly like what I needed.

For some time, it even looked like it was working.

That was the dangerous part.

A database migration is not successful because a command starts printing progress. It is successful only when the complete schema and the complete data arrive correctly.

In our setup, pgloader did not give me a migration I could trust for all three databases.

Maybe I could have spent more time debugging and tuning it.

But I had production data, millions of rows, and a deadline. “Somewhat working” was not enough.

Day 2 ended with another failed approach.

At that point, I was tired.

But I also understood the problem better.

The Problem Was Actually Two Problems

Until then, I was thinking about the migration as one big task:

Move the databases.

It became easier when I split it into two independent jobs:

1. Convert the schema

Tables, columns, indexes, constraints, default values, and data types had to become valid PostgreSQL objects.

For example, an MSSQL type does not always have a perfect one-to-one PostgreSQL equivalent. Some types are easy to map. Others need a decision.

2. Move the data

After creating the correct destination schema, millions of rows still had to travel from Azure SQL to GCP Cloud SQL.

The tool converting the structure did not need to be the same tool copying the rows.

That separation unlocked the migration.

Day 3: The OG AWS SCT

Then I found AWS Schema Conversion Tool, or AWS SCT.

The funny part was that neither the source nor the destination was on AWS.

The source was Azure. The destination was GCP. And the tool that finally helped me was from AWS.

Cloud migrations have a sense of humour.

AWS SCT could inspect the MSSQL schema and convert it into PostgreSQL-compatible SQL. AWS documents Azure SQL Database as a supported source and PostgreSQL as a supported target for the tool.

I connected the databases, generated the assessment, reviewed the conversion, and converted the schemas.

Within minutes, the part that had felt impossible for two days finally started making sense.

Not every cross-engine schema can be converted blindly. Stored procedures, database-specific functions, computed fields, and unusual data types may still need manual work. AWS SCT also reports objects it cannot convert automatically.

Our schemas were manageable, and the converted structure gave me the foundation I needed.

For the first time in three days, I felt:

Okay. This can actually be done.

Days 4 and 5: AWS DMS Between Azure and GCP

The schemas were ready.

Now came the rows.

For that, I used AWS Database Migration Service, or AWS DMS.

Again, AWS was sitting in the middle of an Azure-to-GCP migration.

AWS DMS supports SQL Server as a source and PostgreSQL as a target. The databases do not have to live inside AWS, but the DMS replication instance must be able to reach both endpoints securely.

The flow looked like this:

Azure SQL (MSSQL)
        |
        v
AWS DMS replication task
        |
        v
GCP Cloud SQL (PostgreSQL)

I created the source and destination endpoints, tested the connections, configured the migration tasks, and started the full load.

Then I watched the tables.

Some completed quickly. Some took time. Some needed attention.

Across the databases, there were millions of rows.

This was not the exciting part where you write clever code. It was the patient part.

Check the task. Check the table status. Check failures. Check the destination. Repeat.

By this stage, most of the migration was working.

But one database was still pending.

Day 6: The Two Tables That Refused to Move Fast

The final database contained two tables with a very large amount of data inside individual rows.

The number of rows was not the only problem.

The size per row mattered.

A table with many tiny rows and a table with fewer but very heavy rows behave differently during migration. Large text or binary-like payloads can change throughput, memory pressure, network transfer, and how fast a migration task completes.

AWS DMS was moving those tables, but not fast enough for our timeline.

This is where I stopped trying to make one tool solve everything.

There were only two problematic tables.

So I used the boring solution.

I downloaded their data and uploaded it to the destination separately.

Manual work is not always bad engineering.

If 99% of a migration works through automation and two known tables need a controlled fallback, manually handling those tables can be safer than redesigning the entire migration at the last moment.

I worked through Sunday too.

By Day 6, all three databases were migrated.

Millions of rows had moved from MSSQL on Azure to PostgreSQL on GCP.

Moving Rows Is Not the Same as Proving Them

After a large migration, seeing tables in the destination feels good.

But it proves almost nothing.

Data accuracy was the main focus.

At minimum, I wanted to check:

• Did every expected table exist?
• Did source and destination row counts match?
• Were important IDs preserved?
• Did nullable values remain nullable?
• Were timestamps and time zones handled correctly?
• Did decimal values keep the required precision?
• Were large fields complete?
• Did relationships still point to valid records?
• Could the application read and write real workflows correctly?

Row counts are a useful first check, not the final check.

You can have the same number of rows and still have truncated strings, shifted timestamps, incorrect booleans, missing defaults, or broken relationships.

The migration was complete only when the data and the application behaviour both looked correct.

Prisma Quietly Saved Us

There was one more migration waiting after the database work:

The application.

If an application is full of raw MSSQL queries, database-specific syntax, and driver behaviour, moving to PostgreSQL can become a second major project.

Our application used Prisma.

That was a win.

Prisma gave us a database abstraction and a schema we could update for the PostgreSQL provider. We still had to review the schema, regenerate the client, update configuration, and test queries properly.

But most of the application did not care whether the rows lived in MSSQL or PostgreSQL.

The application-side migration was much smaller than the data migration.

This does not mean an ORM makes every database portable automatically. Raw queries, unsupported native types, provider-specific features, and behavioural differences can still create work.

But compared with tightly coupling the whole codebase to a raw MSSQL driver, Prisma gave us a much better starting point.

So, What Actually Broke?

Not the production database.

What broke were my assumptions.

“A cloud-to-cloud migration is mostly downloading and uploading”

Only when the database engine remains compatible.

“One migration tool will handle everything”

Schema conversion and data movement are different jobs. Different tools may be better at each.

“If the migration starts, it is working”

Progress logs do not prove completeness or accuracy.

“Row count is the main factor”

Row size and data shape can matter just as much.

“Using an ORM is only about developer experience”

It can also reduce how deeply an application is coupled to one database vendor.

What Those Six Days Taught Me

Before this task, I knew how to move data between two similar databases.

After it, I understood heterogeneous database migration.

I learned how to:

• Separate schema conversion from data transfer
• Compare tools using the exact source and destination combination
• Use AWS SCT to convert MSSQL schemas to PostgreSQL
• Use AWS DMS to move data across two other cloud providers
• Recognise when row size is becoming a bottleneck
• Use manual migration as a controlled fallback
• Validate data instead of trusting a green status
• Appreciate portability at the application layer

Six days earlier, I did not know how I would complete the task.

I lost sleep. I lost a Sunday. Two approaches failed.

But all three databases moved.

And I came out knowing something I could not have learned by migrating another .bacpac.

The best engineering tasks are sometimes the ones where your first plan stops working before you even begin.