microsoft / mcw-migrating-oracle-to-azure-sql-and-postgresql

MCW Migrating Oracle to Azure SQL and PostgreSQL

Migrating Oracle to Azure SQL and PostgreSQL

This workshop is archived and no longer being maintained. Content is read-only.

Wide World Importers (WWI) has experienced significant growth in the last few years. As a result, the Oracle OLTP database that powers their vendor integrations and inventory and sales applications has proved inadequate. The CIO has read about the performance and security enhancements of Azure SQL Database. Before committing to an Azure migration, the CIO would like a PoC of the OLTP database migration and the necessary modifications to the ASP.NET Core inventory app.

WWI has also learned of the benefits that Azure Database for PostgreSQL offers for an Oracle migration, including the similarities between PL/SQL and PL/pgSQL. Consult the PostgreSQL Migration Lab document to use the open-source ora2pg tool to assess an Oracle transactional database and migrate it to Azure.

In addition to predictable growth, they’ve had a substantial amount of growth in the data they store in their data warehouse. Their data warehouse is starting to show its age, slowing down during extract, transform, and load (ETL) operations and during critical queries. The data warehouse is running on SQL Server 2008 R2 Standard Edition. The CIO is excited about the potential performance improvements offered by Azure SQL Database, such as clustered ColumnStore indexes. She is also hoping that table compression can improve performance and backup times.

February 2022

Target audience

• Application developer
• SQL Developer
• Database Administrator

Abstracts

Workshop

In this workshop, you gain a better understanding of how to conduct a site analysis for a customer to compare cost, performance, and level of effort required to migrate from Oracle to Azure SQL Database or Azure Database for PostgreSQL. There are two migration paths included in this workshop and the training files are named appropriately for each path. Both workshops share a common customer Oracle migration scenario. Each workshop path has tailored database platform migration steps to assist you in this learning journey. You will evaluate the dependent applications and reports that need to be updated and come up with a migration plan. Also, you will design and build a proof of concept (POC) to help the customer take advantage of new Azure SQL Database or Azure Database for PostgreSQL features to improve performance and resiliency.

For those students focusing on the optional SQL Server migration (part of the Oracle to Azure SQL Lab document), you will explore ways to migrate from an old version of SQL Server to the latest version (Azure SQL Database) and consider the impact of migrating from on-premises to the cloud.

Given the time required to complete the workshop, it is recommended that the student and trainer pick a single migration path.

Whiteboard design session

In this whiteboard design session, you work with a group to design a proof of concept (POC) for conducting a site analysis for a customer to compare cost, performance, and level of effort required to migrate from Oracle to Azure SQL Database or Azure Database for PostgreSQL. You evaluate the dependent applications and reports that need to be updated and come up with a migration plan. Also, you review ways to help the customer take advantage of the database features to improve performance and resiliency. For the optional SQL Server path, you explore ways to migrate from an old version of SQL Server to the latest version (Azure SQL Database) and consider the impact of migrating from on-premises to the cloud.

At the end of this whiteboard design session, you will be better able to design a database migration plan and execute the steps.

Hands-on lab

In this hands-on lab, you implement a proof of concept (POC) for conducting a site analysis for a customer to compare cost, performance, and migration level of effort. You will evaluate the dependent applications and reports that need to be updated and come up with a migration plan. Also, you help the customer take advantage of new features to improve performance and resiliency and perform a migration.

At the end of this hands-on lab, you will be better able to design and build a database migration plan and implement any required application changes associated with changing database technologies.

Azure services and related products

• Azure App Services
• Azure Database Migration Service (DMS)
• Azure SQL Database
• Azure SQL Data Warehouse
• SQL Server on Azure Virtual Machines (SQL Server 2008 R2)
• Data Migration Assistant (DMA)
• SQL Server Management Studio (SSMS)
• SQL Server Migration Assistant (SSMA)
• Visual Studio 2019
• Azure Database for PostgreSQL
• ora2pg
• pgAdmin

Azure solution

Data Modernization to Azure

Related references

MCW

Help & Support

We welcome feedback and comments from Microsoft SMEs & learning partners who deliver MCWs.

Having trouble?

• First, verify you have followed all written lab instructions (including the Before the Hands-on lab document).
• Next, submit an issue with a detailed description of the problem.
• Do not submit pull requests. Our content authors will make all changes and submit pull requests for approval.

If you are planning to present a workshop, review and test the materials early! We recommend at least two weeks prior.

Please allow 5 - 10 business days for review and resolution of issues.

mcw-migrating-oracle-to-azure-sql-and-postgresql's Issues

Bug - SQL 2008 or 2008 R2?

The before the HOL states to deploy 2008 R2 and SQL 2017. Exercise 1 screenshots/steps have steps to connect to SQL 2008 (no R2).

I would recommend not mixing the heterogenous and homogenous migration in the same lab. I would create a dedicated lab for DMA and DSM - Task 10 and Task 11 should be in a different lab file

I would recommend not mixing the heterogenous and homogenous migration in the same lab. I would create a dedicated lab for DMA and DSM - Task 10 and Task 11 should be in a different lab file

Originally posted by @aciortea in #46 (comment)

Migrating Oracle to Azure PostgreSQL

I have a quick question regarding "Migrating Oracle to Azure PostgreSQL. "I am on step 24, "Creating a new migration project", it seems like the "create project only" is in public preview and requires approval. Can you please guide me on overcoming this?

Overall i enjoyed the lab and lots of great learnings on modifying both DB and App side. I have provided my feedback trust it is useful.

Overall i enjoyed the lab and lots of great learnings on modifying both DB and App side. I have provided my feedback trust it is useful.

The only thing from the app side might be an option lab for PowerApp to demonstrate the power of Microsoft cloud. Perhaps we could include that for those students that might be keen to learn and we could simply point them to existing PowerApp labs.

Originally posted by @shashankpawar78 in #46 (review)

PostgreSQL: Exercise 3: Task 6: ora2pg not found

Exercise 3: Task 6: On executing command cd C:\ora2pg, it says directory not found.

Can we please have this reviewed?

Thanks

HOL step by step lab : Solution architecture

Can someone check on Solution Architecture which has provided in the HOL- step by step.
May i know why Azure SQL Datawarehouse is there in preferred architecture when there is no mention about it in the overall solution? Can we update that in documentation?

Issue while installing postgres NuGet package.

Hi,

Facing the below issue while installing and updating the NuGet packages,

Able to resolve it after updating the version in NorthwindMVC.csproj target .net version file

Image is not shown in HOL Guide

Images not shown in HOL Guide:

• Task 1 Point 3
• Task 2 Point 1

Unable to cast object of type 'Npgsql.NpgsqlParameter' to type 'Oracle.ManagedDataAccess.Client.OracleParameter'.

Getting issue in Exercise-4. But in task-7,

Please find the screenshots below:

HOL step-by-step - Migrating Oracle to PostgreSQL

In Exercise 3 Task4 ,we are asked to download installora2pg.ps1 file using the URL provided in the task. But, the URL does not download the installora2pg.ps1 script file. Instead the URL just navigates to the installora2pg.ps1 file in browser.
https://github.com/microsoft/DataMigrationTeam/blob/master/IP%20and%20Scripts/PostgreSQL%20Migration%20and%20Assessment%20Tools/installora2pg.ps1
The file has been downloaded manually and executed. As per the instruction in Exercise 3 Task4 step6, after executing installora2pg.ps1 the oci.dll file is located under C:\instantclient_12_2\oci.dll.
In Exercise 3 Task 6, when step1 was performed I got to know that ora2pg directory does not exists.

Issue with the URL in Before the HOL

1. In Task-9, it is mentioned to use the URL https://raw.githubusercontent.com/microsoft/DataMigrationTeam/master/IP%20and%20Scripts/PostgreSQL%20Migration%20and%20Assessment%20Tools/installora2pg.ps1 to download installora2pg.ps1 script. But it is throwing the error 400: Invalid request.

WDS - trainer presentation (PPT)

Missing last customer objection question. I see you combined questions 2 & 3 for PPT space, but we need to get that last one on the slide since you asked and answered it please.

HOL step-by-step - Migrating Oracle to Azure SQL

In Exercise 4 Task6, while building the NorthwindMVC solution the build got failed and giving me this below error.

This project references NuGet package(s) that are missing on this computer. Use NuGet Package Restore to download them.
For more information, see http://go.microsoft.com/fwlink/?LinkID=322105. The missing file is ..\packages\Microsoft.Net.Compilers.3.1.0-beta1-final\build\Microsoft.Net.Compilers.props.

I followed the above link mentioned in the error to rectify the issue. Even though, the Package restore has been enabled and all the packages are up-to-date. Still the build got failed. Please, find the below screenshot for reference.

Thanks and Regards,
Tejaswini.

Issue with Oracle download link:- Exercise 4: Setup Oracle 11g Express Edition

1. Oracle download link for Oracle 11g XE takes us to the latest edition of XE for Linux, it should point to Windows.
2. The path to lab files has a minor inaccuracy in the Oracle Script lab for Northwind
3. Execute SQL in fusion via Visual Studio doesn’t work well, After the execute selection for script 4, we had to do execute selection for the rest of the scripts. The execute (all) button did not work anymore.

Unable to find a directory C:\ora2pg

Hello team,

I have followed all the steps until Task 6 of HOL step-by-step - Migrating Oracle to PostgreSQL.
I installed ora2pg in Task 4, but there is no directory "C:\ora2pg" that mentioned in Task 6> Step1.
Can anyone help me what should I check more ?

Azure Database HOL

It's said in HOL Abstract and Learning Objectives that we will "Implement high availability using Stretch Database and AlwaysOn Availability Groups" but there's no lab to implement this. Can we add one more exercise to cater this requirement?

Issue in Dashboard launch in browser in Exercise1>Task2>Step8.

Encountered issue in Dashboard launch in browser in Exercise1>Task2>Step8 stating "InvalidOperationException: The view 'Index' was not found". PFB screenshot FYR.

No Peering between DMS and SQL2008R2 VM

Hi - SQL2008R2 VM is on a different VNET to Azure DMS but there's no step to peer the two. Would recommend changing instructions to deploy the DMS to the same vnet/subnet as the SQL 2008R2 VM.

In the SSMA,SSMS and Portal, the IP address is not consistent.

when i look at the screenshots, under ssma section, in one i see localhost,
in portal section ip address is 109.209.246.36 but in ssms 52.183.11.81 and in another
either redact all the IPs and blur them or make it consistent

SSMA 8.10

i think we should introduce the more later 8.22 or so. change the screenshots of SSMA download

December 2018 test-fix for Data Platform upgrade and migration

This package is scheduled for a December test-fix. Please review the contents of this repo and comment on this issue with suggested updates.

September 2018 test-fix ready for review/QC

The sept-2018-test-fix branch is ready for review/QC.

Correct workshop title

Please correct the title of this workshop to - Data Platform upgrade and migration

HOL - unguided

Need one more level in the TOC please - to include Tasks to complete and Exit criteria.

Migrating Oracle to Postrges: Exe 4 Task6:

Facing issue in HOL Oracle, Exercise 1 Task 1.

Facing issue while running the 5.northwind.oracle.seed.sql queries,
Please find the screenshot below:

1. Getting this popup while running the query.
   
   I have avoided this after adding set define off; in starting of the queries.

2. Once the execution is completed getting this issue.
   

But the tables are getting created

Before hands on lab: Task 9, Install ora2pg is failing randomly

HI,

The Installora2pg script is failing randomly, in some cases its working fine in some its not,
find the attached file for the logs.
[](url
ora2pglogs.txt
)

Issue in Migrating Oracle to PostgreSQL Exercise 4, task 3

In Exercise 4, Task 3, step 4, after migrating table data to the PostgreSQL instance successfully using ora2pg utility, I am not able to see the data in PgAdmin after running the query inside the editor.

As shown in the below screenshot, the query is running successfully but the data is not loaded.

Before Migrating the Application section could we add an Assess you Application - we can Data Access Migration Toolkit which is a VS code extension. Below are the steps for Java app but would be the same for .NET and you can use the Oracle dialect

Before Migrating the Application section could we add an Assess you Application - we can Data Access Migration Toolkit which is a VS code extension. Below are the steps for Java app but would be the same for .NET and you can use the Oracle dialect
https://techcommunity.microsoft.com/t5/microsoft-data-migration/migrate-your-java-applications-from-oracle-to-sql-server-with/ba-p/368727

Originally posted by @aciortea in #46 (comment)

Suggested changes for next update

Adding this in for review during the next update of this HOL.

1. Provision lab resources using an ARM template.
2. Create a configuration script for the SqlServer2008 VM to do the following:
   • Download and install DMA
   • Download the database files from https://github.com/Microsoft/sql-server-samples/releases/tag/adventureworks2008r2.
   • Extract the downloaded database files to C:\AdventureWorksSample.
3. Create a configuration script for the LabVM to do the following:
   • Download and install SSMA for Oracle from https://download.microsoft.com/download/1/2/6/126B0BE5-7731-4070-9AB1-73828D419781/SSMAforOracle_8.4.0.msi.
   • Download and install dbForge Fusion from https://www.devart.com/dbforge/oracle/fusion/dbforgeoracle39vs17.exe
   • Download the lab starter solution from https://github.com/microsoft/MCW-Data-Platform-upgrade-and-migration/archive/master.zip.
   • Extract the lab starter solution to C:\handsonlab.

HOL - step-by-step

Line 350 says:

TODO: Move steps 16 - 20 down to the migrate Oracle to SQL section.

Is that correct?

April 2019 scheduled content test-fix

The April 2019 test-fix pull request is ready for review/QC.

Issue in Exercise3 - Data Compression not supported.

In Exercise 3 Task 1 step 7, SQL Server 2008 R2 doesn't support data compression.
Enclosing image for reference.

Having gone through the Labs, i noticed that there is Inconsistency of SSMA install on LabVM vs SQL Server 2008 VM - in the Before the hands-on lab setup guide. It should be DMA setup on SQL Server 2008 VM and SSMA on the LabVM, whereas currently it has installation of SSMA on SQL Server 2008 VM and no pre-install of DMA tool on SQL SErver 2008 VM which is fine but perhaps have consistency in installation of tools.

Having gone through the Labs, i noticed that there is Inconsistency of SSMA install on LabVM vs SQL Server 2008 VM - in the Before the hands-on lab setup guide. It should be DMA setup on SQL Server 2008 VM and SSMA on the LabVM, whereas currently it has installation of SSMA on SQL Server 2008 VM and no pre-install of DMA tool on SQL SErver 2008 VM which is fine but perhaps have consistency in installation of tools.

Originally posted by @shashankpawar78 in #46 (comment)

WDS trainer guide

Please rename document to take out dashes & update any links that change may affect.

FYI - I increased an indent on line 410 and removed a duplicate word (let me know if that was wrong and I'll change it back)
Before:

• An upgraded data warehouse using Azure Database Migration Service, including
• Including upgraded SSIS, SSAS, and SSRS projects

Now:

• An upgraded data warehouse using Azure Database Migration Service, including:
  • Upgraded SSIS, SSAS, and SSRS projects

Getting an error for employee table when using DMS

For some reason, the number of employee tables are different from the oracle source db. Appreciate if you can please look into this.
Thanks

HOL - step-by-step & unguided

Please remove dashes in the document names an update any needed link.

Ex 5: Migrate the Oracle database to SQL Server 2017

In the Oracle Metadata Explorer, check the box next to NW, expand the NW database, and un-check Packages. Next, click on NW to make sure it is selected in the tree.

If I convert the schema by un-checking the Packages, get errors for all the procedures because the cursor type is defined in the package. Please advise.

Also, If i include the package I get the below errors -

Please advise. Thanks.

SQL Server 2017 Image

Can you use SQL Server 2017 image instead to combine Exercise 1 - Task 1, Task 2 and Task 3? It will help minimize the effort to install the OS and SQL Server respectively

csc.exe file is missing from MCW-Data-Platform-upgrade-and-migration-master\Hands-on lab\lab-files\starter-project\NorthwindMVC\bin\roslyn\csc.exe

When running starter project in Exercise 4 -Task 6, Showing below error.
Server Error in '/' Application.

Could not find a part of the path 'C:\handsonlab\MCW-Data-Platform-upgrade-and-migration-master\Hands-on lab\lab-files\starter-project\NorthwindMVC\bin\roslyn\csc.exe'.
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details: System.IO.DirectoryNotFoundException: Could not find a part of the path 'C:\handsonlab\MCW-Data-Platform-upgrade-and-migration-master\Hands-on lab\lab-files\starter-project\NorthwindMVC\bin\roslyn\csc.exe'.

Exercise 5: Task 1: SQL Server Migration Assistant for Oracle is not starting

Exercise 5: Task 1: Step 1: On your LabVM, launch Microsoft SQL Server Migration Assistant for Oracle from the Start Menu.

Issue: SQL Server Migration Assistant for Oracle is not starting as shown below:

Can we please have a solution to this?

Thanks

Before the HOL

I corrected file name to Before the HOL (from Before the lab) - please check that didn't break any links. (I found and updated 2 in the step-by-step lab)

I added updated headers and licensing at the top. The template has a Requirements section. I added it to the document, please fill in that section.

I removed your Next Steps section since it's not on the current template. We could put that in as a future enhancement/add to workshops if you'd like?

Sept 2021 Update Suggestions