MIGRATING FROM ORACLE TO SQL SERVER 2014

By CHARLOTTE BROWN

INTRODUCTION 1 OVERVIEW OF ORACLE TO SQL SERVER 2014 MIGRATION 2 DATA MIGRATION ARCHITECTURE OF SSMA FOR ORACLE 6 MIGRATING ORACLE DATA TYPES 9 MIGRATING ORACLE SPATIAL DATA 14 EMULATING ORACLE SYSTEM OBJECTS 16 CONVERTING NESTED PL SQL SUBPROGRAMS 55 MIGRATING ORACLE USER DEFINED FUNCTIONS 63 MIGRATING ORACLE TRIGGERS 73 EMULATING ORACLE PACKAGES 97 CONVERSION OF ORACLE MATERIALIZED VIEWS 109 SEQUENCES CONVERSION 112 MIGRATING HIERARCHICAL QUERIES 116 EMULATING ORACLE EXCEPTIONS 120 MIGRATING ORACLE CURSORS 128 CONTINUE STATEMENT OF A LOOP 143 SIMULATING ORACLE TRANSACTIONS IN SQL SERVER 2014 145 SIMULATING ORACLE AUTONOMOUS TRANSACTIONS 147 MIGRATING ORACLE RECORDS AND COLLECTIONS 152 MIGRATING TABLES TO MEMORY OPTIMIZED TABLES 177 CONCLUSION 182 CONTENTS INTRODUCTION MIGRATING FROM AN ORACLE DATABASE TO MICROSOFT SQL SERVER 2014 FREQUENTLY GIVES ORGANIZATIONS BENEFITS THAT RANGE FROM LOWERED COSTS TO A MORE FEATURE RICH ENVIRONMENT THE FREE MICROSOFT SQL SERVER MIGRATION ASSISTANT SSMA FOR ORACLE SPEEDS THE MIGRATION PROCESS SSMA FOR ORACLE V6 0 CONVERTS ORACLE DATABASE OBJECTS INCLUDING STORED PROCEDURES TO SQL SERVER DATABASE OBJECTS LOADS THOSE OBJECTS INTO SQL SERVER MIGRATES DATA FROM ORACLE TO SQL SERVER AND THEN VALIDATES THE MIGRATION OF CODE AND DATA THIS WHITE PAPER EXPLORES THE CHALLENGES THAT ARISE DURING MIGRATION FROM AN ORACLE DATABASE TO SQL SERVER 2014 IT DESCRIBES THE IMPLEMENTATION DIFFERENCES OF DATABASE OBJECTS SQL DIALECTS AND PROCEDURAL CODE BETWEEN THE TWO PLATFORMS 1 OVERVIEW OF ORACLE TO SQL SERVER 2014 MIGRATION THIS SECTION EXPLAINS THE ENTIRE SSMA FOR ORACLE MIGRATION PROCESS WITH A SPECIAL FOCUS ON CONVERTING DATABASE OBJECTS AND PL SQL CODE MAIN MIGRATION STEPS THE FIRST MIGRATION STEP IS TO DECIDE ON THE PHYSICAL STRUCTURE OF THE TARGET SQL SERVER DATABASE IN THE SIMPLEST CASE YOU CAN MAP THE ORACLE TABLESPACES TO SQL SERVER FILEGROUPS HOWEVER BECAUSE THE FILES IN THE FILEGROUPS AND THE INFORMATION STORED IN THE FILES ARE USUALLY DIFFERENT THIS IS NOT USUALLY POSSIBLE THE NEXT STEP IS TO CHOOSE HOW TO MAP THE ORACLE SCHEMAS TO THE TARGET IN SQL SERVER SCHEMAS ARE NOT NECESSARILY LINKED TO A SPECIFIC USER OR A LOGIN AND ONE SERVER CONTAINS MULTIPLE DATABASES YOU CAN FOLLOW ONE OF TWO TYPICAL APPROACHES TO SCHEMA MAPPING BY DEFAULT IN SSMA EVERY ORACLE SCHEMA BECOMES A SEPARATE SQL SERVER DATABASE THE TARGET SQL SERVER SCHEMA IN EACH OF THESE DATABASES IS SET TO DBO THE PREDEFINED NAME FOR THE DATABASE OWNER USE THIS METHOD IF THERE ARE FEW REFERENCES BETWEEN ORACLE SCHEMAS ANOTHER APPROACH IS TO MAP ALL ORACLE SCHEMAS TO ONE SQL SERVER DATABASE IN THIS CASE AN ORACLE SCHEMA BECOMES A SQL SERVER SCHEMA WITH THE SAME NAME TO USE THIS METHOD YOU CHANGE THE SSMA DEFAULT SETTINGS USE THIS METHOD IF DIFFERENT SOURCE SCHEMAS ARE DEEPLY LINKED WITH EACH OTHER FOR INSTANCE IF THERE ARE CROSS REFERENCES BETWEEN ORACLE TABLES IN DIFFERENT SCHEMAS WHEN TRIGGER IS ON THE TABLE AND THE TABLES ITSELF ARE IN DIFFERENT SCHEMAS SSMA APPLIES THE SELECTED SCHEMA MAPPING METHOD CONSISTENTLY WHEN IT CONVERTS BOTH DATABASE OBJECTS AND THE REFERENCES TO THEM AFTER YOU CHOSE YOUR OPTIMAL SCHEMA MAPPING YOU CAN START CREATING THE TARGET SQL SERVER DATABASE AND ITS REQUIRED SCHEMAS BECAUSE THE SQL SERVER SECURITY SCHEME IS QUITE DIFFERENT FROM ORACLE S WE CHOSE NOT TO AUTOMATE THE SECURITY ITEM MIGRATION IN SSMA THAT WAY YOU CAN CONSIDER ALL POSSIBILITIES AND MAKE THE PROPER DECISIONS YOURSELF THE TYPICAL SSMA MIGRATION INCLUDES CONNECTING TO THE SOURCE ORACLE SERVER SELECTING THE SERVER THAT IS RUNNING SQL SERVER AS THE TARGET AND THEN PERFORMING THE CONVERT SCHEMA COMMAND WHEN THE TARGET OBJECTS ARE CREATED IN THE SSMA WORKSPACE YOU CAN SAVE THEM BY USING THE LOAD TO DATABASE COMMAND FINALLY EXECUTE THE MIGRATE DATA COMMAND WHICH TRANSFERS THE DATA FROM THE SOURCE TO THE TARGET TABLES MAKING THE NECESSARY CONVERSIONS THE DATA MIGRATION PROCESS IS EXECUTED ON THE SERVER THAT IS RUNNING SQL SERVER THE INTERNAL IMPLEMENTATION OF THIS FEATURE IS DESCRIBED IN DATA MIGRATION ARCHITECTURE OF SSMA FOR ORACLE 2 CONVERSION OF DATABASE OBJECTS NOT ALL ORACLE DATABASE OBJECTS HAVE DIRECT EQUIVALENTS IN SQL SERVER IN MANY CASES SSMA CREATES ADDITIONAL OBJECTS TO PROVIDE THE PROPER EMULATION GENERAL CONVERSION RULES ARE AS FOLLOWS EACH ORACLE TABLE IS CONVERTED TO A SQL SERVER TABLE DURING THE CONVERSION ALL INDEXES CONSTRAINTS AND TRIGGERS DEFINED FOR A TABLE ARE ALSO CONVERTED WHEN DETERMINING THE TARGET TABLE S STRUCTURE SSMA USES TYPE MAPPING DEFINITIONS DATA TYPE CONVERSION IS DESCRIBED IN MIGRATING ORACLE DATA TYPES NOW CONVERSION TO MEMORY OPTIMIZED TABLES IS SUPPORTED AN ORACLE VIEW IS CONVERTED TO A SQL SERVER VIEW THIS ALSO CONCERNS MATERIALIZED VIEWS WHICH ARE MIGRATED TO INDEXED ONES SSMA CREATES EMULATIONS FOR COMMONLY USED ORACLE SYSTEM VIEWS FOR MORE INFORMATION ABOUT SYSTEM VIEW CONVERSION SEE EMULATING ORACLE SYSTEM OBJECTS ORACLE STORED PROCEDURES ARE CONVERTED TO SQL SERVER STORED PROCEDURES NOTE THAT ORACLE PROCEDURES CAN USE NESTED SUBPROGRAMS WHICH MEANS THAT ANOTHER PROCEDURE OR FUNCTION CAN BE DECLARED AND CALLED LOCALLY WITHIN THE MAIN PROCEDURE SSMA CAN CONVERT INLINE SUBPROGRAMS AUTOMATICALLY SEE CONVERTING NESTED PL SQL SUBPROGRAMS ORACLE USER DEFINED FUNCTIONS ARE CONVERTED TO SQL SERVER FUNCTIONS IF THE CONVERTED FUNCTION CAN BE COMPATIBLE WITH SQL SERVER REQUIREMENTS OTHERWISE SSMA CREATES TWO OBJECTS ONE FUNCTION AND ONE STORED PROCEDURE THE ADDITIONAL PROCEDURE INCORPORATES ALL THE LOGIC OF THE ORIGINAL FUNCTION AND IS INVOKED IN A SEPARATE PROCESS FOR MORE INFORMATION SEE MIGRATING ORACLE USER DEFINED FUNCTIONS SSMA EMULATES MOST OF THE ORACLE STANDARD FUNCTIONS SEE THE COMPLETE LIST IN EMULATING ORACLE SYSTEM OBJECTS ORACLE DML TRIGGERS ARE CONVERTED TO SQL SERVER TRIGGERS BUT BECAUSE THE TRIGGER FUNCTIONALITY IS DIFFERENT THE NUMBER OF TRIGGERS AND THEIR TYPES CAN BE CHANGED SEE A DESCRIPTION OF TRIGGER CONVERSION IN MIGRATING ORACLE TRIGGERS SOME ORACLE OBJECT CATEGORIES SUCH AS PACKAGES DO NOT HAVE DIRECT SQL SERVER EQUIVALENTS SSMA CONVERTS EACH PACKAGED PROCEDURE OR FUNCTION INTO SEPARATE TARGET SUBROUTINES AND APPLIES RULES FOR STAND ALONE PROCEDURES OR FUNCTIONS OTHER ISSUES RELATED TO PACKAGE CONVERSION SUCH AS CONVERTING PACKAGED VARIABLES CURSORS AND TYPES ARE EXPLAINED IN EMULATING ORACLE PACKAGES IN ADDITION SSMA CAN EMULATE SOME COMMONLY USED ORACLE SYSTEM PACKAGES SEE THEIR DESCRIPTION IN EMULATING ORACLE SYSTEM OBJECTS SQL SERVER 2014 HAS A SEQUENCES MECHANISM THOUGH SOME FEATURES OF ORACLE SEQUENCES E G CURRVAL ARE NOT SUPPORTED IN SQL SERVER BUT YOU CAN FIND METHODS TO MANUALLY CONVERT THEM IN SEQUENCES CONVERSION ORACLE PRIVATE SYNONYMS ARE CONVERTED TO SQL SERVER SYNONYMS STORED IN THE TARGET DATABASE SSMA CONVERTS PUBLIC SYNONYMS TO SYNONYMS DEFINED IN THE SSMA ORACLE SCHEMA MIGRATING FROM ORACLE TO SQL SERVER 2014 3