Skip to content

1: Build a Server Inventory

When you’re in charge, you need to know exactly what you’re in charge of.

Make an Server Inventory

1 row for every server you have – whether you’re in charge of it or not.
in a spreadsheet or a table
- tenant
- 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 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.

possibly add columns for Core Count, CPU Count, and Memory.
The core and CPU counts will get you a head start on licensing,
To get even more ambitious, read the comments on this post about what should be in your DBA inventory.

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

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.

PowerShell  Kendal Van Dyke wrote the open source SQL Power Doc to discover instances on the network and export the list to Excel. 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, a sealed-box-product tool

another way 

Extra

  • Usernames/schemas and passwords
  • Instance/Server names and whether it’s Dev/Test or Prod
  • Number of users each box has (roughly)
  • What app runs on each box and the criticality of it
  • Backup schedules/maintenance plans/scheduled jobs
  • Where the backups are written to
  • Recovery model

  • 3rd party contact details for support where applicable

  • Data access scheme (sprocs, views, ad hoc, programs that access)

  • Purpose of server/data (mportant to understand why people want the data outside of the name of an app)
  • Benchmark of performance. Is good to know what “average” performance is.
  • Jobs, any scheduled jobs, Agent or otherwise
  • ETL stuff, linked servers, network paths accessed, any files that are imported. Need dependencies here on network items.
  • compliance considerations to be addressed or encryption technologies to be aware of
  • any current known issues with any of the platforms.
  • Report of what outages have occured recently and why.
  • Any recent h/w or s/w changes.
  • What their indexing strategy was and why.
  • stakeholders in the database (application that it supports) and what the POCs are, if available.
    You might want to introduce yourself to those stakeholders and let them know that you will be taking over maintenance of their database.
  • You also want to know who to notify about scheduled maintenance windows, unscheduled outages, and so forth.
  • Documentation of any current development initiatives that involve the databases.
  • Frontend apps – stored proc mappings
  • f which internal systems are linked to each other and why. Is server A is getting data from a table in server B and how often.
  • any SSIS packages saved on that instance
  • Any Source Control information
  • Logs of the past X number of months of support calls
  • Logs of the past X backups
  • Last time the recovery plan was tested and its outcome
  • UNC Links to the networkshares of the server log, DB files and backups. ,
    share these folders to have access without any tool.