Amazon RDS

Multi-AZ deployments

I recommend that they migrate their on-premises database to Amazon Relational Database Service (Amazon RDS), and use a Multi-AZ deployment for high availability.

In a Multi-AZ deployment, Amazon RDS automatically creates a primary database (DB) instance and synchronously replicates the data to an instance in a different Availability Zone. When it detects a failure, Amazon RDS automatically fails over to a standby instance without manual intervention. This failover mechanism meets the customer’s need to have a highly available database.

The following diagram shows a Multi-AZ deployment with one standby DB instance, and how it works.

For even higher availability, the customer could explore deploying two standby DB instances, and use three Availability Zones instead of two.

Say that you deploy MySQL or PostgreSQL databases in three Availability Zones by using Amazon RDS Multi-AZ with two readable standbys. With this configuration, you can see automatic failovers typically happen in under 35 seconds, and with a transaction-commit latency that can be up to two times faster when compared to an Amazon RDS Multi-AZ deployment with one standby. You can also gain additional read capacity, and a choice of AWS Graviton2–based or Intel–based instances for compute.

The following diagram shows a Multi-AZ deployment with two standby DB instances, and how it works.

Read replicas

Customers can also make RDS more highly available by using read replicas.

Amazon RDS read replicas provide enhanced performance and durability for Amazon RDS DB instances. For read-heavy database workloads, read replicas make it easier to elastically scale out beyond the capacity constraints of a single DB instance.

You can create one or more replicas of a given source DB instance and serve high-volume application read traffic from multiple copies of your data, which increases aggregate read throughput. Read replicas can also be promoted to become standalone DB instances, when needed.

Read replicas are available in Amazon RDS for MySQL, MariaDB, PostgreSQL, Oracle, Microsoft SQL Server, and Amazon Aurora.

For the MySQL, MariaDB, PostgreSQL, Oracle, and SQL Server database engines, Amazon RDS creates a second DB instance by using a snapshot of the source DB instance. Amazon RDS then uses the engine’s native asynchronous replication to update the read replica when there’s a change to the source DB instance.

The read replica operates as a DB instance that allows only read-only connections. Applications can connect to a read replica like they would connect to any DB instance. Amazon RDS replicates all databases in the source DB instance.

Here’s an example of when to use a read replica. Say that you’re running reports on your database, which is causing performance issues with CPU-intensive reads. You can use a read replica and direct all the reporting queries to that replica instead of to the primary instance. Offloading some of the intense queries to the replica should result in enhanced performance on the primary instance.

Scaling Amazon RDS instances

Scale your instance up vertically

When you create an RDS DB instance, you choose a database instance type and size.

Amazon RDS provides a selection of instance types that are optimized to fit different use cases for relational databases. Instance types comprise varying combinations of CPU, memory, storage, and networking capacity. You have the flexibility to choose the appropriate mix of resources for your database. Each instance type includes several instance sizes, which means that you can scale your database to your target workload’s requirements.

Not every instance type is supported for every database engine, version, edition or Region.

When you want to scale your DB instance, you can vertically scale the instance and choose a larger instance size. This might be the route you choose to take when you need more CPU and storage ability for an instance.

Use read replicas

If you need more CPU capabilities but don’t need more storage, you might choose to create read replicas to offload some of the workload to a secondary instance.

Enable RDS Storage Auto Scaling

If you need more storage, but don’t need more CPU, then you could scale the storage horizontally. You can scale storage horizontally by allocating more storage volumes for your instance manually, or by enabling RDS Storage Auto Scaling. RDS Storage Auto Scaling automatically scales storage capacity in response to growing database workloads, with virtually zero downtime.

Previously, you needed to manually provision storage capacity based on anticipated application demands. Underprovisioning could result in application downtime, and overprovisioning could result in underutilized resources and higher costs. With RDS Storage Auto Scaling, you set your desired maximum storage limit and Auto Scaling takes care of the rest.

RDS Storage Auto Scaling continuously monitors actual storage consumption, and scales capacity up automatically when actual utilization approaches provisioned storage capacity.

Auto Scaling works with new and existing database instances. You can enable Auto Scaling with a few clicks in the AWS Management Console.

Change the storage type for increased performance

Finally, here’s one last thing to consider. If you’re looking for better performance, consider using a different storage type. For example, using Provisioned IOPS instead of General Purpose could give you some of the performance enhancements that you want.

The following list briefly describes the three storage types:

• General Purpose SSD: General Purpose SSD volumes offer cost-effective storage that works well for a broad range of workloads. These volumes deliver single-digit millisecond latencies and the ability to burst to 3,000 IOPS for extended periods of time. Baseline performance for these volumes is determined by the volume’s size.
• Provisioned IOPS: Provisioned IOPS storage is designed to meet the needs of I/O-intensive workloads — particularly database workloads — that require low I/O latency and consistent I/O throughput.
• Magnetic: Amazon RDS also supports magnetic storage for backward compatibility. We recommend that you use General Purpose SSD or Provisioned IOPS for any new storage needs. The maximum amount of storage that’s allowed for DB instances on magnetic storage is less than that of the other storage types.

For more resources about Amazon RDS, see the following:

• For more information about Amazon RDS Multi-AZ Deployments, see Amazon RDS Multi-AZ.
• For more information about Amazon RDS read replicas, see Amazon RDS Read Replicas.
• For more information about Amazon RDS DB instances, see Amazon RDS DB instances.

AWS DMS

Morgan suggested that this week’s customer use AWS Database Migration Service (Amazon DMS) to migrate data from their on-premises database to Amazon RDS.

AWS DMS helps you migrate databases to AWS quickly and securely. The source database remains fully operational during the migration, which minimizes the downtime to applications that rely on the database. The AWS DMS can migrate your data to and from widely used commercial and open-source databases.

At a basic level, AWS DMS is a server in the AWS Cloud that runs replication software. You create a source and target connection to tell AWS DMS where to extract from and load to. Then, you schedule a task that runs on this server to move your data. AWS DMS creates the tables and associated primary keys if they don’t exist on the target. You can precreate the target tables yourself, if you prefer. You can also use AWS Schema Conversion Tool (AWS SCT) to create some or all of the target tables, indexes, views, triggers, and so on.

The following diagram shows the AWS DMS replication process.

This week’s customer would use AWS DMS to perform a homogenous database migration.

In homogeneous database migrations, the source and target database engines are the same, or are compatible — such as Oracle to Amazon RDS for Oracle, MySQL to Amazon Aurora, MySQL to Amazon RDS for MySQL, or Microsoft SQL Server to Amazon RDS for SQL Server. Because the schema structure, data types, and database code are compatible between the source and target databases, this kind of migration is typically a one-step process. You create a migration task with connections to the source and target databases, and then start the migration. AWS DMS takes care of the rest. The source database can be located in your own premises outside of AWS, on an Amazon Elastic Compute Cloud (Amazon EC2) instance, or in an Amazon RDS DB instance. The target can be a database in Amazon EC2 or Amazon RDS.

For more general information about AWS DMS, see What is AWS Database Migration Service?