Thread: migrating from MSSQL

Eugene . wrote:
> Hi all,
>
> I've been tasked with evaluating the feasibility of migrating our
> in-house application from MSSQL to PostgreSQL. It is fairly old and
> has lots of stored procedures, which is why we need to think carefully
> before making the switch. Does anyone else have a similar experience?
> What are some of the things to watch out for?
>

from what all I gather, transact*SQL is -way- different, so you'll be
pretty much rewriting your procedures, you might consider just
rearchitecting the whole application.    A lot of people are moving
their business logic out of stored procedures and into a application
server, programmed in a conventional language, such as Java, or Ruby, or
whatever, and just using stored procedures where it makes sense for
performance.

> Secondly, which commercial support vendor would you recommend? I found
> EnterpriseDB and CommandPrompt, but I don't know anything about them.
> Any other candidates?

Those are both fine upstanding members of the postgres community, each
employs a number of primary project developers, and both have a long
history of providing support.

Eugene . wrote:
>
> Hi all,
>
> I've been tasked with evaluating the feasibility of migrating our
> in-house application from MSSQL to PostgreSQL. It is fairly old and has
> lots of stored procedures, which is why we need to think carefully
> before making the switch. Does anyone else have a similar experience?
> What are some of the things to watch out for?

TSQL is very very different from any of the languages supported in
PostgreSQL. You are most likely going to end up having to rewrite all
the procedures more or less completely, unless they are just wrappers
around single SQL statements (in which case you can use a SQL language one).

One thing to note is that in PostgreSQL you can't just execute the
typical "sp_myfunc" command as you would in MSSQL - you need to do
"SELECT sp_myfunc()". That means you may need to change every call in
the application, if that's the syntax you have been using.


> Secondly, which commercial support vendor would you recommend? I found
> EnterpriseDB and CommandPrompt, but I don't know anything about them.
> Any other candidates?

Depends completely on where in the world you are located. There is a
list of providers available on our website at
http://www.postgresql.org/support/professional_support

The ones you recommended are both good, but depending on where you are
there may be another good option available closer to you.

//Magnus

Hi

I have migrated a very large database from MSSQL to PG. This was a
database of +/- 400 tables. You should note the following.

- MSSQL datatypes are not always compatible with PG datatypes. If you do
this carefully PG will save you a lot of time.
- The "timestamp" datatype of MSSQL is not the same thing in PG. The
"timestamp" is from MSSQL 7 and 2000 days
- Check if all the built-in functions that you are using in MSSQL also
are available in PG (most are by the way)
- Auto numbering is MSSQL is different from PG. In PG you have to do
with sequences (serial datatype)

- About the stored procedures:

The first thing you will notice in migrating stored procedures is that
in PG you will have to define what the return type will be of your
stored procedure.
This might be a little frustrating because if you have a stored
procedure that returns a set of columns which belong to various tables,
you then have to create a custom type that exactly matches your stored
procedures number (and type) of columns or use the "record" datatype as
return type. (I can tell you that this is not very handy)

Please note that TSQL is a very different language. In my case 70% of
all the stored procedures had to be customized one way or another.

Entity names in PostgreSQL are down folded by default: CREATE TABEL
MyTable  results  the table  name to be  mytable (lower case).
This causes a lot of problems if your application (especially C/C++ apps
) expects the column names to be case preserved.
In order to achieve case preserving in PG you must do: CREATE TABLE
"MyTable" (note the quotes around the table name)

- I have had no problems migrating constraints and indexes.
- Triggers are also different but very easy to migrate.
- I had to write a little application for migrating data. because I had
to check and modify data before I inserted them into PG. Perhaps you can
export the data to CSV and then import it in PG using the COPY command.

I hope this helps,

Regards,
Gevik.


Eugene . wrote:
> Hi all,
>
> I've been tasked with evaluating the feasibility of migrating our
> in-house application from MSSQL to PostgreSQL. It is fairly old and
> has lots of stored procedures, which is why we need to think carefully
> before making the switch. Does anyone else have a similar experience?
> What are some of the things to watch out for?
>
> Secondly, which commercial support vendor would you recommend? I found
> EnterpriseDB and CommandPrompt, but I don't know anything about them.
> Any other candidates?
>
> thanks,
>
> Eugene
>

On Fri, 2009-05-08 at 00:08 -0700, Eugene . wrote:

> Secondly, which commercial support vendor would you recommend? I found
> EnterpriseDB and CommandPrompt, but I don't know anything about them.
> Any other candidates?

Those two companies operate mostly in US. There are various other
companies in Europe, South America, Asia, etc who may be able to provide
better alternatives. Major contributors are spread across the world, not
localised in any country, timezone or language (though English is the
accepted language on this particular list).

--
 Simon Riggs           www.2ndQuadrant.com
 PostgreSQL Training, Services and Support

On Friday 08 May 2009 12:08:44 am Eugene . wrote:
> Hi all,
>
> I've been tasked with evaluating the feasibility of migrating our in-house
> application from MSSQL to PostgreSQL. It is fairly old and has lots of
> stored procedures, which is why we need to think carefully before making
> the switch. Does anyone else have a similar experience? What are some of
> the things to watch out for?
>
> Secondly, which commercial support vendor would you recommend? I found
> EnterpriseDB and CommandPrompt, but I don't know anything about them. Any
> other candidates?
>
> thanks,
>
> Eugene

I know this is going to sound like pie in the sky but.  I had the same task.
I found a program that converted everything on the web.  However, the program
was very expensive over $3000.00 US.  The author provided a test demo that
would convert 2 or 3 SP's.  It did look very good.  The author is more
interested in getting large companies to make purchases.  Not that is a bad
thing only that he was not to responsive to a non-profit request.  Keep
looking because the name escapes me and also the link.

--
John Fabiani

In response to johnf <jfabiani@yolo.com>:

> On Friday 08 May 2009 12:08:44 am Eugene . wrote:
> > Hi all,
> >
> > I've been tasked with evaluating the feasibility of migrating our in-house
> > application from MSSQL to PostgreSQL. It is fairly old and has lots of
> > stored procedures, which is why we need to think carefully before making
> > the switch. Does anyone else have a similar experience? What are some of
> > the things to watch out for?
> >
> > Secondly, which commercial support vendor would you recommend? I found
> > EnterpriseDB and CommandPrompt, but I don't know anything about them. Any
> > other candidates?

I took a project to do this under contract a few years ago.  Quite
frankly, it was an interesting and trouble-free project.  Any
competent programmer should be able to handle it for you.  The cost
involved is going to depend on the amount of procedures to convert.

Of course, I did it all by hand.  If someone out there has a program
to convert, it would probably reduce the cost.  But I also had the
ability to optimize some of the SPs as I converted them.

--
Bill Moran
http://www.potentialtech.com
http://people.collaborativefusion.com/~wmoran/

On Fri, 2009-05-08 at 10:11 +0200, Magnus Hagander wrote:
> Eugene . wrote:

> > Secondly, which commercial support vendor would you recommend? I found
> > EnterpriseDB and CommandPrompt, but I don't know anything about them.
> > Any other candidates?
>

As a person from Command Prompt :) we do have people on staff with
experience with this. I am sure that EnterpriseDB does as well.


> Depends completely on where in the world you are located. There is a
> list of providers available on our website at
> http://www.postgresql.org/support/professional_support

Although proximity can be important he should work with the company that
suits his needs as a whole. There a number of long standing companies
that would likely do a good job for him:

OmniTI : http://www.omniti.com
Command Prompt : http://www.commandprompt.com/
EnterpriseDB : http://www.enterprisedb.com/
Redpill : http://www.redpill-linpro.com/
2nd Quandrant : http://www.2ndquandrant.com/

Sincerely,

Joshua D. Drake


--
PostgreSQL - XMPP: jdrake@jabber.postgresql.org
   Consulting, Development, Support, Training
   503-667-4564 - http://www.commandprompt.com/
   The PostgreSQL Company, serving since 1997

On Fri, May 8, 2009 at 3:48 PM, Joshua D. Drake <jd@commandprompt.com> wrote:
> On Fri, 2009-05-08 at 10:11 +0200, Magnus Hagander wrote:
>> Eugene . wrote:
>
>> > Secondly, which commercial support vendor would you recommend? I found
>> > EnterpriseDB and CommandPrompt, but I don't know anything about them.
>> > Any other candidates?
>>
>
> As a person from Command Prompt :) we do have people on staff with
> experience with this. I am sure that EnterpriseDB does as well.

Compatibility is something of a specialty of ours as you know, and we
are experienced with DBMSs other than Oracle, including SQL Server.


--
Dave Page
EnterpriseDB UK:   http://www.enterprisedb.com