DBS-C01 Flashcards — Free AWS Certified Database Specialty DBS-C01 Study Cards

Create a covering index on the filter column

Option D is correct because a covering index includes all columns needed by the reporting queries, allowing PostgreSQL to satisfy the query entirely from the index without accessing the heap (table) pages. This eliminates the overhead of random I/O for row lookups, which is especially beneficial when filtering on a high-cardinality, low-selectivity column where many rows match but the index scan alone can return the required data. In Amazon RDS for PostgreSQL, this reduces read IOPS consumption and improves query latency.

Use AWS Database Migration Service (AWS DMS) with ongoing replication.

AWS DMS with ongoing replication (change data capture, CDC) is the correct choice because it allows continuous synchronization of the source PostgreSQL database with the target RDS for PostgreSQL instance after an initial full load. This minimizes downtime by enabling the target to stay up-to-date with changes until the cutover, which is critical for a 2 TB database with a high write workload.

The application is connecting to the DB instance in a different Availability Zone, increasing network latency.

Option B is correct because after failover, the new primary DB instance resides in us-east-1b, while the application likely continues to connect to the original endpoint or is still running in us-east-1a. This cross-AZ network hop introduces additional latency for write operations, as the application must send data over the network between Availability Zones. The DB instance class and storage are identical, so performance differences are not due to hardware changes.

Create one or more read replicas and redirect read traffic to them.

Creating read replicas offloads read queries from the primary DB instance to replica instances, directly reducing the number of read I/O operations on the primary. Since the application is read-heavy and Read IOPS is high during business hours, distributing read traffic to replicas alleviates the I/O bottleneck on the primary instance without requiring a larger instance or storage changes.

Configure the DynamoDB table to use AWS KMS encryption with a CMK. Create an IAM role with a policy that grants access to the table and includes a condition that the encryption context matches the CMK.

Option D is correct because it combines DynamoDB encryption at rest with a customer-managed KMS CMK and uses an IAM role policy with an encryption context condition. This ensures that only specific IAM roles can access the table, and the encryption context condition ties the KMS key usage to the table, providing fine-grained access control with minimal operational overhead. The encryption context is automatically set by DynamoDB to the table ARN, so the condition key `kms:EncryptionContext:aws:dynamodb:tableName` can be used to restrict decryption to that specific table.

Deploy an Aurora Global Database with a secondary cluster in us-west-2. Configure the secondary cluster as a failover target.

Aurora Global Database provides replication across regions with typical RPO of seconds and RTO of minutes. It is the most appropriate solution for low RPO/RTO with minimal overhead. Option B is correct. Option A (automated backups) have an RPO of 5 minutes, which exceeds the required 5 seconds. Option C (DMS) adds cost and complexity. Option D (cross-Region read replicas) can have replication lag exceeding 5 seconds.

The table is encrypted using a customer managed KMS key.

Option D is correct. The output shows SSEDescription with Status ENABLED, SSEType KMS, and a KMSMasterKeyArn, which indicates that the table is encrypted using a customer-managed KMS key. Option A is incorrect because AWS managed KMS keys do not have an ARN like the one shown; they would have an alias like 'aws/dynamodb'. Option B is incorrect because DynamoDB does not use S3 managed keys; the SSEType is KMS. Option C is incorrect because the status is ENABLED, indicating encryption at rest is enabled.

Create a new RDS DB instance with encryption enabled and migrate the data.

Amazon RDS for Oracle supports encryption at rest using AWS KMS. Encryption can only be enabled when the DB instance is created; you cannot encrypt an existing unencrypted RDS instance. Therefore, to enable encryption for an existing on-premises Oracle database being migrated, you must create a new RDS DB instance with encryption enabled at launch and then migrate the data into that new instance. This makes option B the correct choice. Option A is incorrect because you cannot modify an existing unencrypted RDS instance to add encryption. Option C is incorrect because while Oracle Transparent Data Encryption (TDE) can be used to encrypt data within the database, it is not the native Amazon RDS encryption method and requires additional configuration; moreover, RDS encryption via KMS cannot be added on an existing instance. Option D is incorrect because you cannot encrypt a snapshot of an unencrypted database and then restore it to get an encrypted instance; the restored instance will inherit the encryption setting of the snapshot, which for an unencrypted source database cannot be retroactively encrypted.

Upgrade the DB instance class to a larger size with more vCPUs.

Option A is correct because upgrading to a larger DB instance class provides additional vCPUs, reducing CPU utilization and alleviating replication lag. Option B is wrong: increasing the backup retention period does not reduce I/O during backups; it only keeps backups longer and does not address CPU or write performance. Option C is wrong: Multi-AZ RDS for PostgreSQL uses synchronous replication; switching to asynchronous replication is not possible without compromising durability, and it would not reduce CPU load on the primary. Option D is wrong: creating a read replica offloads read queries, but it does not reduce write load or CPU usage from writes on the primary instance; replication lag may persist or increase.

Create a snapshot of the DB instance, copy the snapshot with encryption enabled, and restore the snapshot to a new encrypted DB instance.

Option A is correct because an existing unencrypted Amazon RDS instance cannot be directly encrypted. The standard approach to enable encryption with minimal downtime is to create a snapshot of the DB instance, copy the snapshot with encryption enabled, and restore the snapshot to a new encrypted DB instance. This process typically involves a brief outage during the restore but is simpler than other methods. Option B is incorrect because the AWS CLI does not support enabling encryption on an existing instance. Option C is incorrect because migrating to RDS Custom adds unnecessary complexity and does not directly enable encryption on the current instance. Option D is incorrect because the AWS Management Console also does not allow enabling encryption on an existing instance.

Use AWS Database Migration Service (AWS DMS) with ongoing replication from the MongoDB source.

AWS DMS with ongoing replication is the correct approach because it supports continuous change data capture (CDC) from MongoDB, enabling a live migration with minimal performance impact on the source. DMS reads the MongoDB oplog to capture changes without locking the database, which is critical for a 100 GB production database. This allows the target DocumentDB to stay synchronized until cutover, reducing downtime and avoiding the need for a full export/import that would strain the source.

AWS Database Migration Service (DMS) with ongoing replication

AWS Database Migration Service (DMS) with ongoing replication (change data capture, CDC) is required because the scenario demands minimal downtime and continuous synchronization after the initial 500 GB load. DMS uses transaction logs (e.g., PostgreSQL WAL) to capture and apply ongoing changes, enabling a near-zero-duration cutover. Full load only (Option B) would cause downtime during the final sync and cannot support ongoing replication.

Modify the replication task to change the migration type to 'full-load-and-cdc'.

After a full load completes and the task stops, the team needs to enable ongoing replication to capture and apply changes made to the source database during the migration window. Modifying the DMS task to use the 'full-load-and-cdc' migration type allows the task to perform the initial full load and then seamlessly transition to Change Data Capture (CDC) to replicate incremental changes, minimizing downtime. This is the correct approach because DMS supports switching a completed full-load task to include CDC without re-running the full load.

AWS DMS data validation

AWS DMS data validation is the correct tool because it provides built-in, row-level checksum-based validation that compares source and target data after a full load or ongoing replication. For DocumentDB migrations, DMS can compute and compare checksums on the fly, ensuring consistency without requiring manual scripting or external tools. This is the recommended AWS approach for validating data integrity during and after a migration to DocumentDB.

The TargetTablePrepMode is set to DROP_AND_CREATE, which creates tables without indexes or constraints.

When TargetTablePrepMode is set to DROP_AND_CREATE, AWS DMS drops the target table if it exists and then creates a new one using only the basic column definitions from the source. It does not migrate indexes, primary keys, or foreign keys because DMS is not designed to replicate schema objects beyond table structure and data. To preserve constraints and indexes, you must either pre-create them on the target or use a different prep mode like TRUNCATE_BEFORE_LOAD.

SQL Server Agent jobs

SQL Server Agent jobs are not supported in Amazon RDS for SQL Server because RDS is a managed service that does not provide access to the underlying operating system or the SQL Server Agent service. To migrate job scheduling, you must use alternatives such as AWS Database Migration Service (DMS) tasks, AWS Lambda, or Amazon RDS for SQL Server native scheduling via stored procedures and Windows Task Scheduler on an EC2 instance.

Modify the security group associated with the RDS instance to allow inbound traffic only from the application's IP addresses.

Option C is correct because modifying the security group associated with the RDS instance to allow inbound traffic only from the application's IP addresses restricts network access at the instance level, preventing unauthorized IP addresses from connecting even if credentials are compromised. Option A (attaching a resource-based policy) does not restrict network access—it controls IAM permissions. Option B (creating a new RDS instance in a VPC with a network ACL) is unnecessarily complex and involves migrating data; the requirement can be met by modifying the existing security group without creating a new instance. Option D (enabling IAM database authentication) authenticates database users via IAM but does not restrict source IP addresses, so it does not meet the stated requirement.

Enable Performance Insights and review the 'DB Connections' metric to identify spikes and troubleshoot application connection pooling

Option C is correct because enabling Performance Insights allows the DBA to monitor the 'DB Connections' metric in near real-time, identify exactly when connection spikes occur, and correlate those spikes with application behavior. This diagnostic approach pinpoints the root cause—such as a connection leak or insufficient connection pooling—without making changes that could degrade performance. Increasing max_connections or lowering wait_timeout without understanding the usage pattern can lead to resource exhaustion or premature connection termination.

The user 'app_user' does not have access from host '10.0.1.50'

The error logs show authentication failures despite the password being correct, which indicates the issue is not with the password itself but with the host-based access control. In MySQL, user accounts are defined as 'user'@'host', and if the application is connecting from an IP address (e.g., 10.0.1.50) that is not included in the user's allowed hosts, MySQL will reject the connection with an authentication error even if the password is correct. This is a common misconfiguration when migrating or scaling applications across different subnets.

The condition kms:ViaService restricts the decryption to requests made via Secrets Manager, but the application is calling KMS directly.

The condition `kms:ViaService` in the key policy restricts the `Decrypt` permission to requests that originate specifically from the AWS Secrets Manager service. When the application on the EC2 instance calls KMS directly (e.g., via the `Decrypt` API) to decrypt the secret, the request does not come through Secrets Manager, so the condition is not satisfied and the request is denied. This is the most likely cause of the decryption failure.

Create a read replica and direct all report queries to the read replica.

Creating a read replica for Amazon RDS for PostgreSQL allows the team to offload all report queries to a separate read-only endpoint, eliminating the I/O and CPU contention on the primary database. This directly addresses the read latency spikes during report generation without requiring any application changes beyond redirecting the reporting queries. The read replica asynchronously replicates data from the primary instance, ensuring the reports see a near-real-time snapshot of the data while the primary remains dedicated to the application workload.

Create a Global Secondary Index (GSI) with customer_id as the partition key and timestamp as the sort key.

Option C is correct because creating a Global Secondary Index (GSI) with customer_id as the partition key and timestamp as the sort key allows efficient querying of all transactions for a given customer within a date range without redesigning the base table. The GSI provides a new access pattern with its own partition and sort keys, enabling the Query operation on customer_id and timestamp, which is far more efficient than a Scan. This approach preserves the existing table schema and supports the required query pattern with minimal overhead.

Enable encryption at rest when launching the RDS instance, and configure the DB parameter group to require SSL connections.

Option B is correct because Amazon RDS for PostgreSQL supports encryption at rest only when enabled at instance launch, and SSL/TLS encryption in transit is enforced by configuring the DB parameter group to require SSL connections (e.g., setting `rds.force_ssl=1`). Encryption at rest cannot be added after creation, and SSL ensures data is encrypted between the application and the database.