Monthly Archives: October 2017

New DBA Inventory – Server details

Posted on by
5

This is a multi-part series on my newly created DBA Inventory system. The main page with a table of contents to link to all the posts is located here

This post will detail the start of the Inventory database schema and the powershell script(s) that poll at the Server level, to get things like number of CPUs, Cores, Memory, OS Version, Bios, Virtualization, etc.

In the Inventory database there is a table named ServerDetails. This table holds the above information. There is also a ServerDetails_Stage table that is where the powershell scripts load the data and at the end of the execution a Merge statement is run to insert new or update existing. This is something I want to enhance at some point.

There is a table named “ClusterServers” which maps the ServerName(s) to a ClusterName. About that, we use Active/Passive clusters exclusively, where all instances are on one node and the other node is there to cover any crashes on the active node, and also to speed up Windows patching. There is a Server Name column in both tables.

There is also an InstanceDetails table (and corresponding _Stage table). This table will be detailed further in the next article, but it contains details about every instance. InstanceName is the key here and is a combo of the server name and named instance, or just the server name in the case of a default instance.

There is also a ServerInstances table, which maps the InstanceDetails table to the ServerDetails table, since a single Server could have (and in our case often does) have multiple instances on it. Our clusters are all multi-instance servers as well, and here I map the instance names to the cluster name, and then use the ClusterServers to get to the physical nodes.

The aforementioned powershell script runs once daily via a SQL Agent job on the server hosting the Inventory database, and uses a proxy account designed to query WMI on the OS and also to be used to query inside the SQL Server via query statements or to use SMO calls.

Here’s the script to create these tables — Inventory_ServerDetailsSchema

The powershell script to poll and insert the data into ServerDetails_STAGE and then merge the results to the ServerDetails table.

[void] [System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SMO")

if (Get-PSSnapin -Registered -Name SqlServerCmdletSnapin100 -ErrorAction SilentlyContinue )
{
	Add-PSSnapin SqlServerCmdletSnapin100 -ErrorAction SilentlyContinue
	Add-PSSnapin SqlServerProviderSnapin100 -ErrorAction SilentlyContinue
	import-module "C:\DBATools\dbatoolsPost2008.psm1"  -ErrorAction SilentlyContinue
}
elseif (Get-Module sqlps -ListAvailable -ErrorAction SilentlyContinue)
{
	Import-Module sqlps -ErrorAction SilentlyContinue
	import-module "C:\DBATools\dbatoolsPost2008.psm1"  -ErrorAction SilentlyContinue
}	
else
{
	import-module "C:\DBATools\dbatoolsPre2008.psm1"  -ErrorAction SilentlyContinue
}

Import-Module "C:\DBATools\DBATOOLSCommon.psm1"

$dbaToolsServer = "CS-SQL-INFRAMONITORING-US-DEV-001"
$inventoryDB = "Inventory"

Function Get-ServerInfo ($serverName)
{ 

	$serverDetails = New-Object System.Data.DataTable
	[void]$serverDetails.Columns.Add("ServerName", "System.String")
	[void]$serverDetails.Columns.Add("Manufacturer", "System.String")
	[void]$serverDetails.Columns.Add("Model", "System.String")
	[void]$ServerDetails.Columns.Add("VM", "System.Boolean")
	[void]$serverDetails.Columns.Add("TotalMemory", "System.String")
	[void]$serverDetails.Columns.Add("BiosDescription", "System.String")
	[void]$serverDetails.Columns.Add("BiosVersion", "System.String")
	[void]$serverDetails.Columns.Add("BiosSerial", "System.String")
	[void]$serverDetails.Columns.Add("OSName", "System.String")
	[void]$serverDetails.Columns.Add("Architecture", "System.String")
	[void]$serverDetails.Columns.Add("Processors", "System.String")
	[void]$serverDetails.Columns.Add("Cores", "System.String")

	$totCores=0
	[wmi]$sysInfo = get-wmiobject Win32_ComputerSystem -Namespace "root\CIMV2" -ComputerName $serverName
	[wmi]$bios = Get-WmiObject Win32_BIOS -Namespace "root\CIMV2" -computername $serverName
	[wmi]$os = Get-WmiObject Win32_OperatingSystem -Namespace "root\CIMV2" -Computername $serverName
	[array]$procs = Get-WmiObject Win32_Processor -Namespace "root\CIMV2" -Computername $serverName
	#[array]$mem = Get-WmiObject Win32_PhysicalMemory -Namespace "root\CIMV2" -ComputerName $serverName
	#[array]$nic = Get-WmiObject Win32_NetworkAdapterConfiguration -Namespace "root\CIMV2" -ComputerName $serverName | where{$_.IPEnabled -eq "True"}

	foreach ($cores in $procs.NumberOfCores)
	{
		$totCores += $cores
	}
	$vm = $FALSE
	if ($sysInfo.Model -like "*Virtual*") { $vm = $TRUE }

	$newRow = $serverDetails.NewRow()
	$newRow["ServerName"]=$serverName
	$newRow["Manufacturer"]=$sysInfo.Manufacturer;
	$newRow["Model"]=$sysInfo.Model;
	$newRow["VM"]=$vm
	$newRow["TotalMemory"]=$sysInfo.TotalPhysicalMemory;
	$newRow["BiosDescription"]=$bios.Description;
	$newRow["BiosVersion"]=$bios.SMBIOSBIOSVersion+"."+$bios.SMBIOSMajorVersion+"."+$bios.SMBIOSMinorVersion;
	$newRow["BiosSerial"]=$bios.SerialNumber;
	$newRow["OSName"]=$os.Name.Substring(0,$os.Name.IndexOf("|") -1);
	$newRow["Architecture"]=$os.OSArchitecture;
	$newRow["Processors"]=@($procs).count;
	$newRow["cores"]=$totCores;
	$serverDetails.Rows.Add($newRow)

	#$serverDetails
	if ($serverDetails.Rows.count -gt 0) {
	# create the connection for the bulk loads
		$connString = "Data Source=" + $dbaToolsServer + "; Initial Catalog=$inventoryDB; Integrated Security=True; Application Name=GetServerInfo.ps1; "
		$sqlConn = New-Object System.Data.SqlClient.SqlConnection $connString
		$sqlConn.Open()

		try
		{
		# upload the table to DBATools
			$bulkCopy = New-Object System.Data.SqlClient.SqlBulkCopy($sqlConn)
			$bulkCopy.DestinationTableName = "ServerDetails_STAGE"
			$bulkCopy.WriteToServer($serverDetails)
		}
		catch [System.Exception]
		{
			$errVal = $_.Exception.GetType().FullName + " - " + $_.FullyQualifiedErrorID
			Write-Host "Bulkcopy Error $serverName"
			Write-Host $errVal
			$errCount ++
		}
	}
 
}


###########################################################################################
#
#		MAIN
#
###########################################################################################

$startDate = get-date
$runDt = Get-Date -format "yyyy-MM-dd HH:mm:00"

QuerySql $dbaToolsServer $inventoryDB "TRUNCATE TABLE [ServerDetails_STAGE]"

$sqlQuery = @"
	SELECT [ServerName]
	FROM [ServerDetails]
	WHERE Retired= 0
	AND DomainJoined = 1
"@

$servers = QuerySql $dbaToolsServer $inventoryDB $sqlQuery 60000

foreach ($serverNameRow in $servers) 
{
	$serverName = $serverNameRow[0]
	Get-ServerInfo $serverName
}

$sqlQuery = "
BEGIN TRAN
MERGE ServerDetails as T
USING ServerDetails_STAGE as S
ON (T.ServerName = S.ServerName)
WHEN NOT MATCHED BY TARGET
	THEN INSERT 
	(
		  [Servername]
		, [Manufacturer]
		, [Model]
		, [TotalMemory]
		, [BiosDescription]
		, [BiosVersion]
		, [BiosSerial]
		, [OSName]
		, [Architecture]
		, [Processors]
		, [Cores]
	) VALUES (
		  S.[Servername]
		, S.[Manufacturer]
		, S.[Model]
		, S.[TotalMemory]
		, S.[BiosDescription]
		, S.[BiosVersion]
		, S.[BiosSerial]
		, S.[OSName]
		, S.[Architecture]
		, S.[Processors]
		, S.[Cores]
	)
WHEN MATCHED AND S.Manufacturer is not null
	THEN UPDATE 
	SET T.[Servername]		= S.[Servername], 	
		T.[Manufacturer]	= S.[Manufacturer],		
		T.[Model]		= S.[Model],				
		T.[TotalMemory]		= S.[TotalMemory], 	
		T.[BiosDescription]	= S.[BiosDescription],		
		T.[BiosVersion]		= S.[BiosVersion],				
		T.[BiosSerial]		= S.[BiosSerial],				
		T.[OSName]		= S.[OSName],				
		T.[Architecture]	= S.[Architecture],		
		T.[Processors]		= S.[Processors],				
		T.[Cores]		= S.[Cores],
		T.[Mod_User]		= 'Inventory Script Update',
		T.[Mod_Dt]		= getdate()		
		;
COMMIT TRAN
"

QuerySql $dbaToolsServer $inventoryDB $sqlQuery 60000

$endDate = get-date
$sqlQuery = "INSERT INTO MonitoringJobStatus 
		(JOBNAME, STATUS, STARTDT, ENDDT, RANGESTARTDT, RANGEENDDT, ERROR)
		VALUES
		('ServerDetails','C','$startDate', '$endDate', '', '', '')"



QuerySql $dbaToolsServer $inventoryDB $sqlQuery 60000

exit

To talk through it quickly…
DBATools is what I call the DBAHelper database we install on every instance, and every installation gets a DBATools folder in the OS that contains helper scripts, powershell scripts, etc. It has NO relationship to DBATools.io (a great set of Powershell scripts with an overlap with the name I’ve used internally for about 3 years now).

In the following code base, we connect to the Inventory database and clear out the STAGE table. I then query the ServerDetails for a list of ServerName to poll this iteration. In the Foreach loop, I pass the server name to the function Get-ServerInfo which calls out to the server and polls the needed data.

BTW, $dbaToolsServer is defined near the head of the script as “CS-SQL-INFRAMONITORING-US-DEV-001” which is a CNAME alias to the server hosting the Inventory database. I use CNAME’s very often for connection string alias’s when helping to set up new applications – rather than give the app owners the NetBios name of the server. If we want to move databases to a new instance, we can take an outage and update the DNS record rather than go in and modify configuration strings or code. Back to the code breakout…

startDate = get-date
$runDt = Get-Date -format "yyyy-MM-dd HH:mm:00"

QuerySql $dbaToolsServer $inventoryDB "TRUNCATE TABLE [ServerDetails_STAGE]"

$sqlQuery = @"
	SELECT [ServerName]
	FROM [ServerDetails]
	WHERE Retired= 0
	AND DomainJoined = 1
"@

$servers = QuerySql $dbaToolsServer $inventoryDB $sqlQuery 60000

foreach ($serverNameRow in $servers) 
{
	$serverName = $serverNameRow[0]
	Get-ServerInfo $serverName
}

In Set-ServerInfo, I build a DataTable named $serverDetails to hold the returned data from the server. I use WMI calls to get the system info, the bios, the OS, and the processor data.

	$totCores=0
	[wmi]$sysInfo = get-wmiobject Win32_ComputerSystem -Namespace "root\CIMV2" -ComputerName $serverName
	[wmi]$bios = Get-WmiObject Win32_BIOS -Namespace "root\CIMV2" -computername $serverName
	[wmi]$os = Get-WmiObject Win32_OperatingSystem -Namespace "root\CIMV2" -Computername $serverName
	[array]$procs = Get-WmiObject Win32_Processor -Namespace "root\CIMV2" -Computername $serverName

I then perform some math on the number of cores, and if the server is a VM. I add that data into the data table. Finally, I insert the data into the ServerDetails_STAGE table… I plan on pulling this functionality out of here and put it in the main body of the script…. rather than connect to Inventory and write one row of data for every server on the list. Later on you’ll see a script that I wrote more recently that does exactly this.

	if ($serverDetails.Rows.count -gt 0) {
	# create the connection for the bulk loads
		$connString = "Data Source=" + $dbaToolsServer + "; Initial Catalog=$inventoryDB; Integrated Security=True; Application Name=GetServerInfo.ps1; "
		$sqlConn = New-Object System.Data.SqlClient.SqlConnection $connString
		$sqlConn.Open()

		try
		{
		# upload the table to DBATools
			$bulkCopy = New-Object System.Data.SqlClient.SqlBulkCopy($sqlConn)
			$bulkCopy.DestinationTableName = "ServerDetails_STAGE"
			$bulkCopy.WriteToServer($serverDetails)
		}
		catch [System.Exception]
		{
			$errVal = $_.Exception.GetType().FullName + " - " + $_.FullyQualifiedErrorID
			Write-Host "Bulkcopy Error $serverName"
			Write-Host $errVal
			$errCount ++
		}
	}

Finally, once the list of servers is complete, I perform my merge statement to refresh / insert into the ServerDetails table from the ServerDetails_STAGE table. I also update some records in a Job status table.

$sqlQuery = "
BEGIN TRAN
MERGE ServerDetails as T
USING ServerDetails_STAGE as S
ON (T.ServerName = S.ServerName)
WHEN NOT MATCHED BY TARGET
	THEN INSERT 
	(
		  [Servername]
		, [Manufacturer]
		, [Model]
		, [TotalMemory]
		, [BiosDescription]
		, [BiosVersion]
		, [BiosSerial]
		, [OSName]
		, [Architecture]
		, [Processors]
		, [Cores]
	) VALUES (
		  S.[Servername]
		, S.[Manufacturer]
		, S.[Model]
		, S.[TotalMemory]
		, S.[BiosDescription]
		, S.[BiosVersion]
		, S.[BiosSerial]
		, S.[OSName]
		, S.[Architecture]
		, S.[Processors]
		, S.[Cores]
	)
WHEN MATCHED AND S.Manufacturer is not null
	THEN UPDATE 
	SET T.[Servername]		= S.[Servername], 	
		T.[Manufacturer]	= S.[Manufacturer],		
		T.[Model]		= S.[Model],				
		T.[TotalMemory]		= S.[TotalMemory], 	
		T.[BiosDescription]	= S.[BiosDescription],		
		T.[BiosVersion]		= S.[BiosVersion],				
		T.[BiosSerial]		= S.[BiosSerial],				
		T.[OSName]		= S.[OSName],				
		T.[Architecture]	= S.[Architecture],		
		T.[Processors]		= S.[Processors],				
		T.[Cores]		= S.[Cores],
		T.[Mod_User]		= 'Inventory Script Update',
		T.[Mod_Dt]		= getdate()		
		;
COMMIT TRAN
"

QuerySql $dbaToolsServer $inventoryDB $sqlQuery 60000

$endDate = get-date
$sqlQuery = "INSERT INTO MonitoringJobStatus 
		(JOBNAME, STATUS, STARTDT, ENDDT, RANGESTARTDT, RANGEENDDT, ERROR)
		VALUES
		('ServerDetails','C','$startDate', '$endDate', '', '', '')"



QuerySql $dbaToolsServer $inventoryDB $sqlQuery 60000

exit

Brand new SQL Server Inventory system

Posted on by
3

I am back working as a DBA after trying a supervisory role as an IT Quality guy. We won’t get into that. Instead, I’m starting a new way for me and the DBA Team to inventory and track our SQL Servers, Instances, Databases, Disks, Data Files, SPN’s, Logins, Connections, Applications, and other great information (such as Licensing!).

I had previously written a number of Powershell scripts that polled the SQL Servers in my charge, and dumped the data to a set of Excel files. I have since started a new iteration that polls a larger set of details and stores them in a SQL Database. Inventory 2.0 if you will

This post will be the master post – like a table of contents where I link to each of the parts of the system. The goal of this system is to track the inventory, it is not designed or intended as a monitoring, performance troubleshooting, or alerting system – there are many free and paid for systems for that. We use Solarwinds where I work and I have Solarwinds DPA to do in depth analysis of those details and to fire alerts.

The requirements as they are today:

  1. Centralized system to track any Servers with SQL installed including VM’s, Physical standalone servers, and HA Clusters.
  2. A Web front end for viewing and some limited data entry, modification abilities (we’re DBA’s that use this, connecting and firing off a few SP’s to manipulate data is trivial for us).
  3. A set of Powershell scripts to collect the data.

About our environment. We have about 100 servers and approximately 180 instances. These are scattered around the globe, in Americas North and South, in the UK and in Europe, in Russia, China, South Africa, Australia, India, and China. All this for the most part is connected via a massive MPLS network. We also have a few servers in Azure and two in a Colo datacenter. The Azure DB’s are not connected to our network, we use a software VPN client to connect to them (this will change as some point). I do not have a solution yet to actively poll those systems. The two servers in our Colo are connected to our MPLS network and I can poll those regularly. They are on a different domain and there is no trust to our internal domain. To address that I do have the concept of creating and securely storing “Alternate SQL Credentials” to connect and poll the SQL Servers on those instances using a SQL Login.

On each SQL Instance that we support, there is a DBA helper database that performs all the needed maintenance, including Backups and retention management, Index and Stats, an Implicit Conversion tracker, DBCC Check DB handling and some other things. It also performs some local Inventory aggregations, such as calling out to Powershell to poll the physical disks, Cluster details, and Login and user tracking.

Coming soon will be the schema for the Inventory database, and the first couple of powershell scripts that poll the Server via WMI, and the instance via WMI.

Here’s the first post, which talks about the first set of the Inventory Schema and a PowerShell script to poll for Server Details.
Here’s #2, which talks about the physical disk details via Powershell and WMI calls for each server in the Inventory System.
Here’s #3, which talks about the script to poll each instance via SMO.
Here’s #4, which talks about the script to poll database details from each instance.