In a Multi-AZ deployment, Amazon RDS automatically provisions and maintains a synchronous standby replica in a different Availability Zone. The primary DB instance is synchronously replicated across Availability Zones to a standby replica to provide data redundancy, eliminate I/O freezes, and minimize latency spikes during system backups. Running a DB instance with high availability can enhance availability during planned system maintenance and help protect your databases against DB instance failure and Availability Zone disruption.

Creating Multi-AZ deployment

You can choose the option of selecting a multi-AZ deployment when creating a DB instance or you can also choose the option of modifying an existing db instance to become a multi-AZ DB instance. The following diagram shows the option where we are modifying an existing option. But the same option is also available during the creation of the DB instance.

How Multi-AZ works

In the event of a planned or unplanned outage of your DB instance, Amazon RDS automatically switches to a standby replica in another Availability Zone if you have enabled Multi-AZ. The time it takes for the failover to complete depends on the database activity and other conditions at the time the primary DB instance became unavailable. Failover times are typically 60-120 seconds. However, large transactions or a lengthy recovery process can increase failover time.

Multi-AZ deployments for Oracle, PostgreSQL, MySQL, and MariaDB DB instances use Amazon”s failover technology. SQL Server DB instances use SQL Server Mirroring.

Throughout the life cycle of amazon RDS DB instances, many DB events occur which are important to be known beforehand. For example – A backup of the DB instance has started, or an error has occurred while restarting MySQL or MariaDB.

Notification Categories

Based on the nature of the event, notifications can be classified into following categories.

Creating Event Notifications

Below are the steps to create event subscriptions through which the notifications are sent to the subscriber.

Step-1

Choose the Event subscription tab from the RDS dashboard.

Step-2

We give a name to the event and choose the subscription source.

Now choosing the source.

Step-3

In the next step we see the details of the source type of a subscription type chosen for the event.

To access the Amazon RDS DB instance the user needs specific permissions. This is configured using AWS IAM (Identity and Access management). In this tutorial we will see how this configuration is done.

The configuration involves two parts.

• Authentication

• Access Control

Authentication

It involves creating the username, password and generating the access keys for the user. With help of access key, it is possible to make programmatic access to the AWS RDS service. The SDK and CLI tools use the access keys to cryptographically sign in with the request.

We can aslo use an IAM Role to authenticate a user. But the role is not attached to any specific user, rather any user can assume the role temporarily and complete the required task. After the task is over the role can be revoked and the user loses the authentication ability.

Access Control

After a user is authenticated, a policy attached to that user determines the type of tasks the uer can carry on. Below is an example of policy which allows the creation of a RDS DB instance, on a t2.micro instance for the DB Engine MySQL.

{
    "Version": "2018-09-11",
    "Statement": [
        {
            "Sid": "AllowCreateDBInstanceOnly",
            "Effect": "Allow",
            "Action": [
                "rds:CreateDBInstance"
            ],
            "Resource": [
                "arn:aws:rds:*:123456789012:db:test*",
                "arn:aws:rds:*:123456789012:og:default*",
                "arn:aws:rds:*:123456789012:pg:default*",
                "arn:aws:rds:*:123456789012:subgrp:default"
            ],
            "Condition": {
                "StringEquals": {
                    "rds:DatabaseEngine": "mysql",
                    "rds:DatabaseClass": "db.t2.micro"
                }
            }
        }
    ]
}

Action on Any RDS Resource

In the below example we see a policy that allows any describe action on any RDS resource. The * symbol is used to represent any resource.

{
   "Version":"2012-10-17",
   "Statement":[
      {
         "Sid":"AllowRDSDescribe",
         "Effect":"Allow",
         "Action":"rds:Describe*",
         "Resource":"*"
      }
   ]
}

Disallow deleting a DB Instance

The below policy disallows a user from deleting a specific DB instance.

{
   "Version":"2012-10-17",
   "Statement":[
      {
         "Sid":"DenyDelete1",
         "Effect":"Deny",
         "Action":"rds:DeleteDBInstance",
         "Resource":"arn:aws:rds:us-west-2:123456789012:db:my-mysql-instance"
      }
   ]
}

In order to maintain the reliability, availability, and performance of Amazon RDS, we need to collect monitoring data so that we can easily debug a multi-point failure. With Amazon RDS, you can monitor network throughput, I/O for read, write, and/or metadata operations, client connections, and burst credit balances for your DB instances. We should also consider storing historical monitoring data. This stored data will give you a baseline to compare against with current performance data.

Below are examples of some monitoring data and how they help in maintaining healthy RDS instances.

• High CPU or RAM consumption – High values for CPU or RAM consumption might be appropriate, provided that they are in keeping with your goals for your application (like throughput or concurrency) and are expected.

• Disk space consumption – Investigate disk space consumption if space used is consistently at or above 85 percent of the total disk space. See if it is possible to delete data from the instance or archive data to a different system to free up space.

• Network traffic – For network traffic, talk with your system administrator to understand what expected throughput is for your domain network and Internet connection. Investigate network traffic if throughput is consistently lower than expected.

• Database connections – Consider constraining database connections if you see high numbers of user connections in conjunction with decreases in instance performance and response time.

• IOPS metrics – The expected values for IOPS metrics depend on disk specification and server configuration, so use your baseline to know what is typical. Investigate if values are consistently different than your baseline. For best IOPS performance, make sure your typical working set will fit into memory to minimize read and write operations.

Monitoring with Amazon CloudWatch

Amazon RDS sends metrics and dimensions to Amazon CloudWatch every minute. We can monitor these metrices from the AWS console as shown in the below diagrams.

Amazon RDS creates automated backups of your DB instance during the backup window of your DB instance and stores them as volume snapshots.

Automated Backup

For Automated backup to work properly, followings are the criteria.

• Your DB instance must be in the ACTIVE state for automated backups to occur. Automated backups don”t occur while your DB instance is in a state other than ACTIVE, for example STORAGE_FULL.

• Automated backups and automated snapshots don”t occur while a copy is executing in the same region for the same DB instance.

The first snapshot of a DB instance contains the data for the full DB instance. Subsequent snapshots of the same DB instance are incremental, which means that only the data that has changed after your most recent snapshot is saved.

The following diagram shows how we can configure the automated backup window for a DB instance using the AWS console. To disable the automated backup, set the number of days to zero.

Manual Backup

We can also take backups manually by using snapshots. To take a snapshot manually we use the instance action option after selecting the instance as shown below.

We can aslo take manual backup using the CLI with the below command.

aws rds create-db-snapshot /
    --db-instance-identifier sourcedbinstance /
    --db-snapshot-identifier dbsnapshotinstance

To connect to Amazon RDS Oracle DB we need a client software. In this case we use Sql Developer. Install it using the link .

After it is successfully installed we follow the steps below to connect it to the Amazon RDS.

Step-1

From the DB instance details get the end point.

Step-2

Use the end point and the master user credentials as the connection details.

Step-3

Once connected, we get the following window.

To import data into the RDS oracle database or export data from it, we need to consider the size of the data involved and use a appropriate technique. The Sql Developer tool which we use to connect to AWS RDS oracle instance, can be used for both export and import small volume of data like 20MB or so. But to import data of size in terabytes, we need to use oracle data pump.

Exporting Data Using SQL Developer

Step-1

After connecting to the AWS RDS Oracle DB, choose tools and Database Export.

Step-2

The next step asks for the type of export where we decide the export format.

Step-3

Next we decide on the DB objects to be exported.

Step-4

We can further decide on the name of the objects to be exported.

Step-5

We can further decide on the objects attributes to be exported.

Step-6

Finally we get the summary screen where we can revisit the objects we have choosen.

Clicking finish on the above step will export the DB into a file in choosen format.

Importing Data Using SQL Developer

Similar to the export steps above we can choose to import the db by using Database Copy command from the Tools menu option.

Oracle is very popular Relational DB which is available in the amazon RDS services with its enterprise edition features. Almost every feature of Oracle can be leveraged in the RDS platform. Below is a brief description on MYSQLs major features in RDS platform.

Supported Versions

The versions 11.2 and 12.1 are the major versions supported in the RDS platform. If no version is mentioned during the DB creation, it defaults to the most recent version at that point in time. Below is an example of how to get the supported DB Engine versions using AWS API in a python SDK program.

import boto3
client = boto3.client(''rds'')
response = client.describe_db_engine_versions(
    DBParameterGroupFamily=''oracle-ee-12.1'',
    DefaultOnly=True,
    Engine='''',
    EngineVersion='''',
    ListSupportedCharacterSets=False, #True,
)
print(response)

When we run the above program, we get the following output −

{
   "ResponseMetadata": {
      "RetryAttempts": 0,
      "HTTPStatusCode": 200,
      "RequestId": "f6805635-3e16-4014-83cd-dfdaf3f17950",
      "HTTPHeaders": {
         "x-amzn-requestid": "f6805635-3e16-4014-83cd-dfdaf3f17950",
         "date": "Fri, 14 Sep 2018 03:46:38 GMT",
         "content-length": "1455",
         "content-type": "text/xml"
      }
   },
   "u''DBEngineVersions''": [
      {
         "u''Engine''": "oracle-ee",
         "u''DBParameterGroupFamily''": "oracle-ee-12.1",
         "u''SupportsLogExportsToCloudwatchLogs''": true,
         "u''SupportsReadReplica''": false,
         "u''DefaultCharacterSet''": {
            "u''CharacterSetName''": "AL32UTF8",
            "u''CharacterSetDescription''": "Unicode 5.0 UTF-8 Universal character set"
         },
         "u''DBEngineDescription''": "Oracle Database Enterprise Edition",
         "u''EngineVersion''": "12.1.0.2.v12",
         "u''DBEngineVersionDescription''": "Oracle 12.1.0.2.v12",
         "u''ExportableLogTypes''": [
            "alert",
            "audit",
            "listener",
            "trace"
         ],
         "u''ValidUpgradeTarget''": []
      }
   ]
}

Oracle Licensing

There are two options for using oracle licenses in RDS. They are License Included and Bring Your Own License.

License Included Model

In this model Amazon holds the license for the software you are going to use. Also AWS itself provides the support for both AWS and Oracle software thorugh its support program. So the user does not purchase any separate license. The platform pricing includes the charges for licensing cost the user pays. The two editions supported in this model are Standard Edition One and Standard Edition Two.

Bring Your Own License (BYOL)

In this model the user brings in the license she holds into RDS platform. It is the user’s responsibility to maintain the compatibility between the license, database instance class and database edition. The user directly contacts the Oracle support channel for any need. In this model the supported editions are Enterprise Edition (EE), Standard Edition (SE), Standard Edition One (SE1) and Standard Edition Two (SE2).

For a multi A-Z deployment, the user should have license for both primary DB instance and the secondary DB instance.

Oracle DB Parameter Group

The oracle DB involves many DB parameters to be configured for various features and performance needs of the database. Aws makes these parameters visible through CLI commands, which the user can use to query for the parameter values. Below is the CLI command and the sample output.

aws rds describe-engine-default-parameters --db-parameter-group-family oracle-ee-12.1

Below are the some important parameters obtained as a result of above CLI command.

{
    "EngineDefaults": {
        "Parameters": [
            {
                "AllowedValues": "TRUE,FALSE",
                "ParameterName": "_allow_level_without_connect_by",
                "ApplyType": "dynamic",
                "Description": "_allow_level_without_connect_by",
                "IsModifiable": true,
                "Source": "engine-default",
                "DataType": "boolean"
            },
            {
                "AllowedValues": "CHOOSE,OFF,CUBE,NESTED_LOOPS,MERGE,HASH",
                "ParameterName": "_always_semi_join",
                "ApplyType": "dynamic",
                "Description": "_always_semi_join",
                "IsModifiable": true,
                "Source": "engine-default",
                "DataType": "string"
            },
            {
                "AllowedValues": "TRUE,FALSE",
                "ParameterName": "_b_tree_bitmap_plans",
                "ApplyType": "dynamic",
                "Description": "_b_tree_bitmap_plans",
                "IsModifiable": true,
                "Source": "engine-default",
                "DataType": "boolean"
            },
    {
                "AllowedValues": "TRUE,FALSE",
                "ParameterName": "parallel_automatic_tuning",
                "ApplyType": "static",
                "Description": "enable intelligent defaults for parallel execution parameters",
                "IsModifiable": true,
                "Source": "engine-default",
                "DataType": "boolean"
            },
            {
                "AllowedValues": "ENABLE,DISABLE",
                "ParameterName": "xml_db_events",
                "ApplyType": "dynamic",
                "Description": "are XML DB events enabled",
                "IsModifiable": false,
                "Source": "engine-default",
                "DataType": "string"
            }
        ]
    }
}

To connect to Amazon RDS MariaDB we need a client software. In this case we use Navicat. Install it using the link .

After it is successfully installed we follow the steps below to connect it to the Amazon RDS.

Step-1

From the DB instance details get the end point.

Step-2

From connecitons choose Amazon AWS -> Amazon RDS for MariaDB.

Step-3

Use the end point and the master user credentials as the connection details.

Step-4

Once connected, we get the following window.

Step-5

Next you can connect to specific db and view the details.

As a cloud platform AWS gives you very minimal number of steps to setup a DB in RDS. Creating a PostgreSQL can be done in three ways. Using AWS management console, AWS CLI or AWS API. We will look at each of these approaches one by one.

Using AWS management Console

AWS management console is the most convenient way to get started with RDS. You login to the AWS console using your AWS account details, locate the RDS service and then follow the steps shown below to create a PostgreSQL instance.

Step-1

Select the PostgreSQL Engine form the console.

Step-2

Specify the required DB details.

Step-3

In this step you decide on the db instance class, amount of storage allocated also set the master password along with few other details.

Stpe—4

This is the final step when you mention the vpc and security settings, encryption, backup options and log export etc. For brevity the screen shot has been shortened showing only the final options.

Stpe—5

In the final step we choose the create Data base option.

Using CLI

To create a PostgreSQL DB instance by using the AWS CLI, call the create-db-instance command with the parameters below.

aws rds create-db-instance
    --db-instance-identifier pgdbinstance
    --allocated-storage 20
    --db-instance-class db.t2.small
    --engine postgres
    --master-username masterawsuser
    --master-user-password masteruserpassword

Using API

To create a PostgreSQL instance by using the Amazon RDS API, we call the CreateDBInstance action with the parameters as shown below.

https://rds.amazonaws.com/
    ?Action=CreateDBInstance
    &AllocatedStorage=20
    &BackupRetentionPeriod=3
    &DBInstanceClass=db.t2.small
    &DBInstanceIdentifier=pgdbinstance
    &DBName=mydatabase
    &DBSecurityGroups.member.1=mysecuritygroup
    &DBSubnetGroup=mydbsubnetgroup
    &Engine=postgres
    &MasterUserPassword=
    &MasterUsername=
    &SignatureMethod=HmacSHA256
    &SignatureVersion=4
    &Version=2013-09-09
    &X-Amz-Algorithm=AWS4-HMAC-SHA256
    &X-Amz-Credential=AKIADQKE4SARGYLE/20140212/us-west-2/rds/aws4_request
    &X-Amz-Date=20140212T190137Z
    &X-Amz-SignedHeaders=content-type;host;user-agent;x-amz-content-sha256;x-amz-date
    &X-Amz-Signature=60d520ca0576c191b9eac8dbfe5617ebb6a6a9f3994d96437a102c0c2c80f88d