New DBA Inventory – Instance Details

Posted on by

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 Instance level, to get things like number of SQL Version, Edition, Service Account, Service Packs, Instance Root, Collation, Default Database and TLOG paths, etc.

There are two key tables, a main table and a STAGE table. The stage table is loaded with the nightly polling Powershell script and then a merge statement is run to insert any new or update any thing that changes. Linked here is the same script from the previous post as it has the InstanceDetails tables and the ServerInstances table which relates multiple instances to a single server or cluster.

Here’s the script to create these tables — Inventory_ServerDetailsSchema

There is also a new table used in this script to get Alternate Credentials – basically we store the netbios name of the server (SourceServerNetBIOS) the script is running on, an instance name, the username, and a secure string for the password. Using powershell’s functionality, only the same computer and same user can decrypt that secure string and use it. Since I run the Inventory system on a cluster, there are two physical computers that could run these scripts.

USE [Inventory]
GO

CREATE TABLE [dbo].[AltSqlCredentials](
	[id] [int] IDENTITY(1,1) NOT NULL,
	[instancename] [varchar](128) NOT NULL,
	[uname] [varchar](64) NOT NULL,
	[pword] [varchar](512) NOT NULL,
	[moddt] [datetime] NULL,
	[moduser] [varchar](50) NULL,
	[SourceServerNetBIOS] [varchar](128) NOT NULL,
 CONSTRAINT [PK_AltSqlCredentials_InstanceName] PRIMARY KEY CLUSTERED 
(
	[instancename] ASC,
	[SourceServerNetBIOS] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]
) ON [PRIMARY]

GO

ALTER TABLE [dbo].[AltSqlCredentials] ADD  CONSTRAINT [DF_AltSqlCredentials_Mod_DT]  DEFAULT (getdate()) FOR [moddt]
GO

ALTER TABLE [dbo].[AltSqlCredentials] ADD  CONSTRAINT [DF_AltSqlCredentials_ModUser]  DEFAULT (suser_sname()) FOR [moduser]
GO

Here’s the powershell script, using SMO to pull it’s data from each instance.

[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-InstanceInfo ($ServerName, $altCredential, $runDt)
{
	$serverName
	$server = New-Object -typeName Microsoft.SqlServer.Management.Smo.Server -argumentList "$serverName"

	if ($altCredential -ne $null) {
		write-host "Using Alt Credentials!"
		$server.ConnectionContext.LoginSecure=$false
		$user=$altCredential.UserName
		$pass=$altCredential.Password

		$server.ConnectionContext.set_Login($user)
		$server.ConnectionContext.set_SecurePassword($pass)
	}

	if ($server.count -gt 0) 
	{
    
		switch ($server.version.Major) 
		{
			8 {$version = "SQL Server 2000"}
			9 {$version = "SQL Server 2005"}
			10 {if ($server.version.Minor -eq 0 ) 
				{
					$version = "SQL Server 2008"
				} else {
					$version = "SQL Server 2008 R2"
				}
			}
			11 {$version = "SQL Server 2012"}
			12 {$version = "SQL Server 2014"}
			13 {$version = "SQL Server 2016"}
			default {$version = "Unknown"}
	       }
		if ($serverName.contains("\")) 
		{
        		$currName = $serverName.split("\")
			$name = $currName[0]
		} else {$name = $serverName}

		$instance=$name
		$platform = $server.Information.Platform.toString()
		$osVersion = $server.Information.OsVersion
		$clustered=0
		$fullText=0
		if ($server.IsClustered -eq $True) {$clustered=1} 
		if ($server.IsFullTextInstalled -eq $True) {$fullText=1}
		[int]$memory = $server.Information.PhysicalMemory
		[int]$processors = $server.Processors
		$serviceAccount = $server.ServiceAccount
		$sqlVersion = $server.Information.VersionString.toString()
		$edition = $server.Information.Edition
		$productLevel = $server.Information.ProductLevel.toString()
		$affinityType = 0
		#$server.AffinityInfo.AffinityType'
		$collation = $server.Collation
		$rootDirectory = $server.Information.RootDirectory.toString()
		$masterDBPath = $server.Information.MasterDBPath
		$installDataDirectory = $server.InstallDataDirectory
		$installSharedDirectory = $server.InstallSharedDirectory
		$errorLogPath = $server.ErrorLogPath
		$defaultFile = $server.DefaultFile
		$defaultLog = $server.DefaultLog
		$backupDirectory = $server.BackupDirectory

		$sqlQuery = "
			INSERT into [InstanceDetails_STAGE] 
			(	InstanceName,
				Platform,
				OSVersion,
				IsClustered,
				Processors,
				Memory,
				Instance,
				ServiceAccount,
				BuildNumber,
				Edition,
				SQLVersion,
				SPLevel,
				AutoAffinity,
				Collation,
				FullText,
				RootPath,
				MasterDBPath,
				InstallPath,
				SharedPath,
				ErrorLogPath,
				DefaultDataPath,
				DefaultLogPath,
				BackupPath 
			) values (
    				'$serverName',
				'$platform',
				'$osVersion',
				'$clustered',
				'$processors',
				'$memory',
				'$instance',
				'$serviceAccount',
				'$sqlVersion',
				'$edition',
				'$version',
				'$productLevel',
				'$affinityType',
				'$collation',
				'$fullText',
				'$rootDirectory',
				'$masterDBPath',
				'$installDataDirectory',
				'$installSharedDirectory',
				'$errorLogPath',
				'$defaultFile',
				'$defaultLog',
				'$backupDirectory'
			)
		"
		#$sqlQuery
		QuerySql $dbaToolsServer $inventoryDB $sqlQuery 60000        
	}
}
 
###########################################################################################
#
#		MAIN
#
###########################################################################################

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

QuerySql $dbaToolsServer $inventoryDB "TRUNCATE TABLE [InstanceDetails_STAGE]" 60000

$sqlQuery = @"
		SELECT ID.InstanceName, 
			[UName] = AC.Uname,
			[PWord] = AC.PWord
		FROM InstanceDetails ID
		LEFT OUTER JOIN [AltSqlCredentials] AC
			ON ID.InstanceName = AC.instancename
			and AC.[SourceServerNetBIOS] = (SELECT SERVERPROPERTY('ComputerNamePhysicalNetBIOS'))
		WHERE ID.Retired = 0
		AND ID.LicenseOnly = 0

"@

$instances = QuerySql $dbaToolsServer $inventoryDB $sqlQuery 60000

foreach ($instance in $instances) 
{
	#$serverName = $serverNameRow | % { $_.instancename} #Convert the datatablerow into a string
	$instanceName = $instance[0]
	$user = $instance[1]
	$pass = $instance[2]

	$altCredential = $null
	if (-not(([DBNull]::Value).Equals($user)) ) {
		write-host "Found alt credential for $instanceName"
		$pass = $pass|ConvertTo-SecureString 
		$altCredential = New-Object -TypeName System.Management.Automation.PsCredential `
			-ArgumentList $user, $pass
	}

	Get-InstanceInfo $instanceName $altCredential $runDt
}

$sqlQuery = "BEGIN TRAN
MERGE InstanceDetails as T
USING InstanceDetails_STAGE as S
ON (T.InstanceName = S.InstanceName)
WHEN NOT MATCHED BY TARGET
	THEN INSERT 
	(
		[InstanceName] , 
		[Platform] ,
		[OSVersion] ,
		[IsClustered] , 
		[Processors] ,
		[Memory] ,
		[Instance] , 
		[ServiceAccount] ,
		[BuildNumber] ,
		[Edition] ,
		[SQLVersion] ,
		[SPLevel] ,
		[AutoAffinity] ,
		[Collation] ,
		[FullText] ,
		[RootPath] ,
		[MasterDBPath] ,
		[InstallPath] ,
		[SharedPath] ,
		[ErrorLogPath] , 
		[DefaultDataPath] ,
		[DefaultLogPath] ,
		[BackupPath]
	) VALUES (
		S.[InstanceName] , 
		S.[Platform] ,
		S.[OSVersion] ,
		S.[IsClustered] , 
		S.[Processors] ,
		S.[Memory] ,
		S.[Instance] , 
		S.[ServiceAccount] ,
		S.[BuildNumber] ,
		S.[Edition] ,
		S.[SQLVersion] ,
		S.[SPLevel] ,
		S.[AutoAffinity] ,
		S.[Collation] ,
		S.[FullText] ,
		S.[RootPath] ,
		S.[MasterDBPath] ,
		S.[InstallPath] ,
		S.[SharedPath] ,
		S.[ErrorLogPath] , 
		S.[DefaultDataPath] ,
		S.[DefaultLogPath] ,
		S.[BackupPath]
	)
WHEN MATCHED AND S.SQLVersion != 'Unknown' or S.Edition != ''
	THEN UPDATE 
	SET T.[InstanceName] 		= S.[InstanceName], 	
		T.[Platform]		= S.[Platform],		
		T.[OSVersion]		= S.[OSVersion],		
		T.[IsClustered] 	= S.[IsClustered], 	
		T.[Processors]		= S.[Processors],		
		T.[Memory]		= S.[Memory],			
		T.[Instance] 		= S.[Instance], 		
		T.[ServiceAccount]	= S.[ServiceAccount],	
		T.[BuildNumber]		= S.[BuildNumber],		
		T.[Edition]		= S.[Edition],			
		T.[SQLVersion]		= S.[SQLVersion],		
		T.[SPLevel]		= S.[SPLevel],			
		T.[AutoAffinity]	= S.[AutoAffinity],	
		T.[Collation]		= S.[Collation],		
		T.[FullText]		= S.[FullText],		
		T.[RootPath]		= S.[RootPath],		
		T.[MasterDBPath]	= S.[MasterDBPath],	
		T.[InstallPath]		= S.[InstallPath],		
		T.[SharedPath]		= S.[SharedPath],		
		T.[ErrorLogPath] 	= S.[ErrorLogPath], 	
		T.[DefaultDataPath]	= S.[DefaultDataPath],	
		T.[DefaultLogPath]	= S.[DefaultLogPath],	
		T.[BackupPath]		= S.[BackupPath],
		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
		('InstanceDetails','C','$startDate', '$endDate', '', '', '')"



QuerySql $dbaToolsServer $inventoryDB $sqlQuery 60000

exit

In the main script we query Inventory to generate the list of instances to query… again with the Alternate Credentials if needed. I also have the concept of “License Only” which are ones that we track for licensing purposes but we do not support. Perhaps it’s a vendor based solution, or some unknown install. The point is we don’t care that much about it, so don’t poll it.

		SELECT ID.InstanceName, 
			[UName] = AC.Uname,
			[PWord] = AC.PWord
		FROM InstanceDetails ID
		LEFT OUTER JOIN [AltSqlCredentials] AC
			ON ID.InstanceName = AC.instancename
			and AC.[SourceServerNetBIOS] = (SELECT SERVERPROPERTY('ComputerNamePhysicalNetBIOS'))
		WHERE ID.Retired = 0
		AND ID.LicenseOnly = 0

For each instance returned, parse and prep the credentials if there are any, and call Get-InstanceInfo to capture and then load the instance information we want.


foreach ($instance in $instances) 
{
	#$serverName = $serverNameRow | % { $_.instancename} #Convert the datatablerow into a string
	$instanceName = $instance[0]
	$user = $instance[1]
	$pass = $instance[2]

	$altCredential = $null
	if (-not(([DBNull]::Value).Equals($user)) ) {
		write-host "Found alt credential for $instanceName"
		$pass = $pass|ConvertTo-SecureString 
		$altCredential = New-Object -TypeName System.Management.Automation.PsCredential `
			-ArgumentList $user, $pass
	}

	Get-InstanceInfo $instanceName $altCredential $runDt
}

Inside the Get-InstanceInfo function, we call SMO, and use the alternate credentials if needed.

	$server = New-Object -typeName Microsoft.SqlServer.Management.Smo.Server -argumentList "$serverName"

	if ($altCredential -ne $null) {
		write-host "Using Alt Credentials!"
		$server.ConnectionContext.LoginSecure=$false
		$user=$altCredential.UserName
		$pass=$altCredential.Password

		$server.ConnectionContext.set_Login($user)
		$server.ConnectionContext.set_SecurePassword($pass)
	}

Then we parse the results from SMO and insert the row into the InstanceDetails_Stage

	if ($server.count -gt 0) 
	{
    
		switch ($server.version.Major) 
		{
			8 {$version = "SQL Server 2000"}
			9 {$version = "SQL Server 2005"}
			10 {if ($server.version.Minor -eq 0 ) 
				{
					$version = "SQL Server 2008"
				} else {
					$version = "SQL Server 2008 R2"
				}
			}
			11 {$version = "SQL Server 2012"}
			12 {$version = "SQL Server 2014"}
			13 {$version = "SQL Server 2016"}
			default {$version = "Unknown"}
	       }
		if ($serverName.contains("\")) 
		{
        		$currName = $serverName.split("\")
			$name = $currName[0]
		} else {$name = $serverName}

		$instance=$name
		$platform = $server.Information.Platform.toString()
		$osVersion = $server.Information.OsVersion
		$clustered=0
		$fullText=0
		if ($server.IsClustered -eq $True) {$clustered=1} 
		if ($server.IsFullTextInstalled -eq $True) {$fullText=1}
		[int]$memory = $server.Information.PhysicalMemory
		[int]$processors = $server.Processors
		$serviceAccount = $server.ServiceAccount
		$sqlVersion = $server.Information.VersionString.toString()
		$edition = $server.Information.Edition
		$productLevel = $server.Information.ProductLevel.toString()
		$affinityType = 0
		#$server.AffinityInfo.AffinityType'
		$collation = $server.Collation
		$rootDirectory = $server.Information.RootDirectory.toString()
		$masterDBPath = $server.Information.MasterDBPath
		$installDataDirectory = $server.InstallDataDirectory
		$installSharedDirectory = $server.InstallSharedDirectory
		$errorLogPath = $server.ErrorLogPath
		$defaultFile = $server.DefaultFile
		$defaultLog = $server.DefaultLog
		$backupDirectory = $server.BackupDirectory

		$sqlQuery = "
			INSERT into [InstanceDetails_STAGE] 
			(	InstanceName,
				Platform,
				OSVersion,
				IsClustered,
				Processors,
				Memory,
				Instance,
				ServiceAccount,
				BuildNumber,
				Edition,
				SQLVersion,
				SPLevel,
				AutoAffinity,
				Collation,
				FullText,
				RootPath,
				MasterDBPath,
				InstallPath,
				SharedPath,
				ErrorLogPath,
				DefaultDataPath,
				DefaultLogPath,
				BackupPath 
			) values (
    				'$serverName',
				'$platform',
				'$osVersion',
				'$clustered',
				'$processors',
				'$memory',
				'$instance',
				'$serviceAccount',
				'$sqlVersion',
				'$edition',
				'$version',
				'$productLevel',
				'$affinityType',
				'$collation',
				'$fullText',
				'$rootDirectory',
				'$masterDBPath',
				'$installDataDirectory',
				'$installSharedDirectory',
				'$errorLogPath',
				'$defaultFile',
				'$defaultLog',
				'$backupDirectory'
			)
		"
		#$sqlQuery
		QuerySql $dbaToolsServer $inventoryDB $sqlQuery 60000        
	}

Finally back in the main function we execute the merge statement to load InstanceDetails

$sqlQuery = "BEGIN TRAN
MERGE InstanceDetails as T
USING InstanceDetails_STAGE as S
ON (T.InstanceName = S.InstanceName)
WHEN NOT MATCHED BY TARGET
	THEN INSERT 
	(
		[InstanceName] , 
		[Platform] ,
		[OSVersion] ,
		[IsClustered] , 
		[Processors] ,
		[Memory] ,
		[Instance] , 
		[ServiceAccount] ,
		[BuildNumber] ,
		[Edition] ,
		[SQLVersion] ,
		[SPLevel] ,
		[AutoAffinity] ,
		[Collation] ,
		[FullText] ,
		[RootPath] ,
		[MasterDBPath] ,
		[InstallPath] ,
		[SharedPath] ,
		[ErrorLogPath] , 
		[DefaultDataPath] ,
		[DefaultLogPath] ,
		[BackupPath]
	) VALUES (
		S.[InstanceName] , 
		S.[Platform] ,
		S.[OSVersion] ,
		S.[IsClustered] , 
		S.[Processors] ,
		S.[Memory] ,
		S.[Instance] , 
		S.[ServiceAccount] ,
		S.[BuildNumber] ,
		S.[Edition] ,
		S.[SQLVersion] ,
		S.[SPLevel] ,
		S.[AutoAffinity] ,
		S.[Collation] ,
		S.[FullText] ,
		S.[RootPath] ,
		S.[MasterDBPath] ,
		S.[InstallPath] ,
		S.[SharedPath] ,
		S.[ErrorLogPath] , 
		S.[DefaultDataPath] ,
		S.[DefaultLogPath] ,
		S.[BackupPath]
	)
WHEN MATCHED AND S.SQLVersion != 'Unknown' or S.Edition != ''
	THEN UPDATE 
	SET T.[InstanceName] 		= S.[InstanceName], 	
		T.[Platform]		= S.[Platform],		
		T.[OSVersion]		= S.[OSVersion],		
		T.[IsClustered] 	= S.[IsClustered], 	
		T.[Processors]		= S.[Processors],		
		T.[Memory]		= S.[Memory],			
		T.[Instance] 		= S.[Instance], 		
		T.[ServiceAccount]	= S.[ServiceAccount],	
		T.[BuildNumber]		= S.[BuildNumber],		
		T.[Edition]		= S.[Edition],			
		T.[SQLVersion]		= S.[SQLVersion],		
		T.[SPLevel]		= S.[SPLevel],			
		T.[AutoAffinity]	= S.[AutoAffinity],	
		T.[Collation]		= S.[Collation],		
		T.[FullText]		= S.[FullText],		
		T.[RootPath]		= S.[RootPath],		
		T.[MasterDBPath]	= S.[MasterDBPath],	
		T.[InstallPath]		= S.[InstallPath],		
		T.[SharedPath]		= S.[SharedPath],		
		T.[ErrorLogPath] 	= S.[ErrorLogPath], 	
		T.[DefaultDataPath]	= S.[DefaultDataPath],	
		T.[DefaultLogPath]	= S.[DefaultLogPath],	
		T.[BackupPath]		= S.[BackupPath],
		T.[Mod_User]		= 'Inventory Script Update',
		T.[Mod_Dt]		= getdate()	
;
COMMIT TRAN

1 thought on “New DBA Inventory – Instance Details”

  1. Pingback: Brand new SQL Server Inventory system | SQL Blog

Leave a Reply

Your email address will not be published.

CAPTCHA

*

This site uses Akismet to reduce spam. Learn how your comment data is processed.