I've run through this four times successfully, so I'm calling these instructions final.=======================================================================================
- MySQL Community Server (v5.6): http://dev.mysql.com/downloads/mysql/
- MySQL Workbench (v6.2): http://dev.mysql.com/downloads/workbench/
- MS SQL Server 2008 R2 (Express Edition): http://www.microsoft.com/en-ca/download/details.aspx?id=30438
- MS SQL Server Management Studio: available from the download window on above URL.
Note: the Microsoft downloads are named SQLEXPR_x64_ENU.exe and SQLManagementStudio_x64_ENU.exe
Once everything is installed and configured, make sure that MS SQL Server and MySQL server are running (type services.msc at a command prompt).
Import CGWP data using SQL Server Management Studio.
- Connect to Server popup should provide sufficient default settings. Click 'Connect'.
- Object Explorer displays a list of objects, including 'Databases'. Right-click 'Databases' and choose 'Restore Database...'.
- To restore from the CGWP dump, Choose 'From device' as the 'Source for restore' and click the browse button to find the dump file. Select the binary dump file (you need to choose 'All Files(*)' in 'Files of type' before you can see the file). It will show up in the 'Select the backup to restore' list if the file is usable. Check the 'Restore' box beside the backup set you want to restore.
- In the 'Destination for restore' field, copy the name of the backup set from the 'Select the backup sets to restore' panel. A database with this name will be created as part of the restoration process. Click 'OK'. Note that you will get an error if you try to restore a backup set to a database with a non-matching name.
- You should get a message telling you that the restore was successful. You can quit the Management Studio now.
Migrate existing MS SQL database to MySQL using MySQL Workbench
NOTE: everything about the MySQL Workbench under Windows seems a bit sluggish. Be patient after clicking buttons.
- We need a connection to a running instance of MySQL. On the MySQL Workbench home page, click on the ⊕ beside 'MySQL Connections' at the top of the page to launch the 'Setup New Connection' wizard.
- Provide a 'Connection Name'. Click 'Configure Server Management...' button (bottom-left) to launch the 'Configure Local Management' wizard.
- On the first page of the 'Configure Local Management' wizard, click 'Next'. You'll be prompted for the MySQL root password. Provide it (select 'Save password in vault' or you'll have problems later). Click 'OK'.
- As long as a connection could be made, this page ('Testing the Database Connection') is merely informative. Click 'Next'.
- If there is only one instance of MySQL running, click 'Next' on the 'Set Windows configuration parameters for this machine' page.
- As long as a connection could be made, this page ('Testing Host Machine Settings') is merely informative. Click 'Next'.
- Review settings or click 'Continue'.
- Back in the 'Setup New Connection' wizard, click 'OK'.
- Choose the 'Database Migration' shortcut on the right.
- If you have already set up a system DSN for the MS SQL DB, click on 'Start Migration'. If you haven't, create one now.
- Click the 'Open ODBC Administrator' button.
- Choose 'System DSN' tab and click the 'Add...' button.
- Select a driver: SQL Server Native Client 10.0 (although both will work it appears that this is the MS-preferred driver)
- Configure the ODBC connection (see 'DSN setup' instructions below)
- Click the 'Start Migration' button.
- On the 'Source Selection' page, choose 'Microsoft SQL Server' as the Database System, and 'ODBC Data Source' as the Connection Method. You should see the DSN you created in the 'DSN' filed. Remove 'sa' from the 'Username' field if present. Click 'Next'.
- On the 'Target Selection' page, choose the 'Stored Connection' you made in step 1. Click 'Next'. The 'Fetch Schema List' page should complete on its own without configuration or interaction. Click 'Next'.
- On the 'Schema Selection' page, choose the database you want to migrate (it will have the name of the database you restored in MS SQL Server Management Studio). You can leave the 'Schema Name Mapping Method' alone. Click 'Next'.
- The 'Reverse Engineer Source' page should complete on its own without configuration or interaction. Click 'Next'.
- On the 'Source Objects' page, you will be prompted to select the objects you want to migrate. In this case we have: 'Migrate Table objects'; 'Migrate View objects'; and 'Migrate Routine objects'. I have been unsuccessful in migrating anything other than the tables, and, having looked through the views and stored procedures (aka Routine objects) I think it's safe to ignore them.
- The 'Migration' page should complete on its own without configuration or interaction. Click 'Next'.
- On the 'Manual Editing' page you can review any objects before the actual migration takes place. I consistently get 55 warnings and no errors. The warnings I've seen are collation issues (e.g. 'Collation SQL_Latin1_General_CP1_CI_AS migrated to utf8_general_ci') and NULL value problems (e.g. 'Default value N'NA' is not supported.' and 'Default value N'SUR' is not supported.'). As far as I can tell, they can safely be ignored.
- On the 'Target Creation Options' the only thing selected by default is 'Create schema in target RDBMS'. This is sufficient for our needs. Click 'Next'.
- If this is the first time through, the 'Create Schemas' page will run on its own. If it finds an existing MySQL schema you will need to allow MySQL Workbench to drop it.
- The 'Create Target Results' page allows you to edit the individual SQL solutions produced by MySQL Workbench to do the actual migration. Leave this alone. Click 'Next'.
- On the 'Data Transfer Setup' page 'Online copy of table data to target RDBMS' should be checked. You can also opt to create a batch file that will 'copy the data at another time'. Click 'Next'.
- Copying the data will take a few minutes, and a progress bar will be displayed while the copy is running. Anything else is likely the result of a config error.
DSN setup for SQL Server Native Client 10.0 Driver
- Name: CGWP
- Description: ODBC connection to CGWP backup
- Server: (local)\sqlexpress (or $MACHINENAME\sqlexpress)
- Click 'Next>' button
- How should SQL Server verify the authenticity of the login ID? With Windows NT authentication using the network login ID = checked
- Connect to SQL Server to obtain default settings for the additional configuration options = checked
- Click 'Next>' button
- Select the 'Change the default database to:' option to include the database in the DSN - it's helpful. The name of the database will show up in the dropdown list as long as a connection can be made. Other than this, leave the other settings alone.
- Click 'Next>' button
- You can leave the settings on this panel alone.
- Click 'Finish' button to review/test the config. Note that a successful config/test connection does not always mean that you won't have problems later in the migration process!
MySQL Workbench can also dump the data from the new MySQL database. Just open up the MySQL connection that you created at the beginning and select 'Data Export' from the navigator on the left. There's a lot of data, so be sure to select complete insert statements in the advanced export options.