Oracle到SQL Server 2008迁移指南

5星 · 超过95%的资源需积分: 9 185 浏览量更新于2024-07-29 收藏 277KB DOCX 举报

"这篇文档是Oracle到SQL Server 2008迁移的官方指南，由DBBest Technologies的专家撰写，并由Dmitry Balin进行技术审阅。发布于2009年8月，适用于SQL Server 2008及2008 R2版本。" 在将Oracle数据库系统迁移到SQL Server 2008的过程中，面临的主要挑战包括不同数据库对象的实现差异、SQL方言的区别以及两个平台之间过程性代码的差异。这篇白皮书深入探讨了这些挑战，并提供了详细的解决方案。 1. **数据库对象的差异**：Oracle和SQL Server在表、索引、视图、存储过程等数据库对象的实现上存在区别。例如，Oracle支持分区表，而SQL Server则有其特有的分区策略；在索引方面，Oracle支持位图索引，而SQL Server主要使用B树索引。理解这些差异对于迁移数据结构至关重要。 2. **SQL方言的对比**：虽然两者都遵循SQL标准，但Oracle和SQL Server都有各自的方言和扩展。例如，Oracle的游标处理方式与SQL Server略有不同，还有对事务处理、联接操作、窗口函数的支持也存在差异。在迁移过程中，需要对SQL脚本进行相应的转换。 3. **PL/SQL与T-SQL的转换**：Oracle使用PL/SQL作为其过程化语言，而SQL Server使用T-SQL。白皮书着重讨论了如何将PL/SQL代码转换为T-SQL，包括函数、触发器、存储过程的迁移，这可能涉及到语法调整、异常处理机制的改变等。 4. **SQL Server Migration Assistant (SSMA)**：微软提供的SSMA工具是Oracle到SQL Server迁移的关键工具。它能够自动检测和转换数据库对象，同时也支持数据迁移。SSMA的工作流程、优点和限制都在文中详细阐述，包括如何配置项目、评估迁移准备、执行转换和测试迁移结果。 5. **迁移过程**：除了技术层面的转换，迁移还包括计划阶段、测试阶段和上线阶段。白皮书将指导读者如何制定迁移策略，如何处理兼容性和性能问题，以及如何确保迁移后的系统稳定性。 6. **最佳实践**：文中还分享了一些最佳实践，帮助用户减少迁移风险，提高迁移效率。这包括在迁移前的充分准备、对源数据库的全面了解以及在目标环境中进行充分的测试。 7. **版权信息**：此文档属于初步版本，可能在最终商业发布前有重大改动。微软公司对其中提供的信息持有当前观点，但不保证信息的完整性和准确性。这篇白皮书为Oracle数据库管理员和IT专业人士提供了一份宝贵的指南，帮助他们成功地将Oracle环境迁移到SQL Server 2008，同时克服迁移过程中可能出现的各种技术和业务挑战。

Data Migration Architecture of SSMA for Oracle

This section describes SSMA for OracleV4.0 components and their interaction during

data migration. The components execute on different computers and use Microsoft SQL

Server2008 database objects for communication. This architecture produces the best

migration performance and flexibility. Understanding this mechanism can help you set

up the proper environment for SSMA data migration. It also helps you to better control,

monitor, and optimize the process.

Implementation in SSMA

We based the SSMA for OracleV4.0 implementation on the SqlBulkCopy class,

defined in the .NET Framework2.0. SqlBulkCopy functionality resembles the bcp

utility, which allows transferring large amounts of data quickly and efficiently. Access to

the source database is established by the .NET Framework Data Provider for Oracle,

which uses the Oracle Call Interface (OCI) from Oracle client software. Optionally, you

can use .NET Framework Data Provider for OLEDB, which requires an installed Oracle

OLEDB provider.

We considered the following when designing SSMA for Oracledata migration:

 The data transfer process must run on SQLServer. That limits the number of

installed Oracle clients and reduces network traffic.

 The client application controls the process by using SQLServer stored

procedures. Therefore, you do not need any additional communication channels

with the server and can reuse the existing server connection for this purpose.

 All tables that are selected for migration are transferred by a single execution

command from the SSMA user.

 The user monitors the data flow progress and can terminate it at any time.

Solution Layers

Four layers participate in the data migration process:

 Client application, an SSMA executable

 Stored procedures that serve as interfaces to all server actions

 The database layer, which comprises two tables:

 The package information table

 The status table

 The server executable, which starts as part of a SQLServer job, executes the

data transfer, and reflects its status

Client Application

SSMA lets users choose an arbitrary set of source tables for migration. The batch size

for bulk copy operations is a user-defined setting.

When the process starts, the program displays the progress bar and a Stop button. If

any errors are found, SSMA shows the appropriate error message and terminates the

transfer. In addition, the user can click Stop to terminate the process. If the transfer is

completed normally, SSMA compares the number of rows in each source with the

corresponding target table. If they are equal, the transfer is considered to be successful.

As the client application does not directly control the data migration process, SSMA

uses a Messages table to receive feedback about the migration status.

Stored Procedures Interface

The following SQLServer stored procedures control the migration process:

 bcp_save_migration_packagewrites the package ID and XML parameters into

the bcp_migration_packages table.

 bcp_start_migration_processcreates the SQLServer job that starts the

migration executable and returns the ID of the job created.

 bcp_read_new_migration_messagesreturns the rows added by the migration

executable, filtered by known job ID.

 stop_agent_processstops the migration job, including closing the original

connections and killing the migration executable. The data will be migrated

partially.

 bcp_clean_migration_datais a procedure that cleans up a migration job.

 bcp_post_processis a procedure that runs all post-processing tasks related to

the single migrated table.

Database Layer

SSMA uses a Packages table, named [ssma_oracle].[bcp_migration_packages], to

store information about the current package. Each row corresponds to one migration

run. It contains package GUID and XML that represents RSA-encrypted connection

strings and the tables that should be migrated.

A Messages table, named [ssma_oracle].[ssmafs_bcp_migration_messages]

accumulates messages coming from migration executables during their work.

Migration Executable

The migration application, SSMA for Oracle Data Migration Assistant.exe, is executed

on a SQLServer host. The executable's directory is determined during the Extension

Pack installation. When bcp_start_migration_package starts the application, it uses

hard-coded file names and retrieves the directory name from a server environment

variable.

When it starts, the migration application gets the package ID from the command string

and reads all other package-related information from the Packages table. That

information includes source and destination connection strings, and a list of the tables to

migrate. Then the tables are processed one at a time. You get source rows via the

IDataReader interface and move them to the target table with the WriteToServer

method.

The BatchSize setting defines the number of rows in a buffer. When the buffer is full, all

rows in it are committed to the target.

To notify you about the progress of a bulk copy operation, the data migration executable

uses the SqlRowsCopied event and NotifyAfter property. When a SqlRowsCopied

event is generated, the application inserts new rows, sending information about the

progress to the Messages table. The NotifyAfter property defines the number of rows

that are processed before generating a SqlRowsCopied event. This number is

25percent of the source table's row count.

Another type of output record—the termination message—is written to the Messages

table when the application terminates either successfully or because of an exception. In

the latter case, the error text is included. If BatchSize=1, additional information about

the columns of the row where the problem occurred is extracted, so that you can locate

the problematic row.

Message Handling

The client application receives feedback from the migration executable by means of the

Messages table. During migration, the client is in the loop, polling this table and verifying

that new rows with the proper package ID appear there. If there are no new rows during

a significant period of time, this may indicate problems with the server executable and

the process terminates with a time-out message.

When the table migration completes, the server executable writes a successful

completion message. If the table is large enough, you may see many intermediate

messages, which show that the next batch was successfully committed. If an error

occurs, the client displays the error message that was received from the server process.

Validation of the Results

Before the migration starts, the client application calculates the number of rows in each

table that will be migrated. With this data, you can evaluate the correct progress

position.

After the migration completes, the client must calculate the target table's row counts. If

they are equal, the overall migration result is considered to be successful. Otherwise,

the user is notified of the discrepancy and can view the source and destination counts.

剩余63页未读，继续阅读

小心小星

粉丝: 2
资源: 7

Oracle到SQL Server 2008迁移指南

oracle数据库导入到SQL server.docx

SQL Server到Oracle的数据迁移方法.pdf

sql-migrate：用于GoSQL模式迁移工具

Oracle To Postgres

Oracle SQL Developer 2.1.0.63.73 for linux-noarch.part2

Oracle SQL Developer 2.1.0.63.73 for linux-noarch.part1

Oracle SQL Developer 1.5.5.59.69-with-jdk6.part2

Oracle SQL Developer 1.5.5.59.69-with-jdk6.part1

Oracle SQL Developer 2.1.0.63.73 for mac-osx-no-jre.part2

Oracle SQL Developer 2.1.0.63.73 for other-platform-no-jre.part2

最新资源