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.
In the next post, we’ll use this inventory spreadsheet to assess how bad of shape we’re in.
25 Comments. Leave new
Buckle up DBA’s!Brent is taking us on a nother adventure!Sit down tight and pick up your notebooks!
I am ready for takeoff..notebook ready!
I use dbatools, it has a lot of functions to collect data and be as granular as you want.
I use a combination of Idera Inventory Manager (can pick up extraneous items or false positives), MAPS (tends to miss servers from run to run, so run it frequently over time), and a list of servers from our Veeam Backup system to compile a SQL Inventory. Connection tests from my Central Monitoring server to verify I can connect to them (get myself added to those I need), and set up a job for each one to “phone home” to my central repository with key properties and stats I am interested in tracking. This “phone home” function is first thing I put on a new SQL implementation. Use the Central Repository to plan Patching, quickly review memory, security, MaxDOP settings, etc. Quick and dirty way to monitor servers we do not want/need to pay a Monitoring tool license for.
So, one of the gotchas about the MAP tool: You have to create a new database every time you run a new collection. If you don’t, you will see this problem. I confirmed this is the intended behavior and usage with MS support. I was a little dumbfounded, but that’s apparently how the tool was meant to be used… it’s great otherwise! Just remember to make a new DB each time…
First thing I ask in a new company (2 in 2 1/2 years) is “Do you have an inventory?” They may claim that they do, but they really don’t. So, I build it, which is usually a pretty big project in & of itself. Discovering who owns the databases & what they are used for is the most daunting task. I go for the detailed list- CPU’s cores, RAM, etc. I also document the backup path. As I retire databases & servers, I document those too on their own tabs. That’s so I can show the boss progress & savings too. My goal, as I think of it, is this- What if the 3 of us go in on a big lottery ticket together & actually win? We quit! (You would too) Well, what about the poor souls who have to step in in a hurry? I’d like to be able to allow them to hit the ground running while I am off globe trotting with my new fortune. 😉
Another means I’ve used for SQL server discovery is connecting to our WSUS server looking for machines that have either downloaded or need to download MS patches for SQL.
Mike – oh, I love it! That’s genius!
Love it! I’d be at a poolside bar too 🙂 I have a document with all the servers with a nice schema of the relations between the servers that allows to understand quickly in case of problems. I’m up to 11 production servers right now and the count is always going up 😉
I have a set of tables I use. The main one for inventory holds the host name, sql server name, and then any property I can get such as port, IP, Windows version, SQL version including numbers, service pack level, CU level, proc name and how many physical and allocated, server collation, security mode, audit level, default paths for data, logs, tempdb, backup, service accounts used, install date, if I have basic audits set up for login and ddl changes, and then static fields for the purpose and owner of the data and notes on the server. I have it updated by SSIS package and use a temporal table to store previous values so I can see when a value changed and run this once per day. I then use this table in other SSIS packages to gather drive letters and sizes, databases and their sizes and properties, I have a script we run when we create a new server that will do a basic insert into the inventory table that will then be updated with the details that night. I can use this inventory table to query against for different sets of information. Such as ordering the list by install date to see my oldest servers or see which servers are out of service pack (or CU) compliance such as needing a new SP or no more than 3 CU levels behind. As new SQL versions, service packs or CU’s are released I update a table with this information so I can use the SQL versions table to compare my inventory with.
Very cool and VERY interested!
We had our own database with our inventory in it and we learned about Idera SQL Inventory Manager, and after demoing it, we decided to switch to that. I really like how the back end databases are intuitive and it is easy for us to write our own queries and reports against. Sure, we could have done it ourselves, but they had several cool things baked in, that we decided to use them.
I wrote a collection service in c#. the spreadsheet is a key/value pair stored in a SQL table. The metrics are queries in another table. Whenever a new server or metric is added, the service starts collecting automatically, keeps history, etc. Operational scripts/processes are built to use settings in a local key/value table so they can be collected as metrics (no guessing if the documentation is up to date). a lightweight UI enables query and export of the data.
Eric – neat! Have you thought about open sourcing that?
ApexSQL Discover? Website says its free and I’ve seen a video where it looks OK. The devil is in the detail though so if anyone’s used it in anger then feel free to comment..
Spiceworks has a decent free tool with auto-discover and CMDB capability, including the ability to compare 2 servers and ask “What’s different?”
There’s a whole bunch of other stuff you may never use, but hey its free (as in ad-supported)!
Toby – ooo, neat, do you have a link to that that shows how it works? That’d be a neat tool to add.
Years ago I was confronted by the same problem. So I ended up with a massive spreadsheet that someone else compiled. I found it strange that DBA’s cannot organize their own data.
I promptly designed my own database, focusing on the entities, Server (windows), Instance (SQL Server), Application (Any app connecting to sql server) and of course people.
I imported the spreadsheet and normalized the data into tables.
Later I developed a C# frontend and voila, all my information at my fingertips.
More functionality came with time and now we have a nice system that helps us manage our inventory. Helps a lot when you need to patch servers.
Hopefully I will have time to release a light version as freeware someday.
Opserver from StackExchange (https://github.com/opserver/Opserver) is another great tool to monitor your sql servers
Yeah, that’s about monitoring servers you already know about though, not gathering an inventory. I’ll be talking about monitoring in a later post.
CMS, I use the SQL built in feature CMS(central management server). I can run a query on all my servers , can group them etc thru SQL registered servers!!
I built our inventory by leveraging the data in our remote desktop manager.
Since servers are added to it organically when our server team builds them its always up to date. As an added bonus I can determine some facts about the servers right out of the gate from the folders that team assigned the server to.
Using that data I built a c# console app that runs through the servers, collects the data and stores it centrally.
The resulting data can be queried and reported on to tell me about our environment. We have an email that goes out to the team every morning with a detailed list of problems to address, from low space volumes to sever misconfigurations.
Forgot to mention, the rdm desktop is updated by the server team, and new servers automagically get added to the inventory collection.
DBATools instance-finder: https://dbatools.io/find-sql-instances
Tested & works.
Check my repository I have told you about before. It’s open source and free to use.
It’s still work in progress. Once it’s complete I’ll sign it and distribute it as an exe.
https://github.com/bmalmutairi15/DB-Inventory