NXT: SQL Server from an Agnostic's Perspective |
| Comparing MySQL and SQL Server seems like comparing apples to boulders. Database developers commonly assume MySQL is smaller, faster, and cheaper. A closer look, however, blows these myths out of the water. One database-agnostic developer digs deeper and finds that SQL Server can be the right choice, and in many scenarios the only choice, for any job. |
| By Justin Whitney |
| As a developer who codes database-driven apps for a wide variety of clients, my first priority is to find the right database for the job, whether it's SQL Server, Oracle, DB2, MySQL, or even Access. As someone who's partial to open source (and free software), I've fallen in love with MySQL's virtues and use it where it makes sense. |
| But with the release of SQL Server Express, that seems to be less and less nowadays. MySQL will always have a place in my toolbox. But when considering the scenarios and applications it previously played a role in, SQL Server deserves a second look. To that end, I've gathered a few of the false assumptions developers sometimes make when choosing between MySQL and SQL Server. I'll also describe practical business cases where SQL Server makes more sense, some of the market considerations an ISV should make, and some war stories from one company in particular who made the switch from MySQL with great success. |
Myths About MySQL and SQL Server |
| When first scoping a database project, I've usually reserved SQL Server for the "big" jobs, i.e. multiple servers, large budget, interoperability with other Windows apps. When a client needs something fast and cheap, that's when I would traditionally talk about MySQL. But all of this warrants a second look. In fact, making assumptions like this can lock you into a non-scalable solution for when your client, flush with success, suddenly finds deep pockets. |
| MySQL = small and fast; SQL Server = big and slow |
| Many Web shops like to use MySQL for simple forward-only (static cursor), read-only data storage. But when it comes to read/write and dynamic cursors, especially when transactions are involved, many companies have shown marked improvement by moving to SQL Server. For example, Reed.co.uk states that "MySQL used 11.7 per cent more CPU time than SQL Server 2005 and was 7.5 per cent slower when responding to queries." Likewise, IBS Synergy Sdn Bhd (IBSS), a Malaysian ISV, says "To support a five gigabyte database, SQL Server 2000 is about 10 times faster than MySQL. The performance is even more impressive when it comes to restoring the data-SQL Server 2000 is between 20 to 30 times faster." |
| As far as size goes, MySQL comes in at 37MB while the SQL Server Express download weighs 50MB - larger, yes, but not significantly so. Use SQL Server Compact Edition (CE), though, and the download drops to 2MB. |
| MySQL = free; SQL Server = $$$ |
| I recently read a side-by-side comparison of MySQL and SQL Server that mentioned Microsoft having a "development" version of its database available, as long as you don't use it for commercial purposes. It's time to put that myth to rest. |
| First of all, Microsoft offers two versions of SQL Server at no cost: SQL Server CE, which is used primarily for mobile devices but can be used in desktop and Web apps, as well, and SQL Server Express, a full featured, but lightweight and redistributable version of Microsoft's SQL Server. You also have a $49 (estimated retail price) version called SQL Server Developer Edition for developing and testing demos and prototypes. |
| Second, like SQL Server, MySQL has multiple licensing options. If you think MySQL is run by a community-driven non-profit, guess again. It's actually managed and distributed by a for-profit Swedish software company who has effectively monetized their open source platform. If you look through their products, you'll see that the MySQL Enterprise Editions are priced per server, with varying levels of tech support. Sound familiar? |
A Closer Look at Application Scenarios |
| Thinking in abstracts sounds too much like marketing jargon. I prefer looking at specific scenarios. Like I mentioned, I try to use the right tool for the job rather than show unquestioning loyalty to one product regardless of the application. With that in mind, here are some of the development scenarios ISVs face in which SQL Server and the Microsoft stack make more sense. |
| 1. You're building a heavy XML app. |
| PHP/Perl scripting gives you plenty of nice XML functionality when using MySQL. But in terms of full-blown XML support, you'll need SQL Server Express for native XML data types, indexing, full-text XML search, and XQuery. MySQL has none of these features. |
| If you're building a page with asynchronous functionality, then you may have JavaScript calls to a Perl script, just as an example. The Perl queries your MySQL database and returns a data set, which gets parsed into XML usable by maybe some 3rd party Ajax functions (assuming you're not doing JSON). This looks pretty straightforward. |
| But now consider extensibility. What if you want to extend this functionality to a mobile device? Or a Windows Vista gadget? Or call your Perl script as a Web service in a future as yet unimagined application? Most likely you'll be stuck rewriting the code. Here's where one of the unseen benefits of SQL Server comes into play. For the same amount of dev time (if not less), you can build a simple C# Web service that pulls native XML from your SQL Server database, instead of using Perl/MySQL. The result: with this one change you have the flexibility to incorporate that service into other apps, including other Web services. Your JavaScript calls work the same, but now you've created a foundation for creating, and upselling, new products and services based on your efforts. |
| 2. Your customer needs Business Intelligence, including reporting and analysis. |
| If you're building a high-transaction database, you almost certainly need BI tools to go with it, whether the customer is an external client, and internal client, or you. Because of MySQL's streamlined functionality, this would be considered a 3rd party partner opportunity. In other words, MySQL doesn't have BI tools, but they know someone who does. |
| To be honest, if you're just doing a straight apple-to-apple comparison, you won't find much more with SQL Server Express. You have some SQL analytical functions and query optimization, but that's about it. That is, until you discover the SQL Server Express Toolkit, which includes Connectivity Components, Business Intelligence Development Studio, Management Studio Express, and a Software Development Kit. The Business Intelligence Development Studio [http://msdn2.microsoft.com/en-us/library/ms173767.aspx] along is worth the download. This free extension for Visual Studio gives you extensive reporting [http://msdn2.microsoft.com/en-us/library/ms173745.aspx] and analysis [http://msdn2.microsoft.com/en-us/library/ms173709.aspx] services like report wizards, multiple data mining models, and data cubes. |
| Also, as with the other scenarios, your feature list grows when you scale up. For example, SQL Server Workgroup edition adds the BI Development Studio natively, as well as enterprise management tools and a report builder, among other services. SQL Server Standard edition goes on to add data warehousing, data analytics, Unified Dimension Modeling, and nine different data mining algorithms. Enterprise edition, of course, adds another dozen or so BI services on top of that. |
| 3. You're deploying your app on multiple Windows platforms. |
| While MySQL comes in an embedded version, when it comes to integrating throughout the stack, it can't begin to compete with SQL Server. SQL Server CE (Compact Edition), which replaces SQL Server Everywhere, uses a sub-set of T-SQL, integrates into Visual Studio, and offers developers a familiar framework and syntax for building distributed mobile apps. Redistribute CE whether you're building a standalone mobile app or syncing with SQL Server 2005 in a client/server architecture. Whatever configuration you dream of can be managed in the same integrated development environment. |
| 4. You need to prepare for wild success and high scalability. |
| No doubt you can think of many stories in which innovative new sites become victims of their own success by failing to scale. That can still happen using SQL Server, obviously--if you build the next MySpace, then at some point you'll hit a few bumps en route to becoming a gazillionaire. But the high scalability of SQL Server Enterprise Edition gives you performance beyond read-only or read-mostly transactions. It scales to 128 CPUs and offers distributed partition views and cross-disk filegroups. So you can code your prototype on the Developer Edition, bump it up to Standard for deployment, then up to Enterprise as demand increases. In fact, that's one of the reasons MySpace itself migrated to the Microsoft Web platform, including SQL Server 2005 and ASP.NET 2.0. |
Stories from the Field |
| In addition to looking at different deployment scenarios, I wanted to talk to someone with extensive non-Microsoft experience that ended up choosing SQL Server as a primary platform. So I talked with Vishwenath Kizhapandal, Chief Operating Officer, iLink Systems. After a decade developing custom apps with Java and open source tools, iLink [http://www.ilink-systems.com/], now a Microsoft Gold Partner, made the switch to Microsoft solutions two years ago. We talked about specific apps he's worked on and why SQL Server gave iLink more power, more flexibility, and as a result, more opportunities. |
| The Problem Is Not the Snow |
| While many articles offer a direct comparison of free databases, such as MySQL to SQL Server Express to DB2 Lite, Kizhapandal says, "It would be unfair to compare just the database without comparing the overall development cost. |
| "You don't typically have a solution with just a database alone. You're always building an app which leverages database technologies. [For example] if your app needs good reporting, what's the point of having data collected if you can't report on it?" iLink customers need the advantages of a full-featured development environment, not just reporting but triggers, stored procedure debugging, and other features. While MySQL added new support for some of these features in version 5.0, other important features are only available through 3rd party tools. |
| Meanwhile, Microsoft integrates programming and business intelligence tools through the entire stack. For example, you can call a stored procedure from an ASP.NET page, step into the procedure, and debug line by line. On one occasion, "it took less than a month to put together a dashboard using the Developer Edition, something not that easily possible with MySQL." |
| "People don't realize, they're trying to save a few thousand in licensing costs, and end up creating several thousands in DEVELOPMENT costs." |
| Kizhapandal likens it to a recent drive through Seattle in the snow. "It wasn't the snow, it was the other drivers who were the problem." |
| A Full-Blown Database in the Field |
| Using the Express and Developer Editions of SQL Server lowers development costs even further while not limiting future deployment. Says Kizhapandal, "We built a distributed app for one of our clients--a centralized server with field-based clients. Clients had a need for storing the data and syncing with the central server. Problem: if we used SQL Server [Standard Edition], client costs would've been prohibitive. Instead we used MSDE [Microsoft SQL Server Database Engine, the precursor to SQL Server Express]. The central server had to have pretty complex needs, but we already had the Developer Edition of SQL Server, so we could take full advantage of the .NET development environment. Then, when we deployed, all we needed on the client was the MSDE engine." iLink initially developed the app on MSDE a couple years back, before SQL Express was available. "But since we had developed the whole app using existing development tools we could easily convert to SQL Express, which we're doing now. MSDE had some limitations but SQL Express has removed connection limitations and other limits in MSDE. Now clients have full-blown database." Database size is limited to 4GB, of course. But field clients don't need to store more than that, so the limitation doesn't present a problem. |
| More than Mobile-Ready |
| As mentioned already, SQL Server CE, formerly SQL Server Everywhere, formerly SQL Server Mobile, gives developers a low footprint solution for mobile devices. While MySQL also has a small footprint, Kizhapandal says it's important to keep in mind that "no one develops just a mobile app alone. Most of them are part of an app family. We have a product suite with desktop apps, enterprise apps, and mobile apps that have to talk with the other apps to upload data. Previously, you developed either flat database apps or apps that talked over the server to store data. Now, you store data locally, then sync data when you have better connectivity. |
| "This is where the CE Edition comes into play. It has a small footprint, but that's not the biggest benefit. This is a complete database. When deploying, you just take the database and bundle it into the app. When you develop the app, you are developing in a full-blown SQL Server edition. You have the ability to take the same exact database and drop it into the mobile device." |
| Prototyping with Potential |
| "It's also true that if you're trying to do proofs of concept, it's tempting to go MySQL because you don't need approval or budget from the boss. But even there, it doesn't make sense--you're not going to be developing proofs with more than 4G data, so use SQL Express. And once you do the pilot and your clients see it work, you don't have to rebuild it in SQL Server. |
| "We do a lot of prototyping and proofs of concept, for example showing WPF [Windows Presentation Foundation] technologies for our clients to help them understand the power behind user experience. You can't demonstrate that except by building the UI and letting them play with it." |
| Similarly, "we also do proofs with the BI stack because not a lot of people understand what they can get from their data using business intelligence until you show them. For example, we built a small 15-line PI [Performance Indicator] for one of our insurance clients. Once we deployed it in the BI stack, they were able to see the charts and graphs. We did a larger project for them after that." |
| "I Grew Up." |
| I asked Kizhapandal about the intense loyalty some MySQL-only developers feel for their chosen platform. He had this advice for other ISVs and start-ups. "They need to be able to understand the user metrics. This is more important than just storing data and just using a shopping cart. If you understand your users today, you'll be able to do more in the future. Are you just storing data or are you trying to understand your business?" |
| He says, "I was in the Anything-But-Microsoft crowd. The simple answer is, I grew up. Once you understand you're trying to solve business problems, not technical problems, you'll get it." |
| Where to Go from Here |
| While SQL Server has long been positioned as the optimum solution for enterprise-level apps, developers often disregard its applicability to other market tiers. If nothing else, developers at mid-level ISVs should consider SQL Server for prototypes, Web apps, multi-platform apps, or any scenario that require versatility, portability, and scalability on the part of the database. ISVs who want to expand their potential customer base by switching over from MySQL might also look at available migration assistance. |
| For instance, the SQL Server product team has released a migration tool called the SQL Server Migration Assistant [http://www.microsoft.com/sql/solutions/migration/default.mspx]. Current versions cover Access, Oracle, and Sybase migration. |
| Also, Microsoft's NXT program [http://www.isvnxt.com/nxt.htm] helps ISVs at all tiers migrate their products to SQL Server. Companies with less than $5M in revenue benefit from complimentary licensing and MSDN subscriptions, instructor-led training, and Microsoft Partner Program benefits. ISVs with over $5M in revenue gain additional benefits, like funding, delivery partner services, and marketing support for their new solutions. |
| Here are a few more resources to check out: |
| SQL Server 2005 Product Comparison Chart: |
| http://www.microsoft.com/sql/prodinfo/features/compare-features.mspx |
| More on SQL Server 2005 Express Edition |
| http://www.microsoft.com/sql/editions/express/features.mspx |
| The SQL Server Express Toolkit |
| http://msdn2.microsoft.com/en-us/library/ms365251.aspx |
| NXT Funding Benefits for ISVs >$5M |
| http://www.isvnxt.com/benefitsFunding.htm |
| NXT Technology Justification |
| http://www.isvnxt.com/whyTechnology.htm |