It's no small feat to manually migrate an enterprise from Microsoft SQL Server to MySQL, but it can be done. In part 3 of our series on migrating to MySQL, Mike Sheffey, CEO of migration tools vendor Versora, gives step-by-step instructions for two manual tasks: moving the data structure and data itself.
In a manual migration, what steps are needed to move the data structure?
Mike Sheffey: A manual migration from Microsoft SQL Server to a MySQL database running on Linux is an extremely tedious undertaking. Each step can require numerous man hours to complete. In addition, the possibility of error is high. Tread carefully.
First, you'll need to export the table structure using the SQL Server:
- At the SQL server, Launch Enterprise Manager and connect to the database you intend to migrate.
- Select all tables that are being migrated, right-click, and choose Generate SQL Scripts.
- In the dialog that appears, switch to the 'Formatting' tab. Uncheck Generate the DROP <object > Command for Each Object box. Check Generate Scripts for All Dependent Objects.
- To make things more manageable, you'll probably want to choose Create One File Per Object. Click OK and indicate where to save script files. This procedure will create a data structure that works only with SQL Server.
Are there any tricks to preparing the new database?
Sheffey: For the new database, tweaking will be required. Consider removing the brackets around names and types. Change types to corollary types. Remove the permissions and index statements from the end of each of these files and store them in a temporary "holding" file which will be applied after data is applied (for speed reasons). If statements are not revised as indicated, migrating the data will be significantly slower.
When completed, copy these files to the new machine (via file sharing, by burning a CD, or any other way you wish) and apply them to the new database. Each database has its own way of running SQL script files, though most will allow you to execute scripts via command line redirection.
The total time required for moving the data structure manually varies depending on which database is being migrated to, how complicated existing tables are, and the number of tables. Though this is not a difficult phase, it can be quite tedious.
In a manual migration, what's the best way to move data?
Sheffey: On the machine that runs MS SQL Server, install an ODBC driver for the new database and set up a connection to the new database. Then, do these tasks:
- Create a DSN in order to export data.
- Right-click on the database and choose All Tasks->Export Data.
- Step through the wizard and on the destination page, choose ODBC Data Source as the connection; then choose the DSN that you created.
- Choose Copy table(s) and view(s) from the source database.
- Select all the tables (but not the views) and select Next. [This is] important, as there will most likely be some data differences that will need to be fixed. Be sure to save the DTS package so you can run it again.
- Run the export and wait until it finishes. Note: When you see that part of the migration is the creation of tables, you may wonder why you were instructed to complete this step above. The reason is that DTS table creation often fails to create tables on non-Microsoft SQL Server databases.
After that, correct any copy errors. Some of the tables may fail to copy for various reasons such as binary data types being of different sizes. For each of these errors, you will need to:
- Go back into the DTS definition to the page where you chose the list of tables to migrate and click on the Transform button for that table.
- Under the Transformations tab, choose Transform information as it is copied to the destination
- In the text box, enter any necessary scripting code to modify any data that is not compatible with the new database.