Drp | Brent Ozar PLF | Brent Ozar PLF

Tag Archive: drp

DBA Nightmare: SQL Server Down, No Plans

Written on May 3, 2011 by Brent Ozar in SQL Server

Managing data is about managing risk, but no matter how we good we are at managing risks, they’re still risks.

We’ve seen several high-profile data failures recently:

Ouch. It’s time we start a series of DBA Nightmares to cover basic preparations that should be a part of every DBA’s career planning. Why career planning? Because if one of these happens to you and you’re not prepared, it’s a URLT moment – Update Resume, Leave Town. If, on the other hand, you’re well-prepared and react smoothly, this could be your moment to shine.

Today’s Nightmare: From-Scratch Server Restore

Let’s be honest: most of us have never rebuilt a product server from scratch under duress. Many of us bury our heads in the sand, hoping production will just keep on keepin’ on. We don’t test our backups, and even if we do, we don’t go to the extreme of attempting a complete from-scratch reinstall. When the system is down and the CIO’s standing behind us, tapping us on the shoulder, we learn some ugly lessons.

Right away, you need to choose one of two recovery plans: will you try to restore everything exactly as it was (including the system databases), or will you build a new server from scratch and just restore the user databases? Ideally, you’ve designed your recovery plan ahead of time, but in a nightmare scenario, you’re standing in the datacenter with empty pockets and no game plan.

If you decide to restore the system databases, you should try this ahead of time. Restoring the master database is different than typical user databases because you can’t use SQL Server Management Studio. You have to set the SQL Server to run in single-user mode, then use SQLCMD to restore the master database, then remove the -m parameter that you added to start SQL Server, and start it back up again. If you’re using a third-party product to do your database backups, it’ll require separate instructions like Tom LaRock’s instructions on restoring master with Quest LiteSpeed. After restoring master, you’ll need to restore the msdb database, but fortunately that one can be done through the SSMS GUI as long as the SQL Server Agent is shut down.

If you don’t restore the system databases, you may be able to get your server up and running faster – at the cost of some configuration data. For example, logins, Agent jobs, and linked servers are stored in the system databases. On a small development server with a handful of logins and only maintenance jobs, it might be easier to install a fresh instance of SQL Server on a newly installed server, then just restore the user databases. (This is one of the reasons I try to avoid excessive custom logins or Agent jobs where possible on development servers.) Knowing your recovery process and risk will help you design your SQL Server security and Agent job configuration better.

If you decide ahead of time that your recovery plan involves a fresh OS and SQL Server, there’s one thing you can do to make your recovery process either: automate login creation. Schedule a job to run weekly with Robert Davis’s login copy script and send the results to yourself via email. That way, at the very least, you’ll have the exact list of logins, passwords, and SIDs to avoid the orphaned login problem when you restore databases. Run the create-login script sent to you by Robert’s tool, then as you restore each user database, the logins will automatically be associated and users can resume work as normal.

To help plan your build-versus-restore decision ahead of time, it helps to think through all of the implications. These are just some of the questions you’ll need to think through when designing a disaster recovery plan:

What service pack & cumulative update pack was the server running?
Did we have any non-SQL applications installed?
Were any server-level settings like trace flags configured?

For answers, try running my Blitz script ahead of time. I bet you’ll learn a lot about your servers – I know my clients do!

Brent Ozar

Brent specializes in performance tuning for SQL Server, VMware, and storage. He's one of the very few Microsoft Certified Masters of SQL Server, a published author, and a Microsoft MVP. He likes travel, Jeeps, Apple gear, jokes, and writing about himself in the third person. Read more and contact Brent.

Website - Twitter - Facebook - More Posts

8 Comments - Leave a comment!

What’s In Your Bug-Out Bag?

Written on January 29, 2010 by Brent Ozar in Computers

In my free webcast on disaster recovery planning for small businesses, I talked about building a bug-out bag.

My Bug-Out Bag

Every IT professional should have a bug-out bag in the trunk of their car with enough stuff to let them hit the road for 24-48 hours straight. This gear helps you react more confidently to minor problems and major disasters. Here’s what I’ve got in my bug-out bag:

A polyester polo shirt. Hardly the pinnacle of fashion, but polyester can be rolled up for months at a time without holding permanent wrinkles. If I spill something all over the front of my shirt right before I need to be seen in public, this gives me a little bit of an insurance policy.

A couple of old t-shirts. These can serve as apparel or rags, whatever’s needed at the moment.

Swimsuit trunks. Because sometimes an opportunity for sailing or swimming just presents itself, and I’m not too fond of skinny dipping.

A power inverter. Plug this into your cigarette lighter, and you can power a couple of electric outlets for things like laptops, cell phone chargers or a coffee bean grinder. (Yep – I used that after a hurricane. Forgot to grind beans ahead of time for my Bialetti Moka coffeemaker that runs off a gas stove.)

Jumper cables with instructions. Every now and then somebody’s car needs a jump-start, and I never remember how these are supposed to clip on. I’ve tried the kind that plug in via cigarette lighters and had miserable luck.

A pocket road atlas. In my disaster recovery webcast, I explain that you might not be able to rely on the internet connection in your fancypants phone. This little pocket atlas helps me find routes around problems.

A padlock. I move around a lot, and every now and then I need to lock up a U-Haul truck or a storage unit.

Some tools, including an adjustable wrench, needle-nose pliers, and a screwdriver kit with socket fittings. I’m not handy by any means, but I can lend these tools to someone who can actually fix something.

A self-powered radio/flashlight/charger. I bought this Eton FR150 for $30 last year, and it’s a slick little device. It’s a small weather radio that also doubles as a cell phone charger, and it’s powered via hand cranking or solar cells. The manual says that 15 minutes of cranking equals around 1 minute of talk time, and I hope I never have to find out. It’s easy to crank, and the weather radio works great.

I’ve also got a few more things that aren’t pictured:

Bottles of water
A box of energy bars
A pair of old blue jeans
A pair of sandals (good for boating or hiking)
Phone charger and cables (I keep those in the glove box)
Copies of my insurance paperwork

In the webcast, I also explain other things database administrators should keep in their bag, like install CDs, license keys, and contact lists. I’m lucky enough not to have to support any production applications at the moment.

You can build a bag just like this for under $50 using things you’ve already got around the house. They’ll do you much more good in your car trunk. Hurricane season is coming again this year – why haven’t you built a bug-out bag yet?

Brent Ozar

Website - Twitter - Facebook - More Posts

7 Comments - Leave a comment!

Losing Weight and Testing Code

Written on November 18, 2009 by Brent Ozar in SQL Server, SQLServerPedia Syndication

David Stein (Blog – Twitter) wrote a post called “Pop Quiz Hotshot” about starting your disaster recovery plan *now*. It’s a great read with good points that everyone needs to act on, but the comments indicate that not everybody’s seeing eye to eye. As usual, I rely on similes because I’m lazy.

How to Lose Weight

Get your pencils ready, because I’m about to give you the ultimate weight loss tip. It’s going to sound almost too good to be true because it’s so darned easy, and here’s the craziest part – it can actually save you money! That’s right – this is the tip that the exercise equipment industry, the personal trainer industry, and the vitamin industry are desperately trying to keep under wraps. You ready? Here it comes. Don’t blink – you might miss it.

Eat less. A lot less.

I know – it sounds ridiculous, but that wild technique helped me drop 40 pounds in under a year. I didn’t exercise one bit, either.

I know what you’re thinking – you’re thinking, “But Brent, how can I possibly lose weight without spending money? Don’t I have to spend a fortune on the FlabBlaster 3000 just like Chuck Norris tells me to?” Far be it from me to disagree with Chuck – very far – but…

When The Regular Thighmaster Isn't Working

Buying Hardware Doesn’t Fix Bad Practices

No matter how much you spend on exercise equipment or systems management, you and your servers aren’t going to get healthier when the stuff sits on the shelf.

Money can’t buy you health.

It can pay for experts to come in and fix you when you’re sick, but it can’t keep you healthy – that part is up to you. The very first step to getting healthier, and this goes for both your servers and your thighs, is to change your habits. Elbow grease has amazing results when applied liberally.

Start Testing Your Restores. Now.

If you don’t have a spare server lying around, use somebody’s spare desktop. We all have ancient machines sitting in closets from our last upgrade or that employee who just got fired because he couldn’t restore a dropped table. (Get it? That’s a hint.) Get that machine, and throw in a 1.5 terabyte drive for about $100. Yes, use your own money if the company won’t pay, because this is an investment in your career. If you’ve got several machines lying around, consider combining their memory if possible, but don’t sweat it – this is only your training wheels system.

Install the OS again from scratch, and put SQL Server Developer Edition on there or the 180-day evaluation version of SQL Server 2005 or 2008. Don’t overcomplicate your life by trying to get every best practice ideally perfect – even if everything’s installed on a 1.5tb C drive, this system will still work for the basics.

Start by testing your restores once per week. The first few times you do it, don’t try to script the whole thing out – just use SQL Server Management Studio and point/click your way through it. Remember, high bang, low buck/effort: we want this whole thing to take less than two hours per week of your time, max. The restores aren’t going to be fast, but the point is to even figure out if we can do them, period. After a couple of weeks, you’ll start scripting your work as you find more and more things that aren’t included in your test system – logins, DTS packages, jobs, whatever. Document what you’re doing along the way, because every time you find something else that has to be done to make the server work, that’s one less lesson you’d need to learn under the gun.

One Month Later: Add the Apps

After you’re comfortable restoring the database, try to configure your application. Install IIS, DLLs, code, whatever else you might need to get the app to run. If you don’t manage the app, ask the app guys to take another old desktop and try to do their part to set up a restore testbed for themselves. If they don’t want to, that’s okay – but now you’re starting to build up some cover for your rear end.

Some things might not work in your environment. For example, if you’re using the evil xp_cmdshell, your developers may have hard-coded paths and files into their code. The faster you find things like this, the faster you can get them fixed before disaster strikes. When disaster strikes, these problems won’t be seen as developer mistakes – you’ll get blamed, because you can’t make the server work the way it used to. 99% of your problems won’t stem from hardware that you can buy with a check, though – they’ll stem from practices. Stop waiting for the company to buy you a Thighmaster, and start doing pushups. It’s better than nothing, and when disaster strikes, the last thing you want to have is nothing.

When there’s enough basic plumbing in place that you think everything works, format the box and start over. Use your documentation and try to repeat the whole process. The first several times you do this, you’re going to continue to find more errors and gotchas.

When you think your documentation is complete, format the box and hand the documentation to your junior person or your manager. Say, “I’ve got a set of steps to follow when disaster strikes, and I want you to test them for me, because if I’m not around then you’ll be the one doing it.” They will be shocked, but down the road they’ll appreciate your due diligence.

This kind of disciplined effort is why experienced DBAs walk around with an air of confidence. The best DBAs aren’t worried about what happens when disaster strikes, because they’ve already practiced it again and again and again. When I was a DBA, I liked to say that disaster struck every week for me – it just struck in my lab.

Brent Ozar

Website - Twitter - Facebook - More Posts

7 Comments - Leave a comment!

My Weekly Bookmarks for October 30th

Written on October 30, 2009 by Brent Ozar in SQL Server

Here’s my bookmarked links for October 26th through October 30th:

SQL Server Links

Windows Azure Platform Launch Update – The blog notes that “a number of new features in Windows Azure will be made available for the first time” – but it’s at PDC, not the Professional Association for SQL Server Summit. This should be your clue that cloud-based SQL Server isn’t targeted at DBAs, folks – it’s targeted at developers.
Don’t be a DBA – Be a Data Professional – Buck Woody explains that your real job isn’t tied to the exact version/edition/brand you’re using – it’s tied to the value you provide.
SQL University: Diving into Authorization – Do you understand the differences between GRANT, REVOKE and DENY? K. Brian Kelley dives in for SQL University.
SQL Server with NetApp SAN – Collection of best practices documents from NetApp.
Checklist for installing SQL Server 2005 as a clustered instance – Your first cluster is painful – get help from Cindy Gross’s simplified checklist.
How People Abuse Their SQL Server Transaction Log – Things NOT to Do – A laundry list of SQL Server abuses.
Merrill Aldrich : Using Historical Perf Counter Data For Storage Planning – Dives into some of the cool metrics you can get from the Quest Spotlight repositories.
Suggested Max Memory Settings for SQL Server 2005/2008 – I like hard-coding the maximum memory size for SQL Server whenever it’s not being shared with multiple applications. Glenn shows you some guidelines for the right numbers.
SQL University: Authentication and Authorization – K. Brian Kelly talks about his specialty – making sure your data stays yours.
Amazon EC2 – Now an Even Better Value – Amazon’s prices are lower now for SQL Server in the cloud.
SSMS Tools Pack 1.7 is out! New feature: SQL Snippets – Great free tool.
Deterministic Functions – Steve Jones talks about how to determine whether something’s deterministic – it affects caching of execution plans, too.
Developing Data-tier Applications Using Visual Studio 2010 – Video showing off what’s coming for DBAs long term.
Templates for New Query Window – Buck Woody shows how to force yourself into commenting your code better.
High Availability Improvements in SharePoint 2010 – Woohoo! It’ll support SQL Server database mirroring out of the box.
Introducing Amazon RDS – The Amazon Relational Database Service – MySQL in the cloud, cheap.

#SQLPASS Links

Starting the Travel Tips Meme – Great tips from Kevin Kline for the upcoming PASS summit.
What Should PASS Strive To Be? – Jack Corbett’s thoughts on #SQLPASS’s directions.
A Rendezvous With Destiny: PASS 2009 (#sqlpass) – If you’re going to the PASS Summit, you need to watch Jimmy May speak.

Tech Links

Losing Net Neutrality: The Worst Case Scenario [Net Neutrality] – If you don’t understand the talk around “net neutrality”, this post will illustrate how the net will look like if we don’t have net neutrality.
VCE-101 Thin Provisioning Part 1 – The Basics – Thin provisioning involves creating storage arrays without actually having as much storage as you think. Doing it right requires some awareness at the OS level.
ScreenFlow 2.0: top Mac screen recording app gets even better – This is the tool I use for my podcasts and screencasts.
Motorola DROID review – Engadget’s review of the highly awaited Android headset. Comes out with more cons than I’d have expected.
Free Public Careers CVs – This is going to change the way geeks get hired.
Announcing Google Maps Navigation for Android 2.0 – Free turn-by-turn navigation for Android phones. Interesting because Google is basically competing against the iPhone on price now – rather than paying for turn-by-turn nav, you can get an Android phone and get that for free.
Dunbar’s Number isn’t just a number, it’s the law – Humans can’t handle having more than 150 friends.
Now Available: The Virtual Storage Console vSphere Plug-in – If you manage VMware and you have a NetApp SAN, this lets you get better insight into your virtual server storage from inside VMware tools.

The Junk Drawer

Newspaper circulation chart over the last 20 years – What a cliff. Scroll down.
I Got Nowhere To Go – Tom LaRock questions why people are starting up all kinds of new sites that do the same thing as ones that are already popular & successful.
Best Buy drops the price on the Netflix-streaming, Blu-ray playing Insignia Blu-ray player – $100 for a Blu-ray player is one heck of a breakthrough price.
Book Review: Twitter Wit – Tom LaRock reviews a book with tweets.
The Power of Being Personal on Your Blog – Wanna know why you should be personal? Ask a blogger who’s already been wildly successful.
Which Religion Should I Follow? – Flowchart to pick your Jeebus.
Caffeine & Edibles : Baconnaise – Man, if only there was some way I could get both bacon and mayo on my sandwich faster….ah-ha!

These bookmarks are automatically imported from my bookmarks at Delicious.com. If you’d like to get up-to-the-minute updates on what I’m bookmarking, you can subscribe to my bookmark RSS feed.

Brent Ozar

Website - Twitter - Facebook - More Posts

1 Comment - Leave a comment!

My Weekly Bookmarks for October 23rd

Written on October 23, 2009 by Brent Ozar in SQL Server

Here’s my bookmarked links for October 17th through October 23rd:

SQL Server Links

Series of “Bad habits to kick” by Aaron Bertrand – There were a ton of great tips in this series, and I’m glad to see it getting press.
Which Visual Studio 2010 is Team Suite? – Both Premium and Ultimate versions come with Data Dude features, and all versions come with Windows Azure access.
How to use SP_CONFIGURE in SQL Server – Great summary by Mike Walsh of this basic tool.
An Introduction to SQL Server System Databases – Good beginner/101 article for new database administrators.
TIP: How to Run Programs as a Domain User from a Non-domain Computer – How to run SQL Server Management Studio as a different Windows domain user. Awesome for consultants.
Meet the MCM’s and MCA’s! – Only 8 people outside of Microsoft have achieved the Microsoft Certified Master status in SQL Server. 8.
MSDN Premium Subscribers and BizSpark Members to Receive Windows Azure and SQL Azure Benefits – Woohoo! Developers will have access to Microsoft’s cloud services.
The patching game. – Test before you apply even the smallest patches to your SQL Server infrastructure.
SQL Server Reporting Services Disaster Recovery Case Study – I often hear from folks who want to build DRP solutions for SSRS. This post lays out how to do it.
Should I have a SNAC with my cumulative update? – When SQL Server patches and updates come out, we don’t always think to upgrade our clients, but there’s important fixes in there.
Virtualisation for the DBA part 3 – SQL Server Performance – Andrew Fryer explains why a lot of servers work just fine under virtualization, and how to make ‘em work even better with data compression.

PASS Links

What Direction Do You Choose? – Kevin Kline talks about how the PASS Nomination Committee works.
PASS Update #16 – Andy Warren talks about his vision for PASS in the future. Great ideas.
PASS Board Candidates – Andy Leonard explains why we should check managers for calluses.
PASS Summit – My List of Tips – Andy Warren’s recommendations for a good time in Seattle.
Transparency and PASS – Steve Jones talks about the nomination process.
PASS Update #17 – Andy Warren talks about the ruckus around the PASS elections.
Where Are My Cookies? – Tom keeps bringin’ the transparency to PASS.
Another Year, Another Missed PASS Summit – K. Brian Kelly won’t be making it to Seattle this year, but he has two really good excuses: twins on the way.

Tech Links

Windows 7 / Windows Server 2008 R2: Problem Steps Recorder – Windows 7 is awesome. Seriously, if I didn’t already have a Mac, and if I found a non-Apple laptop I liked enough, I might just roll with Windows 7. This is yet another example of why.
Reading gets personal with Popular items and Personalized ranking – I already found a couple of interesting new feeds with this new Google Reader feature.
Steve Ballmer’s Windows 7 pitch features MacBook Pro in the background – Yes, it does. Somebody’s gonna get fired.
Setting up Hyper-V with a UPS – Set up your hypervisor so that it gracefully shuts down the guests first when the power goes out.
Everyone Should Be Skeptical About Cloud Service Providers – Great post about problems with cloud providers and what questions you should ask.
USB PC Prankster: guaranteed to freak out, enrage your cubicle mates – Turn on caps lock or garble text on someone’s PC after a timer delay. Brilliant.

The Junk Drawer

Healthiness of food vs. cost of food – The less it costs, the worse it is.
How to Speed Read Like Theodore Roosevelt – does what it says on the box.
10/16/09 PHD comic: ‘Parental Expectations vs. Time’ – Oh, man, I’ve been on the receiving end of this phone call.

These bookmarks are automatically imported from my bookmarks at Delicious.com. If you’d like to get up-to-the-minute updates on what I’m bookmarking, you can subscribe to my bookmark RSS feed.

Brent Ozar

Website - Twitter - Facebook - More Posts

1 Comment - Leave a comment!

Bookmarks for September 25th

Written on September 25, 2009 by Brent Ozar in SQL Server

These are my recent favorite links:

Comic for September 22, 2009 – Why you can’t telecommute.
The Beginner’s Guide to Tricking Out Your WordPress Blog [WordPress] – Similar to my series on getting started with blogging, but updated with more goodness.
10 Best Things We’ll Say to Our Grandkids – With such gems as, “There used to be so much snow up here, you could strap a board to your feet and slide all the way down.”
Five Stars Dominate Ratings – The vast majority of video ratings on YouTube are five stars. Interesting questions about what it means for reviewers and data.
Our Look at The New Microsoft Office Web Apps – A screenshot tour of the web-based MS Office.
The Etymology of Consulting – Dilbert cartoon about what you get when you combine con and insult.
Step by Step Guide to Creating a SQL Server VM Using VMWare – And yes, you CAN virtualize SQL Server and still get good performance.
Lousy HA is Not Necessarily Better Than No HA – This is so freakin’ true. Adding clustering, for example, increases the number of things in your server that can go bump in the night. I’d go so far as to say that I’ve worked with more lousy clusters than good clusters.
Links for the Week 2009.09.20 – Jeremiah Peschka’s favorite reading for last week. Good stuff.
Awesome Blogs of the Month – SEP ‘09 – Rhonda is a ferocious reader. She always does a great job with linkposts, and now she’s cluing you in to some more good blogs.
The Value of a Mentor – The fastest way to get ahead is to get advice from somebody who’s already done it.
Updated SQL Blogger Rankings – Tom LaRock updates his list of top bloggers.
Identifying Frequently Running Queries – By SQL Server tuning guru Grant Fritchey.
The SQLBits Mug Shot Competition – SQLBits goes West – 19-21 Nov 2009 – Newport – Want a free registration for SQLBits in Great Britain? Just take a mug shot picture.
A Simple Active/Passive Configuration Change To Reduce Downtime – Make your blog posts more interesting by using the active voice. NOW, DAMMIT! (That’s me trying to be active.)
Estimated rows, actual rows and execution count – Another one of Gail Shaw’s excellent posts about query execution.
The Great Breakup – Removing application servers from SQL Servers.
ADFS Authentication Failure – If SQL fails when a domain controller is rebooted, you’ve got this issue.
On the search for the perfect OLAP browser – A comparison review of some of the big OLAP players.
DBCC CHECKFILEGROUP bug on SQL Server 2008 – if you’re using a table on multiple filegroups (like partitioning) and you’re running DBCC on just one filegroup at a time, it’s probably not working.

Unfortunately, there’s more, but the WordPress plugin I’m using will only import 15 bookmarks per hour. Grumble. To see the full list of what I’ve been reading lately, either check out my Delicious bookmarks or subscribe to my Google Reader feed.

Brent Ozar

Website - Twitter - Facebook - More Posts

0 Comments - Leave a comment!

My Weekly Bookmarks for September 17th

Written on September 17, 2009 by Brent Ozar in SQL Server

Posting this one a little early since I’ll be doing presentations all day tomorrow for a Quest Day with the Experts in Boston, MA. You can watch online too.

SQL Server & Tech Links

Query plans webcast next week with Kevin Kline – Simon Sabin is doing a free webcast with Kevin Kline on SQL Server query execution plans.
Dates can easily be the hardest datatype to work with. – Good post by Denny Cherry about indexing date fields.
Lessons Learned: When the Lights Go Out – Mike Hillwig shares notes after a disaster. Read stuff like this before it happens to you.
More Free Training – Grant Fritchey talks about the QuestConnect webcast next month.
Imitation Isn’t Always Flattery: Microsoft Previews Google Apps Killer To Beta Testers – Screenshots & explanations of the new online Microsoft Office.
Deriving a list of tables in dependency order – When you need to empty out all of your tables but they’ve got foreign keys, you have to delete them in order. Jamie’s script gives you that order. (This beats my method of scripting out “truncate tables” for every table in alphabetical order, and then repeatedly hitting F5 until they’re all empty.)

The Junk Drawer

Blur Tripod – iPhone tripod adapter and an app that has a built-in delay after you click to take a photo – that way the phone stops moving and the photo will be crisp.
Professional Development: Internet Image – When someone tells you that you should have a nice, clean, sanitized blog that’s free of any personal details, send them this blog by Jason Massie. I’m right there with him – I would rather see someone’s personality. People are likable – Books Online is not.
Trackin’ Away – Ping.fm now lets you track statistics on your broadcasted links. Yet another reason to use the PingPressFM plugin for WordPress.
Training Benefits – When you stop training, your career comes to a grinding halt.
Your Own Personal Development Plan – End-of-year reviews are coming up – time to start working on your Personal Development Plan.
Recording a webcast for Quest Connect 2009 – Colin Stasiuk talks about the upcoming free QuestConnect webcast.
Your company? There’s an app for that. – Many companies are going to be competing with dirt-cheap iPhone applications sooner or later.

These bookmarks are automatically imported from my bookmarks at Delicious.com. If you’d like to get up-to-the-minute updates on what I’m bookmarking, you can subscribe to my bookmark RSS feed.

Brent Ozar

Website - Twitter - Facebook - More Posts

0 Comments - Leave a comment!

Getting Started with Amazon EC2 SQL Server 2005

Written on October 27, 2008 by Brent Ozar in Cloud Computing, SQL Server

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:

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.

Brent Ozar

Website - Twitter - Facebook - More Posts

29 Comments - Leave a comment!