How to create highly efficient relational databases?

Before jumping into AWS Relational Database, or AWS RDS in short, it is essential to understand the significance of the words ‘Relational Database’.

Relational Database, as the name suggests, contains rows and columns of data, which is represented as a table in which the data elements have a predefined relationship with each other. Every element in the table is a data object which is stored in the database. Every row in the data table can be represented uniquely by assigning a key to it, which is known as the primary key. Relationships between multiple rows of different data tables can be established with the help of foreign keys. This is how a basic relational database is designed.

Now let us look at how Amazon makes it different by coming up with Amazon RDS.

Amazon RDS is a web service which helps make the process of setting up a relational database an easy ride. In addition to this, the relational database, which is set up, needs to be managed. Amazon RDS helps in efficiently managing and scaling up or down a database which is present in the AWS Cloud. Scaling up or down refers to the database’s ability to be resized based on the requirement. Most of the time, it would be required to be scaled up.

Now your question could be, why Amazon’s RDS only?

This is because Amazon provides a cost-effective method to manage the RDB, in addition to providing facilities to manage basic database administration operations.

The basic structure of Amazon RDB involves creation of an RDS instance. This instance provides a network address which is commonly known as an ‘endpoint’. This endpoint can be used by our applications in accessing the database instance. When a DB instance is created, specifications regarding memory, database engine, version, network configuration, security, and its maintenance periods are mentioned. A security group behaves like an interface to the user and DB instance to access it through a network.

If you already have an AWS account, follow the below steps. Otherwise, create an AWS account and follow the instructions mentioned. From there, follow the below steps.

Creating a DB instance

Let us see how to create a DB Instance:

1. In the top right corner of the AWS RDS Console, choose the Amazon region where you wish to create the database instance.
2. In the navigation bar, click on the ‘Databases’.
3. Click on ‘Create Database’ and choose the ‘Easy Create’ way.
   
4. Choose MySQL as the database engine in the configuration.
   
5. Choose ‘Free tier’ for the DB instance size.
   
6. Give a name to the DB instance in the ‘DB instance identifier’. You could also leave it as is, since it will have a default name.
7. Provide a ‘Master username’ in the textbox or leave it as is to continue with the default name.
   
8. If you wish to not type a password, click on the checkbox 'Auto Generate a password’, else create one and confirm the newly created password.
9. View the default settings and click on ‘Create Database’.

It takes about 20 to 22 minutes for the newly created database instance to be available for use.

To confirm that your instance is being created, check for the details of your newly created instance on the RDS console.

Connecting the newly created DB instance to MySQL 

1. The endpoint URL and the port number of your newly created DB instance can be found in your RDS console.
2. Click on ‘Databases’ and choose your newly created instance. This will display all the details of the instance.
3. The endpoint and the port number can be found in the ‘Connectivity and security’ tab.
4. Download an SQL client which will be required to connect to the DB instance.
5. In your command prompt, enter the below command. (Enter your endpoint url and master username)

   mysql –h <endpoint> -P 3306 –u <masterusername> -p

6. It will prompt the user to enter a password for that master username. After the password entry is successful, the below screen can be seen on your console.

This will connect to a database of MySQL database instance.

Deleting a database instance

1. Sign in to your AWS account and open up the RDS console.
2. In the navigation bar, select ‘Databases’.
3. Click on the database instance that you wish to delete.
4. For the ‘Actions’ options, click on Delete.
5. For the ‘Create final snapshot?’ option, click on ‘No’ and select the acknowledgment.
6. Click on Delete.

This will delete your database instance.

Salient features of Amazon RDS

• RDS provides a way to split the server resources (such as CPU, memory, storage) according to our requirements and resize them all separately. It is an easy task to allocate space to them individually.
• In addition to storage, RDS provides backups, and software patches. It also detects failures automatically and helps in recovering the data from the database.
• The backups can be automated or manually created according to the requirement. Such backups are essential to restore a database. RDS helps in efficiently creating a backup and its recovery.
• The security feature in RDS makes sure that specific system procedures and tables which require higher level privileges are restricted from certain users.
• RDS provides a highly available primary instance, in addition to facilitating the users with a secondary instance that can be used when the primary instance is unavailable or it has failed due to certain complications. 
• It supports Amazon Aurora, MySQL, MariaDB, Microsoft SQL, Oracle, and PostgreSQL database engines.
• Additional security can be built up on databases by placing them in a private virtual cloud.  
• A new layer of security can be added to the RDS by using Amazon’s other services such as AWS Identity and Access Management (IAM) services, which help in defining specific permissions to specific users.
• The burden of maintaining the database which is on the administrator is reduced by a great extent.

Amazon RDS works with AWS Management Console, Command Line Interface (CLI), and programmatically.

Conclusion

In this post, we saw how Amazon’s relational database service can be used to create highly efficient relational databases.