Unit 4: Databases and Data Management

I. Orientation: The Database Layer in Cloud Architecture

The database layer stores, organizes, protects, and serves application data. In AWS, architects can choose managed relational, key-value, document, graph, ledger, time-series, in-memory, and analytical databases instead of forcing every workload into one database model.

• Governing principle — purpose-built persistence: Select a database according to the workload’s data model, access patterns, consistency requirements, scale, and operational constraints.
• Shared-responsibility model: AWS protects the underlying cloud infrastructure; customers remain responsible for data classification, access policies, database configuration, application credentials, and some patching choices.
• Managed-service advantage: Services such as Amazon RDS and DynamoDB reduce work involving hardware provisioning, software installation, backups, failure detection, and replacement.
• Core design dimensions:
  • Performance: Throughput, latency, connection count, query efficiency, and caching.
  • Reliability: Backups, replication, failover, recovery objectives, and fault isolation.
  • Security: Encryption, network isolation, authentication, authorization, and auditing.
  • Cost: Instance capacity, storage, requests, data transfer, licenses, and operational effort.
• Availability terminology:
  • RTO: Recovery Time Objective, the maximum acceptable restoration time.
  • RPO: Recovery Point Objective, the maximum acceptable amount of data loss measured in time.
• Scaling distinction: Vertical scaling increases the capacity of one database node, while horizontal scaling distributes work across nodes, replicas, or partitions.

II. Database Architecture — Selecting the Right Persistence Model

A. Database layer considerations and use cases

Database selection should begin with application requirements and access patterns, not with a preferred database product.

• Data model: Match the service to the structure and relationships in the data.
  • Relational databases suit normalized tables, joins, constraints, and SQL.
  • Key-value databases suit lookups through known partition keys.
  • Graph databases suit highly connected entities and relationship traversal.
• Transaction requirements: Relational engines are appropriate when multi-row ACID transactions and referential integrity are central, such as transferring funds between accounts.
• Consistency: Strong consistency returns the latest acknowledged write, while eventual consistency may temporarily return an older value in exchange for greater availability or lower coordination overhead.
• Access patterns: Identify operations such as GetOrderById, ListOrdersByCustomer, and UpdateInventory. DynamoDB table keys, relational indexes, and read replicas must support these concrete operations.
• Workload characteristics: Consider read-to-write ratio, request rate, item size, query complexity, growth rate, and traffic variability. A flash-sale catalogue differs from a stable payroll system.
• Availability needs: Production systems commonly distribute resources across Availability Zones. Multi-AZ deployment reduces dependence on one data centre location.
• Scaling method: RDS commonly scales vertically and uses read replicas for read scaling; DynamoDB partitions data automatically and scales throughput horizontally.
• Security controls: Place databases in private subnets where possible, restrict security-group rules, encrypt data with AWS KMS, protect credentials in AWS Secrets Manager, and record relevant activity.
• Common use cases:
  • Amazon RDS/Aurora: ERP, CRM, financial records, and transactional web applications.
  • DynamoDB: Shopping carts, gaming state, session stores, and high-scale metadata.
  • ElastiCache: Frequently accessed or temporary data requiring microsecond-level in-memory performance.
  • Redshift: Large-scale analytical queries over warehouse data.
• Polyglot persistence: One application may use Aurora for orders, DynamoDB for sessions, ElastiCache for caching, and Redshift for analytics. This improves workload fit but increases governance and integration complexity.

III. Amazon RDS — Managed Relational Databases

A. Amazon RDS features and connection management

Amazon Relational Database Service operates supported relational engines while preserving familiar SQL, schemas, drivers, and database tools.

• Supported engines: RDS supports Amazon Aurora, PostgreSQL, MySQL, MariaDB, Oracle Database, Microsoft SQL Server, and Db2, subject to regional and edition availability.
• Managed operations: AWS handles infrastructure provisioning, storage management, monitoring integration, automated backups, and configurable maintenance activities.
• Deployment choices: Architects select a DB instance class, storage type and capacity, engine version, VPC, subnet group, security groups, backup retention, and availability configuration.
• Endpoints: Applications connect through a DNS endpoint and port, such as PostgreSQL on port 5432; they should not depend on an underlying instance IP address because failover can change it.
• Network control: A DB subnet group identifies eligible subnets. Security groups should permit inbound traffic only from approved application security groups on the database port.
• Authentication: Store and rotate passwords through Secrets Manager where practical. Supported engines can also use IAM database authentication through short-lived authentication tokens.
• Connection cost: Every database connection consumes memory and processing resources. Serverless applications can create sudden connection storms because many concurrent functions may open separate connections.
• Connection pooling: Reuse established connections in the application or use Amazon RDS Proxy. RDS Proxy pools connections, queues or shares demand, and can improve resilience during failover.
• Operational visibility: CloudWatch exposes metrics such as CPUUtilization, DatabaseConnections, FreeStorageSpace, read/write latency, and IOPS. Performance Insights helps identify database load and expensive SQL.
• Availability distinction: Multi-AZ is primarily for availability and failover; it is not the standard mechanism for directing read-only application queries to a standby.

B. Automated backups and read replicas in RDS

Automated backups protect recoverability, whereas read replicas primarily improve read scalability and can support separate recovery strategies.

1. Automated backups:

   • Mechanism: RDS creates storage-level backups and continuously captures transaction logs during the configured retention period.
   • Point-in-time recovery: A database can be restored to an eligible second within the retention window, producing a new DB instance rather than overwriting the source.
   • Backup window: The preferred backup window schedules daily backup activity; retention can generally be configured up to 35 days for RDS DB instances.
   • Snapshots: Manual snapshots persist until deleted and are useful before major schema changes or for longer retention. Snapshots can be copied across Regions or accounts under supported configurations.
   • Recovery design: Backup existence is insufficient; restore procedures must be tested against stated RTO and RPO targets.

2. Read replicas:

   • Mechanism: A source database asynchronously replicates changes to one or more read-only replicas, depending on engine capabilities.
   • Use cases: Reporting, dashboards, read-heavy API requests, and analytical queries can be moved away from the writer.
   • Replication lag: Because replication is generally asynchronous, a replica may return stale data. Read-after-write operations should use the writer when freshness is mandatory.
   • Promotion: A read replica can be promoted into an independent writable database, but promotion and application redirection are not equivalent to automatic Multi-AZ failover.
   • Regional resilience: Cross-Region replicas can place readable data nearer users and support disaster-recovery designs, with additional transfer cost and replication lag.

IV. Purpose-Built AWS Databases — Matching Engines to Workloads

A. Amazon DynamoDB and other purpose-built databases

DynamoDB is a serverless NoSQL key-value and document database designed for predictable low-latency access at virtually any scale.

• DynamoDB data model: A table contains items and attributes. Each item is identified by a partition key or by a composite primary key consisting of a partition key and sort key.
• Partitioning: The partition key determines data distribution. A high-cardinality key such as customerId usually distributes traffic better than a low-cardinality value such as country.
• Capacity modes: On-demand mode charges per request and adapts to variable traffic; provisioned mode defines read and write capacity and can use auto scaling.
• Queries and scans: Query uses key conditions and is efficient; Scan reads every item or index entry examined and can consume substantial capacity.
• Indexes: Global secondary indexes provide alternative partition and sort keys; local secondary indexes share the table’s partition key and must be created with the table.
• Consistency and transactions: Reads can be eventually consistent or, where supported, strongly consistent. DynamoDB also supports ACID transactions across multiple items.
• Resilience features: Point-in-time recovery, on-demand backups, DynamoDB Streams, Time to Live, and Global Tables support recovery, event processing, expiry, and multi-Region replication.
• Other purpose-built services:
  • Amazon Aurora: Cloud-optimized relational processing compatible with MySQL or PostgreSQL.
  • Amazon DocumentDB: JSON document workloads requiring MongoDB-compatible APIs.
  • Amazon Neptune: Property-graph and RDF workloads such as fraud networks and knowledge graphs.
  • Amazon Timestream: Time-series data from telemetry, operations, and IoT systems.
  • Amazon QLDB: Cryptographically verifiable, immutable transaction journals.
  • Amazon ElastiCache: Redis- or Memcached-compatible in-memory caching.
  • Amazon Redshift: Columnar data warehousing and analytical SQL.

V. Database Migration — Moving Data and Workloads

A. Migrating databases to AWS

Database migration combines assessment, schema conversion, data movement, validation, and controlled application cutover.

• Migration strategies: Rehost the existing engine, replatform onto a managed equivalent, or refactor for a different data model. Greater transformation can yield more cloud benefit but raises migration complexity.
• Assessment: Inventory database size, engine versions, extensions, schemas, stored procedures, dependencies, throughput, downtime tolerance, RTO, and RPO.
• Homogeneous migration: Moving Oracle to Oracle or PostgreSQL to PostgreSQL preserves the engine and can use native tools, snapshots, or AWS Database Migration Service.
• Heterogeneous migration: Moving Oracle to Aurora PostgreSQL changes engines and usually requires schema and code conversion through AWS Schema Conversion Tool or AWS DMS Schema Conversion.
• AWS DMS: Database Migration Service can perform a full load and then apply ongoing changes through change data capture, reducing the final outage window.
• Connectivity: Source and target systems require secure routing through VPN, AWS Direct Connect, VPC peering, or other approved network paths, plus least-privilege database accounts.
• Validation: Compare row counts, checksums where appropriate, constraints, representative query results, and application behaviour. Performance tests should reproduce realistic concurrency.
• Cutover sequence: Reduce DNS caching where relevant, stop or control source writes, allow replication lag to reach an acceptable level, validate the target, redirect applications, and monitor errors.
• Rollback planning: Define a decision deadline and a method for reconciling writes. Rollback becomes difficult once the target accepts data that is not replicated back to the source.

VI. Well-Architected Database Design — Balancing Cloud Qualities

A. Applying Well-Architected principles to the database layer

A database design should be evaluated across the AWS Well-Architected Framework pillars rather than optimized for performance alone.

• Operational excellence: Provision databases through infrastructure as code, standardize parameter changes, monitor metrics and logs, automate maintenance, and maintain tested runbooks.
• Security: Use private connectivity, least-privilege IAM and database roles, KMS encryption at rest, TLS in transit, Secrets Manager rotation, audit logging, and data classification.
• Reliability: Use Multi-AZ deployment, suitable replication, automated backups, cross-Region recovery where justified, and regular restore or failover testing against RTO and RPO.
• Performance efficiency: Choose the correct engine, instance family, storage configuration, indexes, partition keys, caching layer, and replica topology. Measure query plans and latency before scaling resources.
• Cost optimization: Remove idle databases, right-size instances, compare on-demand and reserved pricing, use storage auto scaling carefully, select DynamoDB capacity mode by demand pattern, and monitor backup retention.
• Sustainability: Prefer managed services, efficient queries, appropriate retention periods, and right-sized capacity to reduce unnecessary computation and storage.
• Trade-off analysis: A cross-Region replica improves resilience and locality but adds cost and lag; strong consistency improves freshness but may reduce flexibility; additional indexes accelerate reads but increase storage and write work.
• Continuous review: Use CloudWatch alarms, AWS Config rules, cost reports, security findings, load tests, and recovery exercises to confirm that the database still satisfies changing requirements.