Best practices to deploy Amazon Aurora databases with AWS CloudFormation

Many organizations prefer infrastructure as code (IaC) for provisioning and maintaining IT infrastructure. With IaC, you can replicate DevOps practices for application code such as storing the infrastructure code in a source control system, automated testing, and automated deployment through a continuous integration and continuous delivery (CI/CD) pipeline.

AWS CloudFormation is an IaC service that lets you provision and manage AWS or third-party resources as code. With AWS CloudFormation, you can provision resources consistently across environments (development, QA, production), accounts, and Regions.

The AWS CloudFormation documentation provides details of the resources and their properties that are used to build the Amazon Aurora database cluster and database instances however some properties need additional consideration to avoid challenges post deployment.

In this post, we cover the best practices for writing CloudFormation templates to provision an Amazon Aurora PostgreSQL-Compatible Edition or Amazon Aurora MySQL-Compatible Edition database. It discusses resource properties that you should consider while creating the CloudFormation template. These properties are for access control, securing data at rest, database monitoring features, and properties that provides flexibility to modify the database instances’ size or configuration of the database without restart. The best practices covered in this post can be used with other IaC tools such as Terraform and AWS Cloud Development Kit (AWS CDK).

Throughout this post, we give code examples for each property. We end this post with a sample template that you can use to deploy an Aurora database cluster.

Before we start, let’s talk briefly about AWS CloudFormation and Aurora.

AWS CloudFormation for infrastructure as code

As mentioned earlier, AWS CloudFormation is an IaC service that allows you to provision and maintain IT infrastructure in a programmatic, descriptive, and declarative way.

To use AWS CloudFormation, you first create templates in JSON or YAML that describe the AWS resources and their properties. AWS CloudFormation then provisions the resources described in the template. Your template can contain one or multiple resources, and you can use input parameters, dependencies, and conditions to change the deployment as needed. For example, you can parameterize instance types to have flexibility to choose a bigger or smaller instance based on the environment, or use conditions to decide how many replica instances to deploy.

Aurora databases

Aurora is a fully managed, highly optimized database from AWS that is available in MySQL and PostgreSQL compatible engines. Aurora features separation of the compute and storage layers, which makes it highly resilient and performant at scale.

An Aurora database cluster consists of one or more database instances. The primary database instance supports read and write operations There is only one primary database instance in a cluster, all other instances are replicas that only support read operations. You can have up to 15 replicas in a cluster. All database instances in the cluster read from a shared cluster storage volume. For additional information, refer to Amazon Aurora DB clusters.

CloudFormation templates for Aurora

Now that we know about CloudFormation and Aurora, let’s dive into the AWS CloudFormation resource properties.

In a CloudFormation template for an Aurora database, you can have many resource types; however, two are mandatory:

• AWS::RDS::DBCluster – Where you define properties for the Aurora database cluster, for example the engine version
• AWS::RDS::DBInstance – Where you define properties of the host on which the database runs, for example the instance size

Let’s look at some important properties of these two resources.

AWS::RDS::DBCluster

The AWS::RDS::DBCluster resource creates an Aurora database cluster. You need only one definition of this resource type to create the Aurora cluster. The following are some key properties to consider when defining your cluster.

MasterUsername

This is the primary user of the database cluster. Instead of specifying the user name explicitly in this property, it’s better to store it in AWS Secrets Manager and refer to the Secrets Manager secret.

Secrets Manager enables you to manage and protect your secrets, such as database passwords, and eliminates the need to hardcode sensitive information in plain text.

You need to create a Secrets Manager secret using the AWS::SecretsManager::Secret resource in the template. The following code example creates the Secrets Manager secret:

  MasterUser:
    Type: 'AWS::SecretsManager::Secret'
    Properties:
      Name: '/rds/EnvType/DatabaseName-MasterUser-secret'
      Description: 'Aurora database master user'
      SecretString: !Ref DBUsername

You can then refer to the Secrets Manager secret in this property:

MasterUsername: !Join ['', ['{{resolve:secretsmanager:', !Ref MasterUser, '}}']]

MasterUserPassword

This property defines the password of the primary user of the database cluster. Unless there are strict requirements to use a specific password, Secrets Manager can generate and store a random password for you. The following code example generates the Secrets Manager password for the secret:

  MasterPassword:
    Type: 'AWS::SecretsManager::Secret'
    Properties:
      Name: '/rds/EnvType/DatabaseName-MasterPassword-secret'
      Description: 'Aurora database master password'
      GenerateSecretString:
        PasswordLength: 16
        ExcludePunctuation: true

You can then refer to the Secrets Manager secret in this property:

MasterUserPassword: !Join ['', ['{{resolve:secretsmanager:', !Ref MasterPassword, '}}']]

Additionally, you should consider the following:

• Create a new admin user rather than using the primary user. Only use the primary user in emergencies. Refer to How do I create another master user for my Amazon RDS DB instance that is running MySQL for steps on how to create an admin user in MySQL. Similar steps can be used for PostgreSQL.
• Avoid using the primary user in your applications. Always create a new user with minimal required privileges for application use.
• Use Secrets Manager to rotate the passwords on a periodic basis. It has built-in integration for Aurora. Refer to the post Rotate Amazon RDS database credentials automatically with AWS Secrets Manager for steps on how to configure it.
• Instead of hardcoding your user name and password in the application, store them in Secrets Manager and configure applications to read from Secrets Manager directly. For details, see Move hardcoded database credentials to AWS Secrets Manager.
• For additional security, use AWS Identity and Access Management (IAM) database authentication for individual users. We provide details on this later in the post.

StorageEncrypted

This property enables data encryption at rest, so we advise setting this property as true. Storage encryption is disabled by default. The following example enables encryption:

StorageEncrypted: true

KmsKeyId

This property encrypts the database instances in the database cluster. If you don’t specify it, Aurora uses the default AWS managed aws/rds AWS Key Management Service (AWS KMS) key for storage encryption. You should create a new symmetric customer managed key for encryption within the template and specify it here. A CMK gives you more flexibility compared to the default key. For example, if you plan to create an Aurora global database in the future, or transfer a database snapshot to another Region or account, you will encounter challenges by using the default key because it’s AWS-managed and non-shareable.

The following code example creates a KMS key:

	  DatabaseKMSKey:
    		Type: AWS::KMS::Key
    		Properties:
      			Description: 'Key used by Aurora database cluster'
      			KeyPolicy:
        			Version: 2012-10-17
        			Id: database-key-default
        			Statement:
          				- Sid: Enable IAM User Permissions
            				  Effect: Allow
            			Principal:
              			AWS: !Join 
                			- ''
                			- - 'arn:aws:iam::'
                  			- !Ref 'AWS::AccountId'
                  			- ':root'
            			Action: 'kms:*'
            			Resource: '*'

You then reference the key in the resource AWS::RDS::DBCluster as follows:

KmsKeyId: !Ref DatabaseKMSKey

DBClusterParameterGroupName

You manage the database parameters by associating your database instances and Aurora database cluster with parameter groups. In this property, we provide the name of the database cluster parameter group to be associated with the database cluster.

You should create a new parameter group even if you don’t intend to change any default values during deployment. If you don’t use this property, Aurora associates the default parameter group to the cluster. Note that you can’t make any changes to the default parameter group. In the future, if you need to make any parameter changes, you need to create a new parameter group and associate it with the cluster. This requires a reboot of the primary database instance in the cluster to apply the change.

The following code example for Aurora MySQL defines a cluster parameter group:

  ClusterParameterGroup:
    Type: 'AWS::RDS::DBClusterParameterGroup'
    Properties:
      Description: Custom cluster parameter group
      Family: aurora-mysql8.0
      Parameters:
        require_secure_transport: 'ON'
        server_audit_logging: 1
        server_audit_events: CONNECT

You then reference the parameter group in resource AWS::RDS::DBCluster as follows:

DBClusterParameterGroupName: !Ref ClusterParameterGroup

DeletionProtection

This property indicates whether the database cluster has deletion protection enabled. Set this property as true to prevent accidental deletion of the instance. This property also prevents replacement of the instance if you modify properties in the CloudFormation template that require resource replacement. Deletion protection is disabled by default. The following code example enables deletion protection:

DeletionProtection: true

EnableIAMDatabaseAuthentication

This property enables IAM authentication along with standard database authentication. Use IAM authentication if multiple users access the database or you have multiple databases accessed by the same set of users, for example an operations team. With IAM authentication, you don’t need to manage different database passwords for user accounts. Instead, users leverage an authentication token, which simplifies user management. For more information, see IAM database authentication. The following code example enables IAM authentication:

EnableIAMDatabaseAuthentication: true

EnableCloudwatchLogsExports

With this property, you can export to Amazon CloudWatch Logs by providing a list of log types. If you don’t specify this property, the logs are only available to list, view, and download via the AWS Management Console, AWS Command Line Interface (AWS CLI), or AWS SDK. With CloudWatch Logs, you can perform real-time analysis of the log data and generate alarms, as we see later in this post.

You should use this property to publish database logs such as MySQL error logs or PostgreSQL logs. This is helpful to debug issues. The following code example enables CloudWatch Logs for MySQL error and audit logs:

EnableCloudwatchLogsExports: 
        - 'error'
        - 'audit'

AWS::RDS::DBInstance

The AWS::RDS::DBInstance resource creates either primary or replica database instances. You only need one of this resource if you only need the primary instance database in the Aurora cluster. For each replica instance, you need an additional AWS::RDS::DBInstance resource. In this section, we explore some of the key properties for this resource.

DBInstanceClass

Use this property to specify the compute and memory capacity of the database host, for example db.r6g.large. If you plan to create read replicas, create separate DBInstanceClass parameters for the writer instance and read replicas in the template. This is helpful to deploy different instance classes during deployment and in the future, it allows you to make instance class changes (with CloudFormation template updates) independent of each other. The following code example sets the instance class to db.r6g.large:

DBInstanceClass: ‘db.r6g.large’

DBInstanceIdentifier

This is the name of the database instance. If you don’t specify a name, AWS CloudFormation generates a unique physical ID and uses it as the name.

Be aware that if you use this property to specify a custom name for an instance, you can’t perform updates (with AWS CloudFormation) that require replacement of the instance. You can use this property as a protection mechanism. If you attempt to update the CloudFormation template and didn’t realize that one of the properties requires instance replacement, the update fails because you used a custom name for the instance.

DBParameterGroupName

For the same reasons as described for the DBClusterParameterGroupName property, you should create a new database parameter group. Associating a new database parameter group later requires a database restart, which can be troublesome for a production database.

Additionally, create separate parameter groups for primary (writer) and replica instances because you may want their parameter values to be different.

The following code example for Aurora MySQL defines a database instance parameter group:

  WriterDBParameterGroup:
    Type: ‘AWS::RDS::DBParameterGroup’
    Properties:
      Description: Custom db parameter group for writer
      Family: aurora-mysql8.0
      Parameters:
        sql_mode: STRICT_ALL_TABLES
        transaction_isolation: READ-COMMITTED

The parameter group is used in the resource AWS::RDS::DBInstance as follows:

DBParameterGroupName: !Ref WriterDBParameterGroup

EnablePerformanceInsights

Amazon RDS Performance Insights is a useful tool to get insights on database performance, and it has no additional cost if you keep data for only 7 days. Note that it has to be enabled on each instance (writer and replicas) separately, and it’s disabled by default. The following code example enables Performance Insights:

EnablePerformanceInsights: true

For more information, refer to Monitoring DB load with Performance Insights on Amazon Aurora.

PerformanceInsightsKMSKeyId

Performance Insights require a KMS key to encrypt the data it collects. If you don’t specify this property, Aurora uses the default aws/rds KMS key. Consider using a CMK. You can reuse the CMK previously provided for KmsKeyId or create a new CMK for Performance Insights. The following code example reuses the CMK we created previously:

PerformanceInsightsKMSKeyId: !Ref DatabaseKMSKey

DeleteAutomatedBackups

This property indicates whether to remove automated backups immediately after the database instance is deleted. At the time of writing, this property is not available in Aurora. This means if you delete the Aurora instance using CloudFormation, the automated backups are also deleted. However, manual snapshots are preserved so we advise taking a manual snapshot of the instance before deleting it.

MonitoringInterval

This property is used to enable Enhanced Monitoring, which collects vital operating system metrics and processes information that is useful while debugging issues.

The Enhanced Monitoring metrics are ingested into CloudWatch Logs, and you’re charged for CloudWatch Logs data transfer and storage. To save costs, you can keep it disabled and enable it only when you need to debug an issue. Note that you can enable, disable, or make granularity changes to Enhanced Monitoring without a database restart.

This property has to be enabled on each instance (writer and replicas) separately. It’s disabled by default. The following code example enables Enhanced Monitoring and sets the interval to 15 seconds:

MonitoringInterval: 15

MonitoringRoleArn

If you enable Enhanced Monitoring, you must specify an IAM role that Aurora can use to send metrics to CloudWatch. While defining the IAM policy for this role, avoid using an inline policy because it can’t be reused by another resource. Define an explicit IAM policy resource and then use it in the role. For more information, see Choosing between managed policies and inline policies.

You can define an IAM role as follows:

IAMRoleRDSMonitoring:
    Type: AWS::IAM::Role
    Condition: EnhancedMonitoringEnabled
    Properties:
      RoleName: !Sub '${DatabaseName}-monitoring-role'
      AssumeRolePolicyDocument:
        Version: 2012-10-17
        Statement:
          - Effect: Allow
            Principal:
              Service:
                - monitoring.rds.amazonaws.com
            Action:
              - sts:AssumeRole
      ManagedPolicyArns:
        - arn:aws:iam::aws:policy/service-role/AmazonRDSEnhancedMonitoringRole

You can then reference this role for your instance as follows:

MonitoringRoleArn: !If [EnhancedMonitoringEnabled, !GetAtt IAMRoleRDSMonitoring.Arn, !Ref 'AWS::NoValue']

Database monitoring

We strongly encourage setting up database monitoring along with the initial setup. Aurora provides multiple ways to monitor your databases.

By default, Aurora sends several metrics, both at the cluster and database level, to CloudWatch. You should set up CloudWatch alarms on the metrics that you deem important, like CPU, memory, storage, and others.

Additionally, you should use the AWS::RDS::EventSubscription resource to subscribe to RDS events. These events alert you in case your database is undergoing changes like shutdown or restart. The events are grouped into categories, and you can subscribe to categories that make best sense to you. You must define resources separately for the Aurora cluster and database instances. The following is a code example that sets Aurora cluster RDS events. Note that you should define a SNS topic ARN to get notifications:

  ClusterEventSubscription:
    Type: 'AWS::RDS::EventSubscription'
    Properties:
      EventCategories:
        - configuration change
        - creation
        - failure
        - deletion
        - failover
        - maintenance
        - notification
      SnsTopicArn: arn:aws:sns:us-east-1:123456789012:example-topic	
      SourceIds:
        - !Ref myAuroraDBCluster
      SourceType: db-cluster
      Enabled: true

Use a metric filter to search for keywords in database error logs and raise a CloudWatch alarm if they’re reported. For example, you can search for the keywords error, exception, or aborted in the MySQL error log. Occurrence of these keywords in the error log means that the database is reporting issues, which should be investigated. For more details, see Monitor errors in Amazon Aurora MySQL and Amazon RDS for MySQL using Amazon CloudWatch and send notifications using Amazon SNS.

Metrics for Aurora are grouped into cluster and instance level. When you create a CloudWatch alarm, the Dimensions property of the resource AWS::CloudWatch::Alarm defines whether the metric is cluster or database level. For cluster-level metrics, use DBClusterIdentifier as the Dimensions name. For instance-level metrics, use DBInstanceIdentifier as the Dimensions name, and the Dimension value can be either the writer or replica instance. See the example in the next point.

While defining CloudWatch alarms, use the TreatMissingData property to avoid missing data status of alarms. For example, if you’re not getting data for CPU or memory, it’s likely that the instance is down. If you declare TreatMissingData as breaching, you will receive an alert. Similarly, for the metric filter, you can declare TreatMissingData as notBreaching, which means if you’re not getting data, the database is healthy.

In the following example, we use TreatMissingData as breaching for a high CPU alarm. We also use the Dimension property to associate this CPUUtilization metric with the primary instance. Note that you should define a SNS topic ARN to get notifications.

  DatabaseCPUUtilizationTooHighAlarm:
    Type: 'AWS::CloudWatch::Alarm'
    Properties:
      AlarmDescription: 'CPU utilization over last 10 minutes higher than 95%'
      Namespace: 'AWS/RDS'
      MetricName: CPUUtilization
      Statistic: Average  
      Period: 60
      EvaluationPeriods: 10             
      ComparisonOperator: GreaterThanThreshold
      Threshold: 95
      TreatMissingData: breaching
      AlarmActions: 
       - arn:aws:sns:us-east-1:123456789012:example-topic
      OKActions:
       - arn:aws:sns:us-east-1:123456789012:example-topic
      Dimensions:
      - Name: DBInstanceIdentifier
        Value: !Ref myPrimaryInstance

Sample CloudFormation template

You can deploy an Aurora database cluster, or review how the parameters mentioned in this post come together, using this sample template in your AWS account. The template includes all the best practices described in this post.

The template is for an Aurora MySQL cluster; however, you could modify it to use with Aurora PostgreSQL. The resources that need modification are AWS::RDS::DBClusterParameterGroup and AWS::RDS::DBParameterGroup.

Conclusion

In this post, we described the best practices for creating a CloudFormation template to provision an Aurora database cluster. We discussed the important properties of AWS::RDS::DBCluster and AWS::RDS::DBInstance resource types along with their advantages. Because database monitoring is also important, we discussed how you can set up monitoring for your Aurora database cluster using the CloudFormation template. You can use this post as a reference to improve your existing CloudFormation templates or create new templates.

We love your feedback! Leave your comments in the comments section.

About the Author

Divaker Goel is a Database Consultant at AWS with over 18 years experience in databases. He helps customers in their journey to AWS cloud.