At your company, walk into the VP of Sales’s office and ask them how many salespeople they have.
NO, I mean, don’t actually DO that, because he’s going to ask you why the sales app is so slow. But I mean, imagine if you COULD walk into his office and ask him that. I bet he would have an instant answer. He wouldn’t wait for a single moment. Or walk into the CEO’s office and ask how many employees he has. Or ask the CFO how much the annual budget is.
My point is that when you’re in charge, you need to know exactly what you’re in charge of.
Make a Spreadsheet Inventory
Let’s start by making a spreadsheet. Across the top, make columns for:
- SQL Server Version (2017, 2016, Azure SQL DB… well, maybe 2008 too, sadly)
- Edition (Standard, Enterprise, Developer)
- Environment (Production, QA, development, disaster recovery)
- Department (sales, HR, accounting, IT, mixed use)
- Business Users Affected (list of people to email when the server dies)
- Application Names (internal or external product names)
- Plan B
That last column gets a little tricky – it means, if this server dies in a fire, what’s our Plan B? Are we going to restore the databases from another server? Will we fail over to a log shipped copy? Or will we update our resume and head out for an early lunch? As we go farther into the training, we’re going to get much more specific about Plan B.
There’s no wrong answers here: this step is about understanding where we’re at today, not where we’d like to be. We’re never where we’d like to be. (Me personally, I’d like to be at a poolside bar right now, but noooo.)
If you’d like to get ambitious, add additional columns for Core Count, CPU Count, and Memory. The core and CPU counts will get you a head start on licensing, although I have to confess that we’re not going to cover licensing as part of our training plan. To get even more ambitious, read the comments on this post about what should be in your DBA inventory.
What We’ll Do With This Spreadsheet
Right now, you probably sleep well at night thinking you know everything that’s happening in these servers. Hoooweee, have I got bad news for you. Over the next six months, we’re going to progressively add more and more columns to this spreadsheet as we learn more about our environment, uncover problems, and learn how to solve them.
For bonus points, add a column for What Scares Me. Write a quick note about the one thing that scares you most about this server. Maybe it’s blocking problems, maybe it’s the failing jobs, maybe it’s code you don’t understand. Six months from now, I bet you’ll be proud of how this column has changed.
How to Survey Your Network for Servers
Put a row in the spreadsheet for every server you have – whether you’re in charge of it or not. We want to start with a good inventory of what we have.
The easiest way – if you’re just starting at a company and you have no idea what the network holds, check out the free Microsoft Assessment and Planning Toolkit. It’s designed for licensing compliance, but it works great for building server inventories. It scans your network looking for whatever programs you pick, but just confine it to SQL Servers only.
The PowerShell way – if you’re into PowerShell, Kendal Van Dyke wrote the open source SQL Power Doc to discover instances on the network and export the list to Excel. He since joined Microsoft and the tool hasn’t been updated much, but it may be enough to get the job done if you’re comfortable with PoSH.
The GUI way – Idera’s SQL Inventory Manager ain’t cheap, and I haven’t used it myself, but if you want a sealed-box-product tool to do it, there you go.
Got another way? If you’ve got a way that you’ve used successfully, leave a comment so we can add it to the list for other readers. Bonus points if it’s free, open source, and/or super-easy to use.