Thread: Migrating from MS SQL 7
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
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
Shouldn't we have a "Migrating to PostgreSQL" section in the FAQ here: http://www.postgresql.org/docs/faq-english.html ? I remember learning this the hard way - from MS Access to PostgreSQL :( Poul L. Christiansen Alexander Jerusalem wrote: > > 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 > > ---------------------------(end of broadcast)--------------------------- > TIP 6: Have you searched our list archives? > > http://www.postgresql.org/search.mpl
> Shouldn't we have a "Migrating to PostgreSQL" section in the FAQ here: > http://www.postgresql.org/docs/faq-english.html ? > > I remember learning this the hard way - from MS Access to PostgreSQL :( I do have something: ftp://candle.pha.pa.us/pub/postgresql/comparison.mbox Seems this is the best we can do in a semi-objective way. -- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 853-3000 + If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup. | Drexel Hill, Pennsylvania 19026
Yes - what do you want to know? We moved over (successfully) because: 1) The major clients to the server are Unix machines, hence MSSQL server is somewhat unsuitable 2) I prefer Postgres' system overall and have experience of it 3) The inet,cidr and macaddr types are very useful in our applications My overall impression is that the MSSQL server might have been a bit quicker in places, but not by much. One thing I did like from MSSQL was the jobs (which are fast) and the GUI management tools for the less technically able. Also, some of my colleagues have problems with the ODBC interface, but it works fine for me. Regards, Phil +----------------------------------+ | Phil Mayers, Network Support | | Centre for Computing Services | | Imperial College | +----------------------------------+ -----Original Message----- From: Peter Morgan [mailto:mash@daffodil.uk.com] Sent: 18 March 2001 15:57 To: pgsql-general@postgresql.org Subject: [GENERAL] Migrating from MS SQL 7 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