building an inventory view of SQL Servers with dbatools

Posted on April 25, 2019 by Andreas

The situation

Hey all, I am Andreas Schubert and I am working as a Principal Consultant and Database Reliability Engineer for SQL Server & Azure for multiple national and international companies. My focus is on implementing and operating complex 24/7 SQL environments with tens and hundreds of servers and multi-terrabyte databases.

With the multitude of environments that I am operating, it’s impossible to remember every server, every database or the multiple different ways they are interacting with each other. Therefore, one of the first things I do when taking over a consulting engagement is mapping out all those different bits of information.

Since the environments usually change pretty fast, my goal is to automate this process as much as possible.

In this series of posts, I will try to show you how I am implementing this. Of course, your requirements or implementations may differ, but hopefully this blog post can give you some ideas about your tasks too.

Enter dbatools

Before dbatools existed, I had to rely on either the various monitoring solutions that my customers are using or on scripts created by myself. There are a lot of really great 3rd party tools out there that do an awesome job. Unfortunately, they all differ in how they are used or what information they report back. I needed something that is easy to implement, with as few dependencies as possible and works across all SQL Server versions. That’s when I started using dbatools.
I immediately felt in love with how flexible it is. And boy, did its functionality grow fast!

Today, there are tons of commands available that cover almost all, of the various, areas SQL Server has to offer.

The task

Before I dive into specific SQL Servers for in-depth analysis, I want to see some sort of inventory. The minimum information I would like to collect is:

What server is SQL running on?
What edition and patch level does it have?
How many cores and RAM is it equipped with?
How many databases are on the SQL, what size are they and what’s the biggest one?
Is the SQL part of an Availability Group? If yes, what’s the name of the AG, what role does the SQL have right now and what’s the underlying cluster object name?
On top of that, I find that SQL Instances usually get a “pet name” alias. This means, when people within the company are talking about the SQL Server, they don’t call it by it’s machine name, but rather something like “the production database” or “the cluster”. Technically not correct, but that’s how it is happening. Due to that, my overview should also contain that alias to make it easier for me until I remember all the mappings.

Whoever has built an inventory script in the past knows that collecting the above information requires quite a few scripts. On top of that, the underlying DMVs have been changed between SQL versions, so you need to account for that. Microsoft has made that much easier with providing SMO (SQL Server Management Objects), a set of libraries that abstract away the complexity of collecting that information. Thankfully, Microsoft also enabled the dbatools team to include SMO in their framework. My example solution relies solely on dbatools (which works – not only but also – with SMO).

OK, enough talk, let’s jump straight into the code.

The solution

Right at the beginning of any of my scripts, I am defining the root of the script itself. I do this because I re-use a lot of functions.

Since we want to collect the information for more than one SQL Server instance, we will first build a list of SQL Servers. We could query the list of instances from a central management server, but for the purpose of this post – and portability- we will keep it simple. We will also assume the account executing this script will have sufficient permissions on each SQL Server instance and that it can connect via Windows Authentication. I generally prefer Windows Authentication over SQL Server authentication due to security concerns, but that is a completely separate topic.

The names of the Servers will be coming from a simple text file in our example. Just do me a favour and do NOT put your server list into an unsecured network location – again, we need to keep security in mind.

Next, we need to load this file into our PowerShell session. For the sake of simplicity, I am loading it explicitely into my script. Normally, I have a variable populated with the servers in my profile, so I don’t have to do this each time.

$script:root = 'D:\AdminScripts'
$ProductionServers = Get-Content (Join-Path $script:root -ChildPath 'Production.txt')

Similar to the actual server list, I am using a text file “AliasList.txt” to store the alias information i mentioned above:

It´s the same system: the name of the server or instance, followed by the alias name. Both values are separated by a semicolon. Loading and storing the alias information in a hash table is a simple one-liner in PowerShell:

$AliasList = Get-Content (Join-Path $script:root -ChildPath 'AliasList.txt') | Select @{Name= "Instance";Expression={$_.ToString().Split(';')[0]}},@{Name= "Alias";Expression={$_.ToString().Split(';')[1]}}

Since I usually exclude system databases from my reports, I am defining a separate list of them as well for easier reuse:

$systemDBs = "master","model","msdb","tempdb", "ReportServer","ReportServerTempDB"

At this point, we have all the preliminaries completed: A list of SQL Servers to query, a list of system databases that we will exclude and a list of alias information. Let’s hit the servers and put the resulting data into a variable. I’ll first show the complete code block, then we will talk about what it does.

$rawData = $ProductionServers | Connect-DbaInstance | Sort-Object Computername | Select-Object ComputerName,
    # map the SQL version
    @{Name="SQL Version";Expression={ if ($_.VersionMajor -eq "11") {"SQL 2012"} elseif ($_.VersionMajor -eq "12") {"SQL 2014"} elseif ($_.VersionMajor -eq "13") {"SQL 2016"}
    elseif ($_.VersionMajor -eq "14") {"SQL 2017"} elseif ($_.VersionMajor -eq "15") {"SQL 2019"} elseif ($_.VersionMajor -lt "11") {"SQL 2008R2 or older"} else {"unknown"}}},
    ProductLevel, Edition,
    # RAM
    @{Name= "Memory (GB)";Expression={[math]::Round(($_.PhysicalMemory) / 1024)}},
    Processors, InstanceName,
    # total count of user dbs
    @{Name= "User DBs";Expression={($_.Databases | where {$_.Name -notin $systemDBs} | Measure).Count}},
    # total db size for all user dbs
    @{Name= "Total DB Size (GB)";Expression={[math]::Round(($_.Databases | where {$_.Name -notin $systemDBs} | Select size | Measure -Property Size -sum | Select sum).sum / 1024)}},
    # biggest DB (name, Size(GB)
    @{Name= "Biggest DB (GB)";Expression={"$($_.Databases | where {$_.Name -notin $systemDBs} | Sort Size -Descending | Select -ExpandProperty Name -First 1)
    ($([math]::Round(($_.Databases | where {$_.Name -notin $systemDBs} | Sort Size -Descending | Select -ExpandProperty Size -First 1)/1024)) GB)"}},
    # add the name of the Availability Group (if any)
    @{Name= "AG (s)";Expression={$_ | Select -ExpandProperty AvailabilityGroups | Select -ExpandProperty AvailabilityGroupListeners}},
    # add the current role of the server in the Availability Group (if any)
    @{Name= "Role (s)";Expression={$_ | Select -ExpandProperty AvailabilityGroups | Select -ExpandProperty LocalReplicaRole}}, ClusterName | Sort ComputerName

While this code may look complex, from a PowerShell point of view it’s really pretty simple. First, we take the list of our Productionservers and pipe it to Connect-DbaInstance cmdlet. Connect-DbaInstance is the result of dbatools calling the SMO functionality, returning a complete SMO object of the SQL Server connected to.

Technically, the part with $rawData = $ProductionServers | Connect-DbaInstance already gives us all the information we need for our report. But since we don’t want to return all the possible SMO properties and objects (that would result in a very long operation), we pipe the results of this directly to a Sort, followed by returning the actual information we are interested in:

$rawData = $ProductionServers | Connect-DbaInstance | Sort Computername | Select ComputerName,

First we extract the Computername. On the next two lines, we map the SQL Server major version number to a clear-text string:

# map the SQL version
@{Name="SQL Version";Expression={ if ($_.VersionMajor -eq "11") {"SQL 2012"} elseif ($_.VersionMajor -eq "12") {"SQL 2014"} elseif
($_.VersionMajor -eq "13") {"SQL 2016"} elseif ($_.VersionMajor -eq "14") {"SQL 2017"} elseif ($_.VersionMajor -eq "15") {"SQL 2019"}
elseif ($_.VersionMajor -lt "11") {"SQL 2008R2 or older"} else {"unknown"}}},

Right on, we extract the product level (e.g. RTM, SP1, …) and the Edition of the SQL Server (Standard, Enterprise…), followed by the available machine memory. Since this is returned in MB, we format and round it so that we get a nice number in GB (e.g. 12, 48 or 128).

Then we include the number of logical processors and the name of the instance – in case we have a named instance.

The number and size of user databases is a bit more complex. We need to query the “Databases” collection of the SMO Server object, filter out the system databases, get the size property of each object in the collection and measure it (count for the number and SUM for the combined size of the databases). Of course, we want those numbers to be nicely formatted and rounded to the full GB, so we add the formatting as well:

# total count of user dbs
@{Name= "User DBs";Expression={($_.Databases | Where {$_.Name -notin $systemDBs} | Measure).Count}},
# total db size for all user dbs
@{Name= "Total DB Size (GB)";Expression={[math]::Round(($_.Databases | Where {$_.Name -notin $systemDBs} | Select size | Measure
-Property Size -sum | Select sum).sum / 1024)}},

To get the size of the biggest / largest database on the server, we use the same technique, only that we sort the database object list by size in descending order and take only the first object:

# biggest DB (name, Size(GB)
@{Name= "Biggest DB (GB)";Expression={"$($_.Databases | Where {$_.Name -notin $systemDBs} | Sort Size -Descending | Select -ExpandProperty Name -First 1)
($([math]::Round(($_.Databases | Where {$_.Name -notin $systemDBs} | Sort Size -Descending | Select -ExpandProperty Size -First 1)/1024)) GB)"}},

The PowerShell pipeline can be really awesome!

Now let’s add the information about the Availability Group Listener to our list. The AG information is another sub-object of the SMO collection which we first have to extract to get to the information below.

# add the name of the Availability Group (if any)
@{Name= "AG (s)";Expression={$_ | Select -ExpandProperty AvailabilityGroups | Select -ExpandProperty AvailabilityGroupListeners}},

And exactly the same way for the role of the current replica as well as the cluster object name:

# add the current role of the server in the Availability Group (if any)
@{Name= "Role (s)";Expression={$_ | Select -ExpandProperty AvailabilityGroups | Select -ExpandProperty LocalReplicaRole}}, ClusterName | Sort ComputerName

That’s a whole lot of information retrieved by just one call to a PowerShell method. How awesome is that? Most of our script is logic around formatting and extracting information from sub-properties and objects.

And finally, we add the Alias to our result, matching them by computername:

# add the alias to the rawdata
$rawData | % {
        $v = $_.ComputerName
        if ('' -ne $_.InstanceName){$v +="\$($_.InstanceName)"}
        $alias = $AliasList | Where {$_.Instance -eq $v } | Select -ExpandProperty Alias -First 1
        $_ | Add-Member -MemberType NoteProperty -Name AliasName -Value $alias
    }

The last step is to convert our object to HTML and add some css styling to it. Then we can either send it via eMail or store the generated html as a file for future reference.

$css = Get-Content (Join-Path $script:root -ChildPath 'css.txt')
$html = $rawData | ConvertTo-Html -Fragment -PreContent "$($css)<h2>Instance KPI Summary</h2>" -PostContent "This summary has been generated with the help of the awesome PowerShell module dbatools!" | Out-File (Join-Path $script:root -ChildPath 'result.html')

The result is a nicely formatted html report:

This was only a very basic example of what you can do with PSTools, PowerShell and a bit of magic piping. I hope you found this useful.

For questions and remarks please feel free to message me at any time!

You can find the complete script in my GitHub repo.

40 thoughts on “building an inventory view of SQL Servers with dbatools”

Marcin Gminski April 25, 2019 Reply

Good job. This subject is close to my heart! You will be pleased to know that we’re actually building an inventory command into dbatools. Feel free to contribute!
- Andreas Post authorApril 25, 2019 Reply
  
  Thank you Marcin, i will get in touch with you on this
Ewan Campbell April 25, 2019 Reply

Thanks. Useful.
When you have a list of SQL Servers to query, very good.
What about a situation where no-one has a SQL server list?
eg. you inherit a SQL estate that isn’t documented properly.
So you want to discover what’s out there.
- Andreas Post authorApril 25, 2019 Reply
  
  Ewan, in this case you have the perfect opportunity to get to know the network team. Grab a bunch of coffee cups, visit them and discuss the options around scanning the network for SQL Instances. You want to ask them first because once you start scanning, all sorts of intrusion alerts will go off when the network is properly monitored. There’s a so a dbatools command for help with finding instances, so once you start you will be amazed how many ‘unknown and unmanaged’ SQLs you will find, even in environments that are considered well documented 😉
- Marcin Gminski April 25, 2019 Reply
  
  A combination of nmap scanner and then WMI discovery to get available instances did the trick. I’m hoping we can build similar discoverer in dbatools at some point.
  - Andreas Post authorApril 25, 2019 Reply
    
    Marcin, sure, that’s a valid and common option. Though I find the usage of nmap raises far too many security concerns in corporate environments so that I try not to use it there.
    I tested “Find-DbaInstance -DiscoveryType Domain, DataSourceEnumeration” and found it to be good enough for a quick inventory. Sure, it doesn’t find those instances that don’t have SQL Browser enabled, but those can be found easily by doing a quick broadcast to 1433 and 1434 😉
    - Marcin Gminski April 25, 2019 Reply
      
      Absolutely, it was just an example of how I dealt with it on 17k+ servers estate.
      - Andreas Post authorApril 25, 2019
        
        And was the number of unknown / unmanaged / unsecured SQL 2 or 3 digits?
      - Marcin Gminski April 25, 2019
        
        It was for license audit and definitely more than we would have liked 🙂
      - Marcin Gminski April 25, 2019
        
        The benefit and the main reason for nmap was the performance. Nothing compared to it.
      - Andreas Post authorApril 25, 2019
        
        Totally agree! Nmap is a tool every DBA should know how to use
Hiram Fleitas April 25, 2019 Reply

This is awesome! Thx
Yuri April 25, 2019 Reply

Thank you! Very useful! Could you share the css file please?
wondi April 25, 2019 Reply

awesome
St0zM4c April 26, 2019 Reply

Thank you! I like it indeed very useful! Could you please share the css file?
- Andreas Post authorApril 27, 2019 Reply
  
  Hey, thanks everyone for your interest! A simplified version of the css that you can customize has been uploaded to the GitHub repository.
AUGUST SPIER April 28, 2019 Reply

I inherited a complicated, undisciplined implementation SQL Server. I found Active Directory to be helpful enumerating SQL Server names. Try:

$servers = Get-ADComputer -Filter -SearchBase …
Guenter April 30, 2019 Reply

Great Script!

Would be even more useful to us, if the Build Number could be included. Like 13.0.5026.0

Best regards
- Andreas Post authorApril 30, 2019 Reply
  
  Guenter, the build number can be easily included:
  connect-dbaInstance YourInstance | Select ComputerName, BuildNumber, VersionString
  
  if you just need the build number, use the property “BuildNumber”. If you want the full version number, use property “VersionString”.
  and then include it in your output 🙂
Guenter April 30, 2019 Reply

Andreas, I just added it to your existing configuration: …VersionString, ProductLevel, Edition,…

Works as expected, thanks a lot for your quick response!

SQL Version VersionString ProductLevel
SQL 2017 14.0.3035.2 RTM
Greg May 2, 2019 Reply

Hello All,
I didn’t use a Server List (Txt File). As I had a direct access to the SCCM Database of our company, I used it to list all the Server hosting a SQL Server Instance (filtering on the Express Editions as those are not managed by the DBA Team).
Obviously to have the SQL Server inventory in SCCM you need to implement it on your SCCM Server (https://sccm-zone.com/sql-version-detection-and-report-sccm-2012-r2-12f299b5e63b).
Then you can build the query easily to retrieve only the Server List.
Raj May 19, 2019 Reply

Awesome and thank you for the neat explanation, I going to give it a try at my current work and post here when I done.
james June 12, 2019 Reply

Thanks for this awesome script.
- Andreas Post authorJune 13, 2019 Reply
  
  Thanks James, glad you find it useful
David Waller June 18, 2019 Reply

I am trying this out and got it working. I am having an issue with the CSS.txt file. Did you all ready have one or do we create our own?

Thanks
- Andreas Post authorJune 18, 2019 Reply
  
  David, the link to github contains a very simple css. Feel free to adapt it to your own liking. The focus of the post was on how to get the data, not on design 😉
  - David Waller June 18, 2019 Reply
    
    Thanks for the quick reply….
David Waller June 18, 2019 Reply

How would you send this in an email? I could setup a job to run it.

Thanks
Jan Debonnet July 3, 2019 Reply

Thank you, this was very useful. I just changed the html/css to this ‘Out-HtmlView’ which I think is very useful: https://evotec.xyz/out-htmlview-html-alternative-to-out-gridview/
Eric September 3, 2019 Reply

Very helpfull,
could i have an example of your css.txt file, i don’t know css…

best regards

Eric
- Andreas Post authorSeptember 5, 2019 Reply
  
  Eric, if you follow the link to the GitHub repo at the end of the article, you will find a default css file
  - Eric September 5, 2019 Reply
    
    great thanks, i need to change my glasses by a loope 😉
BK December 11, 2019 Reply

Hi Eric, Thank you for providing such great stuff. Can we insert the data into SQL table and then send a email to DBA team .
- Andreas Post authorDecember 16, 2019 Reply
  
  BK, yes. Absolutely
Cor Perlee December 24, 2019 Reply

Hi Andreas

Love your script!
Is there a way to get the clustername and Roles of ‘ normal’ SQL clusters in stead of just AG’s?

Best regards,

Cor Perlee
- Andreas Post authorDecember 24, 2019 Reply
  
  Hi Cor,
  
  thanks and a Merry Christmas to you!
  Sure there is. Start with the Get-DbaWsfcCluster and Get-DbaWsfcRole commands, they will give you most of what you need.
  Best regards
  Andreas
Cor Perlee January 2, 2020 Reply

Thanks Andreas!

Happy New Year!

Best regards,

Cor
Cor Perlee January 2, 2020 Reply

Hi Andreas,

I am trying to incorporate the Get-DbaWsfcRole command in the script but having no succes.
Could you provide an example of this?

Best regards,

Cor Perlee
garrett devine January 30, 2020 Reply

is it possible to write this using sql authentication? I tried this

$Credential = Get-Credential sa
….
$rawData = $ServerList | Connect-DbaInstance -SqlCredential $Credential

but it does not like the “-SqlCredential $Credential” part
Al March 11, 2020 Reply

Hi Andreas,

Very nice script, is it possible to add disk capacity information?