From On-Premise Databases to AWS: RDS, Aurora, Redshift, and Data Migration Explained

How Traditional Databases Actually Work

Before we jump into AWS RDS and Aurora, it’s worth pausing and understanding what a database really is under the hood and what it traditionally took to run one. Once this clicks, RDS and Aurora will make a lot more sense — you’ll clearly see exactly what problem AWS is solving for you.

The Three Layers of a Database System
Think of a database system as three layers stacked on top of each other:

• Disk — where data lives permanently
• RAM — fast, temporary memory used while processing queries
• Database engine — the software that manages both and connects to your application

Commonly Used Database Engines
MySQL, PostgreSQL, Oracle Database, Microsoft SQL Server, MariaDB

What Exactly Does a Database Engine Do?
The database engine is the actual software responsible for:

• Stores data on disk
• Executes SQL queries
• Manages transactions and concurrency
• Caches data in memory for speed

What It Takes to Set Up a Database On-Premise
Running a database in-house means procuring servers, installing and patching the OS and the engine, and manually configuring replication, failover, backups, and monitoring. None of this is one-time work — it has to be maintained continuously by a dedicated team for as long as the database runs.

The Simple Alternative: AWS RDS and Aurora
This is exactly what AWS RDS and Aurora eliminate. They run the same database engines you already know — but remove the manual, ongoing work involved in keeping them running:

• No hardware to manage — no procuring servers, racking, or capacity planning
• Automated patching — OS and engine patches are applied for you, on a schedule you control
• Built-in high availability — Multi-AZ failover is a checkbox, not a custom script
• Automated backups — point-in-time recovery is available out of the box
• Easy scaling — resize compute or storage in minutes, without manual server migrations
• Built-in monitoring — CloudWatch metrics (CPU, memory, connections) are available immediately, no setup required

In short: the database engine stays the same — MySQL is still MySQL — but AWS takes over everything around it.

RDS (Relational Database Service)

It is AWS’s managed service for running relational databases in the cloud. It’s not a database engine itself — it’s the platform that hosts engines like MySQL, PostgreSQL, Oracle, and SQL Server for you.

You just choose an engine, an instance size, and storage — and AWS handles the server, patching, backups, and monitoring.

Your application connects to it exactly like any traditional database — same drivers, same SQL, same connection string. Only the operational work behind the scenes has changed.

Supported Database Engines in RDS are MySQL, PostgreSQL, MariaDB, Oracle Database, and Microsoft SQL Server.

DB instance classes
A DB instance class defines the compute capacity of your RDS database — essentially, how much CPU, RAM, and network performance it gets. Choosing the right instance class directly affects your database’s performance, and it’s one of the first decisions you make when creating an RDS or Aurora database.

Storage Types
RDS storage determines how your database’s data is physically stored and how fast it can be read from or written to disk. Choosing the right storage type affects both performance and cost, and it’s configured separately from your instance class.

RDS offers three main storage types:

High Availability & Scaling
RDS offers two distinct features that are often confused with each other — Multi-AZ and Read Replicas. They solve different problems, and understanding the difference is important for real-world architecture decisions.

Backup & Recovery
RDS provides two types of backups, each serving a different purpose.

Security: RDS should sit in a private subnet, not reachable from the internet. Security groups act as a firewall, controlling which resources (like your Spring Boot app on EC2) can connect and on which port. RDS also supports encryption at rest (KMS) and in transit (SSL/TLS). IAM database authentication replaces hardcoded passwords with short-lived, AWS-generated tokens — a more secure alternative.

Parameter Groups vs Option Groups
Parameter groups control engine-level settings — things like max connections or memory allocation. Option groups enable extra features for specific engines, like encryption tools for Oracle or SQL Server. In short: parameter groups tune how the engine behaves; option groups turn on extra features.

Monitoring: RDS automatically sends metrics to CloudWatch — CPU usage, free memory, number of connections, and free storage space — so you can see database health without setting anything up yourself. Performance Insights goes a level deeper, showing which specific queries are consuming the most database load.

Maintenance: AWS applies OS and engine patches during a maintenance window you choose. If Multi-AZ is enabled, AWS patches the standby first, fails over to it, then patches the original primary — minimizing downtime during updates.

Pricing: You pay for RDS based on the instance running (on-demand, or cheaper with a Reserved Instance commitment), the storage you provision, and backup storage beyond the free allowance. If you use cross-region read replicas, you also pay for data transfer between regions.

Aurora

It is AWS’s own relational database engine, built specifically for the cloud. It comes in two flavors — Aurora MySQL and Aurora PostgreSQL — and is compatible with those engines at the protocol level. That means your existing SQL, JDBC drivers, and Spring Boot/JPA code work without any changes.

But under the hood, Aurora isn’t just “managed MySQL.” AWS re-architected how the database stores and replicates data.

How is Aurora different?
In a traditional engine (including standard RDS), the database engine and its storage are tightly coupled — one server holds both the compute and the disk. Aurora separates the two:

• Compute (the database engine) runs on one or more instances
• Storage is a separate, distributed layer that automatically replicates your data 6 times across 3 Availability Zones

This separation is what gives Aurora its key advantages:

• Faster failover — typically under 30 seconds, since a new instance can attach to the same shared storage instead of waiting for replication to catch up
• Auto-scaling storage — grows automatically up to 128TB, with no manual provisioning
• More read replicas — up to 15 Aurora Replicas (vs 5 for standard RDS), and they share the same underlying storage, so replication lag is much lower
• Better resilience — losing a copy of data (or even an entire AZ) doesn’t mean losing availability, since 6 copies exist across 3 AZs

Where Aurora Fits
If you’re already comfortable with MySQL or PostgreSQL, Aurora gives you the same interface with a more resilient, cloud-native storage layer underneath — usually at a higher cost than standard RDS, but with less manual tuning needed for performance and availability.

Aurora Cluster: Writer and Reader Instances
Unlike a standalone RDS instance, an Aurora database runs as a cluster — a group of instances that all share the same underlying storage layer.

Aurora Serverless v2
Aurora Serverless v2 automatically scales compute up and down based on actual traffic, instead of you choosing a fixed instance size. Capacity is measured in Aurora Capacity Units (ACUs) — you set a min/max range, and Aurora adjusts within it in real time.

Best for: unpredictable or spiky workloads, dev/test environments, or new applications where traffic patterns aren’t known yet. You pay based on actual usage rather than a fixed instance running 24/7.

Connecting to it works exactly like connecting to a regular Aurora cluster — same connection string, no application changes needed.

Aurora Global Database
Aurora Global Database replicates your data to up to 5 other AWS regions in under 1 second, letting users in those regions read data locally instead of round-tripping to the primary region.

Scenario: Your Aurora database runs in the US, but you have an India office. Without Global Database, every request from India travels to the US and back. With it, India reads from a local copy — much faster. Writes still go to the US primary, since only the primary accepts writes.

Disaster recovery: If the US region goes down entirely, you can promote the India secondary to become the new primary in about a minute, instead of losing your database until AWS restores the region.

Key limitation: Secondaries are read-only — writes always reach the primary first, either handled by your app or automatically via write forwarding.

Aurora Backup & Storage Behavior
Aurora handles backups and storage differently from standard RDS, with less manual overhead.

RDS versus Aurora

Amazon Redshift

The Core Problem Redshift Solves
Imagine your Loan system has been running for 3 years on RDS/Aurora, and the business asks:

“Show me total loan amount disbursed per region, per quarter, for the last 3 years.”

This query has to scan millions of rows and aggregate across them. Running it directly on RDS would be slow, and it would compete for resources with your live application — real customers checking their accounts could see slowdowns.

RDS/Aurora are built for many small, fast queries (fetch one customer, insert one loan) — not massive aggregations across millions of rows. This is exactly the gap Redshift fills.

Row-Based vs Column-Based Storage
This is the key reason Redshift is fast for analytics.

Take a simple employees table:

| id | name  | department  | salary |
|----|-------|-------------|--------|
| 1  | Alice | Engineering | 85000  |
| 2  | Bob   | Sales       | 60000  |
| 3  | Carol | Engineering | 90000  |

Query: SELECT department, SUM(salary) FROM employees GROUP BY department;
Row-based storage (RDS/Aurora) stores data row by row:

Row 1: [id=1, name=Alice, dept=Eng, salary=85000]
Row 2: [id=2, name=Bob, dept=Sales, salary=60000]

This is efficient when you want an entire row — like “get everything about customer #123.” But if you only need the salary column across a million rows, the engine still has to read every row in full, wasting effort on columns you don't need.

Get CodeCraft by Ramcharan’s stories in your inbox

Join Medium for free to get updates from this writer.

Remember me for faster sign in

Column-based storage (Redshift) stores data column by column instead:

id column:     [1, 2, ...]
name column:   [Alice, Bob, ...]
dept column:   [Eng, Sales, ...]
salary column: [85000, 60000, ...]

Now, summing salaries across a million rows only touches the salary column — nothing else. This makes aggregation-heavy queries much faster, and columns compress better too, since similar values sit together.

Redshift Cluster Architecture
A Redshift cluster consists of:

• Leader Node — receives your query, creates an execution plan, coordinates the compute nodes, and returns the final combined result. You never query compute nodes directly — always through the leader node.
• Compute Nodes — do the actual work, each holding a portion of your data. Your query gets split into pieces, and each compute node processes its slice of data in parallel.

How Data Actually Gets Into Redshift
Unlike RDS/Aurora, your application typically doesn’t write directly to Redshift in real time. Common patterns:

• COPY command — the standard way to bulk-load data, usually from files sitting in S3, in parallel across all nodes (fast)
• AWS Glue — a managed ETL (Extract, Transform, Load) service that can pull data from RDS/Aurora, transform it, and load it into Redshift on a schedule
• AWS DMS — can also be used for continuous replication from RDS/Aurora into Redshift
• Redshift Spectrum (below) — lets you skip loading entirely for some use cases

A realistic pattern for your Loan system: a nightly job exports yesterday’s loan_applications and accounts data from RDS/Aurora into S3, then COPY it into Redshift, where analysts and dashboards query the accumulated historical data.

//Query to load data in Redshift from S3 
COPY table_name
FROM 's3://bucket-name/path/to/file.csv'
IAM_ROLE 'arn:aws:iam::<account-id>:role/<role-name>'
CSV
IGNOREHEADER 1;

Redshift Spectrum
Redshift Spectrum lets you run SQL queries directly against data sitting in S3, without loading it into Redshift’s storage.

• Useful for huge historical datasets that would be expensive to store fully inside Redshift
• Redshift’s compute nodes do the processing, but the data stays in S3 (Parquet, ORC, CSV)
• Good middle ground: keep recent data in Redshift, keep historical data in S3, and query both together

Setting up Spectrum takes three steps:
1. Create an External Schema: Connects Redshift to the Glue Data Catalog, which holds metadata about your S3 data.

CREATE EXTERNAL SCHEMA spectrum_schema
FROM DATA CATALOG
DATABASE 'spectrum_db'
IAM_ROLE default
CREATE EXTERNAL DATABASE IF NOT EXISTS;

2. Create an External Table: Tells Redshift what the S3 data looks like. No data is moved; it’s just metadata pointing at S3.

CREATE EXTERNAL TABLE spectrum_schema.loan_disbursements_historical (
    loan_id BIGINT,
    region VARCHAR(50),
    risk_category VARCHAR(20),
    disbursed_amount DECIMAL(15,2),
    disbursed_date DATE
)
STORED AS PARQUET
LOCATION 's3://my-bank-demo-bucket/historical-loans/';

3. Query the Data Present in S3: Run a normal SQL query against the external table; Redshift reads the data directly from S3 on the fly.

Redshift Serverless
Similar concept to Aurora Serverless v2 — Redshift also offers a serverless option where compute capacity automatically scales based on query load, and you pay based on usage rather than maintaining an always-on cluster. Good fit for sporadic reporting workloads (e.g., a monthly finance report) where a permanently running cluster would be wasteful.

Distribution Keys and Sort Keys
Redshift spreads your table’s data across multiple compute nodes. Distribution Key and Sort Key control how that happens, and both affect query performance.

Distribution Key (DISTKEY) decides which node each row lands on.

• DISTKEY (column) — rows with the same value (e.g., same region) land on the same node. This speeds up joins on that column, since matching rows are already together — no data has to move between nodes.
• DISTSTYLE ALL — copies the entire table to every node. Good for small lookup tables that get joined often.
• DISTSTYLE EVEN — spreads rows evenly with no particular logic. A safe default when there's no clear join pattern.

Sort Key (SORTKEY) decides the order data is physically stored within each node.
If a table is sorted by disbursed_date, a query filtering WHERE disbursed_date BETWEEN ... can skip straight to the relevant range instead of scanning everything — much faster for range-based queries.

SQL Differences
Basic SQL — SELECT, WHERE, GROUP BY, JOIN — works the same on both.
Redshift is PostgreSQL-based, so it’s closer to Aurora PostgreSQL than MySQL. Core queries are portable; engine-specific functions and features (like DISTKEY/SORTKEY) are not.

AWS Glue

It is a serverless ETL (Extract, Transform, Load) service — it moves data from one place to another, transforming it along the way, without you managing any servers.

• Extract — pull data from a source (RDS, Aurora, S3, on-prem databases via JDBC, etc.)
• Transform — clean, reshape, or convert the data (e.g., rename columns, filter bad rows, convert CSV to Parquet)
• Load — write the result somewhere (S3, Redshift, another database)

Core Components

1. Glue Data Catalog — a central metadata repository. It doesn’t store your actual data, just information about it: table names, column names, data types, and where the data physically lives (S3, RDS, Redshift, etc.).
2. Crawlers — scan your data source (e.g., a folder of CSV files in S3) and automatically figure out its structure, then write that information into the Data Catalog.
3. Jobs — the actual ETL work: extracts data from a source, transforms it, and loads it into a destination. Glue generates the code for you (Python or Scala, using Apache Spark) based on the source and destination you configure.
4. Triggers — decide when a Job runs — on a schedule, on demand, or in response to an event.
5. Glue Studio — a drag-and-drop interface for building ETL jobs without writing code.

Migrating to RDS/Aurora

The Core Problem Migration Solves
Imagine a company has been running their Employee/Loan system on an on-premises Oracle server for 10 years. They’ve decided to move to RDS or Aurora — but the database has millions of live rows, and the application can’t just go offline for hours while data copies over. They need a way to move the data safely, accurately, and with minimal downtime.

Manually exporting/importing SQL dumps works for small databases, but breaks down at scale — dumps take hours, data keeps changing during the export, and you risk losing transactions that happened mid-migration. This is exactly the gap AWS’s migration tools fill.

The Two Main Tools
1. AWS Database Migration Service (DMS) — the actual data-mover
2. AWS Schema Conversion Tool (SCT) — the translator, needed only when source and target use different database engines

Migration Strategies
Full Load — one-time bulk copy of all existing data. Good for a database that isn’t actively changing, or as the first phase of a bigger migration.

Full Load + CDC (Change Data Capture) — bulk copies existing data first, then continuously replicates every new insert/update/delete that happens on the source after the bulk copy started. This is the standard approach for near-zero-downtime migrations — your source database keeps taking live writes throughout the entire process, and DMS keeps the target in sync.

CDC Only — used when the target already has the base data (e.g., restored from a snapshot separately) and you just need ongoing changes replicated from that point forward.

Homogeneous vs Heterogeneous Migrations

• Homogeneous — same engine on both sides (MySQL → MySQL, or MySQL → Aurora MySQL). Simpler, since schema and SQL syntax match exactly.
• Heterogeneous — different engines (Oracle → Aurora PostgreSQL, on-prem SQL Server → Aurora MySQL). This is where AWS Schema Conversion Tool (SCT) comes in — it analyzes the source schema, stored procedures, and functions, and automatically converts as much as possible into the target engine’s syntax (e.g., translating Oracle’s PL/SQL into PostgreSQL’s PL/pgSQL). Anything it can’t auto-convert, it flags for manual review.

Cutover
It is the actual moment of switching your live application from the old database to the new one.

Example: Migrating Oracle to Aurora MySQL Using SCT and DMS
This is a heterogeneous migration — Oracle and Aurora MySQL are different engines, so both SCT (schema) and DMS (data) are needed.

Step 1: Convert the Schema Using SCT

1. Download and install AWS Schema Conversion Tool on your local machine (it’s a desktop application, not a console feature)
2. Open SCT → Create New Project
3. Set Source: Oracle (host, port, credentials)
4. Set Target: Aurora MySQL (your cluster’s connection details)
5. Connect to both source and target
6. SCT analyzes your Oracle schema and shows a compatibility report — what percentage of tables, stored procedures, and triggers can convert automatically vs what needs manual fixes
7. Review the report — simple tables usually convert cleanly; complex PL/SQL procedures often need manual adjustment
8. Click Convert Schema — SCT generates the equivalent MySQL-compatible schema
9. Apply the converted schema to Aurora MySQL — this creates the empty tables/structure (no data yet)

Step 2: Set Up DMS to Move the Actual Data

1. Create a DMS replication instance — the “worker” that performs the data copy
2. Create a Source Endpoint (Oracle) and a Target Endpoint (Aurora MySQL), testing both connections
3. Create a Migration Task, selecting “Migrate existing data and replicate ongoing changes” (Full Load + CDC)
4. Start the task — DMS copies all existing Oracle data into the Aurora tables SCT created, then switches to CDC mode, continuously replicating new changes

Step 3: Monitor Progress

The DMS console shows full load progress and CDC latency — how far behind the target is from the source. This should approach near-zero once caught up.

Step 4: Cutover

Once CDC latency is near zero:

1. Briefly pause writes to Oracle
2. Let DMS apply the last remaining changes
3. Point your application to the Aurora MySQL endpoint instead of Oracle
4. Resume traffic

Conclusion

We started with how a traditional database works and what it took to run one on-premises, then saw how RDS and Aurora take over that operational burden — Aurora with a cloud-native storage architecture built for speed and resilience.

Beyond the application database, we covered Redshift for analytics at scale, Glue for automating data movement, and DMS/SCT for migrating existing databases into AWS.

Together, these services cover the full lifecycle of a real system: running live data (RDS/Aurora), analyzing it (Redshift), moving it between systems (Glue), and migrating it into AWS in the first place (DMS/SCT). These aren’t just exam concepts — they’re the same building blocks used in production systems everywhere.