Business Need:
Currently we are in the cross-roads of growing from small-mid sized company to large scale K-12 online Test Delivery and reporting Solution provider. We primarily operate on K-12 Summative Market and its absolutely critical for us to provide 100 % accuracy and availability during Student Test windows. Its cruel for students to spend all night to prepare for tests and then realize that their tests are cancelled because of glitches in the System, Network, Datacenter ,etc. Kids doesn’t care about excuses and all they want is to take the tests on Scheduled test windows. We can’t say that 99.999 percent of the kids answers are stored and scored correctly. That would wreak havoc on our business and it may a ruin a kids future. He/she might be on the way to become the founder next big thing or Contributors like Linus Torvalds / Andy Tenanbaum.
When I ask a simple Question, why software products doesn’t come with any warranty?. When I buy a Tire , they guarantee its not going to crash or explode in the middle of the road. It doesn’t matter its one out of million or billion and they guarantee the highest quality for each and every tire.
I know its super crazy to compare Software products to BFGoodrich tires. Is it really?.May be our expectations for software products should go up, so we get better products.
Maybe one day, SQL Server might provide a warranty “I stand by our workmanship and you will never encounter system crash”.
Why SQL server doesn’t guarantee anything?. We have to make sure each and every student tests would be securely stored , scored and reported correctly. Eery kid is super important for our business.
How can we achieve this kind of SLA’s while using the products without any warranties?.
Right now , we are in the Quest for finding the best product available to suit our business Data needs.
From Application perspective , if our clients needs to support 2X-100X Volume, we just keep adding more machines and upgrading the network infrastructure. Its still takes time and effort to build the infrastructure but its fairly established and proven strategy. Scale-out approach works like a charm for our application and we have tested scale-out infrastructure with Load/Stress Testing Suite.
If our clients need to support 2X-100X volume , we can’t say we have a fool-proof strategy to support the data needs.
Scale-up is an option but we can’t afford to buy bigger beefier machines for all our customers.
Scale-out is an option too. Replication , Mirroring with Snapshot , Partitioning , Storage engineering to improve the IOPS are all options to improve the scalability and throughput times.
But setting up the infrastructure and managing it is not a trivial task.
If you ask professionals who have been working with SQL Server for past 10-20 years, they will tell you the horror stories and sleepless nights trying to keep the systems up and running.
I have Travel Industry experience and Thanksgiving and Holiday seasons are our DBA’s nightmare days.
All the suppliers want to update the inventory and change prices and availability data as well as the world wants to search and book their trips.Simply put, Ton of Read / Write Contention , which causes latency which translates into frustrated customers and suppliers, which is not good for any business , let alone travel industry needs.
We use SQL Server for all our data needs. Our typical setup involves High Availability as well as Disaster Recovery solutions based on the specific client needs.
We usually have StandAlone or Clustered SQL Server as Primary and Mirror the required databases for High Availability Solutions.We take full / log backups and ship it to remote datacenter location for Disaster Recovery solution.
Because we use SQL Server for our data needs, you get ACID Compliancy out of the box. We don’t have to engineer anything. Because you get ACID out of the box, you make trade-offs on Concurrency and Partition Tolerance.
CAP Theorem:
C – Consistency
A – Availabilty
P – Partition Tolerance
You can’t improve all three at once and you have to sacrifice one for other two.
http://en.wikipedia.org/wiki/CAP_theorem
Based on Wiki Definition :
In theoretical computer science, the CAP theorem, also known as Brewer’s theorem, states that it is impossible for a distributed computer system to simultaneously provide all three of the following guarantees:
Consistency (all nodes see the same data at the same time)
Availability (a guarantee that every request receives a response about whether it succeeded or failed)
Partition tolerance (the system continues to operate despite arbitrary message loss or failure of part of the system)
SQL Server as well as MySQL both are geared toward Consistency and Availability and makes sacrifices on Partition Tolerance.
Using SQL Server We have following Advantages :
1. Team Expertise :
Everyone in our team knows how to write queries to get things done. Overall everyone is comfortable using SQL Server and Management Studio.
Special Tools and Licenses has been bought over the years to automate some of the mundane processes .
2. Ease of Use and Tools Available :
We have bought RedGate Suite which does the database compare and deployment like a champ. All our deployments are made easy with Tools we bought over the years. Visual Studio and Data Tools are pretty heavily used in our development environment.
3.Cost :
We are spending quite a bit for on SQL Server licensing costs. We use enterprise edition for few customers and majority of the small clients we use standard edition. We can’t really use one code-base because Partitioning is not available on standard edition.
This the primarily driving factor for us to look into MySQL. We are in the business of competing with other online test provider and cost savings is huge competitive advantage as long as the product provides on-par or better performance.
Article talking about compelling Reason to move from SQL Server to MySQL.
http://dev.mysql.com/tech-resources/articles/move_from_microsoft_SQL_Server.html
Factors Considered in above Article:
Installing and Configuring MySQL vs. SQL Server
Comparing Core SQL Server and MySQL Features
Storage Engines
Development Capabilities
Performance and Scalability
Management, Monitoring, and Tuning
SQL Server to MySQL Transitioning Cost
San Francisco SQL Group speech on How MySQL and NOSQL are implemented at Craigslist.
MySQL and NoSQL at Craigslist YouTube Video.
Factors Considered :
Ease of Use
Some of the NoSQL Products are building SQL Like Query language.
Building SQL on NoSQL Engines ( Hive Query language ).
Caching Infrastructure using Redis/MemCached
The initial readings and research has been strongly favors experimenting with MySQL for our data needs.
Why we didn’t do this earlier. Like everyone else, we used SQL Server and there wasn’t enough time , expertise , business need to look into other products.
Its the same situation where we don’t have enough expertise or time but business needs have changed. We have to find ways to support our business and MySQL seems to be good alternative for SQL Server.
List of Things to do :
1. Install MySQL Server
2. Figure out the existing tables business needs which in turn would help us decide which storage engine to choose for what tables.
Not all tables need ACID and MyISAM seems to be match made in heaven for those tables.
3.Migrate modular tables and related objects to MySQL.
This involves figuring out those tables and migrating those tables and data, stored procedures , functions , custom data types and other related objects.
We do have some CLR Stored procedures and functions.
4. Modify our application Data Layer to talk to MySQL Storage engine.
Top Reasons to Use MySQL
http://www.mysql.com/why-mysql/topreasons.html
1. Scalability and Flexibility
2. High Performance
3. High Availability
4. Robust Transactional Support
5. Web and Data Warehouse Strengths
6. Strong Data Protection
7. Comprehensive Application Development
8. Management Ease
9. Open Source Freedom and 24 x 7 Support
10. Lowest Total Cost of Ownership
Tech Republic Article on MySQL Vs SQL Server
MySQL may be free, but what if money isn’t the only determining factor? Find out how these two database heavyweights stack up against each other and how to decide which one to use as your database system.
Two of the most popular back-end data stores Web developers work with today are MySQL and SQL Server. They are fundamentally similar in that both are data storage and retrieval systems. You can use SQL to retrieve data with either because both claim support for ANSI-SQL. Both database systems support primary keys and key indices, so you can also create indices used simply to speed up queries and for constraining input. Further, both provide some form of XML support.
Aside from the obvious difference of price, what distinguishes these two products from each other, and how do you choose between them? Let’s take a look at the core differences between these two products, including licensing costs, performance, and security.
Core principles are the root differences
The differences begin with principles: open vs. proprietary. SQL Server with its closed, proprietary storage engine is fundamentally different from MySQL’s extensible, open storage engine. You’re stuck with the SQL Server’s Sybase-derived engine for better or worse, while MySQL provides multiple choices such as MyISAM, Heap, InnoDB, and Berkeley DB.
This open vs. closed difference is, by itself, enough reason for some folks to choose one over the other. But, there are some technical differences as well. To begin with, MySQL doesn’t fully support foreign keys, making it less of a relational database than SQL Server, which has full relational features. Also, MySQL has previously lacked support for stored procedures, and the default MyISAM engine doesn’t support transactions.
Licensing costs: MySQL isn’t always free, but it is cheap
When it comes to licensing costs, both products use a two-tiered scheme. With SQL Server, the most popular way to get a free developer license is to purchase a license for Microsoft Office Developer or Microsoft Visual Studio, both of which give you a free “development use only” SQL Server license. If, however, you want to use it in a commercial production environment, you’ll have to pay for at least the SQL Server Standard Edition, which will set you back around $1,400 for five client connections.
On the other hand, MySQL is open source and licensed through the GNU General Public License (GPL). For developers, this means that as long as the project you are working on is also open source, you don’t have to pay to use MySQL. If, however, you plan to sell your software as a closed-source product, you’ll need to pick up a commercial license, which currently costs $440 for up to nine clients. Schools and nonprofits are exempt from this commercial licensing requirement.
Performance: Advantage MySQL
In terms of pure performance, MySQL is the leader, mostly due to its default table format, MyISAM. MyISAM databases are very compact on disk and place little demand on CPU cycles and memory. MySQL can run on Windows without complaint but performs better on UNIX and UNIX-like systems. You can experience additional performance gains by using MySQL on a 64-bit processor (e.g., one of those sweet SPARCstations), because MySQL uses an abundance of 64-bit integers internally. Much of the very busy Yahoo! Finance portal uses MySQL as a back-end database.
As I mentioned, with MySQL, you have a choice of table formats, but generally, these nondefault choices exact a cost in increased resource usage over MyISAM. Typically, though, these alternative table formats provide some additional functionality. For example, Berkeley DB supports transactions and actually has better performance with indexed fields than MyISAM.
When it comes to performance, SQL Server’s strength—providing many more features than its competitors—is also its weakness. Granted, many of these features are geared toward performance tuning, but being a feature-rich environment means sacrificing something else. In this case, the cost is additional complexity, disk storage, memory requirements, and poorer performance. If you can’t afford to support SQL Server with powerful hardware and trained expertise, you should definitely look elsewhere for a DBMS because you likely won’t be happy with the results.
It’s worth noting that both systems will work well within either a .NET or J2EE architecture. Similarly, both will benefit from RAID, and both will perform best if the data store is on a hard drive or array that is solely dedicated to that purpose.
Replication and scalability: A dead heat
MySQL keeps a binary log of all SQL statements that change data. Because it’s binary, this log can be used to replicate data from the master to the storage on one or more slaves very quickly. Even if the server goes down, the binary record is still intact, and replication can take place. For query-heavy databases systems, MySQL scales easily into large data farms.
In SQL Server, you can also record every SQL statement, but doing so can be costly. I know of one development shop that had to do this because of other architectural issues, and the sheer volume of data that they were storing on tape was quite remarkable. Instead, SQL Server relies on elaborate mechanisms of record and transaction locking, cursor manipulation, and dynamic replication of data to keep database servers synchronized. If you’re skilled at juggling these mechanisms, replication is pretty easy.
Security: Also tough to call
Both products have perfectly acceptable default security mechanisms, as long as you follow the manufacturer’s directions and keep up with your security patches. Both products operate over well-known default IP ports, and, unfortunately, those ports draw intruders like flies to honey. My firewall logs are always chock-full of folks trying to contact nonexistent database instances on my machine over the default ports. Fortunately, both SQL Server and MySQL allow you to change that port should the default become too popular a destination for your taste.
Recovery: Advantage SQL Server
Failsafe and recovery is one area where MySQL, in its default MyISAM configuration, falls a little short. With MyISAM, a UPS is absolutely mandatory because MyISAM assumes uninterrupted operation. If it is shut down unexpectedly, the result could be corruption of the entire data store and loss of all your data. SQL Server, on the other hand, is far more resistant to data corruption. SQL Server’s data goes through multiple checkpoints as it passes from the keyboard to the hard disk and back out to the monitor. And SQL Server remembers where it was in the process even if it happens to be shut down without warning.
The best choice depends on the situation
If you were hoping to get an ironclad recommendation that one database is better than the other, I’m going to disappoint you. From my point of view, any database that helps you do your job is a good database; one that doesn’t is a bad database. I can tell you that to make a good decision about which of SQL Server and MySQL will help you most, you’ll need to look beyond politics and hype and instead look at function and mission. What do you want to accomplish?
If you’re trying to build a .NET services architecture, synchronizing data between multiple disparate platforms, or learning the loftier precepts of database management, SQL Server will help you most. If you’re building a third-party-hosted Web site, pushing a lot of data out to a lot of clients, or have a budget in the neighborhood of free, then MySQL will be your best bet.
I will keep posting about my experience with this Experiment.
MySQL Resources :
Website : http://www.mysql.com/
Forums : http://forums.mysql.com/
Database Rankings site :
http://db-engines.com/en/ranking
Introduction to MySQL Videos:
https://www.youtube.com/watch?v=zrDU7H9OLmI