I've been trying to specialize into the MS SQL server technologies, but my job has required me to understand more MySQL recently. Has any MS SQL DBA or developer had to do this, and if so, what were their biggest challenges? At the moment, for me:
As an IDE for designing MySQL DBs I'd suggest MySQL Workbench.
I think one of the biggest problems migrating from MSSql to MySQL is saying good bye to transactions and hello to autocommit.
Here's a little comparison between MySQL 5.0 and MSSql 2005 (a little outdated...)
and here's what MySQl says...
The biggest difference between the two technologies in my opinion is that the database engines that are under the hood are quite fundamentally different.
This has implications, particularly for enterprise class systems, as the performance tuning techniques and best practice design principals that you use to optimise and design a SQL Server platform will not necessarily translate to MySQL.
If you already have a solid understand of the SQL Server database engine then I would look to develop this level of understanding with MySQL, starting off by looking at the different database drivers that can be used to drive the MySQL Engine such as INODB and MyIsam etc.
The fundamentals of relational database management of course remain the same, i.e. you are working with tables, indexes and keys etc. so you are not starting from the absolute beginning here. If you are familiar with using T-SQL to navigate your database structures and tables then porting this to a MySQL command prompt is quite a smooth transition.
You may find GUI tools such as Navicat to be to your liking if you frequently use SQL Server Management Studio (SSMS) to administer your databases.
To my knowledge, there is no integrated ETL platform such as SSIS to compliment MySQL, as it focuses solely on the core database engine. Microsoft SQL Server is considered an Enterprise database suite and so incorporates additional components that complement the database engine, such as Reporting Services, Analysis Services, SSIS and Notification Services etc.
I hope this helps you on your quest to work with a new technology. Learning something new is a great problem to have.
Cheers, John
The last I checked there were no UNIQUEIDENTIFIER data type in MySql. That could become a hard part in migration since lots of applications are written based on vast use of Guids.
I'm not following of the development of MySql, could be that this datatype is already there.