MySQL to SQL Server Data Migration

Overview

In this chapter we will discuss how to migrate your data from MySQL to SQL Server.

Explanation

For the mgiration we will use the SQL Server Import and Export Wizard.  Follow the steps below to migrate your data from a MySQL database to a SQL Server database.

• Download and install the MySQL ODBC Connector. The latest release is available at  http://dev.mysql.com/downloads/connector/odbc/
• Open your ODBC Data Source Administrator from the Control Panel -> Administrative Tools. On the System DSN tab click ‘Add’, browse the ‘MySQL ODBC driver’ and click ‘Finish’.
   
  
  
  
• In the next dialog enter your MySQL server connection details, test it and click OK.
• Set SQL_MODE to ANSI_QUOTES on MySQL Server.
• Launch the SQL Server Management Studio
• Create a new database or use an existing database
• Right click on the target database and choose Tasks then click Import Data to launch the SQL Server Import and Export Wizard.
  • Select the .NET Data Provider for ODBC as Data Source and specify the DSN we created previously.
     
    
    
    
  • Select SQL Server Native Client 10 as Destination, enter the details of your SQL Server database and click Next.
  • Select Copy data from one or more tables or views and click Next.
  • Select the source and destination tables. You can enable identity insert using the Edit Mappings option.
  • Finally execute the package and save it for later use if necessary.