Ms Sql Vs Mysql A Comparison Computer Science Essay

Published:

This essay has been submitted by a student. This is not an example of the work written by our professional essay writers.

Database engines are a crucial fixture for businesses today. There is no shortage of both commercial and open source database engines to choose from. Microsoft SQL Server 2005 is Microsoft next-generation data management solution that claims to deliver secure and scalable applications while making them easy to deploy and manage. MySQL has long been the DBMS of choice 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 standards.

This paper aims to give the low-down on features most desirable to database developers and compare both database management systems in light of these features.

Contents

Introduction …………………………………………………………………………

4

The Open Source versus Commercial License Paradigm …………………………

5

Performance …………………………………………………………………………

5

Replication ……………………………………………………………………………

6

SQL Server 2000 and MySQL v5.0 limits ……………………………………………

7

Data Storage …………………………………………………………………………

8

Database Features ……………………………………………………………………..

9

Security ………………………………………………………………………………

10

Database Vulnerability ………………………………………………………………..

12

Recovery ………………………………………………………………………………

13

Concluding Thoughts ………………………………………………………………….

15

Refrences ………………………………………………………………………………

18

The most obvious difference between the two products is in philosophy. SQL server is essentially a proprietary storage engine. Once you purchase the product, you are only limited to the Sybase-derived engine. By contrast, MySQL is an open storage engine offering multiple choices: InnoDb, BerkleyDB, MyISAM and Heap amongst other supported engines.

The second marked difference between the two database systems is in the technical features and specifications implemented. SQL Server is a fully-fledged database system developed specifically for large enterprise databases. All advanced features of a relational database are fully implemented. MySQL, on the other hand, has only come out of edge in the acceleration front, with recent support for foreign keys.

The latest release of MySQL, the 5.X offering, has rounded up on features that lagged commercial equivalents such as SQL Server. There is now full support for cursors, complete views and stored procedures according to the SQL 2003 syntax. Other features that were a major differentiator between MySQL and SQL Server are now part of the 5.X release. Triggers, stored procedures and foreign keys are fully implemented.

But is MySQL 5.0 really up to industry-level database standards? The features outlined above have only been implemented in the latest release and are yet to fully stabilize. They are yet to be rationalized across the different databases in the MySQL suite of products. InnoDB, MyISAM, MaxDB and the new data clusters. MySQL is still carrying four distinct database architectures and it proves very challenging to fully implement replication, parallel processing, journaling and recovery across different databases.

SQL Server continues to have the edge, as the advanced features list has long stabilized. The latest release of SQL Server 2005 provides the necessary technological underpinnings to keep it in the higher-end of database systems. There is now a far greater integration with Microsoft .NET Framework, a development environment that greatly facilitates coding without the need to learn advanced features of SQL. It is also tightly integrated with Visual Studio .NET. This will provide better support for XML, querying multi-dimensional data in the SQL server and a set of advanced reporting controls. Finally, XML is now a native data type within XML. This enables a DBA to modify an XML document within the DBMS environment, query the document and validate it against an XML schema.

The Open Source versus Commercial License Paradigm

Another difference between the two database engines is licensing costs. Both databases have a two-tiered licensing scheme, but have little else in common.

The first licensing scheme is essentially free. SQL Server provides a free license for "development use only". What this means is that the database system cannot be deployed in a commercial environment. MySQL, on the other hand, is free to use under any environment, provided one abides by GPL license rules.

This brings us to the second-tier of licensing. For use in a commercial environment, one would need to purchase the SQL Standard Edition license. It costs a whopping $1,400, a substantial investment for a small business. However, it is a fully-fledged relational database system complete with all features needed to develop and deploy enterprise databases. This goes a long way towards justifying the hefty price tag.

MySQL also provides licensing schemes to circumvent some of the restrictions of the GPL license. This is especially important for companies that deal with proprietary information. These commercial licenses are piloted by MySQL AB, the company behind the development of MySQL, and cost a very affordable $400. Non-profit organisations and educational establishments are exempt from this fee.

Performance

In terms of performance, MySQL fairs better than SQL on a variety of platforms thanks to the default table format of its MyISAM database. They are compact on disk and use less memory and CPU cycles. While the database system performs well on Windows, it is better suited for UNIX and UNIX-like systems. The performance can further be tuned on 64-bit processors (such as SPARC stations) because of the internal use of 64 integers in the database. The latest release of MySQL 5.0 has seen further improvements in engine performance, through compact mode support. Engines such as InnoDB and NDB Cluster uses 20% less space than it required in previous versions.

For additional non-default MySQL features, there is an increased demand on resource usage and this has obviously an effect on performance. For instance, alternative table formats on MyISAM or transactions on Berkeley DB will require additional memory usage. These features will, however, offer additional functionality.

For SQL Server, the full-set of powerful features that surpasses that of most competitors has a negative effect on performance. It's true that many of these features are geared towards performance tuning up overall the system is more complex, places additional requirements on memory and disk storage. These results in a poorer performance compared with MySQL. The performance will benefit greatly with RAID and a dedicated hard drive for the data store.

Replication

Both Database systems are scalable and support replication to a different degree of complexity.

Replication on MYSQL is easy because all SQL statements that change data are kept in a binary log. Because of the binary nature of the records, data can be replicated easily and quickly 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.

Unlike MySQL one-way replication, SQL Server 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.

However, increased replication support comes at the expense of a greater degree of complexity. This is due to SQL's complex transaction and record locking mechanism, cursor manipulation and synchronization of dynamic data replication. If you are skilled in these elaborate mechanisms, then replication and migration shouldn't be an issue.

SQL Server 2000 and MySQL v5.0 limits

Although many of the limits placed by the database are for the purists, some are important for everyone to take note.

Some of these built-in limitations may be crucial for database design. Varchar size especially can be problematic, as often stored data such as comments or articles may be forced into BLOBs or TEXT columns. Total row size is also important for this reason.

Feature

SQL Server 2000

MySQL v5.0 (MyISAM)

Column name length

128

64

Index name length

128

64

Table name length

128

64

Max indexes per table

250

64 (128 with recompile)

Index length

900

1024

Max index column length

900

255

Columns per index

16

16

Max char size

8000

255

Max varchar size

8000

65532

Max blob size

2147483647

2147483647

Max columns in GROUP BY

Limited by number of bytes (8060)

64

Max columns in ORDER BY

Limited by number of bytes (8060)

64

Tables per SELECT statement

256

31

Max columns per table

1024

3398

Max table row length

8036

65534

Longest SQL statement

16777216

1048574

Constant string size in SELECT

16777207

1048565

Data Storage

SQL Server with its closed, proprietary storage engine is fundamentally different from MySQL extensible, open storage engine. Its Sybase-derived database engine boasts of an adaptive algorithm that does most of the tuning that earlier needed to done manually. While SQL Server 2000 chooses the strategy of a single engine doing all the work, MySQL supports pluggable storage engines that can be chosen depending upon the facilities needed.

MySQL's strategy is useful for various types of database use: quick read access to data without the need for transactional overhead is provided by the built in MyISAM engine, whereas InnoDB, a third party database engine owned by Oracle and licensed under the GPL is most often used for transactions and other features at the cost of some read performance. The MEMORY storage engine creates tables with contents that are stored in memory, and is useful for embedded database applications. NDB Cluster is the storage engine used by MySQL Cluster to implement tables that are partitioned over many computers.

The FEDERATED storage engine stores data in a remote database. In its current release, it works with MySQL only but its future releases will be able to connect to other data sources using other driver or client connection methods.

InnoDB engine has the most advanced database feature set. The disadvantage to MySQL's pluggable database engine scheme is that care must be given when selecting the engine to use when designing the database before use.

MySQL database engine feature comparison

MyISAM

InnoDB

MEMORY

NDB

Multi-statement transactions, ROLLBACK

-

X

-

X

Foreign key constraints

-

X

-

-

Locking level

table

row

table

row

BTREE indexes

X

X

-

X

FULLTEXT indexes

X

-

-

-

HASH lookups

-

X

X

X

Other in-memory tree-based index

-

-

4.1.0

-

GIS, RTREE indexes

4.1.0

-

-

-

Unicode

4.1.0

4.1.2

-

-

Merge (union views)

X

-

-

-

Compress read-only storage

X

-

-

-

Relative disk use

low

high

-

low

Relative memory use

low

high

low

high

Database Features

One of the critical features of any database engine is data integrity. ACID (Atomic, Consistent, Isolated, and Durable) compliance is a qualification that assures data integrity. ACID essentially means that when a transaction is performed within a database, either the whole transaction is successful and the information is written to the database, or nothing is written. Both SQL Server 2000 and MySQL supports ACID-compliant transaction functionality. SQL Server locks are dynamically applied at various levels of granularity, in order to select the least restrictive lock required for the transaction.

InnoDB provides MySQL with a transaction-safe (ACID compliant) storage engine with commit, rollback, and crash recovery capabilities. InnoDB does locking on the row level and also provides an Oracle-style consistent non-locking read in SELECT statements. These features increase multi-user concurrency and performance. In SQL queries you can freely mix InnoDB type tables with other table types of MySQL, even within the same query.

With the new release of MySQL 5.0, it has now incorporated several features that were missing earlier, and thus some of the reasons that caused you to choose SQL Server 2000 are no longer valid, for example, the need of Views, Cursors and Procedures. One thing now lacking is the full support for triggers. Another thing in which MySQL lags behind is XML support, and with the release of Yukon, this is going to look like a big gap in functionality.

Feature

SQL Server 2000

MySQL

ACID

Yes

Yes

Referential Integrity

Yes

Yes

Transactions

Yes

Yes

Temporary Table

Yes

Yes

Views

Indexed views

Updateable views

Indexes

R-/R+ tree

?

MyISAM only

Hash

?

MEMORY only

Expression

?

No

Partial

?

No

Reverse

?

No

Bitmap

?

No

Cursor

Yes

Yes

Trigger

Yes

Rudimentary

Function

Yes

Yes

Procedure

Yes

Yes

External routine

Yes

Yes

Partitioning

Range

Yes

NDB only

Hash

No

No

Composite

No

No

List

No

No

XML support

Yes

No

Unicode

Yes

Yes

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. By contrast, SQL server fully supports security at the column level.

Another important consideration is security certificates - the verification of the database security by a third party. SQL Server has been certified as C-2 compliant, which means the database system has adequate security for government applications. MySQL has no such certification.

Moving on to more advanced features of protecting data on the database, the SQL Server 2005 have implemented more advanced authentication and authorization features. The database supports native encryption capabilities, obfuscating the DBA from writing user-defined functions using column encryption APIs. 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 DBA has also the choice of specifying his own user-defined security functions through the encryption facility implemented in the .NET Framework.

Another major concern to business today is security. While data management can seem to be a mundane process at times, securing critical data from "the outside world" is an ever increasing and trying task.

While is good to know that your database management system (MySQL or SQL Server) utilize security features it is very important to know that the application has been verified by a third party. SQL Server has been certified as C2 compliant, which ensures that the database has adequate security support for government applications.

Along with C2 certification, Microsoft Baseline Security Analyzer helps administrators ensure that their SQL Server installations are up to date with the most current patches and security features. MySQL has no equivalent tool to protect and ensure the same confidence in their platform.

While both MySQL and SQL Server support security measures within in their platforms, MySQL supports security via the SQL GRANT command. The MySQL GRANT command is limited to granting security at the table level. This means that if any portion of data in the table needs to be secure from any particular user, then the entire table has to be secured from that user. SQL Server supports security at the column level, meaning that any portion of data in a table can be secured from any particular user while allowing that same user to see other portions of the table data.

SQL Server also makes their database more secure by abstracting its data behind a layer of stored procedures. This ensures that developers never see how the actual data is represented. My SQL, as noted above, does not support stored procedures

Database Vulnerability

Security breaches are an increasing phenomenon. As more and more databases are made accessible via the Internet and web-based applications, their exposure to security threats will rise. The objective is to reduce susceptibility to these threats. Perhaps the most publicized database application vulnerability has been the SQL injection. SQL injections provide excellent examples for discussing security as they embody one of the most important database security issues, risks inherent to non-validated user input. SQL injections can happen when SQL statements are dynamically created using user input. The threat occurs when users enter malicious code that 'tricks' the database into executing unintended commands. The vulnerability occurs primarily because of the features of the SQL language that allow such things as embedding comments using double hyphens (- -), concatenating SQL statements separated by semicolons, and the ability to query metadata from database data dictionaries. The solution to stopping an SQL injection is input validation.

A common example depicts what might occur when a login process is employed on a web page

that validates a username and password against data retained in a relational database. The web

page provides input forms for user entry of text data. The user-supplied text is used to dynamically create a SQL statement to search the database for matching records. The intention is that valid username and password combinations would be authenticated and the user permitted access to the system. Invalid username and passwords would not be authenticated. However, if a disingenuous user enters malicious text, they could, in essence, gain access to data to which they have no privilege. For instance, the following string, ' OR 1=1 -- entered into the username gains access to the system without having to know either a valid username or password. This hack works because the application generates a dynamic query that is formed by concatenating fixed strings with the values entered by the user.

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.

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 file groups, except those marked as read-only.

MySQL falls short in recovery with its default MyISAM mechanism. The UPS assumes uninterrupted data, and in the event of an unexpected shutdown your data can be lost and the data store corrupted.

The following table shows a more complete comparison of the differences between MySQL 4.1 and SQL Server:

Feature

MySQL

SQL Server 2000

Notes

SQL VIEW support

SQL VIEWS let administrators abstract database designs away from developers.

Triggers

Lack of triggers makes MySQL developers add extra logic to their front end and middle tier when the logic should go into the database.

Stored Procedures

Stored procedures are mechanisms for abstraction and security

User Defined Functions

User Defined Functions (UDFs) allow encapsulation of complex code into simple callable interfaces.

CURSOR Support

Lack of CURSOR support in MySQL increases network traffic and lowers app response time.

SQL SELECT

MySQL supports using a regular expression as a filter clause in a query.

XML Support

XML is a standards-based format for data. MySQL has no native support for XML.

FULL JOIN

MySQL does not support FULL JOIN

Referential Integrity

MySQL 4.0 supports referential integrity (RI) so long as the InnoDB table type is chosen.

Transaction Support

MySQL´s default installation does not support transactions. Transaction support requires InnoDB.

Full Text Support

Import/Export Support

Replication Support

Auto Tuning

Database Management Tools

Query Analysis Tools

Job Scheduling

Profiling

Online backup support

Clustering Support

Log Shipping

Support for Storage Area Networks (SANs)

Hot Backups / Incremental backups

Basic Security

Security Certifications

Security Tools

Security through stored procedures & views

OLAP Services

Data Mining

Data Reporting

Concluding Thoughts

From a database developer's perspective, choosing between a MySQL and SQL Server DBMS is a matter of the scale of the database application. For enterprise-level applications, SQL Server wins hands down. It has advanced set of SQL features, superior replication, clustering, security and management tools.

For lower-tier database applications, MySQL can offer the core functionality you require at a very low cost. Some might argue that the latest offering from MySQL has made the open source database system enterprise is worth, but this remains to be seen. The advanced functionalities implemented are yet to stabilize and be rationalized across the database engine. What's more, Microsoft has upped the ante with even more advanced features of its own. It is up to MySQL to rise up to the challenge, but at this point in time MySQL is nowhere near the competitive enterprise field of the more established MS-SQL Server 2005.

It is not true that SQL Server 2000 is better than MySQL version 4.1 or vice versa. Both products can be used to build stable and efficient system and the stability and effectiveness of your applications and databases depend rather from the experience of the database developers and database administrator than from the database's provider. But SQL Server 2000 has some advantages in comparison with MySQL version 4.1 and vice versa.

The SQL Server advantages:

SQL Server holds the top TPC-C performance and price/performance results.

SQL Server is generally accepted as easier to install, use and manage.

Transact-SQL is more powerful language than MySQL dialect.

The MySQL advantages:

MySQL supports all known platforms, not only the Windows-based platforms.

MySQL requires less hardware resources.

You can use MySQL without any payment under the terms of the GNU General Public License.

I would like to conclude, by listing the feature that I have used and that I like the most on both database platforms:

Microsoft SQL Server 2005/2008

DMV

Database Mirroring

Database snapshot

Extended Events

Auditing

Transparent data Encryption

Change data capture (CDC)

Resource Governor

MySQL 5.x and above

Ability to run on multiple OS

Share Nothing Cluster

Easy to Scale Out on commodity hardware

MySQL Proxy

Replication

Multiple Storage Engines

Writing Services

Essay Writing
Service

Find out how the very best essay writing service can help you accomplish more and achieve higher marks today.

Assignment Writing Service

From complicated assignments to tricky tasks, our experts can tackle virtually any question thrown at them.

Dissertation Writing Service

A dissertation (also known as a thesis or research project) is probably the most important piece of work for any student! From full dissertations to individual chapters, we’re on hand to support you.

Coursework Writing Service

Our expert qualified writers can help you get your coursework right first time, every time.

Dissertation Proposal Service

The first step to completing a dissertation is to create a proposal that talks about what you wish to do. Our experts can design suitable methodologies - perfect to help you get started with a dissertation.

Report Writing
Service

Reports for any audience. Perfectly structured, professionally written, and tailored to suit your exact requirements.

Essay Skeleton Answer Service

If you’re just looking for some help to get started on an essay, our outline service provides you with a perfect essay plan.

Marking & Proofreading Service

Not sure if your work is hitting the mark? Struggling to get feedback from your lecturer? Our premium marking service was created just for you - get the feedback you deserve now.

Exam Revision
Service

Exams can be one of the most stressful experiences you’ll ever have! Revision is key, and we’re here to help. With custom created revision notes and exam answers, you’ll never feel underprepared again.