Tag Archives: SQL Server Virtualization

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

Posted on by
Reply

There seem to be a lot of questions and even some misinformation popping up with regards to SQL Server 2012 licensing. Microsoft is moving away from licenses based on the number of processor in a server to figure out per/cpu licensing. They are now licensing hardware on a per/core license.

Right or wrong, agree or disagree, here’s the details as I know them. Based on a number of sources, including a meeting with my employers VAR and an internal MS licensing expert.

Per core licensing is based on “Core-Packs”. Each core-pack covers two cores, and there is a minimum purchase of two core-packs. This will be an expensive premium if one plans on building a single processor dual core machine, and you’re paying to license a minimum of four cores.

I’ve been told that the core-packs cost 50% less than current per/cpu licenses. That makes the magic number a total of 4 cores with 2012 licensing costing the same a single processor license with current license costs.

I’ve read that with 2012 licesning – in the case of virtualization if you license all the physical cores you have unlimited rights to virtual OS’s (vOS). Current day licensing with Enterprise Edition would only allow a total of 4 vOS’s per license.

Also, current customers with an EA will retain their current purchasing plan until the expiration of the EA, regardless of when that is.. if it’s Jan 1 of 2013, then all of 2012, you continue to purchase the licenses as you have. Once the EA is up, you will have to submit the number of cores in your currently licensed environment and MS will “trade” those for the equivalent number of Core-Packs. I’ve heard conflicting reports of a hard limit of 20 cores or 10 Core-Packs per server, and other reports that do not mention that limit.