失眠网 > ssma迁移助手_如何使用SQL Server迁移助手（SSMA）和SSIS将MySQL表迁移到SQL Server

ssma迁移助手_如何使用SQL Server迁移助手（SSMA）和SSIS将MySQL表迁移到SQL Server

时间：2024-02-20 15:43:13

ssma迁移助手

介绍 (Introduction)

In this article, we will demonstrate how to migrate MySQL tables to SQL Server using the SQL Server Migration Assistant (SSMA) and SSIS. We will give a brief introduction to MySQL and create a table. If you do not have any experience in MySQL, this tutorial will teach you your first steps. After that, we will migrate the created table and existing tables. We will also mention common problems when we migrate tables.

在本文中，我们将演示如何使用SQL Server迁移助手（SSMA）和SSIS将MySQL表迁移到SQL Server。我们将简要介绍MySQL并创建一个表。如果您没有MySQL的经验，那么本教程将教您第一步。之后，我们将迁移创建的表和现有表。迁移表时，我们还将提到常见问题。

Finally, we will migrate data from MySQL to SQL Server using SSIS

最后，我们将使用SSIS将数据从MySQL迁移到SQL Server

要求 (Requirements)

SQL Server (or earlier versions). SQL Server （或更早版本）。 MySQL 5.7 installed MySQL 5.7 SQL Server Management Studio (SSMS) SQL Server管理Studio （SSMS） SSMA 7.3 for MySQL 适用于MySQL的SSMA 7.3 SQL Server Agent Service is started 已启动SQL Server代理服务 SSDT installed SSDT

For SSMA, you need to have the .NET framework installed. In Windows Server, go to Server Manager, and select the Add roles and features:

对于SSMA，您需要安装.NET框架。在Windows Server中，转到“服务器管理器”，然后选择“添加角色和功能”：

Run the wizard until the Features section and make sure that the .NET Framework Features are installed:

运行向导，直到“功能”部分，并确保已安装.NET Framework功能：

入门 (Getting started)

Once MySQL is installed, we will run MySQL Workbench which comes with the installer. This is like the SQL Server Management Studio in MySQL:

安装完MySQL后，我们将运行安装程序随附MySQL Workbench。这就像MySQL中SQL Server Management Studio：

Click on Local Instance MySQL57:

单击本地实例MySQL57：

Specify your password. This is created when you install MySQL:

指定您的密码。这是在安装MySQL时创建的：

Double click on World database that includes sample data, tables for our tests, to convert it to the default database. It is. Press the SQL icon to create a new query and add the following SQL code to create a new table named Contacts.

双击包含示例数据和测试表的World数据库，将其转换为默认数据库。它是。按下SQL图标创建一个新查询，并添加以下SQL代码创建一个名为Contacts的新表。

create table contacts(id int,fname char(50),lname char(50));

Finally, press the execute icon.

最后，按执行图标。

Now, insert data into the new contacts table and execute the code:

现在，将数据插入新的联系人表并执行代码：

insert into contacts values (1,'John','Smith');insert into contacts values (2,'James','Dean');

If you refresh the world schema, you will be able to see the new contacts table created:

如果刷新世界架构，您将能够看到创建的新联系人表：

We will create a new database named world in SQL Server. Open the SSMS and right click on databases and select New Database:

我们将在SQL Server中创建一个名为world的新数据库。打开SSMS并右键单击数据库，然后选择“新建数据库”：

Specify a name for the database:

指定数据库的名称：

If you refresh the SSMS, you will be able to see the new database created:

如果刷新SSMS，您将能够看到创建的新数据库：

适用于MySQL的SSMA (SSMA for MySQL)

We have a database in MySQL and we will try to migrate 3 existing tables and the new table named contacts just created in MySQL to SQL Server using the SQL Server Migration Assistant for MySQL, the software that will be used to migrate data.

我们在MySQL中有一个数据库，我们将尝试使用MySQLSQL Server迁移助手将刚在MySQL中创建的3个现有表和名为contact的新表迁移到SQL Server，该软件将用于迁移数据。

Open the SSMA and select the option New Project:

打开SSMA并选择选项New Project：

You can migrate from MySQL to SQL server , , , and you can even migrate to Azure. This time we will migrate to SQL Server :

您可以从MySQL迁移到SQL Server 、、、，甚至可以迁移到Azure。这次我们将迁移到SQL Server ：

We will connect to MySQL:

我们将连接到MySQL：

Specify the server name, user name, password, port and press connect:

指定服务器名称，用户名，密码，端口，然后按连接：

If everything is OK, you will have access to the Databases:

如果一切正常，您将可以访问数据库：

Connect to SQL Server to the world database just created:

将SQL Server连接到刚创建的世界数据库：

If everything is OK, you will be able to see the SQL Server databases:

如果一切正常，您将能够查看SQL Server数据库：

In MySql Metadata Explorer pane, select the tables that you want to export and press the convert schema icon:

在“ MySql Metadata Explorer”窗格中，选择要导出的表，然后按转换模式图标：

Notice that country and countrylanguage tables have a problem:

请注意，国家和国家/地区语言表存在问题：

If you check the output in SSMA, you will notice that some unhandled exceptions occurred:

如果在SSMA中检查输出，您会注意到发生了一些未处理的异常：

If we take a look to the table country, we will notice that there is a column named continent with the Data Type ENUM. Enum is a string object and is not compatible with SQL Server for migration:

如果我们查看表格国家/地区，则会注意到有一个名为continent的列，其数据类型为ENUM。枚举是一个字符串对象，与SQL Server不兼容以进行迁移：

We have a similar problem with the table countrylanguages:

表格国家/地区语言存在类似的问题：

We can solve this problem using SSIS. We will explain later how to do it.

我们可以使用SSIS解决此问题。稍后我们将解释如何做。

We will migrate just the City and Contacts tables using SSMA. To do that, in SQL Server Metadata, select the Synchronize with database option:

我们将使用SSMA仅迁移City和Contacts表。为此，请在SQL Server元数据中，选择“与数据库同步”选项：

A new windows will be displayed. Press OK:

将显示一个新窗口。按确定：

Finally, select the tables in MySQL metadata, and press the Migrate Data icon:

最后，选择MySQL元数据中的表，然后按Migrate Data图标：

If everything is OK, it will show the total rows and the number of migrated rows. In this example, only the tables city and contacts were successfully migrated. The other 2 tables had some problems with incompatible columns:

如果一切正常，它将显示总行数和已迁移的行数。在此的示例仅成功迁移表城市和联系人。其他2个表的列不兼容存在一些问题：

To verify the results, you can open the SSMS, and do a select in the world database to verify that the city table was successfully imported:

要验证结果，可以打开SSMS，然后在world数据库中进行选择以验证是否成功导入了城市表：

You can also verify that the table contacts was imported successfully:

您还可以验证表联系人是否已成功导入：

使用SSIS将数据从MySQL导入SQL Server (Importing data from MySQL to SQL Server using SSIS)

SSMA had some problems to import data from the country and countrylanguage tables. This time we will try to import the data of these 2 missing tables using SSIS.

SSMA在从国家和国家语言表导入数据时遇到一些问题。这次，我们将尝试使用SSIS导入这两个缺失表的数据。

In SSDT, go toFile>New Projectand create a new SQL Server Integration Project.

在SSDT中，转到“文件”>“新建项目”，然后创建一个新SQL Server集成项目。

In the SSIS project, drag and drop the Data Flow Task to the design pane:

在SSIS项目中，将“数据流任务”拖放到设计窗格中：

Double click the Data Flow task and in the Data Flow pane, drag and drop the ADO NET Source. This Source will be used to get the MySQL information:

双击“数据流”任务，然后在“数据流”窗格中，拖放ADO NET Source。此源将用于获取MySQL信息：

Press New to create a new connection:

按“新建”以创建新的连接：

In the Configure Connection Manager, press the New button:

在“配置连接管理器”中，按“新建”按钮：

In Provider, select .Net Providers\MySQL Data Provider. This Provider comes with the Database Installer. You can also download the provider using the following link:

在提供程序中，选择.Net Providers \ MySQL Data Provider。该提供程序与数据库安装程序一起提供。您也可以使用以下链接下载提供程序：

Download Connector/Net 下载连接器/网络

In server name, specify the name of the MySQL Server. In this case, the database is in the localhost. In addition, you can use a database user name and password. The root user and password are created when you install MySQL, but you can create other users. The option Save my password must be checked to create the connection. The combobox is used to connect to the database. In this example, the database is world:

在服务器名称中，指定MySQL服务器的名称。在这种情况下，数据库位于本地主机中。另外，您可以使用数据库用户名和密码。安装MySQL时会创建root用户和密码，但您可以创建其他用户。必须选中选项保存我的密码来创建连接。组合框用于连接到数据库。在此示例中，数据库为世界：

In Data access mode, select Table or view. In name of the table or view, select “world”.”country” and press the Preview button:

在数据访问模式下，选择表或视图。在表或视图的名称中，选择“世界”。“国家”，然后按“预览”按钮：

You will receive an error similar to the following:

您将收到与以下类似的错误：

The error is related to the column Continent because the data type is enum and it is a data type that is not compatible with SQL Server. To solve this problem, we will run a query that excludes the Continent column. In Data access mode, selectSQL commandin the provider.

该错误与列Continent有关，因为数据类型为enum，并且它是与SQL Server不兼容的数据类型。为了解决这个问题，我们将运行一个查询，其中不包括“大陆”列。在数据访问模式下，在提供程序中选择SQL命令。

In SQL command text write a query excluding the Continent column and press the Preview button:

在SQL命令文本中，编写一个查询（不包括Continent列），然后按Preview按钮：

When you press Preview, you can now see the data. Now you can export it to SQL Server:

当您按预览时，现在可以看到数据。现在，您可以将其导出到SQL Server：

Drag and drop the SQL Server Destination task to the design pane and join the ADO NET Source to SQL Server Destination:

将“ SQL Server目标”任务拖放到设计窗格中，然后将ADO NET Source连接到SQL Server目标：

Double click SQL Destination and press the New button to create a new connection:

双击“ SQL Destination”，然后按“新建”按钮以创建新连接：

In provider, select the Native OLE DB\SQL Server Native Client. In Server name, specify the SQL Server name. In select or enter a database, select the database where you want to import the table:

在提供程序中，选择“本机OLE DB \ SQL Server本机客户端”。在服务器名称中，指定SQL Server名称。在选择或输入数据库中，选择要在其中导入表的数据库：

Press the New button to create a new table:

按“新建”按钮创建一个新表：

We will rename the table to country. You can also modify the column names here:

我们将表重命名为国家。您还可以在此处修改列名称：

Click on the Mappings page to map the source and destination columns:

单击“映射”页面以映射源和目标列：

If the source and destination columns have the same names, the matching will be created automatically. Otherwise, you will need to match the columns manually:

如果源列和目标列名称相同，则将自动创建匹配项。否则，您将需要手动匹配各列：

Run the start button to start importing data:

运行开始按钮以开始导入数据：

If everything is OK, you can verify the data with SSMS. The new table named Country will be created and the data will be populated successfully.

如果一切正常，则可以使用SSMS验证数据。将创建名为Country的新表，并将成功填充数据。

结论 (Conclusions)

SSMA is a great tool to import data from different data sources. In this example, we used SSMA for MySQL to export MySQL data to SQL Server, but SSMA works for Oracle, DB2, Access and Sybase as well.

SSMA是从不同数据源导入数据的绝佳工具。在此示例中，我们使用SSMA for MySQL将MySQL数据导出到SQL Server，但是SSMA也适用于Oracle，DB2，Access和Sybase。

However, as you could see, there are some limitations and there are some scenarios where it is not possible to migrate all the tables. In such cases, you can use SSIS or other tools of your preference to complete the migration of the non-compatible tables.

但是，如您所见，存在一些限制，并且在某些情况下无法迁移所有表。在这种情况下，您可以使用SSIS或您喜欢的其他工具来完成不兼容表的迁移。

SSMA is an intuitive tool to export multiple tables from MySQL to SQL Server and it is the first choice to export. If it fails to export some tables, you can use other tools like SSIS.

SSMA是将多个表从MySQL导出到SQL Server的直观工具，并且是导出的首选。如果无法导出某些表，则可以使用SSIS等其他工具。

参考资料 (References)

For more information, refer to these links:

有关更多信息，请参考以下链接：

SQL Server Migration Assistant SQL Server迁移助手 Getting Started with MySQL MySQL入门 Tutorial: SQL Server Management Studio 教程：SQL Server Management Studio