As a cloud platform AWS gives you very minimal number of steps to setup a DB in RDS. Creating a MYSqlDB 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 MariaDB instance.

Step-1

Select the MSSql 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

The final step is to click create database, after which the MySql DB is created with a available end point as shown below.

Using CLI

To create a MySql 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 mydbinstance
    --db-instance-class db.m1.small
    --engine MySQL
    --allocated-storage 20
    --master-username masterawsuser
    --master-user-password masteruserpassword
    --backup-retention-period 3

Using API

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

https://rds.us-west-2.amazonaws.com/
    ?Action=CreateDBInstance
    &AllocatedStorage=20
    &BackupRetentionPeriod=3
    &DBInstanceClass=db.m3.medium
    &DBInstanceIdentifier=mydbinstance
    &DBName=mydatabase
    &DBSecurityGroups.member.1=mysecuritygroup
    &DBSubnetGroup=mydbsubnetgroup
    &Engine=mysql
    &MasterUserPassword=masteruserpassword
    &MasterUsername=masterawsuser
    &Version=2014-10-31
    &X-Amz-Algorithm=AWS4-HMAC-SHA256
    &X-Amz-Credential=AKIADQKE4SARGYLE/20140213/us-west-2/rds/aws4_request
    &X-Amz-Date=20140213T162136Z
    &X-Amz-SignedHeaders=content-type;host;user-agent;x-amz-content-sha256;x-amz-date
    &X-Amz-Signature=8052a76dfb18469393c5f0182cdab0ebc224a9c7c5c949155376c1c250fc7ec3

To protect data from being viewed by unintended parties, we can use connection encryption between the client application and the RDS DB instance. Encryption is available in all AWS regions and for all the DB types supported by AWS RDS. In this chapter we will see how encryption is enabled for MSSQL Server.

There are two ways to enable encryption.

• Force SSL for all connections — this happens transparently to the client, and the client doesn”t have to do any work to use SSL.

• Encrypt specific connections — this sets up an SSL connection from a specific client computer, and you must do work on the client to encrypt connections.

Force SSL

In this approach we force all the connections form the DB client to use SSL. This is done by using the rds.force_ssl parameter. Set the rds.force_ssl parameter to true to force connections to use SSL. As it is a static parameter, we must reboot your DB instance for the change to take effect. The below diagram shows how to reset the value by visiting the DB parameters settings page to set the value for rds.force_ssl parameter.

Encrypting Specific Connections

We can encrypt connections from specific client computers only to the RDS DB Instance. In order to do this, we need to install certificate on the client computer. Below are the steps to install the certificate.

Step-1

Download the certificate to the client computer from .

Step-2

Follow the path Windows -> Run -> type MMC and enter. It opens the following window.

Step-3

In the Add or Remove Snap-ins dialog box, for Available snap-ins, select Certificates, and then choose Add.

Step-4

Follow the Path Computer Account -> Local Computer -> Finish.

Step-5

In the MMC console, expand Certificates, open the context (right-click) menu for Trusted Root Certification Authorities, choose All Tasks, and then choose Import.

Step-6

Select the .pem file downloaded in the previous step and finish the import wizard by choosing the default values and clicking next.

Step-7

We can see the certificate installed as below.

Step-8

When connecting to AWS RDS MSSQL Db instance using SSMS, expand the options tab and choose Encrypt connection.

Now the client connection to RDS from this computer will be encrypted.

To connect to Amazon RDS MySQL DB we need a client software. In this case we use MySQL Workbench. 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.

Step-4

We can browse the DB and query the DB now.

MariaDB is a popular open Source Relational DB which is available in the amazon RDS services with its community edition features. Almost every feature of MariaDB can be leveraged in the RDS platform. Below is a brief description on MariaDB”s major features in the RDS platform.

Supported Versions

The versions 10.0, 10.1,10.2 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 all 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='''',
    DefaultOnly=True,
    Engine=''mariadb'',
    EngineVersion='''',
    ListSupportedCharacterSets=False, #True,
)

print(response)

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

{
   "ResponseMetadata": {
      "RetryAttempts": 0,
      "HTTPStatusCode": 200,
      "RequestId": "16179fbd-9d07-425b-9b86-cc61359ce7b4",
      "HTTPHeaders": {
         "x-amzn-requestid": "16179fbd-9d07-425b-9b86-cc61359ce7b4",
         "date": "Fri, 14 Sep 2018 06:45:52 GMT",
         "content-length": "1658",
         "content-type": "text/xml"
      }
   },
   "u''DBEngineVersions''": [
      {
         "u''Engine''": "mariadb",
         "u''DBParameterGroupFamily''": "mariadb10.2",
         "u''SupportsLogExportsToCloudwatchLogs''": true,
         "u''SupportsReadReplica''": true,
         "u''DBEngineDescription''": "MariaDb Community Edition",
         "u''EngineVersion''": "10.2.12",
         "u''DBEngineVersionDescription''": "mariadb 10.2.12",
         "u''ExportableLogTypes''": [
            "audit",
            "error",
            "general",
            "slowquery"
         ],
         "u''ValidUpgradeTarget''": [
            {
               "u''Engine''": "mariadb",
               "u''IsMajorVersionUpgrade''": false,
               "u''AutoUpgrade''": false,
               "u''Description''": "MariaDB 10.2.15",
               "u''EngineVersion''": "10.2.15"
            }
         ]
      }
   ]
}

Database Security

The security for RDS MariaDB is managed at three layers.

Using IAM

In this approach the IAM user should have appropriate policies and permissions. Granting of such permissions is decided by the account holder or the super user who grants these permissions.

Using VPC

You either use a VPC security group or DB security group to decide which EC2 instances can open connections to the endpoint and port of a DB instance. These connections can also be made using SSL.

Using IAM Database Authentication

In this approach you use a IAM role and an authentication token. The authentication token generates a unique value which is relevant to the IAM role that is used in the access process. Here the same set of credentials are used for database as well as other aws resources, like EC2 and S3 etc.

Cache Warming

Cache warming can provide performance gains for your MariaDB DB instance by saving the current state of the buffer pool when the DB instance is shut down, and then reloading the buffer pool from the saved information when the DB instance starts up. This approach bypasses the need for the buffer pool to “warm up” from normal database use and instead preloads the buffer pool with the pages for known common queries.

Cache warming primarily provides a performance benefit for DB instances that use standard storage.

You can create an event to dump the buffer pool automatically and at a regular interval. For example, the following statement creates an event named periodic_buffer_pool_dump that dumps the buffer pool every hour.

CREATE EVENT periodic_buffer_pool_dump
   ON SCHEDULE EVERY 1 HOUR
   DO CALL mysql.rds_innodb_buffer_pool_dump_now();

MySQL is a popular Relational DB which is available in the amazon RDS services with its community edition features. Almost every feature of MYSQL can be leveraged in the RDS platform with only some restrictions based on regions and availability zones. Below is a brief description on MYSQLs major features in the RDS platform.Supported Versions

The versions 5.5, 5.6 and 5.7 are the major versions supported in the RDS platform. Except for 5.6.27 all versions are supported in all AWS regions. 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 all 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=''mysql5.6'',
    DefaultOnly=True,
    Engine=''mysql'',
    EngineVersion=''5.6'',
    ListSupportedCharacterSets=True,
)

print(response)

When the above code is run we get an output as below −

{
   "ResponseMetadata": {},
   "DBEngineVersions''": [
      {
         "Engine''": "mysql",
         "DBParameterGroupFamily''": "mysql5.6",
         "SupportsLogExportsToCloudwatchLogs''": true,
         "SupportedCharacterSets''": [],
         "SupportsReadReplica''": true,
         "DBEngineDescription''": "MySQL Community Edition",
         "EngineVersion''": "5.6.40",
         "DBEngineVersionDescription''": "MySQL 5.6.40",
         "ExportableLogTypes''": [
            "audit",
            "error",
            "general",
            "slowquery"
         ],
         "ValidUpgradeTarget''": [
            {
               "Engine''": "mysql",
               "IsMajorVersionUpgrade''": true,
               "AutoUpgrade''": false,
               "Description''": "MySQL 5.7.22",
               "EngineVersion''": "5.7.22"
            }
         ]
      }
   ]
}

Version Upgrade

There MySQL version number is maintained as MYSQL A.B.C. In this notation, A.B indicates the major version and C indicates the minor version. The upgrade approach is different between minor and major version upgrades.

Minor Version Upgrade

The DB instances are automatically upgraded to new minor versions when ever they are supported by Amazon RDS. This patching occurs during a schedules maintenance window which you can control. You can also manually upgrade to new versions if you prefer to turn off the automatic update.

Major Version upgrade

Major version upgrades are not available as automatic upgrade. It must be done by the account user manually by modifying the DB instance. Below flowchart indicated the steps in achieving the major version upgrade. This approach ensures that the upgrade process is thoroughly tested before it is applied on the live production database.

Database Security

The security for RDS MYSQL DB is managed at three layers.

Using IAM

In this approach the IAM user should have appropriate policies and permissions. Granting of such permissions is decided by the account holder or the super user who grants these permissions.

Using VPC

You either use a VPC security group or DB security group to decide which EC2 instances can open connections to the endpoint and port of a DB instance. These connections can also be made using SSL.

Using IAM Database Authentication

In this approach you use a IAM role and an authentication token. The authentication token generates a unique value which is relevant to the IAM role that is used in the access process. Here the same set of credentials are used for database as well as other aws resources, like EC2 and S3 etc.

A DB instance is an isolated database environment running in the cloud which can contain multiple user-created databases. It can be accessed using the same client tools and applications used to access a standalone database instance. But there is restriction on how many DB instances of what type you can have for a single customer account. The below diagram illustrates the different combinations based on the type of license you opt for.

Each DB instance is identified by a customer supplied name called DB instance identifier. It is unique for the customer for a given AWS region.

DB Instance Classes

Depending on the need of the processing power and memory requirement, there is a variety of instance classes offered by AWS for the RDS service.

When there is a need of more processing power than memory requirement you can choose the standard instance class with a higher number of virtual CPUs. But in the case of very high memory requirement you can choose Memory optimized class with appropriate number of VCPUs. Choosing a correct class not only impacts the speed of the processing but also the cost of using service. The burstable performance class is needed when you have a minimal processing requirement and the data size in not in peta bytes.

DB Instance Status

The DB Instance status indicates the health of the DB. It’s value can be seen from the AWS console or using AWS CLI command describe-db-instances. The important status values of DB instances and their meaning is described below.

To connect to Amazon RDS MSSQL server we need a client software. In this case we use Sql Server Management Studio. 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.

Step-4

We can also query the MSSQL DB as shown below.

As a cloud platform AWS gives you very minimal number of steps to setup a DB in RDS. Creating a MSSQL DB 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 MSm SQL DB instance.

Step-1

Select the MySQL db 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 important options.

Using CLI

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

aws rds create-db-instance
    --engine sqlserver-se
    --db-instance-identifier mymsftsqlserver
    --allocated-storage 250
    --db-instance-class db.m1.large
    --db-security-groups mydbsecuritygroup
    --db-subnet-group mydbsubnetgroup
    --master-user-name masterawsuser
    --master-user-password masteruserpassword
    --backup-retention-period 3

Using API

To create a MSSQL DB 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=250
    &BackupRetentionPeriod=3
    &DBInstanceClass=db.m1.large
    &DBInstanceIdentifier=mydbinstance
    &DBSecurityGroups.member.1=mysecuritygroup
    &DBSubnetGroup=mydbsubnetgroup
    &Engine=sqlserver-se
    &MasterUserPassword=masteruserpassword
    &MasterUsername=masterawsuser
    &SignatureMethod=HmacSHA256
    &SignatureVersion=4
    &Version=2014-10-31
    &X-Amz-Algorithm=AWS4-HMAC-SHA256
    &X-Amz-Credential=AKIADQK/20140305/us-west-1/rds/aws4_request
    &X-Amz-Date=20140305T185838Z
    &X-Amz-SignedHeaders=content-type;host;user-agent;x-amz-content-sha256;x-amz-date
    &X-Amz-Signature=d445654615b1522c5b2b37c13bsseb7575hdh45e209d4b3a064a3

The RDS instances use Amazon Block Storage (EBS) volumes for storing data and log. These storage types can dynamically increase their size as and when needed. But based on the database workloads and price associated with these storage types we can customize the storage need. Following are the factors to be analysed while deciding on the storage types.

• IOPS – It represents the number of Input Output operations performed per second. Both read and write operations are summed up for finding the IOPS value. AWS creates a report of IOPS value for every 1 minute. It can have value from 0 to tens of thousands per second.

• Latency – It is the number of milliseconds elapsed between the initiation of an I/O request and the completion of the I/O request. A bigger latency indicates a slower performance.

• Throughput – The number of bytes transferred to and from the disk every second. AWS reports the read and write throughput separately for every 1-minute interval.

• Queue Depth – It is the number of I/O requests waiting in the queue before they can reach the disk. AWS reports the queue depth for every 1-minute interval. Also a higher queue-depth indicates a slower storage performance.

Based on the above considerations, the aws storage types are as below.

General Purpose SSD

This is a cost-effective storage that is useful in most of the common database tasks. It can provide 3000 IOPS for a 1- TiB volume. In a 3.34 TiB size the performance can go up to 10000 IOPS.

I/O Credits

Each GB of storage allows 3 IOPs as a base line performance. Which mean a 100 GB volume can provide 300 IOPs. But there may be scenario when you need more IOPS. In such scenario you need to use some IO credit balance which is offered when the storage is initialized. It is 5.4 million IO credits which can be used when a burstable performance need arises. On the other hand when you use less IOPS than the baseline performance, you accumulate the credits which can be used in future requirement of burstable performances.

Below is a equation which shows the relation between burst duration and Credit balance.

Burst Duration = (credit Balance) / [(burst IOPS) – 3(Storage size in GB)]

If your DB needs frequent and long duration burstable performance, then the next storage type will be a better choice.

Provisioned IOPS Storage

This is a type of storage system that gives sustained higher performance and consistently low latency which is most suitable for OLTP workloads.

When creating the DB instance, you specify the required IOPS rate and volume size for such storage. Below is a chart which is used for reference for deciding about the IOPS and storage needed under provisioned storage.

This is a very old storage technology which is maintained by aws, only for backward compatibility. Its features are very limited which are the following.

• Does not support Elastic Volumes

• Limited to maximum size of 4 TB

• Limited to maximum of 1000 IOPS

The RDS interfaces are a way to access the RDS service we create. After the creation and configuration of the RDS service there is a need of accessing the data, uploading data to this database and running some other program which should be able to connect to the database. Such requirements of accessing and manipulating data by end users of the database and not necessarily the AWS account holder which created the database needs these interfaces.

There are three main such interfaces.

GUI Console

This is the simplest of the interfaces where the user can login through a web browser and start using the DB services. The down side of such access is , it needs a human to interact with the RDS services and we cannot run a database program to do some regular tasks like – backup or analysing the DB etc.

Command Line Interface

It is also called CLI access where you can execute DB command through the AWS command prompt screen which should have been installed in the client computer you are using. Below are the steps to install CLI in your local system using which you will access AWS services.

The steps to install AWS CLI are as below.

Step-1

Check for the version of python in your environment.

ubuntu@ubuntu:~$ python -V
ubuntu@ubuntu:~$ python3 -V

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

Python 2.7.12
Python 3.5.2

If the version is less than 2.6 or 3.3 , then you need to upgrade the version of python in your system.

Step -2

Check for availability of the python package named pip . It will be needed to install AWS CLI.

Pip -V

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

pip 10.0.1 from /home/ubuntu/.local/lib/python3.5/site-packages/pip (python 3.5)

Step -3

Issue the following command to install the AWS CLI.

pip install awscli –upgrade –user
aws --version

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

Aws-cli/1.11.84 Python/3.6.2 Linux/4.4.0

Step-4

Next we configure the aws CLI with credentials. We issue this command and then input the required values one by one.

aws configure

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

AWS Access Key ID [None]: ****PLE
AWS Secret Access Key [None]: ********8
Default region name [None]: us-west-2
Default output format [None]: json

With the above configuration in place you are now ready to use CLI for communicating with AWS environments for setting up and using amazon RDS. In the next chapters we will see how we can do that.

AWS API

Amazon Relational Database Service (Amazon RDS) also provides an application programming interface (API). APIs are used when the information is exchanged between the systems rather than a human issuing the commands and receiving the result. For example, if you want to automate the addition of database instances to a RDS service when the number of transactions reach certain threshold , then you use a AWS SDK to write a program which will monitor the number of database transactions and spin-off a RDS instance when the required condition is met.

Below is an example of API code that creates a copy of a DB snapshot. It is a python program which uses AWS sdk named boto3. The client library in boto3 has a method named copy_db_snapshot which is called by the python program to create a copy of the DB snapshot with the required parameters as shown.

import boto3

client = boto3.client(''rds'')

response = client.copy_db_snapshot(
    SourceDBSnapshotIdentifier=''mydbsnapshot'',
    TargetDBSnapshotIdentifier=''mydbsnapshot-copy'',
)

print(response)

When the above program is run we get the response which describes the various properties of the copy event. Here the term string represents the various names of parameters which is defined by the user for their environment. For example VpcID represents the ID of the vpc in which the copy action is happening.

{
    ''DBSnapshot'': {
        ''DBSnapshotIdentifier'': ''string'',
        ''DBInstanceIdentifier'': ''string'',
        ''SnapshotCreateTime'': datetime(2015, 1, 1),
        ''Engine'': ''string'',
        ''AllocatedStorage'': 123,
        ''Status'': ''string'',
        ''Port'': 123,
        ''AvailabilityZone'': ''string'',
        ''VpcId'': ''string'',
        ''InstanceCreateTime'': datetime(2015, 1, 1),
        ''MasterUsername'': ''string'',
        ''EngineVersion'': ''string'',
        ''LicenseModel'': ''string'',
        ''SnapshotType'': ''string'',
        ''Iops'': 123,
        ''OptionGroupName'': ''string'',
        ''PercentProgress'': 123,
        ''SourceRegion'': ''string'',
        ''SourceDBSnapshotIdentifier'': ''string'',
        ''StorageType'': ''string'',
        ''TdeCredentialArn'': ''string'',
        ''Encrypted'': True|False,
        ''KmsKeyId'': ''string'',
        ''DBSnapshotArn'': ''string'',
        ''Timezone'': ''string'',
        ''IAMDatabaseAuthenticationEnabled'': True|False,
        ''ProcessorFeatures'': [
            {
                ''Name'': ''string'',
                ''Value'': ''string''
            },
        ]
    }
}