Playing with the Amazon Relational Database Service

4. november 2009

In this post I will look at the new Relational Database Service (RDS) from Amazon Web Services (AWS).

This was supposed to be a short post, but ended up being huge! So good luck to anyone who wants to read it...

AWS offers a bunch of services ranging from simple data storage to full virtual servers. The common factor for all of them is that they are controlled using standard web services. This means that you can easily manage them programmatically if you want to. In this article I won’t focus on the management bit, but rather on how and why to use the service.

In all simplicity RDS is MySQL running on a virtual server somewhere in Amazons server farm. The service is a port of MySQL so if you can connect to a regular MySQL database you can connect to RDS as well. You also manage RDS the same way you would manage a regular MySQL server, you can connect to it using the standard tools and create databases and tables as you want.

In my opinion there are two scenarios where you would use RDS: Either you have an application using MySQL already running on a virtual server on Amazon EC2 or you have an application that needs to reach a MySQL database from anywhere on the internet. In the first case you can now host the database on a dedicated system instead of a custom virtual server. This allows you to easily scale the performance of your application by scaling up your database. In the second case you have a desktop application that needs to connect to a database while being connected to the internet. In that case you could host the database on your own server, but hosting it at AWS is guaranteed to offer better availability and much better bandwidth.

There are at least four obvious advantages to using RDS as compared to running your own server with MySQL:

  • High availability – AWS guarantees an uptime of at least 99.95%. Since it is hosted in Amazons server farm it will probably be much more available than most services.
  • Easy to upgrade – Using the management web services you can easily scale your RDS server using a single api call. This is much easier than even upgrading a virtual server.
  • Automatic backups – RDS will automatically backup your database and in addition you can set up your own backups if you need to.
  • Available anywhere – It is easy to configure the RDS firewall so you can make the database available in any way you need to.

But there are also some disadvantages to RDS:

  • You trust AWS with your data – Since AWS are hosting your database you also trust them with your data. In some scenarios this may be a problem.
  • You pay per hour – When using RDS you have to pay per hour your database is running. While the prices are quite reasonable they still accumulate over time. It will be cheaper to run your database on an existing server if you have one.
  • Limited bandwidth – Depending on your use this may not be a problem, but running your database on a remote server means that you connection will be limited to the capacity of your internet line.

Getting started with RDS

The first thing you need to get started with RDS is an account with AWS. It is free to sign up for AWS, but you must pay to use their services. Signing up is simple, especially if you have shopped at Amazon.com before. Sign up here: https://aws-portal.amazon.com/gp/aws/developer/registration/index.html

After you have signed up for AWS you need to create a digital certificate that identifies you. This is done in the Your Account -> Security Credentials page. To access AWS you need two keys – an Access Key ID and a Secret Access Key.

With your keys in place you are ready to connect to AWS and the RDS service. I’ll show you an example .NET application that manages your database instances later, but first we will use the management tools supplied by AWS. These tools are a bunch of Java command line applications, so you need the Java runtime installed to use them. The tools can be downloaded here: http://developer.amazonwebservices.com/connect/entry.jspa?externalID=2928&categoryID=294

Setting up the command line tools is relatively easy – just unzip the file and follow the instructions in README.txt. You should setup a text file containing your Access Key ID and Secret Access Key based on the template found in the zip file.

Now we are ready to create a server! First you should make sure everything works properly. Try to run the rds-describe-db-instances command. If the tools are properly set up it should return an empty list. The next command you should try is rds-create-db-instance. This is the command that creates your server. I used the following command:

rds-create-db-instance --db-instance-identifier MyTestServer123 --allocated-storage 5 
--db-instance-class db.m1.small --engine MySQL5.1 --master-username root --master-user-password Secret123

And I got the following response:

DBINSTANCE  mytestserver123  db.m1.small  mysql5.1  5  root  creating  1
      SECGROUP  default  active
      PARAMGRP  default.mysql5.1  in-sync

This gave me a small instance of a database server with 5 Gb of disk storage. The instance is named MyTestServer123 and is running MySQL 5.1.

Now if I run the rds-describe-db-instances command again I get the following result:

DBINSTANCE  mytestserver123  db.m1.small  mysql5.1  5  root  creating  1
      SECGROUP  default  active
      PARAMGRP  default.mysql5.1  in-sync

Note that it says creating. It can take a few minutes to setup a new server and you cannot access it before it is ready. Try running the rds-describe-db-instances command after a minute until you get something like this:

DBINSTANCE  mytestserver123  2009-11-04T11:50:21.554Z  db.m1.small  mysql5.1  5  root  
available  mytestserver123.cqpn2yka5vjx.us-east-1.rds.amazonaws.com  3306  us-east-1b  1       SECGROUP  default  active       PARAMGRP  default.mysql5.1  in-sync

Now the status should be available. You should also note the text mytestserver123.cqpn2yka5vjx.us-east-1.rds.amazonaws.com. This is the name of your server and you need it when you want to connect later.
Before you can connect to your server you need to open its firewall to let you through. This is done using the command rds-authorize-db-security-group-ingress. You must run it two times. First you must set the security group for your server and then you must allow your ip-address through the firewall to servers in that security group. Use the following commands:

rds-authorize-db-security-group-ingress MyTestServer123 --ec2-security-group-name default

This will place your server in the “default” security group. The default group always exists so it is ok to use here. Then you use the following command to give your ip address access to the server:

rds-authorize-db-security-group-ingress default --cidr-ip 123.124.125.126/24

Here my public ip address is 123.124.125.126. The /24 after the ip address means that all addresses starting with 123.124.125 should be allowed to connect. Read more about the cidr format here: http://en.wikipedia.org/wiki/CIDR.

Now, finally, your server is ready to be used! Try connecting using your favorite tool. I prefer MySQL Query Browser which is part of the MySQL GUI Tools. Download it here: http://dev.mysql.com/downloads/gui-tools/5.0.html.

Trying to connect

To connect you must specify the server name you noted earlier. Run rds-describe-db-instances if you forgot it. The user name and password are the ones you specified when you created the server.

Connected!

 

Great! Now we have a database server that we can do anything with! Try creating a database and some users and some tables.

After you are done playing with your server you will probably want to remove it. If you don’t you will be billed per hour until you do, so don’t forget this! Use the following command to delete it:

rds-delete-db-instance mytestserver123 --skip-final-snapshot

The --skip-final-snapshot flag tells the program that you don’t need a backup of the server. This is ok as long as you are just experimenting. When deleting a server you get one final chance to stop the process. Answer Y at the following prompt to start the deletion:

    Once you begin deleting this database, it will no longer be able to accept connections.
    Are you sure you want to delete this database? [Ny]y
DBINSTANCE  mytestserver123  2009-11-04T11:50:21.554Z  db.m1.small mysql5.1  5
 root  deleting  us-east-1b  1
      SECGROUP  default  active
      PARAMGRP  default.mysql5.1  in-sync

Now you server will be deleted after a few minutes.

Using the web services from a .Net application

Well, all this has been nice and useful, but not very fun. So I decided to write an application that manages these servers for us. Since I’m a .Net developer that is the tool I will be using.

MySQL has a nice set of ADO.Net compatible classes for download here: http://dev.mysql.com/downloads/connector/net/6.1.html.
And AWS has a nice set of .Net classes that wrap the RDS web service for download here: http://developer.amazonwebservices.com/connect/entry.jspa?externalID=2946&categoryID=293.

I found a bug in the AmazonRDSClient class where the SkipFinalSnapshot argument was incorrectly encoded. This is easy to fix by changing line 1458 in AmazonRDSClient.cs:

// From
parameters.Add("SkipFinalSnapshot", request.SkipFinalSnapshot + "");
// To
parameters.Add("SkipFinalSnapshot", request.SkipFinalSnapshot.ToString().ToLower() + "");

Using these classes I wrote a simple WPF application that connects to RDS and downloads a list of server instances. It also lets you create new instances, delete instances and test that you can connect to an instance.


Download the source code here. The application is a WPF 4 application that needs Visual Studio 2010 to compile.

RDSApp.zip (781.82 kb)

 

kick it on DotNetKicks.com

.NET, Databases , , , ,