SQL Server Versus MySQL
Which database engine to select is a critical choice faced by most businesses. SQL
Server and MySQL are just two databases on the market. We develop using both databases,
and give an overview here of their features.
Microsoft SQL Server 2005 is Microsoft?s current version data management solution
that delivers secure and scalable applications while making them easy to deploy
and manage.
Open source MySQL has long been popular in the open source community. The recent
release of MySQL 5.0 has seen major changes in both features and performance to
bring the database system into enterprise-level standard. Free availability has
left many people questioning how well an open source system stacks up against its
commercial counterparts, and they're often surprised to find out how solid MySQL
is in production.
Sql Server and MySQL Database Performance
In some benchmarks, MySQL has proven to be faster in its basic 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. Additional performance gains can be had by using MySQL on a 64-bit processor, because MySQL uses an abundance of 64-bit integers internally.
Replication
Both SQL Server and MySQL Database engines can be scaled and support some degree
of replication.
SQL Server replication occurs within either a snapshot, transactional or merge model
. The snapshot model is a snapshot of the entire replicated database. .The transactional
replication model is more flexible for use where databases regularly change. The
database is monitored for any changes by the replication agent monitor and when
database changes do take place, they are transmitted to the subscribers. The third
replication model - 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.
Replication on MYSQL is straight forward because all SQL statements that change
data are kept in the binary log ? the changed data can be replicated easily to one
or more slave machines. This also means that data remains intact and replication
takes place even when the server goes down. On the scalability front, MYSQL scales
easily into large, query-heavy databases.
Security
Information Governance and security issues are perhaps the most important factor
for many companies selecting a database.
SQL Server is certified as C-2 compliant, meaning that the database has enough security
for government applications.
MySQL does not have this certification. SQL Server has more advanced authentication
and authorization features when compared with MySQL. The SQL Server database supports
native encryption capabilities and the encryption mechanism is based on a combination
of third-party certificates, symmetric keys and asymmetric keys. You can specify
asymmetric keys for increased security or symmetric keys for better performance.
A SQL Server DBA has also the choice of specifying his own user-defined security
functions through the encryption facility implemented in the .NET Framework. Recovery
SQL Server is more robust and less liable to suffer data corruption.
The SQL Server checkpoint mechanism means that even if the databases shut down unexpectedly
without warning, the data can be recovered. New features in the SQL 2005 release
provide enhanced mechanisms to manage data protection and rapid restoration. Mirrored
backups allow you to create multiple copies of the backup file. These backups have
identical content, so you can always mix the files in case one of the sets becomes
corrupt. Copy only backups enable you to make a copy of the database without
interrupting the sequence of other backup files. This copy can be used to restore
your database, instead of going through the full backup and translation log. You
can also save time by using partial backups for all filegroups, except those marked
as read-only.
MySQL default MyISAM recovery mechanism does not offer the same level of resilience - In the event of an unexpected shutdown data can be lost and the data store itself corrupted.
Final Thoughts - Which to use Sql Server of MySQL ?
From our point of view, the choice of which database engine comes largely down to the scale of the application ? for large company enterprise applications, Microsoft SQL Server 2005 is a sure winner ? it offers a comprehensive enterprise feature set including Analysis Services, Reporting Services, Integration Services, The database Engine itself and a strong set of tools handling replication, security and clustering. Where a less comprehensive database requirement exists, MySQL gives great functionality at low cost (in our view) it is not however the database engine of choice for mission critical applications.