Getting Started with Amazon EC2 SQL Server 2005

Sometimes you need an offsite database server in case something goes wrong, but you can’t afford a full-blown disaster recovery datacenter.  Or maybe you’ve got some ideas that you’d like to try out with a big SQL Server 2005 box, but you don’t have the hardware sitting around idle.  Or maybe you’d just like to learn SQL Server 2005 – sure, it’s not the latest and greatest, but it’s still the most popular version out in the wild.  Now you’ve got a way to accomplish this for around $1 per hour.

With Amazon EC2, DBAs can “rent” virtual servers running Windows 2003 and SQL Server 2005.  In this article, I’ll explain the five big steps required to turn on your own SQL Server in Amazon’s datacenters.

Step 1: Understand what “cheap” SQL Server hosting costs.

As of this writing (10/2008), here’s the smallest SQL Server you can get at Amazon EC2, what’s known as a “Standard Large” configuration:

• 7.5 GB of memory
• 4 EC2 compute units (2 virtual cores with
• 850 GB of storage
• 64-bit Windows 2003
• SQL Server 2005 Standard
• $1.10 per hour, or roughly $800 per month

Yes, that’s a lot of money, and no, that does not include your bandwidth.  You’ll also incur some additional fees for bandwidth – probably nowhere near $100/mo, but you can use the Amazon EC2 pricing calculator to estimate your numbers.

Let me put it this way: $9 for this much power for one day is a heck of a deal.  I can do a lot of learning and experimenting for my $9.  Even if I use it as a lab box one day per week, that’s still around $40/mo – not a bad deal, especially if I’m a junior DBA who wants a sandbox to break stuff.  At $800/mo, well, we’re in Jaguar XF territory.

Step 2: Get an Amazon EC2 hosting account.

Sign up for an account at aws.amazon.com.  It’s free to enroll, but you have to link it to a method of payment like your credit card or checking account.  Your server usage will be deducted automatically from your account, so don’t blame me if you turn on a whole virtual datacenter and you can’t pay your rent.

After you’ve got the account set up, enable your account for EC2 (the virtual server hosting part) and S3 (where you can store your hard drives):

• Sign Up for Amazon EC2
• Sign Up for Amazon S3

Step 3: Get the Elasticfox plugin for Firefox to manage your virtual EC2 SQL Servers.

Elasticfox is a browser-based way to manage your virtual army.  It’s free, it’s open source, and it’s the easiest way to get started with EC2.  There’s a guide on getting started with Elasticfox, but I’ll give you the highlight reel:

• Download the Elasticfox plugin and install it
• Launch Firefox and click Tools, Elasticfox.
• Click the Credentials button and input your Amazon Web Services access key and secret key.  Click OK.
• Create a key pair (to encrypt your Windows admin login) by clicking the KeyPairs tab and click the button to create a new keypair.  Type in anything for the key name, and save the certificate file.
• Build a set of firewall rules by clicking the Security Groups tab.  In theory, you could skip this step and leave all your ports open, but come on.  Click the Create Security Group button and create a group named SQL Servers.
• Click the Grant Permission button to set up a firewall rule.  For testing purposes, you can leave the CIDR (source) IPs at 0.0.0.0/0, which means the entire internet.  For production, you would want to restrict this access to your company’s subnet.  In the Protocol Details, set up each of these:
  • TCP/IP 3389 – remote desktop
  • TCP/IP 1433 – SQL connections (if you want to connect to your cloud-based server using SSMS on your desktop)

Step 4: Start up a virtual EC2 SQL server.

Go back to the AMIs and Instances tab and your Elasticfox screen will look something like the below screenshot (you can click on it to enlarge).  I’ve resized my columns to make it easier to see the instances I want:

Elasticfox AMI List

In the screenshot, there’s an edit box at the right side where I typed in “sql” to help filter down the list of instances.  Amazon has a ton of servers available, and you have to pay close attention to get the right one.  Here’s a zoomed screenshot, and the highlighted one is the one I’ll be using:

Readthe filename carefully: the “Anon” means it’s not using the extra-charge Windows Authentication Services, and the “v1.01″ is the latest version available as of this writing (10/2008).  Newer versions mean newer patches of Windows and SQL, so the newer the better.

Right-click on the instance you want and click Launch Instance.  The next screen is full of pitfalls.

The Instance Type must be m1.large or greater.  The default is probably going to be m1.small, but that won’t work.  If you try to launch a SQL Server with m1.small, you’ll get this error:

Amazon EC2 Launch Error

The error says:

“EC2 responded with an error for RunInstances
InvalidParameterValue: The requested instance type’s architecture (i386) does not match the architec…”

The virtual image for SQL Server is a 64-bit machine, and you have to launch it with an InstanceType of m1.large or larger.  This catches me all the dang time.

For the KeyPair dropdown, choose the certificate name you generated, and under Security Groups, move SQL Server over into the “Launch In” group.

Click Launch, and if all goes well, your instance will show up in the “Your Instances” list in the bottom of the screen.  It takes a minute for the server to boot, but the Elasticfox screen doesn’t update on its own – you have to push the Refresh button manually to see if the server’s available.

Step 5: Connect to your new virtual EC2 SQL Server.

When the server’s State shows “running”, right-click on it and click Get Administrator Password.  Elasticfox will ask for the key pair certificate file that we created earlier.  I’ve had problems with it not always recognizing the file, so just try again and it’ll probably work.  The administrator password will be saved to your clipboard.  Windows doesn’t always allow pasting into the password field, so you may need to bring up Notepad, paste the password in there, and then look at that Notepad screen while you’re logging in.

Click on the server and click the Connect button in Elasticfox.  Elasticfox starts the Remote Desktop client and directs it to the server’s public DNS name, which is going to be something completely forgettable.  Don’t worry – if you’re planning to use this server for disaster recovery, you can assign it a permanent IP address and a better DNS name, and there’s plenty of instructions for that in the Amazon documentation.

When you start SQL Server Management Studio, you’ll either have to put in (local) for the server name to connect to, or start the SQL Server Browser service.

Before you create databases, go into Windows Explorer and take a look at your hard drive configuration:

800 GB, here I come!

In this screenshot, I’ve got two local drives, D and E, each with 420 GB.  Cha-ching!

From here, the world is your oyster.  You could set up database mirroring, and use this as a disaster recovery server.  Be aware that SQL 2005’s database mirroring is not compressed, so your bandwidth charges may be higher.  Instead, I’d suggest doing log shipping.  The advantage to using log shipping is that you can compress it with Quest LiteSpeed, plus you don’t necessarily have to be running the SQL Server at all times.  You can copy the files to a cheap non-SQL box at Amazon, and only start up the SQL Server once per day (or per week!) to apply the log files.  (I see a blog post coming on that after PASS when things die down.)

Another great use: testing software.  The whole reason I wrote this article today was that I had to test a new beta of Quest Change Director, and I needed a quick new environment to test it in.

I’ve got an upcoming project where I’m working with a European client on a SharePoint whitepaper, and both of us work for secure public companies with paranoid IT departments.  Neither company wants to give VPN access to the others’ staff, so instead we can just build our lab in Amazon EC2 from scratch and both access it from anywhere on the planet.  Everybody wins.

Just don’t forget to shut the server down when you’re done with it, or else you’ll keep paying by the hour!

Update 10/30 – Stephen Moore asked about SQL Express, and yes, they do have AMIs prebuilt with SQL Express and they start as low as $.125 per hour.  That’s a steal if you only have small databases.