# Database Migration Planning and Assessment

> Chapter 5 of the Courseiva AWS-DATABASE-SPECIALTY curriculum — https://courseiva.com/learn/aws-database-specialty/database-migration-planning

**Official objective:** 4.1 — Plan and prepare database migrations to AWS using tools like AWS DMS and AWS Schema Conversion Tool

## Introduction

Without proper planning, a database migration can silently corrupt your data, cause days of application downtime, or even fail completely, leaving your business stranded. That is why the AWS Certified Database Specialty exam demands you master the art of evaluating your existing environment and choosing the right migration tools and strategies. This chapter will give you a framework to assess your current database, plan the journey to AWS, and select the correct services – like AWS DMS and the Schema Conversion Tool – without guesswork.

## The Moving House Analogy

Your current house, filled with furniture, boxes, and years of accumulated belongings, represents your on-premises database. The neighbourhood is your data centre. You have decided to move to a new house in a different city – that city is AWS. But this isn't a simple move. You cannot just pick up the house and carry it. Everything must be carefully planned, packed, transported, and unpacked in the new location, without breaking anything or losing items. 

First, you must assess everything you own. That old sofa (a legacy database schema), the fragile china (sensitive customer data), and the heavy exercise equipment (large tables) all require different handling. You draw a floor plan of your new house (your target AWS database architecture) and decide which room each item goes into. This is the assessment and planning phase. You cannot start moving until you know exactly what you have and where it is going. 

Next, you choose your moving method. Will you hire professional movers (AWS Database Migration Service, DMS) to continuously move items while you still live in the house (minimising downtime)? Or will you box everything up, shut the doors, and move it all in one go (a one-time, offline migration)? For some items, you might need to disassemble them first (schema conversion using AWS SCT) to fit through the new doorways. Throughout the move, you create an inventory list, track which boxes are in transit, and test that nothing is damaged upon arrival. The entire operation is a 'Database Migration Planning and Assessment', and without a solid plan, you will end up with a sofa in the kitchen and a broken lamp.

## Core explanation

Database migration is the process of moving your database from one location to another, typically from on-premises (in your own data centre) or from another cloud provider, into AWS. For the DBS-C01 exam, you need to understand the planning and assessment phase, which is the critical first step before any data actually moves. 

First, you must know what you are migrating. This means performing a thorough 'discovery' and 'assessment'. You need to identify every database in your environment, not just the obvious ones. This includes legacy databases, test databases, and even spreadsheets masquerading as databases. For each database, you must document: the 'database engine' (e.g., Oracle, Microsoft SQL Server, MySQL, PostgreSQL), the 'version' (e.g., Oracle 11g, SQL Server 2012), the 'size' (storage used and data volume), the 'schema' (the structure of tables, indexes, views, and stored procedures), and the 'network connectivity' between the source and target. 

The assessment phase answers a crucial question: is a 'homogeneous migration' or a 'heterogeneous migration' possible? A homogeneous migration means moving from one database engine to the same engine, for example, from Oracle on-premises to Oracle on Amazon RDS (Relational Database Service). This is simpler because the database speaks the same language. A heterogeneous migration means moving to a different engine, for example, from Oracle to Amazon Aurora (AWS's own database engine). This requires converting the database schema and code, which is where the AWS Schema Conversion Tool (SCT) comes in. 

AWS DMS (Database Migration Service) is the workhorse that actually moves the data. For planning purposes, you must understand its modes. A 'full load' migration copies all existing data once. A 'continuous replication' (or CDC – Change Data Capture) migration applies ongoing changes from the source to the target after the full load, keeping them in sync with minimal downtime. The assessment must determine which mode is suitable based on your downtime tolerance. 

You also need to assess the 'network'. The source database must have network connectivity to the AWS DMS replication instance. This usually involves setting up a VPN (Virtual Private Network) or a Direct Connect (a dedicated private network link). The bandwidth must be sufficient to handle the data volume within your allowed migration window. 

Finally, the assessment must include 'compatibility checks'. Not every feature of your old database exists in the target database. For instance, Oracle's hierarchical queries using 'CONNECT BY' must be rewritten for PostgreSQL. The SCT analyses your source schema and generates a report of action items, flagging incompatible objects. 

Why does this matter for the DBS-C01 exam? The exam will present scenarios where you must choose the correct assessment tool (SCT for schema conversion, DMS Fleet Advisor for recommendations), identify the correct migration method (homogeneous vs. heterogeneous), and recognise when you need additional steps like converting stored procedures. It tests your ability to plan for challenges like large databases, limited bandwidth, and complex schemas, rather than just running a tool blindly.

## Real-world context

Consider a company called 'GlobalRetail Inc.', which runs its e-commerce platform on a legacy Oracle 11g database in its own data centre. The database is 2 TB in size, contains 500 tables, and 200 stored procedures. The company wants to move to Amazon Aurora PostgreSQL to save on licensing costs and gain scalability. A junior DBA is tasked with the migration. Without planning, they might just point AWS DMS at the Oracle instance and hope for the best. That would fail. 

Here is what a real-world assessment and planning process looks like: 

1. Kick-off and Discovery: The team identifies all databases. They discover three more smaller databases (a reporting tool, a logging database, and a development copy) that must also be migrated. They use AWS SCT to analyse the Oracle schema. The SCT report shows that 50 tables have incompatible data types (e.g., Oracle-specific 'ROWID' usage) and 20 stored procedures contain Oracle-specific PL/SQL code that must be rewritten for PostgreSQL's PL/pgSQL. 

2. Network Assessment: They check the bandwidth between their data centre and AWS. The link is a 1 Gbps connection. A 2 TB full load at 80% utilisation would take approximately 20000 seconds, or about 5.6 hours. This fits their six-hour maintenance window, but only if there are no other traffic demands. They decide to schedule the full load during a low-traffic period and use DMS CDC to catch up on changes made during the load. 

3. Schema Conversion and Refactoring: The DBA uses AWS SCT to generate a conversion report. The tool auto-applies many conversions but flags the 20 stored procedures as needing manual rewriting. The team prioritises these rewrites. They also must handle the 'ROWID' usage, which PostgreSQL does not have. They replace it with a unique primary key on the relevant tables. 

4. Migration Strategy: They choose a 'homogeneous' target? No – it is heterogeneous (Oracle to Aurora PostgreSQL). They plan to: a) Run SCT to convert the schema into a PostgreSQL-compatible DDL (Data Definition Language) script. b) Create the target database schema in Aurora using that script. c) Start a 'full load + CDC' task in AWS DMS to copy historical data and then continuously replicate changes. d) After validation, cut over the application by pointing it to the new Aurora endpoint. 

5. Validation and Testing: Before going live, they run data validation scripts to ensure row counts and key data points match between Oracle and Aurora. They also test the application against the new database in a staging environment. 

The entire process – from discovery to validation – is documented in a 'Migration Plan'. This plan becomes the blueprint for execution and the key deliverable the team presents to management. Without this assessment, the migration would be a disaster.

## Exam focus

The DBS-C01 exam focuses heavily on the 'why' and 'how' of migration planning, not just the 'what'. You will not be asked to write a command line, but you will be questioned on selecting the best tool and strategy for a given scenario. 

Key concepts the exam tests about planning and assessment: 

- **Homogeneous vs. Heterogeneous**: You must know which scenario requires AWS SCT. If the source and target are the same engine (e.g., MySQL to MySQL), you do not need SCT. If they are different (e.g., Oracle to Aurora PostgreSQL), you must use SCT. A common trap question says: 'You are migrating an Oracle database to Amazon RDS for Oracle. What tool should you use?'. The answer is AWS DMS; SCT is not required. 

- **Online vs. Offline**: The exam will ask about minimising downtime. 'Continuous replication' (CDC) is for near-zero downtime. 'Full load' is for offline migrations where downtime is acceptable. You must know that CDC requires source database configuration (like binary logs in MySQL or archive logs in Oracle). 

- **AWS SCT Capabilities**: SCT does not just convert schemas. It also generates a **'Action Items' report** that tells you what needs manual intervention. The exam loves this report. You must know that SCT handles data type mapping and code conversion, but it cannot rewrite custom application code that runs outside the database. 

- **DMS Fleet Advisor**: This is a relatively newer service for discovery. It can scan your network to find databases and recommend target AWS database engines. The exam may test that this is an alternative to manual discovery. 

- **Network and Bandwidth Planning**: The exam will present scenarios with very large databases (e.g., 10 TB) and limited bandwidth (e.g., 100 Mbps). You must recognise that a full load may take too long. Solutions include using AWS Snowball (a physical data transfer device) for the initial full load, then using DMS for continuous replication. 

- **Migration Statuses**: DMS tasks have statuses like 'running', 'stopped', 'failed', 'completed'. The exam tests that 'failed' requires checking the DMS task logs or the CloudWatch logs for error details. 

- **Traps**: A common trap is offering a solution like 'Use AWS EC2 to install Oracle' when the question asks for a 'managed service' like Amazon RDS. Another trap is suggesting SCT for a homogeneous migration. Always read the scenario carefully: are they moving to a different engine or the same? 
- **Key memorisation points**: Know the default ports for common databases (Oracle: 1521, MySQL: 3306, SQL Server: 1433, PostgreSQL: 5432). Know that DMS needs a source database user with READ privileges. Know that for CDC, DMS needs additional privileges (like SELECT on the redo log or binary log). 
- **Task ordering**: The exam presents a sequence of steps: 1) Assess with SCT, 2) Create target schema, 3) Start DMS full load, 4) Start DMS CDC, 5) Validate, 6) Switch application. Any answer that suggests starting the DMS task before running SCT is wrong.

## Step by step

1. **1. Discovery and Inventory** — Identify every database in your environment, including versions, sizes, schemas, and network connectivity. Use manual surveys or cloud discovery tools like AWS DMS Fleet Advisor. This step ensures you do not miss a critical database that could derail the migration.
2. **2. Schema Analysis and Conversion Planning** — Run the AWS Schema Conversion Tool (SCT) on each source database. SCT analyses the schema, data types, stored procedures, and functions, and generates a target-compatible schema plus an Action Items report. This tells you exactly what must be rewritten manually.
3. **3. Network and Bandwidth Assessment** — Measure the available network bandwidth between the source and the target AWS environment. Calculate the time needed for a full load. If it exceeds your maintenance window, plan for solutions like AWS Snowball or multiple parallel DMS tasks.
4. **4. Choose Migration Method and Tools** — Decide between homogeneous (same engine, DMS only) or heterogeneous (different engine, DMS + SCT). Also decide on offline (full load) or online (full load + CDC). This decision drives all subsequent setup.
5. **5. Prepare Source and Target Environments** — Configure the source database for DMS access (create a user with necessary privileges). Set up the target database (e.g., Amazon RDS or Aurora) with the converted schema. Ensure network connectivity, security groups, and VPN/Direct Connect are operational.
6. **6. Execute Migration and Validate** — Start the DMS task (full load, then CDC if enabled). After the full load, run validation jobs (DMS built-in validation) or custom scripts to compare row counts and key columns. Only then cut over the application to the target database.

## Comparisons

### Homogeneous Migration vs Heterogeneous Migration

**Homogeneous Migration:**
- Same database engine source and target (e.g., MySQL to RDS MySQL)
- Does not require AWS SCT for schema conversion
- Simpler and faster, but may not address licensing cost reduction

**Heterogeneous Migration:**
- Different database engine source and target (e.g., Oracle to Aurora PostgreSQL)
- Requires AWS SCT to convert schema and code
- More complex upfront, but often reduces costs and adds features

### Full Load Migration (Offline) vs Full Load + CDC (Online)

**Full Load Migration (Offline):**
- Copies all data once; source must be quiescent or offline
- Short migration window (hours or days) is acceptable
- No ongoing replication after load; target becomes stale immediately

**Full Load + CDC (Online):**
- Copy data initially, then continuously replicate changes
- Near-zero downtime required for applications
- Keeps target in sync until manual cutover, reducing rollback risk

### AWS DMS vs AWS SCT

**AWS DMS:**
- Moves data; cannot convert schema or code
- Works with networks, VPNs, and Direct Connect
- Supports both full load and CDC modes

**AWS SCT:**
- Converts schema, stored procedures, and code; does not move data
- Generates an 'Action Items' report for manual fixes
- Used during the planning and assessment phase before data is moved

## Diagram

_A decision flow from legacy database discovery through assessment, tool selection (SCT for heterogeneous, DMS for data), to validation and cutover._

```mermaid
flowchart TD
    A[Start: Legacy Database On-Prem] --> B{Discovery & Assessment}
    B --> C[Assess Schema & Code with AWS SCT]
    B --> D[Assess Network & Bandwidth]
    B --> E[Identify Databases & Versions]
    C --> F{Is it Homogeneous?}
    F -->|Yes| G[Use AWS DMS Only]
    F -->|No - Heterogeneous| H[Convert Schema with AWS SCT]
    H --> I[Create Target Schema in AWS]
    G --> J[Create DMS Replication Instance]
    I --> J
    D --> J
    J --> K[Start Full Load + CDC]
    K --> L[Validate Data Integrity]
    L --> M{Cut Over Application to AWS}
```

## Common misconceptions

- **Misconception:** You only need to assess the main production database because all other databases are copies. **Reality:** You must assess every database, including development, test, and reporting instances, because they may have unique schemas, dependencies, or custom code that complicates the migration. (Beginners think only the 'main' database matters, but in real-world environments, peripheral databases often hold unique data or are tightly coupled to the main one, and failing to plan for them can block the entire project.)
- **Misconception:** AWS DMS can automatically convert schema from Oracle to PostgreSQL without using SCT. **Reality:** AWS DMS only moves data. It cannot convert schema structure, data types, or stored procedures. You must use the AWS Schema Conversion Tool (SCT) separately to handle the schema conversion. (The name 'Migration Service' sounds like it does everything, so people assume it includes conversion. The exam specifically tests that DMS and SCT are separate services.)
- **Misconception:** Continuous replication (CDC) does not require any special configuration on the source database. **Reality:** CDC requires the source database to have change capture enabled (e.g., binary logs for MySQL, archive logs for Oracle). Without these, DMS cannot track ongoing changes. (Beginners think 'continuous' means it magically sees all changes. In reality, the source must be configured to expose those changes.)
- **Misconception:** A heterogeneous migration is always riskier and harder than a homogeneous one, so homogeneous is always the best choice. **Reality:** Homogeneous is easier technically, but may cost more due to licensing. Heterogeneous, while more work upfront, can save significant money and offer better performance on modern engines like Aurora. (People default to 'easy' without considering business drivers like cost. The exam tests that you understand the trade-off between effort and cost.)

## Key takeaways

- Always perform a discovery and assessment of all databases before any migration, including non-production instances.
- Use the AWS Schema Conversion Tool (SCT) only for heterogeneous migrations where the source and target database engines differ.
- AWS DMS moves data; it does not convert schemas. Schema conversion is a separate step handled by SCT.
- Continuous replication (CDC) in DMS requires the source database to be configured with transaction logs or binary logs enabled.
- For very large databases with limited network bandwidth, use AWS Snowball for the initial full load, then DMS for ongoing replication.
- The SCT Action Items report identifies objects that require manual remediation after conversion, such as unsupported data types or stored procedures.
- Validating data integrity (row counts, checksums) after migration is a critical step before cutting over the application.

## FAQ

**What is the difference between AWS DMS and AWS SCT?**

AWS DMS (Database Migration Service) moves the actual data from source to target. AWS SCT (Schema Conversion Tool) converts the database schema, code, and objects (like tables and stored procedures) from one engine to another. You typically use both for a heterogeneous migration.

**Do I need a VPN to use AWS DMS?**

Not always, but often. Your source database must be reachable by the DMS replication instance. If the source is on-premises, you either need a VPN connection, AWS Direct Connect, or a public endpoint with appropriate security. If the source is already in AWS (e.g., EC2), they can be in the same VPC.

**What is CDC in AWS DMS?**

CDC stands for Change Data Capture. It is a feature of AWS DMS that continuously replicates ongoing changes from the source database to the target after an initial full load. This enables near-zero downtime migration because the target stays in sync until you switch applications.

**Can I migrate a database from on-premises to AWS using only AWS SCT?**

No. AWS SCT only converts the schema and code; it does not move the data. You must use AWS DMS (or another data transfer method) to actually copy the data after the schema is converted.

**What happens if my database migration fails?**

AWS DMS tracks the task's state. If it fails, you can check the DMS task logs or CloudWatch logs for error details. Common causes include network issues, user permission problems, or incompatible data. You can fix the issue and resume the task from where it left off, usually without restarting the entire load.

**How do I know if my database schema is compatible with the target engine?**

Run the AWS Schema Conversion Tool (SCT). It will analyse your source schema and produce a detailed report ('Action Items') that lists everything that is incompatible or requires manual rewrite. It also automatically converts most objects.

## Check your understanding

1. **You are migrating an on-premises Microsoft SQL Server database to Amazon RDS for SQL Server. Which AWS service is required for the schema conversion?**

   Answer: None. This is a homogeneous migration (SQL Server to SQL Server), so AWS SCT is not required. AWS DMS alone can handle the data transfer.

2. **Your company needs to migrate a 5 TB Oracle database to Amazon Aurora PostgreSQL with minimal downtime. What two services should you use together?**

   Answer: You should use AWS SCT to convert the Oracle schema to PostgreSQL, and then AWS DMS with continuous replication (CDC) to move the data and apply ongoing changes.

3. **The AWS SCT generates a report listing several stored procedures that cannot be converted automatically. What is this report called?**

   Answer: The 'Action Items' report. It details the objects that require manual intervention after the automatic conversion.

4. **You have only a 100 Mbps connection between your data centre and AWS, and you need to migrate a 10 TB database. What additional AWS service or feature could you use to accelerate the initial full load?**

   Answer: You could use AWS Snowball (a physical appliance) to transfer the initial data, then use AWS DMS with CDC to replicate ongoing changes.

5. **Why must you configure binary logging on a MySQL source database when using AWS DMS with CDC?**

   Answer: AWS DMS reads the binary logs (binlogs) to capture and replicate ongoing changes to the target. Without them, CDC cannot work.

---

Interactive version with quiz and diagrams: https://courseiva.com/learn/aws-database-specialty/database-migration-planning