• No results found

Guide to the MySQL Workbench Migration Wizard: From Microsoft SQL Server to MySQL

N/A
N/A
Protected

Academic year: 2021

Share "Guide to the MySQL Workbench Migration Wizard: From Microsoft SQL Server to MySQL"

Copied!
20
0
0

Loading.... (view fulltext now)

Full text

(1)

Guide to the MySQL Workbench

Migration Wizard:

From Microsoft SQL Server to MySQL

(2)

Table of Contents

 

Introduction ... 3

 

MySQL & LAMP... 3

 

MySQL Reduces Database TCO by over 90% ... 4

 

MySQL Migration Wizard... 5

 

Set up the parameters to connect to your source database ... 8

 

Set up the parameters to connect to your target Database... 9

 

Select the Schema to Migrate ... 10

 

Select the Objects to Migrate... 11

 

Review the Proposed Migration ... 13

 

Run the resultant MySQL code to create the database objects . 14

 

Transfer the Data to the MySQL Database ... 16

 

Verification Step... 18

 

Conclusion ... 19

 

(3)

Introduction

 

MySQL has become the world's most popular open source database because of its reliability, high-performance, and ease of use. MySQL combines the benefits of a widely adopted open source database solution with high quality, 24x7 support, training and consulting services delivered by Oracle. As a result, MySQL users benefit from both, a strong ecosystem with millions of users, as well as the backing of the world’s leading database company.

MySQL & LAMP

MySQL is the “M” in the rapidly growing open source LAMP stack (Linux, Apache, MySQL PHP/Perl/Python). LAMP is chosen by savvy IT leaders as a way to improve operational efficiency and reduce IT infrastructure costs. Plus, LAMP is a proven technology stack used by the most popular Web sites in the world including Facebook, Google, Twitter, YouTube and Zappos.

MySQL supports over 20 platforms including Windows, Linux and Unix. In addition, MySQL gives developers the freedom to develop applications using the language of their choice including PHP/Perl/Python, Ruby, C, C++, C#, as well as Java and .Net.

(4)

MySQL Reduces Database TCO by over 90%

 

In MySQL’s annual surveys, lower cost is consistently shown to be the number one reason why users choose MySQL.

Microsoft changed the SQL Server pricing from Per Processor to Per Core, resulting in a major price increase for customers who want to take advantage of the latest multi-core hardware.

Below is a chart which compares the 3 year database TCO of MySQL Enterprise Edition vs. Microsoft SQL Server 2014 Enterprise Edition. In this configuration, Microsoft has doubled the license cost of SQL Server 2014 over SQL Server 2008.

3 Year TCO vs Microsoft SQL Server 2014

Hardware Configuration:

- Intel x86_64 Servers: 4, CPUs/Server: 4, Cores/CPU: 8

(5)

MySQL Migration Wizard

 

MySQL introduced a new Migration Wizard module as part of MySQL Workbench 5.2.41. The Migration Wizard allows you to easily and quickly migrate databases from Microsoft SQL Server, PostgreSQL, Sybase ASE, and most ODBC-capable RDBMSs to MySQL. In addition, you can use the module to create MySQL to MySQL database copies that can be used for tasks such as copying a database across servers or migrating data across different versions of MySQL.

In rest of this paper, I’ll show you step-by-step how to migrate a Microsoft SQL Server database to MySQL database using the MySQL Workbench Migration Wizard. First, make sure you have:

Appropriate user access to a running SQL Server instance of the database

you want to migrate,. From this point on, I’ll refer to this database as the source database. I have a remote SQL Server instance available and the sample

Northwind or AdventureWorks database on top of it. I’m using the standard “sa” user, which has full privileges. You can use whatever SQL Server version you have at hand. Keep in mind that the Migration Wizard officially supports SQL Server 2000 and newer so very old SQL Server versions may not work.

Appropriate user access to a running MySQL Server instance. The Migration Wizard supports MySQL versions from 5.0 onwards so make sure you have a supported version. For this tutorial I’m using MySQL Server 5.5.15 CE installed in the same PC that’s running MySQL Workbench.

MySQL Workbench 6.0 or newer for Windows. The Migration Wizard is also available in the Linux and Mac versions of MySQL Workbench, but running it from Windows will save you from installing an ODBC driver to connect to your SQL Server instance. See the MySQL Documentation on how to proceed in those cases.

(6)

Open MySQL Workbench and start the Migration Wizard

From the main MySQL Workbench screen you can start the Migration Wizard by clicking on the Database Migration launcher in the Workbench Central panel or through

Database –> Migrate in the main menu. Or use the shortcut on the right hand side.

A new tab showing the Overview page of the Migration Wizard should appear.

Please read the Prerequisites section carefully. Note that you need an ODBC driver for your installed source RDBMS. Recent versions of Windows come with some ODBC drivers installed. For Windows 2000 and earlier these can be installed with the Microsoft Data Access Components (MDAC).

(7)

Check that you have an ODBC driver for SQL Server. Start the Windows ODBC Data Source Administrator from MySQL Workbench. You should see something like this:

Because SQL Server if often preinstalled with Windows, you may actually find two SQL Server ODBC drivers installed. The driver is frozen at the level of functionality provided by SQL Server 2000. This may meet your needs if your database doesn’t make use of the new features and datatypes introduced after SQL Server 2000. If you have a SQL Server instance on the same machine where you installed MySQL Workbench, or where you installed the SQL Server Native Client, then you will have see it listed in the image (named “SQL Server Native Client…”). This comes with SQL Server and fully supports the companion SQL Server version. If you don’t see it listed, you can also download and install the Microsoft SQL Server 2012 Native Client:

SQL Server 2012 Feature Pack

or

SQL Server 2012 Native Client - x86 Package

(8)

This client driver is compatible with SQL Server 2012 as well as with previous SQL Server versions.

Once you decide which driver to use, note its name as shown in the ODBC Data Source Administrator. You will use this name to connect to your SQL Server instance from the Migration Wizard. Let’s go back to the Migration Wizard (you can close the ODBC Data Source Administrator now) and start the migration process.

Set up the parameters to connect to your source database

Click on the Start Migration button in the Overview page to advance to the Source Selection page. In this page you need to provide the information about the RDBMS you are migrating, the ODBC driver to use, and the parameters for the connection.

If you open the Database System combo box you’ll find a list of the supported

RDBMSs. Select Microsoft SQL Server from the list. Just below it there’s another combo box named Stored Connection. It will list saved connection settings for that RDBMS. You can save connections by marking the checkbox at the bottom of the page and giving them a name you choose.

The next combo box is for the selection of the Connection Method. This time we are going to select ODBC Data Source from the list since we are using the native ODBC drivers provided by Microsoft. Other alternatives are ODBC data sources and ODBC FreeTDS (FreeTDS is a popular open source driver for Microsoft SQL Server and Sybase).

Now it’s the time for to put ting the parameters for your connection. In the DSN drop down select the ODBC driver name from the previous step.

Enter your credentials (user name and password – stored in Vault) to connect to the server. If you know the name of the database you want to migrate, enter it in the

Database field. Otherwise leave it blank; you will be able to select the name from a list at a later point.

(9)

At this point you should have something like this:

Click on the Test Connection button to check the connection to your SQL Server instance. If you put the right parameters you should see a message reporting a successful connection attempt.

Set up the parameters to connect to your target Database

Click on the Next button to move to the Target Selection page. Once there, set the parameters to connect to your MySQL Server instance. When you are done click on the

(10)

Select the Schema to Migrate

Click on the Next button to move to the next page. The Migration Wizard will

communicate to your SQL Server instance to fetch a list of the catalogs and schemata. If you left blank the Database field in the Source Selection page, it will retrieve all of the catalogs in the server. Otherwise it will just fetch the schema(s) corresponding to the catalog you explicitly typed.

Verify that all tasks finished successfully and click on the Next button to move forward. You will be given a list of catalogs and their corresponding schema(s). Select the ones you want to migrate. Keep in mind that you can only migrate schemata from one catalog at a time. The Schema Selection page will look like this:

(11)

Select the Northwind, Adventure works or your database of choice from the list and its default schema dbo, or change it depending on what you want to do. Now look at the options below. A SQL Server database is comprised of one catalog and one or more schemas. MySQL only supports one schema in each database (to be more precise, a MySQL database is a schema) so we have to tell the Migration Wizard how to handle the migration of schemas in our source database. We can either keep all of the schemas as they are (the Migration Wizard will create one database per schema), or merge them into a single MySQL database. The two last options are for specifying how the merge should be done: either remove the schema names (the Migration Wizard will handle the possible name collisions that may appear along the way) or add the schema name to the database object names as a prefix. Let’s select the second option since we only have one schema and we are not particularly interested in keeping its meaningless dbo name.

Select the Objects to Migrate

Move to the next page using the Next button. You should see the reverse engineering of the selected schema in progress. At this point the Migration Wizard is retrieving relevant information about the involved database objects (table names, table columns, primary and foreign keys, indices, triggers, views, etc.). You will be presented with a page showing the progress as shown in the image below.

Depending on your connection speed to the server, it may take some time for your SQL Server and your local machine to load, etc. Wait for it to finish and verify that everything went well.

(12)

Then move to the next page. In the Source Objects page you will have a list with the objects that were retrieved and are available for migration. It will look like this:

 

As you can see the Migration Wizard discovered table and view objects in our source database. Note that only the table objects are selected by default to be migrated. You can select the view objects, or the Routine,, but you would have to provide their corresponding MySQL equivalent code later (no automatic migration is available for them at this time) so let’s leave that off for now.

If you click on the Show Selection button you will be given the opportunity to select exactly what you want to migrate as shown here:

The items in the list to the right are the ones to be migrated. Notice how you can use the filter box to easily filter the list (wildcards are allowed as you can see in the image

(13)

above). By using the arrow buttons you can filter out the objects that you don’t want to migrate. At the end, don’t forget to clear the filter text box to check the full list of the selected objects. We are going to migrate all of the table objects, so make sure that all of them are in the Objects to Migrate list and that the Migrate Table Objects checkbox is checked. Most of the time you’ll want to migrate all objects in the schema, so you can just click Next.

Review the Proposed Migration

 

Move to the next page. You will see the progress of the migration there. At this point the Migration Wizard is converting the objects you selected into their equivalent objects in MySQL and creating the MySQL code needed to create them in the target server. Let it finish and move to the next page. You might have to wait a little bit before the Manual Editing page is ready but you’ll end up with something like these next 2 screens:

Next

As you can see in the images above there is a combo box named View. By using it you can change the way the migrated database objects are shown. Also take a look at the

View->Column Mappings and then click the Show Code and Messages button. If you click on it you can see (and edit) the generated MySQL code that corresponds to the selected object.

(14)

An interesting option in the View combo box is the Column Mappings. This will show you all of the table columns and will let you individually review and fix the mapping of column types, default values, and other attributes.

Run the resultant MySQL code to create the database objects

Move to the Target Creation Options page. It will look like this:

   

As you can see there, you are given the options of running the generated code in the target RDBMS (your MySQL instance from the second step) or just dumping it into a

(15)

SQL script file. Leave it as shown in the image and move to the next page. The migrated SQL code will be executed in the target MySQL server.

You can view its progress in the Create Schemata page:

Once the creation of the schema and their objects finishes, you can move to the Create Target Results page. It will display a list with the created objects, indicating whether there were errors while creating them. Review it and make sure that everything went OK. It should look like this:

You can still edit the migration code using the code box to the right and save your changes by clicking on the Apply button. Keep in mind that you would still need to

(16)

recreate the objects with the modified code in order to actually perform the changes. This is done by clicking on the Recreate Objects button. You may need to edit the generated code if its execution failed. You can then manually fix the SQL code and re-execute everything. In this tutorial we are not changing anything, so leave the code as it is and move to the Data Transfer Setup page by clicking on the Next button.

Transfer the Data to the MySQL Database

 

The next steps in the Migration Wizard involve the transference of data from the source SQL Server database into your newly created MySQL database. The Data Transfer Setup page allows you to configure this process.

   

There are two sets of options here: The first one allows you to perform a live

transference and/or to dump the data into a batch file that you can run later. The other set of options allows you to tune up this process.

Leave the default values for the options in this page as shown in the above image and move to the actual data transference by jumping to the next page. It will take a little while to copy the data. At this point the corresponding progress page will look familiar:

(17)

Once it finishes, move to the next page. A report page will be displayed with a summary of the entire process:

 

(18)

Verification Step

Now that your database was successfully migrated, check the results. Open a SQL Editor page associated with your MySQL Server instance, and select and query the database. You can try something like “SELECT * FROM adventureworks.contact”. You should get something like this:

You can also create an EER diagram from the migrated database. Click on the

Database-> Reverse Engineer … in the main screen of MySQL Workbench and follow the wizard steps. If you migrated foreign keys those relationships will be shown.

Once done, go to Arrange –> Autolayout in the main menu to accommodate your EER model and you should get something like this:

(19)

Conclusion

By following these steps, you should have a good understanding of the MySQL Workbench Migration Wizard’s capabilities, and should be ready to use it for your own migrations. The documentation is also available you can always ask questions at any time in the MySQL Migration Forum.

Additional  Resources  

Video Tutorials

MySQL Workbench Tutorial

A comprehensive MySQL Workbench tutorial video that shows how to best use the official MySQL GUI application.

How to Setup a Restricted SQL Server Account for Migrations with MySQL Workbench

Create a SQL Server User to migrate your databases to MySQL from Microsoft SQL Server.

MySQL White Papers

Migrating from Microsoft SQL Server to MySQL

An increasing number of organizations are migrating from Microsoft SQL Server to MySQL because they have reached the conclusion that the combination of cost-savings, platform freedom, and feature set of MySQL make for a compelling business case.

A Guide for Migrating From Microsoft Access to MySQL

Learn how to migrate from Microsoft Access and achieve better scalability, cost-savings, platform freedom and more.

MySQL Enterprise Edition

MySQL Enterprise Edition includes the most comprehensive set of advanced features and management tools to achieve the highest levels of scalability, security, reliability, and uptime.

MySQL Enterprise Monitor

Explores the MySQL Enterprise Monitor in detail and explains how it can be leveraged as a "Virtual MySQL DBA" assistant to help over-extended DBAs proactively manage more MySQL servers with less time and effort.

(20)

MySQL Case Studies

Ticketmaster

Ticketmaster.com Migrated from Microsoft SQL Server to MySQL and Improved Scalability by 400%.

thePlatform

thePlatform Migrates Media Publishing System to MySQL and Achieves 23x Performance Gain with the MySQL Query Analyzer.

NetMotion Wireless

NetMotion Wireless Migrates Product from Microsoft SQL Server to MySQL for Lower Costs and Increased Flexibility.

MySQL Migration Case Studies

Learn how organizations are saving money while improving performance and scalability by migrating to MySQL.

References

Related documents

Click take your MySQL server instance hold the Server Administrator section of MySQL workbench to create major new database user and assign privileges to your new fill Click on

You should see ‘Service successfully removed’. All other versions are not supported. The license agreement will display. Select the I accept the terms in the License Agreement if

repository in your MySQL database, you must set up the MySQL ODBC driver so that you can connect to this repository from Enterprise Architect and publish model contents as

 MySQL Enterprise backup writes status to internal backup table.  Useful to see detailed history

repository in your MySQL database, you must set up the MySQL ODBC driver so that you can connect to this repository from Enterprise Architect and publish model contents as

In patients with type 2 diabetes inadequately controlled by dietary therapy, nateglinide and rosiglitazone resulted in similar overall improvements in glucose control and beta

Parameter Table Editor Spreadsheet  ProTab vs... Parameter Table Editor Spreadsheet 

To learn more about the Ambit Engineering Salary & Contract Index, please feel free to contact one of our recruitment specialists on 08 9423 1400 or