DBS-C01 Practice Exam 4 — 20 Questions

Question 1

A financial services company uses Amazon RDS for MySQL to store transaction data. The database has a single table 'transactions' with 500 million rows. The table has an auto-increment primary key and an index on 'transaction_date'. The company runs a monthly report that aggregates transactions by account_id and transaction_date. The report query uses a GROUP BY on account_id and transaction_date, and scans the entire table. The query takes over 2 hours to complete and often times out. The DBA suggests creating a materialized view. However, the company wants to minimize operational overhead. Which solution meets the requirements with the LEAST operational overhead?

Accepted Answer

Migrate the reporting workload to Amazon Redshift by loading the transactions table into Redshift and running the report query there.. Option B is correct because Amazon Redshift is purpose-built for large-scale analytical queries. By migrating the reporting workload to Redshift, the company offloads the heavy aggregation from the transactional RDS instance to a columnar storage engine that can scan and aggregate 500 million rows efficiently using massively parallel processing (MPP). This approach requires no changes to the existing RDS database and minimizes operational overhead compared to managing a materialized view or manual indexing.

Answer

Increase the RDS instance size to the largest available to improve performance.

Answer

Create a materialized view in MySQL that pre-aggregates the data and refreshes it nightly.

Answer

Add a composite index on (account_id, transaction_date) to speed up the GROUP BY.

Question 2

A company is using AWS Database Migration Service (AWS DMS) to migrate a 5 TB MySQL database to Amazon RDS for MySQL. The migration is taking longer than expected. The company notices that the source database has a high volume of write operations. Which configuration change would MOST likely improve the migration performance?

Accepted Answer

Increase the number of parallel threads in the DMS task settings.. Increasing the number of parallel threads in the DMS task settings allows AWS DMS to process multiple table partitions or row changes concurrently, which directly addresses the bottleneck caused by a high volume of write operations on the source. By default, DMS uses a single thread per table, but when the source is under heavy write load, parallel threads can capture and apply changes more efficiently, reducing the overall migration time.

Answer

Enable Multi-AZ on the target RDS instance.

Answer

Use a smaller instance class for the replication instance to reduce cost.

Answer

Set the LOB mode to 'Full LOB mode'.

Question 3

A company is migrating a 1 TB Oracle database to Amazon Aurora PostgreSQL using AWS DMS. They need to ensure minimal downtime and data consistency. Which TWO actions should the company take?

Accepted Answer

Enable change data capture (CDC) on the source Oracle database. Option B is correct because enabling Change Data Capture (CDC) on the source Oracle database allows AWS DMS to capture ongoing changes after the full load, enabling a near-zero downtime migration by continuously replicating transactions to the target Aurora PostgreSQL. Option C is correct because enabling data validation on the DMS task ensures that the data migrated is consistent between the source and target, verifying that no data loss or corruption occurred during the migration process.

Answer

Increase the target Aurora storage to 2 TB to avoid storage issues

Answer

Disable foreign key constraints on the target during migration

Answer

Use AWS Schema Conversion Tool to convert the schema

Question 4

A company is migrating an on-premises PostgreSQL database to Amazon RDS for PostgreSQL. The database is 2 TB in size and has a high write workload. The company needs to minimize downtime during the migration. Which AWS service or feature should the company use to achieve this?

Accepted Answer

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.

Answer

Use pg_dump and pg_restore to export and import the database.

Answer

Use the AWS Schema Conversion Tool (AWS SCT) to convert the schema and migrate data.

Answer

Use AWS DataSync to replicate the database files.

Question 5

A company is migrating a self-managed Oracle database to Amazon Aurora PostgreSQL using AWS DMS. The source database has a large number of tables with foreign key constraints. During the full load phase, some tables fail to load due to foreign key violations. What is the most efficient way to resolve this?

Accepted Answer

Disable foreign key constraints on the target before migration and re-enable after. Option C is correct because disabling foreign key constraints on the target Aurora PostgreSQL database before the migration allows DMS to load tables in any order without violating referential integrity. After the full load completes, re-enabling the constraints ensures data consistency. This approach is the most efficient as it avoids complex dependency ordering and reduces migration failures.

Answer

Increase the DMS task memory and parallel load threads

Answer

Use the table preparation mode 'Do nothing' and load tables in dependency order

Answer

Pre-create all target tables with the same constraints and use 'Truncate' mode

Question 6

A company wants to migrate its on-premises Oracle database to Amazon Aurora PostgreSQL. The company needs to automatically convert the Oracle schema to PostgreSQL-compatible format. Which AWS service should the company use?

Accepted Answer

AWS Schema Conversion Tool (AWS SCT). AWS Schema Conversion Tool (AWS SCT) is designed specifically to convert database schemas from one engine to another, including Oracle to Amazon Aurora PostgreSQL. It automatically translates Oracle DDL (tables, indexes, stored procedures, functions, etc.) into PostgreSQL-compatible format, handling data type mappings, PL/SQL to PL/pgSQL conversion, and other schema-level transformations. AWS DMS handles data migration, not schema conversion, making SCT the correct choice for this requirement.

Answer

AWS Database Migration Service (AWS DMS) with the Oracle native dump and load option

Answer

AWS Server Migration Service (AWS SMS)

Answer

AWS Database Migration Service (AWS DMS)

Question 7

An IAM policy is attached to a user who needs to restore an Amazon RDS DB instance from a DB snapshot. The user attempts to restore and receives an 'Access Denied' error. Which missing permission is MOST likely causing the failure?

Accepted Answer

rds:CreateDBInstance. To restore an Amazon RDS DB instance from a DB snapshot, the user must have the `rds:CreateDBInstance` permission. This is because the restore operation internally calls the CreateDBInstance API to create a new DB instance from the specified snapshot. Without this permission, the request fails with an 'Access Denied' error, even if the user has permissions to describe snapshots or instances.

Answer

rds:DescribeDBSnapshots

Answer

rds:DescribeDBInstances

Answer

rds:CreateDBSubnetGroup

Question 8

A company is migrating a 500 GB on-premises PostgreSQL database to Amazon RDS for PostgreSQL. The migration must have minimal downtime and support ongoing replication after the initial load. The network link has limited bandwidth, and the company wants to avoid costly data transfer fees. Which approach should be used?

Accepted Answer

Use AWS DMS with ongoing replication (CDC) from the start.. AWS DMS with ongoing replication (CDC) enables a full load followed by continuous change data capture, which meets the requirements of minimal downtime and ongoing replication. DMS compresses data in transit and can throttle network usage, helping to manage limited bandwidth and avoid costly data transfer fees by using the existing network link efficiently.

Answer

Use AWS Database Migration Service (DMS) with a full load only, then cut over.

Answer

Use pg_dump to export the database and pg_restore to import into RDS.

Answer

Use AWS Snowball Edge to transfer the database files, then load into RDS.

Question 9

A company is migrating an on-premises Microsoft SQL Server database to Amazon RDS for SQL Server. The database uses SQL Server Agent jobs for ETL processes. After migration, the company needs to continue running these jobs. What is the MOST efficient way to manage these jobs in the AWS cloud?

Accepted Answer

Recreate the jobs as AWS Lambda functions and trigger them with Amazon CloudWatch Events.. AWS Lambda functions triggered by Amazon CloudWatch Events (now Amazon EventBridge) provide a serverless, fully managed alternative to SQL Server Agent jobs. This approach eliminates the need to manage any compute infrastructure, as Lambda handles the execution of ETL logic directly against the RDS for SQL Server instance. It is the most efficient because it requires no additional servers, reduces operational overhead, and integrates natively with AWS monitoring and scheduling services.

Answer

Install SQL Server Agent on an Amazon EC2 instance and connect to RDS.

Answer

Use the AWS Schema Conversion Tool (AWS SCT) to convert the jobs to PostgreSQL functions.

Answer

Use AWS Database Migration Service (AWS DMS) tasks to replicate the jobs.

Question 10

A company is migrating a 3 TB on-premises Oracle database to Amazon Aurora PostgreSQL using AWS DMS. The migration task is failing with an error indicating insufficient memory. Which TWO actions should the company take to resolve this issue? (Choose TWO.)

Accepted Answer

Increase the instance class of the DMS replication instance.. Option C is correct because the 'insufficient memory' error during an AWS DMS migration indicates that the replication instance lacks the memory required to handle the data volume, transformation rules, or cache for large transactions. Increasing the instance class (e.g., from dms.c5.large to dms.c5.2xlarge) provides more memory and CPU, directly resolving the resource constraint.

Answer

Change the target database engine to Amazon RDS for MySQL.

Answer

Disable Multi-AZ on the DMS replication instance.

Answer

Use a smaller instance class for the DMS replication instance.

Question 11

A team is setting up a DMS migration task. The IAM policy above is attached to the DMS replication instance's IAM role. The team is unable to modify the target RDS instance's maintenance window during the migration. Which additional permission is missing?

Accepted Answer

Add 'rds:ModifyDBInstance' permission for the source RDS instance.. The correct answer is A because modifying the target RDS instance's maintenance window requires the `rds:ModifyDBInstance` permission. The DMS replication instance's IAM role must have this permission for the target RDS instance to allow the migration task to adjust the maintenance window, which is necessary to prevent conflicts during ongoing replication. Without this permission, the DMS task cannot alter the maintenance window, even if the IAM role has other RDS permissions.

Answer

Add 'rds:ModifyOptionGroup' permission for the target RDS instance.

Answer

Add 'dms:ModifyReplicationInstance' permission for the DMS replication instance.

Answer

Add 'ec2:DescribeVpcs' permission to the policy.

Question 12

A company wants to migrate an on-premises MySQL database to Amazon RDS for MySQL with minimal downtime. They plan to use AWS DMS. Which source database configuration is required to enable ongoing replication (CDC)?

Accepted Answer

Enable binary logging (binlog) on the source database. AWS DMS requires binary logging (binlog) enabled on the source MySQL database to capture ongoing changes for continuous replication (CDC). The binlog records all data modifications (INSERT, UPDATE, DELETE) in a sequential log, which DMS reads to apply those changes to the target RDS instance with minimal downtime. Without binlog enabled, DMS can only perform a full load migration and cannot support ongoing replication.

Answer

Set auto_increment_increment to 1

Answer

Enable the slow query log

Answer

Enable the query cache

Question 13

A company is migrating a 2 TB MySQL database from on-premises to Amazon RDS for MySQL. They need to minimize downtime and ensure data consistency. They plan to use AWS DMS. What is the first step they should take before starting the migration task?

Accepted Answer

Enable binary logging (binlog) on the source MySQL database.. AWS DMS requires binary logging (binlog) to be enabled on the source MySQL database to capture ongoing changes during the full-load and change data capture (CDC) phase. This ensures data consistency and minimizes downtime by allowing DMS to replicate incremental changes after the initial load. Without binlog, DMS cannot perform CDC, and the migration would be limited to a one-time snapshot, risking data loss or extended downtime.

Answer

Create a read replica of the source database to reduce load.

Answer

Disable foreign key checks on the source database to speed up the load.

Answer

Enable binary logging on the target RDS for MySQL instance.

Question 14

A company is planning to migrate a 1 TB MySQL database from on-premises to Amazon RDS for MySQL. The migration must have minimal downtime and support ongoing replication. Which THREE steps should the company include in the migration plan? (Choose THREE.)

Accepted Answer

Set up an AWS Direct Connect or VPN connection between on-premises and AWS.. Option A is correct because a stable, low-latency network connection (Direct Connect or VPN) is essential for AWS DMS to perform ongoing replication with minimal downtime. Without this, the replication can be interrupted by network issues, causing data loss or extended cutover windows.

Answer

Deploy an Amazon EC2 instance to act as a proxy for the DMS replication.

Answer

Install the AWS Schema Conversion Tool on the source server to convert the schema.

Question 15

A company is migrating a self-managed PostgreSQL database to Amazon Aurora PostgreSQL. They want to use the pglogical extension for logical replication to minimize downtime. Which THREE prerequisites must be met before setting up pglogical?

Accepted Answer

Add pglogical to shared_preload_libraries and restart the source database. Option A is correct because pglogical must be loaded into the shared preload libraries on the source PostgreSQL instance so that it is available at server start. Without this, the extension cannot be installed or used. The database must be restarted after modifying shared_preload_libraries for the change to take effect.

Answer

Configure max_wal_senders to at least 10

Answer

Enable track_commit_timestamp on the source

Question 16

A company is using Amazon DynamoDB with on-demand capacity mode. The application experiences occasional throttling during peak hours. The operations team wants to reduce throttling without changing the application code. What should they do?

Accepted Answer

Switch to provisioned capacity mode and configure auto scaling.. Option B is correct because DynamoDB on-demand capacity mode does not allow manual adjustment of capacity units; it scales automatically but can still throttle if traffic exceeds the previous peak by a large margin. Switching to provisioned capacity mode with auto scaling allows you to set a higher minimum capacity and scale proactively based on actual usage patterns, reducing throttling without code changes. This approach gives more control over capacity limits while still automating adjustments.

Answer

Increase the read and write capacity units in the on-demand mode.

Answer

Enable DynamoDB Accelerator (DAX) to cache reads.

Answer

Use DynamoDB global tables to distribute traffic across regions.

Question 17

A company is migrating a 2 TB SQL Server database to Amazon RDS for SQL Server. They need to minimize downtime and are using AWS DMS with CDC. The migration is taking longer than expected during the full load phase. Which change would most likely improve the full load performance?

Accepted Answer

Increase the number of parallel load tasks in the DMS task settings. Increasing the number of parallel load tasks in the DMS task settings allows the full load to be split into multiple concurrent threads, each handling a subset of tables or partitions. This directly improves throughput by utilizing more of the available source and target resources, which is the most effective way to accelerate a slow full load phase when using AWS DMS with CDC.

Answer

Disable CDC to focus on full load only

Answer

Use AWS Schema Conversion Tool to optimize schema

Answer

Use a larger RDS instance class for the target

Question 18

A company's Amazon RDS for PostgreSQL instance is experiencing high CPU utilization. The DB instance is a db.r5.xlarge with 500 GB of General Purpose SSD (gp2) storage. The application team reports that write-intensive batch jobs are causing latency. The DBA notices that the ReadIOPS metric is low but WriteIOPS is consistently near the instance's maximum. Which action would most effectively reduce write latency?

Accepted Answer

Increase the allocated storage to 1,000 GB to increase baseline IOPS.. The correct answer is B because increasing the allocated storage for a gp2 volume from 500 GB to 1,000 GB doubles the baseline IOPS from 1,500 to 3,000 (gp2 baseline is 3 IOPS per GB). Since the write-intensive workload is hitting the maximum IOPS of the current volume, this increase directly raises the IOPS ceiling, reducing write latency without changing storage type.

Answer

Change the storage type to Provisioned IOPS (io1) with the same IOPS.

Answer

Add a read replica to distribute read traffic.

Answer

Enable Multi-AZ deployment to offload writes to a standby instance.

Question 19

A database administrator is troubleshooting an Amazon RDS for PostgreSQL DB instance that is experiencing high CPU utilization. The administrator runs the following query to find the current running queries:

SELECT pid, now() - pg_stat_activity.query_start AS duration, query, state
FROM pg_stat_activity
WHERE state = 'active';

The output shows a high number of queries with a state of 'active' and durations exceeding several minutes. What should the administrator do FIRST to reduce CPU utilization?

Accepted Answer

Use pg_terminate_backend to terminate the long-running queries.. Option C is correct because the immediate cause of high CPU utilization is the long-running active queries consuming resources. Using pg_terminate_backend to terminate these queries will quickly free up CPU cycles, providing immediate relief. This is the first troubleshooting step before making configuration changes or scaling, as it directly addresses the symptom shown in the pg_stat_activity output.

Answer

Modify the max_connections parameter to limit concurrent sessions.

Answer

Scale up the DB instance to a larger instance class.

Answer

Enable pg_stat_statements to collect query performance data.

Question 20

A company is running an Amazon RDS for MySQL Multi-AZ DB instance. The primary instance in us-east-1a experiences an unexpected failure. After the automatic failover, the application team reports that write latency has increased significantly. The new primary instance is in us-east-1b. The DB instance class and storage configuration are identical. What is the MOST likely cause of the increased write latency?

Accepted Answer

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.

Answer

The DB instance class in us-east-1b is a different size than the original.

Answer

The Multi-AZ configuration uses asynchronous replication, causing higher latency.

Answer

The new primary is in the same Availability Zone as the application, but the standby is in a different AZ, causing synchronous replication overhead.