Tag: Oracle Data Provider for .NET

How to Migrate On-Premise Oracle Database onto AWS RDS Microsoft SQL Server

Introduction

This article will demonstrate step by step how to migrate on-premise Oracle database onto AWS RDS SQL Server, the issues encountered plus their solutions and workarounds during the migration.

The following environment and tools will be involved in this activity:

  • Oracle Database 19c
  • SQL Server 2019
  • AWS RDS SQL Server 2019
  • Oracle Client 19c
  • AWS Database Migration Service ( DMS) or AWS S3
  • SQL Server Management Studio ( SSMS )
  • Microsoft SQL Server Migration Assistant (SSMA) for Oracle

There is no direct migration on-premise Oracle database onto AWS SQL Server RDS, so the the high level migration procedure will be:

  1. Migrate on-premise Oracle database by using SSMA to on-premise SQL Server database.
  2. Migrate on-premise SQL Server database onto AWS RDS SQL Server.

Subscribe to get access

Read more of this content when you subscribe today.

Subscribe
Advertisement

SQL Server Migration Assistant (SSMA) Tables Partially Migrated and High Memory Usage

PROBLEM

While we migrate a relatively big size database from Oracle to Microsoft SQL Server, all the small tables are migrated successfully without any issues. But the following issues occur from big tables migration.

  • “SSMAforOracle.exe” process consumes high memory, so the server got 99% memory utilization consistently.
  • For big tables with more than 10 million records, they are always partially migrated.
  • After big tables migration failure, we can see sometime the follow message in the migration log.

“Execution Timeout Expired. The timeout period elapsed prior to completion of the operation or the server is not responding.”

DataMigration Error. 
From : "SCHEMANAME"."TABLENAME"
To : [DBNAME].[SCHEMANAME].[TABLENAME] Execution Timeout [Error: Datamigrator] [9812/41] [2020-11-01 21:21:34]: Exception: Execution Timeout Expired.  The timeout period elapsed prior to completion of the operation or the server is not responding.

It is not really a timeout issue. Even after increasing the “Data Migration Timeout” from default 5 mins to maximum 1440 mins( 24 hrs), the migration of big tables still fails.

SOLUTION

Subscribe to get access

Read more of this content when you subscribe today.

Subscribe