Relational and NoSQL databases

Every appointment, vaccination, prescription and invoice at Cascade Ridge lives in one Microsoft SQL Server database behind VetLedger. It is about 400 GB, and it is the thing Mateo worries about most. He patches it at night, checks the backup logs every morning, and once spent a whole weekend restoring it after a disk failed. The migration plan says the database will be replatformed to Amazon RDS for SQL Server. This session is about what that means, and about the other kinds of databases AWS offers, because BookVet's phase 2 design in lesson 2 already mentioned one of them.

Gwen frames the session around two decisions. First: should a database run on an EC2 instance that you manage, or as an AWS managed service? Second: what kind of database does the data actually need? The exam tests both.

Self-managed on EC2 or managed by AWS

A database can be installed on an EC2 instance like any other software. That gives full control: any engine, any version, any setting, any operating system tool. It also keeps all of the work Mateo does today: installing and patching the database software and operating system, configuring backups and testing restores, setting up replication or failover, monitoring storage and scaling the server.

An AWS managed database service takes most of that work. AWS provisions the database, patches the engine and operating system in a maintenance window you choose, takes automated backups, can maintain a standby copy in another Availability Zone, and scales storage. The customer still designs the schema, writes queries, controls who can connect and manages the data. In the language of the earlier course, the customer gives up some control in return for less undifferentiated heavy lifting.

VetLedger's vendor certifies Amazon RDS for SQL Server, which settles it. Mateo will no longer patch SQL Server at night.

Relational databases: Amazon RDS and Amazon Aurora

A relational database stores data in tables of rows and columns, with relationships between tables, and is queried with SQL. It enforces structure (each row in a table has the same columns), supports transactions that either complete fully or not at all, and is good at queries that join data across tables, such as all unpaid invoices for clients whose pets were seen at the Red Deer clinic this month. Most business systems, VetLedger included, are relational.

Amazon Relational Database Service (Amazon RDS) runs relational databases as a managed service. It supports several engines: MySQL, PostgreSQL, MariaDB, Oracle, Microsoft SQL Server and IBM Db2, as well as Amazon Aurora. Key features appear on the exam again and again.

• Multi-AZ deployments keep a synchronized standby copy in a second Availability Zone. If the primary fails, or during maintenance, RDS fails over to the standby automatically, usually within a minute or two, and the database endpoint name stays the same. Multi-AZ is about availability.
• Read replicas are copies kept up to date asynchronously, used to spread read-heavy traffic such as reports, and they can be in other Regions. Read replicas are mainly about performance and scale, though a cross-Region replica can also help disaster recovery.
• Automated backups with point-in-time recovery to any second within a retention period, plus manual snapshots kept until you delete them.
• Encryption at rest and in transit, network isolation inside a VPC and access control through security groups.

Amazon Aurora is a relational database built by AWS for the cloud and compatible with MySQL and PostgreSQL, so applications written for those engines usually work with little change. Aurora separates compute from a shared storage layer that keeps six copies of the data across three Availability Zones, grows automatically and recovers quickly. It supports up to 15 low-latency read replicas, global databases across Regions, and Aurora Serverless, which adjusts capacity automatically with demand. AWS positions Aurora as giving the performance and availability of commercial databases at lower cost.

Kwame asks whether VetLedger could use Aurora instead. Not today, Gwen says: VetLedger needs SQL Server, and Aurora is compatible with MySQL and PostgreSQL. If the vendor ever supports PostgreSQL, the question can be reopened.

NoSQL databases

Not all data fits neatly into tables. NoSQL (often read as not only SQL) databases use other data models and trade some relational features, such as joins across many tables, for flexibility, scale or speed. AWS offers purpose-built databases for each model, and the exam expects you to match a description to the service.

Amazon DynamoDB is a serverless key-value and document database. Data is stored in tables of items, each found by a primary key; items in the same table can have different attributes. DynamoDB delivers consistent single-digit millisecond performance at virtually any scale, with no servers, patching or storage to manage. Capacity can be on-demand, paying per request, or provisioned, with optional auto scaling. Global tables replicate a table across Regions, and DynamoDB Accelerator (DAX) adds an in-memory cache for microsecond reads. DynamoDB suits applications with known access patterns at large or unpredictable scale: shopping carts, user profiles, session state, game data and device data.

For BookVet phase 2, DynamoDB fits the booking sessions and the reminder schedule: each item is found by a client or appointment key, traffic is bursty, and there is no server to maintain. The authoritative appointment records stay in VetLedger's relational database.

# The same appointment seen two ways: relational rows versus a key-value item.
import sqlite3, json

db = sqlite3.connect(":memory:")
db.executescript("""
CREATE TABLE client(id INTEGER PRIMARY KEY, name TEXT);
CREATE TABLE pet(id INTEGER PRIMARY KEY, client_id INTEGER, name TEXT, species TEXT);
CREATE TABLE appt(id INTEGER PRIMARY KEY, pet_id INTEGER, clinic TEXT, slot TEXT);
INSERT INTO client VALUES (1, 'R. Okafor');
INSERT INTO pet VALUES (10, 1, 'Biscuit', 'dog');
INSERT INTO appt VALUES (4471, 10, 'Lethbridge', '2026-12-03 09:30');
""")
row = db.execute("""SELECT a.id, c.name, p.name, a.clinic, a.slot
                    FROM appt a JOIN pet p ON p.id = a.pet_id JOIN client c ON c.id = p.client_id""").fetchone()
print("Relational join:", row)

# Key-value: one item, fetched by its key, holding everything the booking page needs.
table = {"APPT#4471": {"client": "R. Okafor", "pet": "Biscuit", "clinic": "Lethbridge",
                       "slot": "2026-12-03 09:30", "reminder": "SMS"}}
print("Key-value get:", json.dumps(table["APPT#4471"]))

Relational join: (4471, 'R. Okafor', 'Biscuit', 'Lethbridge', '2026-12-03 09:30')
Key-value get: {"client": "R. Okafor", "pet": "Biscuit", "clinic": "Lethbridge", "slot": "2026-12-03 09:30", "reminder": "SMS"}

The relational version can answer almost any question by joining tables. The key-value version answers one question, show me appointment 4471, in a single fast lookup, which is exactly what the booking page asks thousands of times a morning.

Other purpose-built databases are worth recognizing by description.

The exam phrase memory-based database points to ElastiCache. A typical scenario: a relational database is overloaded by the same read queries again and again; placing ElastiCache in front of it to serve the repeated reads from memory reduces the load and speeds up responses. Kwame notes that the BookVet clinic-hours page, which reads the same few rows constantly, is a candidate.

Moving databases: AWS DMS and schema conversion

Moving a database is often the hardest part of a migration, because the business cannot stop while it happens. AWS Database Migration Service (AWS DMS) copies data from a source database to a target with the source still running, then keeps the target in step by replicating ongoing changes until cutover. It supports homogeneous migrations, where source and target use the same engine (SQL Server to RDS for SQL Server), and heterogeneous ones, where they differ (Oracle to Aurora PostgreSQL).

Heterogeneous migrations also need the schema, stored procedures and other code converted to the new engine. The AWS Schema Conversion Tool (AWS SCT), and the schema conversion feature built into DMS, analyse the source, convert what they can automatically and report what needs manual work.

Cascade Ridge's move is homogeneous, so Gwen's plan is a native SQL Server backup restored into RDS for the initial copy, with DMS available to replicate changes if the cutover window must be very short. The cutover itself is planned for a Sunday night in spring 2027, when the emergency hospital is quietest.

Backups, recovery and the numbers that matter

Two numbers shape every database recovery plan, and Dr. Kaur learns them in this session. The recovery point objective (RPO) is how much data the business can afford to lose, measured in time: if the database failed now, how far back is acceptable? The recovery time objective (RTO) is how long the business can afford to be without the system. For the emergency hospital, Dr. Kaur says, losing an hour of records would be serious and being without the system for more than an hour would be dangerous.

Managed databases make good RPO and RTO values easier to reach. RDS automated backups allow point-in-time recovery to within about five minutes of the present. A Multi-AZ standby handles the failure of an Availability Zone in minutes, without restoring anything. A cross-Region snapshot copy, or a cross-Region read replica for engines that support it, protects against the loss of a whole Region, with a longer recovery time. AWS Backup can manage these copies alongside the archive and file share backups, so Hannah sees one report instead of five.

Gwen adds a warning she gives every client: a backup that has never been restored is a hope, not a plan. The design includes a quarterly test in which Mateo restores last night's VetLedger backup into the development account and checks that the clinics' records open correctly. The first test is scheduled for the month after cutover.

Who does what for a managed database

Hannah, the security lead, asks for the responsibility split in writing. For RDS, AWS manages the hardware, the host operating system, the database engine installation and patching, automated backups and the failover mechanism. Cascade Ridge manages the database settings it chooses, user accounts and permissions inside the database, the security groups that decide who can connect, encryption choices, the schema and queries, and of course the data. For DynamoDB, which is serverless, AWS also manages capacity and partitioning, and Cascade Ridge manages tables, keys, IAM permissions and data.