Skip to content

03_01.Assessment_N_Convert.md

Latest commit

 

History

History
118 lines (101 loc) · 9.32 KB

03_01.Assessment_N_Convert.md

File metadata and controls

118 lines (101 loc) · 9.32 KB
Raw

Database Migration Process Overview

00_02.database_migration_process_overview

Assessment

  • What To Assess

    • Review and Assess output of Azure migration assistant and Azure Migration output.
    • Assess application technologies and target remediation SKUs, dependency map with application, server, DBs.
    • Run and gather output from Ora2PG / SSMA.
    • Identify Application and Databases breaking and critical changes. (For example: Third party application that does not support movement to Azure, functions, Package body and nested procedure calls , Synonyms, Table sub-partitions etc.)
    • Map the discovered inventory with limitations of Azure SQL Database. (For example: Storage -> IOPS)
    • Review LOBS, XML, JSON types.
    • Data load frequency.
    • HA/DR- possible configurations and cost assessment.
    • Table partitioning, Indexes, Ingestion record sizes.
    • Realtime analytical workload scenarios.
    • Data Encryption.
    • Experience from previous migration using Ora2PG / SSMA can convert 80-90% schema and 50% of code for very complex code involves nested procedure.

  • Outcomes Of Assessment

    • Target SKUs (For example - Azure SQL Managed Instance vs Azure SQL Database Hyperscale)
    • Target topology - Application, DB, storage, sizing, network requires, back ups, monitoring, security, logging, analytics, optimization and Modernize options post migration, replication (geo Vs local), back up and retention, storage size, application remediation areas, code changes, function changes/re-write code paths, Deployment and cutover plan.
    • Decision to leave some DBs On-prem and move application only using hybrid connections and move to IaaS for now with a future path to optimize and modernization in the radar i.e. move on to PaaS.

  • Accelerators For Assessment



Convert

In this step of the migration, the Oracle code and DDL scripts are converted or translated to SQL Server. The SSMA tool convert the Oracle objects in a SQL Server format automatically. Some of the generated objects can't be compiled in the SQL Server without manual changes.



  • Useful Tips & Guide for Convert
    • In Oracle there is CLOB & BLOB data type holding 4 GB data Max, but in SQL there is the limitations up to 2GB. SQL Managed Instance doesn't support filestream. This means, you need to save BLOB data in Storage Account and need to develop on application side to handle data. (Database can keep file location on the table and app need to access storage account based on the location information.)
    • Oracle XMLTYPE store XML data and alternative for SQL Server is XML. XMLTYPE datatype in Oracle can handle 6 GB but SQL XML datatype can handle up to 2 GB. This means, you need to save BLOB data in Storage Account and need to develop on application side to handle data. (Database can keep file location on the table and app need to access storage account based on the location information.)
    • You can leverage below script for account migration.
    
SELECT 'USE MASTER 
GO 
CREATE LOGIN ' || USERNAME || ' WITH PASSWORD = '''' 
GO 
USE ' || USERNAME || ' 
GO 
CREATE USER ' || USERNAME || ' FOR LOGIN ' || USERNAME || ' 
GO'
FROM DBA_USERS
WHERE USERNAME NOT IN ('SYS', 'SYSTEM', 'ANONYMOUS')
AND ACCOUNT_STATUS = 'OPEN';



References