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):
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:
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:
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:
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.
30 Comments. Leave new
That’s the best “How to setup cloud-anything” article I have seen, even better it applies to SQL Server!!
HA HA at the “shut down the server or you’ll pay” comment, right on!
Personally I am not a big fan of the cloud computing, especially at $800/month.
For someone learning, I still suggest either the 180-day trial version, or the $50 Developer Edition
Thanks, Jerry! I appreciate it. I hope it helps people get started faster than it took me!
You’re right: if you’re going to do long-term training on a single machine, you’re better off buying a cheap PC and installing the trial or Developer Edition on it. EC2 comes in as a neat solution when you need to quickly spin up multiple machines to test mirroring failovers, test log shipping, test vendor products without blowing away your machine and reinstalling, etc – but you pay for that convenience. I wanted to spin up a new lab server, install a beta and test it against a few large databases yesterday, and it cost me the whopping sum of $3 to use it for 3 hours. That’s pretty convenient.
Good article Brent. Love to see more like it when you have time.
Anu comments on Azure?
Thanks, Bill!
About Azure – the cool thing about working for a huge vendor like Quest is that we get access to a lot of stuff, but the drawback is that I’m under NDA. 🙂 I don’t have anything I can talk about that isn’t already public, or that isn’t protected by NDA. Doh!
Hi Brent,
How about persistance? Can I use my sql server instance for production? Can I keep it running all the time? What if it crashes, do I lose all my data?
I saw your page on sql server hosting and you didn’t mention amazon’s SimpleDB. What do you think of it in relation to Microsoft’s SDS?
Emad – yep, you can use it for production. Read the documentation on EC2, and you can use persistent storage in Amazon S3 for your machine’s hard drives.
I don’t have much of an opinion about either SimpleDB or SDS right now. I’m a SQL Server DBA, and my opinion is that if I can’t connect to it with T-SQL, it’s not on my radar. I think they’re both interesting technologies, but they’re both pretty early in their lifespans, and I wouldn’t want to bet the farm on either one just yet.
Hi,
I’ve been wanting to investigate Amazon Web Services for some time now, and this is by far and away the best introductory article I’ve seen. I’ve started doing some reading a couple of times before but never really got anywhere.
Got a simple Windows 2003 / SQL Server Express edition up and running, and I’m just knocked out. I’ll never need to buy a “spare PC” for server work again.
This is great technology, made accessible by a great article.
Thanks Brent.
Hi Brent,
Thanks a lot for the comprehensive and easy to follow instructions.
I got stuck at getting the Administrator password. The certificate with the credentials (*.pem file) doesn't seem to be recognized. You mention in your article that it eventually works after couple tries. It never did for me.
Is there any workaround that you have found for that issue?
No, I haven't, but Amazon has added a new management console that may suit your needs better than the Firefox plugin. I haven't used it yet, but it's on my to-do list this week, funny enough: https://console.aws.amazon.com/
Thanks Brent,
The Firefo plug-in eventually worked and I was able to get the admin password for the SQL Server instance.
Thanks again for taking the time and documenting your experience. It's much appreciated!
Thanks for the post Brent, I don’t know if I can connect my SSMS (on my laptop) to the sql instance in the EC2 server… if so what do i have to put in the SSMS to connect????
You have to put your fully qualified domain name for your instance. It’s in the Amazon control panel, and it’s around a hundred characters or more long. Also, you’ll need to open port 1433 in order to connect, and that’s not really a good idea over the internet. You’re better off remote desktopping into the EC2 server and running SSMS from there.
Agreed! one more question how can I upload a backup file to the server?
You can save it on a file sharing service like drop.io or filedropper.com and retrieve it via RDP.
I am new to EC2 and figuring things our how to setup my servers.I am using SQL Server 2005 on windows server 2003 .
I am an ASP.NET developer and not a DBA .For backup instead of doing log shipping and storing transaction logs I am thinking to have a differnt solution which can work like Full backup.I think to have 5 snapshots of my EBS database through out the day.Snapshots older than 2 days will be deleted.In this case all snapshot will have Fullbackup for last 2 days as those are going to be exact same copy of EBS volume.
Is this going to work or its too much allocation of S3?
Waitig for your suggestions.
It depends on the size of your database and how critical it is. If it’s critical, it’s worth the money – but only you can decide that.
How does the database persist in the case of an instance crash? Don’t you need to setup an ESB and put your database files on that? That’s what I am looking for help with. I setup my server, create a ESB, connect the ESB as a drive letter to my instance. That all seemed to work fine, however restarting the instance and re-connecting the ESB was a bit clumsy and I kept feeling like I was doing it wrong.
So if you felt like writing a new article about how to use EC2 and ESB to run your PRIMARY database application in the cloud I would love it.
thx
Yes, you’re right – dealing with that is pretty clumsy, unfortunately, and I don’t know of an elegant way of doing that at this time. I don’t use EC2 for anything permanent, only short bursts of analytics, so I don’t need to persist the data. You might try checking the EC2 forums at Amazon though.
Hi Brent,
I an able to login the remote server .. whne i open the sql sever management studio asking user name and password.. which on we need to provide
You’ll need to refer to Amazon’s documentation for that, unfortunately – I don’t know off the top of my head.
Thanks for this nice and detailed description. I have one query: Will the SQL server on EC2 support Windows authentication? How can we configure that?
Good day!
I’m was able to create an EC2 instance with a SQL Server 2008 express on it. I’m able to RDP to the instance from my workstation, but I cannot connect to the DB server from the SSMS in my workstation. I already added the port 1433 to the firewall settings. Also, any ideas how to setup hamachi in EC2? Help!
Hi,
I am looking to experiment with SQL failover clustering. Do you think it will be cost effective to set up on Amazon EC2?:
I want to practise the exercises in the MS book 70-432 exam chapter 14. Here is the requirements:
Before You Begin
To complete the lessons in this chapter, you must have
???? Cluster-capable hardware or Microsoft Virtual Server 2005 R2
???? Windows Server 2003 SP2 and later or Windows Server 2008 installed on your server
NOTE VIRTUAL SERVER
You can use Virtual Server and Microsoft Virtual PC to simulate hardware confi gurations.
Unlike Virtual PC, Virtual Server supports Windows clustering and you can use it to build
a SQL Server failover cluster.
IMPORTANT LESSON PRACTICES
You use Virtual Server for all the practices in this chapter. To follow the steps in the practices,
The practices in the lessons require you to have performed the following actions:
???? Created three virtual machines
???? Installed Windows Server 2003 Standard edition and later or Windows Server 2008
Standard edition and later onto each virtual machine
???? Configured one virtual machine as a domain controller
???? Configured two virtual machines as member servers in the domain
???? Configured the domain controller with a single network adapter, as shown in Table 14-1
???? Configured the member servers with two network adapters, as shown in Table 14-1
???? Configured all networks as Guest Only
Hi, Arnold. “Cost effective” depends on your personal finances. If you don’t have any hardware at home, you might find it cheaper to use EC2 for this instead of buying a powerful desktop to run Virtual Server or Hyper-V.
Brent,
You mention using this for disaster recovery – shipping logs up to the storage for the machine while it’s shut down. Sounds great to me. What about security to the machine. Would you change to a non-standard port and then force ssl connections? What else could be done?
“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.)”
Several years later – ever get around to writing this? Maybe just a brief outline of what you’d do?
Thanks oh so much!1
Sam – thanks! I wouldn’t expose it to the web. Amazon has a VPN product so you can bridge to your own datacenter.
If you check out https://www.brentozar.com, we’ve got a list of upcoming events on the right. Jeremiah’s got an upcoming webcast on SQL in EC2. Enjoy!
Also, just to clarify – I did cover the log shipping to a non-SQL box in one of my paid private webcasts at SSWUG. I don’t think it’s still online, but in a nutshell, you can use something like JungleDisk to copy your backups to S3 and then restore them in EC2.
Got it – I’ll have to watch Jermiah – saw Kendra do one and it was good stuff. I was also wondering about what type of storage would be used, but EC2 docs say that W2008 is default to EBS, giving you persistent storage. VPN is a extra charge of .05 per hour of connection – and I would assume you could shut that down just like you could with the SQL box. Looks great for SAN meltdowns!
Hi Brent Ozar,
Could you please let me know How to restore full sql server database to amazon EC2?
I have two servers in an EC2 (amazon cloud services), both are SQL Server 2012 standard edition.I have a .bak file of local server and i have few questions. How can I move it on EC2 and restore it? How can i configure backup on s3? How can recover it from s3 ? How can i configure log shipping / mirroring/ replication. If any one have reference link then please share it.
SQLUser – Amazon EC2 is just a virtualization platform. Everything that works on traditional SQL Servers works the same in EC2. Your best bet for EC2-related questions will be Books Online.
Amazon S3 is nothing like a Windows file share. If you’re just now learning log shipping, mirroring, and replication, I’d avoid using Amazon S3. Stick with traditional file shares for now.