Get Started With Amazon AWS RDS SQL Server

In the past, if you were a SQL Server shop and wanted to use the Amazon AWS cloud, your only option was to bring up Windows Virtual Machine instances and run those machines just like your on premises machines. With the new Amazon RDS SQL Server options, developers and IT shops can now spin up standalone SQL Server databases without having to worry about the machines that are hosting those databases. This brings Amazon’s SQL Server offering more inline with SQL Azure. Both have their benefits, and it’ll be up to each developer to decide what’s right for them.

Overview

For small developers and startups, Amazon has provided a free tier that includes SQL Server Express and up to 20 GB of storage (note that SQL Server Express 2008 R2 has a size limitation of 10 GB, so this 20 GB cannot be a single database). This is valid for new customers and for 1 year and is a good start. After this first year, this same instance will cost you $0.035 per hour or around $300 per year regardless of how much data you use.

Compare with Microsoft’s SQL Azure

If you have straightforward database needs, the two options are competitive. SQL Azure has some hard limitations make the decision easy for some IT shops (in favor of Amazon). More research will be necessary to see if there are any deal-breaking limitations on the Amazon RDS product, such as the ability to include .NET assemblies (SQL Azure has that limitation).

Database Setup Walkthrough

1. Once the AWS Toolkit is installed, click on View -> AWS Explorer to view the AWS Explorer. This will show all the options (once you have put your account information in). Expand “Amazon RDS”, right-click on “DB Instances” and click “Launch DB Instance”.
2. Then be presented with all the various options for creating a database. Scroll to the bottom and you’ll find the SQL Server options. For now, the options are SQL Server 2008 R2, but SQL Server 2012 is coming later this year. The options include:
   • SQL Server Enterprise Edition
   • SQL Server Express Edition
   • SQL Server Standard Edition
   • SQL Server Web Edition
   For the purposes of this walkthrough, I’m going to create an Express Edition instance.
3. After picking the instance type, you’ll be asked to set various options on that instance including what class of machine is desired, storage allocation, instance name, and a master username/password.
4. Then you can set additional options like the port number and the security group. By default, a security group is created with the  name “default”. Click the “Add current CIDR” check box to give your development machine firewall access to that database.
5. Next up are backup options. Note that these are backup options for the instance, not for your databases.
6. Review the options selected and click “Launch” when you’re ready. Clicking this will tell Amazon to create the instance and that will take a while. Double click on the new instance in the AWS Explorer to view the progress of your instance creation. This means that you cannot immediately create a database.
7. Double-click on the new instance to view its current status. Once the instance status says “Available”, you’ve now got a SQL Server that you can interact with just like any other on-premise SQL Server. Now let’s create a database.
8. Right click on your brand new instance and click “Create SQL Server Database”.
9. You’ll then see a dialog box that will, once you’ve typed in the correct username/password combination, let you name the database. That’s it! You can now interact with your database via SQL Server Management Studio or the Server Explorer in Visual Studio.

Prove It To Me

[code language=“csharp”] public class Category { public string CategoryId { get; set; } public string Name { get; set; }

    public virtual ICollection<Product> Products { get; set; }
}

public class Product
{

public int ProductId { get; set; } public string Name { get; set; } public string CategoryId { get; set; }

public virtual Category Category { get; set; } }

public class ProductContext : DbContext { public DbSet<Category> Categories { get; set; } public DbSet<Product> Products { get; set; } } [/code]

Using ASP.NET MVC 3, I created a quick CategoryController and let studio scaffold the necessary CRUD methods. To use the RDS database, just like any other database, we need to set the connection string in the web.config.

[code language=“xml”] <add name="ProductContext" providerName="System.Data.SqlClient" connectionString="Data

Source=heavyrdstest.coe5trsaqias.us-east-1.rds.amazonaws.com,1433;Initial Catalog=heavytestdb;User

ID=heavyrdsmasteruser;Password=********" /> [/code]

Your connection string will look similar, but is dependent on the data center chosen as well as other settings. A quick way to find the connection string is to view the properties on your database from the Server Explorer.

I ran my test MVC application and used it to create a couple of Category records. To confirm that these records were created correctly, I expanded my database in Server Explorer, right clicked on the database and clicked “Show Table Data”. And voilà!

More Options Can Be Good

Questions or comments? Let me know below. Be sure to follow me on Twitter @kenstone.