Cloud database fundamentals: relational, non-relational and managed

Why this matters

CompTIA's bullet is exploring database concepts and their cloud applications, and the emphasis is on the second half. You are not being examined as a database administrator. You are being examined on the decisions a cloud practitioner makes around a database: which shape of store fits the access pattern, what a managed service takes off your hands, how replication changes consistency, and what the recovery guarantees actually are.

Those decisions are also where a lot of money and a lot of outages live. The database is usually the most expensive single component of an architecture and the one least able to scale horizontally, which makes it the constraint that shapes everything else.

The lesson

Relational and non-relational stores, and choosing by access pattern rather than fashion

Relational databases store data in tables with a fixed schema, enforce relationships and constraints, and are queried with SQL. Their defining property is ACID transactions — atomic, consistent, isolated, durable — which means a transaction either fully happens or does not, and concurrent transactions do not corrupt each other.

Choose relational when the data is structured and interrelated, when correctness under concurrency matters, and when queries are varied and not all known in advance. Financial records, orders, inventory, anything where a half-completed operation is unacceptable.

Non-relational ("NoSQL") is a family rather than one thing, and the exam expects you to recognise the four shapes:

• Key-value. A dictionary at scale. Extremely fast lookups by key, no querying by value. Sessions, caches, user profiles.
• Document. Semi-structured records, typically JSON, each self-contained and queryable by fields. Flexible schema. Catalogues, content, event records.
• Column-family / wide-column. Rows with sparse, dynamic columns, optimised for very large volumes and writes at scale. Time series, telemetry.
• Graph. Nodes and edges, optimised for traversing relationships. Social graphs, fraud detection, recommendations, dependency mapping.

Many of these offer BASE semantics rather than ACID — basically available, soft state, eventually consistent — trading immediate consistency for availability and partition tolerance. That trade is the practical face of the CAP theorem: when a network partition occurs, a distributed store must choose between consistency and availability.

The decision rule the exam rewards: choose by access pattern. What are the queries? Are they known in advance? Is the data highly interrelated? Does a stale read cause harm? "The team prefers document databases" is not a reason, and a scenario that includes a driver like "must never show a stale balance" has told you the answer.

Managed database services: what the provider takes over and what stays yours

A managed database moves the line from objective 1.1 upward. Typically the provider takes on:

• Installation, patching of the engine and the operating system beneath it
• Automated backups and point-in-time recovery within a retention window
• Replication, failover and often automatic promotion of a standby
• Monitoring, metrics and basic alerting
• Scaling operations, sometimes without downtime

What remains yours, always:

• Schema and query design, including indexing. A managed service will happily run a badly designed query forever.
• Data itself — its contents, its classification, its retention.
• Access control at the database level, and the network path to it.
• Encryption choices and key management, covered in objective 4.5.
• Capacity and cost decisions.
• Backups beyond the provider's retention window, and any copy that must live outside the account.

The trade-offs are worth stating because scenarios test them: managed services usually restrict superuser access, so extensions, engine-level settings and some operational tricks may be unavailable. Maintenance happens in a window you configure but do not fully control. And you are more tightly coupled to the provider — a straightforward instance of lock-in from objective 1.1.

The middle option, self-managed on IaaS, is the right answer when you need engine control the managed service withholds, and it means you have taken back patching, backup, replication and failover.

Read replicas, multi-primary and the consistency you trade for each

Three patterns, three different guarantees.

Read replicas. Asynchronous copies serving read traffic. They scale reads, offload reporting, and can often be promoted. The cost is replication lag: a write committed on the primary is not instantly visible on a replica. An application that writes and then immediately reads from a replica can fail to see its own write — a genuinely confusing bug, and a standard exam scenario. The remedy is to route reads that must be current to the primary.

Synchronous standby (multi-AZ). A hot copy kept in step, in another availability zone, with automatic failover. This is a resilience feature, not a scaling one — the standby usually serves no traffic. It costs write latency, because a commit waits for the second copy. When a scenario asks for high availability, this is the answer; when it asks for read scaling, replicas are.

Multi-primary / active-active. Writes accepted in more than one place. It buys write availability and locality and introduces write conflicts, which must be resolved by rules that sometimes lose data. Genuinely useful for globally distributed systems and usually the wrong answer for a conventional transactional application.

The examinable distinction: read replica = read scaling, lag; synchronous standby = availability, write latency; multi-primary = write availability, conflicts.

Backups, point-in-time recovery and the retention window you are actually given

Managed services take automated backups and support point-in-time recovery — restoring to any moment within the retention window by replaying transaction logs onto a base backup. That is a strong capability and it has edges people discover late:

• The retention window is finite, commonly days to a few weeks. Damage discovered after it has passed is unrecoverable from automated backups. If you need longer, take your own snapshots or exports on a schedule.
• Restores usually create a new instance. Recovery is not in-place, so cutover involves repointing the application, and the restore takes time proportional to the data. Measure it — objective 3.2 makes this a rule.
• Deleting the database can delete its automated backups. Manual snapshots usually survive; automated ones often do not. This is a genuinely dangerous default.
• Backups usually live in the same account and region by default. An account compromise or a regional failure can take the data and its backups together, which is the cloud version of storing tapes next to the server.
• Point-in-time recovery does not protect the schema from a bad migration any better than from anything else — it just lets you go back before it, losing everything since.

Data warehouses and caches as the other two shapes a scenario will describe

Two more components appear constantly in scenarios and are neither of the stores above.

Data warehouse. Optimised for analytical queries across very large volumes — typically columnar storage, heavy compression, massive parallelism. It is for OLAP, not OLTP: excellent at aggregating a billion rows, poor at single-row updates. The standard architecture keeps a transactional database for the application and a warehouse for analysis, with data moved between them. A scenario where reporting queries are hurting the production database is describing exactly this split, and adding a read replica is the smaller alternative.

Cache. An in-memory store in front of a slower one, holding frequently read data. It changes performance and cost dramatically and introduces its own problems:

• Invalidation — how does cached data get removed when the source changes? Time-to-live is simple and serves stale data; explicit invalidation is correct and easy to get wrong.
• Cold start — an empty cache after a restart pushes full load onto the database at the worst moment.
• It is not durable. Never treat a cache as a system of record.

A scenario about repeated identical expensive reads is describing a cache; one about analytical queries on a transactional system is describing a warehouse or a replica.

What to take into the exam

• Choose by access pattern. ACID and interrelated data → relational. Known key lookups, scale, flexible schema → the matching NoSQL shape.
• CAP: under partition, choose consistency or availability. BASE is the eventual-consistency trade.
• Managed services take patching, backup, replication, failover; you keep schema, data, access, cost and anything beyond the retention window.
• Read replica = read scaling with lag (read-after-write can miss). Synchronous standby = availability, costs write latency. Multi-primary = write conflicts.
• Deleting a database can delete its automated backups; retention windows are finite; restores create a new instance and take real time.
• Warehouse = OLAP, cache = speed, not durability, and cache invalidation is the hard part.