Ok, I’ve been promising this to people for a while, time to get at it. Brace yourself for a long one.
As enterprise DBA’s, we usually have many servers to manage. Whether it’s around ten, a hundred, or a thousand (or more!), tracking this manually is a bear. However, there are still many shops maintain convoluted spreadsheets and other manual documentation to track their inventory. Manual sucks. As John Sansom(@SQLBrit) says, the best DBAs automate everything, including their server inventories.
There’s a lot of approaches and third party tools you can use for your own environment. In my mind, I’m totally cool with spending the money on a third party tool because of the depth they can provide in my tracking and monitoring. A lot of times, though, we might not have access to those tools or we may need more information than those tools provide. It’s at this point we sit down at our keyboard, crack our fingers, and start banging out some code. Of course, the code I started banging out was Powershell (shocking, I know!).
I didn’t start from scratch, though. When putting my own script together (which we’ll get to later), I found two great resources that got me about 80% of the way:
Allen White(@SQLRunr) – Let PowerShell do an Inventory of your Servers
Colleen Morrow(@ClevelandDBA) – Building A SQL Server Inventory
These are both great resources that will get you started, but I needed a little more. The biggest challenge I had was a need to poll cluster information and collect SQL instance information separate from my machine information. I also wanted to do some consolidation of my data sets. So while I borrowed heavily from both Allen and Colleen, but then molded it to my own purposes.
Process
Before we dig into the code, I first want to touch on the collection process and how I handle it. It’s not complex, but code doesn’t make sense until you understand the “why” of the different components.
What I’m collecting
-
SQL Instance information – Whether it’s an instance living on a cluster node, a physical stand alone, or a VM host, I want the SQL Instance without really caring about the machine it lives on. At least not meshed in directly with the machine, though I DO collect the physical host name so I can identify which machine information I need.
-
Physical Machine information – Since I’m not tying the machine info directly with the SQL Instance collection, I want the physical information for the servers my instances live on. This is where I get everything that isn’t tied directly to the SQL Instance.
How I’m collecting it
-
I’ve got 4 tables, 2 live tables and 2 stage tables, that I use for the collection. Nothing out of the ordinary, but there’s an instance table with a stage table and a machine table with a stage table.
-
I use a Powershell script to perform the collection itself. It uses instance table as its primary driver, where the idea is that I provide the initial population of instance names to track into the table and the inventory process will fully populate the rest of the information.
-
When the Powershell scripts complete, there is a stored procedure it executes to load data from the stage tables into the live tables. The load is simple, where the instance table is updated with data from the stage and the machine information is deleted/replaced.
-
The Powershell script is run by a SQL Agent job running under a specific monitoring Active Directory account created as a credential in SQL Server. The gotcha here is that the monitoring account needs domain access to the clusters and machines it’s going to be querying.
The Code
I won’t list the full scripts here, but you can download and review them here:
Let’s first talk about the dependencies:
-
The SMO, so you’re going to need to have either your script or your profile load the 2008 snap-ins or import the 2012 sqlps module.
-
Chad Miller’s Out-DataTable and Write-DataTable (I combined them into one file, DataTables.ps1).
-
The FailOverClusters module, part of the Remote Admin pack if you’re not running the code on a machine with the Windows Server OS, part of the Fail Over Cluster components if you are.
There are two key functions I use, Get-Instance to gather the SQL instance information and Get-Machine to gather the machine information:
#Collects and returns SQL Instance information
function Get-Instance([string]$instcoll,[int]$id,[string]$name)
{
try
{
$smo = new-object ('Microsoft.SqlServer.Management.Smo.Server') $name
$sname = $smo.NetName
$iname = $smo.InstanceName
if($iname.Length -eq 0 -or $iname -eq $null) { $iname = "MSSQLSERVER" }
$managedcomp = new-object ('Microsoft.SqlServer.Management.Smo.WMI.ManagedComputer') $sname
$output = New-Object System.Object
$port = $managedcomp.ServerInstances[$iname].ServerProtocols["Tcp"].IPAddresses["IPAll"].IPAddressProperties["TcpPort"].Value
$ip = (Test-Connection $sname -count 1).IPV4Address.ToString()
$output | Add-Member -type NoteProperty -name InstanceId -value $id
$output | Add-Member -type NoteProperty -name SQLVersion -value $smo.VersionString
$output | Add-Member -type NoteProperty -name SQLVersionDesc -value $smo.ProductLevel
$output | Add-Member -type NoteProperty -name SQLEdition -value $smo.Edition
$output | Add-Member -type NoteProperty -name MemoryMinMB -value $smo.Configuration.MinServerMemory.RunValue
$output | Add-Member -type NoteProperty -name MemoryMaxMB -value $smo.Configuration.MaxServerMemory.RunValue
$output | Add-Member -type NoteProperty -name MAXDOPVal -value $smo.Configuration.MaxDegreeOfParallelism.RunValue
$output | Add-Member -type NoteProperty -name IP -value $ip
$output | Add-Member -type NoteProperty -name Port -value $port
$output | Add-Member -type NoteProperty -name PhysicalHost -value $smo.ComputerNamePhysicalNetBIOS
return $output
}
catch
{
write-host "Error collecting $name"
return $null
}
}#Get-Instance
#Get host machine information via WMI
function Get-Machine([string]$name,[string]$clst)
{
try
{
$comp = gwmi Win32_ComputerSystem -Computer $name | select Model,Manufacturer,TotalPhysicalMemory
$proc = gwmi Win32_Processor -Computer $name | select NumberOfLogicalProcessors,MaxClockSpeed
$os = gwmi Win32_OperatingSystem -Computer $name | select OSArchitecture,Name,Version,ServicePackMajorVersion,ServicePackMinorVersion
$output = New-Object System.Object
$output | Add-Member -type NoteProperty -name MachineName -value $name
$output | Add-Member -type NoteProperty -name Model -value $comp.Model
$output | Add-Member -type NoteProperty -name Manufacturer -value $comp.Manufacturer
$output | Add-Member -type NoteProperty -name Architechture -value $os.OSArchitecture
$output | Add-Member -type NoteProperty -name PhysicalCPUs -value $(if(!$proc.Length){"1"}else{$proc.Length})
$output | Add-Member -type NoteProperty -name LogicalCPUs -value ($proc | Measure-Object NumberOfLogicalProcessors -sum).Sum
$output | Add-Member -type NoteProperty -name CPUSpeed -value ($proc | Measure-Object MaxClockSpeed -max).Maximum
$output | Add-Member -type NoteProperty -name MaxMemory -value ($comp.TotalPhysicalMemory/1MB)
$output | Add-Member -type NoteProperty -name OSName -value $os.name.split("|")[0]
$output | Add-Member -type NoteProperty -name OsVersion -value $os.Version
$SPMaj = $os.ServicePackMajorVersion
$SPMin = $os.ServicePackMinorVersion
$output | Add-Member -type NoteProperty -name SPVersion -value "$SPMaj.$SPMin"
$output | Add-Member -type NoteProperty -name Cluster -value $clst
return $output
}
catch
{
write-host "Error collecting $name"
return $null
}
}#Get-Machine
Both leverage the SMO and the WMI to get relevant info. I’ll let you sort through the individual elements I’m pulling, it’s fairly standard config info. Data gathering, because I think in terms of tables, is a collection of objects that are essentially rows. I append each new object with all the appropriate properties to a larger collection, then use Write-Datatable to push that data directly into the related stage table.
Look at the entire script for my full process. I’ve been pretty happy with it and it’s been chugging away in my environments now for about 8-9 months without a whole lot of interaction from me. If we stand up a new server, then I just pop that name in the instance table, run the job, and everything gets updated. What made me most proud was that we had a recent meeting with a new VP and pulled up the SSRS report built off of this information to give him an idea of what our environments. His comment was “This is the most documentation I’ve seen since I’ve got here.” The beauty is that it was provided without a whole lot of manual maintenance on my part.
This is very much an evolving work on my part, but hopefully gives you some insight into how I manage my environments. I’ve still got lots of ideas on improvements, including automatic population (or other use) of Central Management Server. If you have any ideas on how this can be improved, I’d love to hear them.
I’m tweeting!