Enable Multi-AZ deployments for your Amazon Redshift data warehouse

Amazon Redshift is a fully managed, petabyte scale cloud data warehouse that enables you to analyze large datasets using standard SQL. Data warehouse workloads are increasingly being used with business-critical analytics applications that require the highest levels of availability and resiliency. Amazon Redshift is a cloud-based data warehouse that already supports many recovery capabilities to address unforeseen outages and minimize downtime. Amazon Redshift RA3 instance types store their data in Redshift Managed Storage (RMS), which is backed by Amazon Simple Storage Service (Amazon S3), which is highly available and durable by default. Amazon Redshift also supports automatic backups that can be used to recover a data warehouse, automatic remediation of failures, and the ability to relocate a cluster to another Availability Zone without changes to applications. Although many customers benefit from these features, enterprise data warehouse customers require a low RTO and higher availability to support their business continuity with minimal impact to applications.

Amazon Redshift now supports Multi-AZ deployments (Preview) for provisioned RA3 clusters. Multi-AZ deployments support running your data warehouse in multiple Availability Zones simultaneously and can continue operating in unforeseen failure scenarios. A Multi-AZ deployment is intended for customers with business-critical analytics applications that require the highest levels of availability and resiliency.

A Redshift Multi-AZ deployment leverages compute resources in multiple AZs to scale data warehouse workload processing.  In situations where there is a high level of concurrency Redshift will automatically leverage the resources in both AZs to scale the workload for both read and write requests using active-active processing.

In this post, we show how to configure an Amazon Redshift Multi-AZ deployment in multiple Availability Zones.

Overview of solution

We provide a walkthrough of how to perform a Multi-AZ deployment for an Amazon Redshift cluster using the AWS Management Console. We also provide a walkthrough on how to test fault tolerance of an Amazon Redshift Multi-AZ data warehouse and monitor queries in your Multi-AZ deployment.

Single-AZ vs. Multi-AZ deployment

Amazon Redshift requires a cluster subnet group to create a cluster in your VPC. The cluster subnet group includes information about the VPC ID and a list of subnets in your VPC. When you launch a cluster, Amazon Redshift either creates a default cluster subnet group automatically or you choose a cluster subnet group of your choice so that Amazon Redshift can provision your cluster in one of the subnets in the VPC. You can configure your cluster subnet group to add subnets from different Availability Zones that you want Amazon Redshift to use for cluster deployment.

All Amazon Redshift clusters today are created and situated in a particular Availability Zone within an AWS Region and thus called Single-AZ deployments. For a Single-AZ deployment, Amazon Redshift selects the subnet from one of the Availability Zones within a Region and deploys the cluster there. You can choose an Availability Zone for deployment, and Amazon Redshift will deploy your cluster in the chosen Availability Zone based on the subnets provided.

On the other hand, a multi-AZ deployment is provisioned in multiple Availability Zones simultaneously. For a Multi-AZ deployment, Amazon Redshift automatically selects two subnets from two different Availability Zones and deploys an equal number of compute nodes in each Availability Zone. All these compute nodes are utilized via a single endpoint as compute nodes from both Availability Zones are used for workload processing. 

As shown in the following diagrams, Amazon Redshift deploys a cluster in a single Availability Zone for Single-AZ deployment, and multiple Availability Zones for Multi-AZ deployment.

Auto recovery of multi-AZ deployment

In the unlikely event of an Availability Zone failure, Amazon Redshift Multi-AZ deployments continue to serve your workloads by automatically using resources in the other Availability Zone. You are not required to make any application changes to maintain business continuity during unforeseen outages since a multi-AZ deployment is accessed as a single data warehouse with one endpoint. Amazon Redshift Multi-AZ deployments are designed to ensure there is no data loss, and you can query all data committed up until the point of failure.

As shown in the below diagram, if there is an unlikely event that causes compute nodes in AZ1 to fail, then a multi-AZ deployment automatically recovers to use compute resources in AZ2. Amazon Redshift will also automatically provision identical compute nodes in another availability zone (AZ3) to continue operating simultaneously in two Availability zones (AZ2 and AZ3).

Amazon Redshift Multi-AZ deployment is not only used for protection against the possibility of Availability Zone failures, but it can also maximize your data warehouse performance by automatically distributing workload processing across multiple Availability Zones. A Multi-AZ deployment will always process an individual query using compute resources only from one Availability Zone, but it can automatically distribute processing of multiple simultaneous queries to both Availability Zones to increase overall performance for high concurrency workloads.

It’s a good practice to set up automatic retries in your extract, transform, and load (ETL) processes and dashboards so that they can be reissued and served by the cluster in the secondary Availability Zone when an unlikely failure happens in the primary Availability Zone. If a connection is dropped, it can then be retried or reestablished immediately. In addition, queries and loads that were running in the failed Availability Zone will be aborted. New queries issued at or after a failure occurs may experience run delays while the multi-AZ data warehouse is being are recovered to a two AZ setup.

Create a new Multi-AZ deployment from the console

You can easily create a new multi-AZ deployments through Amazon Redshift console. Amazon Redshift will deploy the same number of nodes in each of the two Availability Zones for a Multi-AZ deployment. All nodes of a multi-AZ deployment can perform read and write workload processing during normal operation. A Multi-AZ deployment will support only provisioned RA3 clusters.

Follow these steps to create an Amazon Redshift provisioned cluster in multiple Availability Zones:

1. On the Amazon Redshift console, in the navigation pane, choose Clusters.
2. A banner displays on the Clusters list page that introduces preview mode. Choose the button Create preview cluster to open the create cluster page.
3. For Preview track, choose preview_2022.
4. We recommend entering a name for the cluster that indicates that it is on a preview track. Choose options for your cluster, including options labeled as -preview, for the features you want to test.

For general information about creating clusters, see Creating a cluster.

5. Choose one of the RA3 node types on the Node type drop-down menu. The Multi-AZ deployment option only becomes available when you choose an RA3 node type.
6. For Multi-AZ deployment, select Yes.
7. For Number of nodes per AZ, enter the number of nodes that you need for your cluster.

8. Under the Database configurations, choose Admin user name and Admin user password.
9. Turn Use defaults on next to Additional configurations to modify the default settings.
10. Under Network and security, specify the following:
    1. For Virtual private cloud (VPC), choose the VPC you want to deploy the cluster in.
    2. For VPC security groups, either leave as default or add the security groups of your choice.
    3. For Cluster subnet group, either leave as default or add a cluster subnet group of your choice. For a Multi-AZ deployment, a cluster subnet group must include one subnet each from at least three or more different Availability Zones.

For general information about managing cluster subnet groups, see Cluster subnet groups

11. Under Database configuration, for Database port, you either use the default value 5439 or choose a value from the range of 5431–5455 and 8191–8215.
12. Under Database configuration, in the Database encryption section, to use a custom AWS Key Management Service (AWS KMS) key other than the default KMS key, choose Customize encryption settings. This option is deselected by default.
13. Under Choose an AWS KMS key, you can either choose an existing KMS key, or choose Create an AWS KMS key to create a new KMS key.

For more information to create key using KMS, refer to Creating keys.

14. Choose Create cluster.

When the cluster creation succeeds, you can view the details on the cluster details page.

Under General information, you can see Multi-AZ as Yes.

On the Properties tab, under Network and security settings, you can find the details on the primary and secondary Availability Zone.

Convert a Single-AZ deployment to Multi-AZ deployment

To convert an existing Single-AZ deployment to a Multi-AZ deployment, you can restore from a snapshot to configure it into a Multi-AZ data warehouse. When migrating to a Multi-AZ deployment from an existing Single-AZ deployment, maintaining performance of a single query may require the same number of nodes used in the current Single-AZ deployment to be provisioned in both Availability Zones, resulting in doubling the amount of cluster nodes needed when migrating to Multi-AZ to ensure that single query performance is maintained.

Complete the following steps to create a Multi-AZ deployment restored from a snapshot:

1. On the Amazon Redshift console, in the navigation pane under Clusters, choose Snapshots.
2. Select the snapshot to use.
3. The snapshot needs to be encrypted in order to restore to a Multi-AZ deployment.
4. On the Restore snapshot menu, choose Restore to provisioned cluster.

5. Choose the Preview mode.
6. For Preview track, choose preview_2022
7. We recommend entering a name for the cluster that indicates that it is on a preview track. Choose options for your cluster, including options labeled as -preview, for the features you want to test.

For general information about creating clusters, see Creating a cluster.

8. Make sure that you choose one of the RA3 node types on the Node type drop-down menu. The Multi-AZ deployment option only becomes available when you chose an RA3 node type.
9. For Multi-AZ deployment, select Yes.
10. For Number of nodes per AZ, enter the number of nodes that you need for your cluster.

11. Scroll down to Additional configurations, expand Network and security, make sure that you either accept the default for Cluster subnet group or choose another one of your choice. For a Multi-AZ deployment, a cluster subnet group must include one subnet each from at least three or more different Availability Zones.
12. Under Additional configurations, expand Database configurations.
13. Under Database encryption, to use a custom KMS key other than the default KMS key, choose Customize encryption settings. This option is deselected by default.
14. Under Choose an AWS KMS key, you can either choose a KMS key or enter an ARN. Or, you can choose Create an AWS Key Management Service key to create a key.

15. Choose Restore cluster from snapshot.

When the cluster restoration succeeds, you can view the details on the cluster details page.

Test fault tolerance of your multi-AZ data warehouse

You can test the fault tolerance of your Amazon Redshift Multi-AZ deployment by injecting a failure that causes compute nodes in one Availability Zone to become unavailable. Amazon Redshift detects this event and triggers an automatic recovery. When the cluster successfully recovers, Multi-AZ deployment becomes available. Your Multi-AZ deployment also automatically provisions new compute nodes in another Availability Zone as soon as it is available.

Let’s test the fault tolerance of the Amazon Redshift Multi-AZ deployment.

1. On the Amazon Redshift console, choose Clusters in the navigation pane.
2. Navigate to the cluster detail page
3. On the Actions menu, choose Inject Failure (Public Preview).

4. When prompted, choose Confirm.

After the cluster is back to Available status, you can observe that the primary and secondary Availability Zones have changed.

The following screenshot shows the status before injecting failure.

The following screenshot shows the status after injecting failure.

Monitor queries for Multi-AZ deployments

A Multi-AZ deployment uses compute resources that are deployed in both Availability Zones and can continue operating in the event that the resources in a given Availability Zone are not available. All the compute resources are used at all times, which allows full operation across two Availability Zones in both read and write operations.

You can query SYS_ views in the pg_catalog schema to monitor Multi-AZ query runs. The SYS_ views cover query run activities and stats from primary and secondary clusters.

The following are the system tables in the SYS_ view list:

• SYS_QUERY_HISTORY
• SYS_QUERY_DETAIL
• SYS_CONNECTION_LOG
• SYS_DATASHARE_CHANGE_LOG
• SYS_DATASHARE_USAGE_CONSUMER
• SYS_DATASHARE_USAGE_PRODUCER
• SYS_EXTERNAL_QUERY_DETAIL
• SYS_LOAD_HISTORY
• SYS_LOAD_ERROR_DETAIL
• SYS_UNLOAD_HISTORY

Follow these steps to monitor the query run on Multi-AZ deployment from the Amazon Redshift Console:

1. On the Amazon Redshift console, connect to the database in your Multi-AZ deployment and run queries through the query editor.
2. Run any sample query on the Multi-AZ Redshift deployment.
3. For a Multi-AZ deployment, you can identify a query and the Availability Zone where it is being run (running on the primary cluster or secondary availability zone) by using the compute_type column in the SYS_QUERY_HISTORY table. The valid values for the compute type column are as follows:
   1. primary – When run on primary availability zone in the Multi-AZ deployment.
   2. secondary – When run on secondary availability zone in the Multi-AZ deployment.

The following is a sample query using the compute_type column to monitor a query:

dev=# select (compute_type) as compute_type, left(query_text, 50) query_text from sys_query_history order by start_time desc;

 compute_type | query_text
--------------+----------------------------------------------------
 secondary    | select count(*) from t1;

You can also access the query history from the console to analyze your query diagnostics.

4. On the Query monitoring tab, choose Connect to database.

5. For Authentication, choose Temporary credentials
6. For Database name, enter the database name (for example, dev).
7. For Database user, enter the database user name (for example, awsuser).
8. Choose Connect.

After you’re connected, under Query Monitoring, on the Query history tab, you can view all the queries and loads, as shown in the following screenshot.

Under Metric filters, you can use the various filters in the Additional filtering options section to view query history based on Time interval, Users, Databases, or SQL commands.

There are a few limitations when working with Amazon Redshift Multi-AZ in preview mode, refer here for the limitations.

Customer feedback

Janssen Pharmaceuticals, a subsidiary of Johnson & Johnson, researches and manufactures medicines with a focus on the changing needs of patients and the healthcare industry. “Janssen Pharmaceutical uses Amazon Redshift to enable critical insights that drive important business decisions for our data scientists, data stewards, business users, and external stakeholders. With Amazon Redshift Multi-AZ, we can be confident that our data warehouse will always be available without any disruptions that might delay impact our ability to make critical business decisions.” – Shyam Mohapatra, Director of Information Technology – Janssen Pharmaceutical Companies of Johnson & Johnson

Conclusion

This post demonstrated how to configure an Amazon Redshift Multi-AZ deployment in multiple Availability Zones and test the fault tolerance of your workloads during an unlikely failure of an Availability Zone. Amazon Redshift Multi-AZ deployment also helps improve overall performance of your data warehouse because compute nodes in both Availability Zones are used for read and write operations. Amazon Redshift Multi-AZ data warehouse helps meets the demands of customers with business critical analytics applications that require the highest levels of availability and resiliency.

For more details, refer Configuring Multi-AZ deployment.

About the Authors

Ranjan Burman is an Analytics Specialist Solutions Architect at AWS. He specializes in Amazon Redshift and helps customers build scalable analytical solutions. He has more than 16 years of experience in different database and data warehousing technologies. He is passionate about automating and solving customer problems with cloud solutions.

Jeff Sosa leads the Redshift product management team responsible for the core redshift compute and storage platform, availability, backup/recovery and disaster recovery areas. Jeff has been at AWS for over 3 years and has focused on high-scale distributed systems processing and storage throughout his 20 year career in product management.

Saurav Das is part of the Amazon Redshift Product Management team. He has more than 16 years of experience in working with relational databases technologies and data protection. He has a deep interest in solving customer challenges centered around high availability and disaster recovery.

Anusha Challa is a Senior Analytics Specialist Solutions Architect focused on Amazon Redshift. She has helped many customers build large-scale data warehouse solutions in the cloud and on premises. She is passionate about data analytics and data science.

Nita Shah is an Analytics Specialist Solutions Architect at AWS based out of New York. She has been building data warehouse solutions for over 20 years and specializes in Amazon Redshift. She is focused on helping customers design and build enterprise-scale well-architected analytics and decision support platforms.

Suresh Patnam is a Principal BDM – GTM AI/ML Leader at AWS. He works with customers to build IT strategy, making digital transformation through the cloud more accessible by using data and AI/ML. In his spare time, Suresh enjoys playing tennis and spending time with his family.