MySQL to MS SQL Tutorial

[Product's Homepage]  [FAQ]  [Download]  [Buy]

1. Connect to MySQL Server

If you selected "Move to MySQL server directly" option on the first page, you will see this screen where you should enter the necessary settings to establish connection with MySQL server. MySQL-to-MSSQL is able to connect to MySQL server using either local or remote mode. Local mode is being used to connect MySQL server launched on the same machine where you run MySQL-to-MSSQL from. Remote mode allows you to connect to MySQL server launched on another computer.

By default the program uses local connection mode. It is indicated by radio button "Local" selected. To establish remote connection you should select "Remote" radio button. For remote MySQL servers you should also enter host and TCP/IP port to connect through. Host could be either network name or IP address. TCP/IP port value is necessary only if it differs from the default MySQL port number 3306. Otherwise you may leave this field as is.

Finally, you are entering user name and password. You can leave these fields empty for anonymous connection.

    


You should have the sufficient privileges to create new database on MySQL server. Otherwise you will be able only to import MS SQL database contents into an existing MySQL database. See Virtual Server User Notes for related information.

NOTE: MySQL 4.1.0 uses the authentication protocol based on a password hashing algorithm that is incompatible with that used by MySQL-to-MSSQL. See MySQL-to-MSSQL FAQ page to learn how to resolve this problem.

2. Connect to MS SQL Server

On this wizard screen you should enter the necessary settings to establish connection with Microsoft SQL Server. In "MS SQL Server" field you should specify the name of an instance of MS SQL Server to which you want to connect. If you leave it empty, the local server is assumed. Also, you can specify custom MS SQL port (other than 1433) in "MS SQL Server" field as follows:
 
Sql_server_name,1234
 
where 1234 is the custom port. Then you should specify the type of authentication to use when connecting to MS SQL Server:

  • Windows authentication - SQL Server will validate the connection using the Windows NT user information. This option is only available when connecting to an instance of MS SQL Server on Windows NT. The client should be member of a Windows NT/2000 domain or workgroup. The user needs to be validated as a Windows NT user before access is granted.
  • SQL Server authentication - SQL Server will use the standard security validation. To log in you should specify user name and password recognized on the target MS SQL Server.

In "SQL Server authentication" mode MySQL-to-MSSQL shows database tables owned by the specified user only. You can change it using "Connect as database owner" checkbox. It will force the program to display all tables from "dbo" tablespace, but only if the specified user has enough privileges to read them. Otherwise, the option will not take effect.

    
3. Select Databases

On this wizard screen you should specify MySQL and Microsoft SQL databases. If you specify the name of existing MS SQL database it will be overwritten.

You can convert only table definitions of the original MySQL database. Select "Convert table definitions only" checkbox at the bottom of the wizard screen to use this feature. If you don't want to convert indexes select "Skip converting indexes" checkbox. Sometime database administrators arrange text values to be stored in BLOB columns. To extract those values as text into Microsoft SQL database, select "Convert BLOB to text" checkbox.

    
4. Select Tables

On this wizard screen you can manage the list of MySQL tables to convert. In the left part of the screen you can see "Available tables" list box. It contains all available tables of the source database except those that you have selected already. On the right you can see "Selected tables" list box. To add new MySQL table for converting into Microsoft SQL format you should select it in "Available tables" list box and click "Add" button (or double-click the selected item). Also you can use "Add all" button to insert all available MySQL tables. To remove an item from "Selected tables" list you should select it and click "Remove" button. You can use "Remove all" button to remove all items from "Selected tables" list box.

Push "Add Query" button to add SELECT-query filtering data that will be migrated to Microsoft SQL server. See Using Queries article for the further information.

To edit selected table or query double click the corresponding item in "Selected tables" list box. If it is a table, dialog box appears to edit table attributes and to define custom type mapping. See this article for the further information. If selected item is a query, you will see dialog box to edit SQL-statement of the query.

NOTE: if you see complex table names including schema (like [dbo].[table1]) while you need it in simple form, return on the previous screen and select "View tables owned by the user" checkbox.

    
6. Using Queries

Queries give you the ability to extract partial data for migrating to Microsoft SQL server. The following examples are provided to illustrate how to use queries for particular purposes. Assume that we have table "Table1" as below:

   Table1(
	ID INT NOT NULL, 
	FName VARCHAR(50),
	LName VARCHAR(50), 
	Birthday DATE, 
	Notes TEXT
   );

Example 1. Convert certain records.

    SELECT * FROM Table1 WHERE ID > 1000

Example 2. Choose and rename columns.

    SELECT FName as FirstName, LName as LastName FROM Table1

Example 3. Skip records containing NULL values.

    SELECT * FROM Table1 WHERE not isnull(Notes)