Since i cannot show the differences in a two-column like table. I am first putting
MS SQL Server 2005 and then MySQL 5.x.
MS SQL Server 2005
Brief Overview
- SQL Server is a full-fledged database system developed specifically for large enterprise databases. All advanced features of a relational database are fully implemented.
- Once you purchase the product, you are only limited to the Sybase-derived engine.
Licensing Costs (both have two-tiered licensing scheme)
1st Tier
- SQL Server provides a free license for "development use only". Means, database system cannot be deployed in a commercial environment
2nd Tier
- For use in a commercial environment, one would need to purchase the SQL Standard Edition license costing app $1400. It is a fully-fledged relational database system complete with all features needed to develop and deploy enterprise databases.
Performance
- The system is more complex, places additional requirements on memory and disk storage. The performance will benefit greatly with RAID and a dedicated hard drive for the data store. Best performance under windows systems.
Replication & Scalability (Both are scalable and support replication to a different degree of complexity)
- In SQL Server, you can record every SQL statement, but doing so can be costly. It 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.
- MS SQL offers replication in a number of models: snapshot, transactional and merge.
- A snapshot application is a simple snapshot of the entire replicated database. It is a time consuming process but can be useful for databases that rarely change or as a way to establish a baseline for replication between systems.
- A transactional replication is a more flexible solution for databases that regularly change. The database is monitored for any changes by a replication agent monitor. When changes do take place, they are transmitted to the subscribers.
- Finally, merge replication allows simultaneous changes to the database by both the publisher and subscribers. Changes can be made without an active network connection, and any conflicting changes are resolved through a predefined conflict resolution algorithm.
Increased replication support comes at the expense of a greater degree of complexity.
Security (Security remains a major concern for most businesses and a compelling consideration in choosing a database system.)
-Both DBMS support security at the base level.
-SQL server fully supports security at the column level.
Third party Security Certificate
SQL Server has been certified as C-2 compliant, which means the database system has adequate security for government applications
Data Corruption & Recovery
SQL Server is more failsafe and less prone to data corruption. SQL has a robust checkpoint mechanism whereby the data passes from the keyboard to the hard drive before showing in the monitor. Even if the databases shut down unexpectedly without warning, the data can be recovered.
Enhanced mechanisms to manage data protection and rapid restoration. Mirrored backups allow you to create multiple copies of the backup file
and now MySQL 5.x
MySQL 5.x
Brief Overview
- The latest release of MySQL, the 5.X offering, has rounded up on features that lagged commercial equivalents such as SQL Server. Triggers, stored procedures, Views, Information Schema, Serverside Cursors, and Precision Math are features that are implemented but not yet stabilised (in BETA).
- By contrast, MySQL is an open storage engine offering multiple choices.
MySQL has six distinct table types.
MyISAM
MERGE
ISAM
HEAP
InnoDB
BDB or BerkeleyDB Tables
Licensing Costs (both have two-tiered licensing scheme)
1st Tier
Free to use under any environment, provided one abides by GPL license rules.
2nd Tier
MySQL also provides licensing schemes to circumvent some of the restrictions of the GPL license and costs app $400/year. Non-profit organisations and educational establishments are exempt from this fee.
Performance
- MySQL is better than SQL on a variety of performance platforms, because of default table format of its MyISAM database. They are compact on disk and use less memory and CPU cycles. It is better suited for UNIX and UNIX-like systems than windows.
Replication & Scalability (Both are scalable and support replication to a different degree of complexity)
- MySQL keeps a binary log of all SQL statements that change data. Because its 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.
- MySQL features support for one-way, asynchronous replication, in which one server acts as the master, while one or more other servers act as slaves. This is in contrast to the synchronous replication which is a characteristic of MySQL Cluster
- In MySql replication is relatively easy because changes are logged in binary format. Data can be moved to one or more slave machines even when the server goes down.
Security (Security remains a major concern for most businesses and a compelling consideration in choosing a database system.)
- Both DBMS support security at the base level.
- MySQL is limited to supporting basic security at the table level, via the SQL command.
Third party Security Certificate
- MySQL has no such certification
Data Corruption & Recovery
- MySQL falls short in recovery with its default MyISAM mechanism. 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.
Strong & Weak Points
MS SQL Server 2005
Strength
Superior replication, clustering, and security
Best for enterprise-level apps.
Better for windows applications.
Tightly integrated with VS.net.
Advanced reporting controls
Advanced data management tools
Weak Point
Complex replication.
Expensive License
MySQL 5.x
Strength
Relatively Inexpensive License.
Easy replication.
Better suited for Unix/Unix-like systems and well for windows.
Multiple storage engines (6)
MySQL is most effective for read-only environments and the web/edge tier applications
Weak Point
Relatively lower Security
MySQL is not effective for applications in demanding OLTP environments, requiring enterprise-class reliability, availability, and scalability.