DBS-C01 questions and answers

Explanation: Correct Answer: A
Explanation from Amazon documents:
Amazon Aurora PostgreSQL is a fully managed relational database service that is compatible with PostgreSQL. Aurora PostgreSQL offers up to three times better performance than standard PostgreSQL, as well as high availability, scalability, security, and durability. Aurora PostgreSQL also supports Babelfish, which is a new feature that enables Aurora to understand queries from applications written for Microsoft SQL Server. Babelfish allows you to migrate your SQL Server databases to Aurora PostgreSQL with minimal or no code changes, and run complex T-SQL queries and stored procedures on Aurora PostgreSQL.
Migrating the database to Amazon Aurora PostgreSQL and turning on Babelfish will meet the requirements of minimizing database server maintenance and operating costs, and minimizing the need to rewrite code as part of the migration effort. This solution will allow the company to benefit from the performance, reliability, and cost-efficiency of Aurora PostgreSQL, while preserving the compatibility and functionality of SQL Server. The company will also avoid the hassle and expense of managing and licensing SQL Server on premises or on AWS.
Therefore, option A is the correct solution to meet the requirements.
Option B is not suitable because Amazon S3 is an object storage service that is not designed for OLTP workloads. Amazon Redshift Spectrum is a feature that allows you to query data in S3 using Amazon Redshift, but it is not compatible with SQL Server or T-SQL.
Option C is not optimal because Amazon RDS for SQL Server is a managed relational database service that supports SQL Server, but it does not offer the same performance, scalability, or cost savings as Aurora PostgreSQL. Kerberos authentication is a security feature that does not affect the migration effort or the operating costs.
Option D is not suitable because Amazon EMR is a big data processing service that runs Apache Hadoop and Spark clusters, not relational databases. EMR does not support SQL Server or T-SQL, and it is not optimized for OLTP workloads.

https://aws.amazon.com/blogs/mt/integrating-aws-cloudformation-with-aws-systems-manager-parameter-store/

Explanation: You can't modify the parameter settings of the default parameter groups. You can use a DB parameter group to act as a container for engine configuration values that are applied to one or more DB instances. If you create a DB instance without specifying a DB parameter group, the DB instance uses a

default DB parameter group. Each default DB parameter group contains database engine defaults and Amazon RDS system defaults. You can't modify the parameter settings of a default parameter group.
Instead, you create your own parameter group where you choose your own parameter settings. Not all DB engine parameters can be changed in a parameter group that you create.

Explanation:
https://medium.com/@souri29/how-to-migrate-from-amazon-rds-aurora-or-mysql-to-amazon-aurora-serverless-55f9a4a74078

Explanation: When you associate a new DB parameter group with a DB instance, the modified static and dynamic parameters are applied only after the DB instance is rebooted.

Explanation: https://docs.aws.amazon.com/dms/latest/userguide/CHAP_Target.S3.html Using Amazon S3 as a target for AWS Database Migration Service

Explanation: Amazon Aurora global databases span multiple AWS Regions, enabling low latency global reads and providing fast recovery from the rare outage that might affect an entire AWS Region. An Aurora global database has a primary DB cluster in one Region, and up to five secondary DB clusters in different Regions.
https://docs.aws.amazon.com/AmazonRDS/latest/AuroraUserGuide/aurora-global-database.html

https://docs.aws.amazon.com/dms/latest/userguide/CHAP_BestPractices.html#CHAP_Bes
tPractices.LOBS, "AWS DMS migrates LOB data in two phases: 1. AWS DMS creates a new row in the target table and populates the row with all data except the associated LOB value. 2.AWS DMS updates the row in the target table with the LOB data." This means that we would need two tasks, one per phase and use limited LOB mode for best performance.

https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/USER_WorkingWithAutomat
edBackups.html

PostgreSQL: sslrootcert=rds-cert.pem sslmode=[verify-ca | verify-full] Reference:
https://forums.aws.amazon.com/message.jspa?messageID=734076

Explanation:
https://aws.amazon.com/premiumsupport/knowledge-center/rds-snapshots-share-account/

https://docs.aws.amazon.com/AmazonRDS/latest/AuroraUserGuide/AuroraMySQL.Managi
ng.FaultInjectionQueries.html
"You can test the fault tolerance of your Amazon Aurora DB cluster by using fault injection queries. Fault injection queries are issued as SQL commands to an Amazon Aurora instance and they enable you to schedule a simulated occurrence of one of the following events: A crash of a writer or reader DB instance
A failure of an Aurora Replica A disk failure Disk congestion.
When a fault injection query specifies a crash, it forces a crash of the Aurora DB instance. The other fault injection queries result in simulations of failure events, but don't cause the event to occur. When you submit a fault injection query, you also specify an amount of time for the failure event simulation to occur for."

https://docs.aws.amazon.com/amazondynamodb/latest/developerguide/DAX.concepts.html

"If the request specifies strongly consistent reads, DAX passes the request through to DynamoDB. The results from DynamoDB are not cached in DAX. Instead, they are simply returned to the application."

Explanation:
https://docs.aws.amazon.com/vpc/latest/peering/vpc-peering-security-groups.html

https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/Appendix.SQLServer.Options
.Audit.html
https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/security_iam_service-with-
iam.html

https://docs.aws.amazon.com/dms/latest/userguide/CHAP_BestPractices.html#CHAP_Bes
tPractices.Performance

For a full load task, we recommend that you drop primary key indexes, secondary indexes, referential integrity constraints, and data manipulation language (DML) triggers. Or you can delay their creation until after the full load tasks are complete. You don't need indexes during a full load task, and indexes incur maintenance overhead if they are present. Because the full load task loads groups of tables at a time, referential integrity constraints are violated. Similarly, insert, update, and delete triggers can cause errors, for example if a row insert is triggered for a previously bulk loaded table. Other types of triggers also affect performance due to added processing.
https://docs.aws.amazon.com/dms/latest/userguide/CHAP_BestPractices.html

Correct Answer: B
Explanation from Amazon documents:
Amazon Quantum Ledger Database (Amazon QLDB) is a fully managed ledger database that provides a transparent, immutable, and cryptographically verifiable transaction log of all your application changes.
Amazon QLDB tracks each and every application data change and maintains a complete and verifiable history of changes over time 1. This makes it ideal for storing historical records that need to be tamper-proof and auditable. Amazon QLDB also supports PartiQL, a SQL-compatible query language that lets you query data using familiar SQL operators 2.
Amazon OpenSearch Service (successor to Amazon Elasticsearch Service) is a fully managed service that makes it easy to deploy, operate, and scale OpenSearch, an open source search and analytics engine.
Amazon OpenSearch Service lets you store, search, and analyze large volumes of data quickly and at low cost 3. You can use Amazon OpenSearch Service to index and query your employee record history data stored in Amazon QLDB using the QLDB Streams feature. This way, you can leverage the powerful search and analytics capabilities of OpenSearch Service to run fast and flexible queries on your historical data.
Therefore, option B is the best solution that meets the requirements of preventing modification of the historical records and maximizing the speed of the queries.
Option A is not suitable because DynamoDB is a key-value and document database that does not provide a ledger-like transaction log.
Option C is not suitable because Aurora PostgreSQL is a relational database that does not guarantee immutability of the historical records.
Option D is not suitable because Redshift is a data warehouse that is optimized for analytical queries on large datasets, not for storing and querying individual records.

Amazon RDS Proxy allows applications to pool and share connections established with the database, improving database efficiency and application scalability. With RDS Proxy, failover times for Aurora and RDS databases are reduced by up to 66% and database credentials, authentication, and access can be managed through integration with AWS Secrets Manager and AWS Identity and Access Management (IAM).
https://aws.amazon.com/rds/proxy/

https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/Overview.Encryption.html#Ov
erview.Encryption.Limitations

https://docs.aws.amazon.com/redshift/latest/gsg/rs-gsg-create-an-iam-role.html
"Now that you have created the new role, your next step is to attach it to your cluster. You can attach the role when you launch a new cluster or you can attach it to an existing cluster. In the next step, you attach the role to a new cluster."

https://docs.aws.amazon.com/redshift/latest/dg/copy-usage_notes-access-
permissions.html

https://aws.amazon.com/blogs/database/best-practices-for-migrating-rds-for-mysql-databases-to-amazon-aurora/
https://docs.aws.amazon.com/AmazonRDS/latest/AuroraUserGuide/AuroraPostgreSQL.Migrating.html#AuroraPostgreSQL.Migrating.RDSPostgreSQL.Replica

Explanation: Correct Answer: B
Explanation from Amazon documents:
The rds.log_retention_period parameter specifies how long your RDS for PostgreSQL DB instance keeps its log files. The default setting is 3 days (4,320 minutes), but you can set this value to anywhere from 1 day (1,440 minutes) to 7 days (10,080 minutes)123. By reducing the log retention period, you can free up storage space on your DB instance without affecting its availability or performance. To modify the rds.log_retention_period parameter, you need to use a custom DB parameter group for your RDS for PostgreSQL instance. You can modify the parameter value using the AWS Management Console, the AWS CLI, or the RDS API1. The parameter change is applied immediately, but it may take up to 24 hours for the database logs to be deleted 2. Therefore, you do not need to reboot the DB instance to save the changes or to reclaim the storage space.
Therefore, option B is the correct solution to meet the requirements.
Option A is incorrect because setting the rds.log_retention_period parameter to 0 disables log retention and prevents you from viewing or downloading any database logs 1. Rebooting the DB instance is also unnecessary and may cause downtime.
Option C is incorrect because the temp file_limit parameter controls the maximum size of temporary files that a session can generate, not the size of database logs. Modifying this parameter will not reclaim any storage space on the DB instance. Option D is incorrect because rebooting the DB instance is not required to save the changes or to reclaim the storage space.

https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/USER_ReadRepl.XRgn.html
With Amazon RDS, you can create a MariaDB, MySQL, Oracle, or PostgreSQL read replica in a different AWS Region from the source DB instance. Creating a cross-Region read replica isn't supported for SQL Server on Amazon RDS.

Explanation: General Purpose SSD storage is a cost-effective storage option that is ideal for a broad range of workloads running on medium-sized DB instances 1. General Purpose storage is best suited for development and testing environments 1. Since the database is primarily used for nightly batch processing, it does not require high I/O performance or low latency that Provisioned IOPS storage offers 12. Magnetic storage and Throughput Optimized HDD are not recommended for new storage needs, and they have lower storage limits than General Purpose SSD and Provisioned IOPS SSD1. Therefore, General Purpose SSD storage meets the requirements most cost-effectively.

Explanation: Correct Answer: B, E, F
Explanation from Amazon documents:
To upload data faster to a Neptune DB instance from a .csv file stored in Amazon S3, the database specialist should use the Neptune Bulk Loader, which is a feature that allows you to load data from external files directly into a Neptune DB instance 1. The Neptune Bulk Loader is faster and has less overhead than the API calls, such as SPARQL INSERT statements or Gremlin addV and addE steps 2. The Neptune Bulk Loader supports both RDF and Gremlin data formats 1.
To use the Neptune Bulk Loader, the database specialist needs to do the following13:

Ensure the vertices and edges are specified in different .csv files with proper header column formatting.
This is required for the Gremlin data format, which uses two .csv files: one for vertices and one for edges.
The first row of each file must contain the column names, which must match the property names of the graph elements. The files must also have a column named ~id for vertices and ~from and ~to for edges, which specify the unique identifiers of the graph elements 1. Ensure an IAM role for the Neptune DB instance is configured with the appropriate permissions to allow access to the file in the S3 bucket. This is required for the Neptune DB instance to read the data from the S3 bucket. The IAM role must have a trust policy that allows Neptune to assume the role, and a permissions policy that allows access to the S3 bucket and objects 3.
Create an S3 VPC endpoint and issue an HTTP POST to the database's loader endpoint. This is required for the Neptune DB instance to communicate with the S3 bucket without going through the public internet.
The S3 VPC endpoint must be in the same VPC as the Neptune DB instance. The HTTP POST request must specify the source parameter as the S3 URI of the .csv file, and optionally other parameters such as format, failOnError, parallelism, etc 1. Therefore, option B, E, and F are the correct steps to upload the data faster.
Option A is not necessary because Amazon Cognito is not used for authenticating the Neptune DB instance to the S3 bucket.
Option C is not suitable because AWS DMS is not designed for loading graph data into Neptune.
Option D is not efficient because curling the S3 URI and running the addVertex or addEdge commands will be slower and more costly than using the Neptune Bulk Loader.

https://docs.aws.amazon.com/amazondynamodb/latest/developerguide/CreateBackup.html#:~:text=If%20you%20don%27t%20want%20to%20create%20scheduling%20scripts%20and%20cleanup%20jobs%2C%20you%20can%20use%20AWS%20Backup%20to%20create%20backup%20plans%20with%20schedules%20and%20retention%20policies%20for%20your%20DynamoDB%20tables.%20AWS%20Backup%20runs%20the%20backups%20and%20deletes%20them%20when%20they%20expire.%20For%20more%20information%2C%20see%20the%20AWS%20Backup%20Developer%
20Guide.
https://docs.aws.amazon.com/amazondynamodb/latest/developerguide/backuprestore_Ho
wItWorks.html

https://docs.aws.amazon.com/amazondynamodb/latest/developerguide/howitworks-ttl.html

Explanation:
https://aws.amazon.com/premiumsupport/knowledge-center/rds-stop-seven-days/

The point-in-time restore capability of Amazon RDS for MySQL allows you to create a new DB instance with the same configuration as the original one, but with data restored to a specific time within your backup retention period. You can specify any time within your backup retention period, up to the last five minutes of your DB instance's usage 1. This feature is useful for recovering from logical corruption or user errors that affect your database.
However, when you use the point-in-time restore capability, you are creating a new DB instance with a different endpoint. Therefore, you need to change the application connection string to point to the new, restored DB instance. You can also delete or rename the original DB instance if you no longer need it1

Explanation: Correct Answer: D
Explanation from Amazon documents:
A CloudFormation stack policy is a JSON document that defines the update actions that can be performed on designated resources in a CloudFormation stack. A stack policy can be used to prevent accidental updates or deletions of stack resources, such as a production database.
The Update:Replace action is an update action that replaces an existing resource with a new one during a stack update. This action can cause data loss or downtime for the resource. To prevent this action from affecting the production database resources, the database engineer should use a Deny statement for the Update:Replace action on the production database resources in the stack policy. This statement will override any Allow statements for the same action and resource, and protect the production database
resources from being replaced during a stack update. Therefore, option D is the correct stack policy action to meet the requirements.
Option A is incorrect because the Update:Modify action is not a valid update action for a stack policy. The valid update actions are Update:Replace, Update:Skip, and Delete.
Option B is incorrect because it does not specify a valid update action for the Deny statement.
Option C is incorrect because the Update:Delete action is not a valid update action for a stack policy. The valid update actions are Update:Replace, Update:Skip, and Delete.

Connect - logins / DCL - authorizations (grant,revoke), DDL - schema updates

https://docs.aws.amazon.com/amazondynamodb/latest/developerguide/bp-partition-key-design.html#bp-partition-key-throughput-bursting
"DynamoDB provides some flexibility in your per-partition throughput provisioning by providing burst capacity. Whenever you're not fully using a partition's throughput, DynamoDB reserves a portion of that unused capacity for later bursts of throughput to handle usage spikes. DynamoDB currently retains up to 5 minutes (300 seconds) of unused read and write capacity. During an occasional burst of read or write activity, these extra capacity units can be consumed quickly--even faster than the per-second provisioned throughput capacity that you've defined for your table. DynamoDB can also consume burst capacity for background maintenance and other tasks without prior notice. Note that these burst capacity details might change in the future."

Explanation: Sharing an unencrypted manual DB snapshot enables authorized AWS accounts to directly restore a DB instance from the snapshot instead of taking a copy of it and restoring from that.
https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/USER_ShareSnapshot.html
However
Resource Access Manager could not share non-Aurora cluster.
https://docs.aws.amazon.com/ram/latest/userguide/shareable.html

Explanation:
https://aws.amazon.com/premiumsupport/knowledge-center/rds-db-storage-size/
Use AWS Database Migration Service (AWS DMS) for minimal downtime.

https://docs.aws.amazon.com/amazondynamodb/latest/developerguide/HowItWorks.Read
WriteCapacityMode.html

Explanation:
https://aws.amazon.com/premiumsupport/knowledge-center/dms-disable-public-access/

Explanation: Known patterns, minimum maintenance, miliseconds latency

References:
https://docs.aws.amazon.com/SchemaConversionTool/latest/userguide/Schema-
Conversion- Tool.pdf Converts DB/DW schema from source to target (including procedures / views / secondary indexes / FK and constraints) Mainly for heterogeneous DB migrations and DW migrations

AWS Backup is a fully managed service that simplifies data protection across AWS services, in the cloud, and on premises. You can use AWS Backup to create backup plans that define how and when your backups are created, how long they are stored, and where they are replicated 1. You can also use AWS Backup to monitor and audit your backup activity.
Using AWS Backup to create a backup plan and configure cross-Region replication is a cost-effective solution for the company's requirements, as it can automate the nightly backup of the DynamoDB table and store the backups in the us-west-2 Region for one year. You can specify the source and destination Regions, the backup vault, and the retention period for your cross-Region replication rule in your backup plan 2. You can also assign your DynamoDB table to your backup plan by using a resource assignment 3.

Target latency is the amount of time that AWS DMS takes to apply changes from the source database to the target database 1. High target latency can indicate performance issues or replication errors in the AWS
DMS task.
One possible cause of high target latency is the lack of a primary key or index in the target database. A primary key or index helps AWS DMS identify and apply changes to the corresponding rows in the target database. Without a primary key or index, AWS DMS has to scan the entire table to find the matching rows, which can increase the target latency and consume more CPU and memory resources 2.
Another possible cause of high target latency is the resource bottlenecks in the replication instance. The replication instance is the compute resource that runs the AWS DMS task and connects to the source and target endpoints. If the replication instance is under-provisioned or overloaded, it can affect the replication performance and cause high target latency. Some factors that can contribute to resource bottlenecks are insufficient network bandwidth, low disk space, high CPU utilization, or large transaction sizes 3.

https://docs.aws.amazon.com/dms/latest/userguide/CHAP_Target.Redshift.html

Explanation: Correct Answer: B, D, E
Explanation from Amazon documents:
To allow an existing Redshift cluster to access data from other Redshift clusters, Amazon RDS for PostgreSQL databases, and AWS Glue Data Catalog tables, the database specialist should use the following features123: Create external tables in the existing Redshift database to connect to the AWS Glue Data Catalog tables.
This feature allows you to query data stored in Amazon S3 using the AWS Glue Data Catalog as the metadata store. You can create external tables in your Redshift database that reference the data catalog tables and use SQL to query the data in S3. This feature is operationally efficient because it does not require moving or copying the data from S3 to Redshift1. Use federated queries to access data in Amazon RDS. This feature allows you to query and join data from one or more Amazon RDS for PostgreSQL and Amazon Aurora PostgreSQL databases with data already in your Amazon Redshift cluster. You can use SQL to query the RDS databases directly from your Redshift cluster without having to load or unload any data. This feature is operationally efficient because it reduces data movement and storage costs, and simplifies data access and analysis 2.
Use data sharing to access data from the other Redshift clusters. This feature allows you to securely share live data across different Redshift clusters without the complexity and delays associated with data copies and data movement. You can share data within or across AWS accounts using a consumer-producer model. The producer cluster grants privileges on one or more schemas, called datashares, to the consumer clusters. The consumer clusters can then query the shared data in the producer cluster as if it were local tables. This feature is operationally efficient because it enables real-time and transactionally consistent data access, and eliminates data duplication and stale data issues 3. Therefore, option B, D, and E are the correct steps to meet the requirements with the most operational efficiency. Option A is not efficient because it involves taking and restoring snapshots, which can be time-consuming and costly.
Option C is not efficient because it involves unloading and loading data between S3 and Redshift, which can also incur additional time and cost.
Option F is not necessary because it involves transferring the AWS Glue Data Catalog tables into S3, which can be avoided by using external tables to connect to the data catalog tables directly.