In my last post I demonstrated how you can use MySQL with Entity Framework 4.
In this post I’m going to show you how to use Amazon RDS. Amazon RDS is a Relational Database Service which is similar to SQL Azure except that it supports MySQL & Oracle is coming soon. This is the actually the first time I’ve attempted to use the service and am going to be writing this as I go.
Amazon RDS takes care of all the critical database management tasks like software updates, backups & replication.
This post assumes you already have an AWS (Amazon Web Services) account, if you don’t go to the Sign In page.
As with all Amazon Web Services you have to explicitly sign up, you can do this by going to http://aws.amazon.com/rds/ and clicking the “Sign Up For Amazon RDS” button.
Note: Signing up is not instant, it took about 12 hours for me to receive the confirmation email.
Launching the DB Instance
Log into the AWS console and go to the Amazon RDS tab.
Step 1 – Launch DB Instance
Click on the big Launch DB Instance button.
Step 2 – DB Instance Details
After clicking the Launch DB button you will get the below screen.
For information on the DB Instance Class go to the AWS RDS page.
Multi-AZ Deployment is the ability to have the DB instance replicated in another availability zone with Amazon managing the failover, we’re not going to worry about that for now. This can be changed at anytime after the instance is created.
Note: See the maximum size of 1024 GB, compared with SQL Azures offering of 50GB.
Step 3 – Additional Configuration
On this screen you can specify the Database name, port & availability zone.
Step 4 – Management Options
On this screen you set your preferences for DB backups and maintenance. You’ll want to tweak this depending on where your customers are geographically located.
Step 5 – Review
Finally you can review all of your settings and when you’re satisfied click the Launch DB Instance and let Amazon work their magic.
After a about 5 minutes or so the instance will be available in the DB Instances page.
Now that we’ve created our instance we need to set some security rules in order to be able to access it from EC2 instances and any other external machines. I’m going to add a rule to allow access from machine so that I can do the data & schema import.
You can either choose from CIDR/IP or EC2 Security Group when setting your rule. When selecting the EC2 Security Group you write the name of the security group used in EC2.
For more information on security groups go to Working with DB Security Groups.
Importing the Data & Schema
The preferred method for importing schema and data into your DB Instance for datasets of 1GB and less is to extract the data with mysqldump and pipe it directly to your instance. If you dataset is larger than 1GB checkout the Amazon RDS Customer Data Import Guide for MySQL.
First you need to get the Endpoint for the instance from the AWS console. This can be found be going to the DB Instances page and clicking on your DB Instance.
Before starting you’ll want to check that you can connect to the instance properly using telnet:
telnet efdemo.cauwsk2cfjqz.ap-southeast-1.rds.amazonaws.com 3306
If you experience any problems here then check your security group rules and local firewall settings.
Here is the command for the import:
"C:\Program Files\MySQL\MySQL Server 5.1\bin\"\mysqldump efdemo --host=efdemo.cauwsk2cfjqz.ap-southeast-1.rds.amazonaws.com --port 3306 --user=willbt --password
If everything went to plan you should get the “Dump completed” message in the command prompt and we’re ready to test the connectivity from EC2.
If you have issues, make sure the version of your DB Instance and local MySQL Installation are the same.
Of course you could also just connect to your instance using MySQL Administrator & MySQL Query Browser.
Accessing from EC2
Once you’ve got your instance setup in EC2 you can deploy your application to it.
If you’ve added the same security group that your EC2 instance uses to the DB Instance security groups then you should have no problems connecting to your DB Instance. Make sure the port you specified when creating the DB Instance is configured in any firewall rules.
To double check go onto your EC2 instance and run the telnet command again, if you have any issues check the instance firewall rules and the security group settings again.
You will need to also install the MySQL Connector onto your EC2 instance or make sure you set Copy To Local on the assemblies MySql.Data and MySql.Data.Entity.
Finally the connection string by setting the server attribute to the Endpoint of your DB Instance.
<connectionStrings> <add name="EFDemoEntities" connectionString="metadata=res://*/EFDemo.csdl|res://*/EFDemo.ssdl|res://*/EFDemo.msl;provider=MySql.Data.MySqlClient;provider connection string="server=efdemo.cauwsk2cfjqz.ap-southeast-1.rds.amazonaws.com;User Id=willbt;Password=password;database=efdemo;Persist Security Info=True"" providerName="System.Data.EntityClient" /> </connectionStrings>
When you compare the pricing between a MySQL solution and the SQL Server solution it’s very easy to see benefits of going MySQL.
|Annual USD||Monthly USD|
|My SQL RDS (Large)||
|SQL Server Standard (Large)||
That’s a pretty big difference & also bear in mind that with a SQL Server solution you’re not going to get automated backups, updates & failover.
Writing this blog was my first experience with Amazon RDS and I have to say I am very impressed with just how easy it is to get setup.
Amazon EC2 & RDS with MySQL for .NET applications is a very attractive deployment option and the next greenfield project I work on I’ll be seriously considering Amazon RDS with MySQL.
I’ve got a few pet projects kicking around with would be well suited for this platform so stay tuned.