During last week I was given a short term assignment: Migration of an ASP.NET 3.5
based application from MS SQL Server To MySQL. In fact it was very urgent requirement
and one of my managers was little pushed by the client.
I was wrapping up the Reporting Services project, It was monday evening when he called and shared his worries. Anyways that's too specific and I know you are not interested to listen. Ok so what I had to do:
- MySQL setup/ DB creation and data migration.
- DAL layer changes.
- Integration & Testing
Yes I can accept It's simple job but only if you are kidding ;)
Ok what I did:-
Database's Objects Migration
- I installed MySQL on my development box. Please remember McAfee is not a friend
of yours to install and configure MySQL DB. I disabled McAfee's access protection
during the setup. But It is not always a case and otherwise it's not so tough.
- I created a database for the project.
- I downloaded MySQL Migration Toolkit(mysql-gui-tools-5.0-r17-win32.msi) from http://dev.mysql.com/downloads/gui-tools/5.0.html
to export objects from MS SQL to MySQL. and I tried the export. But It didn't help
enough to achieve the goals. It copied few tables and shown errors for others. I
tried to fix with heuristics, I'll point out later.
- I also searched on net for migration tools. I got certain tools but not free of
cost. And I wasn't relying on automations and other thing was cost factor, so I
decided to apply some tricks.
- I got generated sql script from MS SQL and convert the whole script in MySQL specific
syntaxes ;) and I ran those scripts on MySQL.
Now I had to change DAL layer to set for MySQL. It was priorily using System.Data.SqlClient in DAL. So I was told to change and optimize it for MySQL. I did following things to achieve the goal.
- I got MySql.Data.MySqlClient (Connector/Net 5.2) for my need from MySQL website:
- The existing solution's DAL was having a sort of SQL Helper class based on System.Data.SqlClient.
Here I replaced this by MySql.Data.MySqlClient.
- I read user manual from here : http://dev.mysql.com/doc/refman/5.1/en/connector-net-ref-mysqlclient.html.
- I found a clue and changed all SqlClient based objects in SQLHelper class just prefixing
"My" and indeed after a succeessful build of
Data Access Layer, my life seemed cool
and I was all set to test the application.
- But grrrrrrrr!! My SPs were not working... errorrrrrrrs. I re-analyzed my lacuna.
and again some interesting changes I had to make in order to fix the problem for
example I had to replace the sign '@' by '?' while calling a stored procedure from
DAL.. and finally I could run the application smoothly :-).
- Don't expect MySQL to have all the same features as MS SQL: If You are using xp_cmdshell
in your MSSQL Stored procedures, do not expect MySQL is also able to do the same.
- Table Variables are not available in MySQL.
- Stored procedure call from DAL in MySQL is prefixed by a question mark i.e. '?'
not by a
'@' as compared to MS SQL Server.
- Couple of data types such as MONEY, IMAGE are not supported in MySQL.
- You cannot make an identity(autonumber/autoincrement) field on MySQL table without
making it a primary key or certain other column as a primary key.
does not support Full Joins.
- Error Handling in MySQL is different from MSSQL and If you are using Try/Catch in your T-SQL code supported by CLR, then you are going to face a really hard time. Find alternatives in terms of output parameters etc.
- Last but not the least, Please learn basics of MySQL programming before going to take responsibility. Varible declarations, control /looping structures, string handling, system functions are pretty different.
I got thanks mail from the manager finally...; )