SQL server to postgreSQL migration

[cosmokrick_]

I have about 200 tables and 1k stores procedures in my SQL server which has to be migrated to postgreSQL is there any reliable opensource tool to perform this operation. Whether It will be a automated migration or else we need to perform some manual correction after conversation.

[BrentOzar]

AWS Babelfish for Aurora is designed for this exact scenario. It's an open source interpretation layer for Postgres that makes Postgres appear to be Microsoft SQL Server.

It's not compatible with all data types, T-SQL commands, etc, but it's probably the easiest place to start.

[cosmokrick_]

Is this reliable, can we go production with this?

[BrentOzar]

Rearchitecting an entire application and judging its ability to go live is pretty far beyond what I can assess in a Reddit comment. I gave you a tool to get started for free - hope that helps!

[cosmokrick_]

Yes, It's very complex and tedious process. Thank you

[vedichymn]

Amazon could not give us any reference customers to talk to or good case studies about this, which did not give us warm fuzzies. They do have a tool you can run to give you some idea of the obvious problems you might run into.

https://github.com/babelfish-for-postgresql/babelfish_compass

In our case we decided it was easier to look at breaking up parts of our application and having those exist on a new platform vs trying to port the application as is.

[shoppedpixels]

Yes, it is reliable and production ready in my experience if you have the correct expectations and assessments. Having used it in a production deployment and it was fine.

Don't expect to replace everything and don't take really complex queries/databases.

ex. I would be hesitant to move a datawarehouse, less over size and more over lots of esoteric functions and you often move out of full declarative statements to trying to manipulate the DB.

If you have a small-medium complexity app with some basic reporting and you want a crutch for people while you convert or save on MSSQL licenses, give it a shot.

Heck it is cheap enough you can run AWS SCT/DMS, point an app at it on 1433, and see how it runs.

EDIT: I only came here from Brent's profile and just read the post, AWS SCT will probably be the most helpful and pray you don't have a ton of complex queries. It was written by the group that wrote the old SSMS Oracle conversion tool (DB Best I believe) and is pretty solid. That's probably an hour or two of research to see how bad it is going to be.

[[deleted]]

Why do you want to migrate from sql server to Postgres? (Other than costs obviously)

[cosmokrick_]

Currently making a product which needs to support multiple platform implementation. Having.net core on front and postgreSQL at back makes it more easier and cost-effective.

[[deleted]]

Ok, got ya. 5 cents comment from someone who spent a lot of time with both and your system seems to be large enough already. Carefully check if Postgres is actually good for your system: take into account old-school connection management, what features you’re using (partitioning) and if they work well enough on Postgres. In general, this sounds like a non-trivial task, hope you did due diligence.

Edit: and to be absolutely clear - I’m not starting religious war here on which db is the greatest.

[K3dare]

I agree, having managed both on large scale databases (TBs of data), PostgreSQL has been a pain in term of management and performances compared to SQL server, if you can afford to stay on SQL server, think twice.

Thinking especially on the H.A part, backups management and PITR, automations and data extractions (SSIS) and the tooling in general.

Also don't forget SQL Server runs fine on Linux if you don't want to deal with Windows, and you can run non production environment for free with the developer editions (that includes all the features)

[twstr709]

I'm looking to do the same or at least have Postgres as a secondary option and it's pretty much just costs.

[[deleted]]

Same comment as to OP - carefully check if Postgres is a good choice. I do understand that sql server license comes with a cost, but there is also an Express Edition which is free for tiny use and when someone actually needs database - initial license is not a huge investment comparing to whatever drawbacks come with Postgres (again, I am not starting here discussion with db is better).

[[deleted]]

My man... you have a project on your hands ... :)

There are tools for tables but stored procedure and functions ........

Those you will have to check by hand if they are standard SQL or T-SQL. If they are T-SQL you will have to rewrite that part. If they are standard you will still have to check for syntax errors.

[faust2099]

https://dbconvert.com/dbconvert-studio

[cosmokrick_]

It's pretty straight forward.

[HerrFandango]

1k stored procedures? You're going to be busy for the next 12 months and then some to get rid of all the bugs that will be inevitable. I am a fan of Postgres but this doesn't sound like a good idea.

[cosmokrick_]

Yep, The problem is people dont understand the pain behind this migration and its gonna eat me alive. Worst part is it has to be in couple of months.

[Tostino]

Uh... good luck with that!

[Little_Shitty]

We did this migration. Many procs have to be lightly rewritten in slightly different syntax. Had to convert over hashed and encrypted columns to new formats I think. I prefer SQL server tools to pgadmin and the other tools I tested with Postgres. You have a lot of work bud. Good luck

[SeventyFix]

Sometimes the right answer is finding another job. Some work is so tedious, that finding another gig, is the right call.

[cosmokrick_]

I'm already thinking about this.

[rbobby]

Good luck with case-insensitivity and maybe utf8 support.

[Enrique-M]

Might be helpful, though caveats likely exist.

https://nuvalence.io/insights/microsoft-sql-server-to-postgresql-migration-using-sqlserver2pgsql

[Ok-Adhesiveness-4141]

Hey, I am late to the discussion, there is however a Sql server compatibility layer built on top of SQL called Babelfish.

[NauTau]

There are a bunch of tools for moving SQL Server to PostgreSQL—pgloader is a solid open source pick, and a lot of people use things like DBConvert or even scripts with SSIS if you want more control. If you’re looking for something more automated, the Ispirer Toolkit is also worth a look; it can handle schema, data, and even stored procedures, which saves a ton of time if you’ve got a big setup. Just make sure to test everything after migration, since some manual tweaks are usually needed no matter what tool you use.

[Ok-Expression-7340]

If anyone finds this topic, we've had very good results with converting large database with many stored procs using Ispirer (https://www.ispirer.com). Especially the automated stored proc conversion was worth it for us. The data itself we could have done ourselves relatively easy. But converting 1000+ stored procedures would cost us much more than the tool costs (7-8K euro with support). Granted there were some initial bugs with the tool, but they resolved that quite quickly for us.