473,323 Members | 1,537 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes and contribute your articles to a community of 473,323 developers and data experts.

MS SQL Server 2005 Vs MySQL 5.x

Coldfire
289 100+
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 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.
- 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.
Jun 29 '07 #1
0 12847

Sign in to post your reply or Sign up for a free account.

Similar topics

6
by: dstewart | last post by:
I have 2 Suse 9.1 boxes with similar configurations. I'm in the process of moving some PHP code from one server (192.168.0.100) to another (192.168.0.102). MySQL is running on each server, and...
4
by: MLH | last post by:
A programmer developed an AMP (Apache/MySQL/PHP) application for me. When he was done, he sent me the PHP files and the MySQL dump file. Now, when I connect to the application on my LAN using...
16
by: MLH | last post by:
Using MS Access, I have attached to MySQL servers in other states and other countries on the other side of my router. But when I use the MySQL ODBC driver 3.51 to connect to a MySQL server on my...
8
by: JJ | last post by:
My potential shared hosting platform uses SQL server 2005. I am developing on the Express version. In order to make sure I am not going to cause myself any problems when I move my ASP .net 2.0...
5
by: Ted | last post by:
I am working on two versions of an application, one of which will be a windows forms application (which will need to be redistributable) and the other will be a web application. I have MS Visual...
1
by: Ted | last post by:
In MS SQL I used the following to create a stored procedure. USE AdventureWorks; GO IF OBJECT_ID ( 'HumanResources.usp_My_Search', 'P' ) IS NOT NULL DROP PROCEDURE HumanResources.usp_My_Search;...
1
by: info | last post by:
How import database mysql to mysql server 2005? -- magix
4
by: Ross | last post by:
Hello, I am trying to Read and Write to a text file on a web server using Microsoft Visual Basic 2005 Express Edition. So far I have managed to complete my testing with a local text file using...
6
by: Cirene | last post by:
From my experience/undertanding SQL Server (and SQL Express) is the preferred db to use with Visual Studio and is very nicely integrated with it. It works well with the built in membership, etc......
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
by: Shζllξpτpο 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.