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 https://www.mysql.com/

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

   

   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.

   

   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.

   

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

   

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.

   

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.

   

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

   

   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.

   

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.

   

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

    

    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.

    

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.

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 https://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.

   

   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.

   

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.

   

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.

   

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

   

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.

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.

   

2. Click the permissions button.

   

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.

   

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.

   

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

   

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