MySQL to SQL Server 2005

Question

How can I convert a database from MySQL to MS SQL Server 2005?

Answer 1

Here is a application that will do the conversion for you:

http://www.spectralcore.com/fullconvert/tutorials/convert-mysql-to-mssql-sql-server.php

This white paper by Microsoft may also help too:

http://technet.microsoft.com/en-us/library/cc966396.aspx

Answer 2

You can use SSIS to copy over the table data to the new structure, but that is the easy part. Next you need to check all your sql code to make sure it will still work. This link can help see the differences between how each of the databases implements SQL http://troels.arvin.dk/db/rdbms/

While you are converting, you might consider if now might not be the best time to do some refactoring as well.

The key piece of doing a conversion though is to make sure that everything is automated and reproducable. You are going to want to do this several times in dev before moving to prod data. And when you go to prod, you will need to take the database down for maintanance or you will end up having data added to the old database after you have moved the data from that table to the new one. You might even want to build the process to copy over the bulk of the rcords before the maintenance window and then during the maintenace window only move the new records or records which hae changed since the main move. This will depend on on how big your database is and how long you will have to be down to move the records. If it can be done in one step without being down for longer than your system can tolerate, it is better to do that. Another choice for a large database might be a client by client data movement, so that instead of being down for everyone for a full day, you are only down for a couple of hours per client. Again this depends on your database design and how possible that might be to set up and do.

Whatever you do, make sure the users are fully aware of what you are doing and when in advance so they can plan. Also avoid times of the month for the change that would coincide with a need for the database to be up and running - I'm thinking in terms of don't close the payroll database the day that payroll runs or the finanical database when end of the fiscal year tasks need to be done or monthly reports run, etc. I don't know if you have any of those issues, but is is good to consider if you do and work around those periods. If the users say, "No we can't do that on Friday", then find out why - they may have a really good reason why the day you choose to implement is bad for their own work schedules.