Chapter 8: AWS Database Services

Most applications need to store, access, organize, and manipulate data in some way. Normally, the data would reside externally to the actual application in what we call a database for several reasons, including efficiency improvements. Databases are designed to do more than simply store data, however. Depending on the type of database, data can be organized and stored in a structured or semi-structured manner, offer high-speed access to the data, and give you the ability to perform queries and scans against the data. Data can also be combined from different tables within the database to help you create complex analytics and reporting. Typical examples of where you would use a database include storing customer records and their orders for your e-commerce website, storing a product listing catalog, and storing temperature information from your home IoT thermostat devices. AWS offers three primary database solutions and several others for specific application...

Technical requirements

To complete the exercises in this chapter, you will need to access your AWS account and be logged in as our fictitious administrator, Alice, using her IAM user credentials.

Managed databases versus unmanaged databases

Traditionally, in an on-premises setup, you would configure a server (physical or virtual) with a base operating system and then proceed to install the database software on it. Because the primary purpose of a database is to store data, you would also need to ensure that you had adequate storage attached to your server. Due to the importance of the data store, you would take additional security measures to protect the data and ensure you had adequate backups and copies of the data (ideally stored offsite in another location) in case of disasters.

On AWS, you can set up an Elastic Compute Cloud (EC2) instance and install your database, such as Microsoft SQL Server or Oracle, in the same manner to serve your frontend web and application servers as required. In this case, you take full ownership of managing the database, provisioning the required amount of Elastic Block Store (EBS) volumes for storage, and ensuring adequate backups are made...

Introduction to database concepts and models

Today, there are several types of database models, but the most common are relational and non-relational models. Relational databases have existed for years and allow you to efficiently manage your data with the ability to perform complex queries and analyses. However, they have certain restrictions, such as the fact that you need to define the database schema (its structure) before you can add data, and changing this later can be difficult. Non-relational databases offer a lot more flexibility and are used for many modern-day web and mobile applications. Let's look at the key differences.

Relational databases

A relational database is often compared to a spreadsheet, although databases offer more capabilities than just letting you analyze data using complex calculations and formulas. Like a spreadsheet, a relational database can be composed of one or more tables. Within each table, you have rows and columns – columns define...

Introduction to Amazon RDS

Amazon RDS offers traditional relational databases as fully managed services on the AWS platform. Ideal for transactional database requirements, also known as OLTP, AWS offers six different database engines, as follows:

• MySQL
• PostgreSQL
• MariaDB
• Microsoft SQL server
• Oracle
• Amazon Aurora

Another term you might have heard of is Relational Database Management System (RDBMS). An RDBMS performs functions to create, read, update, and delete (CRUD) data from the database using an underlying software component, which we call the database engine.

An important point to understand here is that when you choose to set up an Amazon RDS database, you are setting up a database instance with a chosen engine to run on that instance. You can then create one or more databases supported by that engine on your database instance. This means you can have several databases running on an individual database instance.

Furthermore, on Amazon...

Learning about Amazon DynamoDB (NoSQL database solution)

Amazon offers a fully managed non-relational database solution called Amazon DynamoDB. Unlike AWS's relational database offerings (excluding Amazon Aurora, which also has a serverless offering, as discussed earlier), you do not need to worry about provisioning the right DB instance with the right specification for your application. DynamoDB is offered as a serverless solution because you do not need to define any database instance configuration, such as CPU or memory configuration. Amazon manages the underlying infrastructure that hosts the DynamoDB service.

DynamoDB is a regional service just like Amazon RDS, but it comes with higher levels of scalability and high availability. You do not need to provision a single DB instance in one Availability Zone as you do with a single instance of an Amazon RDS database. Instead, when you provision a DynamoDB table, Amazon provisions the database and automatically spreads the...

Understanding the use cases for Amazon Redshift and data warehousing

A data warehousing solution is a specialized database solution designed to pull data from other relational databases and enable complex querying and analytics to be performed across different datasets. For example, you can combine data across customer orders, inventory data, and financial information to analyze product trends, demands, and return on investments.

Clients of Amazon Redshift include business intelligence (BI) applications, reporting, and analytics toolsets.

Online Analytical Processing (OLAP)

Amazon Redshift is designed for analytics and is optimized for scanning many rows of data for one or multiple columns. Instead of organizing data as rows, Redshift transparently organizes data by columns; it converts the data into columnar storage for each of the columns. Let's look at what this means.

In a traditional database, data for each record is stored as rows. The columns represent the...

Understanding the importance of in-memory caching options with Amazon Elasticache

Often, you will find yourself accessing a set of data regularly, which is what we term as frequently accessed data. Every time you run a query on the database, you consume resources to perform the query operation and then retrieve that data. Overall, this can add additional load to your database and may even affect performance as you constantly write new data to the database.

As part of your overall application architecture, you should consider using in-memory caching engines offered by AWS to alleviate the load on your primary databases. Amazon Elasticache is a web service that offers in-memory caching in the cloud. By caching frequently accessed data on Amazon Elasticache, applications can be configured to retrieve frequently accessed data from it rather than make more expensive database calls.

AWS offers two in-memory caching engines, as follows:

• Amazon Elasticache for Redis: This is...

Learning about additional database services for specific niche requirements

In addition to Amazon RDS and DynamoDB, AWS also offers additional databases that meet the requirements of specific niche applications. In this section, we will take a look at two of those databases: Amazon Neptune and Amazon Quantum Ledger Database (QLDB).

Introduction to Amazon Neptune

Amazon Neptune is a fully managed graph database service and a type of NoSQL database. Graph databases are designed to store data as nodes (person, place, location, and so on) and directions. Each node would have some property and nodes have relationships between them. So, for example, Alice lives in London, and in London, there is a resident called Alice. This is a simple example, but you can start to imagine how complex your nodes and their relationships can become. These kinds of complex relationships between the nodes are just as important as the data itself and are ideal for a graph database solution. Traditional...

Database Migration Service

Amazon offers a Database Migration Service (DMS) that can be used to migrate data from one database to another. Often, this is used as part of an on-premises to cloud migration strategy, where you need to migrate database services located in your data center to your AWS account in the cloud. AWS DMS offers support for both homogeneous migrations, such as from MySQL to MySQL or Oracle to Oracle, as well as heterogeneous migrations between engines, such as Oracle to Microsoft SQL Server or Amazon Aurora.

An important point to be aware of is that, while migrating, you can continue to use your source database, which minimizes downtime for your business operations. In addition, you can also use DMS to perform continuous data replication from your on-premises environment to the cloud to offer high availability or disaster recovery capabilities.

Exercise 8.1 – Extending your VPC to host database subnets

In Chapter 7, AWS Compute Services, you expanded your VPC to include both private subnets and public subnets. Generally, you would only host services in a public subnet that would need direct exposure on the internet. Examples include the bastion host server we deployed earlier in Chapter 7, AWS Compute Services (which we will discuss in the next chapter).

Most applications are deployed across tiers – so, for example, you can have a web tier, an application tier, and a database tier. These different tiers are designed to separate different components of your application stack, allowing you to create a degree of isolation, as well as benefit from a layered security model. In Chapter 7, AWS Compute Services , as part of Exercise 7.1 – Expanding ProductionVPC so that it includes two public subnets and two private subnets, you also configured two private subnets across two Availability Zones to host your...

Exercise 8.2 – Creating a database subnet group

Before you can launch an RDS database in your VPC, you need to define a DB subnet group. A DB subnet group is a collection of two or more subnets within the VPC where you want to deploy your database instance. When creating your DB subnet group, at least two subnets must be selected in the VPC that are associated with two separate Availability Zones in a Region. Amazon RDS uses the subnet group's IP address CIDR block to assign your RDS database instance(s) with an IP address.

Amazon RDS can then deploy the database instance on one of your chosen subnets that is part of the group. In the case of a Multi-AZ deployment, the master copy will be deployed in one subnet in a particular Availability Zone, while the standby copy will be deployed in another subnet that is hosted within another Availability Zone.

Note that the subnets in a DB subnet group are either public or private, but they cannot be a mix of both public...

Exercise 8.3 – Launching your Amazon RDS database in ProductionVPC

In this exercise, you will launch an Amazon RDS MySQL database in the DB subnet group of ProductionVPC. Let's get started:

1. Ensure that you are logged into your AWS account as the IAM user Alice.
2. Navigate to the Amazon RDS dashboard.
3. From the left-hand menu, select Databases.
4. On the right-hand side of the pane, click the Create database button.
5. Next, you will be presented with the Create database wizard, where you will need to define various parameters of your VPC. Amazon offers the t2.micro database instance running the MySQL engine as part of the Free Tier offering, which comes with the following features for up to 12 months:
   • 750 hours of Amazon RDS in a Single-AZ db.t2.micro instance.
   • 20 GB of General Purpose storage (SSD).
   • 20 GB for automated backup storage and any user-initiated DB snapshots.
6. For Choose a database creation method, select the option next to Standard create.
...

Exercise 8.4 – Deploying an Amazon DynamoDB table

In this exercise, you will deploy a very simple DynamoDB table. Let's get started:

1. Ensure that you are logged into your AWS account as the IAM user known as Alice.
2. Next, navigate to the DynamoDB dashboard. You can search for DynamoDB from the top search box of AWS Management Console.
3. If this is the first time you have visited the DynamoDB console page, you will be presented with a splash screen.
4. Click the Create table button.
5. Provide a name for your table in the text box next to Table name; for example, Recipes.
6. In the Primary key field, enter RecipeName and ensure that the type is set to String.
7. Under Table settings, uncheck the box next to Use default settings.
8. In the Read/write capacity mode section, select the On-demand option.
9. Click the Create button at the bottom of the page. DynamoDB will create a new table for you in a few seconds, as per the following screenshot:

   Figure...

Summary

In this chapter, we learned about the various database services offered by Amazon, comprising both relational and non-relational databases services. You learned how AWS enables you to quickly deploy new RDS databases and offers full management of your database as a service, rather than you having to provision EC2 instances that you will install database software on.

Amazon RDS comes with six engines – MySQL, PostgreSQL, Microsoft SQL, Oracle, MariaDB, and Amazon Aurora. Amazon RDS is a regional service and must be deployed in your VPC. You have options to configure for high availability using services such as Multi-AZ and backup and restore strategies. You can also scale out read copies of your RDS database to offload read queries away from the primary master copy of your database.

Amazon Aurora comes with a lot more features and addresses some of the limitations of traditional RDS engines out of the box, including features such as self-healing and high availability...

Questions

1. A company plans to migrate its on-premises MySQL database to Amazon RDS. Which AWS service should they use for this task?
   1. Amazon Snowball
   2. AWS Database Migration Service (AWS DMS)
   3. AWS VM Import/Export
   4. AWS Server Migration Service
2. Which of the following is the primary benefit of using an Amazon RDS database instead of installing a MySQL-compatible database on your EC2 instance?
   1. Managing the database, including patching and backups, is taken care of by Amazon.
   2. Managing the database, including patching and backups, is taken care of by the customer.
   3. You have full access to the operating system layer that the RDS database runs on.
   4. You can choose which drive and partition to install the RDS database on.
3. AWS RDS supports six database engines. From the following list, choose three engines supported by Amazon RDS.
   1. Microsoft SQL
   2. Oracle
   3. MySQL
   4. FoxPro
   5. Db2
4. You are building an application for a wealth asset management company that will be used to store portfolio data and transactions...