Re: reasons to use postgreSQL rather than MS SQL

I used to work at Microsoft PSS (Developer support-- we supported SQL
Server and MSDE).

I would choose PostgreSQL over MSDE/SQL Express *any day.*

Microsoft releases SQL Server Express as a loss leader to help create a
market for SQL Server. The idea is that as your business grows, the
limitations of SQL Express will almost certainly become unbearable and
then you will have to purchase SQL Server Standard Edition and the
required number of client access licenses. If you want your road map to
be towards SQL Server and Windows Server, that is all good and well.
However, if not, think good and hard before using the freebie.

SQL Server Express has the following limitations built into it:
4GB data size (not usually an issue)
Performance limited (in other words, more connections, worse
performance-- plan on using it for no more than 5 concurrent connections)

This second one is the bigger issue, as it may impact your ability to
administer the database properly during times when it is likely to be in
use at all.

Basically with SQL Server Express you are using a hobbled version of SQL
Server with the idea that at some point in the future, you will want to
migrate to the Real Thing(tm). With PostgreSQL you get the Real Thing
to start with and can use any of a large number of tools to administer
it (such as PGAdmin III).
<snip>
Now for your requirements

>* Low Cost
>
>
Can't beat Free (as in Freedom and cost), can you? Note that the
license for PostgreSQL should require very little administrative
overhead to track unlike SQL Server (Express or not).

>* Stability
>
>
Check.

>* An installation and data porting process that can be
>austomated
>
>
I guess you should be able to do this with either Access and VB or with
other various tools. You could probably even do it with psql and Access
(using COPY and csv files).

>* Access from VB6 (probably ODBC)
>
>
Why ODBC? ADODB is much better, IMO. I believe that PostgreSQL
supports both.

>* Accessability from yet unspecified "supported"
>language for future rewrite (VB .NET, Java or C# are
>good candidates at the moment)
>
>
No reason to think you would have any problem with this. Drivers exist
for every programming language you have listed (.Net providers, JDBC
drivers, etc) as well as Perl, Python, PHP, etc. Any languages not
supported? I guess one could always use system calls to run psql if you
had to....

>* Some means for support staff to view and query table
>contents
>
>
I recommend PgAdmin III for this.

>
>The obvious solution seems to be to migrate the
>database to MS SQL Server Express. This seems to be
>targeted to avoid small applications like ours going
>to open-source. It gives the advantage of an easy
>upgrade-path from ACCESS.
>
However, this is only a valid choice, IMHO, if you are willing to commit
to MS SQL Server as your business grows.

> The databases can be viewed
>by support staff using ACCESS 2002, which has the
>advantage of having a small lurning-curve from access
>97.
>
No reason you can't do this with PostgreSQL too. Access can be used to
query table contents with PostgreSQL using linked tables.

> We are way below the limits currently imposed on
>the Express edition, and it is not conceivable that
>the data would increase to anywhere even close to the
>4GB limit even in the long term.
>
>
Don't look at the data limit. Your bigger issue will almost certainly
be concurrent connections, especially when support staff are involved.

>I do have worries about Microsoft changing the "free"
>licensing or reducing the limits in future releases.
>
A valid concern. But the big issue is the question of concurrent
connections. I don't see their limit falling below, say, 5. But... If
you want to tie in other applications to the database, that could become
a problem quite quickly. With PostgreSQL, you will have very little
licensing tracking (probably just a note that says "PostgreSQL:
BSD-License-- no restrictions on use or redistribution"), and will never
have to count connections or plan for additional licenses.

If you need help with porting the data, I am sure there are a large
number of firms <shameless_plug>(mine included:
http://www.metatrontech.com)</shameless_plug>, Command Prompt,
PostgreSQL, Inc. and others. Creating such an automated tool would take
very little time, IMO...

Hope this helps,
Chris Travers
Metatron Technology Consulting
chris(at)metatrontech(dot)com