ssma5.3_使用SSMA v7.1 for Oracle迁移到SQL Server

ssma5.3_使用SSMA v7.1 for Oracle迁移到SQL Server–>

ssma5.3

介绍 (Introduction)

This is a second article that is a continuation of the Microsoft SQL Server Migration Assistant (SSMA) v7.1 for Oracle. In my first article I wrote an overview about the tool and how to install it. In this second article I will write about how to use it to migrate data and objects from an Oracle database to a SQL Server database.

这是第二篇文章，是Oracle的Microsoft SQL Server迁移助手（SSMA）v7.1的继续 。 在我的第一篇文章中，我写了关于该工具及其安装方法的概述。 在第二篇文章中，我将介绍如何使用它将数据和对象从Oracle数据库迁移到SQL Server数据库。

As described in my first article, SSMA v7.1 for Oracle consists of a GUI client application, which must be installed on the computer from which you will perform the migration steps and there is also an extension pack to be installed on the target SQL Server machine. To start working with this tool, connect to the machine that has the GUI client application installed and click in the icon on the desktop. If for some reason you don’t have it in your desktop you can find the application file in the location you installed it (default path is C:\Program Files (x86)\Microsoft SQL Server Migration Assistant for Oracle\bin) where should be the 64bit (SSMAforOracle.exe) and 32bit (SSMAforOracle32.exe ) versions.

如我的第一篇文章所述，Oracle的SSMA v7.1由一个GUI客户端应用程序组成，该应用程序必须安装在要从中执行迁移步骤的计算机上，并且在目标SQL Server上还要安装一个扩展包。机。 要开始使用此工具，请连接到安装了GUI客户端应用程序的计算机，然后单击桌面上的图标。 如果由于某种原因您的桌面上没有该文件，则可以在安装文件的位置找到该应用程序文件（默认路径为C：\ Program Files（x86）\ Microsoft SQL Server Migration Assistant for Oracle \ bin ）。是64位（SSMAforOracle.exe）和32位（SSMAforOracle32.exe）版本。

NOTE: The SQL Server Agent service must be running in the target SQL Server instance to create and run migration jobs.

注意：SQL Server代理服务必须在目标SQL Server实例中运行才能创建和运行迁移作业。

所需的Oracle权限 (Required Oracle permissions)

There is no need for a user with high privileges as SYSDBA to execute the migrations. As usual and for security reasons, always uses the minimum required privileges and for SSMA v7.1 for Oracle the account to connect to the Oracle database must have CONNECT permissions to obtain metadata from schemas owned by the user. In the case the user needs to migrate from objects in other schemas the account must have the following permissions:

不需要具有SYSDBA特权的用户来执行迁移。 与往常一样，出于安全原因，请始终使用最低要求的特权，对于Oracle的SSMA v7.1，连接到Oracle数据库的帐户必须具有CONNECT权限才能从用户拥有的架构中获取元数据。 如果用户需要从其他架构中的对象迁移，则该帐户必须具有以下权限：

• CREATE ANY PROCEDURE

  创建任何程序

• EXECUTE ANY PROCEDURE

  执行任何程序

• SELECT ANY TABLE

  选择任何表格

• SELECT ANY SEQUENCE

  选择任何顺序

• CREATE ANY TYPE

  创建任何类型

• CREATE ANY TRIGGER

  创建任何触发器

• SELECT ANY DICTIONARY

  选择任何词典

环境 (The environment)

When opening the GUI for the first time the default layout will be presented:

首次打开GUI时，将显示默认布局：

At the top it has the usual menu options and toolbar icons, followed by the two Metadata Explorer parts. In the first part it will be presented the metadata information from the source (an Oracle database) and in the second part it will be presented the metadata information from the target (a SQL Server instance). This Layout can be altered by using the menu View / Layouts:

它的顶部具有常用的菜单选项和工具栏图标，其后是两个Metadata Explorer部分。 在第一部分中，将显示来自源（Oracle数据库）的元数据信息，在第二部分中，将显示来自目标（SQL Server实例）的元数据信息。 可以使用菜单查看/布局来更改此布局 ：

迁移过程 (Migration process)

新项目 (New project)

A new project needs to be created to start a migration process. A new project can be created using the menu File/New Project… option or simple by clicking in the New Project… icon in the toolbar.

需要创建一个新项目以开始迁移过程。 可以使用菜单文件/新建项目…来创建一个新项目，也可以通过单击工具栏中的新建项目…来简单地创建一个新项目。

Provide a Name to the project, a path for the Location of the project files (source database metadata information, configurations and mappings are saved in those files) and chose the target database version from the supported versions in the Migrate To combo box:

提供项目的名称 ，项目文件位置的路径（源数据库元数据信息，配置和映射保存在这些文件中），并在“ 迁移到”组合框中从受支持的版本中选择目标数据库版本：

项目设定 (Project settings)

After the creation of the project you are able to review and change the settings for the current project in the menu Tools / Project Settings (for specifying settings for all SSMA projects, select Default Project Settings instead):

创建项目后，您可以查看和更改设置菜单中的工具当前项目/项目设置 （对所有项目SSMA指定设置，选择默认项目设置代替 ）：

Clicking in the (Default) Project Settings you will see on the top left side you can choose between 3 predefined Modes (Default, Full and Optimistic) and a Custom mode where you can configure it as you will. For the sake of this article size and to keep things simple, in this article I will proceed with the Default mode but you can play around to see how some options change with the different modes (they will be in bold so you can see better those changes):

单击（默认）项目设置，您将在左上角看到，您可以在3种预定义模式 （默认，完全和乐观）和自定义模式之间进行选择，在该模式下可以对其进行配置。 为了这篇文章的大小并且为了简化起见，在本文中，我将继续使用Default模式，但是您可以四处看看如何在不同的模式下更改某些选项（它们将以粗体显示，因此您可以更好地看到那些变化）：

On the top right side there is a combo box where you will need to provide the Migration Target Version of the SQL Server. I am using a SQL Server 2014 to write this article so is the one that I will use:

在右上角有一个组合框，您需要在其中提供SQL Server的迁移目标版本 。 我正在使用SQL Server 2014撰写本文，因此将使用：

There are few settings that worth to be explained in the Migration pane since they are important to understand the rest of this article:

在“ 迁移”窗格中，几乎没有什么值得解释的设置，因为它们对于理解本文的其余部分很重要：

连接到数据库 (Connect to databases)

After creating a project the Connect to Oracle and Connect to SQL Server will be available.

创建项目后， 将可以使用Connect to Oracle和Connect to SQL Server 。

From the menu:

从菜单中：

And from the toolbar:

从工具栏：

In case of errors during the connections, check the Output pane for respective error messages that can lead you to solve the issue. You can also check the log (default location is C:\Users\UserID\AppData\Roaming\Microsoft SQL Server Migration Assistant\Oracle\log\) for more detailed information about the error.

如果在连接过程中出现错误，请检查“输出”窗格中是否有相应的错误消息，这些消息可以引导您解决问题。 您也可以检查日志（默认位置为C：\ Users \ UserID \ AppData \ Roaming \ Microsoft SQL Server迁移助手\ Oracle \ log \ ），以获取有关该错误的更多详细信息。

连接到Oracle (Connect to Oracle)

To connect to the desired Oracle database you will need to provide the necessary information for SSMA can establish the connection to the source database.

要连接到所需的Oracle数据库，您将需要提供必要的信息，以便SSMA可以建立与源数据库的连接。

The first step is to click on the Connect to Oracle option to provide the necessary information for the connection to the source database.

第一步是单击“ 连接到Oracle”选项，以提供必要的信息以连接到源数据库。

Choose the Provider depending on the client that you have installed:

根据您安装的客户端选择提供程序：

And there are three modes available:

共有三种模式：

In the Standard mode provide the Serve name, port (by default is presented the port 1521), an Oracle SID and the user credentials (name and password):

在标准模式下，提供服务名称，端口（默认情况下为端口1521），Oracle SID和用户凭证（名称和密码）：

In the TNSNAME mode provide the connection identifier previously added in the TNSNAMES.ora file and the user credentials (name and password):

在TNSNAME模式下，提供先前在TNSNAMES.ora文件中添加的连接标识符和用户凭证（名称和密码）：

The Connection String mode is not recommended because it leaves the password visible. You can see a warning stating that in this option:

不建议使用“连接字符串”模式，因为它会使密码可见。 您可以在此选项中看到警告说明：

The connection to the source database server stays active until the project is closed. When reopening the project a reconnect to the source database is also needed. All the connection information is stored and you will only need to provide the password when reconnecting. The exception is for the Connection String mode where the password is stored together with the Connection String.

与源数据库服务器的连接将保持活动状态，直到关闭项目为止。 重新打开项目时，还需要重新连接到源数据库。 所有的连接信息都已存储，您只需在重新连接时提供密码。 连接字符串模式例外，其中密码与连接字符串一起存储。

Working offline is also possible but a connection to the source database will be requested when you need to refresh the metadata (can only be done manually since is not automatically refreshed) or load the database objects into SQL Server and migrate the data.

也可以脱机工作，但是当您需要刷新元数据（只能手动完成，因为不会自动刷新）或将数据库对象加载到SQL Server并迁移数据时，将请求连接到源数据库。

After a successful connection, Oracle schemas it will appear in Oracle Metadata Explorer.

成功连接后，它将在Oracle Metadata Explorer中显示它的Oracle模式。

In the top left pane, called Oracle Metadata Explorer, you will see the Oracle database and the available schemas and synonyms if any exists. Drilling down to an object level you will see in the top right pane the respective object properties. It varies depending on the object type.

在称为Oracle Metadata Explorer的左上方窗格中，您将看到Oracle数据库以及可用的模式和同义词（如果存在）。 向下钻取到对象级别，您将在右上窗格中看到相应的对象属性。 它随对象类型的不同而不同。

Right-clicking on objects in Oracle Metadata Explorer, a context menu will appear with the available tasks that can be performed for the respective object. Those tasks can also be performed by using the toolbars and menus.

右键单击Oracle Metadata Explorer中的对象，将显示一个上下文菜单，其中包含可以对相应对象执行的可用任务。 这些任务也可以通过使用工具栏和菜单来执行。

连接到SQL Server (Connect to SQL Server)

After connecting to the source Oracle database you should now connect to the target SQL Server instance by clicking on the Connect to SQL Server option and provide the necessary information for the connection to the target database.

连接到源Oracle数据库之后，现在应该通过单击“ 连接到SQL Server”选项连接到目标SQL Server实例，并提供连接到目标数据库的必要信息。

Provide the Server name for the SQL Server instance and the Server port if it is not using the default port 1433. Indicate the Database that will receive the migrated objects and data and configure the Authentication mode. If you are using SQL Server Authentication mode you’ll need to provide the SQL Server user and password that will be used for the connection. Be sure that the user that is provided has sysadmin server role in the target SQL Server instance:

提供SQL Server实例的服务器名称和服务器端口（如果未使用默认端口1433）。指示将接收迁移的对象和数据的数据库 ，并配置身份验证模式。 如果使用SQL Server身份验证模式，则需要提供将用于连接SQL Server用户和密码。 确保提供的用户在目标SQL Server实例中具有sysadmin服务器角色：

If you are not running the SSMA tool locally then for security reasons keep the Encrypt Connection and Trust Server Certificate options checked.

如果您不是在本地运行SSMA工具，则出于安全原因，请选中“ 加密连接和信任服务器证书”选项。

After a successful connection your SSMA screen should look similar to the next one:

成功连接后，您的SSMA屏幕应类似于下一个屏幕：

迁移对象和数据 (Migrate objects and data)

In the Oracle Metadata Explorer drill drown until the Schemas level and chose in the Schema that you want to migrate the objects to be migrated. When migrating all objects, click at the Schema level so all the objects belonging that Schema will be automatically selected. It will also present the number of selected objects from the total number of objects that exists in each object type:

在Oracle Metadata Explorer中钻进淹没，直到架构级别，然后在架构中选择要迁移的对象。 迁移所有对象时，请在架构级别单击，以便将自动选择属于该架构的所有对象。 还将从每种对象类型中存在的对象总数中显示所选对象的数量：

After connecting to Oracle and SQL Server databases, move the focus to the Oracle Schema that you want to migrate to have the following options enabled in the Toolbar (same options can be found enabled in the menu Tools):

连接到Oracle和SQL Server数据库后，将焦点移到要迁移的Oracle模式上，以在工具栏中启用以下选项（可以在“工具”菜单中找到相同的选项）：

By default a target schema with the same name as the source schema will be created in the target SQL Server database. This can be changed by providing the new schema name. To do that select the source schema name in the Oracle Metadata Explorer so the scope in the respective Details pane will be the schema:

默认情况下，将在目标SQL Server数据库中创建与源模式同名的目标模式。 可以通过提供新的架构名称来更改。 为此，请在Oracle Metadata Explorer中选择源模式名称，这样相应的Details窗格中的作用域将成为模式：

Click on Modify button to provide the new target schema name (you can also use this option to modify the target database):

单击修改按钮以提供新的目标模式名称（您也可以使用此选项来修改目标数据库）：

NOTE: In this article we are going to continue with the default names.

注意：在本文中，我们将继续使用默认名称。

建立报告 (Create a report)

Use this option before starting with the migration process. This will return a report of errors and warnings identifying potential migration issues, so you can mitigate them first before proceeding with the migration.

在开始迁移过程之前，请使用此选项。 这将返回错误和警告报告，指出潜在的迁移问题，因此您可以在进行迁移之前先缓解它们。

In case of errors it usually means that a particular object can’t be migrated as is and the object should be rewritten to be convertible for SQL Server. Other option is to remove the object(s) from the migration list if it’s acceptable that it won’t be needed or it will be replaced in the SQL Server migrated database with some other native solution.

如果发生错误，通常意味着不能按原样迁移特定对象，并且应将对象重写为可转换为SQL Server。 另一个选择是，如果可以不需要该对象，或者将其用其他本机解决方案替换在SQL Server迁移的数据库中，则从迁移列表中删除该对象。

In case of warnings it usually means that the object can be migrated but will lose some particular feature that doesn’t exist in SQL Server. You can accept that outcome or alternatively rewrite the object creation code to be 100% convertible for SQL Server.

在出现警告的情况下，通常意味着可以迁移对象，但是将丢失SQL Server中不存在的某些特定功能。 您可以接受该结果，也可以重写对象创建代码以使其可以100％转换为SQL Server。

In my case I had no errors and the warnings presented in the next figure:

在我的情况下，我没有错误，警告如下图所示：

The report is generated in an HTML format and can be read by any browser. Besides presenting the errors, warnings and other informational data it also allows you to navigate through each error and warning presenting valuable information to mitigate each one. And it also presents an estimated manual conversion time for each issue’s mitigation as well the estimated total manual of time needed to mitigate all issues.

该报告以HTML格式生成，并且可以由任何浏览器读取。 除了显示错误，警告和其他信息数据之外，它还使您可以浏览每个错误并提供有价值的信息以减轻每个错误。 并且它还提供了缓解每个问题的估计手动转换时间，以及缓解所有问题所需的估计总时间。

转换架构 (Convert schema)

After getting the assessment report from the previous step you can now start to convert objects from Oracle Schema to the respective SQL Server objects. This option will not create or load the information into the SQL Server instance but load only the information into the SSMA metadata so you can view the objects and their properties in the SQL Server Metadata Explorer pane.

从上一步获得评估报告后，您现在可以开始将对象从Oracle架构转换为相应SQL Server对象。 此选项将不会创建信息或将信息加载到SQL Server实例中，而只会将信息加载到SSMA元数据中，因此您可以在“ SQL Server元数据资源管理器”窗格中查看对象及其属性。

Similar to the Create Report option, Schema conversion prints output messages to the Output pane and error messages to the Error List pane. Use the output and error information to determine whether you have to modify your Oracle databases or your conversion process to obtain the desired conversion results.

与“创建报告”选项类似，架构转换将输出消息打印到“输出”窗格，将错误消息打印到“错误列表”窗格。 使用输出和错误信息来确定是否必须修改Oracle数据库或转换过程才能获得所需的转换结果。

In my case there is an Oracle View created WITH READ ONLY option and I just need to remove the respective line by editing the View definition.

在我的情况下，有一个使用WITH READ ONLY选项创建的Oracle View，我只需要通过编辑View定义来删除相应的行。

NOTE: Altering the code in SSMA does not really alter the code in the Oracle database. These changes only affect the local metadata that will be used for the migration.

注意：更改SSMA中的代码并不会真正更改Oracle数据库中的代码。 这些更改仅影响将用于迁移的本地元数据。

Selecting an object in the Oracle object details pane will present in the converted object code in the SQL Server object details pane where you can find comments about the errors and warnings so you can make the necessary changes to allow the migration running without errors.

在Oracle对象详细信息窗格中选择一个对象将显示在SQL Server对象详细信息窗格中转换后的对象代码中，您可以在其中找到有关错误和警告的注释，以便可以进行必要的更改以使迁移无错误地运行。

I still have 40 conversion warnings about a conversion issue. I could just accept any possible data loss but I will opt for changing the target data type by editing the Type Mapping in the Project properties for the Columns Type Mapping so this change will affect all objects in the current migration project:

关于转换问题，我仍然有40条转换警告。 我可以接受任何可能的数据丢失，但是我将选择通过在Columns Type Mapping的Project属性中编辑Type Mapping来更改目标数据类型，以便此更改将影响当前迁移项目中的所有对象：

By default a number data type in Oracle is mapped to a float[53] in SQL Server. Changing this to numeric and run the convert schema again by overwriting the existing object definitions it should clear my warning list.

默认情况下，Oracle中的数字数据类型映射到SQL Server中的float [53] 。 将其更改为数字，然后通过覆盖现有对象定义再次运行转换模式，它将清除我的警告列表。

This was a quick mitigation example but do not expect this to be easy for every migration. Database with more objects may lead to more complex mitigations.

这是一个快速的缓解示例，但是不要期望每个迁移都如此简单。 具有更多对象的数据库可能会导致更复杂的缓解措施。

迁移资料 (Migrate data)

Now that you have all objects of the Oracle schema converted into SSMA metadata and the issues mitigated, you are able to start the migration of the data into the SQL Server database. You can confirm in the SQL Server Metadata Explorer that a Schema has been created to receive the data from Oracle:

现在，您已经将Oracle架构的所有对象都转换为SSMA元数据，并且已解决了问题，现在可以开始将数据迁移到SQL Server数据库中了。 您可以在SQL Server元数据资源管理器中确认已创建架构以从Oracle接收数据：

There is also a new schema called ssma_oracle that is used to support the migration and should be deleted by SSMA as soon the migration ends.

还有一个名为ssma_oracle的新模式，用于支持迁移，并且迁移结束后应由SSMA删除。

By clicking in the Migrate Data option you will be asked to connect again to the Oracle and SQL Server databases. A pop-up window will report about the success of the migration and you can then confirm it by logging in the SQL Server database using the SQL Server Management Studio (SSMS):

通过单击“ 迁移数据”选项，将要求您再次连接到Oracle和SQL Server数据库。 弹出窗口将报告有关迁移成功的信息，然后您可以通过使用SQL Server Management Studio（SSMS）登录SQL Server数据库来进行确认：

Confirm that all objects and data are corrected migrated and you should have finished with success your Oracle to SQL Server migration.

确认已正确纠正所有对象和数据的迁移，并且您应该成功完成从Oracle到SQL Server的迁移。

翻译自: https://www.sqlshack.com/using-ssma-v7-1-for-oracle-to-migrate-into-sql-server/

ssma5.3

本文来源 互联网收集，文章内容系作者个人观点，不代表 本站 对观点赞同或支持。如需转载，请注明文章来源,如您发现有涉嫌抄袭侵权的内容，请联系本站核实处理。