Mastering Schema Conversion with AWS SCT and DMS

This study guide focuses on the critical task of transforming database schemas and code objects to ensure compatibility when migrating data to AWS, specifically focusing on heterogeneous migrations using the AWS Schema Conversion Tool (SCT) and AWS DMS Schema Conversion.

Learning Objectives

After studying this guide, you should be able to:

• Differentiate between AWS SCT and AWS DMS Schema Conversion.
• Generate and interpret Migration Assessment Reports to identify manual conversion efforts.
• Identify which database objects (tables, views, stored procedures) are automatically converted.
• Explain the workflow for migrating Teradata BTEQ scripts to Amazon Redshift RSQL.
• Determine when to use a replication instance versus a schema conversion tool.

Key Terms & Glossary

• Heterogeneous Migration: A migration where the source and target database engines are different (e.g., Oracle to Amazon Aurora).
• AWS SCT (Schema Conversion Tool): A standalone desktop application used to convert database schemas and application code.
• CDC (Change Data Capture): A process that monitors and captures inserts, updates, and deletes from a source database in near real-time.
• Assessment Report: A document generated by SCT/DMS that summarizes the complexity of a migration and highlights items that cannot be converted automatically.
• RSQL: A terminal-based front-end to Amazon Redshift, used as a target for converted Teradata scripts.

The "Big Idea"

Schema conversion is the architectural bridge of a database migration. While tools like AWS DMS handle the heavy lifting of moving the actual rows of data, they are not optimized for redesigning the structure (the schema). AWS SCT and DMS Schema Conversion act as the "translator," ensuring that the logic, constraints, and data types of a legacy system (like an on-premises Oracle DB) make sense in a modern cloud environment (like Amazon Redshift).

Formula / Concept Box

Hierarchical Outline

1. Phase 1: Assessment
   • Connect to source and target endpoints.
   • Generate a Migration Assessment Report.
   • Analyze the "Action Items" for manual conversion.
2. Phase 2: Conversion
   • Automated conversion of tables, views, and functions.
   • Mapping data types (e.g., converting NUMBER to DECIMAL).
   • Teradata Specifics: Converting BTEQ scripts to RSQL.
3. Phase 3: Application
   • Apply the converted schema to the target database.
   • Verify the structure and constraints.
4. Phase 4: Data Migration
   • Use AWS DMS for the full load and CDC for ongoing replication.

Visual Anchors

Migration Workflow

Schema Complexity Visualization

\begin{tikzpicture} \draw[thick,->] (0,0) -- (6,0) node[anchor=north] {Migration Complexity}; \draw[thick,->] (0,0) -- (0,4) node[anchor=east] {Manual Effort}; \draw[blue, thick] (0.5,0.5) .. controls (2,0.8) and (4,2.5) .. (5.5,3.5); \node at (1,0.2) [scale=0.8] {Tables}; \node at (3,1.2) [scale=0.8] {Views/Procs}; \node at (5,3.5) [scale=0.8] {Proprietary Code}; \filldraw[red] (5.5,3.5) circle (2pt) node[anchor=south west] {Manual Intervention Required}; \end{tikzpicture}

Definition-Example Pairs

• Object Mapping: The process of defining how source schema objects relate to target schema objects.
  • Example: Mapping a case-sensitive source schema to a case-insensitive target schema in Amazon Redshift.
• Script Conversion: Automatically rewriting SQL scripts from one dialect to another.
  • Example: AWS SCT automatically converting Teradata BTEQ (Basic Teradata Query) scripts into RSQL for use with Redshift.

Worked Examples

Scenario: Migrating Oracle to Amazon Redshift

1. Step 1 (Assessment): Use AWS SCT to connect to the Oracle instance. SCT identifies that 90% of tables convert automatically, but 10 Oracle-specific stored procedures use PL/SQL features not supported by Redshift.
2. Step 2 (Conversion): Click "Convert Schema." SCT creates a local representation of the Redshift schema. For the 10 procedures, SCT marks the code with comments explaining why the conversion failed.
3. Step 3 (Manual Fix): The Data Engineer rewrites the 10 procedures using Redshift-supported SQL or Python UDFs.
4. Step 4 (Apply): Select "Apply to Database" to create the tables and fixed procedures in the Redshift cluster.

Checkpoint Questions

1. Why is AWS SCT recommended over DMS for the actual schema conversion process?
   • Answer: SCT is optimized for performance and provides more detailed analysis of complex code objects compared to the basic schema creation capabilities of DMS.
2. What is the first document you should review before starting a schema conversion?
   • Answer: The Migration Assessment Report.
3. True or False: AWS DMS can perform continuous data replication without a prior schema conversion.
   • Answer: True, but it is not recommended for heterogeneous migrations as DMS may not create optimal indexes or data types on its own.

Comparison Tables

Muddy Points & Cross-Refs

• Zero-ETL vs. DMS: Note that if a Zero-ETL integration is available (e.g., Aurora to Redshift), you should use that instead of DMS/SCT, as it handles the pipeline automatically with less overhead.
• SCT vs DMS SC: If you are already using the DMS console for a simple migration, DMS Schema Conversion is more convenient. If you have complex application code or proprietary scripts (like Teradata BTEQ), use the AWS SCT Desktop Tool.