How to Build a SQL Server Support Matrix

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

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 in our First Aid Kit and making it your own.

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

Previous Post
Consulting Lines: “Do you want 10% faster, 100% faster, or 1000% faster?”
Next Post
Dealing with Presentation Criticism

48 Comments. Leave new

  • Very nice post, Brent. You do like to do things the Chicago way!

    Reply
  • Matthew Clyman
    March 22, 2011 11:07 am

    Wow, this is really helpful. Great post as always Brent.

    Reply
  • Stephen Archbold
    March 22, 2011 11:18 am

    Great post, helpful as always!

    Reply
  • Did I read this correctly that you allowed RDC into prod and QA/test if the boxes were standalone and on virtual servers, but not for dev? Interesting.

    Reply
    • Will – right, because dev servers were *always* shared. Nobody got a dedicated dev server, but they could get a dedicated prod or QA server.

      Reply
      • I get it now… Since the dev servers are a shared resource letting potentially many people have RDC access would degrade performance for everyone. Even in dev this is bad action. Thanks!

        Reply
  • Pavel Nefyodov
    March 23, 2011 4:35 am

    Hi Brent,
    Just wanted to confirm regarding this bit
    “SQL Server will allow you to create multiple objects with the same name as long as they’re owned by different people. For example, we could end up with dbo.Customers, and Brent.Customers. Consider the following query:

    SELECT * FROM Customers

    When Brent logs in, he will be querying the table Brent.Customers. When any other user logs in, they’ll query the table dbo.Customers. The two tables will have different data, and that will quickly become a troubleshooting nightmare.

    I thought in SQL Server 2005/2008 it is no longer the case. Schemas must be different, but owner could be exactly the same.

    Reply
    • Pavel – nope, you can have different schemas and different owners.

      Reply
      • Pavel Nefyodov
        March 23, 2011 8:13 am

        Hi Brent,
        I am a bit confused. Did you mean “must” or “can”?

        Reply
        • Pavel Nefyodov
          March 23, 2011 8:25 am

          Source of my confusion – “SQL Server will allow you to create multiple objects with the same name as long as they’re owned by different people.”
          This does not sound right to me. You can create table with the same name in different schemas. Schemas should not necessarily be owned by different people.

          Reply
  • Brent

    Great post, I’ve always wanted to implement something along this line but never really had time to consider how to visualise it, great incentive…

    Reply
  • outstanding! can’t wait to drop the hammer on my first naughty vendor.

    Reply
  • Question, how do you set up your SMS alerts?

    I tried to set something up to go to my google voice number, but they don’t have a smtp to SMS gateway

    Reply
  • An excellent post Brent, I am going to borrow your support matrix. And put it to good use. Thanks.

    Reply
  • Ivan Rodriguez
    March 23, 2011 9:33 pm

    Brent,

    Could you explain the reason why would you allow users to be members of the db_owner role in a critical production server or any production server?

    Great post by the way!

    Reply
    • Ivan – because it’s usually what outside vendors require for their applications. It’s typical for a third party vendor app to require DBO, and I can’t say they can’t have it in production – that would immediately be a deal-breaker. You have to be realistic about what you’ll allow.

      Sure, I don’t want them to write cursors either – but that doesn’t mean I can ban all cursors from production. I don’t have that choice when we’re implementing third party apps.

      Reply
      • I’ve seen an app that requires it’s admin user to be a member of the sysadmin server role! You can guess what we did there….

        Reply
      • Thanks for the matrix!

        But what about actual developer users – do they get accounts on the servers and db_owner rights on a critical server?

        Reply
        • Sam – I actually address this in the matrix download. Go ahead and read through that and it says who gets accounts where.

          Reply
          • Brent,
            It’s so much mo betta than I thought it would be – was just expecting the matrix.

            I noted the section on change control, which implies that developers shouldn’t be running change scripts in production. Hopefully I didn’t miss it, but what are your thoughts on read access in production? Write access?!

            I mention to our devs that in some places you don’t get ad-hoc query to the production databases and they think I’m delusional.

          • As far as ad-hoc query to production – two phrases that don’t play nice with each other:”cartesian join” and “million row tables”

            I usually tell the Dev to google the first phrase, and imagine it combined with the second phrase.

  • Thanks Brent. As always, your posts are informative and helpful.

    Reply
  • brent excellent support matrix.
    Thanks for sharing

    Reply
  • Great post, thanks. As soon as I saw the matrix, I immediately started building one.
    Question, even though it’s called a support matrix, but do you include those not under your responsibilities as well, just to make it clear that “Look, I am aware of this server, but see here, it’s not supported in my matrix”?

    Reply
    • Glad you liked it. No, I didn’t include any under my responsibilities. That’s a great question though – I can see why you’d have a separate list of excluded servers that you don’t manage.

      Reply
  • Brent, Great post. I have general environment but was not able to create such a nice visual representation. I will be borrowing your spreadsheet visual and incorporating it into our Policy and Procedures doc.

    Reply
  • Good post, but I want to bring up a topic on framing. (Medium is the message kind of stuff). As a recent hobby, I’ve been looking at how document layouts and other visuals influence what the audience/reader assumes.

    Making things look spiffy and polished can go a long way. If you deliver a support matrix as a well laid out pdf. It’s going to say a lot more than something scribbled on a napkin “I’m not gonna support devs on weekends”. It hints at “non-negotiable” without saying it.

    It goes for other things too. This site (brentozar.com) looks professional and it seems like each article could have been vetted by a team of tech experts just on style alone. (Mental note to self, book a photoshoot for blog)

    On the other hand, there’s a SQL website I know with a huge amount of technical authority that could probably use a visual overhaul, so what do I know.

    Reply
    • Thanks, sir! I’m honored by the comments about the style, but honestly, I’m back to the point where I hate the look of it again. (sigh) I seem to only stick with a visual look for about six months, and then I wanna tweak it. I’m working with branding & marketing consultants to come up with a new logo, tagline, etc. The reasons for that will become apparent in about a month when we (yes we) relaunch this site. Stay tuned!

      Reply
      • “We”? I knew you had a decent sized ego but …
        (I tease)
        Seriously, the “we” sounds intriguing though.

        For me, it’s hard to spend too much time/$ on the look of my blog when my blog is not a big part of “what I do” (the drawings are just a fun hobby that meshes well with it).

        And I wonder what you think about hand-me-down wordpress themes if you’re not using this one (okay, teasing again)

        Reply
  • Just had some funny looks from colleagues as I read that last bad cop paragraph laughing out loud.
    Brent, the stuff you cover is often on the fringes of what I do technically but your consulting line and general “upward management” tips are right on the money. Many thanks

    Reply
  • I really like this. I may steal your idea and start implementing it for clients. I always suggest creating a vendor checklist of questions we should ask vendors as DBAs (shameless plug to one such vendor interview questionnaire – http://www.straightpathsql.com/archives/2009/01/dba-questions-to-ask-a-vendor/) but I never thought of an internal support matrix. One more thing to go into the run books/database of databases/etc. Would certainly make life easier when deciding where to put something. Thanks 🙂

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

    Woot! There’s a Dilbert Cartoon in there!

    Great post.

    Reply
  • Brent,

    I have used your coding style(SOP) to my blog scripts

    http://sqlserverblogforum.blogspot.com/2011/04/shrinking-log-file-script-for-sql.html

    Thanks.

    Reply
  • Hi Brent,

    Thanks for another superb post. The problem is, I am not able to download the SQL-Server-Support-Matrix.doc document. When I click on it, it takes be back to this page. I was wondering if the document is still there or not?

    Thanks.

    Reply
  • I follow all your on-line information, but I just found this little doosey….
    Awesome Post. Totally clear and user friendly

    Reply
  • Darshan Dace
    April 23, 2014 1:54 pm

    Hey Brent, Very helpful doc. Just started my new job as DBA few months back and I am glad to run into your this post through one of your training documents. I have download the support matrix which is going to be very helpful to set the right expectation…Thanks again!

    Dave

    Reply
  • Darshan Dave
    April 23, 2014 1:55 pm

    Hey Brent, Very helpful doc. Just started my new job as DBA few months back and I am glad to run into your this post through one of your training documents. I have download the support matrix which is going to be very helpful to set the right expectation…Thanks again!

    Dave

    Reply
  • This is a great post and it has just painted some real life experience. It has been helpful Brent!!!

    Reply
  • 2006-03-28 Bill Gates Redesigned to use the new getOrderHistory function.

    Reply
  • Farren Minns
    June 2, 2016 4:31 am

    Always the best source of information for anything SQL Server.

    Thanks as always 🙂

    Reply
  • Thanks for the wonderful article. Is this matrix still available?

    Reply

Leave a Reply

Your email address will not be published. Required fields are marked *

Fill out this field
Fill out this field
Please enter a valid email address.

Menu
{"cart_token":"","hash":"","cart_data":""}