November 26, 2024 at 9:42 am
We are planning to migrate our on-premises SQL Server to an AWS PostgreSQL database instead of AWS SQL Server. However, I'm uncertain if this is a wise decision given the differences in data types and potential issues that may arise. Before proceeding with the migration, I would like to conduct an assessment and generate a report on any possible issues or blockers. While we can use SSMS for XYZ, I don't believe there is an SSMA tool for PostgreSQL. I'm also working on setting up the DMS service in AWS to help create this report. Do you have any better suggestions for easily generating this report?
November 26, 2024 at 4:26 pm
This was removed by the editor as SPAM
November 27, 2024 at 10:10 am
Thanks for posting your issue and hopefully someone will answer soon.
This is an automated bump to increase visibility of your question.
November 27, 2024 at 2:13 pm
Migrating between data platforms is a massive undertaking. It's not just data types, although that's a big part of it. You also have to take into account connection mechanisms within the code, the code itself, because the vast majority of your queries, even generated through ORM tools, will have to be rearchitected and reengineered. This goes way, way, beyond a simple mapping exercise. Are you using any unique SQL Server functionality, like, availability groups? Sure, AWS RDS has mechanisms to help replace that, but it's a huge architectural change.
Then, of course, there's simply knowledge of PostgreSQL itself. Are you ready to deal with how to set up your auto-vacuum (and please, do leave auto-vacuum enabled)? There are a whole slew of PostgreSQL internals that you'll need to learn, even though a lot of things are handled by AWS RDS, you'll still have to tune settings to your workload.
Then we have to talk about data movement and migration. No, not the work of doing it. That's honestly relatively straight forward. I'm talking about testing, simply everything, to be sure you haven't lost data.
Don't get me wrong, PostgreSQL is pretty cool. But you have to very clearly state exactly what you're hoping to achieve by leaving SQL Server and moving to PostgreSQL. And it had better be something beyond licensing cost, because, I assure you, 3-5 years of licensing costs will easily be eaten up by this migration process. It's not impossible by any means, but it's not easy.
Data has weight, gravity, momentum. You don't simply pick it up and toss it around. There are successful migrations out there. They are somewhat rare. Most people, if they're migrating, do it over time. Green field projects go to the new technology. Brown field, existing projects, either get left entirely alone, or, are rebuilt from scratch in the new technology. This way you can implement the new technology in new ways that are more appropriate to it. Then, brown field projects either limp along in the old tech stack, or, eventually are simply switched off because newer tools have replaced them. No actual migration occurs.
Best of luck.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply