New DBA Inventory – Instance Details

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

New DBA Inventory – Server Disk Info

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

The previous post discussed Server Details, things like Memory, CPU’s, Cores, BIOS, Operating System details using WMI Calls. This next post is another poll of Server level (Operating System) type detail… Disk Info.

This Powershell “poller” grabs disk info such as the Drive Letter, the Disk Model, the Partition, a Description, if it’s a Primary Partition, the Volume Name, if it’s SAN attached, if it’s a Mount Point, Disk Size, Disk Free Space, and a Serial Number. It stores all of this in a history table by an “Audit Date”. That table is named “DiskInfo”!

CREATE TABLE [dbo].[DiskInfo](
	[ServerName] [varchar](256) NOT NULL,
	[DiskName] [varchar](256) NOT NULL,
	[AuditDt] [datetime] NOT NULL,
	[Model] [varchar](256) NULL,
	[DiskPartition] [varchar](256) NULL,
	[Description] [varchar](256) NULL,
	[PrimaryPartition] [bit] NULL,
	[VolumeName] [varchar](256) NULL,
	[Drive] [varchar](128) NULL,
	[SanAttached] [bit] NULL,
	[MountPoint] [bit] NULL,
	[DiskSizeMB] [bigint] NULL,
	[DIskFreeMB] [bigint] NULL,
	[SerialNumber] [varchar](128) NULL,
 CONSTRAINT [PK_DiskInfo] PRIMARY KEY CLUSTERED 
(
	[ServerName] ASC,
	[DiskName] ASC,
	[AuditDt] 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

The powershell itself actually only queries my Helper Database installed on every instance – named DBATools. Each instance has a job that runs nightly to get the local disk information. I had originally worked on a centralized script that polled via WMI but it was slow and cumbersome to run in my environment. This is much faster. So the central polling script is here, and it’s pretty simple.

[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 GetDiskInfo ($serverName, $sourceDB, $altCredential, $runDt)
{

	$DiskInfo = New-Object System.Data.DataTable
	[void]$DiskInfo.Columns.Add("ServerName", "System.String")
	[void]$DiskInfo.Columns.Add("DiskName", "System.String")
	[void]$DiskInfo.Columns.Add("AuditDt", "System.DateTime")
	[void]$DiskInfo.Columns.Add("Model", "System.String")
	[void]$DiskInfo.Columns.Add("DiskPartition", "System.String")
	[void]$DiskInfo.Columns.Add("Description", "System.String")
	[void]$DiskInfo.Columns.Add("PrimaryPartition", "System.Int32")
	[void]$DiskInfo.Columns.Add("VolumeName", "System.String")
	[void]$DiskInfo.Columns.Add("Drive", "System.String")
	[void]$DiskInfo.Columns.Add("SanAttached", "System.Int32")
	[void]$DiskInfo.Columns.Add("MountPoint", "System.Int32")
	[void]$DiskInfo.Columns.Add("DiskSizeMB", "System.String")
	[void]$DiskInfo.Columns.Add("DiskFreeMB", "System.String")
	[void]$DiskInfo.Columns.Add("SerialNumber", "System.String")

	$sqlQuery = "
		SELECT [ServerName]
			  ,[DiskName]
			  ,[AuditDt]
			  ,[Model]
			  ,[DiskPartition]
			  ,[Description]
			  ,[PrimaryPartition]
			  ,[VolumeName]
			  ,[Drive]
			  ,[SanAttached]
			  ,[MountPoint]
			  ,[DiskSizeMB]
			  ,[DIskFreeMB]
			  ,[SerialNumber]
		  FROM  [DiskInfo]
	"

	if ($altCredential -ne $null) {
		write-host "Using Alt Credentials!"

		$user=$altCredential.UserName
		$pass = $altCredential.GetNetworkCredential().Password
		$splat = @{
			UserName = $user
			Password = $pass
			ServerInstance = $serverName
			Database = $sourceDB
			Query = $sqlQuery
		}
		$diskQuery = Invoke-SqlCmd @splat 
	} else {
		$diskQuery = QuerySql $servername $sourceDB $sqlQuery 60000
	}
	foreach ($row in $diskQuery)
	{
			$newRow = $DiskInfo.NewRow()
			$newRow["ServerName"] = $row.Servername
			$newRow["DiskName"] = $row.DiskName
			$newRow["AuditDt"] = [DateTime]$runDt
			$newRow["Model"] = $row.Model
			$newRow["DiskPartition"] = $row.DiskPartition
			$newRow["Description"] = $row.Description
			$newRow["PrimaryPartition"] = $row.PrimaryPartition
			$newRow["VolumeName"] = $row.VolumeName
			$newRow["Drive"] = $row.Drive
			$newRow["SanAttached"] = $row.SanAttached
			$newRow["MountPoint"] = $row.MountPoint
			$newRow["DiskSizeMB"] = $row.DiskSizeMB
			$newRow["DiskFreeMB"] = $row.DiskFreeMB
			$newRow["SerialNumber"] = $row.SerialNumber
			$DiskInfo.Rows.Add($newRow)
	}

	#$diskInfo	

	try
	{
		if ($DiskInfo.Rows.Count -gt 0) {
	
			$cn = new-object System.Data.SqlClient.SqlConnection("Data Source=$dbaToolsServer;Integrated Security=SSPI;Initial Catalog=$inventoryDB");
			$cn.Open()
			$bc = new-object ("System.Data.SqlClient.SqlBulkCopy") $cn
			$bc.DestinationTableName = "DiskInfo"
			$bc.WriteToServer($DiskInfo)
			$cn.Close()
		}
	}
	catch [System.Exception]
	{
		$errVal = $_.Exception.GetType().FullName + " - " + $_.FullyQualifiedErrorID
		Write-Host "Insert Error Error $serverName"
		Write-Host $errVal
		$errCount ++
	}
}


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

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

#Get-ClusteredDiskInfo "NAT317DBS" $runDt
#EXIT

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

$clusteredServers = QuerySql $dbaToolsServer $inventoryDB $sqlQuery 60000

foreach ($instance in $clusteredServers)
{
	$instanceName = $instance[0]
	$sourceDB = $instance[1]
	$user = $instance[2]
	$pass = $instance[3]

	$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
	}

	$instanceName
	GetDiskInfo $instanceName $sourceDB $altCredential $runDt
}

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



QuerySql $dbaToolsServer $inventoryDB $sqlQuery 60000

exit

This query gets the list of instances to poll and also introduces the concept of a SQL Login credential that’s stored as a Secure String to use a SQL Login to connect rather than the AD Credential (for servers not joined to the domain).

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

The function GetDiskInfo just creates a new data table, queries the local DBATools..DiskInfo table and uses the credentials if they exist. It then bulk uploads that data into the Inventory table.

The local DiskInfo table is identical, its just in the DBATools database on every instance. So let’s look at the Powershell script that’s on each local server as part of the DBATools installation.

#
#	GetDiskSanInfo.ps1
#	
#	This script determines for SAN that is attached whether 
#	it is a mount point and whether it is replicated.
#
#	DiskExt.exe is used to determine which physical disk a mount point resides on.
#	inq.wnt.exe is used to determine whether a given SAN volume is replicated.
#
#	The dbo.DiskInfo table in DBATools is updated with this information. From
#	there the DB Inventory service will read it and update the DBInv database.
#

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"

$scriptPath = split-path -parent $MyInvocation.MyCommand.Definition

$dbaToolsServer = "CS-SQL-INFRAMONITORING-US-DEV-001"
$SQLName = $args[0]
if ($SQLName -ne $null)
{ $dbaToolsServer = $SQLName }

if (Get-Module FailoverClusters -ListAvailable -ErrorAction SilentlyContinue)
{  
	Import-Module FailoverClusters	
	$clusterName = GET-Cluster
	$physicalName = $clusterName.Name.toUpper()
} else {
	$physicalName = (hostname).toUpper()
}


$runDt = Get-Date -format "MM/dd/yyyy HH:00:00"

# this is the table that will hold the data during execution
$serverDisk = New-Object System.Data.DataTable
[void]$serverDisk.Columns.Add("ServerName", "System.String")
[void]$serverDisk.Columns.Add("DiskName", "System.String")
[void]$serverDisk.Columns.Add("AuditDt", "System.DateTime")
[void]$serverDisk.Columns.Add("Model", "System.String")
[void]$serverDisk.Columns.Add("DiskPartition", "System.String")
[void]$serverDisk.Columns.Add("Description", "System.String")
[void]$serverDisk.Columns.Add("PrimaryPartition", "System.Boolean")
[void]$serverDisk.Columns.Add("VolumeName", "System.String")
[void]$serverDisk.Columns.Add("Drive", "System.String")
[void]$serverDisk.Columns.Add("SanAttached", "System.Boolean")
[void]$serverDisk.Columns.Add("MountPoint", "System.Boolean")
[void]$serverDisk.Columns.Add("DiskSizeMB", "System.Int64")
[void]$serverDisk.Columns.Add("DiskFreeMB", "System.Int64")
[void]$serverDisk.Columns.Add("SerialNumber", "System.String")
                
# WMI data
$wmi_diskdrives = Get-WmiObject -Class Win32_DiskDrive
$wmi_mountpoints = Get-WmiObject -Class Win32_Volume -Filter "DriveType=3 AND DriveLetter IS NULL AND NOT Name like '\\\\?\\Volume%'" | Select $WMI_DiskMountProps
                
$AllDisks = @()
$DiskElements = @('ComputerName','Disk','Model','Partition','Description','PrimaryPartition','VolumeName','Drive', 'SanAttached', 'MountPoint', 'DiskSize','FreeSpace','DiskType','SerialNumber')
                foreach ($diskdrive in $wmi_diskdrives) 
                {
                    $partitionquery = "ASSOCIATORS OF {Win32_DiskDrive.DeviceID=`"$($diskdrive.DeviceID.replace('\','\\'))`"} WHERE AssocClass = Win32_DiskDriveToDiskPartition"
                    $partitions = @(Get-WmiObject -Query $partitionquery)
                    foreach ($partition in $partitions)
                    {
                        $logicaldiskquery = "ASSOCIATORS OF {Win32_DiskPartition.DeviceID=`"$($partition.DeviceID)`"} WHERE AssocClass = Win32_LogicalDiskToPartition"
                        $logicaldisks = @(Get-WmiObject -Query $logicaldiskquery)
                        foreach ($logicaldisk in $logicaldisks)
                        {
				$newRow = $serverDisk.NewRow()
				$newRow["ServerName"] = $physicalName
				$newRow["DiskName"] = $logicalDisk.Name
				$newRow["AuditDt"] = [DateTime]$runDt
				$newRow["Model"] = $diskDrive.Model
				$newRow["DiskPartition"] = $partition.Name
				$newRow["Description"] = $partition.Description
				$newRow["PrimaryPartition"] = $partition.PrimaryPartition
				$newRow["VolumeName"] = $logicalDisk.volumeName
				$newRow["Drive"] = $diskDrive.Name
				$newRow["SanAttached"] = if ($diskdrive.Model.StartsWith("NETAPP")) { $TRUE } else { $FALSE }
				$newRow["MountPoint"] = $FALSE
				$newRow["DiskSizeMB"] = [math]::truncate($logicalDisk.Size / 1MB)
				$newRow["DiskFreeMB"] = [math]::truncate($logicalDisk.FreeSpace / 1MB)
				$newRow["SerialNumber"] = $diskDrive.serialNumber
				$serverDisk.Rows.Add($newRow)
                        }
                    }
                }
                # Mountpoints are weird so we do them seperate.
                if ($wmi_mountpoints)
                {
                    foreach ($mountpoint in $wmi_mountpoints)
                    {
				$newRow = $serverDisk.NewRow()
				$newRow["ServerName"] = $physicalName
				$newRow["DiskName"] = $mountpoint.Name
				$newRow["AuditDt"] = [DateTime]$runDt
				$newRow["Model"] = 'Mount Point'
				$newRow["DiskPartition"] = $DBNULL
				$newRow["Description"] = $mountpoint.Caption
				$newRow["PrimaryPartition"] = $FALSE
				$newRow["VolumeName"] = $mountpoint.Caption
				$newRow["Drive"] = [Regex]::Match($mountpoint.Caption, "(^.:)").Value
				$newRow["SanAttached"] = $TRUE
				$newRow["MountPoint"] = $TRUE
				$newRow["DiskSizeMB"] = [math]::truncate($mountpoint.Capacity / 1MB)
				$newRow["DiskFreeMB"] = [math]::truncate($mountpoint.FreeSpace / 1MB)
				$newRow["SerialNumber"] = $DBNULL
				$serverDisk.Rows.Add($newRow)
                    }
                }

$sqlCmd = "Select name from sys.databases where name like 'DBATools%'"
$dbatoolsDBName = QuerySql  $dbaToolsServer "master" $sqlCmd 

$DBAToolsDB = $dbatoolsDBName[0]

$sqlCmd = "TRUNCATE TABLE dbo.DiskInfo"
QuerySql $dbaToolsServer $DBAToolsDB $sqlCmd 

# create the connection for the bulk loads
$connString = "Data Source=$dbaToolsServer; Initial Catalog=$DBAToolsDB; Integrated Security=True; Application Name=GetSanDiskInfo.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 = "dbo.DiskInfo"
	$bulkCopy.WriteToServer($serverDisk)
}
catch [System.Exception]
{
	$errVal = $_.Exception.GetType().FullName + " - " + $_.FullyQualifiedErrorID
	Write-Host "Bulkcopy Error"
	Write-Host $errVal
	$errCount ++
}

Very similar as it creates a data table for each disk row. It also imports FailoverClusters to get the cluster name (in the case of a cluster, it stores all the disks under the Cluster Name) It then queries via WMI out to WIN32_DiskDrive and then to Win32_Volume. The filter on the Win32_Volume limits to just mount points via -Filter “DriveType=3 AND DriveLetter IS NULL AND NOT Name like ‘\\\\?\\Volume%'”.

It then uses some WMI Magic of “ASSOCIATORS OF {Win32_DiskDrive} by device ID” to get partitions, and another “ASSOCIATORS OF {Win32_DiskPartition} by device ID” to get the logical disks. This is the part that really slowed down running this from a centralized location, and forcing me to run it locally on each server.

I do some assumptions here, with the SanAttached value, if the Drive model starts with “NETAPP”, I assume it’s SAN Attached. I only have Netapp arrays in the company, so I can make that assumption. I’m sure there would be additional models, for EMC, 3Par, Dell, etc… but this works for my environment today.

I do a separate loop to load Mountpoints into the data table, and always assume they are SAN Attached.

So, if you’re astute, you’ll notice or think to yourself, if I have multiple instances on the same server, each of those DBATools databases will have a DiskInfo table and the same information in it. And then my centralized polling powershell will pull those and load them into the Inventory database…. but that table has a Primary Key on the ServerName, DiskName, and AuditDt. And you’re right it is the physical server name, or in the case of a cluster, the Virtual Computer Name of the Cluster. What I do is discard the duplicates – who needs them? Why write a bunch of logic to check for it, and not poll it, or whatnot, let the Constraint of the Clustered Primary Key do the work and disallow dupes. For example, I have one two node “active/passive” cluster that has seven instances on it. Each instance has a DBATools database, and each instance runs the local powershell script and loads the disk info for the cluster into each of the seven DBATools databases. The centralized polling script connects to each of those seven instances and attempts to load the same data seven times. It discards six of them – big deal, as long as I get one that’s all that matters…. I now have a list of all disks, volumes, mount points, total space and free space for every server that I have in my Inventory, and I have it every day. I can now trend growth rate at the server / disk layer over time.

Here’s the sample data from a single cluster of mine, with six instances on it (I group instances on a drive letter with three mountpoints under it, one for User Database Datafiles, one for User Database Tran Logs, and one for System DB’s / instance root). Just think of the trending and PowerBI charts you could make with a year’s worth of data from every SQL Server in your environment!

New DBA Inventory – Server details

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

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.

Free Data Analysis and Reporting course from Google

Google is offering a free course titled “Making Sense of Data”. Google Data Course

This is very timely for me. In my role now as the Supervisor of IT Quality, I’ve been asked to organize and lead a monthly meeting with the Director of Infrastructure and Applications and his direct reports – managers of the various IT silos reporting to him. I’ve already started with one base sources of information, our Help Desk system which tracks both Incidents and Changes. I have a number of Pivot Charts in an excel spreadsheet that will take only a few minutes to refresh the data from our Help Desk source and refresh the charts.

With this new course – while probably specifically geared towards Google Docs and Fusion Tables, I’m hoping the underlying concepts of how to answer questions with data and how to recognize patterns in data will help me prepare a great monthly meeting that will be a value add to the organization. This is something that has not been done before by the IT organization – there have been some monthly data charts prepared in the past, but nothing like this monthly overview where I’ll walk the heads through some of the data, and drill down into information like “The number of incidents reported by region”, or “Incidents by incident area”. Over time this data will build up some great trends on performance. A repository not of data, but of knowledge. Maybe its a natural progression for me, as a DBA I have a long history of working with and supporting “Data” systems, this will be a path of turning “Data” into something useful for the business.

Disable UAC without reboot on Windows 7, Server 2008, Server 2008 R2

I thought this was common knowledge, but after a discussion on twitter, and a subsequent internet search, I think it’s not well known. In Windows 7, and Server 2008 and 2008 R2, you can disable UAC without requiring a reboot. You simply have to log in with the local administrator account and disable it via the User Account Control Settings dialog. Any change made will not require a reboot. Please note, you must use the built in Administrator account… using an account that’s in the Administrators group will not work.

It appears that in Windows 8, a reboot is never required, at least I couldn’t get a forced reboot in my testing. If I have time, I’ll update this with testing on a Server 2012 system in a few days.

Note, I am not endorsing that one routinely use the built-in administrator account on a system, and I’m not endorsing completely disabling UAC. Both are very real security risks… but there are times when it’s far more expedient to do this in a controlled fashion than the alternatives. I am a firm believer in the policy of Least Permissions Needed.

Powershell script to capture OS level properties (CPU, Cores, NIC, Memory, BIOS, OS)

I have a master post which links to a set of powershell scripts all related to this. The Master post which links to all the scripts.

This script is an updated and streamlined version of this original and ugly script

This version actually dumps out to a CSV via the “Export-CSV” command-let. It has some error handling and will check the first WMI call and skip the rest if the first one can’t connect. It has a friendly output to the screen so the user knows which server is currently being queried.

$serverList = "..\SQL Servers.txt"
#$serverList = "test.txt"
[System.Collections.ArrayList]$sysCollection = New-Object System.Collections.ArrayList($null)

foreach ($server in (Get-Content $serverList)) 
{ 
    "Collecting information from $server"
    [System.Collections.Specialized.OrderedDictionary]$si = @{}    # Blank hashtable for each iteration
    $totCores=0

    $si.Server=[string]$server;

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


        $si.Manufacturer=[string]$sysInfo.Manufacturer;
        $si.Model=[string]$sysInfo.Model;
        $si.TotMem=[string]$sysInfo.TotalPhysicalMemory;
        $si.BiosDesc=[string]$bios.Description;
        $si.BiosVer=[string]$bios.SMBIOSBIOSVersion+"."+$bios.SMBIOSMajorVersion+"."+$bios.SMBIOSMinorVersion;
        $si.BiosSerial=[string]$bios.SerialNumber;
        $si.OSName=[string]$os.Name.Substring(0,$os.Name.IndexOf("|") -1);
        $si.Arch=[string]$os.OSArchitecture;
        $si.Processors=[string]@($procs).count;
        $si.Cores=[string]$procs[0].NumberOfCores;
        $iter=0
        while ($iter -lt 12)   #Parse through each stick of memory (arbitrary 12 Memory stick columns)
        {
            if ($mem[$iter] -ne $null) {
                $si."MemBank$iter" = [string]$mem[$iter].Capacity + "," + [string]$mem[$iter].BankLabel 
            } else {
                $si."MemBank$iter" = ""
            }
            $iter++
        }
        $iter=0
        while ($iter -lt 4)    #Parse through each NIC (arbritrary 4 Network Card columns)
        {
            $si."DHCP$iter"=[string]$nic[$iter].DHCPEnabled;
            $si."IPAddress$iter"=[string]$nic[$iter].IPAddress;
            $si."Subnet$iter"=[string]$nic[$iter].IPSubnet;
            $si."Gateway$iter"=[string]$nic[$iter].DefaultIPGateway;
            $si."Mac$iter"=[string]$nic[$iter].MacAddress;
            $iter++
        }
    }
    catch [Exception]
    {
        "Could not contact $server, skipping to next"
        Continue
    }
    finally
    {
        [void]$sysCollection.Add((New-Object PSObject -Property $si))    #Add the dictionary list to the collection
    }
}

$sysCollection | Export-CSV -path ServerInventory.csv -NoTypeInformation    #Output the collection to a CSV file

Another developer gift

Just found this one, looks like it was dropped in right around the time I started at the company.

ALTER procedure [dbo].[usp_Get_RowCount] 
@pStructureRowCount	nvarchar(500),
@pRowCount int output

as

set nocount on
	
Declare
@SQLStatement varchar(500)

set @SQLStatement = 'Select count(*) From ' + @pStructureRowCount

create table #tempRowCount (----Temporary table created for storing related to current Database
StrucRowCount bigint,
)

insert into #tempRowCount exec(@SQLStatement)

select @pRowCount = StrucRowcount from #tempRowCount

Developers and Reporting Services

Hello again. Recently I accepted a new job offer at the same company, I’m now the “Supervisor of Quality” which means I’m going to be working on Service Management, Change Management, Problem Management, etc, etc, etc.

So I kind of figured that my SQL posting days were done. However, my company likes to put the cart way in front of the horse and I’m still the lone DBA for 50 production servers, 70 production instances, and thousands of databases, scattered around the globe from South Africa to the UK, from Australia to Amsterdam. Oh, and everywhere in-between.

Today, I got a message from a “developer” that his new report is timing out.

I ran the report manually with his parameters, and do a little “SP_WHOISACTIVE” while the report was running. Here’s one of the queries I captured…
Continue reading Developers and Reporting Services

Surface Pro – Sleep oddities

No, this isn’t a post about me sleeping with my Surface Pro! My wife probably wouldn’t like that too much.

No this is a post about problems I seem to be having with this thing going to sleep. So, as a desktop replacement, it’s been pretty good so far. I’m still looking for a good way to split off the mini Display Port to two DVI monitors… I’m a dual 24″ monitor type of worker when I’m in the office. Using one and the Surface screen for work is not the best arrangement for me. But with that aside, I’ve been very happy so far.

I have noticed though that at times, when I go away from my desk, after hitting Windows Key + L to lock my screen, if I’m gone for more than just a minute or two, the SP seems to go into a deep sleep almost like hibernate mode – even when plugged into wall power. I’m a DBA, and I use SSMS all day long to interact with my SQL Servers… I try not to RDP to them to do work, unless I have OS level work to do. So, I might need to kick off a backup on a large DB that might take 30 minutes or longer to finish. I launch SSMS and run my TSQL command, all good. However, during the duration, if I lock my SP and then go to the rest room, and come back, I log back in, and my wireless connection is dropped… ergo, my backup has failed and I have to kick it off again.

It seems to me that perhaps there’s a proximity sensor on this thing, kind of like the XBOX Kinect camera that knows not only that I’ve locked my SP, but also that I’ve walked away, and it decides to override my power configuration and take a nap. That’s not a good thing… if I wanted to have to use a VDI type interface for all my work, I’d work off my Android phone with a Blue Tooth Keyboard, Mouse, and Micro HDMI to my monitor. If I can’t keep this thing running while on house power while I lock it, and go to a 30 minute meeting, it’s not useful as a workstation replacement. Off house power, I get it… but on it, let me decide – I am still a user, right Redmond?