# Migrating Databases and Data Stores to AWS

> Chapter 13 of the Courseiva AWS-SAP-C02 curriculum — https://courseiva.com/learn/aws-sap-c02/migrating-databases-and-data-stores

**Official objective:** 4.2 — Design the migration of databases and data stores

## Introduction

Domain 4 of the SAP-C02 exam focuses on designing migration of databases and data stores. This chapter is about understanding how to move an organisation's data from its existing location — typically a physical server in a company basement (on-premises) or another cloud — into AWS without losing data, breaking applications, or causing unacceptable downtime. For an SAP-C02 candidate, mastering these migration patterns is critical because the exam loves to test your ability to choose the right tool and strategy for different business constraints, such as acceptable downtime, database engine compatibility, and data volume.

## The Moving House Analogy

Your family home contains all your stuff: furniture in the living room, pots in the kitchen, clothes in the wardrobe, photos in the attic. This house is your current on-premises database. The data store is every drawer and cupboard. Now you are moving to a new house — the AWS cloud. You cannot just snap your fingers and teleport everything perfectly arranged. You have decisions to make.

Option one: lift the entire house onto a giant truck and drop it on the new plot. In AWS this is called a "homogeneous migration" — you take your entire database engine (for example, Oracle) and move it unchanged to an Amazon RDS instance running the same engine. The house keeps its internal layout.

Option two: sell all your old furniture and buy new, better-designed pieces that fit the new house perfectly. This is a "heterogeneous migration" or "database re-platforming". You move the data but swap the engine from Oracle to Amazon Aurora, like changing from oak dining chairs to lightweight, foldable ones that are easier to stack.

Option three: pack delicate heirlooms in bubble wrap and carry them yourself. That is the AWS Database Migration Service (DMS) doing a continuous replication — moving data in batches while your old house is still lived in, keeping the new one synced so when you finally turn off the lights at the old place, the new one is ready to go.

Each approach has trade-offs in cost, downtime, and effort. The best choice depends on what you own, what you are willing to let go of, and how long you can bear living out of boxes.

## Core explanation

This section explains the core concepts you need to understand for migrating databases and data stores to AWS. We will define each term as we go, using plain language.

**What is a database?** A database is a structured collection of data stored electronically. Think of it as a digital filing cabinet with labelled drawers, folders, and a consistent way to find and update information. A relational database (like MySQL, PostgreSQL, Oracle, or SQL Server) stores data in tables with rows and columns, connected by relationships. A non-relational database (like Amazon DynamoDB or MongoDB) stores data in documents, key-value pairs, or graphs — more like a giant pile of labelled boxes.

**What is a data store?** This is a broader term for any system that stores data, including file systems (like Amazon S3 — Simple Storage Service, for object storage), block storage (like Amazon EBS — Elastic Block Store, for virtual hard drives), and databases. The SAP-C02 exam expects you to consider all types of data stores when designing a migration.

**Why migrate?** Companies move databases to AWS for several reasons: they want to reduce hardware costs, improve scalability (ability to handle growth), increase security, or offload maintenance tasks like patching and backups. The exam assumes migration is driven by business requirements.

**The key services you need to know:**

- **AWS Database Migration Service (DMS):** This is the primary tool for migrating databases to AWS. DMS can move data from a source database to a target database with minimal downtime. It works as a software appliance that you run on an Amazon EC2 instance (a virtual server). DMS connects to your source database, reads the data, and writes it to the target. It can perform a one-time full load or a continuous replication (called ongoing replication or CDC — Change Data Capture) where it captures changes as they happen at the source and applies them to the target. This keeps the target nearly in sync with the source, allowing you to cut over with very little downtime.

- **AWS Schema Conversion Tool (SCT):** When you are migrating between different database engines (heterogeneous migration), you need to convert the database schema — the structure of tables, indexes, views, and stored procedures. A PostgreSQL database does not use exactly the same SQL syntax as Oracle. SCT analyses your source database schema, generates reports on incompatibilities, and converts the schema to the target engine format. It also assesses any code (like stored procedures) that needs manual rewriting.

**Types of migrations:**

- **Homogeneous migration:** The source and target use the same database engine (for example, Oracle to Amazon RDS for Oracle). This is simpler because the schema does not need conversion. DMS can often handle this directly.

- **Heterogeneous migration:** The source and target use different engines (for example, Oracle to Amazon Aurora PostgreSQL, or SQL Server to MySQL). This requires SCT to convert the schema, and you may need to rewrite some application code because the new engine may not support all the same features.

- **Re-platforming (also called lift, tinker, and shift):** You move the database to AWS but make moderate changes — for instance, moving from a self-managed Oracle database on EC2 to Amazon RDS for Oracle, which handles backups and patching for you.

**Other important concepts:**

**Cutover** is the moment when you stop using the source database and start using the target in AWS. Your goal is to minimise the time between stopping writes on the source and resuming writes on the target. With DMS ongoing replication, cutover can be as short as a few minutes.

**Full load plus CDC** is the typical pattern: DMS first performs a full load of all existing data, then continuously applies incremental changes (CDC) to keep the target synced. When you are ready, you stop the source application, let the final CDC catch up, and then redirect the application to the target.

**Validation** is a feature of DMS that compares data between source and target to ensure no data is lost or corrupted during migration.

**The 4 pillars of a good migration (AWS well-architected):** security, reliability, performance efficiency, cost optimisation. Every migration decision affects these pillars. For example, encrypting data in transit using TLS (Transport Layer Security) increases security; choosing a multi-AZ (availability zone) target increases reliability; choosing the right instance size for DMS optimises performance and cost.

**Common constraints you must consider:**

- **Downtime window:** How long can the application be offline? Some businesses allow zero downtime, requiring continuous replication. Others can tolerate a few hours overnight.

- **Data volume:** Migrating 10 GB is very different from migrating 10 TB. For large volumes, you might use AWS Snowball (a physical device for transferring data) or create a dedicated network link like AWS Direct Connect.

- **Source database version and engine:** Older database versions may not be supported by DMS or may require upgrades before migration.

- **Network connectivity:** The DMS replication instance must be able to connect to both the source and target databases. This often requires VPN (Virtual Private Network) connections or Direct Connect.

- **Security and compliance:** Data at rest and in transit must be encrypted. You may need to comply with regulations like GDPR or HIPAA, which impose restrictions on how data is moved and stored.

## Real-world context

Sarah is the lead database architect for a mid-sized financial services company. The company has operated for 15 years using a self-managed Oracle database running on a physical server in their office. The server is old, the hardware warranty expired two years ago, and the IT team spends every weekend patching and backing up the database. The CEO wants to move to AWS to reduce operational overhead and improve disaster recovery.

Sarah is studying for the SAP-C02 exam and decides this is the perfect test case. She does the following:

**Step 1 — Assessment and discovery:** Sarah runs the AWS Schema Conversion Tool (SCT) on the Oracle database to assess its size, complexity, and any features that would not work if she changed the engine. The SCT reports that the database uses Oracle-specific features like Materialised Views and several PL/SQL stored procedures. The report indicates that migrating to Amazon Aurora PostgreSQL would require rewriting about 20% of those procedures — that is significant effort. The business wants to minimise risk, so Sarah recommends a homogeneous migration to Amazon RDS for Oracle, keeping the same engine.

**Step 2 — Choose the migration tool:** For a homogeneous migration from Oracle to RDS for Oracle, DMS is the primary choice. Sarah creates a DMS replication instance — a small EC2 instance (size: dms.c5.large) in the same AWS region as the target. She sets up a VPN connection between the office network and the AWS VPC so DMS can talk to the source database. She also opens the firewall on the Oracle server to allow inbound connections from the DMS instance IP.

**Step 3 — Full load:** Sarah configures a DMS task to perform a full load of all tables. The database is 500 GB. The full load takes approximately 8 hours over their 100 Mbps internet connection. During this time, the source database continues running normally — users are actively trading and updating accounts. DMS reads a snapshot of the data without locking tables (using Oracle's Flashback Query capability).

**Step 4 — Ongoing replication (CDC):** After the full load completes, Sarah enables ongoing replication. DMS now captures every INSERT, UPDATE, and DELETE on the source database and applies them to the target. The latency between source and target stays under 2 seconds — the company's compliance team approves this as acceptable.

**Step 5 — Cutover planning:** The cutover window is scheduled for Saturday at 2:00 AM when trading is closed. Sarah stops the application, waits for CDC to catch up (it takes 30 seconds because activity is low), and then stops the DMS task. She changes the DNS (Domain Name System) records in Route 53 (AWS's DNS service) to point the application's database connection string to the new RDS endpoint. She restarts the application. It connects to RDS successfully. Data integrity checks (using DMS validation) confirm zero data loss.

**Step 6 — Post-migration:** The team decommissions the old Oracle server. Sarah creates automated backups (RDS snapshots) and configures Multi-AZ for high availability. She also sets up AWS CloudWatch alarms to monitor database performance. The company saves 40% on monthly infrastructure costs and eliminates the weekend patching burden.

**What if Sarah had chosen heterogeneous migration?** If the business had insisted on moving to Aurora PostgreSQL to reduce licensing costs, Sarah would have used SCT to convert the schema and manually rewritten the PL/SQL procedures into PostgreSQL-compatible code. She would have run the DMS task with extra column mapping rules because the data types differ between Oracle and PostgreSQL. That approach would have taken 3 weeks longer due to application testing requiring code changes.

**Key tools used in this scenario:**
- AWS DMS (database migration)
- AWS SCT (schema conversion)
- AWS VPN or Direct Connect (network connectivity)
- Route 53 (DNS cutover)
- AWS CloudWatch (monitoring)
- AWS KMS (encryption — optional but used here for compliance)

## Exam focus

The SAP-C02 exam focuses heavily on your ability to select the correct migration strategy and tool based on the scenario presented in the question. You will not be asked to memorise DMS command-line options. Instead, you will need to evaluate trade-offs between different approaches.

**Common question types:**

- **Scenario-based choice questions:** The exam gives a narrative about a company with a specific source database (e.g., on-premises MySQL 5.6), target preference (e.g., Aurora MySQL), constraints (e.g., “must minimise downtime, cannot change application code”), and asks which migration approach to use. The correct answer almost always involves AWS DMS with ongoing replication for low downtime, and SCT only if the engine changes.

- **When not to use DMS:** A frequent trap is that DMS cannot migrate to a database that is not supported as a target. For example, DMS does not support migrating directly to Amazon Redshift from an on-premises database using built-in replication — you would use a staging target (like Amazon S3) first. The exam tests whether you know which engines are supported.

- **Schema conversion vs. data migration:** You will see questions that mix up SCT and DMS. Remember: SCT handles schema conversion (structure), DMS handles data movement. A question might say “the company wants to move from Microsoft SQL Server to Amazon RDS for PostgreSQL” — you need both SCT (for schema) and DMS (for data). If the question says “same engine,” you only need DMS.

- **Large data volumes (>1 TB):** When the data is too large to migrate over the internet within the allowed time, AWS recommends using AWS Snowball (a physical device) or AWS Direct Connect (dedicated network link) for the full load, then DMS for ongoing CDC. The exam loves this pattern because it tests your understanding of network constraints.

- **Data validation traps:** The exam may present a scenario where after migration, the application reports missing data. DMS validation can identify mismatches. Some organisations skip validation to save time — do not suggest that. Validation is always recommended.

**Trap patterns to watch out for:**

- **DMS ongoing replication requires a source database that supports CDC.** Not all databases support CDC by default. For example, older versions of MySQL require specific configuration (binary logs enabled). If the scenario does not mention it, assume it is configured — but the trap is that the candidate forgets to check source prerequisites.

- **SCT does not migrate data.** A common wrong answer suggests using SCT for moving data. That is incorrect. SCT only converts the schema. The data movement is DMS's job.

- **Mixing up homogeneous and heterogeneous:** The exam might describe a migration from Oracle to Amazon RDS for Oracle and mention “use SCT to convert the schema”. That is unnecessary because the schema is the same engine — it would be wasteful. So the correct answer would omit SCT.

- **Cutover timing:** Questions about minimising downtime often expect you to choose “full load + CDC” rather than just a full load. The trap answer is “take a backup, restore to RDS, then point the application” — that has high downtime because the source must be offline for the entire backup/restore duration.

**Key concepts to memorise:**

- Homogeneous migration does not require schema conversion (SCT is not needed)
- Heterogeneous migration requires SCT and possibly code changes
- DMS supports full load and CDC
- AWS Snowball is for large data volumes when network is slow
- Direct Connect provides consistent, high-bandwidth network connection
- Use RDS custom endpoints for cutover control (less common but tested)
- DMS validation should be enabled to ensure data integrity
- Target databases include: RDS, Aurora, DynamoDB, S3, Redshift, DocumentDB, and more
- Source databases include: Oracle, MySQL, PostgreSQL, SQL Server, MongoDB, SAP ASE, and others

## Step by step

1. **Assess and Discover** — Run AWS SCT on the source database to identify schema complexity, size, and any incompatible features. This determines whether a homogeneous or heterogeneous migration is feasible. For example, if the source uses Oracle Spatial feature and the target is Aurora PostgreSQL, SCT will flag incompatibilities.
2. **Prepare Source and Network** — Configure the source database to support CDC (e.g., enable binary logs for MySQL, archive logging for Oracle). Establish network connectivity between the on-premises environment and AWS — typically via VPN or AWS Direct Connect — and ensure the DMS replication instance can reach both source and target databases.
3. **Create DMS Resources** — Provision a DMS replication instance (an EC2 instance managed by DMS) of appropriate size based on data volume. Define source and target endpoints in DMS with connection details and credentials. Create a DMS task specifying full load or full load plus CDC.
4. **Run Full Load and CDC** — Start the DMS task. It performs a full load of all existing data from source to target. For heterogeneous migrations, SCT must have already converted the schema. After full load, if ongoing replication is enabled, DMS captures changes (CDC) and applies them to the target, keeping the source and target in sync.
5. **Validate and Cut Over** — Enable DMS validation to compare source and target data for consistency. When ready, stop the source application, let the final CDC changes apply, stop the DMS task, and redirect the application's connection string to the new target database endpoint (often via DNS update in Route 53). Verify application functionality.

## Comparisons

### Homogeneous Migration vs Heterogeneous Migration

**Homogeneous Migration:**
- Source and target use the same database engine (e.g., Oracle to Oracle)
- No Schema Conversion Tool (SCT) needed
- Application code usually requires no changes

**Heterogeneous Migration:**
- Source and target use different engines (e.g., Oracle to Aurora PostgreSQL)
- SCT is required to convert the schema
- Application code must be rewritten for compatibility with new engine

### AWS DMS Full Load Only vs AWS DMS Full Load Plus CDC

**AWS DMS Full Load Only:**
- Copies all existing data once, source must be offline during load
- Higher downtime because cutover cannot happen during the load
- Simpler to configure: no CDC setup on source

**AWS DMS Full Load Plus CDC:**
- Copies existing data then continuously applies changes (CDC)
- Low downtime: cutover occurs in minutes after catching up CDC
- Requires configuring CDC on the source database (e.g., binary logs)

### Migrate over Internet (VPN) vs Migrate using AWS Snowball

**Migrate over Internet (VPN):**
- Data travels over your existing internet connection
- Cheapest option: no hardware rental cost
- Suitable for data volumes under 1 TB and fast network

**Migrate using AWS Snowball:**
- Data is physically shipped via a ruggedised device
- Cost includes device rental and shipping
- Suitable for multi-TB data when internet is slow or costly

## Diagram

_The overall workflow for migrating a database to AWS, showing the decision between homogeneous and heterogeneous migration using SCT and DMS._

```mermaid
flowchart TD
    A[On-Premises Database] -->|Assess with SCT| B{Same Engine?}
    B -->|Yes| C[Use DMS full-load + CDC]
    B -->|No| D[Use SCT to convert schema]
    D --> E[Rewrite code if needed]
    E --> C
    C --> F[Validate with DMS validation]
    F --> G[Cut over: update DNS]
    G --> H[Target Database in AWS (RDS, Aurora, DynamoDB)]
```

## Common misconceptions

- **Misconception:** The AWS Schema Conversion Tool (SCT) also migrates the actual data. **Reality:** SCT only converts the database schema (tables, indexes, views). The data migration is handled exclusively by AWS Database Migration Service (DMS) or by native backup/restore. (The tool name 'Schema Conversion Tool' suggests conversion of everything, but beginners confuse 'schema' (structure) with 'database' (structure + data).)
- **Misconception:** DMS can migrate databases from any on-premises source to any AWS target without any source-side configuration. **Reality:** DMS requires the source database to be configured for CDC (e.g., binary logs enabled for MySQL or archive logging for Oracle). Without this, DMS cannot capture ongoing changes. (Beginners assume that because it is a managed service, it automates everything. In reality, the source must be set up properly for the tool to work.)
- **Misconception:** When migrating between different database engines (e.g., Oracle to Aurora PostgreSQL), you can use DMS alone with a simple task. **Reality:** Heterogeneous migrations require SCT to convert the schema first, and often need manual rewriting of stored procedures, functions, and triggers. DMS alone will fail because the data types and SQL syntax differ. (Plenty of documentation markets DMS as 'the migration service' so people assume it handles everything. They do not realise schema conversion and code rewriting are separate, manual steps.)
- **Misconception:** The fastest migration approach is to take a full native backup (e.g., RMAN for Oracle), restore it to Amazon RDS, and then point the application to RDS. **Reality:** This approach requires significant downtime because the source database must be offline for the entire backup and restore process. For minimal downtime, the correct approach is DMS full load plus CDC, which allows the source to remain operational until just before cutover. (Native backup/restore is a natural, familiar pattern for database administrators. They underestimate how fast DMS CDC can cut over with near-zero downtime.)
- **Misconception:** AWS Snowball is only for large file transfers, not for database migrations. **Reality:** Snowball can be used for the initial full load of a database migration when the data volume is large (multiple TB) and the network is too slow. DMS can then perform CDC over the network afterward. (Because Snowball is primarily marketed as a 'data transport' for S3, people do not connect it to database migration workflows. But AWS explicitly references this pattern for large-scale migrations.)

## Key takeaways

- AWS DMS is the primary service for migrating data with minimal downtime, supporting both homogeneous and heterogeneous migrations.
- Use AWS SCT only for heterogeneous database migrations where the source and target engines differ; do not use it for same-engine migrations.
- DMS ongoing replication (Change Data Capture) reduces cutover downtime to minutes by keeping the target continuously synced with the source.
- For databases larger than 1 TB with limited network bandwidth, use AWS Snowball or Direct Connect for the initial full load, then DMS CDC for ongoing changes.
- Always enable DMS validation to verify that data is identical between source and target after migration, preventing silent data loss.
- The exam will test your ability to choose a migration strategy based on constraints: downtime window, data volume, engine compatibility, and security requirements.
- Homogeneous migrations require less effort than heterogeneous ones because no schema conversion or code rewriting is needed.
- After migration, update DNS entries (e.g., in Route 53) to point applications to the new database endpoint for a clean cutover.

## FAQ

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

AWS Database Migration Service (DMS) migrates actual data from source to target. AWS Schema Conversion Tool (SCT) converts the database schema (tables, indexes, functions) between different engines. You use SCT when the engine changes, and DMS always for data movement.

**Can I migrate from Oracle to Amazon Aurora using DMS without any other tool?**

No. Aurora uses either MySQL or PostgreSQL engines, whereas Oracle uses its own engine. This is a heterogeneous migration. You must use SCT to convert the schema (tables, indexes, stored procedures) and then DMS to move the data. The application code may also need changes.

**How do I perform a database migration with zero downtime?**

Use AWS DMS with full load plus CDC (Change Data Capture). DMS continuously applies changes from the source to the target. When you are ready for cutover, stop the source application, let CDC catch up (usually seconds to minutes), then redirect the application. The source remains operational until the final cutover instant.

**What do I do if my on-premises database is 10 TB and my internet connection is only 50 Mbps?**

A full load over the internet would take too long (over 20 days). Use AWS Snowball to physically ship the data to AWS for the initial full load, or use AWS Direct Connect for a dedicated high-bandwidth link. After the initial load, DMS can handle CDC over a slower connection because the data volume per second is small.

**Does DMS support migrating to Amazon DynamoDB?**

Yes, DMS supports Amazon DynamoDB as a target. You can migrate from relational databases like Oracle or MySQL to DynamoDB. However, you must use SCT to convert the relational schema into a DynamoDB-compatible format (tables become items, keys become partition and sort keys).

**What happens if my source database does not support Change Data Capture (CDC)?**

If CDC is not supported, DMS can still perform a one-time full load. However, the source must be offline during the full load to ensure consistency, because changes made during the load will not be captured. For minimal downtime, always configure CDC if your source engine supports it (e.g., enable binary logs for MySQL, archive logs for Oracle).

## Check your understanding

1. **A company is migrating from on-premises MySQL to Amazon Aurora MySQL and wants to minimise downtime. Which AWS services should they use?**

   Answer: AWS DMS with ongoing replication (CDC). Since it is a homogeneous migration (MySQL to MySQL-compatible Aurora), no Schema Conversion Tool is needed.

2. **What is the primary function of AWS Schema Conversion Tool (SCT) in a database migration?**

   Answer: SCT converts the database schema (tables, indexes, views, stored procedures) from one database engine to another, for example from Oracle to PostgreSQL. It does not migrate data.

3. **When would you use AWS Snowball in a database migration scenario?**

   Answer: When the data volume is very large (multiple terabytes) and the network bandwidth is too slow to transfer the data within the allowed time window. Snowball provides a physical transport alternative.

4. **True or False: AWS DMS can migrate data between different database engines without requiring any additional schema conversion.**

   Answer: False. For heterogeneous migrations (different engines), the schema must be converted using SCT first. DMS only moves the data; it does not handle schema incompatibilities.

5. **What is the purpose of enabling DMS validation after a migration task?**

   Answer: DMS validation compares the data in the source and target databases to detect any mismatches, ensuring that data was migrated completely and accurately without corruption.

---

Interactive version with quiz and diagrams: https://courseiva.com/learn/aws-sap-c02/migrating-databases-and-data-stores