Tech Issues
.NET
.NET FrameWork
ASP.NET FrameWork
Cross Platform
C Sharp
VB.NET
ADO.NET
Managed C++
Remoting
Smart Client
SOAP/XML
Threading
XML Web Services
Windows Services
.NET General
Active Directory
ADO.NET
Applications
ATL Server
BizTalk Server
COM Interop
Compact Framework
Databases
Deployment
Encryption
Exception Handling
Forth .NET
Fortran .NET
GDI+
General
How To
Instrumentation
Internet & Network
J# .NET
MFC/ATL for VC++ 7
MSIL
Office Development
Parsers
Patterns and Practices
Printing
Purgatory
Samples
Security
Unedited Stuff
Utilities
Visual Basic .NET
VS.NET Add-Ins
VS.NET Macros
XAML
ASP.NET
Application
ASP.NET Controls
Caching
Content Management
Custom Controls
Data & Database
Debugging
Design & Architecture
HTML Controls
Images & Multimedia
Reporting
Security
Server Management
Session & Session State
User Controls
Validation
Viewstate
Web Forms
XML/XSL
MS SQL Server
Backup & Recovery
Constraints
Data Types
Design Issues
DTS (Data Transformation)
Full Text Catalog
Functions
Indexing
Job Scheduling
Maintenance
Meta Data Services
Processes
Profiler
Query Analyzer
Replication
Security
Stored Procedures
Support Services
System Databases
Tables
Triggers
T-SQL
Tuning
Views
C#
Algorithms
Applications
Attributes
Beginners
COM / COM+
COM Interop
Computational Geometry
Controls
Data Structures
Database
DataSets, DataGrids etc
Debug and Tracing
Delegates and Events
Design and Architecture
Distributed Apps
Files and Directories
Fuzzy Logic
Games
GDI+
General
Generics
Graphics
How To
Internet
Libraries
Longhorn
Memory Management
Multimedia
Network
Office Programming
Parsers
PInvoke
Printing
Purgatory
Registry
Regular Expressions
Remoting
Samples
Security
Shell Programming
System
Threading
Threads
Unedited Reader Contributions
Utilities
VS.NET Addins
Web Services
Windows Forms
XML/XSLT
Tech >> DOTNET >> Migration

Migrating an ASP.NET application from MS SQL Server To MySQL

By Sudhir Dwivedi

Introduction

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:

  1. MySQL setup/ DB creation and data migration.
  2. DAL layer changes.
  3. Integration & Testing

Yes I can accept It's simple job but only if you are kidding ;)

Ok what I did:-


Database's Objects Migration

  1. 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.
  2. I created a database for the project.
  3. 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.
  4. 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.
  5. I got generated sql script from MS SQL and convert the whole script in MySQL specific syntaxes ;) and I ran those scripts on MySQL.
?..And my life was 20% happy ;)

DAL Migration:

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.
  1. I got MySql.Data.MySqlClient (Connector/Net 5.2) for my need from MySQL website: http://dev.mysql.com/downloads/connector/net/5.2.html.
  2. 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.
  3. I read user manual from here : http://dev.mysql.com/doc/refman/5.1/en/connector-net-ref-mysqlclient.html.
  4. 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.
  5. 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 :-).
What I learnt from this exercise:
  • 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.
  • MySQL 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.
Well, this project was not very complex, I wrapped in 2 days. Cool start in MySQL : )

I got thanks mail from the manager finally...; )

Sudhir