Automate migration of stored procedures from SQL Server to Postgres

user_5597631 0
Viewed 831

We are having around 75~ table and 100~ stored procedures. We have created a custom NodeJS app with Sequelize to migrate the tables and its data. But we wanted to migrate the stored procedures too.

The only possible options that we do have is, is to manually convert every stored procedure.

Manually converting each stored procedure is a tedious task. So is there any way other than manually converting the code? I hope someone can guide/help me with this.

FYI:

  • SQL Server version: 16+
  • Postgres version: 12+
database-migration postgresql sql-server
2 Answers

There soon will be, Amazon is launching an open-source tool under Apache to act as a translation layer between traditional SQL applications and a Postgres database. This translation layer allows your code to operate under its current SQL setup, but it gets translated for the Postgres DB. It's called Babelfish for Postgresql. It's slated for 2021, but it is not currently available. https://babelfish-for-postgresql.github.io/babelfish-for-postgresql/

user_80882670

There is absolutely no possibilities to automatically convert Transact SQL procedures to PG PL/SQL functions because of many lack of functionalities :

  1. PG does not do pessimistic lock that SQL Server uses by default

  2. PG do not support nested transaction that SQL Server support. In this case the behaviour will be different and the results not the same.

  3. String data have collations CI/AS by default in SQL Server that PG do not support completly (ICU collations are not supported for LIKE and raise an error as an example).

  4. PG does not conform to the SQL Standard regarding the string datatype. PG use only CHAR/VARCHAR. No NCHAR/NVARCHAR, but strinsg in PG are NCCHAR/NVARCHAR

  5. PG support function overloading that is not supported in SQL Server. The function using a generic code with the sql_variant datatype must be translated into function overloading

  6. PG does not make differnces between function and procedure (which is a lack of security). SQL Server does it...

There will be many other functionalities that is completly different, and I am writing a series of papers about the differences between PG and SQL Server. The first one is about performances of DBA queries, the secound about COUT performances and the third a complete panorama of functional differences...

user_126598720
Related