When I want to check out a piece of software, one of the first things I look for (after the price tag) is the support matrix.  I want to know what versions of SQL Server and Windows they support.  I lined that up with the available machines I had in-house, picked the right platform, and got started.

Years ago, I noticed that when other users wanted to bring a new piece of software in-house, they brought me the product’s support matrix and asked what versions & servers we had in-house.  Some departments (like the mainframe guys) got so many requests that they published their own standards document showing what they supported.  When a manager insisted on putting certain kinds of software onto specific servers, the mainframe guys could point to their support matrix and say, “Sorry, that doesn’t match our standards.  It needs to go on this other server over here.”  Bam, discussion over.

Wow – why wasn’t I doing that?

I decided to build my own support matrix and standards document for our SQL Servers.  I broke the servers out into categories: Development, QA/Testing, Production, and Mission-Critical Production.  I came up with standard descriptions for each category that covered our HA/DR levels, our on-call availability, and security.  Here’s a screenshot:

My SQL Server Support Matrix

Kapow, I laid down the law.  For example, I would not be on call if the dev server crashed at 9pm when one lonely developer was working late – he could open a support ticket, and I’d address the issue in the morning.  Note that this didn’t mean I wouldn’t fix the dev server after hours; I still set up SMS alerts to notify the team whenever ANY server was experiencing problems, and I wanted to get them fixed before the developers came in the next day.  However, with this support matrix, I was setting up reasonable expectations across the staff so that we knew what was an emergency, and what wasn’t.  If someone needed a database to be available 24/7, then that fell into the production category, not development.

When I built this document, I didn’t show it to my existing customers.  (Yes, I really think of my users and developers as my customers, not my coworkers.)  The goal of the document wasn’t to change expectations for our existing databases, but to shape expectations for any new databases and applications that came on board.  When a project manager wanted to bring in a nasty, poorly-written app that required SA permissions and 24/7 uptime, my support matrix backed me up.  I simply said, “No, that’s not in our support matrix,” and handed them the document.

If the manager has dealt with support matrixes before, they’ll recognize that these documents are usually built over time with a great deal of testing time and political wrangling.  Your document wasn’t – but they don’t know that.  They don’t know your document was only crafted with your finely honed intellect.  They’ll assume the support matrix is not negotiable, because the vendor’s got a support matrix too, and it’s not negotiable either.

The project manager will take the vendor’s support matrix, set it side by side with your support matrix, and look for a way to make this work.  They want the path of least resistance.  Your support matrix needs to give them an easy way to get their app in the house in the way YOU want to support it.  In my support matrix, I noted that with asterisks – if someone really wanted to get remote desktop access or SA access to their server, they could have it – as long as we could install them in a dedicated VM.

“No one will take me seriously.”

You think you’re powerless against your customers, and you’re right.  The project manager can pull rank over you and tell your manager to shut up and start installing.  For years, they’ve been kicking sand in your face, but I’m going to introduce you to your enforcers. Your existing customers will play the bad cop. Watch how this works:

Ned the New Guy: “Here’s an installation script for a new database we need.  After you put it on the mission-critical production cluster, let me know what the SA password is.”

Me: “Sorry, apps on the production cluster don’t get the SA password.  Here’s our support matrix.”

Ned the New Guy: “I’ll show you where to put that support matrix.  Don’t make me ask your manager.”

Me: “Well, if I give you SA permissions on that cluster, I’m also giving you SA permissions on our SalesApp database – the system that all our revenue comes through.  I’m not comfortable doing that without Bob’s okay.  He’s the SalesApp project manager.  Hey, Bob – would it be okay if I gave someone else permission to truncate your tables, drop your databases, and shut down your servers?”

Bob the Bad Cop: “Hell no, Ned, you’re not getting that.  Go get your own server.  Don’t make me get the CFO.”

Presto – you’re not the bad guy, and you’ve got someone with much more power in your corner.  Get started today by taking my sample SQL Server Support Matrix and making it your own.

If you liked this post, check out my Consulting Lines series here.

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.

More Posts - Website

Follow Me:

In part 1 of this post, I covered how SQL Server handles scaling up.  We talked about how quickly it becomes expensive to add more CPU power, memory, and storage throughput to our database servers.  Today, we’re going to focus on a different way to scale.

When most folks talk about scaling out SQL Server, they’re adding more SQL Servers to the infrastructure and dividing the work between them.  I want you to take a bigger step back, though, and ask the question, “What are the different things we’re asking SQL Server to do?”  The answer isn’t just storing data, either:

1. We insert data – and not just data, but different kinds of data
2. We update data
3. We delete data
4. We do some processing of that data
5. We retrieve the data – and we want to include the different ways we retrieve it, and with which tools
6. We check the data to make sure it’s still correct
7. We back up the data – or sometimes we don’t, because it’s easier to rebuild from source

By the time our application gets large enough for the word “scale” to get thrown around, we’ve usually got several different kinds of data.  Each of those data groups has different answers to those seven processes above.  Let’s take a common type of data – orders for a web site.  At first glance, a web site might seem like it has just one type of data, but here’s several types for a system I tuned recently:

• Items – product details about what we sell.  This data is only periodically updated, and we’re not the primary source for this data.  Our manufacturers update the data via file feeds they send us.  Reads have to be absurdly fast, and the load will be very, very high.
• Price rules – no, one price does not fit everybody.  We run sales, referral programs, and discounts for our bulk buyers.  This data may not need frequent updates, but when updates happen, they have to be available everywhere instantaneously.  Otherwise, a pricing error waiting for a rollout might cost us millions.
• Reviews – end users can add their own reviews about our items.  We’re the primary source for this data, but it doesn’t have to be transactionally consistent with our sales data.  We can afford to lose some of this data.  Reads have to be fairly fast, and the data can be completely out of date.
• Shopping carts – transient data about people shopping at any given time.  We’re the primary source for this.  It’s insert-focused, and data has to be up-to-the-second.  We could lose all of this data at any time without a serious outage, but while it’s down, our sales stops.
• Orders placed – This is the good stuff.  We’re the primary source for this, and it absolutely, positively has to be completely consistent.  Our credit card records, items ordered, and shipment addresses have to be complete, and they have to be available across multiple datacenters simultaneously.

If we take each of those data profiles and put our 7 questions to it, we could get completely different answers.  Of particular interest is question 5 – how we retrieve the data.  At first glance, we give answers like, “When we retrieve orders placed, we need to join to items to see what they bought.”  There’s an important scalability part of that question, though – how old can the related data (items) be when we query the orders placed?  If I’m querying an order from yesterday, I can probably live with yesterday’s copy of Items.

Scaling Back Our Demands on SQL Server

As we dissect our data into different types, start exploring the needs of each type, and stay completely honest with ourselves, we’ll probably discover that a relational database like SQL Server might not be the best option for everything.  I’m a Microsoft SQL Server cheerleader, but if I’m going to get it to scale, I have to be honest about its strengths and weaknesses.  Pushing a technology to its limits isn’t always the right answer – especially if there’s another technology at hand that can do the job with less effort.

Separating this data off the SQL Server and onto other servers is a method of scaling out – throwing more hardware and services at our business problems.  StackOverflow’s first iteration of search used SQL Server’s built-in full-text search and we ran into scaling challenges.  SQL Server 2008 moved the full text search into the engine, and this introduced some concurrency issues that we weren’t able to solve in a cost-effective way.  I could have recommended that they build out a replication or log shipping infrastructure and start querying slightly stale copies of the database, but that just didn’t make sense for their needs.  They were armed to the teeth with web server hardware and guys who knew how to write code, so why not scale ourselves right out of SQL Server?  They switched to Lucene, and I (as a DBA guy) have been happy ever since.  Are the programmers happy?  Who cares?  Not me – I’m able to help them focus on what SQL Server really does well.

There are going to be parts of our data storage solution that absolutely require a relational database with transaction support.  Our example business needs the orders-placed data to leverage all of the data integrity features built into SQL Server.

Scaling Out SQL Server’s Remaining Work

Once we’ve pared down our list of demands, we can make different architectural decisions about how to add more hardware into the mix.  Some of the most common scale-out infrastructures I’ve seen include:

Using bidirectional or merge replication – this allows two SQL Servers to handle writes to the same database, same tables, at the same time.  Changes are replicated between the servers so that within a few seconds, both servers will have the same records.  Schema designs that rely on identity fields can run into trouble here, and we have to compensate by using identity fields with different seeds – one server uses odd numbers, the other uses even.  I only recommend replication as a scale-out method when the client has an around-the-clock database team on duty at all times because when this thing breaks, it breaks hard.  A DBA has to be available to get the alerts of problems with replication latency, jump to work on solving the problem, and get it fixed before a performance-killing reinitialization is required.

Putting several read-only SQL Servers behind a load balancer – if our primary bottleneck is reads (like reporting queries or a lack of app-tier caching), we can build several SQL Servers that are refreshed via log shipping or SAN snapshots.  End users or app servers don’t access SQL Server for reads directly – they get a server name that points to the load balancer hardware (like an F5 Big-IP), and the load balancer redirects that connection to an available SQL Server automatically.  Every X minutes, we pull a server out of the farm by telling the load balancer it’s no longer open for accepting connections.  After the last remaining query finishes, we refresh the data by applying new transaction logs or mounting a new SAN snapshot.  We then put it back into the load balancer pool, and the load balancer starts sending user connections to it.  This is a lot of moving parts, and while it’s all automated by scripts, scripts can still break.  Depending on the robustness of our scripts and our DBA team, we might be able to get away with this solution without an around-the-clock DBA team, but people still have to be on call.

Using SQL Server Denali (2011) AlwaysOn – this one isn’t actually available yet, but when this new version of SQL Server comes out, it holds the promise of up to 4 read-only replicas.  Apps can declare a read-only intent in the connection string, and the production SQL Server will redirect them to one of the available replicas.  Up to 2 of the replicas can be synchronous, although I don’t think many customers will opt for live reads from a synchronously updated SQL Server – the overhead will slow down production transactions.  You can read more at my post on Denali AlwaysOn.

Using third-party scale-out products – I’m always leery of adding third-party infrastructure to scale out SQL Server because it’s just so darned difficult.  Xkoto Gridscale sounded like the brightest hope in a while, but that was discontinued when Teradata bought Xkoto.  I don’t know of any other reliable technology that I’d trust to pull it off, and maybe more importantly, that I’d trust with my long-term business model.

The first two solutions (replication and load balanced read-only servers) don’t require anything unsupported, so they’re a safe bet.  Denali might not be seen as a safe bet because it’ll be a version 1 technology when it ships, but if you need to scale out, you can’t afford not to investigate it.  I’d even argue that some of my scale-up customers (like data warehouses) would be well-served by kicking AlwaysOn’s tires – if you could shed 50% of your load by moving your read-only queries away from your >8-CPU server currently running SQL Server Datacenter Edition, you could easily save hundreds of thousands of dollars in licensing.  If you’re currently running a 4-CPU box and you’re worried about headroom, Denali might be your silver bullet to avoid a big expenditure.

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.

More Posts - Website

Follow Me:

Before I start with this sordid tale of low scalability, I want to thank the guys at Phusion for openly discussing the challenges they’re having with Union Station.  They deserve applause and hugs for being transparent with their users.

Today, they wrote about their scaling issues.  That article deserves a good read, but I’m going to cherry-pick a few sentences out for closer examination.

“Traditional RDBMSes are very hard to write-scale across multiple servers and typically require sharding at the application level.”

They’re completely right here – scaling out writes is indeed hard, especially without a dedicated database administrator.  Most RDBMSes have replication methods that allow multiple masters to write simultaneously, but these approaches don’t fare well with schemaless databases like Phusion wanted.  SQL Server’s replication tools haven’t served me well when I’ve had to undergo frequent schema changes.  Some readers will argue that the right answer is to pick a schema and run with it, but let’s set that aside for now.  Phusion chose MongoDB for its ease of scale in these situations.

“In extreme scenarios our cluster would end up looking like this.”

Their final goal was to have three MongoDB shards.  Unfortunately, they didn’t understand that the Internet is an extreme scenario.  If your beta isn’t private, you can’t scale with good intentions.  Instead, they went live with:

“We started with a single dedicated server with an 8-core Intel i7 CPU, 8 GB of RAM, 2×750 GB harddisks in RAID-1 configuration and a 100 Mbit network connection. This server hosted all components in the above picture on the same machine. We explicitly chose not to use several smaller, virtualized machines in the beginning of the beta period for efficiency reasons: our experience with virtualization is that they impose significant overhead, especially in the area of disk I/O.”

The holy trinity of CPU, memory, and storage can be tough to balance.  For a new database server running a new application, which one needs the most?  In the immortal words of Wesley Snipes, always bet on black memory.  A lot of memory can make up for slow storage, but a lot of slow storage can’t make up for insufficient memory.  When running your database server – even in a private beta – 750GB SATA drives in a mirrored pair is not web scale.

“Update: we didn’t plan on running on a single server forever. The plan was to run on a single server for a week or two, see whether people are interested in Union Station, and if so add more servers for high availability etc. That’s why we launched it as a beta and not as a final.”

Unfortunately, if you go live with just one database server and all your eggs in two SATA drives, you’re going to have a really tough time catching up.  As Phusion discovered, it’s really hard to get a lot of data off SATA drives quickly, especially when there’s a database involved.  They ran into a few issues while trying to bring more servers into the mix.  They would have been better served by starting with three virtual servers, each running a shard, and then separating those virtual machines onto different hosts (and different storage) as they grew.  Instead, when faced with getting over 100GB of user data within 12 hours, they:

“During the afternoon we started ordering 2 additional servers with 24 GB RAM and 2×1500 GB hard disks each, which were provisioned within several hours.”

Now things really start to go wrong.  They’ve gotten 100GB of user data within 12 hours, and they’re moving to a series of boxes with more SATA drives.  They still only used two slow SATA drives, but don’t worry, they had a plan for better performance:

“We setup these new harddisks in RAID-0 instead of RAID-1 this time for better write performance…”

Whoa.  For the part-time DBAs in the crowd, RAID 0 has absolutely zero redundancy – if you lose any one drive, you lose all the data in that array.  Now we’re talking about some serious configuration mistakes based on some very short-term bets.  Phusion had been overwhelmed with the popularity of the new application, and decided to put this “beta” app on servers with no protection whatsoever – presumably to save money.  This makes their scaling challenge even worse, because sooner or later they’ll need to migrate all of this data again to yet another set of servers (this time with some redundancy like RAID 10).  Two SATA drives, even in RAID 0, simply can’t handle serious IO throughput.

“RAID-0 does mean that if one disk fails we lose pretty much all data. We take care of this by making separate backups.”

Pretty much?  No, all data is gone, period.  When they say they’ll make separate backups, I have a hard time taking this seriously when their IO subsystems can’t even handle the load of the existing end user writes, let alone a separate set of processes reading from those servers.

“And of course RAID-0 is not a silver bullet for increasing disk speed but it does help a little, and all tweaks and optimizations add up.”

Even better, try just getting enough hard drives in the first place.  If two SATA drives can’t keep up in one server, then more servers with two SATA drives aren’t going to cut it, especially since you have to get the data off the existing box.  I can’t imagine deploying a web-facing database server with less than 6 drives in a RAID 10 array.  You have to scale exponentially, not in small leaps.

“We’ve learned not to underestimate the amount of activity our users generate.”

I just don’t believe they’ve learned that lesson when they continue to grow in 2-drive increments, and even worse, in RAID 0.  I wish these guys the best of luck, but they need to take radical steps quickly.  Consider implementing local mirrored SSDs in each server like Fusion-IO or OCZ PCI Express drives.  That would help rapidly get the data off the existing dangerous RAID 0 arrays and give them the throughput they need to focus on users and code, not a pyramid of precarious SATA drives.

(Jumping on a plane for a few hours, but had to let this post out.  Will moderate comments when I get back around 5PM Eastern.)

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.

More Posts - Website

Follow Me: