migrate Sql Server database to PostgreSql

Hi,

At the company we work for, we migrated from Oracle to PostgreSQL
"community edition". We have no regrets. PostgreSQL allowed us to expand,
we configured it so now we have a better performance compared to before,
and we even have streaming replication to mirror our main database.

By the way, to perform the migration from Oracle to PostgreSQL we used
OmniDB [1]http://www.omnidb.com.br/en_index.aspx, an open source web tool for database management and migration,
written by ourselves. OmniDB supports many well-known RDBMS system, among
them Oracle, PostgreSQL and MS SQL Server.

However, OmniDB can only migrate tables, data, indexes, unique constraints,
primary and foreign keys. In our case, we had hundreds of procedures and
functions, and they were manually translated.

[1]: http://www.omnidb.com.br/en_index.aspx

Em 17h18 Qua, 18/01/2017, PAWAN SHARMA <er.pawanshr0963@gmail.com> escreveu:

Hello All,

I am using postgres 9.5 enterprise edition.

i want to to how to migrate Sql Server database to PostgreSql. what are
the things required for migration and what are the cron and prons of
migration.

-Pawan

--

William Ivanski - Microsoft MVP

On Thu, Jan 19, 2017 at 6:17 AM, PAWAN SHARMA <er.pawanshr0963@gmail.com>
wrote:

Hello All,

I am using postgres 9.5 enterprise edition.

i want to to how to migrate Sql Server database to PostgreSql. what are
the things required for migration and what are the cron and prons of
migration.

Well, how challenging the migration is going to be purely depends on
complexity of the the database and the how the Applications. As far as i
know, there is no open-source tool available to migrate SQL Server database
to PostgreSQL. You can try "sqlserver2pgsql" which migrates does not
migrate procedures.

Overall with my experience, i can say, migrating application would be most
challenging part.

- Preliminary analysis on the database, its object types, data types and
code complexity of the procedures. T-SQL procedures will need to re-written
and they can in-turn trigger application code changes which can be
challenging.
Data types and procedures can pose real challenges while migrating
applications.
- If applications being used against SQL Server are proprietary, then, you
need to ensure they work with PostgreSQL.
- Evaluate the amount of Application code changes to be done, that will
give you an idea on how difficult migration will be.

Hope that helps !

Regards,

Venkata B N
Database Consultant

On Thu, Jan 19, 2017 at 03:29:34PM +1100, Venkata B Nagothi wrote:

On Thu, Jan 19, 2017 at 6:17 AM, PAWAN SHARMA <er.pawanshr0963@gmail.com>
wrote:

Hello All,

I am using postgres 9.5 enterprise edition.

i want to to how to migrate Sql Server database to PostgreSql. what are
the things required for migration and what are the cron and prons of
migration.

Well, how challenging the migration is going to be purely depends on
complexity of the the database and the how the Applications. As far as i
know, there is no open-source tool available to migrate SQL Server database
to PostgreSQL. You can try "sqlserver2pgsql" which migrates does not
migrate procedures.

Overall with my experience, i can say, migrating application would be most
challenging part.

- Preliminary analysis on the database, its object types, data types and
code complexity of the procedures. T-SQL procedures will need to re-written
and they can in-turn trigger application code changes which can be
challenging.
Data types and procedures can pose real challenges while migrating
applications.
- If applications being used against SQL Server are proprietary, then, you
need to ensure they work with PostgreSQL.
- Evaluate the amount of Application code changes to be done, that will
give you an idea on how difficult migration will be.

Hope that helps !

Regards,

Venkata B N
Database Consultant

Hi,

For the procedure part, you might want to take a look at pgTSQL:

https://bitbucket.org/openscg/pgtsql

Here is another link with some examples. I have not tried them myself.

https://www.bigsql.org/docs/pgtsql/

Regards,
Ken

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

--
David Rader
davidr@openscg.com

On Thu, Jan 19, 2017 at 8:56 AM, Kenneth Marshall <ktm@rice.edu> wrote:

On Thu, Jan 19, 2017 at 03:29:34PM +1100, Venkata B Nagothi wrote:

On Thu, Jan 19, 2017 at 6:17 AM, PAWAN SHARMA <er.pawanshr0963@gmail.com

wrote:

Hello All,

I am using postgres 9.5 enterprise edition.

i want to to how to migrate Sql Server database to PostgreSql. what are
the things required for migration and what are the cron and prons of
migration.

Well, how challenging the migration is going to be purely depends on
complexity of the the database and the how the Applications. As far as i
know, there is no open-source tool available to migrate SQL Server

database

to PostgreSQL. You can try "sqlserver2pgsql" which migrates does not
migrate procedures.

Overall with my experience, i can say, migrating application would be

most

challenging part.

- Preliminary analysis on the database, its object types, data types and
code complexity of the procedures. T-SQL procedures will need to

re-written

and they can in-turn trigger application code changes which can be
challenging.
Data types and procedures can pose real challenges while migrating
applications.
- If applications being used against SQL Server are proprietary, then,

you

need to ensure they work with PostgreSQL.
- Evaluate the amount of Application code changes to be done, that will
give you an idea on how difficult migration will be.

Hope that helps !

Regards,

Venkata B N
Database Consultant

Hi,

For the procedure part, you might want to take a look at pgTSQL:

https://bitbucket.org/openscg/pgtsql

Here is another link with some examples. I have not tried them myself.

https://www.bigsql.org/docs/pgtsql/

Regards,
Ken

Hello -

pgTSQL enables you to run some parts of T-SQL code in PostgreSQL. Coverage
is not complete by any means. As others have written, it all depends on how
much T-SQL code you have in your database and application. Migrating the
schema and data is fairly painless. But the amount of code you have will
determine whether it's a 1-day or 1-month exercise.

-Dave