Skip to main content

Entity Framework 4 with Amazon RDS

Image
By

 

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.

Signing Up

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.

launchdb

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.

launchdb_step1

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.

launch_db_step2

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.

launch_db_step3

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.

launch_db_step_4

Done

After a about 5 minutes or so the instance will be available in the DB Instances page.

DBInstances

 

Security

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.

security_groups

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.

Connection

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.

endpoint

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.

Import

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=&quot;server=efdemo.cauwsk2cfjqz.ap-southeast-1.rds.amazonaws.com;User Id=willbt;Password=password;database=efdemo;Persist Security Info=True&quot;" providerName="System.Data.EntityClient" />
  </connectionStrings>

Pricing

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)

$ 3854.40

$ 321.20
SQL Server Standard (Large)

$ 9460.80

$ 788.40

 

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.

Conclusion

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.

Labels:

Popular posts from this blog

ASP.NET MVC Release Candidate - Upgrade issues - Spec#

By
First of all, great news that the ASP.NET MVC Release Candidate has finally been released.  Full credit to the team for the hard work on this.  You can get the download here  However this is the first time I have had upgrade issues.  Phil Haack has noted some of the issues here   If like me you have lot's of CTP's and Add-Ins then you might experience some pain in Uninstalling MVC Beta on Vista SP1  This is the list of Add-Ins / CTP's I had to uninstall to get it to work  Spec# PEX Resharper 4.1  Sourcelinks ANTS Profiler 4   Can't say I'm too impressed as it wasted over an hour of my time.  As it turned out Spec# turned out to be the offending culprit, it's forgiveable to have issues with a third party product but a Microsoft one? Guess no-one on the ASP.NET team has Spec# installed. 
Read more

Freeing Disk Space on C:\ Windows Server 2008

By Will Beattie
  I just spent the last little while trying to clear space on our servers in order to install .NET 4.5 . Decided to post so my future self can find the information when I next have to do this. I performed all the usual tasks: Deleting any files/folders from C:\windows\temp and C:\Users\%UserName%\AppData\Local\Temp Delete all EventViewer logs Save to another Disk if you want to keep them Remove any unused programs, e.g. Firefox Remove anything in C:\inetpub\logs Remove any file/folders C:\Windows\System32\LogFiles Remove any file/folders from C:\Users\%UserName%\Downloads Remove any file/folders able to be removed from C:\Users\%UserName%\Desktop Remove any file/folders able to be removed from C:\Users\%UserName%\My Documents Stop Windows Update service and remove all files/folders from C:\Windows\SoftwareDistribution Deleting an Event Logs Run COMPCLN.exe Move the Virtual Memory file to another disk However this wasn’t enough & I found the most space...
Read more

CPF Contribution Rates for new Singapore Permanent Residents (SPR’s)

By Will Beattie
Recently my wife and I applied and got approved for Singapore Permanent Residency. After completing the formalities the most significant immediate change is the contribution to CPF which is Singapore’s mandatory social security savings scheme requiring contributions from employers and employees. CPF contributions start from the date you obtain SPR status, which is the date of the entry permit.   Being a relentless budgeter I needed to know exactly how much I and my employer would have to contribute so that I could adjust my budget accordingly as the employee contributions get deducted from the monthly salary. After doing some research I discovered that there is a “graduated” approach to CPF contributions for new SPR’s where the contributions gradually increase in the first and second year and then upon reaching the third year are at the full amount. Note: There is an option for employers to contribute the full amount for year 1 and year 2 and the employee can use the gra...
Read more