使用Microsoft数据迁移助手在Oracle数据库和SQL Server之间迁移的具体示例

最新推荐文章于 2024-11-18 08:41:09 发布

culuo4781

最新推荐文章于 2024-11-18 08:41:09 发布

阅读量417

点赞数

文章标签：数据库 python java linux oracle

原文链接：https://www.sqlshack.com/a-concrete-example-of-migration-between-an-oracle-database-and-sql-server-using-microsoft-data-migration-assistant/

版权

介绍 (Introduction)

Intended audience

目标听众

This document is intended for application developers and database administrators who plan to migrate an Oracle Database to Microsoft SQL Server.

本文档适用于计划将Oracle数据库迁移到Microsoft SQL Server的应用程序开发人员和数据库管理员。

Context

语境

In the previous article, we saw how to install and prepare to migrate one or more Oracle Schema(s) and their objects to SQL Server. Now it’s time to go through the migration process…

在上一篇文章中，我们了解了如何安装和准备将一个或多个Oracle Schema及其对象迁移到SQL Server。现在是时候完成迁移过程了……

议程 (Agenda)

In its documentation, Microsoft takes the official Oracle HR schema. It seems to work like a charm and with no conversion work. What would it be with a more concrete example?

Microsoft在其文档中采用了官方的Oracle HR模式。它似乎像一种魅力，没有任何转换工作。一个更具体的例子会是什么？

For this test, we will use a free and open source load testing tool called “HammerDB”. It can connect to both Oracle Database and SQL Server.

对于此测试，我们将使用一个免费的开源负载测试工具“ HammerDB ”。它可以连接到Oracle数据库和SQL Server。

In short, this tool is a benchmarking tool that creates and uses a database schema and a set of databases objects to do its job.

简而言之，该工具是一个基准测试工具，可创建并使用数据库模式和一组数据库对象来完成其工作。

As, we previously created a user called SSMAUsr and its associated schema in our source instance (in contrast to SQL Server, in Oracle Database, there is only one schema associated to one database user), we will take and use it as a destination for HammerDB objects.

因为，我们之前在源实例中创建了一个名为SSMAUsr的用户及其关联的架构（与SQL Server相反，在Oracle数据库中，只有一个架构与一个数据库用户关联），因此我们将其用作目标HammerDB对象。

We will then run SSMA against the SSMAUsr’schema and try to migrate it to our destination SQL Server instance.

然后，我们将针对SSMAUsr'schema运行SSMA，并尝试将其迁移到我们的目标SQL Server实例。

Once the migration process is complete, we will run HammerDB against that migrated schema and check if the application works.

迁移过程完成后，我们将针对该迁移的架构运行HammerDB，并检查应用程序是否正常运行。

Installing HammerDB

安装HammerDB

Go to HammerDB website and download the installer. Once the download is completed, go to the computer on which SSMA has been installed and run the executable file. You could get following warning message. If so, click on “Run”.

转到HammerDB 网站并下载安装程序。下载完成后，请转到已安装SSMA的计算机并运行可执行文件。您可能会收到以下警告消息。如果是这样，请单击“运行”。

Note: For HammerDB to be able to connect to an Oracle Database instance, it needs the Oracle client to be installed.

注意：为了使HammerDB能够连接到Oracle数据库实例，需要安装Oracle客户端。

Once HammerDB installer is started, you will provide the language in which the tool will be installed.

HammerDB安装程序启动后，您将提供安装工具的语言。

Select the appropriate language and click on the “OK” button.

选择适当的语言，然后单击“确定”按钮。

Let’s choose “English”. A dialog will appear and ensure you are really willing to continue…

让我们选择“英语”。将出现一个对话框，确保您确实愿意继续…

Click on “Yes” button and the installation wizard will be displayed.

单击“是”按钮，将显示安装向导。

Click on “Next”

点击“下一步”

Select the appropriate destination folder and click on “Next” button.

选择适当的目标文件夹，然后单击“下一步”按钮。

Click on “Next” button and the installation begins.

单击“下一步”按钮，安装开始。

And, finally, HammerDB is installed.

最后，安装了HammerDB。

Click on “Finish” button and HammerDB will start.

单击“完成”按钮，HammerDB将启动。

Note: If anything went wrong during the installation or the remaining of this procedure, please, refer to HammerDB documentation. It’s the best resource for getting support.

注意：如果在安装过程中或此过程的其余过程中出现任何问题，请参阅HammerDB 文档。这是获得支持的最佳资源。

Creating source schema using HammerDB

使用HammerDB创建源模式

Here is what HammerDB user interface looks like:

HammerDB用户界面如下所示：

For those who are interested in the kind of benchmarks HammerDB can perform, you can go to Options menu and click on “Benchmark”.

对于那些对HammerDB可以执行的基准测试类型感兴趣的用户，可以转到“选项”菜单，然后单击“基准”。

You will get an overview of its benchmarking capabilities:

您将大致了解其基准测试功能：

For the purpose of this test, we will keep the default, which is Oracle and TPC-C option.

为了进行此测试，我们将保留默认值，即Oracle和TPC-C选项。

Adapting SSMAUsr on the source database instance

在源数据库实例上 调整 SSMAUsr

Let’s now adapt the SSMAUsr database user on source instance so that it can be used for this test.

现在，让我们在源实例上适应SSMAUsr数据库用户，以便可以将其用于此测试。

First, let’s grant it the permission to create objects and data.

首先，让我们授予创建对象和数据的权限。

GRANT RESOURCE, CREATE VIEW to SSMAUsr;
GRANT UNLIMITED TABLESPACE to SSMAUsr; 
GRANT EXECUTE ON SYS.DBMS_RANDOM TO SSMAUsr; -- Used in NEWORD procedure

Now, let’s create a dedicated tablespace:

现在，让我们创建一个专用的表空间：

CREATE TABLESPACE SSMA_DATA 
DATAFILE '/u02/oradata/TEST01/data/SSMA_DATA.DBF' 
SIZE 2048M 
AUTOEXTEND ON NEXT 128M MAXSIZE 16384M
LOGGING 
EXTENT MANAGEMENT LOCAL 
SEGMENT SPACE MANAGEMENT AUTO
;

The previous command creates a tablespace called SSMA_DATA with one datafile with an initial size of 2 Gb and extensible by step of 128Mb to 16 Gb.

前面的命令创建一个名为SSMA_DATA的表空间，该表空间的一个数据文件的初始大小为2 Gb，并且可扩展为128Mb至16 Gb。

Now, let’s assign this tablespace as the default one for SSMAUsr:

现在，让我们将此表空间分配为SSMAUsr的默认表空间：

ALTER USER SSMAUsr DEFAULT TABLESPACE SSMA_DATA;

For testing purpose, let’s create a simple table and check that the object is located in the SSMA_DATA tablespace.

为了进行测试，让我们创建一个简单的表，并检查对象是否位于SSMA_DATA表空间中。

CREATE TABLE SSMAUsr.TestTbl(
    ColName VARCHAR2(256)
);
 
SELECT TABLESPACE_NAME
FROM all_tables
WHERE 
    OWNER      = 'SSMAUSR'
AND TABLE_NAME = 'TESTTBL'
;

You should get SSMA_DATA as a result.

结果，您应该获得SSMA_DATA。

Now, we can drop the table we just created.

现在，我们可以删除刚刚创建的表。

DROP TABLE SSMAUSR.TESTTBL;

Creating HammerDB objects

创建HammerDB对象

In this subsection, we will create the schema objects using HammerDB.

在本小节中，我们将使用HammerDB创建模式对象。

We will start by editing HammerDB configuration by double-clicking on “Option” item in the “Schema Build” tree item.

我们将通过双击“ Schema Build”树项中的“ Option”项来编辑HammerDB配置。

This will result in the display of a dialog where we can change connection settings as well as other aspects.

这将导致显示一个对话框，我们可以在其中更改连接设置以及其他方面。

Here is the expected configuration:

这是预期的配置：

Once the configuration is done, you can hit the “OK” button and click on the “build” item, just under the “Options” on which you just clicked before. You will be prompted to accept the actual building of the HammerDB schema.

完成配置后，您可以单击“确定”按钮，然后单击您之前单击的“选项”下方的“构建”项。系统将提示您接受HammerDB模式的实际构建。

You could notice messages about the failure of user creation or tablespace creation, but you can ignore it as we prepared all the work before the execution.

您可能会注意到有关用户创建或表空间创建失败的消息，但是当我们在执行之前准备所有工作时，可以忽略它。

Normally, after a while, you should get a view similar to the following one.

通常，一段时间后，您将获得与下图类似的视图。

And if we run following query, we should get a list of all the objects HammerDB has created.

而且，如果我们运行以下查询，我们将获得HammerDB创建的所有对象的列表。

select * From all_objects where owner= 'SSMAUSR';

Now, we can close HammerDB as we won’t use it anymore.

现在，我们可以关闭HammerDB，因为我们将不再使用它。

Running SSMA for reporting

运行SSMA进行报告

In this section, we will focus on the preparation for actual migration and check what objects can be migrated directly and which one requires code conversion.

在本节中，我们将着重于实际迁移的准备工作，并检查哪些对象可以直接迁移以及哪些对象需要代码转换。

First of all, we will start SSMA and create a new project with the options specific to this step, as explained in a previous section.

首先，如上一节中所述，我们将启动SSMA并使用此步骤特定的选项创建一个新项目。

The first step when preparing a migration is getting an overview of the work that has to be done. We can do it thanks to a “Create Report” menu item in the contextual menu that appears when clicking on the source schema SSMAReport.

准备迁移的第一步是获得必须完成的工作的概述。我们可以通过单击源模式SSMAReport时显示的上下文菜单中的“创建报告”菜单项来完成此操作。

It will actually build an HTML report and propose to open it up with Internet Explorer or your default web browser.

它实际上将构建一个HTML报告，并建议使用Internet Explorer或默认的Web浏览器将其打开。

Note: If the path in which the report is stored contains a space character, it will result in the opening of two different navigation tabs. You will have then to consolidate the URL…

注意：如果存储报告的路径包含空格字符，则将导致打开两个不同的导航选项卡。然后，您将不得不合并URL…

If we display the report, we can see that, unfortunately, or expectedly, we can’t migrate the schema directly to SQL Server.

如果显示该报告，则很不幸或预期会看到，我们无法将架构直接迁移到SQL Server。

But, good news though, almost 98% of the schema is directly transferable.

但是，好消息是，几乎有98％的架构可以直接转让。

If we look at the right of the screen, we can see that:

如果我们看屏幕的右边，我们可以看到：

Tables and indexes are transferred without any work to do
无需进行任何工作即可转移表和索引
Schema and procedures are the objects that require some roll up our sleeves.
模式和过程是需要折腾我们的对象的对象。

Now, if we look at the left-hand tree, we can expand the “Procedures” node and learn that problematic procedures are DELIVERY and NEWORD.

现在，如果我们看左手树，我们可以展开“ Procedures”节点，并了解有问题的过程是DELIVERY和NEWORD。

Converting Oracle schema to SQL Server

将Oracle模式转换为SQL Server

Now, let’s connect to SQL Server destination instance by clicking the “Connect to SQL Server” button. You will be asked to provide connection information to destination SQL Server instance:

现在，让我们通过单击“连接到SQL Server”按钮连接到SQL Server目标实例。系统将要求您提供到目标SQL Server实例的连接信息：

Once it’s done, click on “Connect” button.

完成后，单击“连接”按钮。

Note:

注意：

Don’t forget to use a SQL Server login that is a member of sysadmin role.
不要忘记使用sysadmin角色成员SQL Server登录名。
if destination database does not exist, SSMA will automatically ask you to create it for you
如果目标数据库不存在，SSMA会自动要求您为您创建它

Now, if we selected the SSMAUsr schema in Oracle Database Metadata Explorer and if we right-click on it, the “Convert Schema” button in the contextual menu will be enabled and ready to use.

现在，如果我们在Oracle数据库元数据资源管理器中选择了SSMAUsr模式，然后右键单击它，则上下文菜单中的“转换模式”按钮将被启用并可以使用。

The conversion will directly start and we can monitor the evolution of the conversion via the Output window at the bottom of SSMA.

转换将直接开始，我们可以通过SSMA底部的“输出”窗口监视转换的演变。

Obviously, we will get as a final word following message:

显然，我们将得到如下消息：

Synchronization operation is complete.

同步操作完成。

If we go to SQL Server Metadata Explorer, and right-click on the TestingSSMA database, a contextual menu will appear. We should click on “Synchronize with Database”.

如果我们转到SQL Server Metadata Explorer，然后右键单击TestingSSMA数据库，将显示一个上下文菜单。我们应该单击“与数据库同步”。

A summary of changes will be displayed. Here is the view when the “Hide Equal Objects” button is selected:

将显示更改摘要。这是选择“隐藏相等对象”按钮时的视图：

We can see that the conversion created two .NET Assemblies, the ssma_oracle schema we talked previously and the SSMAUsr schema we are trying to migrate.

我们可以看到，该转换创建了两个.NET程序集，即我们之前讨论的ssma_oracle模式和我们正尝试迁移的SSMAUsr模式。

If we hit “OK”, the metadata explorer will be updated with the data displayed in the screen capture above.

如果单击“确定”，则将使用上面的屏幕截图中显示的数据更新元数据浏览器。

A quick look at the stored procedures of the SSMAUsr schema in SQL Server metadata explorer will highlight the fact that the conversion also imported the two problematic stored procedures.

在SQL Server元数据资源管理器中快速查看SSMAUsr架构的存储过程将突出显示以下事实，即转换还导入了两个有问题的存储过程。

If we export let’s say the DELIVERY stored procedure as a script, which is done by right-clicking and hitting the “Save as Script” of the contextual menu, we will notice that the SSMA added some comments during the conversion.

如果我们将DELIVERY存储过程导出为脚本，这是通过右键单击并单击上下文菜单的“另存为脚本”完成的，我们将注意到SSMA在转换过程中添加了一些注释。

Here are some examples:

这里有些例子：

要么

Other noticeable facts:

其他值得注意的事实：

The generated code does not have any “;” at the end of the T-SQL instructions.
生成的代码没有任何“;” 在T-SQL指令的末尾。
The generated code is indented using a 3 spaces basis.
生成的代码使用3个空格缩进。

In order to be able to actually migrate the application, we have to fix the code for DELIVERY and NEWORD stored procedures and keep a close look at the source stored procedure. In some cases, it could be simple changes, in other cases, a complete code rewrite should be made.

为了能够实际迁移应用程序，我们必须修复DELIVERY和NEWORD存储过程的代码，并仔细查看源存储过程。在某些情况下，可能只是简单的更改，在其他情况下，应进行完整的代码重写。

We won’t go any further in the SP code conversion because it won’t give any additional value to the content of this article.

我们不会在SP代码转换中做进一步的介绍，因为它不会给本文的内容带来任何其他价值。

Data migration

数据迁移

While stored procedures need to be reviewed, tables have been converted successfully. As we installed the SSMA Extension Pack on a destination server, we are able to migrate data. It’s simply performed using the “Migrate Data” button of the contextual menu that appears when right-clicking on the source schema:

尽管需要检查存储过程，但表已成功转换。在目标服务器上安装SSMA扩展包后，我们便能够迁移数据。只需使用右键单击源模式时出现的上下文菜单中的“迁移数据”按钮即可执行此操作：

It will open a dialog asking for credentials to create a second connection to the source database

它将打开一个对话框，要求输入凭据以创建与源数据库的第二个连接

And it will do the same for destination server:

它将对目标服务器执行相同的操作：

And the process starts:

然后该过程开始：

Once the data migration is complete, a report dialog is displayed with the ability to store the report to disk.

数据迁移完成后，将显示一个报告对话框，可将报告存储到磁盘。

We can check the content if we want to ensure that the no bad conversion occurred in text or dates columns and some other tests for consistency.

如果我们要确保在text或date列中进行正确的转换，并进行其他一些一致性测试，则可以检查内容。

Testing HammerDB on converted schema

在转换后的架构上测试HammerDB

You will find attached the converted stored procedures that I have modified and were highlighted by SSMA and the PAYMENT stored procedure where I had to change every OUTPUT parameter definition with a “= NULL”.

您会发现我已修改并由SSMA和PAYMENT存储过程突出显示的转换后的存储过程附在其中，其中我必须用“ = NULL”更改每个OUTPUT参数定义。

I finally got the following message which tends to tell me that SSMA is not yet well designed for code conversion.

我最终得到以下消息，该消息倾向于告诉我SSMA尚未很好地设计用于代码转换。

Error in Virtual User 1: Payment : 25000 266 {[Microsoft][SQL Server Native Client 11.0][SQL Server]Transaction count after EXECUTE indicates a mismatching number of BEGIN and COMMIT statements. Previous count = 1, current count = 0.}

虚拟用户1中的错误：付款：25000 266 {[Microsoft] [SQL Server Native Client 11.0] [SQL Server] EXECUTE之后的事务计数指示BEGIN和COMMIT语句的数量不匹配。先前计数= 1，当前计数=0。}

I stopped the conversion process at this point because it does not fit any real-life need and would be a waste of time for me.

我此时停止了转换过程，因为它无法满足现实生活中的任何需求，并且对我来说是浪费时间。

In case of some readers would want to go till the end of the conversion, here are the steps to follow in order make HammerDb run against a SQL Server database:

如果某些读者希望一直到转换结束，请按照以下步骤操作，以使HammerDb针对SQL Server数据库运行：

First, create a HammerDb SQL Login on destination SQL Server instance, which has access to all objects in SSMAUsr schema so as ssma_oracle schema. For simplicity, you can add the database user mapped with HammerDb login as a member of the db_owner database role.

首先，在目标SQL Server实例上创建HammerDb SQL登录名，该实例可以访问SSMAUsr模式中的所有对象，例如ssma_oracle模式。为简单起见，您可以将通过HammerDb登录名映射的数据库用户添加为db_owner数据库角色的成员。

This can be performed using following script:

可以使用以下脚本执行此操作：

USE [master]
GO
CREATE LOGIN [HammerDb] WITH PASSWORD=N'123456a.', DEFAULT_DATABASE=[TestingSSMA], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF
GO
USE [TestingSSMA]
GO
CREATE USER [HammerDb] FOR LOGIN [HammerDb]
GO
USE [TestingSSMA]
GO
ALTER USER [HammerDb] WITH DEFAULT_SCHEMA=[SSMAUSR]
GO
USE [TestingSSMA]
GO
ALTER ROLE [db_owner] ADD MEMBER [HammerDb]
GO

Once the HammerDb user is created, in HammerDB application, create a new project and set the benchmark option to “SQL Server” with “TPC-C”.

创建HammerDb用户后，在HammerDB应用程序中，创建一个新项目，并将基准测试选项设置为“ SQL Server”和“ TPC-C”。

Once done, go down to “Driver Script node in treeview on the left-hand pane and double-click on “Options”.

完成后，转到左侧窗格中树视图中的“驱动程序脚本”节点，然后双击“选项”。

This will open a connection description dialog. Edit the information in that dialog and click “OK”.

这将打开一个连接描述对话框。在该对话框中编辑信息，然后单击“确定”。

Then, click on “Load”. It will load a script that will be executed by virtual users.

然后，单击“加载”。它将加载将由虚拟用户执行的脚本。

The last step is to create virtual users and make them run.

最后一步是创建虚拟用户并使其运行。

Note: I used a case-sensitive database and failed to run HammerDB, so if there is one advice to give, it’s to use a case-insensitive database or to adapt the object and column names according to the code.

注意：我使用了区分大小写的数据库，但无法运行HammerDB，因此，如果有建议，可以使用不区分大小写的数据库或根据代码修改对象和列的名称。

清理工作 (Cleanups)

To clean up your testing environment, here are the steps to follow:

要清理您的测试环境，请按照以下步骤操作：

Remove HammerDb SQL login and dependencies on destination instance
删除HammerDb SQL登录名和目标实例上的依赖项
Remove TestingSSMA database
删除TestingSSMA数据库
Remove SSMAUsr on source database
在源数据库上删除SSMAUsr
Uninstall SSMA Extension Pack from destination server
从目标服务器上卸载SSMA Extension Pack
Uninstall SSMA client
卸载SSMA客户端
Uninstall Oracle Database Client from both client and destination hosts
从客户端和目标主机上卸载Oracle Database Client

结论并进一步 (Conclusion and going further)

The SSMA tool is pretty handy when it comes to converting Oracle databases tables and transfer data easily. It has advanced customizations that we did not cover in this article like schema mapping.

在转换Oracle数据库表和轻松传输数据时，SSMA工具非常方便。它具有我们在本文中没有介绍的高级定制，例如模式映射。

However, you cannot use it blindly and imagine the migration will be fine. A complete code review and advanced testing of client applications are mandatory as it’s quite a complex change.

但是，您不能盲目使用它，并且无法想象迁移会很好。必须进行完整的代码审查和客户端应用程序的高级测试，因为这是一个相当复杂的更改。

Another interesting point would be to compare performances between a migrated schema using SSMA and the same schema but specifically designed for SQL Server.

另一个有趣的观点是比较使用SSMA的迁移架构与为SQL Server专门设计的相同架构之间的性能。

Anyway, I would suggest investigating the tool in depth to see all its capabilities and to stay tuned for new versions.

无论如何，我建议您深入研究该工具，以了解其所有功能并随时关注新版本。

Previous articles in this series:

本系列以前的文章：