Thread: MSSQL to PostgreSQL

Hi all,

We're migrating the contents of an old MSSQL server to PostgreSQL 8.3.7, so a full conversion is required.  Does anyone know of any guides which highlight common gotchas and other userful information?

Thanks

Thom

On Fri, Jul 31, 2009 at 8:47 AM, Thom Brown<thombrown@gmail.com> wrote:
> Hi all,
>
> We're migrating the contents of an old MSSQL server to PostgreSQL 8.3.7, so
> a full conversion is required.  Does anyone know of any guides which
> highlight common gotchas and other userful information?

That's going to depend a lot on how many features of the database you
were using...especially higher level features like stored procedures.
Converting the schema and the data shouldn't be too bad -- there are a
number of relatively easy ways to do it including microsoft DTS
levering the pg odbc driver.

The biggest change is going to be getting used to the new tools...if
you haven't already, i'd highly recommend mastering psql (don't lean
too much on pgadmin) and learning basic administration tasks like
restarting the database while active, killing particular backends,
backup strategies, etc.  If the sql server installation is 2000 or
older, you should be able to port most things pretty easily except for
any t-sql procedures.  PostgreSQL supports functions which are a
little different so some of the things you used to do in t-sql you may
have to do in sql scripts and an external scheduler such as cron.

Many things in PostgeSQL are easier than you might be used to...it's
got superior concurrency handing and a much better type system.  Good
luck!

merlin

Learning PostgreSQL isn't a problem since most of our database servers are Postgres.  It's just an old database we want to convert.

Thankfully we don't rely on DTS but I think there may be some refactoring of stored procedures into functions as some return multiple datasets.

And I personally much prefer Postgres to MSSQL Server any day.

Thom

On Fri, 31 Jul 2009 13:47:39 +0100
Thom Brown <thombrown@gmail.com> wrote:

> Hi all,

> We're migrating the contents of an old MSSQL server to PostgreSQL
> 8.3.7, so a full conversion is required.  Does anyone know of any
> guides which highlight common gotchas and other userful
> information?


http://wiki.postgresql.org/wiki/Microsoft_SQL_Server_to_PostgreSQL_Migration_by_Ian_Harding

http://edoceo.com/creo/ms2pg

These were the places I read when I had to migrate some MS SQL DB to
Postgresql.

I admit I mostly did it by exporting csv from MS SQL[1],
refactoring the schema and rewriting from scratch functions... but
those were the places where I read the info I needed.

[1] suddenly MS SQL became unable to export proper csv so I remember
I wrote an rudimentary odbc2csv tool as well. Since it was inspired
by an even more rudimentary work of a colleague I'll ask if he can
release it under GPL if you'll find yourself in the need of such a
beast.

You may also find useful FreeTDS
http://www.freetds.org/
http://www.webthatworks.it/d1/page/odbc,_freetds_and_microsoft_sql_(and_php)
and dblink

http://www.postgresonline.com/journal/index.php?/archives/44-Using-DbLink-to-access-other-PostgreSQL-Databases-and-Servers.html
http://www.postgresql.org/docs/8.3/static/dblink.html


--
Ivan Sergio Borgonovo
http://www.webthatworks.it

On Fri, Jul 31, 2009 at 9:17 AM, Thom Brown<thombrown@gmail.com> wrote:
>
>> That's going to depend a lot on how many features of the database you
>> were using...especially higher level features like stored procedures.
>> Converting the schema and the data shouldn't be too bad -- there are a
>> number of relatively easy ways to do it including microsoft DTS
>> levering the pg odbc driver.
>>
>> The biggest change is going to be getting used to the new tools...if
>> you haven't already, i'd highly recommend mastering psql (don't lean
>> too much on pgadmin) and learning basic administration tasks like
>> restarting the database while active, killing particular backends,
>> backup strategies, etc.  If the sql server installation is 2000 or
>> older, you should be able to port most things pretty easily except for
>> any t-sql procedures.  PostgreSQL supports functions which are a
>> little different so some of the things you used to do in t-sql you may
>> have to do in sql scripts and an external scheduler such as cron.
>>
>> Many things in PostgeSQL are easier than you might be used to...it's
>> got superior concurrency handing and a much better type system.  Good
>> luck!
>>
>> merlin
>
> Learning PostgreSQL isn't a problem since most of our database servers are
> Postgres.  It's just an old database we want to convert.
>
> Thankfully we don't rely on DTS but I think there may be some refactoring of
> stored procedures into functions as some return multiple datasets.

you meant to say t-sql?  DTS is data transformation services, you can
use it to do the one time schema and data converesions (it's not
perfect, but can be a time saver).

merlin

On Fri, Jul 31, 2009 at 2:47 PM, Thom Brown<thombrown@gmail.com> wrote:
> Hi all,
>
> We're migrating the contents of an old MSSQL server to PostgreSQL 8.3.7, so
> a full conversion is required.  Does anyone know of any guides which
> highlight common gotchas and other userful information?
>

As for other useful information:
Postgres 8.4 was released a month ago, I would think you are better
off targeting that.


Isak


> Thanks
>
> Thom
>

On Friday 31 July 2009 05:47:39 am Thom Brown wrote:
> Hi all,
>
> We're migrating the contents of an old MSSQL server to PostgreSQL 8.3.7, so
> a full conversion is required.  Does anyone know of any guides which
> highlight common gotchas and other userful information?
>
> Thanks
>
> Thom

You might want to check out
http://mstopsql.wikidot.com/

Johnf