Re: reasons to use postgreSQL rather than MS SQL

Our company has a project to migrate some financial
software from a Microsoft Access 97 database. This
software is run at various office locations, and each
location will require data-porting as part of the
upgrade process. Each databse is quite small, ranging
from only 10MB in smaller branches to about 600MB in
the gargest, with a typical size being arround 150MB.
The taget platform will be Windows 2000. The
application is written in Visual Basic (VB6), but is
likeley to be rewritten at some point *after* the
database convesrion.

The main requirements are:

* Low Cost
* Stability
* An installation and data porting process that can be
austomated
* Access from VB6 (probably ODBC)
* Accessability from yet unspecified "supported"
language for future rewrite (VB .NET, Java or C# are
good candidates at the moment)
* Some means for support staff to view and query table
contents

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. The databases can be viewed
by support staff using ACCESS 2002, which has the
advantage of having a small lurning-curve from access
97. 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.

I do have worries about Microsoft changing the "free"
licensing or reducing the limits in future releases.
Having succesfully used open source products in our
ecommerce system, however, I would like to consider
open source alternatives. I think I would need more
reasons not to use SQL Sever Express to convince the
IT manager that this is the best option; at the moment
I could only put postgreSQL as a "possible alternative
option".

Is there anything else I can add to strnegthen the
argument?Our company has a project to migrate some
financial software from a Microsoft Access 97
database. This software is run at various office
locations, and each location will require data-porting
as part of the upgrade process. Each database is quite
small, ranging from only 10MB in smaller branches to
about 600MB in the largest, with a typical size being
around 150MB. The target platform will be Windows
2000. The application is written in Visual Basic
(VB6), but is likely to be rewritten at some point
*after* the database conversion.

The main requirements are:

* Low Cost
* Stability
* An installation and data porting process that can be
automated
* Access from VB6 (probably ODBC)
* Accessibility from yet unspecified "supported"
language for future rewrite (VB .NET, Java or C# are
good candidates at the moment)
* Some means for support staff to view and query table
contents

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. The databases can be viewed
by support staff using ACCESS 2002, which has the
advantage of having a small learning-curve from access
97. 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.

I do have worries about Microsoft changing the "free"
licensing or reducing the limits in future releases.
Having successfully used open source products in our
ecommerce system, however, I would like to consider
open source alternatives. I think I would need more
reasons not to use SQL Sever Express to convince the
IT manager that this is the best option; at the moment
I could only put postgreSQL as a "possible alternative
option".

Is there anything else I can add to strengthen the argument?

On Friday 15 July 2005 06:24, Chris wrote:
> Our company has a project to migrate some financial
> software from a Microsoft Access 97 database.
<snip>
> The taget platform will be Windows 2000. The
> application is written in Visual Basic (VB6), but is
> likeley to be rewritten at some point *after* the
> database convesrion.
> The main requirements are:
> * Low Cost
<snip>
> The obvious solution seems to be to migrate the
> database to MS SQL Server Express.
<snip>
> The databases can be viewed
> by support staff using ACCESS 2002, which has the
> advantage of having a small lurning-curve from access
> 97. We are way below the limits currently imposed on
<snip>
> I do have worries about Microsoft changing the "free"
> licensing or reducing the limits in future releases.
<snip>

The main issue that would concern me is that you're going to move to a new m$
product on an obsolete m$ platform (w2k). While you might be able to get
the databases for "free", you might want to factor in the cost of upgrading
your OS to something that m$ will support you on. If you swap to PostgreSQL,
I suspect we'll support w2k for quite some time longer, but even if not you
could swap to *nux as well and not have to pay os licensing fees.

--
Robert Treat
Build A Brighter Lamp :: Linux Apache {middleware} PostgreSQL

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