These 2 database servers are totally different under the skin of holding data in a relational form.

MySQL is published under the GPL (GNU General Public License, and there are 2 versions of this database software: (Note 1). MySQL is aimed at the target market of Internet servers and open source software. It's goal is speed, speed and more speed, options are added when they can put them in without impeding the database's speed advantage. The added advantage of bazillions of user-written projects available on the Internet is another reason to use MySQL.

1. The free Community version. There are forums provided and a bugfix database available free for the Community version, but that's all you will or even CAN get.

2. You can buy the Enterprise edition with extra bells and whistles detailed at http://www.mysql.com/products/which-edition.html#difference, and purchase varying levels of support for this version. We run 2 at the moment, both of them 64-bit MySQL 5.1.40sp1-enterprise-gpl-pro-log.

3. MySQL Downloads Available:

1. The free Community version is at: http://www.mysql.com/downloads/mysql/

2. You can buy and/or trial download the Enterprise edition here at: http://www.mysql.com/downloads/enterprise/

One can get these admin downloads to help with MySQL here. I find myself moving back and forth between these 3 products. (Guess one really should learn one and stick with it!)

1. The recommended free MySQL Workbench. This is a graphical data modeling, code testing, and server management interface at: http://www.mysql.com/downloads/workbench/

2. HeidiSQL has a very nice interface and a decent user manager at: http://www.heidisql.com/download.php

3. The archived End Of Life versions of MySQL's GUI Tools. (MySQL Administrator, Query Browser, and Migration Toolkit) at: http://dev.mysql.com/downloads/gui-tools/5.0.html

3. Two of the main depositories of freeware or open source software that front-ends MySQL: SourceForge.net is at: http://sourceforge.net/ Freshmeat.net is at: http://freshmeat.net/.

Microsoft SQL Server is aimed at corporate and enterprise markets, with the pluses of functionality, feature completness, and inclusion of things they think you may need like graphical user, administration and data modeling interfaces. Fine tuning of user permissions are already included in the product. Microsoft SQL Server is proprietary closed-source software, and you are at the mercy of a company (and so's MySQL for most of us!). You however gain in compatibility with Microsoft Windows and Office, you have some better tools, more wizards, and MS-Access just plain works better with it, esp. with date fields. Part of this is the "Apple Principle", where you control the OS and the applications so everything works together with a smoothness not possible with MySQL.

There are several versions of Microsoft SQL Server (or as most people say: SQL Server). For SQL Server 2005, this list is at: http://www.microsoft.com/Sqlserver/2005/en/us/compare-features.aspx

Note: SQL Server 2005 Service Pack 3 is what we are running currently at VCU. SQL Server 2008 will be available in a few months.

1. Enterprise Edition, Meets the high demands of enterprise online transaction processing and data warehousing applications, > $20,000. A trial 180-day download is at: http://www.microsoft.com/sqlserver/2005/en/us/trial-software.aspx

2. Standard Edition, Data management and analysis platform for small and medium-sized organizations, > $5,000.

3. Workgroup Edition, Designed for small organizations that need a database with no limits on size or number of users, > $3,000

4. Developer Edition, May be installed and used by one user to design, develop, test and demonstrate your programs on as many systems as needed, and includes all the functionality of Enterprise Edition, $49.

5. Express Edition, An easy-to-use, lightweight, embeddable, and redistributable version of SQL Server 2005 at: http://www.microsoft.com/downloads/details.aspx?familyid=220549b5-0b07-4448-8848-dcc397514b41&displaylang=en

6. Compact Edition, a free, compact, embedded database for single-user client applications for all Windows platforms, Compact Edition is at: http://www.microsoft.com/sqlserver/2005/en/us/compact-downloads.aspx

7. Several editions of SQL Server 2005 for trial downloads for single-user client applications for all Windows platforms are at: http://www.microsoft.com/sqlserver/2005/en/us/trial-software.aspx

User Security Models:

User security models are totally different between the two database systems. One way to look at it is Microsoft SQL Server's security is based on controlling what you can get to, MySQL's is based on controlling who gets in.

1. Microsoft SQL Server works with sql server logins, trusted logins, and/or Windows domains.

2. MySQL totally assumes Internet access, and has username@ipaddress formatting, enabling one to lock down a user to one ip address, or lock out all but one subnet from access at all. Both dbms systems can lock down users by login, database, table, column, and view.

MySQL cannot for example put security on one particular stored procedure, but can put security on who can get to any stored procedure, and can lock the data by securing the underlying data.

Microsoft SQL Server can fine-tune on who can run which stored procedure, as well as lock the underlying data against access.

Differences between the two:

Database Engines:

Microsoft SQL Server utilizes the SYBASE storage engine. This is a full-featured engine that is slower than MySQL's default MyISAM, but is ACID compliant for reliability.

MySQL can use at least 10 different storage engines, all with their advantages and disadvantages. The usual default engine is the MyISAM engine for speed. The more durable (but slower) engine that is ACID-compliant is the InnoDB engine. A detailed explanation of these engines is at: http://dev.mysql.com/doc/refman/5.1/en/storage-engines.html Of particular interest is the BLACKHOLE engine, one puts data into it and it disappears. This is useful for replicating data to a slave server via the transaction log, w/o taking space up on the primary server. (I guess sorta like using a wormhole.)

Backups and Recovery:

Both systems can do log-based recovery and backups.

1. Microsoft SQL Server has backup and recovery systems builtin. Here at VCU we use IBM's Tivoli to backup databases w/o using backup files.

2. MySQL can dump to flat SQL files at pre-determined intervals, it is up to you to put them somewhere that is safe. There is no Tivoli client for MySQL, one has to backup the flat SQL files. The format of these can be made ANSI by a MySQL setting for reading into Microsoft SQL Server.

Replication:

Both systems can do log-based replication.

1. Microsoft SQL Server has several replication schemes, snapshot, transactional and merge.

2. MySQL replication is based on the transaction log only.

Report Generation:

1. Microsoft SQL Server has TONS of report generating capacities, plus Microsoft Access can natively access SQL Server databases.

2. MySQL is very sparse in this.

Conclusion:

If you have a package already one wishes to use, such as REDCap, MySQL is very likely to have already been integrated into the package, so in a funny way, you're locked into open source!

VCU's main administration differences between the two systems:

We have much more on Microsoft SQL Server servers, we have better backups, replication is done on as-needed by basis of whether VCU will drop dead or not. MySQL is available for those who have packages that either require it, or just want a simpler database system, or have a bad case of Bill fever.

References:

Note 1: The GPL is at: http://www.gnu.org/licenses/gpl.html)

Here is MySQL's reason to leave Microsoft SQL Server, At: http://dev.mysql.com/tech-resources/articles/move_from_microsoft_SQL_Server.html

Here is a tutorial about MySQL's migrating from Microsoft SQL Server to MySQL. At: http://dev.mysql.com/tech-resources/articles/migrating-from-microsoft.html

Here is Microsoft's reason to leave MySQL to Microsoft SQL Server. At: http://dev.mysql.com/tech-resources/articles/move_from_microsoft_SQL_Server.html

Here is a tutorial about Microsoft's migrating from MySQL to Microsoft SQL Server 2000. At: http://technet.microsoft.com/en-us/library/cc966396.aspx

Here is a tutorial about using MySQL in the Microsoft .NET Environment. At: http://dev.mysql.com/tech-resources/articles/dotnet/

SQL Data Type Quick Reference

The "same, yet different" nature of SQL data types is of vital importance for any developer working with multiple database products, or those valiantly attempting to write SQL that lives in the application layer, independent of specific database platform choices. While by no means complete, the following table outlines some of the common names of data types between the various database platforms: