/aws-database-specialty

Tips and hints for anyone trying to take AWS Certified Database – Specialty.

MIT LicenseMIT

AWS Certified Database – Specialty

  • You are experiencing performance issues when saving data in an Amazon ElastiCache for Redis cluster with cluster mode disabled. The performance issues are occurring during the cluster's backup window. The cluster runs in a replication group containing three nodes. Memory on the nodes is fully utilized. The solution:

    • Configure the backup job to take a snapshot of a read replica.
    • Increase the reserved-memory-percent parameter value.

  • To encripyt RDS MySQL Create a snapshot of the database. Create an encrypted copy of the snapshot and Create a new database from the encrypted snapshot.

  • To make a alerting and a monitoring strategy for SQL Server database installed in a EC2 instance you can configure Amazon CloudWatch Application Insights for .NET and SQL Server to monitor and detect signs of potential problems. Configure CloudWatch Events to send notifications to an Amazon SNS topic.

  • Add more storage space to the DB instance using the ModifyDBInstance action if you run out of storage space in a MySQL RDS.

  • To support proper traceability, governance, and compliance, each database administration team member must start using individual, named accounts. Furthermore, longterm database user credentials should not be used. For that Enable IAM database authentication on the Aurora cluster. Create a database user for eachv team member without a password. Attach an IAM policy to each administrator’s IAM user account that grants the connect privilege using their database user account.

  • Use Amazon DynamoDB global tables and configure DynamoDB auto scaling for the tables if you want to run an application in several regions and low-latency.

  • Schedule an AWS Lambda function to create an hourly snapshot of the DB instance and another Lambda function to copy the snapshot to the second Region. For disaster recovery, create a new RDS Multi-AZ DB instance from the last snapshot. With that you can restore a database in a different region within 2 hours.

  • Take a manual snapshot of the source DB instance and share the snapshot privately with the new account. Specify the snapshot ARN in an RDS resource in an AWS CloudFormation template and use StackSets to deploy to the new account. ow you can achieve centrally manage resource provisioning for its development teams across multiple accounts.

  • Configure the RDS database to use IAM DB Authentication. Generate the access token using the aws rds generate-db-auth-token command. With that you will secure an Amazon RDS for MySQL database. Instead of an alphanumeric password, the database will only be accessed via a short-lived authentication token for greater security.

  • Configure a DynamoDB Accelerator to cache data to handle a large volume of requests.

  • You can copy a snapshot that has been encrypted using an AWS KMS encryption key. If you copy an encrypted snapshot, the copy of the snapshot must also be encrypted. If you copy an encrypted snapshot within the same AWS Region, you can encrypt the copy with the same KMS encryption key as the original snapshot, or you can specify a different KMS encryption key. If you copy an encrypted snapshot across Regions, you can't use the same KMS encryption key for the copy as used for the source snapshot, because KMS keys are Region-specific. Instead, you must specify a KMS key valid in the destination AWS Region. The source snapshot remains encrypted throughout the copy process. You can also encrypt a copy of an unencrypted snapshot. This way, you can quickly add encryption to a previously unencrypted DB instance.

  • Before you move a RDS DB instance to a new network, configure the new VPC, including the security group inbound rules, the subnet group, and the route tables. When you change the VPC for a DB instance, the instance reboots when the instance moves from one network to another. Because the DB instance isn't accessible during the network modification, change the VPC during a scheduled maintenance window. You can't change the VPC for a DB instance if the DB instance is in multiple Availability Zones. Convert the DB instance to a single Availability Zone, and then convert it back to a Multi-AZ DB instance after moving to the new VPC.

  • Amazon DynamoDB global tables provide a fully managed solution for deploying a multi-regional, multi-master database, without having to build and maintain your replication solution.

  • DynamoDB optionally supports conditional writes for PutItem, UpdateItem, DeleteItem. A conditional write will succeed only if the item attributes meet one or more expected conditions. Otherwise, it returns an error. Conditional writes are helpful in cases where multiple users attempt to modify the same item.

  • Create the RDS DB instance with Multi-AZ deployment and set the RDS maintenance window to perform maintenance items during a low activity period to minimize downtime due to maintenance.

  • You can create an AWS DMS task that captures ongoing changes to the source data store. You can do this capture while you are migrating your data. You can also create a task that captures ongoing changes after you complete your initial (full-load) migration to a supported target data store. This process is called ongoing replication or change data capture (CDC). AWS DMS uses this process when replicating ongoing changes from a source data store. This process works by collecting changes to the database logs using the database engine's native API.

  • With Amazon DocumentDB (with MongoDB compatibility), you can audit events that were performed in your cluster. Examples of logged events include successful and failed authentication attempts, dropping a collection in a database, or creating an index. By default, auditing is disabled on Amazon DocumentDB and requires that you to opt-in use this feature.

  • If you see the error Error Code: 1290. The MySQL server is running with the -–read-only option check if The user was connected to the Aurora cluster's reader endpoint when the error occurred.

  • Enable automatic workload management in the Redshift cluster and assign the group's queries to a queue with the HIGHEST priority. Enable short query acceleration. With that you will ensure that the group's critical queries, especially the short-running ones, get prioritized over less critical and more complex queries, minimize query management and avoid any additional costs.

  • AWS DMS uses the fn_dblog() or fn_dump_dblog() function in SQL Server to read the changes in the transaction log based on the log sequence number (LSN). This is how AWS DMS reads the ongoing changes from the source database in a on-premise to RDS migration involving SQL Server.

  • Launch the AWS DMS replication instance in the same AWS Region, VPC, and Availability Zone where the Oracle database instance is running if you want to migrate a Oracle database instance to a different region using AWS DMS and make sure that the performance impact in the Oracle database is minimized and if the source database is configured to dynamically transform and manipulate data using transformation rule expressions.

  • Using database cloning, you can quickly and cost-effectively create clones of all of the databases within an Aurora DB cluster. The clone databases require only minimal additional space when first created. Database cloning uses a copy-on-write protocol, in which data is copied at the time that data changes, either on the source databases or the clone databases.

  • Monitoring is an integral part of maintaining the reliability, availability, and performance of Amazon RDS and your AWS solutions. Two of the recommended RDS monitoring tools are:

    • Amazon RDS Enhanced Monitoring — provides metrics in real-time for the operating system (OS) that your DB instance runs on.
    • Performance Insights - expands on existing Amazon RDS monitoring features to illustrate your database's performance and analyze any issues that affect it. With the Performance Insights dashboard, you can visualize the database load and filter the load by waits, SQL statements, hosts, or users.

  • Graph databases are useful for connected, contextual, relationship-driven data. Graph databases have advantages over relational databases for certain use cases—including social networking, recommendation engines, and fraud detection—when you want to create relationships between data and quickly query these relationships. They can represent how entities relate by using actions, ownership, parentage, etc. Amazon Neptune uses graph structures such as nodes (data entities), edges (relationships), and properties to represent and store data. Edges can describe parent-child relationships, actions, product recommendations, purchases, and so on. A relationship, or edge, is a connection between two vertices that always has a start node, end node, type, and direction.

  • Many applications can benefit from the ability to capture changes to items stored in a DynamoDB table, at the point in time when such changes occur. DynamoDB Streams enables solutions such as these, and many others. DynamoDB Streams captures a time-ordered sequence of item-level modifications in any DynamoDB table and stores this information in a log for up to 24 hours. Applications can access this log and view the data items as they appeared before and after they were modified, in near-real-time. When an item in the table is modified, StreamViewType determines what information are written to the stream for this table. Valid values for StreamViewType are:

    • KEYS_ONLY — Only the key attributes of the modified item.
    • NEW_IMAGE — The entire item, as it appears after it was modified.
    • OLD_IMAGE — The entire item, as it appeared before it was modified.
    • NEW_AND_OLD_IMAGES — Both the new and the old images of the item.

  • In the IO:XactSync wait event, a session is issuing a COMMIT or ROLLBACK, requiring the current transaction’s changes to be persisted. Aurora is waiting for Aurora storage to acknowledge persistence. This wait event most often arises when there is a very high rate of commit activity on the system. You can sometimes alleviate this by modifying applications to commit transactions in batches.

  • Increase the size allocated to the RDS DB instance storage to reduce latency in RDS with general purpose SSD.

  • DynamoDB deletes expired items on a best-effort basis to ensure there's enough throughput for other data operations. Depending on the size and activity level of a table, an expired item's actual delete operation can vary. Because TTL is meant to be a background process, the nature of the capacity used to expire and delete items via TTL is variable (but free of charge). TTL typically deletes expired items within 48 hours of expiration. Processing takes place automatically, in the background, and doesn't affect read or write traffic to the table.

  • An ElastiCache Redis cluster provides varying levels of data durability, performance, and cost for implementing disaster recovery or fault tolerance of your cached data. You can choose the following options to improve the data durability of your ElastiCache cluster:

    • Daily automatic backups
    • Manual backups using Redis append-only file (AOF)
    • Setting up a Multi-AZ with Automatic Failover

  • Check if the client-side application's time-to-live is set too high and is caching the old DNS data. This can cause the connection failure for many minutes.

  • You can use the profiler in Amazon DocumentDB (with MongoDB compatibility) to log the execution time and details of operations that were performed on your cluster. The profiler is useful for monitoring the slowest operations on your cluster to improve individual query performance and overall cluster performance.

  • With the DeletionPolicy attribute, you can preserve or (in some cases) backup a resource when its stack is deleted. You specify a DeletionPolicy attribute for each resource that you want to control. If a resource has no DeletionPolicy attribute, AWS CloudFormation deletes the resource by default.

    • Delete - CloudFormation deletes the resource including its content if applicable during stack deletion.
    • Retain - CloudFormation keeps the resource without deleting the resource or its contents when its stack is deleted
    • Snapshot - For resources that support snapshots, AWS CloudFormation creates a snapshot for the resource before deleting it.

  • An Aurora DB cluster is fault-tolerant by design. The cluster volume spans multiple Availability Zones in a single AWS Region, and each Availability Zone contains a copy of the cluster volume data. This functionality means that your DB cluster can tolerate a failure of an Availability Zone without any loss of data and only a brief interruption of service. If the primary instance in a DB cluster using single-master replication fails, Aurora automatically fails over to a new primary instance in one of two ways:

    • By promoting an existing Aurora Replica to the new primary instance
    • By creating a new primary instance

  • You can use Amazon RDS Event Notifications and create proper event subscriptions that send notifications to the Manager using the RDS Console.

  • A local secondary index maintains an alternate sort key for a given partition key value. A local secondary index also contains a copy of some or all of the attributes from its base table; you specify which attributes are projected into the local secondary index when you create the table. The data in a local secondary index is organized by the same partition key as the base table, but with a different sort key. This lets you access data items efficiently across this different dimension. For greater query or scan flexibility, you can create up to five local secondary indexes per table.

  • To set the retention period for system logs, use the rds.log_retention_period parameter. You can find rds.log_retention_period in the DB parameter group associated with your DB instance. The unit for this parameter is minutes. For example, a setting of 1,440 retains logs for one day. The default value is 4,320 (three days).

  • AUTH can only be enabled for ElastiCache for Redis clusters if in-transit encryption was enabled during creation.

  • Amazon RDS provides metrics in real time for the operating system (OS) that your DB instance runs on. You can view the metrics for your DB instance using the console, or consume the Enhanced Monitoring JSON output from CloudWatch Logs in a monitoring system of your choice. By default, Enhanced Monitoring metrics are stored in the CloudWatch Logs for 30 days. To modify the amount of time the metrics are stored in the CloudWatch Logs, change the retention for the RDSOSMetrics log group in the CloudWatch console.

  • For applications that need to read or write multiple items, DynamoDB provides the BatchGetItem and BatchWriteItem operations. Using these operations can reduce the number of network round trips from your application to DynamoDB. In addition, DynamoDB performs the individual read or write operations in parallel. Your applications benefit from this parallelism without having to manage concurrency or threading.

  • You can authenticate to your DB instance using AWS Identity and Access Management (IAM) database authentication. IAM database authentication works with MySQL and PostgreSQL. With this authentication method, you don't need to use a password when you connect to a DB instance. Instead, you use an authentication token. By default, IAM database authentication is disabled on DB instances. You can enable IAM database authentication (or disable it again) using the AWS Management Console, AWS CLI, or the API. IAM authentication for PostgreSQL DB instances requires that the SSL value be 1. To allow an IAM user or role to connect to your DB instance, you must create an IAM policy. After that, you attach the policy to an IAM user or role. To use IAM authentication with PostgreSQL, connect to the DB instance, create database users, and then grant them the rds_iam role.

  • Which combination of steps should the Database Specialist do to protect the ElastiCache cluster from unauthorized access?

    • Set the associated security group to allow inbound traffic on TCP port 6379 from trusted clients only.
    • Enable encryption in-transit and encryption at-rest on the ElastiCache cluster including Redit AUTH. Configure the clients to use the auth-token parameter when connecting to the Redis cluster.

  • Take note that Amazon RDS is a fully-managed database service which means that AWS controls and manages the underlying servers that power your databases. Unlike a database hosted in Amazon EC2, you don't have the ability to directly connect to server and the configuration artifacts such as the pg_hba.conf or postgresql.conf files. You can use the GRANT or REVOKE commands to control table-level access.

  • To migrate from an RDS PostgreSQL DB instance to an Aurora PostgreSQL DB cluster, it is recommended to create an Aurora Read Replica of your source PostgreSQL DB instance. When the replica lag between the PostgreSQL DB instance and the Aurora PostgreSQL Read Replica is zero, you can stop replication. At this point, you can promote the Aurora Read Replica to be a standalone Aurora PostgreSQL DB cluster. This standalone DB cluster can then accept write loads.

  • You can use Microsoft Windows Authentication to authenticate users when they connect to your Amazon RDS for Microsoft SQL Server DB instance. The DB instance works with AWS Directory Service for Microsoft Active Directory, also called AWS Managed Microsoft AD, to enable Windows Authentication. When users authenticate with a SQL Server DB instance joined to the trusting domain, authentication requests are forwarded to the domain directory that you create with AWS Directory Service.

  • AWS CloudFormation StackSets extends the functionality of stacks by enabling you to create, update, or delete stacks across multiple accounts and regions with a single operation. Using an administrator account, you define and manage an AWS CloudFormation template, and use the template as the basis for provisioning stacks into selected target accounts across specified regions.

  • DynamoDB supports two types of secondary indexes:

    • Global secondary index — an index with a partition key and a sort key that can be different from those on the base table. A global secondary index is considered "global" because queries on the index can span all of the data in the base table, across all partitions.
    • Local secondary index — an index that has the same partition key as the base table, but a different sort key. A local secondary index is "local" in the sense that every partition of a local secondary index is scoped to a base table partition that has the same partition key value. To speed up queries on non-key attributes, you can create a global secondary index. A global secondary index contains a selection of attributes from the base table, but they are organized by a primary key that is different from that of the table. The index key does not need to have any of the key attributes from the table; it doesn't even need to have the same key schema as a table.

  • If the Amazon RDS instance is configured for Multi-AZ, you can perform the reboot with a failover. An Amazon RDS event is created when the reboot is completed. If your DB instance is a Multi-AZ deployment, you can force a failover from one Availability Zone (AZ) to another when you reboot.

  • An important part of the AWS Schema Conversion Tool is the database migration assessment report that it generates to help you convert your schema. The report summarizes all of the schema conversion tasks and details the action items for schema that can't be converted to the DB engine of your target DB instance. You can view the report in the application. To do so, export it as a comma-separated value (CSV) or PDF file.

  • Amazon Redshift Federated Query enables you to use the analytic power of Amazon Redshift to directly query data stored in Amazon Aurora PostgreSQL and Amazon RDS for PostgreSQL databases.

  • Configure all Aurora Replicas to have the same instance class as the primary DB instance. Implement Aurora PostgreSQL DB cluster cache management. Set the failover priority to tier-0 for the primary DB instance and one replica with the same instance class. Set the failover priority to tier-1 for the other Aurora Replicas. With that you can avoid problems if a failover event occurs.

  • With Amazon Aurora with MySQL compatibility, you can backtrack a DB cluster to a specific time, without restoring data from a backup.

  • Backtracking allows you to rewind the DB cluster to the time you specify. When you specify a time for a backtrack, Aurora automatically chooses the nearest possible consistent time. Although backtracking is not a replacement for backing up your database, it provides the following advantages over traditional backup and restore:

    • You can easily undo mistakes. If you mistakenly perform a destructive action, such as a DELETE without a WHERE clause, you can backtrack the DB cluster to a time before the destructive action with minimal interruption of service.
    • You can backtrack a DB cluster quickly. Restoring a DB cluster to a point in time launches a new DB cluster and restores it from backup data or a DB cluster snapshot, which can take hours. Backtracking a DB cluster doesn't require a new DB cluster and rewinds the DB cluster in minutes.
    • You can explore earlier data changes. You can repeatedly backtrack a DB cluster back and forth in time to help determine when a particular data change occurred. For example, you can backtrack a DB cluster for three hours and then backtrack forward in time for one hour. In this case, the backtrack time is two hours before the original time.

  • For data protection, AWS recommends that you protect your AWS account credentials and set up principals with AWS Identity and Access Management (IAM). Doing this means that each user is given only the permissions necessary to fulfill their job duties. It is also recommended that you secure your data in the following ways:

    • Use multi-factor authentication (MFA) with each account.
    • Use SSL/TLS to communicate with AWS resources.
    • Set up API and user activity logging with AWS CloudTrail.
    • Use AWS encryption solutions, along with all default security controls within AWS services.
    • Use advanced managed security services such as Amazon Macie, which assists in discovering and securing personal data that is stored in Amazon S3.

  • If yo need to migrate large LOBs configure an AWS DMS task to migrate the tables without LOBs. Configure another AWS DMS task using limited LOB mode with a LobMaxSize setting of 100MB to migrate LOB data to the target RDS instance.

  • When you connect to an Aurora cluster, the hostname and port that you specify points to an intermediate handler called an endpoint. You can map each connection to the appropriate instance or group of instances based on your use case. Amazon Aurora has four types of endpoints available:

    • cluster endpoint (or writer endpoint),
    • reader endpoint,
    • instance endpoint
    • custom endpoint

  • Each Aurora cluster has a single built-in reader and cluster endpoint, whose name and other attributes are managed by Aurora. You cannot create, delete, or modify both kinds of endpoints. An instance endpoint connects to a specific DB instance within an Aurora cluster. Meanwhile, each custom endpoint has an associated type that determines which DB instances are eligible to be associated with that endpoint. Currently, the type can be READER, WRITER, or ANY. Only DB instances that are read-only Aurora Replicas can be part of a READER custom endpoint. Both read-only Aurora Replicas and the read/write primary instance can be part of an ANY custom endpoint. Aurora directs connections to cluster endpoints with type ANY to any associated DB instance with equal probability. The WRITER type applies only to multi-master clusters because those clusters can include multiple read/write DB instances. Furthermore, you can define a list of DB instances to include in, or exclude from, a custom endpoint. We refer to these lists as static and exclusion lists, respectively. Each custom endpoint can contain only one of these list types. While the static list enforces that the endpoint connects to the specified instances, the exclusion list enforces the endpoint to represent all DB instances in the cluster, including any that you add in the future, except the ones specified for exclusion.

  • Aurora Auto Scaling dynamically adjusts the number of Aurora Replicas provisioned for an Aurora DB cluster using single-master replication. Aurora Auto Scaling is available for both Aurora MySQL and Aurora PostgreSQL. Aurora Auto Scaling enables your Aurora DB cluster to handle sudden increases in connectivity or workload. When the connectivity or workload decreases, Aurora Auto Scaling removes unnecessary Aurora Replicas so that you don't pay for unused provisioned DB instances.

  • 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. You create a read replica in a different AWS Region to do the following:

    • Improve your disaster recovery capabilities.
    • Scale read operations into an AWS Region closer to your users.
    • It is easier to migrate from a data center in one AWS Region to a data center in another AWS Region. Creating a read replica in a different AWS Region from the source instance is similar to creating a replica in the same AWS Region. However, you can only create a cross-Region Amazon RDS read replica from a source Amazon RDS DB instance that is not a read replica of another Amazon RDS DB instance.

  • Amazon RDS uses the Amazon Simple Notification Service (Amazon SNS) to provide notification when an Amazon RDS event occurs. These notifications can be in any notification form supported by Amazon SNS for an AWS Region, such as an email, a text message, or a call to an HTTP endpoint. Amazon RDS groups these events into categories that you can subscribe to so that you can be notified when an event in that category occurs. You can subscribe to an event category for a DB instance, DB snapshot, DB parameter group, or DB security group.

  • Redis and Memcached are popular, open-source, in-memory data stores. Although they are both easy to use and offer high performance, there are important differences to consider when choosing an engine. Memcached is designed for simplicity while Redis offers a rich set of features that make it effective for a wide range of use cases. In terms of commands execution, Redis is mostly a single-threaded server while memcached is multithreaded.

  • For fast recovery of the writer DB instance in your Aurora PostgreSQL clusters if there's a failover, use cluster cache management for Amazon Aurora PostgreSQL. Cluster cache management ensures that application performance is maintained if there's a failover. In a typical failover situation, you might see a temporary but large performance degradation after failover. This degradation occurs because when the failover DB instance starts, the buffer cache is empty. An empty cache is also known as a cold cache. A cold cache degrades performance because the DB instance has to read from the slower disk, instead of taking advantage of values stored in the buffer cache.

  • If the replica SQL_THREAD is the source of replication delays, those delays could be due to the following reasons:

    • Long-running queries on the primary DB instance
    • Insufficient DB instance class size or storage
    • Parallel queries executed on the primary DB instance
    • Binary logs synced to the disk on the replica DB instance
    • Binlog_format on the replica is set to ROW
    • Replica creation lag

  • In the IO:XactSync wait event, a session is issuing a COMMIT or ROLLBACK, requiring the current transaction’s changes to be persisted. Aurora is waiting for Aurora storage to acknowledge persistence.

  • Launch and configure a snapshot copy grant for a master key in another AWS region. Enable cross-region snapshots in the Redshift cluster to copy snapshots of the cluster to the other region. With that a backup cluster must be highly available and fault-tolerant even in the event of an AWS region outage.

  • When you create an on-demand backup, a time marker of the request is cataloged. The backup is created asynchronously by applying all changes until the time of the request to the last full table snapshot. Backup requests are processed instantaneously and become available for restore within minutes. When you do a restore, you can change the following table settings:

    • Global secondary indexes (GSIs)
    • Local secondary indexes (LSIs)
    • Billing mode
    • Provisioned read and write capacity
    • Encryption settings

  • However, some settings are not carried over on the restored table and you must manually configure them after restoring. You must manually set up the following on the restored table:

    • Auto scaling policies
    • AWS Identity and Access Management (IAM) policies
    • Amazon CloudWatch metrics and alarms
    • Tags
    • Stream settings
    • Time to Live (TTL) settings

  • AWS Database Migration Services (DMS) provides support for data validation to ensure that your data was migrated accurately from the source to the target. If you enable it for a task, AWS DMS begins comparing the source and target data immediately after a full load is performed for a table, and reports any mismatches. Also, for a CDC-enabled task, AWS DMS compares the incremental changes and reports any mismatches. Data validation can be done during:

    • A new AWS DMS task
    • An existing AWS DMS task
    • A completed AWS DMS task
    • Revalidation from the table statistics section of the AWS DMS task

  • You can simulate a disk failure for an Aurora DB cluster using this fault injection query:

    • ALTER SYSTEM SIMULATE DISK FAILURE

  • DynamoDB table backup is created asynchronously and DynamoDB backups do not guarantee causal consistency across items.

  • For Aurora MySQL, you can't delete a DB instance in a DB cluster if both of the following conditions are true:

    • The DB cluster is a read replica of another Aurora DB cluster
    • The DB instance is the only instance in the DB cluster.

  • To delete a DB instance in this case, first promote the DB cluster so that it's no longer a read replica. After the promotion completes, you can delete the final DB instance in the DB cluster.

  • If you disable point-in-time recovery and later re-enable it on a table, you reset the start time for which you can recover that table. As a result, you can only immediately restore that table using the LatestRestorableDateTime.

  • A ProvisionedThroughputExceededException error means that you’ve exceeded your maximum allowed provisioned throughput for a table or for one or more global secondary indexes. You can resolve this issue by increasing the throughput of your DynamoDB table.

  • If you encountes ERROR: could not write block 18980612 of temporary file: No space left on device Lessen the database workload to reduce the amount of temporary storage required and modify the Aurora database to use a DB instance class with more local SSD storage.

  • An Amazon RDS DB instance in the STORAGE_FULL state doesn't have enough available space to perform basic operations, such as connecting to or restarting the instance. To resolve this issue, follow these steps:

    • Confirm that the DB instance status is STORAGE_FULL.
    • Add more storage space to the instance.
    • Increase the allocated storage property of your DB instance.

  • Performance Insights expands on existing Amazon RDS monitoring features to illustrate your database's performance and help you analyze any issues that affect it. With the Performance Insights dashboard, you can visualize the database load and filter the load by waits, SQL statements, hosts, or users. To use Performance Insights, you must enable it on your DB instance. Enabling and disabling Performance Insights does not cause downtime, a reboot, or a failover. You have the following options when you choose to Enable Performance Insights:

    • Retention – The amount of time to retain Performance Insights data. Choose either seven days (the default) or two years.
    • Master key – Specify your AWS Key Management Service (AWS KMS) key. Performance Insights encrypts all potentially sensitive data using your AWS KMS key. Data is encrypted in transit and at rest.

  • When you restore a DB instance from a DB snapshot, the default DB parameter and default security group are associated with the restored instance. That association means that the default security group does not allow access to the DB instance, and no custom parameter settings are available in the default parameter group. You need to retain the DB parameter group and security group associated with the DB instance that was used to create the DB snapshot.

  • Beyond external security threats, managed databases need to provide protection against insider risks from database administrators (DBAs). Database Activity Streams, currently supported for Amazon Aurora, provides a real-time data stream of the database activity in your relational database. When integrated with 3rd party database activity monitoring tools, you can monitor and audit database activity to provide safeguards for your database and meet compliance and regulatory requirements.

  • To allow an IAM user or role to connect to your DB instance, you must create an IAM policy that allows rds-db:connect action. After that, you attach the policy to an IAM user or role. To use IAM authentication with PostgreSQL, connect to the DB instance, create database users, and then grant them the rds_iam role.

  • DB clusters that are encrypted can't be modified to disable encryption. You can't convert an unencrypted DB cluster to an encrypted one. However, you can restore an unencrypted Aurora DB cluster snapshot to an encrypted Aurora DB cluster. To do this, specify a KMS encryption key when you restore from the unencrypted DB cluster snapshot.

  • You can require that connections to your PostgreSQL DB instance use SSL by using the rds.force_ssl parameter. By default, the rds.force_ssl parameter is set to 0 (off). You can set the rds.force_ssl parameter to 1 (on) to require SSL for connections to your DB instance.

  • AWS retains log data published to CloudWatch Logs for an indefinite period unless you specify a retention period.

  • Each developer can perform DDL operations for the schema changes on the MySQL read replica once the read replica is in sync with its primary DB instance. Then the developer can promote the read replica and direct the application to use the promoted instance during the development phase. This solution isolates the schema changes done by each developer to their own promoted instance. This also avoids the problem of keeping track of the "correct restore point" that the team faced while using the same DB instance.

  • To share an encrypted Amazon RDS DB snapshot with another account:

    • Add the target account to a custom (non-default) KMS key.
    • Copy the snapshot using the customer managed key, and then share the snapshot with the target account.
    • Copy the shared DB snapshot from the target account.

  • You can use Aurora Read Replicas to migrate from an Amazon RDS DB Instance for MySQL to Amazon Aurora. The migration process begins by creating a DB snapshot of the existing DB Instance and then using it as the basis for a fresh Aurora Read Replica. After the replica has been set up, replication is used to bring it up to date with respect to the source. Once the replication lag drops to 0, the replication is complete. At this point, you can make the Aurora Read Replica into a standalone Aurora DB cluster and point your client applications at it.

  • Applications that need to perform many kinds of queries, using a variety of different attributes for their query criteria should use Global Secondary Indexes.

  • If the writes are throttled on the Global Secondary Indexes, then the main table will be throttled, even though the Write Capacity Units on the main tables are fine.

  • A Global Secondary Index contains a selection of attributes from the base table which are organized by a primary key that is different from that of the table.

  • Multi-AZ deployments for RDS MySQL follow synchronous replication whereas Multi-AZ deployments for Aurora MySQL follow asynchronous replication.

  • Read Replicas can be manually promoted to a standalone database instance for RDS MySQL whereas Read Replicas for Aurora MySQL can be promoted to the primary instance.

  • The primary and standby DB instances are upgraded at the same time for RDS MySQL Multi-AZ. All instances are upgraded at the same time for Aurora MySQL.

  • To secure ElasticCache:

    • Configure the ElastiCache cluster to have both in-transit as well as at-rest encryption.
    • Create the cluster with auth-token parameter and make sure that the parameter is included in all subsequent commands to the cluster.
    • Configure the security group for the ElastiCache cluster with the required rules to allow inbound traffic from the cluster itself as well as from the cluster's clients on port 6379.

  • To copy snapshots for AWS KMS–encrypted clusters to another AWS Region, you need to create a grant for Redshift to use a KMS customer master key (CMK) in the destination AWS Region. Then choose that grant when you enable copying of snapshots in the source AWS Region. You cannot use a KMS key from the source Region as AWS KMS keys are specific to an AWS Region.

  • IAM database authentication works with MySQL and PostgreSQL.

  • Multi-AZ follows synchronous replication and spans at least two Availability Zones within a single region. Read Replicas follow asynchronous replication and can be within an Availability Zone, Cross-AZ, or Cross-Region.

  • Any database engine level upgrade for an RDS DB instance with Multi-AZ deployment triggers both the primary and standby DB instances to be upgraded at the same time. This causes downtime until the upgrade is complete.

  • If it provisioned 20 Read Capacity Units for the table. then you will have Read throughput of 80KB/sec with strong consistency, Read throughput of 160KB/sec with eventual consistency, Transactional read throughput of 40KB/sec.

  • You can't stop a DB instance if that instance has a read replica or that instance itself is a read replica. Therefore, for the given use-case, you must delete all Read Replicas before stopping the corresponding primary RDS DB instance.

  • Use ElastiCache to improve performance of compute-intensive workloads.

  • A stack set lets you create stacks in AWS accounts across regions by using a single AWS CloudFormation template. All the resources included in each stack are defined by the stack set's AWS CloudFormation template. As you create the stack set, you specify the template to use, as well as any parameters and capabilities that the template requires.

  • An unencrypted RDS DB instance will result in unencrypted Read Replicas only, You can't have an encrypted Read Replica of an unencrypted DB instance.

  • You can restore an unencrypted Aurora DB cluster snapshot to an encrypted Aurora DB cluster. Aurora DB offers this facility, though this is not possible for RDS DB instances. You can't convert an unencrypted Aurora DB cluster to an encrypted one.

  • To copy an encrypted snapshot from one AWS Region to another, you must specify the KMS key identifier of the destination AWS Region.

  • For Amazon Aurora, each Read Replica is associated with a priority tier (0-15). In the event of a failover, Amazon Aurora will promote the Read Replica that has the highest priority (the lowest numbered tier). If two or more Aurora Replicas share the same priority, then Amazon Aurora promotes the replica that is largest in size.

  • The DeleteTable operation deletes a table and all of its items. After a DeleteTable request, the specified table is in the DELETING state until DynamoDB completes the deletion. This is the most cost-efficient option.

  • Amazon RDS for MySQL allows you to create a second-tier read replica from an existing first-tier Read Replica.

  • You can configure RDS source DB instance as a Multi-AZ deployment to avoid I/O suspension that occurs when a Read Replica is initiated.

  • Only ElastiCache for Redis (not Memcached) is HIPAA compliant.

  • For a dynamic parameter, the changes are applied immediately. However, for a static parameter, the change takes effect after you manually reboot the DB instance.

  • Applications that need to perform many kinds of queries, using a variety of different attributes for their query criteria should use Global Secondary Indexes.

  • A custom endpoint for an Aurora cluster represents a set of DB instances that you choose. When you connect to the endpoint, Aurora performs load balancing and chooses one of the instances in the group to handle the connection. An Aurora DB cluster has no custom endpoints until you create one. You can create up to five custom endpoints for each provisioned Aurora cluster.

  • While migrating an Amazon Relational Database Service (Amazon RDS) for Oracle source database to a different AWS Region, AWS suggests that you create the replication instance in the VPC of the target AWS Region.

  • The default backup retention period is one day if you create the DB instance using the Amazon RDS API.

  • DB Snapshots can be created with the AWS Management Console, CreateDBSnapshot API, or create-db-snapshot command.

  • Disabling automatic backups for a DB instance deletes all existing automated backups for the instance.

  • You can map multiple IAM users or roles to the same database user account.

  • If your application is running on Amazon Elastic Compute Cloud (Amazon EC2), then you can use EC2 instance profile credentials to access the database.

  • IAM database authentication should only be used if your application requires no more than 200 new connections per second.

  • The automated RDS backup retention period has a maximum configurable value of 35 days.

  • It is impossible to delete the last instance of a read replica DB cluster. It must be promoted to a standalone DB cluster.

  • mysqldump utility can be used to create a backup of the existing on-premise database. The backup can then be transferred in compressed form to the AWS cloud. Finally, mysqldump utility can be used to restore the data into an RDS instance.

  • Amazon Aurora Multi-Master cluster offers write capability to multiple instances. In applications where zero downtime is required for database write operations, a multi-master cluster can be used to avoid an outage when a writer instance becomes unavailable.

  • It is possible to create a Route 53 hosted zone and record set to store the DNS endpoints of the RDS read replica instances. Each endpoint should have the same value for the weighted routing configuration to ensure equal distribution of traffic among the RDS read replicas.

  • DynamoDB Point-in-Time Restore enables recovery of a DynamoDB table across AWS regions. Further, it enables a full table to restore, as well restore of GSI’s and LSI’s. Restoring a DynamoDB table using Point-in-Time restore consumes no provisioned throughput. Data transfer charges between the regions are the only costs associated with this solution.

  • Amazon Aurora only supports InnoDB MySQL compatible storage engine.

  • One read capacity unit is equivalent to one strongly consistent read per second, or two eventually consistent reads per second, for an item up to 4 KB in size. One write capacity unit is equivalent to one write per second for an item up to 1 KB in size.

  • CloudFormation Change sets enable the preview of proposed changes to a stack to assess the impact on existing resources. This functionality allows the team to check if any existing databases will be deleted or replaced upon application of the updated CloudFormation template.

  • CloudFormation Change sets enable the preview of proposed changes to a stack to assess the impact on existing resources. This functionality allows the team to check if any existing databases will be deleted or replaced upon application of the updated CloudFormation template.

  • The query operation is used to search items based on primary key values. The name of the partition key attribute and a single value for that attribute must be provided with the query. The query returns all items with that partition key value. A filter expression parameter can be used to narrow down the results based on some required criteria. A filter expression is applied AFTER a query operation completes, but BEFORE the results are returned to the client. Therefore, a query operation consumes the same amount of read capacity, regardless of whether a filter expression is present.

  • PrivateLink Gateway endpoint is used to integrate DynamoDB to Amazon VPC without the use of Internet Gateway.

  • PrivateLink Interface Endpoint does not support DynamoDB.

  • Amazon Neptune supports Gremlin and SPARQL query languages.

  • RAID 0 is used to distribute I/O across volumes and achieve increased IOPS and throughput performance.

  • Parallel scans can be used by multiple worker threads in an application to perform a scan of a DynamoDB table much faster.

  • AWS Schema Conversion Tool (SCT) can be used to produce the database migration assessment report.

  • AWS SCT does not include database drivers with the installation. Database drivers required to connect to the source and target database need to be downloaded and installed as a separate manual step.

  • SPARQL is the query language used with RDF models and supported by Amazon Neptune.

  • You can stop a DB instance for up to seven days when it is in a Multi-AZ configuration.

  • The maximum item size in DynamoDB is 400 KB.

  • CloudWatch Application Insights for .NET and SQL Server collects metrics and logs of .NET and SQL Server applications across the stack (e.g., IIS server, OS, SQL Server database). It collects performance metrics and helps in troubleshooting by automatically correlating errors and creating visual dashboards.

  • The reserved-memory-percent parameter specifies the amount of memory available for non-data use. Setting or increasing this parameter value for cluster nodes can improve performance during backups since more memory will be allocated for the backup processes.

  • –ssl_mode=verify-full ensures that the client connection is encrypted and performs validation of the certificate to ensure that the server is trusted. This is for Aurora MySQL DB Cluster..

  • Setting the trustServerCertificate property to false ensures that the client will validate the TLS certificate and confirm that the server is the correct server to connect to. This is for a SQL Server on the Amazon RDS database

  • DynamoDB does not have an equivalent command to the SQL TRUNCATE command. The only way to delete all data in a DynamoDB table is to perform a table scan to fetch all the item keys. Then you must iterate through the keys and delete each item using the delete-item command.

  • ssl_server_dn_match property must be set to true on the client to perform certificate verification and ensure that the certificate is from the server. This is for Sql Server RDS.

  • The first and most important step in designing a DynamoDB application is to identify the data query access patterns that the application utilizes and model the database based on those patterns.

  • The Global Datastore feature for ElastiCache can be used to create cross-region read replica clusters.

  • AWS DMS does not migrate indexes. For large data migrations, it is most efficient to pre-create indexes in the target Amazon DocumentDB cluster before migrating the data.

  • Setting the “secondaryPreferred” read preference option distributes requests to read replicas on DocumentDB. This is recommended as it increases the performance efficiency of the database cluster.

  • db.runCommand on DocumentDB can be used with explain() method to provide a detailed execution plan of a query and provide insight into the query performance.

  • DocumentDB profiler feature can be enabled to log the details (including execution time) of MongoDB operations to CloudWatch Logs. Then, CloudWatch Logs Insights can be used to analyze the data and investigate slow queries.

  • MongoDB currentOp command can be used to list queries that are either blocked or executing longer than a specified time. Reference.

  • Creating an AWS Lambda Function to read the file from the S3 bucket and import the data items into the DynamoDB table is the simplest and most cost-efficient method to import CSV data.

  • Write-Through caching strategy updates the cache with every write operation, thus ensuring that the cache always contains the most recent data.

  • If the requirement is to perform strongly consistent queries on a new partition key. In this scenario, a new DynamoDB table must be created as GSI only supports eventual consistency.

  • LSI shares write-capacity with the base table.

  • AWS SDK for Java provides direct data type mapping from Date Java data type to DynamoDB String type with date values stored in ISO 8601 format.

  • RDS proxy is a service that can be used to pool simultaneous connections from serverless applications and alleviate the connection management from the RDS database instance.

  • ElastiCache for Redis (Cluster Mode Disabled) clusters support scaling up or down of read capacity by creating or deleting read replicas within the cluster.

  • ElastiCache for Redis with Cluster Mode Enabled clusters provide multiple write-endpoints which can be used to distribute traffic for write-heavy applications.

  • Amazon ElastiCache for Redis with cluster mode disabled cluster consists of a single shard with up to a maximum of 5 read replicas.

  • AWS Workload Qualification Framework (AWS WQF) is a standalone tool that is used during the database migration planning phase to assess migration workloads. It produces an assessment report detailing migration complexity and size and provides migration strategy and tool recommendations.

  • The Aurora Serverless DB cluster can’t have a public IP address. An Aurora Serverless DB cluster can only be accessed from within a VPC. Therefore, we require to connect the AWS Lambda to the private VPC using an elastic network interface.

  • enabling Data API on the Amazon Aurora Serverless cluster allows web-based applications to access the cluster over a secure HTTP endpoint.

  • Amazon RDS root certificates are available for download at https://s3.amazonaws.com/rds-downloads/rds-ca-2019-root.pem.

  • The user must use an authentication token to access Amazon RDS database instances when IAM authentication is configured. To generate a temporary authentication token, aws rds generate-db-auth-token CLI command can be used.

  • For MariaDB, MySQL, and Oracle RDS instances, when the source database is deleted, read replicas in the same region and cross-region read replicas are promoted. For PostgreSQL RDS instances, when the source database is deleted, read replicas in the same region are promoted, and cross-region read replicas are set to replication status “terminated.”