Is Your Code an English Garden or Ikebana?
Erika loves having fresh flowers around the house. Every Saturday morning, I pick up a bouquet at a farmer’s market or grocery store and put it in a vase for her. I’m slowly upping my game by learning more and more about the art of arranging flowers.
When I say flowers, I bet you think about the English Garden style: a big, complex vase with all kinds of flowers crammed into every nook and cranny. It’s an explosion of color and life.
Photo Source: Conveyor Belt Sushi
That’s way too stuffy for us. We’re into minimalism, clean lines, and letting materials speak for themselves. I like plucking one or two of the more beautiful or unusual flowers and putting them in their own vase. This leans toward the Ikebana style of Japanese flower arrangement, specifically the Nageire type. (I don’t even want to think about how badly I’m going to mispronounce these if I ever have to say them out loud.)
Writing database code is like arranging flowers.
If you show someone your bouquet, they might not like it. They might give you a million reasons about why it’s not right or why another way is better. That’s not the point – does it produce the results you want?
If your goal is to get to market quickly and cheaply, just buy a premade bouquet from the grocery store, throw the flowers in the vase and be done with it. Use LINQ, Entity Framework, NHibernate, or whatever code tools make your job easy.
If you translate your app code into SQL code, you’re building an English Garden. You start by declaring variables at the center, then populating those variables by checking configuration tables, then spin out to more and more other tables, getting your results in loops and setting values one at a time. This is exactly how developers have always been taught to arrange their flowers, and it works just fine. Once you’re used to doing it, you can bang that code out quickly, and the results are attractive.
But if you need it to be beautifully fast, you need Ikebana. You need very clean, very minimalist code that gets the job done in as few statements as possible. In a database environment, this means set-based code that avoids cursors and loops.
While clean, Ikebana-style database code is simple to behold, it’s deceivingly complex to build. The first step is moving as much logic as possible from the database server to the application server – starting with the ORDER BY statement. If you’re not fetching just the TOP X rows, then do all sorting in the application server. Removing just that one line from a query will often cut its cost dramatically. Your development platform (.NET, Java, Cobol, whatever you kids are using these days) is really good at scaling out CPU and memory-intensive work like sorting, and you’re already really good at splitting out your work into multiple application servers. Leverage that capability.
Think of it like pruning your code – remove all the things that database servers don’t do beautifully, and what you’re left with will be gorgeous.
How to Manage Vendor Databases [Video]
Are you frustrated by third party applications that you can’t change, but you have to support? Tired of beating your head against the wall when your users complain about things you can’t fix? In this 30-minute session, Brent Ozar will show you his favorite tricks to get the most performance without losing support. He’ll show you how to interact with vendors and get what you want – without getting heartburn:
Like that video? We’ve got half a dozen more scheduled for upcoming Tuesday lunches. Click the boxes you want and sign up for free.
SQL Intersection Registration Open – and a $100 Off Code!
Today is your lucky day.
How would you like to go to a SQL Server conference in Las Vegas where the sessions are taught by Brent Ozar Unlimited, SQLskills, SQLServerCentral, and SQL Sentry?
Yep. Me, Jeremiah, Kendra, Kimberly Tripp, Paul Randal, Jonathan Kehayias, Erin Stellato, Steve Jones, and Aaron Bertrand. Between us, that’s 3 MCMs, 2 MCM instructors, 7 MVPs, and 2 MVP Regional Directors.
If you’re serious about learning SQL Server, this should be the very first conference on your fall priority list. Check out some of these sessions:
- Troubleshooting SQL Servers in VMware and SANs (me)
- Understanding Locking, Blocking, and Isolation Levels (Kimberly)
- Understanding Logging and Recovery (Paul)
- X-Ray Glasses for Your Indexes (Kendra)
- Branding Yourself for a Dream Job (Steve)
- Deadlocking for Mere Mortals (Jonathan)
- Hadoop: The Great and Powerful (Jeremiah)
- Making the Leap from Profiler to Extended Events (Erin)
Team building in a Boxster through canyons
How much would you pay for three days of awesome learning at a conference like this with top-notch speakers, all killer no filler?
You want more sessions? You’re in luck! Your registration also includes ASP.NET Intersection sessions and Visual Studio Intersection sessions for developers, SharePoint Intersection sessions for sharing pointers. If your coworkers want to attend an open-source-friendly conference focusing on JavaScript and the web, the Angle Brackets conference is happening in the same hotel at the same time, so it makes for a great company getaway.
And hey, it’s Vegas, so it’s a great team building city, like when Jeremiah and I rented cars last time and, uh, built teams. Yeah.
But wait – there’s more! Check out the pre-con workshops:
- Accidental DBA Starter Kit (me, Jeremiah, Kendra – Pre-Con Sunday) - You’re responsible for managing SQL Servers, but you’ve never had formal training. You’re not entirely sure what’s going on inside this black box, and you need a fast education on how SQL Server works. In one day, you’ll learn how to make your SQL Server faster and more reliable. You’ll leave armed with free scripts to help you find health problems and bottlenecks, a digital set of posters that explains how SQL Server works, and an e-book that will keep your lessons moving forward over the next 6-12 months.
- Queries Gone Wild: Real-World Solutions (Kimberly – Pre-Con Sunday) - Have you ever wondered why SQL Server did what it did to process your query? Have you wondered if it could have done better? And, if so, how? Transact-SQL was designed to be a declarative language that details what data you need, but without any information about how SQL Server should go about getting it. Join order, predicate analysis – how does SQL Server decide the order or when to evaluate a predicate? Most of the time SQL Server gets the data quickly but sometimes what SQL Server does just doesn’t seem to make sense. Inevitably you’ll encounter certain workloads and queries that just aren’t performing as well as you expect. There are numerous reasons why query performance can suffer and in this full-day workshop Kimberly will cover a number of critical areas while showing you how to analyze a variety of query plans throughout the day.
- Scale Up or Scale Out: When NOLOCK Isn’t Enough (me, Jeremiah, Kendra – Post-Con Thursday) - Partitioning, replication, caching, sharding, AlwaysOn Availability Groups, Enterprise Edition, bigger boxes, or good old NOLOCK? You need to handle more data and deliver faster queries, but the options are confusing. In this full-day workshop, Brent, Kendra, and Jeremiah will share the techniques they use to speed up SQL Server environments both by scaling up and scaling out. We’ll share what features might save you hundreds of development hours, what features have been a struggle to implement, and how you can tell the difference. This workshop is for developers and DBAs who need to plan long term changes to their environment.
- Practical Disaster Recovery Techniques (Paul – Post-Con Thursday) - Disasters happen – plain and simple. When disaster strikes a database you’re responsible for, can you recover within the down-time and/or data-loss limits your company requires? What if your plan doesn’t work? This workshop isn’t about how to achieve high-availability, it’s about how to prevent or overcome the obstacles you’re likely to hit when trying to recover from a disaster – such as not having the right backups, not having valid backups, or not having any backups! In this demo-heavy workshop, you’ll learn a ton of practical tips, tricks, and techniques learned from 15 years of experience helping customers plan for and recover from disasters, including less frequently seen problems and more advanced techniques. All attendees will also receive a set of lab scenarios for further study and practice after the class with assistance from Paul.
Ernie takes in Hoover Dam
Now how much would you pay for all this? Three thousand? Four thousand? Ten thousand? BUT WAIT, THERE’S MORE!
For $1,894 before June 24th, you can get the Show Package: the conference, PLUS a pre-con or post-con of your choice, PLUS your choice of a Surface RT, Xbox, or a $300 gift card.
For $2,294, you get all that plus ANOTHER pre-con or post-con – five days of nonstop learning from the absolute best in the business.
No? You want more? Okay, you drive a hard bargain, buddy. Use discount code OZAR and you get another $100 off. Register now. Operators are standing by.
51 Questions About Your #SQLPASS Summit Submission
Jeremiah and I presenting in Atlanta
This weekend, emails went out to folks who’d submitted their sessions for the PASS Summit 2013 in Charlotte.
If you’re bummed, listen up. I know what it feels like to get turned down because I got turned down the first couple of times I submitted, too. The blessing and the curse of the SQL Server community is that there’s so many people who want to help others – but of course this makes it harder to get your place up on the stage. It’s only going to get worse/better as more people continue to discover the community.
Whether you got a good email or a bad one, your work is just beginning. Either you’re prepping for this October, or you need to start prepping for the next conference. In either case, here’s 51 questions you need to ask yourself about your abstract, your material, and your delivery.
- What pain is bringing the attendee to this session?
- How are they going to relieve that pain when they get back to the office?
- What does the attendee know already coming in?
- Who should not attend this session?
- Reading your abstract, are the answers to the above four questions crystal clear?
- What did you learn from Adam Machanic’s post Capturing Attention?
- Did your abstract take one thing off before it left the house?
- If you search the web for your abstract title, what comes up?
- Who else do you expect will submit on a similar topic?
- How will you show your own personality and expertise in the abstract?
- Of ProBlogger’s 52 Types of Blog Posts, which one matches your planned sessions?
- What other types of sessions from that list could you use to surprise and delight attendees?
- Are you teaching why or how?
- How would a handout make it easier for attendees to learn your lessons?
- What visualization would bring your session to life?
- Could you contract out a local design student or company to build it for you?
- Are you presenting to teach or to impress?
- Have you gotten feedback on your abstract from a proven speaker you trust?
- If a teacher graded your presentation, would you get an A?
- On that 24-point scale, what would it take to succeed at a national conference?
- What topics are you going to avoid entirely in order to save time?
- How often have you rehearsed this presentation before giving it to a local user group?
- Have you given this presentation before at local user groups and SQLSaturdays?
- Did you record the session (either video or audio)?
- Did you watch the recording to see where you can improve the material and your delivery?
- What questions did the attendees ask at those sessions?
- What feedback did the attendees give at the user group or SQLSaturday?
- How will you use that feedback to improve your session?
- If you gave attendees a test at the end of your session, what questions would be on it?
- If your session was a movie, what genre would it be?
- What other movies would be sitting next to it in the store?
- Who would play the leading role?
- What are three words you want attendees to use to describe your session?
- How do your abstract, material, and delivery inspire those three words?
- Have you clearly attributed ownership to the code and pictures in your session?
- If nobody asks any questions at all, will you still be able to fill the time slot?
- If you get many questions, which slides/sections can you skip without losing meaning?
- Where will you post all of the resources for your session?
- If people have a question while reading those resources, how will they contact you?
- If this session was a module in an all-day training class, what would the other modules be?
- What’s the worst thing that could happen in your session?
- How will you recover if that thing happens?
- Can you form an instant community of your attendees using a Twitter hash tag or chat room?
- What would your session look like with no demos whatsoever?
- What would your session look like as 100% demos and no slides?
- If you started the session with a question, what would that question be?
- What’s the easiest, simplest way for the attendee to learn the lessons?
- Could you get the presentation’s learning lessons across with a blog post or series?
- When you ask people why they linked to your post, what do they say they found compelling?
- What questions did readers ask in the comments?
- What’s stopping you from writing that blog post right now to gauge reader interest?
No, really. What’s stopping you? Don’t think for one moment that attendees will skip your session because they’ve read your work. It’s the exact opposite: readers come to your session because they like your work. Whether PASS told you yes or no, start writing your blog posts right now to find out what works and what doesn’t.
SQL Server Virtualization Q&A Session [Video]
The PASS Virtualization Virtual Chapter hosted a Q&A session with me last week. We talked about storage configuation options like VMDK/VHD vs RDM, how licensing works, what’s the biggest SQL Server I’m comfortable virtualizing, and much more:
For more tips, check out our virtualization resources page.
1TB Databases for Developers [Video]
If you write code that accesses a database one terabyte or larger, you need to know that things are different around here. When you hit the very large database (VLDB) territory, you need to pay particular attention to statistics, TempDB, and staging tables. Microsoft Certified Master Brent Ozar will share his favorite lessons for developers who work with either OLTP or data warehouses in this 25-minute webcast:
The links discussed in the webcast include:
SQL Server 2012 Query Performance Tuning by Grant Fritchey – we talked about the statistics analysis chapter as an example of what matters much more in the terabyte territory.
SQL Server 2012 Internals and Troubleshooting – the storage chapter helps you pick the right storage for your TempDB, which matters much more in VLDBs.
Download SQL Server 2012 Management Studio – free download for all Microsoft SQL Server users. I’d recommend using this even if you’re not using SQL Server 2012 on the back end – it’s a better development environment. Bonus – check out Jes’s SSMS tips and tricks video.
sp_Blitz™ v22: Output Results to a Table, Better Comments, and an App
Our free server troubleshooting tool, sp_Blitz™, just learned some new tricks. If you call it like this:
EXEC dbo.sp_Blitz
@OutputDatabaseName = ‘DBAtools’,
@OutputSchemaName = ‘dbo’,
@OutputTableName = ‘BlitzResults’
It will create the table DBAtools.dbo.BlitzResults (if it doesn’t already exist) and insert the results. The table includes columns for the server name and the check date, so you can query this table to see what’s changed since the last time you ran sp_Blitz™. Plus, there’s more:
Added new checks for disabled CPU schedulers (due to licensing or affinity masking), extended stored procedures, disabled remote access to the DAC, databases in unusual states like suspect or emergency, logins with CONTROL SERVER permission.
Easier readability – whenever anyone’s asked how to add checks, I’ve always just said, “Read the code.” Last week, I read the code with fresh eyes and realized it could use some cleanup and comments. I used Red Gate SQL Prompt to clean up the formatting, which is better but could still use some work. The code is nearing 4,000 lines, so I moved the old changes out of the proc and into a new online changelog. I put in some time to explain what the proc is doing, and that should help people reading it from the first time. I’ve also started an sp_Blitz™ Documentation page where I’ll be explaining some of the more advanced uses.
New downloadable sp_Blitz™ app for Windows - over and over, we heard from people that they wanted a quick way to generate a print-friendly copy of the sp_Blitz™ results. Jeremiah worked with a developer and built our first app:
sp_Blitz™ Windows App
The app creates sp_Blitz™ in TempDB on the server of your choice, executes it, shows you the results, and can output the results to PDF – complete with links to the explanations for each result.
We’ve got lots of ideas for the app, but we wanted to get it out there first and let you help drive it. If there’s something you’d like to see added or changed, leave a comment here or contact us. The whole point of this thing is to make your life easier. I wish this tool would have been available back when I was a production DBA struggling to figure out what was going on in my environment, so every little improvement helps. Hopefully this can save you from the stress I had back then. Get it from the download page and enjoy!
Write a Note and Shoot Yourself Today
When I’m writing a presentation or blog post, I often start here:
2004 Brent
It’s a photo of me in my office in Dallas, Texas in 2004. When I look at that picture, I remember everything like it was yesterday. I can talk at length about everything on the bookshelf, on my desk, in my drawers (the desk drawers, that is).
I can tell you what technology problems I was struggling with, plus what problems my manager was concerned about. I remember what I knew, and what I didn’t know yet. I can recite the web sites I frequented.
Next, I can turn the mental camera around and see exactly what’s outside my office door: my developers and my support team. I can tell you what they rocked at and what they wanted training on. I can remember how we decorated their cubes for their birthdays – covering Julian’s stuff in aluminum foil, building a princess’ castle for Hima.
The funniest thing, though, is that I didn’t remember any of this until I rediscovered this photo several years ago. All of a sudden, everything was clear to me.
And I realized who I was writing for.
Now, it’s really easy for me to scope my presentations and blog posts because I’m writing for 2004 Brent. 2004 Brent hadn’t studied databases and tried to turn them inside out – he just needed to store data and get it back out quickly. He wasn’t on a first name basis with book authors and MVPs – he didn’t even know what an MVP was.
You need to take this picture today.
Set up your camera with a self-timer or get a colleague to shoot a few pictures of yourself sitting in your work environment. Get pictures of the books on your shelf, the stuff on your desk, and maybe take a screenshot of your task list. Write yourself a one-page note covering:
- The stuff you’re comfortable with
- The stuff you’re uncomfortable with
- The things you want to learn this year
- The things you learned recently that surprised you
Stash these pictures and words away in a time capsule folder somewhere. A few years from now, when you’re writing a presentation covering something you’ve learned, get these back out. Think about what you knew and didn’t know, and that’s your target audience. Before you use a term or acronym, think back and ask, “Did 2013 Me know that? If not, lemme introduce the topic.”
When you’re writing, remember that you’re never writing for your current self. You’re writing for the past version of you. Having these pictures and words will help you define your audience.
Answers to Common High Availability and Disaster Recovery Questions
In our recent webcast on HA & DR basics for DBAs, we got a bunch of questions that couldn’t be answered fast enough. Here you go:
Q: Do you have book recommendations for replication, mirroring, and the other topics?
Absolutely. Check out the resources on our HA & DR Basics video page.
Q: Do any of these techniques work for companies with just 1-2 IT staff?
To keep things simple, I’d check out virtualization replication like VMware SRM. SRM isn’t necessarily easy, but if you use that one technique, you can protect all of your virtual machines no matter what they’re running. That’s easier than learning different HA/DR techniques for a lot of different applications.
Q: For 1,000 databases on an instance, what uses more resources – mirroring or log shipping?
Technically, the answer is going to be log shipping because you’re probably already doing transaction log backups on those databases anyway. However, when you’re running thousands of databases per instance, several other interesting issues arise. How do you react to newly added databases? Can you back up all 1,000 in a timely manner, or do you need to roll your own transaction log backup jobs to run simultaneously? What’s your RPO/RTO? There’s a lot of neat questions that determine the real answer at this scale. Feel free to contact us for consulting help – we’ve got clients who run as many as 10,000 databases per instance, and we can help you learn lessons the easy way instead of the hard way.
Q: What happens if my DR is in a different datacenter, on another domain?
Your life is harder. Many built-in solutions become more challenging, and even just getting your users to be able to connect to the database become more challenging. This is a scenario where I like to step back and ask, “What problem are we trying to solve by using different domains?”
Q: Do my HA and DR servers have to be on the same subnet or VLAN?
No, all of SQL Server’s HA/DR features work on different subnets and VLANs.
Q: Do you have to kill all SQL Server connections before restoring a database?
Yes. Restoring a database – even a similar copy – means starting over with all-new data. Restoring transaction logs means SQL Server will be jumping around all over inside the database, and it doesn’t have time to deal with your queries. 
Q: Does clustering work for SSAS, SSIS, and SSRS?
Microsoft sells these things inside the same box as SQL Server, but for all practical purposes, you should consider them different products. They’re like free toys that just come in the same box, but they’re wildly different. The techniques you use to protect the SQL Server engine won’t be the same as the techniques you use for the rest.
Q: Can you read a database mirror?
Not directly, but you can take a snapshot of it and read the snapshot. I rarely see this used, though, because in order to refresh the snapshot, you have to drop it – and that means kicking everybody out of the snapshot. Users don’t seem to be a big fan of getting their queries killed.
Q: What Windows Server edition is needed for clustering and AlwaysOn Availability Groups?
You can use any version of Windows Server 2012, but if you’re still using Windows Server 2008R2, you’ll need Enterprise Edition.
Q: How do you get hosts in different data centers into the same cluster?
Clusters don’t need shared storage anymore. This is why I wrote the post Everything You Know About Clustering Is Wrong. Things really have changed a lot in the last several years around clustering.
Q: How well do these features work over a slow connection?
If you’ve got enough bandwidth to keep up with copying transaction log backups as they’re happening, you can stay up to date. If you can’t, they don’t work well.
Q: Can you install SQL Server Enterprise on Windows Standard?
Yes.
Q: I just joined the webcast and it’s almost over. Can you send me the slides?
You’d be surprised how often I get this question. This is why we make our material available for free on our YouTube channel. I don’t usually share the slide decks, though, because I’ve had some nasty incidents of people taking my slide decks, plagiarizing them, and presenting them as their own material.
Q: I’m using an AlwaysOn Availability Group. If I fail over to an asynchronous replica, will there be data loss?
Probably. If your primary has been doing a lot of work, and hasn’t been able to send all of that work to the asynchronous secondary, you’re doing to lose that data. It’s up to you to monitor the DMVs to see how far behind the replicas are.
Q: Is (feature A) better than (feature B)?
Yes.
Q: Really?
No. They both suck.
Q: Seriously, I need an answer.
Getting to the right answer means knowing your RPO, RTO, business needs, staff capabilities, hardware, network throughput, and more. If you ask a stranger to guess the right answer for you with just five minutes of background information, odds are it’s going to be the wrong answer. You’re going to have to buckle down and do some research into the features and your business needs, or you can bring in an experienced outsider who does this for a living. This is what we do, and we can help you get to the right answer as quickly as possible, and probably for less money than you’d expect.
Q: Is there a way of automating failovers for SQL Agent jobs for HA purposes?
If you’re using clustering, then this is built in. If you’re using a solution that does failovers at the user database level, then things get trickier because you have to know which jobs you want to fail over. For example, you probably don’t want backup jobs failing over (because you can just run those jobs on all nodes), but you might want a few database-based jobs failing over. That’s left as an exercise for the reader, though, because every shop’s needs are different – often even different between database servers in the same shop.
Q: What’s the right quorum configuration for my cluster?
Check out the excellent Books Online quorum sections for Windows Server 2012 and Windows Server 2008/R2. It’s a lot of reading, but it’s worth it – the quorum decision is a big one.
Q: Can I integrate clustering with (mirroring/log shipping/replication/AlwaysOn AGs)?
Clustering is probably the strongest feature in terms of interoperability with other features. It’s been around a long time, so it’s got a good interoperability story for pretty much everything.
Q: How can one second RTO be achieved in a DR scenario?
By having both the production and disaster recovery systems live at all times, accepting queries. At that point, it’s up to the application logic to try both servers to see which one is available fastest. Some applications do this by timing out their database connections after just one second. Other applications do it by trying both database servers simultaneously and taking the first available connection.
Q: Can you back up a SQL Server to SQL Azure?
Not in the sense that you can restore directly into SQL Azure (or as it’s known now, Windows Azure SQL Database.) However, you can use Windows Azure as a backup solution if you’re so inclined.
Q: Azure is a French word meaning blue. Americans are not pronouncing French correctly.
French is the most beautiful language in the world – when it’s spoken by the French. When the rest of us try it, we sound like we’re coughing up an unfiltered Marlboro. Thank you for your cheese, wine, and wonderful words like lingerie and ménage à trois. You are a beautiful country, and the rest of us apologize for the sounds we make when confronted with French.
Q: Can you cluster inside VMware?
Yes, but if you’re asking the question, you shouldn’t be doing it.
Q: What are the best practice recommendations for achieving 99.999% uptime?
Obviously this is way beyond what I can answer quickly, but the best advice I can give is to remember that there’s three parts to every solution: people, process, and technology. You need written processes that cover exactly how to react to an outage, and you need to rehearse and improve those processes constantly. Car racing teams practice over and over to get the fastest pit stop possible so that their race car spends more time driving and less time sitting. DBAs need to do the same.
Q: What HA option is Brent’s personal favorite, and why?
I like failover clustering because it protects everything in the instance – jobs, logins, and the server name itself – with as little manual intervention as possible. It still has weaknesses in a few single points of failure, and it’s not completely easy to implement and manage. I think the benefits still outweigh the costs.
Cloud Alternatives to Microsoft SQL Server
When people say “cloud”, they’re simplifying a lot of different solutions into a single catchphrase. Let’s break out the different options and compare them.
1. SQL Server in Amazon EC2 and Azure VMs
Amazon EC2 is a virtualization platform. Amazon buys servers, installs their secret sauce software, and rents you Windows virtual machines by the hour. Microsoft offers a similar product, Windows Azure Virtual Machines, that just went officially live.
You can rent a blank Windows VM without SQL Server installed, and then install SQL Server yourself just like you would on-premise. That’s a licensing mess, though – you have to use your existing SQL Server licenses or buy new ones for your VMs. That doesn’t make much financial sense. Instead, Amazon and Microsoft will rent you a Windows VM with SQL Server already configured, and your hourly fee includes the SQL Server licensing.
SQL Server runs just as it would in your own datacenter, which means you can use this as a disaster recovery option for your on-premise SQL Servers. You can do log shipping or database mirroring up to SQL in the cloud, running in Amazon EC2 or Microsoft Azure. When disaster strikes, fail over to your EC2/MS infrastructure, and you’re off and running.
The term “cloud” conjures up images of easy scalability and redundancy, but that’s not really the case here. We’re talking about a single virtual machine. This isn’t much different from running SQL Server in a VMware or Hyper-V guest in your own datacenter or in a colo datacenter. You can use all your traditional tools and techniques to manage SQL Server, which is both a pro and a con. If you need to patch it or scale out to multiple servers, there’s no tools included here. I still consider this the cloud, though, because the infrastructure and licensing are managed by somebody else. It’s easy to get started with one – or a hundred – virtual SQL Servers with no initial investment.
This method is the most conventional, and as I explain other options, I’m going to move from conventional to really-far-out-there. SQL in EC2 or MS VMs just works – it’s easy to understand and leverage without changing your code or your techniques – but it doesn’t bring a lot of the cloud’s benefits.
2. Amazon RDS for SQL Server
Instead of running SQL Server in an EC2 virtual machine, let’s start giving up a little bit of control in order to get more of cloud benefits. The next layer of clouds is Amazon Relational Database Service (RDS). Here, Amazon builds a Windows VM, installs SQL Server, configures it, and manages both Windows and the SQL Server service for you.
This is still the real, true blue SQL Server you know and love – all of your commands still work exactly the same as you’re used to, as long as you don’t try to access the server’s local drives directly. (Example: you can’t upload a flat file to the server’s C drive and then try to BCP data from that file into SQL Server.)
Amazon RDS is kinda like they’re the DBA, and you’re a very, very powerful developer. You can create and drop databases and users, but you can’t Remote Desktop into the SQL Server, nor can you access the drives.
Amazon RDS takes a few job duties away from you:
- Storage management – want faster storage? Just pick (and pay for) more IOPs. There’s no arguing with the SAN guy.
- Monitoring – Amazon CloudWatch tracks performance metrics and sends you emails when they’re outside of your thresholds.
- Patching – You pick the major/minor versions you want and when you want patches applied.
- Backups – You pick a time window for the full backups, and Amazon manages it using storage snapshots. You can restore from snapshots just by pointing and clicking in the management console.
But herein lies the first real compromise: you can’t restore from anything except snapshots. You can’t upload your own database backup file and restore it. To get data into Amazon RDS, you’ll want to export it to a file, upload that file to Amazon S3 (cloud-based file storage), and then import it. This also means you can’t use Amazon RDS as a participant in log shipping or database mirroring.
Microsoft doesn’t have a competitor to Amazon RDS for SQL Server today. Well, I say that, but some shops already manage their SQL Servers this way – they have an internal admin team that manages Windows and SQL. Departments get access to create & drop databases, change code, etc, but they don’t get access to the server’s desktop or backups. This doesn’t really compete with Amazon RDS, though – RDS is for companies who are too small to have this kind of internal engineering infrastructure. (Or for companies that want to get rid of this large engineering burden, I suppose.)
3. VMware vFabric Data Director
vFabric Data Director is a lot like running Amazon RDS in your own datacenter, but you can control the base Windows virtual machines. You build a Windows template to be used by default whenever a new SQL Server is created. VMware vFabric manages the implementation details for backups and high availability.
vFabric also supports Oracle, PostgreSQL, and Hadoop for a single pane of glass to create and manage your database servers. When someone in your company wants a new database instance, the sysadmins open up vFabric, configure it, and within a few minutes, it’s up and running.
vFabric makes sense for 100% virtualization shops who aren’t interested in moving their databases up to the cloud, but they want easier database management integrated into their virtualization tools.
Microsoft doesn’t have a competitor to VMware vFabric Data Director today. In theory, you could build your own alternative using System Center and a whole lotta scripting. That is left as an exercise for the reader.
4. Microsoft Windows Azure SQL Database (WASD)
The artist formerly known as SQL Azure takes the least conventional approach of all. While it’s technically built on Microsoft SQL Server, that’s like saying your pants are built on your underpants. They both cover your back end, but you can’t use them interchangeably.
Microsoft essentially built a new product designed for common database storage requirements. Like your underpants, you only get a minimum of feature and command coverage here. The new features and commands Microsoft has been adding to the boxed product for the last couple of versions just aren’t available in WASD including partitioning, Resource Governor, Service Broker, and CLR. But hey – are you really using those anyway? Most folks aren’t.
Rather than building large databases, WASD encourages developers to shard out their data across multiple smaller databases. While on-premise SQL Servers have had techniques to do this in the past, Microsoft started over and developed a new technique that makes more sense for cloud implementations. Again, though – we’re talking about a difference from the boxed product, something else that developers have to learn differently. As we’ve gone up this ladder into the clouds, we’ve been handling our problems differently. WASD’s partitioning technique is a good reminder that once you’ve gone this far up into the cloud, you’re dealing with something very different from SQL Server. You’re not going to take large volumes of code written for SQL Server and simply point them at WASD – you’re going to be doing a lot of testing and code changes.
Like Amazon RDS, there’s no backup/restore functionality here to get your existing data into the cloud. You’ll be exporting your data to a file, loading it into the cloud, and then…staying there. There’s no database mirroring or log shipping to/from Azure SQL Databases to on-premise SQL Servers.
Which One is Right for You?
If you’ve got an existing app, and you just want to cut costs without changing code, you can get started today with any of the first three options. They support the same T-SQL commands and datatypes you already know and love.
If you’ve got an existing app, and you’re willing to make code changes & do testing, you might be able to save even more plus gain new flexibility by going to Windows Azure SQL Database. In most cases, though, the cost savings won’t come anywhere near the costs required for the code changes and code review. We haven’t seen a case yet where the tradeoff made sense for our clients. I’m sure it’s out there – the perfect example would be a small amount of code that the developers know very well, can easily modify, and produces large server loads in short bursts.
If you’re building a new app from scratch, then let’s take a step back and survey the database industry as a whole. This is an incredible time to be in databases and there’s a bunch of really good options we didn’t even cover here.
