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:
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.
51 Comments. Leave new
Very nice post, Brent. You do like to do things the Chicago way!
Thanks, sir! And yeah, I’m very much into the Chicago way, hahaha. I wasn’t born here, but I think my political mind was!
Wow, this is really helpful. Great post as always Brent.
Great post, helpful as always!
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.
Will – right, because dev servers were *always* shared. Nobody got a dedicated dev server, but they could get a dedicated prod or QA server.
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!
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.
Pavel – nope, you can have different schemas and different owners.
Hi Brent,
I am a bit confused. Did you mean “must” or “can”?
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.
“Multiple objects with the same name” means two tables named Customer.
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…
outstanding! can’t wait to drop the hammer on my first naughty vendor.
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
An excellent post Brent, I am going to borrow your support matrix. And put it to good use. Thanks.
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!
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.
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….
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?
Sam – I actually address this in the matrix download. Go ahead and read through that and it says who gets accounts where.
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.
brent excellent support matrix.
Thanks for sharing
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”?
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.
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.
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.
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!
“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)
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
Greg – hahaha, nice. Thanks, glad you liked it!
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 🙂
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.
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.
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.
Mohammad – doh! Sorry about that, fixed.
Thanks for that 🙂
I follow all your on-line information, but I just found this little doosey….
Awesome Post. Totally clear and user friendly
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
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
Great, glad I could help.
This is a great post and it has just painted some real life experience. It has been helpful Brent!!!
2006-03-28 Bill Gates Redesigned to use the new getOrderHistory function.
Always the best source of information for anything SQL Server.
Thanks as always 🙂
Thanks for the wonderful article. Is this matrix still available?
Yep! Just click on the link in the post.
Looks like it is not in the FirstResponderKit.zip now 🙁
If I understand correctly the matrix is no longer available, but If I ask really nicely is it possible to somehow obtain a copy? 🙂
No, I didn’t keep it – the instructions you need to build your own are here in the post.