Re: Migrating from MS SQL 7

Hi Peter,

I have done the same and for similar reasons. In general it worked fine.
The biggest problem with migration from SQL Server to almost any other
database is always that SQL Server uses case insensitive string comparisons
by default whereas other DBMSs use case sensitive comparisons and collation
order. Postgresql has two features that make your life easier in that
respect: You can use function based indexes with lower() and/or you can use
ILIKE instead of LIKE for case insensitive wildcard searches. There's some
other magic postgres can do with regular expressions but I don't know that
feature very well.

The other thing I trapped into only recently is that correlated subqueries
using IN don't use indexes in postgres and are therefore very slow. You can
work around that by using EXISTS instead. There's a note on this in the FAQ.

I'm also working with Oracle and my experience was that it's much harder
(and much more expensive) to migrate to Oracle because function based
indexes are only available in Oracle enterprise edition which truely costs
a fortune. And something like ILIKE doesn't exist in Oracle at all. I use
Oracle only in projects where a single database server is not enough for
scalability or high availability reasons so I need to use Oracle Parallel
Server.

When it comes to maintainance, you have to be aware, that you must run
vacuumdb on your postgresql database from time to time to keep good query
performance (especially if you have much update and delete activity). If
you do that, postgresql performance is comparable to SQL Server (I've not
done extensive performance testing though).

I have "shopped" around a lot for databases on Linux and tried some things.
Postgresql is by far the most powerful DBMS of those I have looked at. The
community is very helpful and the developers are doing a good job in
bringing up new features and fixing bugs. Above all Postgres runs very
stable, I never had mysterious standstills (like those I frequently
experienced with SQL Server 6.x)

Regards,

Alexander Jerusalem


At 16:56 18.03.01, Peter Morgan wrote:
>Anyone migrated from SQL server 7 to PostgreSQL
>
>I'm considering this so I can replace my NT box with Linux for deployment
>across a business.
>
>Any advice would be appreciated
>
>Pete
>
>
>
>---------------------------(end of broadcast)---------------------------
>TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org