Microsoft SQL Server does have features that can't be found in MySQL 4, according to Mike Sheffey, CEO of migration tools vendor Versora. But that situation is probably temporary. In part two of our series on migrating to MySQL, Sheffey discusses what users need to know about those differences.
What does MySQL have to offer that MS SQL Server doesn't?
Mike Sheffey: MySQL offers multiple storage engines. One database can accommodate different storage engines. SQL Server does not offer this flexibility as it offers only a single storage engine.
With MySQL, for instance, you may choose the NODB storage engine which will provide a standard transaction based database with support for foreign keys and other features that enable a robust application. You may also choose the MyISAM engine which can turn off transactions [creating] a substantial speed increase for simpler databases or data warehouses.
Another example is the memory storage engine which allows you to always ensure certain tables are in memory for rapid access. Additional specialized engines are constantly in development.
It is also important to note that MySQL is cross-platform and available for use on Windows, Linux, Mac OS 10 and various Unix OSes, and can be compiled to have an extremely low footprint.
What does MS SQL Server offer that MySQL does not?
Sheffey: Stored procedures and triggers. Being able to store your business logic inside the database is quite useful and is relied on by many corporations. Note that MySQL 5.0 will support these features.
It is convenient that SQL Server is well integrated in the Windows OS in that you may assign permissions to Active Directory users which are automatically updated when you add new users in active directory. Of course, integration does lead to more security issues.
The graphical tools for SQL Server are more robust and have been around longer while the [user interface] for MySQL is fairly new. However, the [user interface] is approaching SQL Server functionality at a fast rate.
When should a company absolutely not get off of MS SQL Server?
Sheffey: If the company or organization needs stored procedures or triggers, they should not migrate to MySQL until version 5 is released. If you have a third-party proprietary application directly tied into SQL Server, the application may be unable to migrate, and if the application is mission critical, it may not be appropriate to move to MySQL. Contact the vendor to find out when it will support MySQL.
When doing a manual migration, one has to move stored procedures, triggers and views. How will the inclusion of stored procedures, triggers and views in MySQL 5 simplify this process?
Sheffey: The inclusion of stored procedures, triggers and views in MySQL 5 [will] allow for a more seamless migration from MS SQL Server. Performing a manual migration of the aforementioned features requires an extensive amount of database re-programming. Before MySQL 5, a migration from MS SQL Server to MySQL was difficult when these advanced features were implemented in MS SQL.