Thread: [GENERAL] migrate Sql Server database to PostgreSql

[GENERAL] migrate Sql Server database to PostgreSql

From
PAWAN SHARMA
Date:
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

Re: [GENERAL] migrate Sql Server database to PostgreSql

From
William Ivanski
Date:

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], 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

Re: [GENERAL] migrate Sql Server database to PostgreSql

From
Venkata B Nagothi
Date:
 
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

Re: [GENERAL] migrate Sql Server database to PostgreSql

From
Kenneth Marshall
Date:
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


Re: [GENERAL] migrate Sql Server database to PostgreSql

From
"Rader, David"
Date:


--
David Rader

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