Unit 4: Databases and Data Management - Subjective Questions

The database layer should be selected after evaluating the application's functional and non-functional requirements.

• Data model: Determine whether the application requires relational tables, documents, key-value pairs, graphs, time-series data, or an in-memory structure.
• Access patterns: Identify common queries, transaction types, read-to-write ratio, and latency requirements before choosing a database.
• Scalability: Decide whether the workload needs vertical scaling, horizontal scaling, read replicas, partitioning, or automatic scaling.
• Availability and durability: Evaluate Multi-AZ deployment, replication, automated backups, failover, and disaster recovery requirements.
• Consistency: Select strong or eventual consistency according to business needs.
• Security and compliance: Consider encryption, network isolation, authentication, authorization, auditing, and data residency.
• Operational effort: Managed AWS services reduce tasks such as patching, backups, monitoring, and hardware provisioning.
• Cost: Include compute, storage, I/O, backup, data transfer, licensing, and administrative costs.

A suitable design balances performance, reliability, security, operational simplicity, and total cost of ownership.

Relational databases organize data into tables with predefined schemas and relationships. They generally support SQL, joins, constraints, and ACID transactions. Amazon RDS and Amazon Aurora are suitable relational services.

Typical relational use cases include:

• Banking and financial transactions
• Enterprise resource planning systems
• Order processing
• Applications requiring complex joins and strong consistency

Non-relational databases use data models such as key-value, document, graph, wide-column, or time-series. They are often designed for horizontal scalability, flexible schemas, and high request volumes. Examples include DynamoDB, DocumentDB, Neptune, and Timestream.

Typical non-relational use cases include:

• Shopping carts and user sessions
• Product catalogs with varying attributes
• Social graphs and fraud detection
• IoT telemetry and time-series analysis

The choice should be driven by data structure, access patterns, consistency requirements, scale, and query complexity rather than by a preference for a particular database technology.

Amazon Relational Database Service, or Amazon RDS, is a managed service for deploying and operating relational databases.

Its principal features include:

• Managed provisioning: AWS provisions database instances and storage.
• Multiple database engines: RDS supports engines such as MySQL, PostgreSQL, MariaDB, Oracle, Microsoft SQL Server, and Amazon Aurora.
• Automated patching: AWS can apply operating system and database engine patches during configured maintenance windows.
• Automated backups: RDS creates backups and retains transaction logs for point-in-time recovery.
• High availability: Multi-AZ deployments maintain a synchronous standby and support automatic failover.
• Read scaling: Read replicas offload read-only workloads from the primary database.
• Monitoring: CloudWatch metrics, Enhanced Monitoring, Performance Insights, and event notifications provide operational visibility.
• Security: RDS integrates with VPCs, security groups, IAM, AWS KMS, and TLS.

These features allow administrators to focus on schema design, query optimization, and application requirements instead of routine infrastructure maintenance.

Engine selection begins with application compatibility and business requirements.

• Choose MySQL, PostgreSQL, or MariaDB for widely adopted open-source compatibility.
• Choose Oracle or Microsoft SQL Server when an application depends on vendor-specific features or existing licenses.
• Choose Amazon Aurora for MySQL- or PostgreSQL-compatible workloads that require cloud-oriented availability, replication, and performance.

The deployment configuration should consider:

• Instance class: Select memory, CPU, and network capacity according to workload characteristics.
• Storage: Choose an appropriate storage type, size, and provisioned IOPS level.
• Availability: Use Multi-AZ for production workloads that require automatic failover.
• Read scaling: Add read replicas for read-heavy traffic.
• Network placement: Deploy the database in private subnets across multiple Availability Zones.
• Backup retention: Configure retention according to recovery objectives.
• Encryption: Enable encryption at rest with AWS KMS and encryption in transit with TLS.
• Licensing and cost: Evaluate engine licensing, compute charges, storage, I/O, and operational costs.

Testing with representative workloads is essential before finalizing the configuration.

Every database connection consumes memory, CPU, and network resources. A sudden increase in application instances can create a connection storm and exceed the database engine's connection limit.

Appropriate solutions include:

• Connection pooling: Reuse existing connections rather than opening a new connection for every request.
• Amazon RDS Proxy: Pool and share database connections among application clients.
• Timeouts: Configure connection, query, and idle timeouts to prevent abandoned connections.
• Backoff and retry: Use exponential backoff with jitter for transient failures.
• Application limits: Restrict the maximum pool size for each application instance.
• Monitoring: Track active connections, failed connections, latency, CPU, and memory through CloudWatch and database metrics.
• Endpoint handling: Use the RDS endpoint instead of hard-coded IP addresses so that applications can reconnect after failover.
• Credential management: Store and rotate credentials with AWS Secrets Manager.

Effective connection management improves stability, particularly for serverless applications and applications that scale rapidly.

Amazon RDS Proxy is a managed database proxy that sits between an application and a supported RDS or Aurora database.

• Connection pooling: It maintains a reusable pool of database connections, reducing the cost of repeatedly opening and closing connections.
• Scalability: It is useful for AWS Lambda and auto-scaling applications that may create many short-lived connections.
• Availability: During a database failover, the proxy can preserve application-facing connections and route traffic to the available database instance, reducing disruption.
• Security: It can integrate with AWS Secrets Manager for credential storage and rotation and can support IAM-based database authentication.
• Resource protection: It prevents bursts of client connections from overwhelming the database.

However, RDS Proxy does not remove the need for query optimization, suitable database capacity, transaction management, or application retry logic. Long transactions and session-specific states may also reduce the effectiveness of connection multiplexing.

When automated backups are enabled, Amazon RDS creates periodic storage-level backups of the database and continuously captures transaction logs. These backups are retained for a configured retention period.

Point-in-time recovery allows a new database instance to be restored to a selected time within the retention window. RDS combines the appropriate backup with transaction logs to recreate the database state.

Important characteristics include:

• Recovery creates a new DB instance rather than overwriting the source.
• Backup retention should be aligned with the recovery point objective.
• Backups are managed by AWS, but restoration should still be tested regularly.
• A backup window can be configured to control when the daily backup activity begins.
• The restored instance may require endpoint changes, security group configuration, and application validation.
• Backup retention and storage consumption can affect cost.

Automated backups protect against accidental updates or deletions, but they should form part of a broader disaster recovery and data protection strategy.

Automated backups:

• Are created and managed automatically by RDS.
• Are retained for a configured number of days.
• Include transaction logs needed for point-in-time recovery.
• Are suitable for routine operational recovery.

Manual snapshots:

• Are initiated explicitly by a user or automation process.
• Remain available until they are deleted.
• Are useful before major upgrades, schema changes, or long-term retention.
• Can be copied across AWS Regions or accounts according to disaster recovery requirements.

Point-in-time recovery:

• Is a restore capability based on automated backups and transaction logs.
• Restores the database to a selected time within the retention period.
• Produces a new database instance.

A sound protection strategy can use automated backups for recent operational recovery, manual snapshots for controlled retention points, and cross-Region copies for protection against regional disasters.

Read replicas are primarily intended for read scalability.

• Replication is generally asynchronous.
• Applications can send read-only queries to replica endpoints.
• Replication lag may cause a replica to return older data.
• A replica can be promoted to an independent database, although promotion is not the same as automatic Multi-AZ failover.
• Depending on the engine, replicas may be placed in another Region.

Multi-AZ deployments are primarily intended for availability and durability.

• A standby copy is maintained in another Availability Zone.
• Replication to the standby is synchronous in the traditional Multi-AZ design.
• RDS automatically fails over when the primary instance or Availability Zone becomes unavailable.
• The standby is not normally used by the application for read scaling.

Therefore, read replicas address performance and read throughput, while Multi-AZ addresses high availability. A production architecture may use both.

A suitable architecture can contain the following components:

1. Primary RDS or Aurora database: Deploy the writer in private subnets and select an instance class based on measured workload requirements.
2. High availability: Use a Multi-AZ deployment so that RDS can automatically fail over to another Availability Zone.
3. Read replicas: Create one or more replicas and route read-only queries to them. The application must tolerate possible replication lag.
4. Connection management: Place RDS Proxy between application services and the database to pool connections and reduce connection storms.
5. Application logic: Separate read and write paths, use the correct endpoints, and implement retry with exponential backoff and jitter.
6. Backups: Enable automated backups and point-in-time recovery. Create manual snapshots before high-risk changes.
7. Regional disaster recovery: Copy snapshots to another Region or maintain a cross-Region replica if the recovery objectives require faster regional recovery.
8. Security: Use private subnets, restrictive security groups, TLS, KMS encryption, Secrets Manager, and least-privilege IAM policies.
9. Monitoring: Configure CloudWatch alarms for CPU, storage, connections, replica lag, latency, and failover events.
10. Testing: Regularly test failover, restoration, replica promotion, and application reconnection.

This design addresses read throughput, instance failure, connection growth, and data recovery while retaining managed operational capabilities.

Amazon DynamoDB is a fully managed, serverless NoSQL database designed for key-value and document workloads.

Its data model includes:

• Tables: Collections of related items.
• Items: Individual records consisting of attributes.
• Partition key: Determines the partition in which an item is stored and must uniquely identify an item when used alone.
• Composite primary key: Combines a partition key with a sort key, allowing multiple related items under the same partition key.

Important features include:

• Automatic distribution and horizontal scaling
• Millisecond-level performance at scale
• On-demand and provisioned capacity modes
• Strongly consistent and eventually consistent read options
• Global and local secondary indexes
• DynamoDB Streams for change capture
• Transactions for coordinated operations
• Time to Live for automatic expiration of items
• Point-in-time recovery and on-demand backups
• Global tables for multi-Region replication
• Integration with IAM, AWS KMS, Lambda, and CloudWatch

DynamoDB is suitable when access patterns are known and predictable keys can be designed for efficient queries.

DynamoDB uses the partition key to distribute items and requests across physical partitions. A good partition key has high cardinality and spreads traffic evenly.

A poorly chosen key can cause a hot partition, where a small number of key values receive a disproportionate share of reads or writes. This may result in throttling, uneven capacity use, and increased latency even when the table has sufficient total capacity.

Methods for avoiding hot partitions include:

• Select partition keys with many distinct values, such as customer IDs or device IDs.
• Avoid low-cardinality keys such as status, country, or current date as the sole partition key.
• Add a calculated or random suffix to distribute extremely high-volume writes.
• Use a composite primary key when related records must be grouped and ordered.
• Cache frequently read items when appropriate.
• Monitor consumed capacity and throttling through CloudWatch and Contributor Insights.
• Model the table around specific access patterns rather than copying a relational schema directly.

Key design is therefore central to DynamoDB scalability and predictable performance.

Capacity modes:

• On-demand mode automatically handles request volume and charges per request. It is appropriate for unpredictable or rapidly changing workloads.
• Provisioned mode allocates read and write capacity. Auto Scaling can adjust capacity and may reduce cost for predictable workloads.

Consistency options:

• Eventually consistent reads may return older data for a short period but provide higher read efficiency.
• Strongly consistent reads return the latest successful value, subject to service and regional limitations.

Transactions:

• DynamoDB transactions provide coordinated all-or-nothing read or write operations across multiple items.
• They are useful for workflows such as financial transfers and reservation systems but consume additional resources.

Secondary indexes:

• A global secondary index can use a different partition key and sort key and supports alternative access patterns.
• A local secondary index uses the same partition key as the base table but a different sort key and must be defined when the table is created.

These options should be selected according to access patterns, correctness requirements, traffic variability, and cost.

AWS offers purpose-built databases so that each workload can use a data model optimized for its access patterns.

• Amazon Aurora: A MySQL- and PostgreSQL-compatible relational database for transactional applications requiring managed availability and read scaling.
• Amazon DynamoDB: A key-value and document database for serverless applications, gaming state, shopping carts, and high-scale request processing.
• Amazon DocumentDB: A document database for JSON-like content, catalogs, content management, and applications needing document-oriented access.
• Amazon Neptune: A graph database for social networks, knowledge graphs, recommendation engines, and fraud detection.
• Amazon ElastiCache: A managed in-memory cache using Redis or Memcached-compatible technologies for sessions, caching, leaderboards, and low-latency access.
• Amazon Timestream: A time-series database for IoT telemetry, operational metrics, and time-dependent measurements.
• Amazon Keyspaces: A managed Apache Cassandra-compatible wide-column database for scalable Cassandra workloads.
• Amazon OpenSearch Service: A search and analytics service for logs, full-text search, observability, and near-real-time analysis.

Choosing a purpose-built database can improve scalability and performance, but it may introduce additional data integration and operational considerations in a multi-database architecture.

AWS Database Migration Service, or AWS DMS, moves data from a source database to a target database. It supports:

• Full-load migration of existing data
• Change data capture for ongoing replication
• Homogeneous and heterogeneous migrations
• Reduced application downtime by keeping the target synchronized until cutover
• Migration monitoring and validation capabilities

AWS Schema Conversion Tool, or AWS SCT, assists primarily with heterogeneous migrations. It can:

• Assess the source database schema
• Convert tables, views, indexes, and some database code to a target-compatible format
• Identify objects that require manual conversion
• Produce assessment reports showing migration complexity

For example, in a migration from Oracle to Amazon Aurora PostgreSQL, AWS SCT can convert schema objects and identify incompatible code, while AWS DMS loads the data and replicates ongoing changes. Neither tool eliminates the need for testing, performance tuning, application modification, and cutover planning.

A minimal-downtime migration can be performed in the following phases:

1. Discovery: Inventory database engines, versions, schemas, sizes, dependencies, stored procedures, applications, and performance characteristics.
2. Assessment: Select an AWS target and evaluate compatibility, licensing, security, availability, and cost. Use migration assessment tools where appropriate.
3. Target design: Provision the target database with private networking, encryption, backups, monitoring, and sufficient capacity.
4. Schema migration: Use native tools or AWS SCT to create and convert the target schema. Resolve unsupported objects manually.
5. Initial data load: Use AWS DMS, native replication, or database-specific utilities to copy existing data.
6. Change data capture: Continuously replicate changes made at the source during the migration period.
7. Validation: Compare row counts, checksums, constraints, business results, query performance, and application behavior.
8. Cutover preparation: Reduce DNS caching, define a rollback plan, communicate the maintenance window, and stop nonessential changes.
9. Cutover: Pause writes, allow replication lag to reach zero, validate the target, switch application connections, and resume processing.
10. Post-cutover monitoring: Monitor errors, latency, resource utilization, and data correctness. Retain the source temporarily according to the rollback plan.
11. Optimization and retirement: Tune the target, update documentation, protect backups, and decommission the source only after formal approval.

Repeated migration rehearsals significantly reduce cutover risk.

A homogeneous migration uses the same or a compatible database engine at the source and target, such as on-premises PostgreSQL to Amazon RDS for PostgreSQL. Schema and data types are usually similar, so native tools or AWS DMS can often perform the migration with fewer changes.

A heterogeneous migration uses different source and target engines, such as Oracle to Amazon Aurora PostgreSQL. It may require conversion of schemas, data types, stored procedures, functions, and application SQL. AWS SCT can assist with this conversion.

Migration data movement commonly has two stages:

• Full load: Copies the data that already exists in the source database.
• Change data capture: Reads transaction logs and applies ongoing inserts, updates, and deletes to the target.

Using both stages allows users to continue working with the source during most of the migration. At cutover, writes are paused, the remaining changes are applied, and the application is redirected to the target. This approach minimizes downtime but requires careful validation and replication-lag monitoring.

Security practices:

• Place databases in private subnets and restrict access through security groups.
• Apply least-privilege IAM permissions and database privileges.
• Store and rotate credentials with AWS Secrets Manager rather than embedding them in code.
• Encrypt data at rest using AWS KMS and use TLS for data in transit.
• Enable database logs, CloudTrail, and relevant auditing features.
• Classify sensitive data and apply retention, masking, and compliance controls.
• Keep database engines and clients patched.

Operational excellence practices:

• Manage database infrastructure through infrastructure as code.
• Automate backups, maintenance tasks, alarms, and routine responses.
• Monitor metrics such as connections, CPU, memory, storage, latency, IOPS, replica lag, and throttling.
• Use runbooks and playbooks for failover, restoration, scaling, and security events.
• Test changes in non-production environments and use controlled deployment procedures.
• Review incidents and improve operational procedures continuously.

These measures make database operations repeatable, observable, secure, and less dependent on manual intervention.

Reliability:

• Use Multi-AZ deployments, replicas, backups, and point-in-time recovery.
• Define recovery time and recovery point objectives.
• Eliminate single points of failure and test failover and restoration regularly.
• Use idempotent retry logic with exponential backoff for transient failures.
• Select cross-Region replication or snapshot copying when regional recovery is required.

Performance efficiency:

• Select the database engine according to the data model and access patterns.
• Right-size compute and storage using measured metrics.
• Create effective indexes and optimize queries.
• Use read replicas, caching, connection pooling, partitioning, and appropriate consistency levels.
• Perform load testing and monitor performance continuously.

Cost optimization:

• Remove idle databases, unused snapshots, and unnecessary replicas.
• Use suitable purchasing options for steady workloads and serverless or on-demand options for variable workloads.
• Right-size instances, storage, provisioned IOPS, and backup retention.
• Apply data lifecycle and archival policies.
• Evaluate licensing and data transfer costs.

The three pillars must be balanced; reducing cost should not violate availability, durability, or performance requirements.

The sustainability pillar encourages organizations to minimize unnecessary resource consumption while still meeting business requirements.

Database-related practices include:

• Right-sizing: Select database instances and storage based on measured demand instead of permanently overprovisioning.
• Elastic services: Use auto scaling, serverless configurations, or on-demand capacity when workloads vary significantly.
• Efficient data models: Design keys, indexes, and queries to avoid excessive scans, duplicated processing, and unnecessary I/O.
• Data lifecycle management: Archive or delete data that no longer needs to remain in high-performance storage.
• Purpose-built databases: Select an engine optimized for the workload rather than forcing every use case into a single inefficient platform.
• Managed services: Use managed automation to improve infrastructure utilization and reduce idle supporting resources.
• Monitoring: Track utilization trends and continuously remove waste.

Sustainability should be considered together with reliability, security, performance, and cost. Resources should not be reduced below the capacity needed to meet service-level objectives.