dan martin wrote: > > I am currently using ms access with a growing 20mb database that I need to upgrade. > My initial plan was to migrate over to SQL 2000 because that seems to be the most > painless (except for price). I am wondering if it would make more sense to migrate > to an open source db: mySQL or postgresql. I am running win2k with cf5 and would > like to keep the db on the same server. > > Does anyone have experience or comparison information between the 3 choices running > on win2k? How much work is it to port access to mysql or postgresql?
Access to PostgreSQL is very little work. Make sure you read the chapter on datatypes and you are set to go. After you have converted your data you need to look over your queries. PostgreSQL supports more join/subquery constructs as Access, so that is no problem. But you might need to rewrite some of your queries because functions like Year() and DatePart() are not present in PostgreSQL, PostgreSQL uses the standard Extract() for that (writing your own Year() and DatePart() functions is trivial). With MySQL data conversion is similar, but rewriting your queries is a little bit the other way around. Subqueries are not supported so you would need to rewrite them, but there are more functions that behave the same betweeen Access and MySQL. But don't forget that query syntax between Access and MS SQL Server requires some changes too. > Are there obvious benefits performance or feature wise between the choices? Featurewise MS SQL Server has advantages over PostgreSQL, which in turn has many advantages over MySQL. I don't think performance matters much for such a small database, except in some rare scenario's. For example, if you disable connection pooling, PostgreSQL will take a much heavier performance hit (it uses one process for each connection). Or if you have some extremely long running requests MS SQL Server will only have limited CALs left to serve other requests. The biggest disadvantage of PostgreSQL, and the one reason I not outright recommending it, is that all the native versions for Windows are still in Beta. You can run a Cygwin version, but it has implications for performance and there might be compatibilities issues if you run other Cygwin applications. I am happily running the beta for a few months now, but that is development only. BTW, one database you didn't mention was Firebird. I hear it is almost as capable as PostgreSQL and it does have a native Windows version. Unfortunately, I have spent too little time working with it to say more. Jochem ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4 Subscription: http://www.houseoffusion.com/cf_lists/index.cfm?method=subscribe&forumid=4 FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Signup for the Fusion Authority news alert and keep up with the latest news in ColdFusion and related topics. http://www.fusionauthority.com/signup.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
