MattCASmith

My first Amazon Web Services (AWS) basics post covered the process of setting up a Virtual Private Cloud (VPC) and a Windows Server 2019 EC2 instance. This time we’re going to build on this simple setup by deploying a Amazon Aurora SQL database and ensuring we can access it from our server.

AWS basics series

1. Deploying a virtual network and server
2. Deploying and connecting to a SQL database

Databases in AWS

Databases in AWS generally come in three different flavours, which are all designed for different use cases, data volumes, and availability requirements. These are:

• Relational Database Service (RDS) - Relational databases are typical, structured, table-based databases. AWS gives you the option to run MySQL, Oracle SQL, and Microsoft SQL Server among other established names, but also offers its own database engine called Amazon Aurora, which is optimised with a few extra features designed for the cloud.

• DynamoDB - DynamoDB is a NoSQL database consisting of key-value pairs for less structured data. This can be a good option if the speed of queries is the most important factor.

• Redshift - Redshift is the AWS service for data warehouses. This is the best solution if you have petabytes of data to store, and is optimised for handling these large datasets.

To keep things simple, we’ll assume our data will fit nicely in an RDS database that needs to be accessed from the Windows Server 2019 EC2 instance we deployed in the last post. Deploying an RDS database will take three steps: creating the database, ensuring our server has the correct access permissions, and connecting to the database to use it. Let’s get started with the setup.

Deploying an AWS RDS database

Navigate to the RDS dashboard and look for the Create Database section, where there is also a Create Database button. Clicking this will take you to a form where you can choose the configuration of your new database, from the engine to which VPC it sits in. Click the Standard Create option to continue.

Now let’s select an engine for our SQL database. You may instinctively reach for the familiar names like MySQL and Microsoft SQL Server, but I’m going to use Amazon Aurora (selecting the edition with MySQL compatibility). This is Amazon’s own database engine, which is optimised for use in the AWS cloud and can support higher throughput, auto-scaling, and replication across availability zones.

We’ll choose Provisioned capacity, which means we manage the server instance the database sits on, and select the Dev/Test template to avoid any extra charges for the high availability and resilience aspects of the Production template. Next we can create a username and password for the master user - make a note of these details as we’ll need them to access the database later on.

Scroll down and you have the opportunity to choose the VPC (basically the network) your database should sit in. We’ll choose the server-deployment VPC we configured during the first blog post. This will make it easier to reach the database from the server and will do for our simple example, but it is not recommended to deploy production databases in VPCs with internet access - it would be best to tuck them away in their own VPC and configure rules for access from another.

Let’s also create a new Security Group to allow access to the database. We’ll call it database-access and configure it a bit later. Click Create Database and you’ll be taken back to the Databases Dashboard, where you’ll be able to see your new Aurora MySQL database is now being created.

Granting access to the EC2 server instance

But if we were to try to connect from our EC2 server to the database now, our connection would fail. Why? Because we haven’t configured the Security Group to allow the connection. We can rectify this by navigating to the VPC Dashboard and clicking on Security Groups. From there we can add a rule that allows access on port 3306 from the subnet our server sits in (10.0.0.0/24).

That’s it - we’ve deployed a SQL database and given our server access to it. The next step is to log in and manipulate some data, just to prove that the database and connection both work.

Connecting to the AWS RDS database

If we RDP to our server, we can now connect to our SQL database - but first, you’ll need to download a client. I used Oracle’s MySQL Shell, with which you can establish a connection with this command:

.\mysqlsh.exe -h <insert database endpoint address> -P 3306 -u <username> -p

After entering our password, we are connected to the database and can interact with its contents using standard SQL commands to add, remove, merge, and filter data. Looks like it’s good to go! If you need any tips on how to manipulate data with SQL, see my previous posts on basic SQL commands, SQL JOINs, and SQL TABLE commands, which should help you to get started.

Again, make sure you go back to the EC2 and RDS Dashboards when you’re finished and stop your EC2 instance and RDS database. This will help you to avoid any unexpected AWS charges.

So now we have a VPC and a Windows Server 2019 EC2 instance, and have deployed a simple RDS Aurora database that we can access from it. In my next and final AWS basics post, I’ll run through how to set up an S3 bucket to store some files in the cloud (and avoid sharing them with everyone).