Dan, I'll add a few comments as well, focusing on MySQL. I've transitioned a number of clients from Access to MySQL, and more than one from MS SQL Server to MySQL for parts of their infrastructure. MySQL is fast, especially for reads (e.g. decision support systems, datawarehousing), but it's missing a ton of features higher-end dbs have. Most of those features are getting addressed in 4.1, but some (stored procs, views) have to wait until version 5.0 of MySQL or later. Of course Access is missing a ton of features too, so no big loss in moving from one to the other. I've used CF4.5 and 5 extensively w/ MySQL -- no production experience with it on CFMX yet.
I've used the free version of AccessDUMP, the scripts at the MySQL site, and even Microsoft's DTS in SQL Server to convert Access databases to MySQL. I've had the most problems converting boolean fields (0/-1 in Access, tinyint in MySQL) and some poorly formatted date fields when using the automatic tools, but nothing too challenging to overcome. But only trivial databases have converted perfectly. The simply aren't a lot of SQL functions in Access, so that helps the transition, though Access does support subselects and MySQL won't for a couple more weeks (4.1 is likely going to be released at the developer's conference). You can often rewrite subselects as self- or outer-joins though, so there's always alternatives. If you use UNIONs, you're stuck since MySQL doesn't support them until 5.0, though as I write this, I can't recall if Access supports them either... MySQL also has a LOT of date data types when compared to Access, so you might want to adjust some of the date data types so your application can take advantage of them. I've generally found that my conversion process involves a test conversion, some data comparison of key fields (particularly boolean and date datatypes), and then a little scripting of the Access database to get the data in a better format for the conversion (DTS is pretty adept at this sort of thing -- nothing like using SQL Server tools to convert Access to MySQL!) It's also worth a sweep through the SQL code, since Access is pretty forgiving about date comparisons and MySQL is stricter -- I've found code that was pretty abysmal to start with that worked in Access but broke in MySQL. Forced the developers to fix code that was crappy to start with. As far as macroscopic issues go, MS-SQL has many more tuning options, extensive replication options, stronger supporting services (scheduling, etc), clustering, etc. Most folks don't need any of that and the crucial services (e.g. scheduling) can be provided through other tools (Windows scheduling, PHP, CF itself, etc) or have some basic support in MySQL. MySQL is IMHO as fast or faster for most folks in this regime (moving from Access to something else) I really like it for DSS, less thrilled for OLTP apps if usage is *truly* large, though plenty of folks do use MySQL that way. And of course you can't beat the price! Regards, John Paul Ashenfelter CTO/Transitionpoint [EMAIL PROTECTED] ----- Original Message ----- From: "dan martin" <[EMAIL PROTECTED]> To: "CF-Talk" <[EMAIL PROTECTED]> Sent: Sunday, March 09, 2003 9:31 PM Subject: sql2k vs mySQL vs postgresql (for win2k) > Hi, > > 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? For sql2k the upgrade tool does most of the work for you. Are there obvious benefits performance or feature wise between the choices? > > Any info would be appreciated. > > > > --------------------------------- > Do you Yahoo!? > Yahoo! Tax Center - forms, calculators, tips, and more > ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| 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 This list and all House of Fusion resources hosted by CFHosting.com. The place for dependable ColdFusion Hosting. Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
