Since the last month both Microsoft and MySQL AB have proudly, with lot of bells and whistles, announced their cooperation (MySQL AB has became an Alliance Member of the Microsoft Visual Studio Partner Program) , and since the information on using MySQL database as data backend for development of .NET applications is still somehow a "taboo", especially among .NET developers (resources on the web are quite rare as well), it might be a right time to look at this matter.
The first question that anybody would ask is WHY would anybody use MySQL as DB backend in .NET applications. Well, there are pros and contras for that question.
MySQL has always been an myth of "performance monster which lacks capabilities". Well, that's only partly true, for both facts (performance and capabilities). I can't here present any benchmarks - to lazy to mine own tests, and there are no accurate benchmarks on the internet - but here, at Daenet Sarajevo we have been developing .NET applications with both MS SQL Server 2005 and MySQL 5 for quite some time now, and I guess we can talk quite neutrally about this topic.
PROS:
- It is a full-blooded Database, which in it's latest version (5) supports as well such things as clustering, grid computing etc.
- It is VERY stable, as well in the environments of very large databases (just to mention that i.e. Turkish Telecom with over 20 mil. mobile users uses MySQL as a storage for SMS application, or Wikipedia…).
- It is free. There is lot's of misunderstanding about is it really free or not, so lets put it this way: it IS free, as long as you don't use it as an embedded database in your solutions. If you embed it (means: with installation program of your application, MySQL is being installed as well), you have to pay some fee, which is again cheaper than with Microsoft. But since we always need to tune up our DB backend, especially for larger-scale projects, embedding is out of question anyway - DB is installed manually. And then, it's free.
- Easy to install, easy to maintain: I have rarely seen such a smart setup procedure - DB setup usually don't take more than 10 mins. You must chose between "Database role template" (development machine, shared server, dedicated server), which you can further fine-tune (how much expected concurrent connections are assumed, how much memory, disk space etc etc MySQL should take), but in over 90% of cases choosing one of the three predefined templates is more than enough.
- Easy integration in .NET projects: MySQL AB offered it's own ADO.NET adapter for both Framework 1.1 and Framework 2.0, but it's CommandBuilder is buggy. But, there is a commercial, CoreLab's MySQL ADO.NET provider for F 1.1, F 2.0 and CF, which works perfectly, and does a job which you expect to be done. It seamlessly integrates in VS2005, and makes development very easy.
(click for full picture)
- Good SQL dialect: MySQL will, more-the-less, swallow almost everything that you give as SQL command. It understands almost all SQL dialects, including T-SQL used by Microsoft and PLSQL used by Oracle. There are some funny stuff though: you can't joint 2 strings with + sign: you need to use CONCAT function.
- Very good set of accompanying tools: Bundled with MySQL, you will get profiler, query performance analyzer, DB Tuner… Free, from MySQL web site, you can download MySQL administrator (necessary tool for DB maintenance: backups, maintenance of users and user permissions…), Query Browser (that's something that should be equivalent for MS SQL Server's Management Studio). The only weak spot in this set of tools is the Query Browser, which cannot measure with Microsoft SQL Server Management Studio, but there is a commercial tool called "Navicat" which even beats Management Studio with power and simplicity of use.
- Easy migration: "MySQL Migration Toolkit" is free tool from MySQL and it is probably the best migration toolkit I have seen. Here at Daenet, our "primary" database is Microsoft SQL Server 2005 (that is where we do development). Two of our customers have, and want to have, MySQL on the backend. We often do data transformation and import at our office and then bring "ready data" to customers: that's where MySQL Migration Wizard jumps in. It converts even Triggers, Stored Procedures and Functions! And we have never experienced ANY problems with MSSQL -> MySQL conversions.
- VERY Low total costs of use: For a comfortable work on .NET development with MySQL, we bought ADO.NET Adapter from CoreLabs (150 US$) and Navicat (100 US$). That's where the costs end. If someone has enough nerves to bypass CommandBuilder in the MySQL's own ADO.NET adapter, and even more nerves to fight with "MySQL Query Browser", even these 250 US$ costs can be avoided. On the customer's side there is absolutely no costs.
- Full UTF8 support - those of us who come from countries where čćđšž are normal characters, and especially those of us whose countries have two official alphabets (Latin and Cyrillic шта има раја код вас?), can really appreciate good UTF8 implementation. And in the same time hate Oracle from the deepest of the soul.
- Speed - although it is a wide-spread myth that MySQL is a speed monster, that is only partly true. MySQL supports two database engines: MyISAM, which is VERY fast (much faster than MS SQL), but lacks almost all important features (transactions, concurrency checking, triggers, foreign keys...), and InnoDB, which has all these features and it's performances are comparable to these of Microsoft SQL Server 2005. It is slightly faster than MS SQL 2005 on relatively simple SELECT queries (even when you SELECT from 5-6 multiple tables), UPDATE has same performances as by MS SQL, and UPDATE with joined tables is little slower than by MS SQL. All in all, it's speed performances are on the same level as with Microsoft SQL Server 2005. Good thing is that you can combine MyISAM and InnoDB tables in one database, so you can use MyISAM in tables which records are often read but rarely changed and where there is no need for concurrency violation checks (like code lists etc), and InnoDB tables for everything else.
- The last point in the "PROs" list will as well be the first point in the "CONs" list: user authentication. MySQL has really brought User authentication and user rights almost to perfection - you can define tons of different scenarios and rights for every user (i.e. you can define the set of IP addresses or host names from where a certain user will have certain rights). Combining these conditions, you can cover almost all user-rights scenarios, but...
CONs
- User authentication: even if DB-level user authentication is almost perfectly made, there is no Windows Based (domain based) authentication. The "official" reason for this is that MySQL is a multiplatform database (you can without problems connect to UNIX-hosted MySQL Database from your .NET application), but the most often scenario is still a Windows Server 2003 hosted MySQL DB with Windows XP, Windows 2000 or Win 98 clients. Knowing this, there is no reason for MySQL not to implement Windows based authentication, as a feature on Windows-based MySQL installations.
- Backend programming: on the backend, you are still damned on SQL-based stored procedures and functions. Even Oracle has in it's latest version (10g SE) implemented .NET managed code on the backend side (database side) on their Windows-based installations, but MySQL still didn't. Of course, debugging SQL based procedures and functions is hell. There are rumors that this will be corrected in version 6... Let's wait and see.
- UID data type: there is really no reason for not implementing this. Even more, because MySQL has implemented UUID() function on the DB side. There are of course ways around this i.e. UNHEX(REPLACE(UUID(), '-','')) - but it's a hack, and not really a solution. I've even seen demo applications on MySQL site which suggest using varchar(128) for storing it... horrifying thoughts.
- Collaboration with other Microsoft's server products - we have never try to make BizTalk or SharePoint use MySQL, but I guess that wouldn't be easy to implement :) OK, jokes aside, if you need application with database engine where you need collaboration with other Microsoft server products, MySQL is nothing but useless.
Conclusion
Even if there are only four CONs for using MySQL in .NET environment, and many more PROs, they are considerable obstacles for using MySQL in practical .NET development. If you use a lot of back-end programming, then better take Microsoft SQL Server 2005. Missing UID data type have been a real problem for us in one of our projects, where we switched in the middle of the project to Microsoft SQL Server.
Otherwise, it is a rock-solid database, which can handle LARGE amounts of data (sky is the limit - pardon me, HDD size is the limit) with superb performances, great 3rd party utilities, very fast installation and deployment and great support on mysql.com site. For example, we are using it's replication features in one of our .NET projects since almost a year now, and we never faced any problems - it is even more simple to implement than with Microsoft SQL Server.
We find it a perfect solution in middle-scaled projects (approximated 50-200 concurrent users), where price plays a major role for the customer, and where we don't need collaboration with other Microsoft's server products. Even for small projects - MySQL performances are MUCH better than those of SQL Server Express and there are no limits on size or number of users.
Idea of using of MySQL with .NET applications shouldn't be any taboo or "belief" issue - rather a decision based on the needs of application. It integrates with Visual Studio 2005 perfectly, and there are no connectivity or compatibility issues as with Oracle. By announcing the alliance with MySQL with all those bells and whistles, Microsoft is obviously telling us that they also do not have any problems with that idea. And Daenet's running projects are great examples that the whole thing works
This blog post has moved to a new address. If you would like to leave a comment, please do it there.