On 10/28/06, Doug Brown <[EMAIL PROTECTED]> wrote: > I am wondering how difficult it might be to migrate over to mySql from > SQL2000? Is this a daunting task? Any assistance would be much appreciated. I > would especially love to hear from someone who has done this.
I do this with some regularity for clients. The standard answer is that it's easy since SQL is 'standard' -- it's just as easy to convert MS-SQL to nearly *any* db -- DB2 or Oracle or MySQL. But in reality there's plenty of quirks the emerge the more db-specific SQL you use. For example: * Data types. There are differences in the underlying details of the "same" data types in each db. For your specific example, VARCHAR in MySQL was limited to 255 characters in versions up through 5.0.3 and now it's 65,000. If you're on MySQL 4.1 for example, you'll have a problem. Date and time is also fun, though a marked improvement IMHO in MySQL since there are DATE, TIME, and DATETIME instead of just DATETIME. * SQL Functions. These vary between dbs. Expect some tweaking if you use a lot of SQL functions. If you're using ColdFusion functions in a SQL string, that's obviously fine. Expect to rewrite all of those queries that have DATETIME fields with hardcoded 00:00:00 and 23:59:59 in them that were written poorly in the first place! * Storedprocs/custom functions. The implementation varies by db, so expect changes here. Also keep in mind the entire security structure is different, radically different in this case. Plus all the new file management techniques, datatable types, etc. I'm a *big* fan of MySQL, on any platform, but license cost savings alone shouldn't drive you to the conversion. If you've follwed MySQL during the past month, they've moved to a model like RedHat where there's a MySQL Community Edition (like Fedora Linux -- free, more cutting edge, not supported) and MySQL Enterprise Edition (like RedHat Enterprise Linux -- not free, stable, supported). Personally, I think you get a lot of value from the MySQL Enterprise Edition and their support is _really_ good in my experience. And if you're comfortable using MySQL without corporate support packages, there's nothing wrong with that either. A lot of people have mentioned tools to help -- another is the MySQL Workbench from MySQL In the major conversions I've done of MS-SQL (or MS Access) to MySQL, most of the problems came from poorly designed databases, bad queries, and a lack of data consistency because of missing constraints -- the conversion in most cases has been the simplest part. I find date/time queries tend to take the bulk of the conversion time. My 2c. -- John Paul Ashenfelter CTO/Transitionpoint (blog) http://www.ashenfelter.com (email) [EMAIL PROTECTED] ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Introducing the Fusion Authority Quarterly Update. 80 pages of hard-hitting, up-to-date ColdFusion information by your peers, delivered to your door four times a year. http://www.fusionauthority.com/quarterly Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:258454 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
