Help a reader decide on SQL Server versus MySQL
Andrew asks:
I have inherited a website that runs on CF7 and about 6 MS-Access databases. It is getting more and more traffic and is hanging more and more often, and it seems to be MS-Access (no surprise there).
I have the choice of upgrading them to MySQL or SQL Server (MSSQL) and was wondering what I should consider when making this choice, or if there is a clear cut favorite for ColdFusion.
I saw an old post where you posted ways to go from SQL Server to MySQL and was wondering if that's because you prefer MySQL.
So in general, I'm not sure there is a clear cut favorite for ColdFusion. My gut says more people use SQL Server just because I think I've seen it more in the field, but that doesn't make it the favorite. I can also say that the database used for a project has very rarely been something I've had any input on. Typically the database was chosen some time ago and we have to work with whatever it is.
But what should you pick given a choice? That's a big question and here are some things to consider. The number one thing to remember is that - most likely - ColdFusion will only be one user of this database. You may have to (or should have to) ensure everyone in the organization is part of the decision making process. If the entire organization is just the web site than I guess it really is just you, but you want to check that.
Other factors that may impact this decision are features and operating system support. MySQL definitely supports more operating systems than SQL Server. We use it at Broadchoice in our Linux production environments and on our Mac development environments. I also use it on Windows for RIAForge. You can't beat the price either, although SQL Server has a free development version now, you still need to shell out some money for production (as far as I know).
I find the MySQL environment friendlier to work with as well. I've had a long hatred for Enterprise Manager, and while I had hoped the last update would have improved the experience, I was sadly disappointed. I've seen some bad MySQL clients as well, but there is definitely more choices out there so you can probably find one more to your liking. Of course, even if you don't find any visual clients you like, the command line support works really well.
Feature-wise, I'm probably not the best person to ask which one is better. Like a lot of ColdFusion developers I only scratch the surface of what is available. Again I tend to be able to do more with MySQL, but I actually spent the time to read a good MySQL book.
So - this morning's coffee talk - all things considered - which platform would folks recommend?
Comments
As you said, Ray, it's rare that we actually have a voice in the DB platform being used. But when I do, my normal reaction is this:SQL Server - For use in Windows/Microsoft shops where everything need to play nice. Especially if they are also doing any .NET/VB/C# dev work that will need to talk to the same database (as SQL now uses Visual Studio to replace the old Enterprise Manager)
MySQL - for everything else.
MySql every time for me! I work on a fairly busy site that is fairly db intensive.
We have 8 db schemas and over 40 tables, the biggest of which holds over 10 million rows. The whole thing runs on MySql - I love it.
The site was set up back in 2005 before MySql had stored procedures in place so our development machine had Sql Server installed to process all the raw data that comes in from a 3rd party.
Using both products has certainly been enlightening for me and my preference for ease of use, performance and price is MySql every time. The MySql GUI tools make it so easy to do stuff I often get frustrated when I have to switch over to Sql Server. The ONLY reason we have kept Sql Server on our development machine is because we also need the connectivity it has with a mapping application that populates spatial information for some of our tables.
I hope that helps.
Cheers
Marty
# Posted By Martin
| 9/15/08 7:34 AM
I'd initially go with MySQL - free - fairly easy to get up and running, and it's going to perform better than Access :)Down the road - if the need arises you can always migrate to SQL Server... or run both!
# Posted By Jim Priest
| 9/15/08 7:57 AM
I will never normally use one technology, such as MSSQL that restricts my choice of technologies in what should be an unrelated area (such as operating system).MySQL *may* be missing some features of MSSQL, but if you're just doing CRUD with your tables, it wont matter.
Both will scream along fine, performance wise.
MySQL is much, much, much cheaper, and you don't have to worry about it waking up one day, phoning home, deciding your licence key is invalid and shutting down.
# Posted By Tom Chiverton
| 9/15/08 8:30 AM
As a consultant I use both MySQL and SQL Server on a daily basis. I can make arguments for using either rdbms but honestly the feature gap between MySQL and ORACLE or SQL Server shrinks every 8 months. For the shear reasons of platform flexibility and license cost MySQL wins hands down. Keep in mind that I'm saying this and I hold certifications on both SQL Server and ORACLE platforms. Don't deploy them unless you have to or unless database budget is not a factor. I would also argue that if you take half of the money for a processor license for SQL Server and put it towards a support agreement with the folks at sun/MySQL you will get way more value for your support dollars than you would with Microsoft or ORACLE.Good luck!
# Posted By Andy Sandefer
| 9/15/08 8:46 AM
I'd like to second Sid's comment, but add - it is often a bit easier to go from access -> mssql. If I remember correctly, there is a nice importer that just moves an access db to a mssql db. Sometimes it's a bit more complicated to go from access to mysql if memory serves.Also mssql has some data mining / visualization tools that are top notch. I haven't seen very many mining tools for MySQL.
Personally, I default to MySQL or Postgres unless there is a compelling external reason to use mssql, oracle etc.
@robThere is a tool called Navicat that converts Access (and lots of other formats) to MySQL in about 3 or 4 clicks for less than $100.00. It also has a job scheduler, data exporter, report builder and some other decent stuff too (some of those features might require the enterprise version - not really sure because I just use it for design and backups). I'm still kind of playing with ERD stuff for MySQL but I recently eval'd a package called ModelRight. It seems great for modeling but not quite as seamless as other diagram tools that I've seen for ORACLE and SQL Server.
# Posted By Andy Sandefer
| 9/15/08 9:00 AM
I did the same thing as your reader (upgraded from Access). That was about 5 years ago and I decided to go with MySQL. Best decision I've made. At that time, there were a few free consoles which allowed for direct import from Access to MySQL. I think you might have to pay for the same tools now ($30-$50)...but it's well worth the price. As I recall, I had to redo (or create) all of the indexes, but that's simple enough.
# Posted By JD
| 9/15/08 9:07 AM
I feel much more productive working in MySQL. Its easier to manipulate my data with SQL (less casting needed, more data format functions available), and getting data in and out of the database is *so* much easier. DTS is a pain and often leaves off important metadata.I love being able to move data around between database servers with just one command.
# Posted By Ryan Stille
| 9/15/08 9:19 AM
Personally i think MS SQL is superior to MySQL. It has some really powerful feaures.BUT if you really want to take full advantage of all of the features you really need to have a trained and dedicated DBA.
I suppose that could be said for MySQL too.
I agree with Chad... both system can be either very slow or very fast. It all depends on your knowledge about the system.In case you go for MySQL, there are MySQL client tools (download from the MySQL web site) that contain a Migration Toolkit (at least in the Windows version) that'll help you convert databases to MySQL.
MySQL has some very nice and easy to use GUI tools. They have a migration tool that allows you to convert almost any DB over to MySQL, these tools are all free:http://dev.mysql.com/downloads/gui-tools/5.0.html
# Posted By Patrick Flynn
| 9/15/08 9:43 AM
MySQL has a Migration Toolkit that will convert MS-Access (and other databases) to MySQL _for free_.
# Posted By Adrian J. Moreno
| 9/15/08 9:46 AM
*shakes fist at Patrick*Blast your faster posting skills!
# Posted By Adrian J. Moreno
| 9/15/08 9:48 AM
Lots of good stuff have been said about both engines, so I won't go into that but instead correct a small bit of detail in Rays post. MS SQL 2005 Express is not a development version, it's a free engine for use even in production, assuming you can live with its limitations. And since it uses the same underlying technology, moving your Express DBs to one of the commercial variants of MS SQL is so eventless it's almost boring.As others have said, I think one has to sit down and evaluate ones needs and the strengths of the competing technologies. But for basic CRUD stuff, if you already have the OS license, cost isn't necessarily a factor.
# Posted By Tomas Fjetland
| 9/15/08 9:59 AM
@TF: Thanks for the correction there. I thought that was a possibility, but wasn't sure.
# Posted By Raymond Camden
| 9/15/08 10:00 AM
Given unlimited budget I would choose MSSQL. It is a better product and you can expect good support.If you've got no money your choice is clear, and using MySQL is not as bad as it used to be.
Since this is all happening because traffic on your site is growing, I'd say this is a good problem to have =)
# Posted By Eric Knipp
| 9/15/08 10:01 AM
I use MySQL at home, MS SQL at work. If I had the choice between the two, I'd learn towards MySQL.And, Tomas Fjetland is right, we have a client using SQL Server 2005 Express running in production. They're grumbling at the moment because they wish they had understood the limitations a little better, but they chose they chose that path because of their budget issues (despite our warnings). They could always upgrade, but they're dragging their feet on the decision.
# Posted By Todd Rafferty
| 9/15/08 10:11 AM
I will reinforce what Andy Sandefer mentioned earlier and say that I just got started using Navicat to manage a MySQL database and it has been a real pleasure.Back ups, data transfer, data conversions, connecting to databases on your remote servers, etc., have all been a breeze so far. They have a 30 day trial which is worth checking out.
# Posted By Brian FitzGerald
| 9/15/08 10:29 AM
I think this more boils down to the feature set you need, and your experience levels.If you are just doing CRUD, as someone mentioned above, MySQL is a cheap solution to your problems.
MSSQL I find is oftentimes easier to get things done more efficiently. This depends on specifically what you are doing... but the product has simply been around longer, and has the benefits of familiar windows gui'ing. There are also some powerful features in MSSQL that MySQL just doesn't have yet. The same could be said from MSSQL to Oracle I suppose too though.
My choice between the two is heavily product specific anyhow...
# Posted By Todd Rafferty
| 9/15/08 10:32 AM
@DanaKNot to call you out, well ok I guess I'm calling you out, but anyway what "powerful features" does SQL Server have that MySQL does not?
# Posted By Andy Sandefer
| 9/15/08 10:48 AM
MSSQL has the SSIS designer, which is handy for moving large quantities of data back and forth. Not sure if MySQL has something similar to this.However, I am disappointed in the sheer number of bugs that are in SQL Server 2005. 2000 was pretty stable and the DTS designer worked well. 2005's SSIS designer tries too much to enforce Microsoft's vision of what a DB should be like (all unicode tables, the Oracle driver is broken).
I'd say hold out for 2008 if you're going to go the MS route.
# Posted By Akira
| 9/15/08 12:23 PM
This one's easy. Don't go with either. Choose PostgreSQL. It's cheaper than SQL Server and it is more SQL compliant than MySQL. Not to mention I trust it with my data way more than I'd trust MySQL.
# Posted By Jeff Self
| 9/15/08 12:31 PM
Somewhat relevant link:http://www.postgresonline.com/journal/index.php?/a...
Comparison of SQL Server 2005, MySQL 5 and PostgreSQL 8.3.
Surprised I haven't heard anyone using PostgreSQL w/ CF...
Since Akira mention SQL Server bugs...I was getting an error in Management Studio every time I tried to connect to a database. A google search revealed an article about it on Microsoft's site (kudos to them for that). But the solution was a head scratcher. I had to find a registry key, set it's value to something other than blank, and then set it back to blank. What the hell? But it did fix the error. <shrug>
# Posted By Jake Munson
| 9/15/08 1:44 PM
@Andy SandeferOff the top of my head:
- SSIS. MYSQL's implmentation can be described, at best imho, as SSIS-lite.
- All the business intelligence tools in 08.
- SSRS (the reporting services). These were really stepped up in sql 08. They even added a scaled down ssrs to the express version.
- As of mysql 5/mssql 2005 (not sure if this is different now?), mysql was still using very distinctive architecture types. This causes headaches if you have the need for advanced options like replication across your databases.
- mssql 2008 (and to some extent 05) added a more significant tie-in to the .NET framework. This one is kind of parallel and not a big sql feature itself, but similar to CF 8.x adding it. The additional capabilities you add under the hood without needing to get your hands messy in advanced sql coding is great!
- I'll disclaim this next one I heard third hand and never fully investigated. Around the timeline of sql 05 vs mysql 5, sql server supported column level security unlike MySQL. It wasn't a concern for me a the time, so I didn't think much of it.
There are a few others, but like I said above, it really just depends on the scale of your apps. Use the right tools for the job. BI tools aren't for everyone heh. I'm not bashing MySQL, the performance of the MyISAM had always been traditionally superior to MSSQL.
@DanaKSome of the tools that ship with SQL Server are great and some of them aren't. I played with the last few releases of Reporting Services and I really liked it. SSIS however has been nothing but non-intuitive and flat out crap every time I've used it.
In regards to the "scale" part of your arguments I would strongly caution you on starting a Windows Server vs. Linux/UNIX pissing contest in terms of scalability. I guess I don't really have a problem with anything that you're saying and I don't have a problem with SQL Server - but I'm just so tired of people treating MySQL like it is a small, desktop class database. It isn't Paradox or FoxPro or Access or any other toy database and saying things like "it really just depends on the scale of your apps" leads people down the path of incorrect thinking when it comes to MySQL. Go ahead and find the best 64 Bit Windows data center on earth and put it in the ring against any Linux or UNIX cluster running ORACLE or MySQL and it will lose hands down every time.
Bottom line - MySQL is a scalable and reliable platform that should be treated just like any of the fully commercial rdbms platforms such as Microsoft, ORACLE, IBM, etc.
@Ray
Where is Sean Corfield when you need him? I asked him for SQL Server help once and he laughed at me and championed MySQL.
# Posted By Andy Sandefer
| 9/15/08 3:57 PM
@AS - Heh, well, we definitely make use of MySQL, so we are all probably biased. ;)
# Posted By Raymond Camden
| 9/15/08 3:59 PM
@chiefI have a theory as to why you don't see much PostgreSQL usage in the ColdFusion community. Its pretty simple. PostgreSQL didn't have a Windows installer until 8.0 was released. Before that, it was pretty much Unix only. And the overwhelming majority of ColdFusion users come from a Windows world. Sure, some have migrated to OS X or even Linux, but most came from Windows or are still on Windows. And MySQL has had a native Windows version since it came out.
When you on the Unix side of things, PostgreSQL definitely has a much larger following. While its still not as much as MySQL, it is definitely much closer. And almost everyone on the Unix camp agrees that PostgreSQL is a much more robust database.
Another reason for MySQL's popularity is that it came out right around the same time as PHP. And every book about PHP in the late '90s used MySQL for the database. So PHP and MySQL became synonymous with one another.
# Posted By Jeff Self
| 9/15/08 8:14 PM
I use MySQL for our DB any time I have the choice and find that the GUI tools are better than MSSQL's. I also love that the database backups are plain text files instead of a cryptic unreadable format - which is great when I want to view or tweak the data during a migration. Performance is great too, no scaling problems so far.
I would say that the MySQL group_concat() aggregate function is all by itself enough reason to go with MySQL over MSSQL 2000. Many headaches easily avoided!
At the risk of being unoriginal, I'll echo much of what Ray said. I've rarely had the opportunity to select the database with which I'll be working, so I really can't contribute decision-making from my own experience. FWIW, I've used both Oracle (8i, 9i, 10g) and SQL Server (2000, 2005) with various ColdFusion versions, and they seem to work fairly well. I don't have any MySQL experience ...Questions to ask:
Who and what else will need access to this data? (Developers, applications, etc.)
How are they using the data?
What availability do they expect?
Are there specific expectations for data recovery?
These questions won't necessarily tell you which DB to choose, but they may guide you with respect to the setup you need when you do choose one. (Of course, if you have an in-house application that is already using a DB, it may make more sense to use it for your site as well. That's my situation: we have two off-the-shelf apps that use SQL Server, so that's what I use.)
How much can you spend on the DB environment?
How much on support?
How much on training?
How much on DBA services?
You're going to be spending money one way or another, even if it's indirectly (by spending time on one or more of these activities). If you have a limited budget, make sure you get the best value you can for your money.
What do you have experience with?
Are there other developers in the company/group/house that have DB experience? What have they used?
Do you get a chance to talk to peers regularly (locally, online)? What do they use?
This is about expertise: sure, you can use online resources, books, etc., but there's nothing quite like getting help directly from someone like you who might know what you're trying to do.
# Posted By Dave DuPlantis
| 9/16/08 8:36 AM
One thing I really enjoy as an app developer about using Coldfusion is that I can deliver an app that will run on a variety of platforms and DBs. I have one app that has in the past 2 years moved from Windows/MS SQL Server to Linux/MySQL to Linux/Oracle 10g (none of the moves under my control). That said, my two cents is: if you are comfortable with Windows and have the $ or can live with the free Express editions use MS SQL Server any other case use MySQL (PHPMYADMIN helps alot). If you are in a situation where performance etc. is becoming an issue then you need a DBA to help decide or "become" a DBA in which case you will have a strong opinion that I couldn't sway anyway.The whole point of my ramblings is that Coldfusion gives you flexibilty to test the waters and see what works best for you :)
# Posted By Tim Smith
| 9/16/08 9:21 AM
@Andy - Sorry I wasn't completely clear I think. I wasn't referring to scale in the terms of scalability. It probably wasn't worded correctly. I meant in the terms of feature-sets. MySQL has made huge strides since the days (not long ago) where joins didn't even exist etc. In fairness SQL server has been laboring under a similar direction playing catch up with Oracle (imho anyhow). What I meant by scale was maybe you have absolutely no use for the BI, or reporting tools. If you're doing just CRUD stuff mostly I think MySQL is the best choice (depending on your environment, as you mentioned) being free and pretty powerful for its price tag. I think as far as scalability is concerned, MSSQL falls flat sometimes because of all that additional complexity that adds overhead to everything.In fact I believe there is a way to hook mssql SSRS into MySQL, it just requires you to have a working mssql installed on the box which is obviously redundant.
Hi Andy,What do you mean by:
"
I'm still kind of playing with ERD stuff for MySQL but I recently eval'd a package called ModelRight. It seems great for modeling but not quite as seamless as other diagram tools that I've seen for ORACLE and SQL Server.
"
We're using ModelRight for Oracle and MySQL and we like it a lot.
Thanks.
# Posted By MySQL user
| 9/16/08 10:46 AM
@MySQL UserUm, it looks a lot like English to me. I "mean" exactly what I said. Well ok, let me give you a scenario of what it is like to use ModelRight and I will try to articulate exactly what I mean...
---
Finally came a moment of synchronicity that took them both by surprise and swept them into their first ModelRight encounter. It was a Cartesian firestorm.
Neither had ever before experienced anything remotely similar. They were equally matched in both strength and quickness, but SQL Server Diagram Toolkit was particularly shocked by ModelRight's aggressive nature. ModelRight had a no-holds-barred approach that was a direct parallel to SQL Server's skills as a fighter pilot. ModelRight was able to make SQL Server fly ModelRightly with the same gusto that ModelRight brought to all data design adventures. ModelRight's subtlety in the handling of SQL Server's control surfaces would confound SQL Server to the very razor edge of an unhandled exception (which is actually quite common for Windows species), anyway then ModelRight would back off as mere hair's breadth to hold SQL Server trembling at that pre-compiled level for astonishingly long moments. When they were finished a small midget named MySQL entered the room riding on a Unicorn named ORACLE.
---
So anyway, that's what I meant.
# Posted By Andy Sandefer
| 9/16/08 11:16 AM
Having spent years firmly in the Microsoft camp, Binetix is now a MySQL shop.We use MySQL for all of our sites (including some transaction-heavy sites that are mission critical for our clients) and to back-end our e-mail server (the excellent hMailServer.) Our web servers are IIS on Win2003.
We're currently running MySQL 5.0. It's rock solid, performance is exceptional, and administration is straightforward.
# Posted By Albert Nurick
| 9/16/08 11:56 AM
I'm the Andrew that submitted the question and just wanted to say thanks. I've talked to everyone a bit more and the MySQL route is definitely better for both now and seemingly in the long run. The points here were definitely interesting and a good read so I for one appreciate everyone's input!
# Posted By Andrew
| 9/22/08 10:59 AM
http://www.adobe.com/go/6ef0253