Export (0) Print
Expand All

Migrating MySQL to Microsoft SQL Server 2000

Updated : July 3, 2001

Abstract

This white paper illustrates the migration capabilities of Microsoft SQL Server 2000 and contains specific information to assist developers in the migration of a MySQL database to SQL Server 2000.

On This Page

Introduction
Target Audience
Overview
The Migration Process
Preparing for Migration
Data Types, Reserved Words, and Operators
MySQL Tools Used For Data Migration
SQL Server Tools for Migration
Features of Data Transformation Services
Data Transformation Services Glossary
Direct Migration
Using Data Loading
Extending the Application
Security
Troubleshooting
MySQL Error Messages
Conclusion
Additional Information

Introduction

This guide explains how to migrate from MySQL to Microsoft® SQL Server™ 2000 using a number of built-in SQL Server tools and utilities. It also provides guidelines on how to modify MySQL applications to work with SQL Server 2000. If you have an investment in MySQL applications, you can retain this investment while adding the advanced features of SQL Server 2000 to your application architecture.

Target Audience

The target audience for this white paper can be new to SQL Server and its operation, but should have a solid foundation in the MySQL DBMS and general database concepts. The target audience should have:

  • General database management knowledge.

  • A strong background in MySQL DBMS fundamentals.

  • Familiarity with the MySQL language.

  • Membership in the sysadmin fixed server role. The sysadmin role has complete control of the server. To learn more about SQL Server logins see the "Logins" section of SQL Server 2000 Books Online.

For clarity and ease of presentation, the reference development and application platform is Microsoft Windows® 2000 operating system and SQL Server 2000. The MySQL ODBC driver is used with MySQL, and the MySQL platform is Red Hat Linux 7.1 using MySQL 3.23.37.

Overview

MySQL is an open-source database management system (DBMS). It uses client/server architecture and is a multi-threaded, multi-user database server. MySQL was designed for speed; therefore, it does not provide many of the features provided by relational database systems, such as sub-queries, foreign keys, referential integrity, stored procedures, triggers, and views. In addition, it contains a locking mechanism that is not adequate for tables containing many write actions occurring simultaneously from different users. It is also lacking in reference to support for software applications and tools.

SQL Server 2000 is a complete Relational Database Management System (RDBMS) that also includes integrated analysis functionality for OLAP and data mining. SQL Server 2000 meets the data and analysis storage requirements of the largest data processing systems and commercial Web sites, yet at the same time can provide easy-to-use data storage services to an individual or small business.

The architecture of Microsoft SQL Server supports advanced server features, such as row-level locking, advanced query optimization, data replication, distributed database management, and Analysis Services. Transact-SQL (T-SQL) is the SQL dialect supported by SQL Server 2000.

The architecture features described in this chapter are only a few of the features provided by SQL Server 2000. SQL Server 2000 Books Online is a valuable resource installed during application set up. To use Books Online, open the Microsoft SQL Server program group and click Books Online.

The Migration Process

This chapter introduces the migration process by outlining the architecture of both MySQL and Microsoft SQL Server 2000. This chapter includes the following:

  • Preparing for migration

  • Data types, reserved words, and operators

  • MySQL tools for data migration

  • Microsoft SQL Server tools for data migration

  • Direct migration: Data Transformation Services (DTS)

  • Using data loading capabilities: Query Analyzer

  • Extending the application

  • Troubleshooting

Preparing for Migration

Proper migration planning is extremely important to ensure a successful outcome. Before beginning the transition, review the schema of the MySQL database that is being migrated. Compare the MySQL data types to the SQL Server 2000 data types, making note of the differences. The "Comparing MySQL to Microsoft SQL Server" section of this paper provides a framework of comparable data types. Be aware that some MySQL database objects may be in conflict with SQL Server 2000 reserved words. These words are included in the following section. You should backup and copy the MySQL database files before using DTS to migrate to SQL Server 2000.

Data Types, Reserved Words, and Operators

This section describes the data types used within SQL Server 2000. A table that compares MySQL data types to SQL Server 2000 data types is provided to ease the migration. It also provides you with a list of reserved words within Microsoft SQL Server. It includes information on the following:

  • Supported SQL Server data types

  • Comparing MySQL to SQL Server 2000

  • SQL Server reserved words

Supported SQL Server Data Types

Data Type

Description

BIGINT

Integer (whole number) data from -2^63 (-9223372036854775808) through 2^63-1 (9223372036854775807).

INT

Integer (whole number) data from -2^31 (-2,147,483,648) through 2^31 - 1 (2,147,483,647).

SMALLINT

Integer data from 2^15 (-32,768) through 2^15 - 1 (32,767).

TINYINT

Integer data from 0 through 255.

BIT

Integer data with either a 1 or 0 value.

DECIMAL

Fixed precision and scale numeric data from -10^38 +1 through 10^38 –1.

NUMERIC

Functionally equivalent to decimal.

MONEY

Monetary data values from -2^63 (-922,337,203,685,477.5808) through 2^63 - 1 (+922,337,203,685,477.5807), with accuracy to a ten-thousandth of a monetary unit.

SMALLMONEY

Monetary data values from -214,748.3648 through +214,748.3647, with accuracy to a ten-thousandth of a monetary unit.

FLOAT

Floating precision number data from -1.79E + 308 through 1.79E + 308.

REAL

Floating precision number data from -3.40E + 38 through 3.40E + 38.

DATETIME

Date and time data from January 1, 1753, through December 31, 9999, with an accuracy of three-hundredths of a second, or 3.33 milliseconds.

SMALLDATETIME

Date and time data from January 1, 1900, through June 6, 2079, with an accuracy of one minute.

CHAR

Fixed-length non-Unicode character data with a maximum length of 8,000 characters.

VARCHAR

Variable-length non-Unicode data with a maximum of 8,000 characters.

TEXT

Variable-length non-Unicode data with a maximum length of 2^31 - 1 (2,147,483,647) characters.

NCHAR

Fixed-length Unicode data with a maximum length of 4,000 characters.

NVARCHAR

Variable-length Unicode data with a maximum length of 4,000 characters. sysname is a system-supplied user-defined data type that is functionally equivalent to nvarchar(128) and is used to reference database object names.

NTEXT

Variable-length Unicode data with a maximum length of 2^30 - 1 (1,073,741,823) characters.

BINARY

Fixed-length binary data with a maximum length of 8,000 bytes.

VARBINARY

Variable-length binary data with a maximum length of 8,000 bytes.

IMAGE

Variable-length binary data with a maximum length of 2^31 - 1 (2,147,483,647) bytes.

CURSOR

A reference to a cursor.

SQL_VARIANT

A data type that stores values of various SQL Server-supported data types, except text, ntext, timestamp, and sql_variant.

TABLE

A special data type used to store a result set for later processing.

TIMESTAMP

A database-wide unique number that gets updated every time a row gets updated.

UNIQUEIDENTIFIER

A globally unique identifier (GUID).

Refer to the SQL Server 2000 Books Online subject "Data Types" for more information.

Comparing MySQL to SQL Server 2000

The following tables represent the mappings of the data types between MySQL and SQL Server 2000. For some MySQL data types, there is more than one alternative SQL Server data type. The tables include information on the following:

  • Numeric types

  • Data and time types

  • String types

Note

D: Applies to floating-point types and indicates the number of digits following the decimal point. The maximum possible value is 30, but should be no greater than M-2.

L: The actual length of column values

M: Indicates the maximum display size. The maximum legal display size is 255.

Numeric Types

MySQL

Size

SQL Server 2000

TINYINT

1 Byte

TINYINT*

SMALLINT

2 Bytes

SMALLINT

MEDIUMINT

3 Bytes

 

INT

4 Bytes

INT

INTEGER

4 Bytes

INT

BIGINT

8 Bytes

BIGINT

FLOAT(X<=24)

4 Bytes

FLOAT(0)

FLOAT(25<=X<=53)

8 Bytes

FLOAT(25)

DOUBLE

8 Bytes

FLOAT(25)

DOUBLE PRECISION

8 Bytes

FLOAT(53)

REAL

8 Bytes

REAL

DECIMAL

M Bytes(D+2, if M<D)

DECIMAL

NUMERIC

M Bytes(D+2, if M<D)

NUMERIC

* If not unsigned, the MySQL TINYINT datatype can range from -127 to 127; whereas the SQL Server TINYINT type always ranges 0 to 255. Therefore, unless it is an unsigned TINYINT, a MySQL TINYINT datatype should be converted to the SQL Server SMALLINT datatype.

Date and Time Types

MySQL

Size

SQL Server 2000

DATE

3 Bytes

SMALLDATETIME

DATETIME

8 Bytes

DATETIME

TIMESTAMP

4 Bytes

TIMESTAMP

TIME

3 Bytes

SMALLDATETIME

YEAR

1 Byte

SMALLDATETIME

String Types

MySQL

Size

SQL Server 2000

CHAR(m)

M Bytes, 1<=M<=255

CHAR

VARCHAR(m)

L+1 Bytes where L<=M and 1<=M<=255

VARCHAR

TINYBLOB

L + 1 Bytes where L<2^8

BINARY

BLOB

L + 2 Bytes where L<2^16

VARBINARY

TEXT

L + 2 Bytes where L<2^16

TEXT

MEDIUMBLOB

L + 3 Bytes where L<2^24

IMAGE

MEDIUMTEXT

L + 3 Bytes where L<2^24

TEXT

LONGBLOB

L + 4 Bytes where L<2^32

IMAGE

LONGTEXT

L + 4 Bytes where L<2^32

TEXT

ENUM (VALUE1, VALUE2, …)

1 or 2 Bytes depending on the number of enum. Values (65535 values max)

No data type available, however, CHECK constraint* provides functionality.

SET (VALUE1, VALUE2, …)

1, 2, 3, 4 or 8 Bytes depending on the number of set members maximum)

 

* The Check Constraint enforces data integrity by limiting the values accepted in a field. See Books Online subject "CHECK constraints" for more detailed information.

Microsoft SQL Server 2000 Reserved Words

ADD

EXCEPT

PERCENT

ALL

EXEC

PLAN

ALTER

EXECUTE

PRECISION

AND

EXISTS

PRIMARY

ANY

EXIT

PRINT

AS

FETCH

PROC

ASC

FILE

PROCEDURE

AUTHORIZATION

FILLFACTOR

PUBLIC

BACKUP

FOR

RAISERROR

BEGIN

FOREIGN

READ

BETWEEN

FREETEXT

READTEXT

BREAK

FREETEXTTABLE

RECONFIGURE

BROWSE

FROM

REFERENCES

BULK

FULL

REPLICATION

BY

FUNCTION

RESTORE

CASCADE

GOTO

RESTRICT

CASE

GRANT

RETURN

CHECK

GROUP

REVOKE

CHECKPOINT

HAVING

RIGHT

CLOSE

HOLDLOCK

ROLLBACK

CLUSTERED

IDENTITY

ROWCOUNT

COALESCE

IDENTITY_INSERT

ROWGUIDCOL

COLLATE

IDENTITYCOL

RULE

COLUMN

IF

SAVE

COMMIT

IN

SCHEMA

COMPUTE

INDEX

SELECT

CONSTRAINT

INNER

SESSION_USER

CONTAINS

INSERT

SET

CONTAINSTABLE

INTERSECT

SETUSER

CONTINUE

INTO

SHUTDOWN

CONVERT

IS

SOME

CREATE

JOIN

STATISTICS

CROSS

KEY

SYSTEM_USER

CURRENT

KILL

TABLE

CURRENT_DATE

LEFT

TEXTSIZE

CURRENT_TIME

LIKE

THEN

CURRENT_TIMESTAMP

LINENO

TO

CURRENT_USER

LOAD

TOP

CURSOR

NATIONAL

TRAN

DATABASE

NOCHECK

TRANSACTION

DBCC

NONCLUSTERED

TRIGGER

DEALLOCATE

NOT

TRUNCATE

DECLARE

NULL

TSEQUAL

DEFAULT

NULLIF

UNION

DELETE

OF

UNIQUE

DENY

OFF

UPDATE

DESC

OFFSETS

UPDATETEXT

DISK

ON

USE

DISTINCT

OPEN

USER

DISTRIBUTED

OPENDATASOURCE

VALUES

DOUBLE

OPENQUERY

VARYING

DROP

OPENROWSET

VIEW

DUMMY

OPENXML

WAITFOR

DUMP

OPTION

WHEN

ELSE

OR

WHERE

END

ORDER

WHILE

ERRLVL

OUTER

WITH

ESCAPE

OVER

WRITETEXT

MySQL Tools Used For Data Migration

MySQL provides a number of client tools and utilities, the most commonly used are:

  • mysql – An interactive client that allows you to issue queries on databases and view the results

  • mysqldump – A tool that allows you to extract the schema and data within a MySQL database and place into a file

  • mysqlimport – A tool that allows you to read the schema and data from a file and place into a MySQL database

  • mysqladmin – A tool that allows you to perform administrative tasks, such as creating databases and dropping databases

  • myODBC – Is a 32-bit Open DataBase Connectivity software that provides ODBC level 0 (whith level 1 and level 2 features) driver for connecting an ODBC-aware application to MySQL

SQL Server Tools for Migration

SQL Server has a rich set of tools and utilities to ease the migration from MySQL. SQL Server 2000 Data Transformation Services (DTS) is a set of graphical tools and programmable objects for extraction, transformation, and consolidation of data from disparate sources into single or multiple destinations.

Features of Data Transformation Services

Data Transformation Services in Microsoft SQL Server 2000 provides a number of ways to migrate different sources of data. DTS can be wizard-driven or built using the DTS package designer. The DTS wizards accomplish straight data copying quickly. The package designer enables developers to write custom transformation scripts in a variety of programming languages. The DTS tool allows you to:

  • Migrate data from MySQL to SQL Server 2000

  • Display representation of data before the migration

  • Migrate tables, data types, such as text and date

  • Migrate MySQL databases with MySQL tables

  • Generate and view a report of the migration

  • Customize tables, and the default data type mapping rules

  • Resolve conflicts, such as SQL Server reserved words

  • Delete and rename objects in the SQL Server schema model

  • Migrate individual table data

Data Transformation Services Glossary

The following terms are used to describe DTS:

DTS Package is an organized collection of connections, DTS tasks, DTS transformations, and workflow constraints that can be assembled graphically in the DTS Designer or programmatically.

DTS Task is a discrete set of functionality, executed as a single step in a package. Each task defines a work item to be performed as part of the data movement and data transformation process, or as a job to be executed.

DTS Transformation is one or more functions or operations applied against a piece of data before the data arrives at the destination.

DTS Package Workflow allows Data Transformation Services (DTS) steps and precedence constraints order work items in a DTS package. You can design DTS package workflow graphically, through DTS Designer, or programmatically.

Meta Data provides DTS with features for saving package meta data and data lineage information to Meta Data Services and linking those types of information. You can store catalog meta data for databases referenced in a package and accounting information about the history of a particular row of data for your data mart or data warehouse.

Direct Migration

The most direct option to migrate data from MySQL to Microsoft SQL Server is to install the myODBC support and create a DTS package to import and create database from MySQL to Microsoft SQL Server.

The following is a walkthrough of setting up Microsoft SQL Server to migrate MySQL database.

  1. Install MyODBC support, which is available at http://www.mysql.com/

  2. During the installation, you are presented with the following dialog box:

    Cc966396.mysql01(en-us,TechNet.10).gif

    Fill out the ODBC setup setting, using information such as:

    Windows DSN name:

    test

    Description:

    This is a test database

    MySQL Database:

    test

    Server:

    seawolf.microsoft.com

    User:

    cgunn

    Password:

    my_password

    Port:

    3306

    With the settings above, the Windows DSN name has to be unique on the computer making the connection, the server setting can fully qualified domain name (ensure that DNS, or you have provided some sort of name resolution), or IP address.

  3. Next, execute the DTS wizard. Select Import and Export Data from the Microsoft SQL Server program group, you should be presented with the following dialog box.

    Cc966396.mysql02(en-us,TechNet.10).gif

    Click Next, and move to the next step.

  4. Now provide the choose the necessary data source information, this should be MySQL as the ODBC data source, and test as the System DSN, and then the security credentials, user name and password, shown the next dialog box, then click Next.

    Cc966396.mysql03(en-us,TechNet.10).gif

  5. Provide the destination connection details, as shown in the dialog box below, and Click Next.

    Cc966396.mysql04(en-us,TechNet.10).gif

  6. The Specify Table Copy or Query dialog box allows the option of selecting database objects from the source, in this case MySQL. Select the Copy Table(s) and View(s) from the source database. It also important to note here again that MySQL does not support views, so in selecting this option, will be copying table objects only, as show below, click Next to continue.

    Cc966396.mysql05(en-us,TechNet.10).gif

  7. The Select Source Tables and View dialog box is next, from this dialog box, one can select the source tables and the destination tables.

    Cc966396.mysql06(en-us,TechNet.10).gif

  8. Click the ellipse button to conduct data transformation, as shown in the Column Mappings and Transformations dialog box below.

    Cc966396.mysql07(en-us,TechNet.10).gif

    In this dialog box, the source and destination data types are matched and nullable data fields are selected. When finished, click OK.

    The Save, Schedule, and Replicate Package dialog box is presented next, providing the capability to schedule the migration to off-peak hours, and well as to save the DTS package in different locations and formats.

    Cc966396.mysql08(en-us,TechNet.10).gif

  9. The DTS save package dialog box provides two types of passwords on the DTS package. The first password is the owner password, allowing you to protect any user/password information contained within the package, whilst the user password provides the capability to execute the package and prevent any unauthorized execution of the DTS package, as shown below, click Next to continue.

    Cc966396.mysql09(en-us,TechNet.10).gif

  10. Finally, the Completing the DTS Import/Export Wizard dialog box shows summary of the selected options within the DTS wizard.

    Cc966396.mysql10(en-us,TechNet.10).gif

    Click Finish, to begin the data migration process.

  11. The Executing Package dialog box displays the status of each task as executed. A green check means successful completion of a task. If a task fails to complete and the error stops the process, an error box will display with information about the error.

    Cc966396.mysql11(en-us,TechNet.10).gif

You have now successfully migrated data from MySQL to SQL Server 2000.

Using Data Loading

You can use mysqldump, a client program shipped with the MySQL server, to output the schema and data of a MySQL database into .sql/.txt files in various formats. DTS can use mysqldump output files to provide an offline data loading capability for large tables. The following topics explain the process of data loading:

  • Generating mysqldump data extract scripts

  • Setting up scripts transfer

  • Using the extracted scripts

Generating mysqldump Data Extract Scripts

MySQL has a utility to dump a database or a collection of databases for backup or for transferring the data to SQL Server.

The mysqldump utility provides the capability to create SQL scripts of a database.

The minimal syntax for mysqldump is:

Shell> mysqldump [OPTIONS] database [tables]

Information on the options available for mysqldump are presented later in this whitepaper, or consult the MySql reference manual.

After using mysqldump you should have an SQL script of the database.

Setting up Scripts Transfer

After generating the scripts with mysqldump, they can then be transferred to SQL Server, using a network application like File Transfer Protocol (FTP) to transfer the scripts from the MySQL host to SQL Server 2000 computer.

Using the extracted scripts with SQL Query Analyzer

Generated scripts can now be used to create database objects and insert data. The preferred method to construct a database schema from MySQL scripts is to use the SQL Query Analyzer tool that is included with SQL Server 2000.

You can run SQL Query Analyzer directly from the Start menu, or from inside SQL Server Enterprise Manager. You can also run SQL Query Analyzer from the command prompt by executing the isqlw utility.

In order for the script to correctly execute there is some additional work required, which involves certain changes with the SQL dialect. Also, remember to walk through the SQL script and change the data types to SQL Server compatible types. The diagram below shows an imported script from mysqldump, it is important to note that the dump is an ASCII script file.

Cc966396.mysql12(en-us,TechNet.10).gif

Microsoft SQL Server 2000 SQL Query Analyzer allows you to:

  • Create queries and other SQL scripts and execute them against SQL Server databases

  • Quickly create commonly used database objects from predefined scripts

  • Quickly copy existing database objects

  • Execute stored procedures without knowing the parameters

  • Debug stored procedures

  • Debug query performance problems

  • Locate objects within databases, or view and work with objects

  • Quickly insert, update, or delete rows in a table

  • Create keyboard shortcuts for frequently used queries

  • Add frequently used commands to the Tools menu

Extending the Application

After you move the data management portion of the MySQL application to Microsoft SQL Server, you can rely on SQL Server to protect the data and maintain all referential integrity and business rules that you have encoded in Transact-SQL.

Database application programming interfaces (APIs) such as ADO, OLE DB, and ODBC expose database data through a variety of programming languages. One can access these APIs with development systems such as Microsoft Visual C++, Microsoft Visual Basic, or Microsoft Visual J++.

In addition, if the application grows, you can move the Microsoft SQL Server to larger computers without changing the application; SQL Server automatically recognizes hardware configurations and tunes itself for optimal memory, I/O and processor usage.

Accessing data from the Internet

SQL Server provides the capability to extend your application to a Web-based interface. This ability allows the application to be accessed anywhere, at anytime. SQL Server can be integrated with Microsoft Internet Information Services (IIS), by using the IIS Web server and using ActiveX Data Objects (ADO) inside Active Server Pages (ASP) to provide a fast and efficient user interface to data stored in SQL Server.

More information can be found at http://www.msdn.microsoft.com

Security

Database security in SQL Server 2000 is robust yet easy to maintain. With respect to both SQL Server and MySQL, it is important to think of security at two levels: 1) gaining access to the server, and 2) gaining access to the individual database/s.

MySQL has a unique way of securing access to the server by restricting access to the source, in the case of the client, by IP address or fully qualified domain name, wildcards such as '%'. SQL Server requires user accounts, either managed by the operating system, or stored inside the SQL Server master database.

SQL Server provides group access by the use of roles, this can ease the management of a database by establishing common access for a group of users.

The following steps outline how Microsoft SQL Server provides access to a server as well as the database through the Enterprise Manager tool.

  1. Open Enterprise Manager, drill down to the Security Folder, Select the Logins icon, right mouse click and select New Login.

    Cc966396.mysql13(en-us,TechNet.10).gif

    The SQL Server Login Properties dialog is displayed, enter a login name; this is similar to a user name in MySQL. Choose SQL Server Authentication to provide a level of security validated at the SQL Server.

  2. Specify the default database and language.

    Cc966396.mysql14(en-us,TechNet.10).gif

  3. Select the Server Roles tab at the top of the dialog box to provide access to server permissions, the role highlighted here is the sysadmins (System Administrators), and this is equivalent to root access in MySQL.

    Cc966396.mysql15(en-us,TechNet.10).gif

  4. The next tab is for Database Access. This property page provides access to not only one database, but to all databases residing physically on the SQL Server. After a database is selected, then also a database role can be set. By default, all users have access to the public role. Permissions still need to be assigned for this role. An additional role selected in this diagram is db_owner, which allows the user to have unrestricted access to the database only, and not to the entire SQL Server, or any other databases, unless the databases are selected individually, and the db_owner permission is assigned.

    Cc966396.mysql16(en-us,TechNet.10).gif

  5. After clicking on OK, a prompt for the password appears.

    mysql17

The new login is visible in the Enterprise Manager. You will also notice in the diagram that there is a login account called 'sa', this systems administrator account should have a password, during the installation of SQL Server, there is the option to have a blank password for this login, a password should always be applied.

Cc966396.mysql18(en-us,TechNet.10).gif

For more information on creating Microsoft SQL Server logins, consult the SQL Server Books Online topic "Managing Security."

Database Permissions

SQL Sever 2000 provides the same capability to secure a database through restricting access to data definition language (DDL) and data manipulation language (DML) statement permissions, the steps to achieve this is similar to creating a login. Setting permissions for a SQL Server database is easily accomplished by using the Enterprise Manager tool.

Data Manipulation Language Permissions

  1. Open Enterprise Manager, and drill into the databases folder and select the database that is to have permissions set. Select the users icon, then select the database user, right click and select Properties.

    Cc966396.mysql19(en-us,TechNet.10).gif

  2. Click the permissions button.

    Cc966396.mysql20(en-us,TechNet.10).gif

  3. The permissions window provides the capability to set DML statements on all of the database objects, such as tables, views, and stored procedures. After the permissions are selected, click OK.

    Cc966396.mysql21(en-us,TechNet.10).gif

Data Definition Language Permissions

  1. To provide DDL statement access to your database, then you need to select the properties of the database. Select the database icon then right click. Select Properties.

    Cc966396.mysql22(en-us,TechNet.10).gif

  2. Next, select the permissions tab in the database properties window.

    Cc966396.mysql23(en-us,TechNet.10).gif

  3. After selecting the appropriate permission(s), click OK.

Troubleshooting

This chapter provides troubleshooting solutions and information about:

  • Defining the user account

  • Dumping MySQL data

  • Optimizing command line options

Defining the User Account

When you installed the MySQL server onto your system, the root user, a user account will full DBA privileges, was set up by default. You should logon to the MySQL server through ODBC using the root user. (Note: By default, the root user is only given logon access to the localhost, remember to allow the root user to logon from the computer IP or DNS address running the DTS wizard.)

Dumping MySQL Data

The table below provides an explanation of the syntax used to dump the MySQL data and to regenerate the database from the mysqldump text file.

Command

Description

mysqldump

A tool that allows you to extract the schema and data in a MySQL database to a file.

mysql

Loads MySQL so you can carry out the command.

-u user name

The root MySQL user name. This user should have full DBA privileges.

-ppassword

The password of the root user of your MySQL database server.

--opt

Optimizes table dumping speed and writes a dump file that is optimal for reloading speed. This option enables the –add-drop-table, --add-locks, --all, --extended-insert, --quick and –lock-tables option. For a list of the options enabled by --opt, refer to the Optimizing Options for MySQL section.

databasename

The name of the database containing the information you want to dump to an output text file.

<

Symbol used for redirecting the input in UNIX and Windows NT/2000.

filename.sql

File name containing the MySQL.

To dump the MySQL data, use the following command:

#> mysqldump –u user name –ppassword –opt databasename < filename.sql

To recreate the database from the mysqldump output text file into a database, use the following command:

#> mysql –u user name –ppassword databasename < filename.sql

Optimizing Command Line Options

You automatically enable options within the mysqldump command line by using –opt. For more information on dumping the MySQL data, refer to the Dumping MySQL Data section. The following table is the --opt commands:

Command

Description

--add-drop-table

Adds a DROP TABLE If EXISTS statement before each CREATE TABLE statement

--all

Includes all of the MySQL specific create options.

--extended-insert

Writes multiple row insert statements

--quick

Dumps directly to the standard output without buffering the query. If you suspend mysqldump while using this option, you may interfere with other clients because it could cause the server to wait.

--lock-tables

Locks all tables as read only

MySQL Error Messages

This section provides error messages that you may receive while migrating your MySQL database to SQL Server 2000.

Error Messages

As you work with DTS to migrate the data, the following error messages can occur:

Error Message

Solution

Cannot connect to MSQL Server <name>.
Is there a MySQL server running on the system/port you are trying to connect to?

The error can be caused by the following:
· The source port is set to 3306 by default. This port number refers to the port through which MySQL communicates on. If the port is defined differently on MySQL, change the port setting in the MySQL ODBC settings.
· Ensure that the user has appropriate DBA permissions to access the MySQL server.
· Ensure that the username is valid.

There is already an object named 'tablename' in the database

The table is created during the execution of the DTS package, ensure that the table is dropped or recreated during package execution.

Conclusion

This paper has provided the basic information and background necessary to successfully migrate the database schema and data from MySQL to Microsoft SQL Server 2000. SQL Server 2000 provides a greater level of reliability, scalability and functionality to applications.

Additional Information

Microsoft SQL Server Web site

Microsoft Developer Network (MSDN) Web site

Microsoft Universal Data Access Web site

MySQL Web site

Was this page helpful?
(1500 characters remaining)
Thank you for your feedback
Show:
© 2014 Microsoft