Get Started With Amazon AWS RDS SQL Server

Microsoft

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

More more information on the RDS SQL Server product, check out the Amazon website. The idea is that you can spin up databases at will, change their computing power as needed, and generally not worry about backups and software patches. This is good for developers who don't excel at these types of tasks or cannot afford a DBA or IT guy to keep up with these demanding tasks.

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

It's important to note that SQL Azure is not the same as the traditional SQL Server product that Amazon is offering. You can Compare SQL Server With SQL Azure over on Technet to see an extensive list of the differences. A 5 GB database would cost around $300 per year on 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

The easiest way to get started with SQL Server on Amazon RDS is to download the AWS Toolkit for Visual Studio. This tool provides easy access for developers to work with various AWS offerings such as DynamoDB, EC2, S3, SimpleDB, and RDS. This tool is not new as both MySql and Oracle databases could be created using it before. Here are the steps to setting up a SQL Server instance and database (these instructions assume you already have an AWS account and know how to set it up):

  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". RDS Create new SQL 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. RDS Create New SQL 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. RDS SQL Create New Instance Options
  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. RDS SQL Create New Instance Options 2
  5. Next up are backup options. Note that these are backup options for the instance, not for your databases. RDS SQL Create Instance Backup Options
  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. RDS Manipulate DB options
  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. Sequence of status of new RDS SQL DB instance
  8. Right click on your brand new instance and click "Create SQL Server Database". Amazon RDS SQL Create 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. RDS SQL Create Database Choose Name

Prove It To Me

To prove that this database is used just like any other, I'm going to create an Entity Framework Code First model and point it at my new SQL Server. Using an EF Code First Walk Through as inspiration, let's create a data model for product Categories.

[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.

RDS SQL Obtain Connection String

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à!

RDS View Table Data

More Options Can Be Good

I'm generally in favor of fewer options in life as too much choice can cause paralysis. In this case, however, this release by Amazon is good for the .NET and SQL Server community as it provides options that were necessary and weren't available.  SQL Azure is a terrific offering and I use it myself. Depending on a developer's needs, however, the Amazon offering may be more fitting. I urge you to take a look at both solutions and use the right tool for the job.

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