SQL to Postgres

Question

SQL to Postgres

SQL Severe DBA

Hall of Fame

Points: 3579
More actions
May 5, 2018 at 12:38 am

#403320

I am working on Postgres performance data capture . I wrote some script to move the Data part from SQL server 2017 to Postgres and it worked fine .
Now I want to move objects /schemas/logins/jobs too , does any one has experience on the same . I know some tool exists but the amount of information on internet is really less.

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Eirikur Eiriksson SSC Guru Points: 182912 More actions · Answer 1

RatanDeep Saha - Saturday, May 5, 2018 12:38 AM
I am working on Postgres performance data capture . I wrote some script to move the Data part from SQL server 2017 to Postgres and it worked fine .
Now I want to move objects /schemas/logins/jobs too , does any one has experience on the same . I know some tool exists but the amount of information on internet is really less.

Haven't found a tool that does all of that automatically.
😎

The process is more or less along these lines:
1. Identify breaking features
2. Script all objects in scope
3. Amend the breaking features whilst editing / transforming the object scripts
4. For SQL Server logins, create the same and let the users set the passwords
5. SQL Server Agent Jobs need to be dissected and created from scratch on the target environment
6. Schemas and objects must be sanitised for the target environment, i.e. changing IDENTITY to SERIAL etc.
etc. etc.

Steve Jones - SSC Editor SSC Guru Points: 736270 More actions · Answer 2

This might help: https://wiki.postgresql.org/wiki/Microsoft_SQL_Server_to_PostgreSQL_Migration_by_Ian_Harding

There are tools that do this, but I think most cost money:
https://www.ispirer.com/products/sql-server-to-postgresql-migration
https://www.devbridge.com/articles/migrating-from-mssql-to-postgresql/#
https://dbconvert.com/mssql/postgresql/

SQL Severe DBA Hall of Fame Points: 3579 More actions · Answer 3

Eirikur Eiriksson - Saturday, May 5, 2018 3:25 AM
RatanDeep Saha - Saturday, May 5, 2018 12:38 AM
I am working on Postgres performance data capture . I wrote some script to move the Data part from SQL server 2017 to Postgres and it worked fine .
Now I want to move objects /schemas/logins/jobs too , does any one has experience on the same . I know some tool exists but the amount of information on internet is really less.
Haven't found a tool that does all of that automatically.
😎
The process is more or less along these lines:
1. Identify breaking features
2. Script all objects in scope
3. Amend the breaking features whilst editing / transforming the object scripts
4. For SQL Server logins, create the same and let the users set the passwords
5. SQL Server Agent Jobs need to be dissected and created from scratch on the target environment
6. Schemas and objects must be sanitised for the target environment, i.e. changing IDENTITY to SERIAL etc.
etc. etc.

I tried this method , its hit and miss . But this gave me starting point to get started . Identifying breaking feature was tricky as online tool is not completely accurate .

SQL Severe DBA Hall of Fame Points: 3579 More actions · Answer 4

Steve Jones - SSC Editor - Monday, May 7, 2018 10:00 PM
This might help: https://wiki.postgresql.org/wiki/Microsoft_SQL_Server_to_PostgreSQL_Migration_by_Ian_Harding
There are tools that do this, but I think most cost money:
https://www.ispirer.com/products/sql-server-to-postgresql-migration
https://www.devbridge.com/articles/migrating-from-mssql-to-postgresql/#
https://dbconvert.com/mssql/postgresql/

Thanks Steve , I tried the first and last tool it worked after few tweaks . The second blog has some interesting points mentioned too .