What if your boss walked up right now and asked you, “How many database servers do we have?”
Would you start sputtering, “Uh, do you mean production, or dev? Do multiple instances on a cluster count? And by the way, who’s asking?”
Or would you confidently answer with an exact, unwavering number?
When I was a DBA, I thought I had a pretty good handle on my environment. I did proactive work like building a database server support matrix, setting up failsafe operators and alerts so I’d get notified when things broke, and even attended development planning meetings so I could see what was coming down the pike. Now that I’m a consultant, though, I realize that I wasn’t really armed for the kinds of questions business users like to throw at DBAs from out of nowhere.
How many database servers do we have?
When the business asks this question, it’s only the beginning. Here’s what they really want to know:
- How many production servers/clusters do we have?
- How many instances running on how many servers?
- How many databases do we have?
- How many applications are we supporting on each database server?
To start the process of surveying a Microsoft SQL Server environment, check out the Microsoft Assessment and Planning Toolkit. This free tool has a sinister purpose – setting up for a licensing audit – but it’s also really helpful to get a scan of SQL Servers buried under desks or hidden on application servers. It helps you find out about servers you’d otherwise never hear of – until they crash, at which point it’s too late.
The best DBA teams track not just the number of instances and databases, but the overall data size in the environment and the growth per week. I love being able to tell business executives, “I’m managing 10.4 terabytes right now and it’s growing an average of 3% per week.” They understand growth metrics, and it shows them that I take my job as seriously as they take company sales and expenses.
Which servers are no longer under support?
Most companies are harboring a few old database servers that soldier on just fine despite being long out of support. Database administrators often take this question personally and start responding with whines about not being able to move an old vendor application off SQL Server 2000, but take the emotion out.
The Microsoft Product Lifecycle Search is a little cryptic, but
- SQL Server 2000 – mainstream support is long gone (2008), but you can still get extended support through April 2013.
- SQL Server 2005 – if you thought 2000 was hard to kill, 2005 is going to be worse. It’s got enough DBA-friendly features like DMVs and database mirroring that it might just stick around forever – certainly long after April 2016 when extended support ends.
- SQL Server 2008 – we’re looking at least through January 2019 for extended support.
- SQL Server 2008R2 – as with 2008, at least through January 2019.
- SQL Server 2012 – servers you deploy today are safe through July 2022, or 2023 for Express Edition.
If you’ve been pushing the business to get off SQL Server 2005 for support purposes, you should find a different approach before management finds the above links.
If that database server went down, what would you do first?
The business doesn’t really care how you answer this question: they just want to hear confidence. They want to hear an immediate, well-thought-out-answer that says to the world, “I’m a data professional, and I’m the person you call to the helm when the database server hits the rocks.”
Here’s the kind of answer the business wants to hear: “I’ve got a script to take a snapshot of the database mirror in our DR datacenter and check the time of the most recent record in the sales table. I tell my manager that if we have to fail over to DR, we’re going to lose X minutes of data. Then I start at the top of our troubleshooting checklist, which is posted on the wall in my cube so I can find it fast when trouble strikes.”
Confidence is a key part of being a good DBA. Don’t just take it from me – take it from world-renowned database expert Jack Palance:
6 Comments. Leave new
All important things. And much easier when your organization likes to have some clarity and specificity as to who is responsible for what. I have worked where the attitude was “Hey, it’s SQL Server how hard can it be?” as well as “Let the DBAs take care of the databases and database servers. Thank you”. The former was a source of frustration, especially when management would simply shrug their shoulders and say “I can’t do anything about that”. The later was where things ran as they should. Either way one can still do the right (DBA perspective) thing, cover the bases, stay calm and carry on.
I was at one large client that I won’t name, but they deal with passenger rail, and there was no idea of how many SQL server installs they had. When I got there, as only SQL DBA, I used some tools and started scouring the network looking for SQL installs to know what I was really up against. Well, the IT network guys freak out that I was pinging all their boxes, they swore I had a virus. In the end I think we found near 2,000 SQL installs, some not used, some as low as v.6.5. Needless to say this lead to a multi-month process to document the servers, migration, upgrading and even decommissioning the ones not needed. Annual saving within a month of starting this internal audit was well over $250k in licensing and maintenance support. It is a very good thing to know what your inventory is, even though it is a pain sometimes.
Hi Brent,
I think these business-related Webcasts are just as important as the technical ones. Sometimes it’s too easy to focus on being data stewards, and lose sight of the fact that the purpose of the data is to enhance the business.
A DBA might say, “No data, no business”, but “No business, no data” is just as true.
Hi Brent,
Awesome work. This was extremely helpful to me and im actually using your support matrix template at my company now. Thanks a million!
you cleared many important things;as i am beginner it will help me.
FYI: I’ve used the MAP Toolkit early this year (2021) to assess a SQL Server estate of about 1000 instances on Azure VM. The tool is old but still makes the job done. I wrote an article in case you stumble upon bugs and errors: https://www.jeeja.biz/2021/07/08/how-to-discover-sql-server-instances-on-azure-vms/