This post walks through a simple introduction to how to deploy a website to Amazon Web Services from Visual Studio that includes a simple database.

This is the second post in a series intended to give .NET developers a quick start for using AWS. Part 1 of the series showed how to get a basic MVC website from Visual Studio into AWS. In Part 2, we'll add a database. This post will also explain some of the database options available to you in AWS.

Overview

In AWS, you have three high level options for a database:

  1. Simply provision a VM and install whatever database software you want on it
  2. Use the AWS NoSQL database solution, called DynamoDB
  3. Use the AWS Database-as-a-Service model, called RDS (Relational Database Service).

This post will use the third option.

RDS

AWS RDS is a database Platform-as-a-Service (PaaS) offering. Basically, it gives you the ability to provision a database in the cloud for use with your applications. It comes with some management services as well, so you don't have to worry about backups, seamless patching, etc. You can also easily setup failover, replication, mirroring, etc.

AWS offers a variety of database servers and versions, including offerings from MySQL, PostegreSQL, Oracle, and of course Microsoft. For Microsoft, AWS supports SQL Server 2008 and 2012R2 in the Express, Web, Standard, and Enterprise editions. Note that the Enterprise offering is only available in RDS and not as an EC2 AMI.

Overall performance is very good. Notably, AWS provides a wide range of disk options, IOPS options, and even options like "Burstable T2" which lets you earn credits over time to apply to periods of intense core usage.

One limitation is that you will not have access to PerfMon on your SQL instance. But, you can query the SQL DMV's to get all the performance stats that you need.

Deployment

The first thing you need to do is set up a security group and parameter group.

To configure the Security Group, open the AWS Management console, choose EC2, and then in the left nav bar click "Security Groups." Note that the "Security Groups" link on the RDS page is deprecated.

Create a new Security Group named something like "RDS Security Group."

Edit the Inbound Rules and add a rule as follows:

In the AWS Management Console, click RDS, and then "Launch."

In the "Select Engine" step, choose the SQL Server tab, then "sqlserver-ex" (Express).

In the "Specify DB Details" section, enter the settings as follows:

Note that these settings make the database eligible for the free tier.

Enter the following for Advanced Options:

The Security Group and Parameter groups should match what you created above.

You can change the port to something other than 1433 if you choose (for security reasons, for example) but it has to be opened in the Inbound rule in your Security Group.

Click on the button to create the instance and wait. This will take longer than you expect to actually provision the instance. In the meantime, you can get your Visual Studio project setup as show in the first post.

Connecting

Once the instance is provisioned, you click on the selector box, or do Instance Actions – See Details. Copy the connection string from here:

Open SQL Management Studio (or your preferred database utility) and connect using that server name and the credentials you entered when launching the instance:

Once opened, create a new database, add tables, data, etc.

Back in Visual Studio, connect with your database like any other SQL Express instance:

	Liststring> myDbItems = new Liststring>(); 
	string ConnectionString = "Data Source=awssqlx.ceenbig7dqz2.us-east-1.rds.amazonaws.com,1433;Initial Catalog=testdb;Persist Security Info=True;User ID=Administrator;Password=Password123;Encrypt=False"; 
 
	using (var Conn = new SqlConnection(ConnectionString)) 
	{ 
		using (var Cmd = new SqlCommand("SELECT ID, Value from MyTable1", Conn)) 
		{ 
			Conn.Open(); 
			SqlDataReader rdr = Cmd.ExecuteReader(); 
			while (rdr.Read()) 
			{ 
				myDbItems.Add(rdr[1].ToString()); 
			} 
		} 
	} 

(Obviously, store the connection string in a config file, encrypt it, and other good coding practices as usual.)

A note on security- most of us are probably used to using Windows credentials to connect to databases. That is certainly possible in AWS, but beyond the scope of this article.

Additionally, you would want to configure your Security Group rules and subnets so that only your Web or App server could access the RDS instance.

Conclusion

Overall the PaaS database options in AWS are extremely easy to use.

Microsoft of course offers similar options in SQL Azure. Comparing Azure and AWS database options in detail is a topic for another day.

AWS RDS of course gives you more options for a database beyond just SQL Server. Since the pricing levels in both services change so frequently I won't bother going into specifics here, but AWS is definitely competitive from that perspective as well.