Thread: reasons to use postgreSQL rather than MS SQL Server Express
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?
> > Is there anything else I can add to strengthen the argument? > don't forget the enterprise manager is not packaged with the msde. However by far the most important consideration is being free from licensing constraints. btw ms did tinker with msde licensing a bit (notice it is no longer downloadable without registration). SQL server is a fine database however it is controlled by someone else. Database servers control your company's most precious asset, its data, and at the end of the day sql server is owned by somebody else and basically rented by you. Merlin
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@metatrontech.com