How-To: Guide to Database Migration from Microsoft SQL Server using MySQL Workbench

July 25, 2012 – 2:40 pm by Sergio de la Cruz

MySQL Workbench 5.2.41 introduces a new Migration Wizard module. This module allows you to easily and quickly migrate databases from various RDBMS products to MySQL. In this initial version, migrations from Microsoft SQL Server are supported, but it should also be possible to migrate from most ODBC capable RDBMS as well, using its generic RDBMS support. Additionally, you can use it to perform MySQL to MySQL database copies, which can be used for tasks such as copying a database across servers or migrating data across different versions of MySQL.

So let’s get our hands dirty and run through the Migration Wizard in order to migrate a Microsoft SQL Server database to MySQL. In the rest of this post I assume that you have:

  • A running SQL Server instance in which you have proper access to the database you want to migrate. (I’ll call this database from now on the source database). I have a remote SQL Server 2000 instance available and the sample Northwind 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 older SQL Server versions might not work.
  • A running MySQL Server instance with proper user access. 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 where MySQL Workbench is running.
  • MySQL Workbench 5.2.41 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 us from installing an ODBC driver to connect to our SQL Server instance. Other blog posts will follow on how to proceed in those cases.

Let’s start now…

 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.

The initial screen of MySQL Workbench

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

Overview Page

Read carefully the Prerequisites section. You can read there that you need an ODBC driver for your source RDBMS installed.  Any recent version of Windows comes with some ODBC drivers installed. For Windows 2000 and earlier these can be installed with the Microsoft Data Access Components (MDAC).

You should check if you have an ODBC driver for SQL Server. Start the Windows ODBC Data Source Administrator from MySQL Workbench using the Plugins –> Start ODBC Administrator menu item or just open a Windows terminal and type odbcad32.exe. Once there, go to the Drivers tab. You should see something like this:

Windows ODBC Data Source Administrator

As you can see, I already have two SQL Server ODBC drivers installed. The first one listed here (named “SQL Server”) comes preinstalled with Windows (you should have it as well). This driver is frozen at the level of functionality provided by SQL Server 2000 and it should be enough for you if your database doesn’t make use of the new features and datatypes introduced after this SQL Server version. If you have a SQL Server instance in the same machine where you installed MySQL Workbench then you should also have the second driver listed in the image (named “SQL Server Native Client…”). This one comes with SQL Server and fully supports the companion SQL Server version. If you don’t have it listed, you can download and install the Microsoft SQL Server 2012 Native Client. This is compatible with SQL Server 2012 as well as with previous SQL Server versions.

Once you take your pick on the driver to use, write down somewhere its name as shown in the ODBC Data Source Administrator. You’ll need 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 RDBMSes. 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 of your preference.

The next combo box is for the selection of the Connection Method. This time we are going to select ODBC (native) 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 putting the parameters for your connection. In the Driver text field, type the ODBC driver name from the previous step.

In the Server field put the values that identify your machine and your SQL Server instance name. If you don’t recall these, go to SQL Server Management Studio and connect to your server. Then right click on the server icon in the Object Explorer and the name will be displayed in the new window that appears. If you have SQL Server Express Edition installed in your local machine and you haven’t change the server name, then the default “localhost\SQLEXPRESS” should work for you. Another option is to put your server IP address instead of the host name. You can also specify a port by adding a comma after the server name/IP (E.g “127.0.0.1,1433″). The instance name is optional and defaults to the default SQL Server instance in the source host.

Now put your credentials (user name and password) to connect to the server. If you know the name of the database you want to migrate, put it in the Database field. Otherwise leave it blank and you will be later given a list to select your database there. At this point you should have something like this:

Source Selection Page

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 Test Connection button and verify that you can successfully connect to it.

Target connection parameters

Select the schema(ta) 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 schemata corresponding to the catalog you explicitly typed.

Fetch schemata list page

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 schemata to select the ones 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:

Schemata Selection Page

Select the Northwind sample database from the list and its default schema dbo. Now look at the options below. A SQL Server database is comprised of one catalog and one or more schemata. 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 schemata in our source database. We can either keep all of the schemata 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 colisions thay may appear along the way) or either adding 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 a page showing the progress as shown in the image below.

Reverse Engineer Progress

 It may take some time, depending on how fast is your connection to the server, your SQL Server load and your local machine load. Wait for it to finish and verify that everything went well. 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:

Source Objects Page

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 too, but you would have to provide their corresponding MySQL equivalent code later (no automatic migration is available for them yet) so let’s leave them off for now. The same applies for stored procedures, functions and triggers.

If you click on the Show Selection button you will be given the oportunity to select exactly which of them you want to migrate as shown here:

Source Objects Page Expanded

The items in the list to the right are the ones to be migrated. Note how you can use the filter box to easily filter the list (wildcards are allowed as you can see in the image 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 anyway, 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 this:

Manual Editing Page

As you can see in the image 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 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. Furthermore, you can double click in a row in the object tree and edit the name of the target object. Suppose you want your resultant database to have another name. No problem: double click on the Northwind row and rename it.

Manual Editing Page Expanded

An interesting option in the View combo box is the Column Mappings one. It 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.

Column Mappings

Run the resultant MySQL code to create the database objects

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

Target Creation Options Page

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 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:

Create Schemata Progress Page

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

Create Target Results Page

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 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 are for 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.

Data Transfer Setup Page

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 gives you a way 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:

Bulk Data Transfer Page

Once it finishes, move to the next page. You will be presented a report page summarizing the whole process:

Migration Report Page

And that should be it. Click on the Finish button to close the Migration Wizard.

A little verification step

Now that the Northwind database was successfully migrated, let’s see the results. Open an SQL Editor page associated with your MySQL Server instance and query the Northwind database. You can try something like “SELECT * FROM Northwind.categories”. You should get something like this:

A query on the MySQL migrated database

And why not create an EER diagram from the migrated database? Click on Create EER Model From Existing Database in the main screen of MySQL Workbench and follow through the wizard steps. Once done go to Arrange –> Autolayout in the main menu to accomodate your EER model and you should get something like this:

EER Model for the Migrated Database

Conclusions

By now you should have a pretty good idea of the capabilities of the Migration Wizard and should be ready to use it for your own migrations. The official documentation is also there for you and you can always ask any question in the comments of this post or in the migration official forum. Live long and prosper!

Sergio de la Cruz

  1. 24 Responses to “How-To: Guide to Database Migration from Microsoft SQL Server using MySQL Workbench”

  2. Thank you very much! That helped a lot. It couldn’t be better.

    By imnobody on Jul 27, 2012

  3. In the stage “Copy data to target RDBMS” the debug log says: “*** ERROR: Copy data to target RDBMS: [Error 87] El par?metro no es correcto”

    I’ve tried with several drivers and or connection strings, I’ve tried with the option “generate batch file” which has no use whatsoever. All no to avail.

    What is worse, it takes me about an hour to reach this point. How come you can’t save the migration project to its current state? I can’t believe how much time I’ve already wasted with mysqlworkbench.

    I’ll push my luck with a few shareware tools. At least in demo mode they perform fine. Slower but at least I get some results.

    By Felipe Figueroa on Aug 25, 2012

  4. @Felipe: we maybe be able to help if you file a bug report in http://bugs.mysql.com with the log file and details about the problem you’re having

    By akojima on Aug 27, 2012

  5. same here – importing AdventureworksDW2008R2 community sample fails at data import. Tables seemed to create fine in MySQL, but data import fails. Log follows:
    Starting…

    Prepare information for data copy…

    Prepare information for data copy done

    Determine number of rows to copy….

    Counting number of rows in tables…

    wbcopytables.exe –count-only –passwords-from-stdin –odbc-source=DSN=aw;DATABASE=;UID=sa –table [AdventureWorksDW2008R2] [dbo].[DimPromotion] –table [AdventureWorksDW2008R2] [dbo].[ProspectiveBuyer] –table [AdventureWorksDW2008R2] [dbo].[FactFinance] –table [AdventureWorksDW2008R2] [dbo].[DimSalesTerritory] –table [AdventureWorksDW2008R2] [dbo].[DimProductCategory] –table [AdventureWorksDW2008R2] [dbo].[FactSalesQuota] –table [AdventureWorksDW2008R2] [dbo].[DimAccount] –table [AdventureWorksDW2008R2] [dbo].[DimEmployee] –table [AdventureWorksDW2008R2] [dbo].[DimCurrency] –table [AdventureWorksDW2008R2] [dbo].[DimGeography] –table [AdventureWorksDW2008R2] [dbo].[DimProduct] –table [AdventureWorksDW2008R2] [dbo].[FactCurrencyRate] –table [AdventureWorksDW2008R2] [dbo].[FactCallCenter] –table [AdventureWorksDW2008R2] [dbo].[DatabaseLog] –table [AdventureWorksDW2008R2] [dbo].[FactSurveyResponse] –table [AdventureWorksDW2008R2] [dbo].[DimCustomer] –table [AdventureWorksDW2008R2] [dbo].[DimReseller] –table [AdventureWorksDW2008R2] [dbo].[DimDate] –table [AdventureWorksDW2008R2] [dbo].[DimScenario] –table [AdventureWorksDW2008R2] [dbo].[FactResellerSales] –table [AdventureWorksDW2008R2] [dbo].[FactInternetSalesReason] –table [AdventureWorksDW2008R2] [dbo].[DimOrganization] –table [AdventureWorksDW2008R2] [dbo].[AdventureWorksDWBuildVersion] –table [AdventureWorksDW2008R2] [dbo].[DimDepartmentGroup] –table [AdventureWorksDW2008R2] [dbo].[FactInternetSales] –table [AdventureWorksDW2008R2] [dbo].[DimProductSubcategory] –table [AdventureWorksDW2008R2] [dbo].[FactAdditionalInternationalProductDescription] –table [AdventureWorksDW2008R2] [dbo].[DimSalesReason]

    18:46:04 [INF][ copytable]: Opening ODBC connection to ‘DSN=aw;DATABASE=;UID=sa;PWD=XXX’

    18:46:04 [INF][ copytable]: ODBC connection to ‘DSN=aw;DATABASE=;UID=sa;PWD=’ opened

    282030 total rows in 28 tables need to be copied:

    - [AdventureWorksDW2008R2].[dbo].[DimPromotion]: 16

    - [AdventureWorksDW2008R2].[dbo].[ProspectiveBuyer]: 2059

    - [AdventureWorksDW2008R2].[dbo].[FactFinance]: 39409

    - [AdventureWorksDW2008R2].[dbo].[DimSalesTerritory]: 11

    - [AdventureWorksDW2008R2].[dbo].[DimProductCategory]: 4

    - [AdventureWorksDW2008R2].[dbo].[FactSalesQuota]: 163

    - [AdventureWorksDW2008R2].[dbo].[DimAccount]: 99

    - [AdventureWorksDW2008R2].[dbo].[DimEmployee]: 296

    - [AdventureWorksDW2008R2].[dbo].[DimCurrency]: 105

    - [AdventureWorksDW2008R2].[dbo].[DimGeography]: 655

    - [AdventureWorksDW2008R2].[dbo].[DimProduct]: 606

    - [AdventureWorksDW2008R2].[dbo].[FactCurrencyRate]: 14264

    - [AdventureWorksDW2008R2].[dbo].[FactCallCenter]: 120

    - [AdventureWorksDW2008R2].[dbo].[DatabaseLog]: 115

    - [AdventureWorksDW2008R2].[dbo].[FactSurveyResponse]: 2727

    - [AdventureWorksDW2008R2].[dbo].[DimCustomer]: 18484

    - [AdventureWorksDW2008R2].[dbo].[DimReseller]: 701

    - [AdventureWorksDW2008R2].[dbo].[DimDate]: 1188

    - [AdventureWorksDW2008R2].[dbo].[DimScenario]: 3

    - [AdventureWorksDW2008R2].[dbo].[FactResellerSales]: 60855

    - [AdventureWorksDW2008R2].[dbo].[FactInternetSalesReason]: 64515

    - [AdventureWorksDW2008R2].[dbo].[DimOrganization]: 14

    - [AdventureWorksDW2008R2].[dbo].[AdventureWorksDWBuildVersion]: 1

    - [AdventureWorksDW2008R2].[dbo].[DimDepartmentGroup]: 7

    - [AdventureWorksDW2008R2].[dbo].[FactInternetSales]: 60398

    - [AdventureWorksDW2008R2].[dbo].[DimProductSubcategory]: 37

    - [AdventureWorksDW2008R2].[dbo].[FactAdditionalInternationalProductDescription]: 15168

    - [AdventureWorksDW2008R2].[dbo].[DimSalesReason]: 10

    Determine number of rows to copy finished

    Copy data to target RDBMS….

    Migrating data…

    wbcopytables.exe –odbc-source=DSN=aw;DATABASE=;UID=sa –target=root@localhost:3306 –progress –passwords-from-stdin –log-level=debug3 –thread-count=2 –table [AdventureWorksDW2008R2] [dbo].[DimPromotion] `dbo` `DimPromotion` [PromotionKey], [PromotionAlternateKey], [EnglishPromotionName], [SpanishPromotionName], [FrenchPromotionName], [DiscountPct], [EnglishPromotionType], [SpanishPromotionType], [FrenchPromotionType], [EnglishPromotionCategory], [SpanishPromotionCategory], [FrenchPromotionCategory], [StartDate], [EndDate], [MinQty], [MaxQty] –table [AdventureWorksDW2008R2] [dbo].[ProspectiveBuyer] `dbo` `ProspectiveBuyer` [ProspectiveBuyerKey], [ProspectAlternateKey], [FirstName], [MiddleName], [LastName], [BirthDate], [MaritalStatus], [Gender], [EmailAddress], [YearlyIncome], [TotalChildren], [NumberChildrenAtHome], [Education], [Occupation], [HouseOwnerFlag], [NumberCarsOwned], [AddressLine1], [AddressLine2], [City], [StateProvinceCode], [PostalCode], [Phone], [Salutation], [Unknown] –table [AdventureWorksDW2008R2] [dbo].[FactFinance] `dbo` `FactFinance` [FinanceKey], [DateKey], [OrganizationKey], [DepartmentGroupKey], [ScenarioKey], [AccountKey], [Amount] –table [AdventureWorksDW2008R2] [dbo].[DimSalesTerritory] `dbo` `DimSalesTerritory` [SalesTerritoryKey], [SalesTerritoryAlternateKey], [SalesTerritoryRegion], [SalesTerritoryCountry], [SalesTerritoryGroup] –table [AdventureWorksDW2008R2] [dbo].[DimProductCategory] `dbo` `DimProductCategory` [ProductCategoryKey], [ProductCategoryAlternateKey], [EnglishProductCategoryName], [SpanishProductCategoryName], [FrenchProductCategoryName] –table [AdventureWorksDW2008R2] [dbo].[FactSalesQuota] `dbo` `FactSalesQuota` [SalesQuotaKey], [EmployeeKey], [DateKey], [CalendarYear], [CalendarQuarter], [SalesAmountQuota] –table [AdventureWorksDW2008R2] [dbo].[DimAccount] `dbo` `DimAccount` [AccountKey], [ParentAccountKey], [AccountCodeAlternateKey], [ParentAccountCodeAlternateKey], [AccountDescription], [AccountType], [Operator], [CustomMembers], [ValueType], [CustomMemberOptions] –table [AdventureWorksDW2008R2] [dbo].[DimEmployee] `dbo` `DimEmployee` [EmployeeKey], [ParentEmployeeKey], [EmployeeNationalIDAlternateKey], [ParentEmployeeNationalIDAlternateKey], [SalesTerritoryKey], [FirstName], [LastName], [MiddleName], [NameStyle], [Title], [HireDate], [BirthDate], [LoginID], [EmailAddress], [Phone], [MaritalStatus], [EmergencyContactName], [EmergencyContactPhone], [SalariedFlag], [Gender], [PayFrequency], [BaseRate], [VacationHours], [SickLeaveHours], [CurrentFlag], [SalesPersonFlag], [DepartmentName], [StartDate], [EndDate], [Status] –table [AdventureWorksDW2008R2] [dbo].[DimCurrency] `dbo` `DimCurrency` [CurrencyKey], [CurrencyAlternateKey], [CurrencyName] –table [AdventureWorksDW2008R2] [dbo].[DimGeography] `dbo` `DimGeography` [GeographyKey], [City], [StateProvinceCode], [StateProvinceName], [CountryRegionCode], [EnglishCountryRegionName], [SpanishCountryRegionName], [FrenchCountryRegionName], [PostalCode], [SalesTerritoryKey] –table [AdventureWorksDW2008R2] [dbo].[DimProduct] `dbo` `DimProduct` [ProductKey], [ProductAlternateKey], [ProductSubcategoryKey], [WeightUnitMeasureCode], [SizeUnitMeasureCode], [EnglishProductName], [SpanishProductName], [FrenchProductName], [StandardCost], [FinishedGoodsFlag], [Color], [SafetyStockLevel], [ReorderPoint], [ListPrice], [Size], [SizeRange], [Weight], [DaysToManufacture], [ProductLine], [DealerPrice], [Class], [Style], [ModelName], [LargePhoto], [EnglishDescription], [FrenchDescription], [ChineseDescription], [ArabicDescription], [HebrewDescription], [ThaiDescription], [GermanDescription], [JapaneseDescription], [TurkishDescription], [StartDate], [EndDate], [Status] –table [AdventureWorksDW2008R2] [dbo].[FactCurrencyRate] `dbo` `FactCurrencyRate` [CurrencyKey], [DateKey], [AverageRate], [EndOfDayRate] –table [AdventureWorksDW2008R2] [dbo].[FactCallCenter] `dbo` `FactCallCenter` [FactCallCenterID], [DateKey], [WageType], [Shift], [LevelOneOperators], [LevelTwoOperators], [TotalOperators], [Calls], [AutomaticResponses], [Orders], [IssuesRaised], [AverageTimePerIssue], [ServiceGrade] –table [AdventureWorksDW2008R2] [dbo].[DatabaseLog] `dbo` `DatabaseLog` [DatabaseLogID], [PostTime], CAST([DatabaseUser] as VARCHAR(160)) as [DatabaseUser], CAST([Event] as VARCHAR(160)) as [Event], CAST([Schema] as VARCHAR(160)) as [Schema], CAST([Object] as VARCHAR(160)) as [Object], [TSQL], CAST([XmlEvent] as NVARCHAR(max)) as [XmlEvent] –table [AdventureWorksDW2008R2] [dbo].[FactSurveyResponse] `dbo` `FactSurveyResponse` [SurveyResponseKey], [DateKey], [CustomerKey], [ProductCategoryKey], [EnglishProductCategoryName], [ProductSubcategoryKey], [EnglishProductSubcategoryName] –table [AdventureWorksDW2008R2] [dbo].[DimCustomer] `dbo` `DimCustomer` [CustomerKey], [GeographyKey], [CustomerAlternateKey], [Title], [FirstName], [MiddleName], [LastName], [NameStyle], [BirthDate], [MaritalStatus], [Suffix], [Gender], [EmailAddress], [YearlyIncome], [TotalChildren], [NumberChildrenAtHome], [EnglishEducation], [SpanishEducation], [FrenchEducation], [EnglishOccupation], [SpanishOccupation], [FrenchOccupation], [HouseOwnerFlag], [NumberCarsOwned], [AddressLine1], [AddressLine2], [Phone], [DateFirstPurchase], [CommuteDistance] –table [AdventureWorksDW2008R2] [dbo].[DimReseller] `dbo` `DimReseller` [ResellerKey], [GeographyKey], [ResellerAlternateKey], [Phone], [BusinessType], [ResellerName], [NumberEmployees], [OrderFrequency], [OrderMonth], [FirstOrderYear], [LastOrderYear], [ProductLine], [AddressLine1], [AddressLine2], [AnnualSales], [BankName], [MinPaymentType], [MinPaymentAmount], [AnnualRevenue], [YearOpened] –table [AdventureWorksDW2008R2] [dbo].[DimDate] `dbo` `DimDate` [DateKey], [FullDateAlternateKey], [DayNumberOfWeek], [EnglishDayNameOfWeek], [SpanishDayNameOfWeek], [FrenchDayNameOfWeek], [DayNumberOfMonth], [DayNumberOfYear], [WeekNumberOfYear], [EnglishMonthName], [SpanishMonthName], [FrenchMonthName], [MonthNumberOfYear], [CalendarQuarter], [CalendarYear], [CalendarSemester], [FiscalQuarter], [FiscalYear], [FiscalSemester] –table [AdventureWorksDW2008R2] [dbo].[DimScenario] `dbo` `DimScenario` [ScenarioKey], [ScenarioName] –table [AdventureWorksDW2008R2] [dbo].[FactResellerSales] `dbo` `FactResellerSales` [ProductKey], [OrderDateKey], [DueDateKey], [ShipDateKey], [ResellerKey], [EmployeeKey], [PromotionKey], [CurrencyKey], [SalesTerritoryKey], [SalesOrderNumber], [SalesOrderLineNumber], [RevisionNumber], [OrderQuantity], [UnitPrice], [ExtendedAmount], [UnitPriceDiscountPct], [DiscountAmount], [ProductStandardCost], [TotalProductCost], [SalesAmount], [TaxAmt], [Freight], [CarrierTrackingNumber], [CustomerPONumber] –table [AdventureWorksDW2008R2] [dbo].[FactInternetSalesReason] `dbo` `FactInternetSalesReason` [SalesOrderNumber], [SalesOrderLineNumber], [SalesReasonKey] –table [AdventureWorksDW2008R2] [dbo].[DimOrganization] `dbo` `DimOrganization` [OrganizationKey], [ParentOrganizationKey], [PercentageOfOwnership], [OrganizationName], [CurrencyKey] –table [AdventureWorksDW2008R2] [dbo].[AdventureWorksDWBuildVersion] `dbo` `AdventureWorksDWBuildVersion` [DBVersion], [VersionDate] –table [AdventureWorksDW2008R2] [dbo].[DimDepartmentGroup] `dbo` `DimDepartmentGroup` [DepartmentGroupKey], [ParentDepartmentGroupKey], [DepartmentGroupName] –table [AdventureWorksDW2008R2] [dbo].[FactInternetSales] `dbo` `FactInternetSales` [ProductKey], [OrderDateKey], [DueDateKey], [ShipDateKey], [CustomerKey], [PromotionKey], [CurrencyKey], [SalesTerritoryKey], [SalesOrderNumber], [SalesOrderLineNumber], [RevisionNumber], [OrderQuantity], [UnitPrice], [ExtendedAmount], [UnitPriceDiscountPct], [DiscountAmount], [ProductStandardCost], [TotalProductCost], [SalesAmount], [TaxAmt], [Freight], [CarrierTrackingNumber], [CustomerPONumber] –table [AdventureWorksDW2008R2] [dbo].[DimProductSubcategory] `dbo` `DimProductSubcategory` [ProductSubcategoryKey], [ProductSubcategoryAlternateKey], [EnglishProductSubcategoryName], [SpanishProductSubcategoryName], [FrenchProductSubcategoryName], [ProductCategoryKey] –table [AdventureWorksDW2008R2] [dbo].[FactAdditionalInternationalProductDescription] `dbo` `FactAdditionalInternationalProductDescription` [ProductKey], [CultureName], [ProductDescription] –table [AdventureWorksDW2008R2] [dbo].[DimSalesReason] `dbo` `DimSalesReason` [SalesReasonKey], [SalesReasonAlternateKey], [SalesReasonName], [SalesReasonReasonType]

    Copy helper has finished

    Data copy results:

    - `dbo`.`DimPromotion` has FAILED (0 of 16 rows copied)

    - `dbo`.`ProspectiveBuyer` has FAILED (0 of 2059 rows copied)

    - `dbo`.`FactFinance` has FAILED (0 of 39409 rows copied)

    - `dbo`.`DimSalesTerritory` has FAILED (0 of 11 rows copied)

    - `dbo`.`DimProductCategory` has FAILED (0 of 4 rows copied)

    - `dbo`.`FactSalesQuota` has FAILED (0 of 163 rows copied)

    - `dbo`.`DimAccount` has FAILED (0 of 99 rows copied)

    - `dbo`.`DimEmployee` has FAILED (0 of 296 rows copied)

    - `dbo`.`DimCurrency` has FAILED (0 of 105 rows copied)

    - `dbo`.`DimGeography` has FAILED (0 of 655 rows copied)

    - `dbo`.`DimProduct` has FAILED (0 of 606 rows copied)

    - `dbo`.`FactCurrencyRate` has FAILED (0 of 14264 rows copied)

    - `dbo`.`FactCallCenter` has FAILED (0 of 120 rows copied)

    - `dbo`.`DatabaseLog` has FAILED (0 of 115 rows copied)

    - `dbo`.`FactSurveyResponse` has FAILED (0 of 2727 rows copied)

    - `dbo`.`DimCustomer` has FAILED (0 of 18484 rows copied)

    - `dbo`.`DimReseller` has FAILED (0 of 701 rows copied)

    - `dbo`.`DimDate` has FAILED (0 of 1188 rows copied)

    - `dbo`.`DimScenario` has FAILED (0 of 3 rows copied)

    - `dbo`.`FactResellerSales` has FAILED (0 of 60855 rows copied)

    - `dbo`.`FactInternetSalesReason` has FAILED (0 of 64515 rows copied)

    - `dbo`.`DimOrganization` has FAILED (0 of 14 rows copied)

    - `dbo`.`AdventureWorksDWBuildVersion` has FAILED (0 of 1 rows copied)

    - `dbo`.`DimDepartmentGroup` has FAILED (0 of 7 rows copied)

    - `dbo`.`FactInternetSales` has FAILED (0 of 60398 rows copied)

    - `dbo`.`DimProductSubcategory` has FAILED (0 of 37 rows copied)

    - `dbo`.`FactAdditionalInternationalProductDescription` has FAILED (0 of 15168 rows copied)

    - `dbo`.`DimSalesReason` has FAILED (0 of 10 rows copied)

    0 tables of 28 were fully copied

    Copy data to target RDBMS finished

    Finished performing tasks.

    By jim on Sep 4, 2012

  6. Hi,

    I’m getting the same result as Jim above trying to migrate a phpbb database. Anyone have an update on this?

    Cheers,
    Gregor

    By Gregor on Sep 24, 2012

  7. I have tried to Migrating Sql Server dbase to MySql. All of the tables are created. I do not see any errors, but no data is transferred. At the end of the Migration Report, I see the section with nothing following:

    II. Data Copy

    By Larry on Sep 28, 2012

  8. Great job Sergio,

    It worked perfectly so now I won’t freak out if one day I’m asked to do a migration.

    Thanks!

    By Luis Angel Lopez on Dec 14, 2012

  9. Wonderful site you have here but I was curious if you knew
    of any user discussion forums that cover the same topics talked about in this article?
    I’d really love to be a part of online community where I can get feedback from other experienced people that share the same interest. If you have any recommendations, please let me know. Kudos!

    By Sign In on Feb 7, 2013

  10. I created the batch file to run it later in the Workbench wizard, but it doesn’t create the structure in dbo. I enter the respective passwords, changed the wbcopytables directory, in the .cmd file; I don’t get any errors after running the .cmd file. I run the cmd file in the command prompt. Any help would be appreciated!

    By Steve on Feb 11, 2013

  11. I connect fine, but no schemas are listed to select and convert.. what gives. No matter what I do. And when I try to specify my database catalog, I get an error saying it’s not there, I know it is.

    By ajm on Feb 16, 2013

  12. Im having the same problem as ajm…

    By Felipe on Apr 23, 2013

  13. Can we migrate stored procdeures?

    By Brian Besthorne on Aug 26, 2013

  14. No, there is no support for migrating code objects.

    By akojima on Sep 3, 2013

  15. tables are migrated but function and procedures are not shown in migrated MySQL data base but in the log tables and objects are migrated successful. where is the migrated objects are stored.

    By shruti on Oct 14, 2013

  16. There is no support for translating and migrating stored procedures and functions in MySQL Workbench.

    By akojima on Nov 7, 2013

  17. Thank You sir…

    now i need to migrate table from MSSQL server on other PC to MySQL with this Migration Wizard.

    hints:
    Source-SQL:
    hostname:192.160.1.36
    Port:1433
    Name:SQLEXPRESS

    Target:
    MySQL
    hostname:loalhost

    Please help me urgent..!

    By Gnanajeyam on Feb 25, 2014

  18. Difference in view concept and procedure
    on mysql and mssql

    By Mahendran on May 12, 2014

  19. I am not able to migrate the stored procedures from SQL Sever to MYSQL

    By sheethal on Jun 11, 2014

  20. Stored procedure migration is not supported.

    By akojima on Jun 27, 2014

  1. 5 Trackback(s)

  2. Sep 13, 2012: Problems migrating data from SQL Server to MySQL | MacLochlainns Weblog
  3. Dec 7, 2012: MySQL Workbench 5.2.44 » Blog Archive » How-To: Migrate PostgreSQL databases to MySQL using the MySQL Workbench Migration Wizard
  4. Feb 18, 2013: MySQL Workbench 5.2.47 » Blog Archive » Installing a driver for Microsoft SQL Server and Sybase ASE in Linux and Mac
  5. Oct 21, 2013: MySQL Workbench Migration ToolCopyQuery CopyQuery | Question & Answer Tool for your Technical Queries,CopyQuery, ejjuit, query, copyquery, copyquery.com, android doubt, ios question, sql query, sqlite query, nodejsquery, dns query, update query, inser
  6. Nov 17, 2013: MySQL Workbench: Cannot export a database | Technology & Programming

Post a Comment


five + 9 =