How long does it take to translate SQL Server queries to MySQL?

Question

I have been tasked with estimating the amount of time it will take to rewrite the data access layer of a .NET application from using SQL Server to use MySQL. It is not up for discussion whether this will happen or not...just how long will it take.

I have estimated all of the work except for translating the stored procedures in SQL Server to MySQL queries. The data model will be essentially the same (either by the tables being the same or by queries intimating the original schema) but in MySQL. I have about a 100 stored procedures, most of them data reads and there will be decent unit tests wrapped around the DAL so we can see when things work/break.

So the question is how much work (in broad terms) is it to translate between the two dialects of SQL? In general terms does it tend to be trivial work and mostly automated with find and replace? Or is it something where a large number of typical queries will need hand tweaking? I asking for a little general guidance on the scale of the task.

Thanks for any assistance.

Answer 1

In a worst case scenario you'd probably have to go through them all line by line. You have to be that thorough, but mostly I guess it's just reading it to make sure it's mysql compatible. Occasionally maybe alter things (mostly in queries).

It's a difficult question, because it depends on so much. How complex are the procedures? They can be 100% MySQL compatible, or they can be 100% INcompatible. You just have to make your own assessment, really.

Shouldn't take more than a day if you're comfortable with MySQL though.

Answer 2

There is no correct answer. It depends. If you have a SQL rockstar for MySQL and MSSQL, he can probably port all 100 stored procedures in a day or two, another programmer might take 2 to 3 weeks.

SQL is suppose to be standard in the ideal world, but we don't live in the ideal world.

http://www.joelonsoftware.com/articles/fog0000000245.html

Answer 3

It's going to depend, but likely you'll have a fair amount of line-by-line crawling through. If the SP's are just simple selects then this may be fairly quick, but even some things that you think will be straightforward will need altering by hand. E.g. select top 20 [column] from [table] in MSSQL becomes select [column] from [table] limit 0,20 in MySQL. This may even mean changes in the higher level code where tasks such as pagination are handled - you can't assume that you'll only need to change the SQL to get things working.

If there are more complex stored procedures, or anything that's using T-SQL functions, then you could be in for a long and generally unsatisfying haul.

Answer 4

I don't think there is a correct answer to this.

How much stuff is using things that are mssql specific, is any of it compatible ? not sure. If they are simple selects you shouldn't have to much of an issue but if ou are doing more than that you're going to need to do a line-by-line analysis to be honest,

How long was the original development effort ? or was it done over a period of many years ?

Personally I'd resist any temptation to use cut and paste at all, I'd assess what you have and then write it all from scratch in mysql, Hopefully your database is fully documented (grins) if not then I don't envy this task.

Is this a system that is currently live or is it something that is in development ? you don't say.. don't forget stuff like collations and unicode data, its not just a case of bringing over sprocs if its a live database with stuff in anymore.

The next thing to question is, is the current structure actually something you want to move forward with or can you move to hibernate, then there are performance questions.

The answer to your question directly is it depends on the person and complexity of what you are talking about.. but it is definately a task to take great care over. Especially if this is a live system

Answer 5

I recently had to move a Oracle DB to MSSQL Server. It took me about 3 days to read through the source and adjust things if necessary. There were about the same amount of queries as in your project, but these queries almost were trivial ones (select, insert, update, delete). A stored procedure normally has more than one single query inside, so a week of work MAY be realistic.