Guide to Migrating from Oracle to SQL Server 2014 and Azure SQL Database.pdf

(1470 KB) Pobierz
Guide to Migrating from Oracle to SQL Server 2014
and Azure SQL Database
SQL Server Technical Article
Writers:
Yuri Rusakov (DB Best Technologies), Igor Yefimov (DB Best Technologies),
Anna Vynograd (DB Best Technologies), Galina Shevchenko (DB Best Technologies)
Technical Reviewer:
Dmitry Balin (DB Best Technologies)
Published:
November 2014
Applies to:
SQL Server 2014
Summary:
This white paper explores challenges that arise when you migrate from an
Oracle 7.3 database or later to SQL Server 2014. It describes the implementation
differences of database objects, SQL dialects, and procedural code between the two
platforms. The entire migration process using SQL Server Migration Assistant (SSMA)
v6.0 for Oracle is explained in depth, with a special focus on converting database
objects and PL/SQL code.
Created by: DB Best Technologies LLC
2535 152
nd
Ave NE, Redmond, WA 98052
Tel: +1-855-855-3600
E-mail: info@dbbest.com
Web:
www.dbbest.com
Copyright
This is a preliminary document and may be changed substantially prior to final
commercial release of the software described herein.
The information contained in this document represents the current view of Microsoft
Corporation on the issues discussed as of the date of publication. Because Microsoft
must respond to changing market conditions, it should not be interpreted to be a
commitment on the part of Microsoft, and Microsoft cannot guarantee the accuracy of
any information presented after the date of publication.
This White Paper is for informational purposes only. MICROSOFT MAKES NO
WARRANTIES, EXPRESS, IMPLIED OR STATUTORY, AS TO THE INFORMATION IN
THIS DOCUMENT.
Complying with all applicable copyright laws is the responsibility of the user. Without
limiting the rights under copyright, no part of this document may be reproduced, stored
in or introduced into a retrieval system, or transmitted in any form or by any means
(electronic, mechanical, photocopying, recording, or otherwise), or for any purpose,
without the express written permission of Microsoft Corporation.
Microsoft may have patents, patent applications, trademarks, copyrights, or other
intellectual property rights covering subject matter in this document. Except as
expressly provided in any written license agreement from Microsoft, the furnishing of
this document does not give you any license to these patents, trademarks, copyrights,
or other intellectual property.
Unless otherwise noted, the example companies, organizations, products, domain
names, e-mail addresses, logos, people, places and events depicted herein are
fictitious, and no association with any real company, organization, product, domain
name, email address, logo, person, place or event is intended or should be inferred.
© 2014 Microsoft Corporation. All rights reserved.
Microsoft and SQL Server are registered trademarks of Microsoft Corporation in the
United States and other countries.
The names of actual companies and products mentioned herein may be the trademarks
of their respective owners.
Contents
Introduction..................................................................................................................... 6
Overview of Oracle-to-SQL Server 2014 Migration ......................................................... 7
Main Migration Steps .................................................................................................. 7
Conversion of Database Objects ................................................................................. 8
Differences in SQL Languages ................................................................................... 9
PL/SQL Conversion .................................................................................................... 9
Data Migration Architecture of SSMA for Oracle ........................................................... 11
Implementation in SSMA ........................................................................................... 11
Solution Layers ......................................................................................................... 11
Client Application ...................................................................................................... 12
Stored Procedures Interface ..................................................................................... 12
Database Layer......................................................................................................... 12
Migration Executable................................................................................................. 12
Message Handling .................................................................................................... 13
Validation of the Results ........................................................................................... 13
Migrating Oracle Data Types ........................................................................................ 14
Numeric Data Types ................................................................................................. 15
Character Data Types ............................................................................................... 16
Date and Time .......................................................................................................... 16
Boolean Type ............................................................................................................ 17
Large Object Types ................................................................................................... 17
XML Type ................................................................................................................. 17
ROWID Types ........................................................................................................... 18
Migrating Oracle Spatial Data ....................................................................................... 19
Emulating Oracle System Objects ................................................................................ 21
Converting Oracle System Views .............................................................................. 21
Converting Oracle System Functions ........................................................................ 28
Converting Oracle System Packages ........................................................................ 40
Converting Nested PL/SQL Subprograms .................................................................... 60
Inline Substitution...................................................................................................... 60
Emulation by Using Transact-SQL Subprograms ...................................................... 64
Migrating Oracle User-Defined Functions ..................................................................... 68
Conversion Algorithm ................................................................................................ 68
Converting Function Calls When a Function Has Default Values for Parameters and
with Various Parameter Notations ............................................................................. 74
PRAGMA INLINE ...................................................................................................... 76
Migrating Oracle Triggers ............................................................................................. 78
Conversion Patterns ................................................................................................. 80
The Execution Order of Triggers ............................................................................... 99
Compound Triggers ................................................................................................ 100
Emulating Oracle Packages ....................................................................................... 102
Converting Procedures and Functions .................................................................... 102
Converting Overloaded Procedures ........................................................................ 103
Converting Packaged Variables .............................................................................. 104
Converting Packaged Cursors ................................................................................ 104
Converting Initialization Section .............................................................................. 106
Package Conversion Code Example ....................................................................... 107
Converting Packages to Azure SQL DB .................................................................. 108
Conversion of Oracle Materialized Views ................................................................... 114
Sequences Conversion............................................................................................... 117
Migrating Hierarchical Queries.................................................................................... 121
Emulating Oracle Exceptions ...................................................................................... 125
Exception Raising ................................................................................................... 125
Exception Handling ................................................................................................. 127
SSMA Exceptions Migration to SQL Server 2014.................................................... 128
SSMA Exceptions Migration to Azure SQL DB ........................................................ 130
Migrating Oracle Cursors ............................................................................................ 133
Syntax ..................................................................................................................... 133
Declaring a Cursor .................................................................................................. 135
Opening a Cursor.................................................................................................... 137
Fetching Data ......................................................................................................... 137
CURRENT OF Clause ............................................................................................ 142
Closing a Cursor ..................................................................................................... 142
Examples of SSMA for Oracle V6.0 Conversion...................................................... 143
CONTINUE Statement of a LOOP .............................................................................. 148
Simulating Oracle Transactions in SQL Server 2014 .................................................. 150
Choosing a Transaction Management Model .......................................................... 150
Autocommit Transactions ........................................................................................ 150
Implicit Transactions ............................................................................................... 150
Explicit Transactions ............................................................................................... 150
Choosing a Concurrency Model .............................................................................. 151
Make Transaction Behavior Look Like Oracle ......................................................... 151
Simulating Oracle Autonomous Transactions ............................................................. 152
Simulating Autonomous Procedures and Packaged Procedures ............................. 153
Simulating Autonomous Functions and Packaged Functions .................................. 154
Simulation of Autonomous Triggers ........................................................................ 155
Code Example ........................................................................................................ 155
Migrating Oracle Records and Collections .................................................................. 157
Implementing Collections ........................................................................................ 157
Implementing Records ............................................................................................ 167
Implementing Records and Collections via XML ..................................................... 168
Sample Functions for XML Record Emulation ......................................................... 171
Emulating Records and Collections via CLR UDT ................................................... 173
SSMA Records and Collections Migration to Azure SQL DB ................................... 179
Migrating Tables to Memory-Optimized Tables ........................................................... 182
Conclusion.................................................................................................................. 187
About DB Best Technologies .................................................................................. 187

Plik z chomika:

Inne pliki z tego folderu:

Inne foldery tego chomika:

Zgłoś jeśli naruszono regulamin