Convert MySQL to an MS SQL Server 2008 Database

When you do a search for "Convert MySQL to MS SQL" in Google, most of the results are database conversion software, which of course is not free.

You might be lucky enough to find some ways to convert MySQL to an MS SQL Server database format using ODBC techniques in the .NET Framework/Data Source. However, if you do try it, you can easily fail, just because the connector is not compatible or there are a lot of errors thrown during the conversion process.

Troubleshooting those problems relating to ODBC connectivity can be quite complex for an ordinary web developer who needs to convert his MySQL database into a fully working MS SQL Server 2008 database to be used in ASP.NET 3.5 projects.

This tutorial will present a new method of converting/importing or migrating a MySQL database (along with its tables) to an MS SQL Server 2008 database environment.

This technique used will not rely on any shareware database conversion software or require you to install MySQL ODBC connectors.  

The Conversion Strategy

Suppose you have a MySQL database file (with .SQL extension). The conversion process to MS SQL database is as follows:

The first step is to import the MySQL database to an XAMPP phpMyAdmin. The purpose is to see the database tables and records, and confirm the correctness of the data to be converted.

The next step is to export the MySQL database (including all associated tables in it) as an Excel workbook.  Further formatting is required to ensure it is in MS Excel workbook format and tables are in the correct format.

The third step is to import the Excel workbook to a Microsoft SQL Server 2008 Import function; this will do the actual conversion to an MS SQL Server database (.mdf extension)

Once the data types and tables have been configured completely in the Microsoft SQL Server import function, it will then be converted into an MS SQL server database.

The final output database should also include the associated tables and records (the same as you had from MySQL).

The output .mdf file will be placed in the data folder of MS SQL Express in Windows program files; it can then be copied to App_Data of your ASP.NET 3.5 projects.

You can then try to output all the database content to the web browser to make sure the records and tables are intact and converted properly.

What are the required software/programs?

You need the following programs fully installed in your Windows computer system:

1. Windows version of XAMPP (Complete/Full installation with phpMyAdmin/MySQL database)

References:

http://www.apachefriends.org/en/xampp-windows.html

http://www.aspfree.com/c/a/BrainDump/XAMPP-Tips-for-Running-an-ApacheMySQL-Server-in-Windows-XP/

This is open source software (free).

2. MS Excel. This is not free, but since you are using Windows, you should also have this installed. Any version will do.

3. Microsoft SQL Server 2008 Express and Visual Web Developer. This is provided free by Microsoft and installed along with Visual Web Developer Express 2008. It is highly recommended that you install Visual Web Developer, which includes MS SQL server 2008 Express.

References:

http://www.aspfree.com/c/a/ASP.NET/Develop-Your-First-ASPNET-Website-with-Visual-Web-Developer-Express/

You can confirm that you have successfully installed Microsoft SQL Server 2008 Express by going to Start à All Programs and looking in the "Add or Remove Programs" section:

The most important feature of that program is the Import and Export Data (32-bit) (see screen shot above, encircled in red box). This will do the actual work of converting the file to an MS SQL Server 2008 database.

Sample Conversion from MySQL to MS SQL

Let's apply the process flow by presenting an actual example. Download this sample MySQL database, http://www.dotnetdevelopment.net/tutorials/ttest.zip , which consists of two MySQL tables.

Unzip it (using Winrar, right click on it and click "Extract here") and then proceed as follows:

Step 1: Log in to your XAMPP phpMyAdmin: http://localhost/phpmyadmin

Step 2: Click the "Import" link in the upper navigation menu.

Step 3: In "Location of the text file," click "Choose file" and then navigate to where you have extracted the ttest.sql file.

Step 4: Set everything to default, which is:

Character set of the file: utf8

Allow interruption of an import...: Checked

Number of records to skip from start: 0

Format of imported file: SQL

SQL Compatibility mode: NONE

Then click GO.

Step 5: You should be seeing the ttest mysql database being imported to your phpMyAdmin. Take a moment to look at the tables (ttest and ttestinverted). There are 999 records for the ttest table but only 20 for ttestinverted.

Step 6. You will need to export this database as an MS Excel Workbook. In the header navigation of phpMyAdmin, click the link "Server: localhost." This will take you to the front page of phpMyAdmin. Then click the "Export" link.

Step 7. Under "Export," select ttest as the database to be exported. Then click "Microsoft Excel 2000" for the type. Under "Options," check "Put field names in first row." Also check "Save as file" and finally, click "GO."

Step 8. The file will be exported as localhost.xls. Open the file in MS Excel. The formatting adjustment in MS Excel needs a more detailed discussion.

Format MS Excel File: Ready for MS SQL Server Import

When you open localhost.xls, it comprises only one sheet, which is localhost. You need to adjust the formatting and create another sheet for the second database table.

Step 1: Reduce the column width to reasonable sizes.

Step 2: Change the sheet name from localhost to ttest.

Step 3: In Excel, go to Insert à Worksheet. Rename the sheet from Sheet1 to ttestinverted.

Step 3: Scroll down to further see the second table's (ttestinverted's) records.

Step 4: Select the rows 1001 to 1021 (which contains the ttestinverted records). Right click à Cut.

Step 5: Go to the ttestinverted Excel worksheet. Paste the records starting at row 1.

Step 6: Remove any formatting (bold, colors, etc) if there are any.

Step 7: Go to File à Save As, and in the "File name," change the name to: ttest. In "Save as type:" choose Microsoft Excel Workbook. Then press the save button.

Your Excel workbook should be same as this one here: http://www.dotnetdevelopment.net/tutorials/ttest.xls

A good rule of thumb should cover the following points:

1. The name of the Excel workbook should also correspond to the name of the MySQL database.

2. The worksheet name should also correspond to the exact name of the MySQL database tables.

3. Table records should start at row 2 with the first row (row 1) used for database fields.

4. Utilize the leftmost column first (which is Column A) in filling up the data.

5. Remove any fancy formatting like bold fonts, colors, etc.

6. It should be saved as a Microsoft Excel workbook (procedure above in Step 7); this is very important.

Import Excel Workbook to Microsoft SQL Server 2008

In your Windows, go to Start à All Programs à Microsoft SQL Server 2008 à Import and Export Data (32-bit)

The database import wizard will then show. Click Next and follow the rest of the procedure:

Step 1. Under "Choose a Data Source," select "Microsoft Excel."

Step 2. Browse to where you have saved your Excel file in "browse."

Step 3. Under Excel Version: select the appropriate version (e.g. Microsoft Excel 97-2003).

Step 4. Check "First row has column names."

Step 5. Under "Choose a Destination:" select "SQL Server Native Client 10.0" for Destination.

Under Server name, leave it as it is. It should be:

YOURWINDOWSCOMPUTERNAMESQLEXPRESS

Under "authentication," check: Use Windows authentication.

Step 6. Under Database, click "New..."

Step 7. In the "Create Database" part, enter only the name of your MS SQL Server database (you can now choose any name), for example: ttestdatabaseexample

It should look like:

Step 8. Select "Copy data from one or more tables or views." Click Next.

Step 9. Under "Select Source Tables and Views"; check all the tables:

ttest$

ttestinverted$

Step 10. Highlight ttest$, and click "Edit Mappings." You need to reconfigure the type to be the same as the data type used in MySQL. See the screen shot below.

Step 11. Highlight ttestinverted$ and click "Edit Mappings" again.

Configuration settings for Step 10 and Step 11:

Step 12. Click Next, and under Review Data Type Mapping, click Next again.

Step 13. Under "Run Package," check "Run Immediately," and then click "Next."

Step 14. Finally you will see "Complete the Wizard." Click Finish.

Step 15. If the execution was successful, you should see a message like the following:

Get the output database now in MS SQL Server 2008 Format

The output database will be located in this path: C:Program FilesMicrosoft SQL ServerMSSQL10.SQLEXPRESSMSSQLDATA

The database name will be appended with _Data, so the final MS SQL server database name for ttest is ttestdatabaseexample_Data.mdf

The log file is also created, but not needed for your ASP.NET 3.5 projects. You can now copy the ttestdatabaseexample_Data.mdf database to App_Data in your ASP.NET projects.