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.
Here’s #4, which talks about the script to poll database details from each instance.

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

Windows Updates affected SQL Server?

I had read about the issues with the SQL Agent “Is Alive” checks spamming event logs on clustered SQL Server 2012 and SQL Server 2008 R2 SP instances… and since I have three sets of 2 Node clusters running multiple instances including 2012, 2008R2 and 2008, after reading about that a few months back, I checked my servers. None of them were spamming the logs. They all have (change that to HAD now that I’ve upgraded) SQL 2012 RTM, SQL 2008 R2 SP2, and SQL 2008 SP3. Not a one of them ever had the “dreaded” LooksAlive or CheckServiceAlive entries with Event ID 53 from [sqagtres]. Here’s one connect article that shows some more details and which versions are fixed with what CU’s. http://connect.microsoft.com/SQLServer/feedback/details/737820/sql-2012-cluster-checkservicealive-spams-application-event-log

Anyway, so, none of the clusters in my environment have any history of Event ID 53 for SQL Agent. Last night, the System Administrator applied some Windows Updates (I don’t have a “test” SQL Cluster, but all these updates were applied on our Test SQL Servers prior to last night). After the Windows updates were applied, my event logs on ONE server started being spammed with the Agent Alive checks. WTF? I thought this was a SQL Agent bug?

Here’s the list of Updates applied last night. KB2799494, KB2789645, KB2251487, KB2790655, KB2538243, KB890830, KB2790113, KB2789642, KB2797052, KB2687441, KB2596672, KB2778344, KB2645410, KB2792100, KB2538242.

AgentAliveSpam

On Monday (since I’ve worked enough this damned weekend already), I’m going to go through the other two clusters and compare Updates that were installed, and base levels of SQL Server. Off hand, I think that both of the other Clusters were running SQL 2012 SP1 already.

Oh, another odd data point for me to consider… I was getting these LooksAlive entries in the event logs for the SQL 2008 instance!

Finally, a parting thought. Installing CU3 for SQL Server 2008 R2 at 2AM (or was it 3AM, damned DST!) on a Sunday morning, I really liked that I had to implement a work around to get the POS CU3 installed. Yeah, the old “Rename the C:\Windows\System32\perf-*-sqlagtctr.dll” file to allow the installer to upgrade your SQL Server bug. Thanks for that MS.

T-SQL Tuesday #39 Changing Service Accounts or Passwords with PoSH

T-SQL Tuesday #39 Changing Service Accounts or Passwords with PoSH

T-SQL Tuesday

I’ve done one other of these in the past. This is a good one, and I too love using Powershell to automate management of multiple SQL Servers. Mine is an example of a situation I was in. We had 30 or so SQL Servers that were configured with Domain accounts to run each of the major services – the Engine, SSAS, SSIS, SSRS, and the Agent. However, these accounts were shared across the computers, so an account XYZ\SQLServer-SVC was used to run SQL Server Engine on all the servers. Due to circumstances outside my control we had to change passwords on all accounts.

I was dreading the fact that I would have to take a major outage and race to change the service account password in AD, and then do each server individually. Then I thought, POWERSHELL, you can save me! Using SMO.WMI.ManagedComputer was the trick for me. Here’s the code.

#Load the SqlWmiManagement assembly off of the DLL

[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SqlWmiManagement") | out-null
$serviceAccountList = Get-Content "SQL Servers.txt"

foreach ($server in $serviceAccountList)
{
    $SMOWmiserver = New-Object ('Microsoft.SqlServer.Management.Smo.Wmi.ManagedComputer') $server #Suck in the server you want
    $changeService = $SMOWmiserver.Services | where {$_.serviceAccount -eq "XYZ\SQLServer-SVC"} #| select name, type, ServiceAccount, DisplayName, Properties, StartMode, StartupParameters | Format-Table
    if ($changeService.name -ne $null) {
        foreach ($service in $changeService)
        {
            "Service " + $service.name + " found on " + $server
		
	    # Change the service account name or password as needed		

            $service.setServiceAccount("ACCOUNT","PASSWORD")
            "Service " + $service.name + " changed on " + $server
            $service = $null
        } 
    } else {
        "Specified account was not found on " + $server
    }
    $changeService = $null
    $SMOWmiserver = $null
}

Right there, $service.setServiceAccount, that’s the magic. The nice thing about this approach is that it will take care of the dependencies. I started with another approach which would change the service account and then restart the service, but had issues with dependent services, like SQL Agent stopping me from stopping SQL Server outright. With the setServiceAccount part of ManagedComputer, it handled it for me.

Oh, and all new Servers, they have unique domain Service Account credentials, we no longer share a service account across computers. With SQL 2012, I plan on testing out Managed Service Accounts.

Extended Properties as a Run Book?

Perhaps Run Book isn’t the right terminology, but it’s close. So, in my environment we are a manufacturer of large equipment. We do have a centralized IT organization, but it seems to be more focused on keeping the lights on and trying to catch up on what the business purchases off the shelf or even what some groups have developed on their own.

One thing I’ve struggled with in the past two years, since this company has no defined processes (no ITIL or anything similar) and little to no documentation, has been in capturing what databases are where, who is the “Business Owner”, what the application is, and such. I’ve also started implementing DNS aliases for connection strings so that we can be more portable when moving applications, especially COTS systems where we might not have the understanding to change a set of connection strings. As you may know, I have a number of Powershell scripts that capture the specifics of the servers, instances, database properties, file locations, etc. One thing that these cannot grab are the applications and business representatives. Sure, with my two years of knowledge, I can look at a database server, look at an instance name, look at the name of the database and make a good guess at what it does, where it runs, who I should contact if there are problems or changes are planned… but when onboarding someone new, it would take an incredible amount of time for that new person to build up the same intimacy with the environment.

Previously, the approach was to take the Excel document output from the Database Properties script and add a column with “Owner” which sometimes had a name I could place with it.

Today I received a request for a new blank database via email from a quasi-developer in another location. This person is not in IT but has developed a number of supporting type applications for the local users at his manufacturing site. I thought to myself, I have all the details in this email from the requestor, maybe we should just embed this email to the excel document. But I’m lazy, and this is a very loose requirement, I could be busy, I could be in a procrastination mood and put off the follow up documentation for… well,,, ever. Next, I thought, we have a web based in-house developed tool for requesting services from IT. So I thought, hey, I could make it a requirement that a ticket be created for any new Database needed, and then just copy the ticket number into the spreadsheet. Blah, same thing, when I’m off creating a DB in SSMS, I don’t really want to have to open a spreadsheet on our SharePoint site to update that… still too manual and loose.

So, I thought, why not make it part of the “Create database” process to copy the details from the request ticket into Extended Properties inside the database. What I came up with was setting up four Key, Value pairs to start with. A Description which contains some details of the what the database is used for. An AppOwner key with the requestor name or whomever is identified as the owner. A Ticket key with the number from the request. The final required one is the ConnectionString which contains the DNS alias name created for the application to use. I kind of like it, it’s tidy and neat, and it stays with the database if I restore to a new server. The only maintenance might be to change the AppOwner if they change roles or leave the company.

I’m going to modify my above referenced powershell script to include grabbing these keys and values from every database when I run it. I’ve also been noodling a type of “Self Service portal” where power users like the requestor above can hit a web page form that would then kick off an approval work flow and I could then choose the server to deploy to and the database create and all the above documentation would be created on the fly. That would be really neat, the only flaw would be organizational changes.

In short, I’m going to make it a requirement than any new database request be filled out with our services request system, and require a description of the application just for paperwork reasons… it’s much easier to run a web based report than search a DBA’s PST file for auditing purposes. I’m going to have the DBA team start using the Extended Properties for the database to hold that. I’m going to “Extend” my powershell script to grab those properties for our documentation purposes. I may or may not work on a Provisioning Portal… I might be getting too far ahead of the rest of the IT organization here.

My Generic Event Notifications for SQL Servers

While reading a blog post regarding using server DDL triggers to capture a “CREATE DATABASE” and fire an email to the DBA, I thought that coupling a trigger to sp_send_dbmail and an external executable wasn’t a great idea. I decided that there must be a better way to capture that important event. For me, this would really only occur in a development server where the developers often have enhanced rights to the dev servers. Production is pretty tight. However, my heart is always broken when a developer runs up and asks me to restore an accidentally dropped database on the Dev server, only for me to find out that the same developer created that database without telling me. My backup packages don’t dynamically grab all the online databases when it runs, so if they add a database and don’t tell me, it don’t get backed up.

My first thought was Extended Events. I don’t know much about them, I’ve heard the abstracts and read the rumors, but I haven’t had call to work with them. If I remember correctly, it seemed there was a way to capture the CREATE Database event, but then I was stuck with a data record of the event, and not a handy way to email it to me. Searching for Extended Events Send Email took me quickly to a Johnathan Kehayias awesome answer on the MSDN forums explaining that, no, there’s no real plumbing between Extended Events and the Service Broker. His second answer in the same discussion linked to his blog which is fountain of knowledge and an article explaining Event Notifications vs Extended Events. From there, I saw a link to another article in his blog. Sounded pretty darn close to what I wanted.

That lead to my current version of a generic Event Notification system which I am currently testing on a number of servers. It’s actually a combination of procedures from Jonathan’s articles and Sergey Maskalik’s article. Sergey’s error handling and timeout on the Waitfor along with cleanup of the Conversation Handles coupled with Jonathan’s shredding of the XML message body seems to be a work of art to me. I added in some of my own magic to ensure XACT_ABORT was on, some COALESCE’s to ensure a null value wasn’t concatenated over valid values, and setting this up in a “utility” database and setting “TRUSTWORTHY ON” to allow the execution of sp_send_dbmail in MSDB.

We’ll start with the guts needed to set up and wire up the Service Broker and Queues, Services, and Routes. It’s pretty boiler plate, with the added commands to turn on the Service Broker and set TRUSTWORTHY.

NOTE: Jonathan has visited and brought up a very valid and real security risk. In my approach, I take a utility database and set TRUSTWORTHY=ON to it. Please visit and read Raul Garcia’s article on the risks of the TRUSTWORTHY bit. At the time of my writing this, the database that I deploy this solution to is already restricted to the DBA team and anyone with Sysadmin privileges. In this case, all those principals already have enough access to do what this setting allows without any other work, so I feel the risk in my environment is low. For a more secure solution, I strongly recommend a careful review of your situation, and indeed using certificates to sign the procedures to allow cross-database execution.

USE [master];
go

-- SET EVERYTHING UP WITH THE SERVICE BROKER
--  We could also do this when creating the DatabaseBackup database
--  as part of the initial package run, or update.
--  Trustworthy allows a stored proc in the current database
--   execute SP_SEND_DBMAIL in msdb

ALTER DATABASE DatabaseBackup SET ENABLE_BROKER;
go
ALTER DATABASE DatabaseBackup SET TRUSTWORTHY ON;
go


USE DatabaseBackup
GO

-- Drop the notification if it exists
IF EXISTS ( SELECT  *
            FROM    sys.server_event_notifications
            WHERE   name = N'CaptureDBAEvents' ) 
    BEGIN
        DROP EVENT NOTIFICATION [CaptureDBAEvents] ON SERVER;
    END

-- Drop the route if it exists
IF EXISTS ( SELECT  *
            FROM    sys.routes
            WHERE   name = N'DBAEventRoute' ) 
    BEGIN
        DROP ROUTE [DBAEventRoute];
    END

-- Drop the service if it exists
IF EXISTS ( SELECT  *
            FROM    sys.services
            WHERE   name = N'DBAEventService' ) 
    BEGIN
        DROP SERVICE [DBAEventService];
    END

-- Drop the queue if it exists
IF EXISTS ( SELECT  *
            FROM    sys.service_queues
            WHERE   name = N'DBAEventQueue' ) 
    BEGIN
        DROP QUEUE [DBAEventQueue];
    END

IF EXISTS ( SELECT * 
			FROM MASTER.sys.event_notifications
			WHERE name = N'CaptureDBAEvents' )
	BEGIN
		DROP EVENT NOTIFICATION [CaptureDBAEvents] ON SERVER
	END

--  Create a service broker queue to hold the events
CREATE QUEUE [DBAEventQueue]
WITH STATUS=ON;
GO

--  Create a service broker service receive the events
CREATE SERVICE [DBAEventService]
ON QUEUE [DBAEventQueue] ([http://schemas.microsoft.com/SQL/Notifications/PostEventNotification]);
GO

-- Create a service broker route to the service
CREATE ROUTE [DBAEventRoute]
WITH SERVICE_NAME = 'DBAEventService',
ADDRESS = 'LOCAL';
GO

-- Create the event notification to capture the events
CREATE EVENT NOTIFICATION [CaptureDBAEvents]
ON SERVER
WITH FAN_IN
FOR CREATE_DATABASE, DROP_DATABASE, CREATE_LOGIN, DROP_LOGIN, CREATE_USER, DROP_USER, BLOCKED_PROCESS_REPORT, DEADLOCK_GRAPH, ADD_ROLE_MEMBER, ADD_SERVER_ROLE_MEMBER
TO SERVICE 'DBAEventService', 'current database';
GO

Right above, while creating the EVENT NOTIFICATION, you can see the event types I have. I decided that while it’s great to have AutoGrowth events sent, that in our current environment, this might be more noise than there is value for, so we have left that out for now. Sure there’s a lot more audit events that I could hit up too, but I felt that the ROLL MEMEBERSHIPS, USER and LOGIN work and the DATABASE create and drop were a great start. Also the DEADLOCK_GRAPH was just a nice freebie.

Next, the guts of this, an Stored Procedure that is generic enough to handle different Event Types and shred as much of the XML as possible into a friendly email message. Right, who doesn’t like a mailbox full of raw XML in the morning? 🙂 Note, the final ELSE in the shredding and Email Body building, so if we decided to add a Event Type, we will always just email off the XML until things are fixed.

USE DatabaseBackup
GO

-- Drop the procedure if it exists
IF EXISTS ( SELECT * 
			FROM sys.procedures
            WHERE   name = N'ProcessEvents' ) 
    BEGIN
        DROP PROCEDURE [ProcessEvents];
    END
GO

CREATE PROCEDURE [dbo].[ProcessEvents]
WITH EXECUTE AS OWNER
AS    
	SET XACT_ABORT ON;
    DECLARE @eventType VARCHAR(128);
	DECLARE @messagetypename NVARCHAR(256);
	DECLARE @ch UNIQUEIDENTIFIER;

    DECLARE @serverName VARCHAR(128);
    DECLARE @postTime VARCHAR(128);
    DECLARE @databaseName VARCHAR(128);
    DECLARE @duration VARCHAR(128);
    DECLARE @growthPages INT;   
	DECLARE @userName VARCHAR(128);
	DECLARE @loginInfo VARCHAR(256);
	DECLARE @SID VARCHAR(128);

    DECLARE @messageBody XML;
	DECLARE @emailTo VARCHAR(50);
	DECLARE @emailBody VARCHAR(MAX);
	DECLARE @subject varchar(150);

	SET @emailTo = '<DBA TEAM EMAIL HERE>@gmail.com;   

	WHILE (1=1) 
	BEGIN         
		BEGIN TRY                
			BEGIN TRANSACTION               
				WAITFOR (                        
					RECEIVE TOP(1)    
					@ch = conversation_handle,                                                            
					@messagetypename = message_type_name,                                
					@messagebody = CAST(message_body AS XML)                        
					FROM DBAEventQueue              
				), TIMEOUT 60000;             
				IF (@@ROWCOUNT = 0)              
				BEGIN                     
					ROLLBACK TRANSACTION;                       
					BREAK;                
				END                
				IF (@messagetypename = 'http://schemas.microsoft.com/SQL/Notifications/EventNotification')                
				BEGIN  
					--  Get the common information 
					SELECT @eventType = COALESCE(@messagebody.value('(/EVENT_INSTANCE/EventType)[1]','varchar(128)'),'UNKNOWN'),
						@serverName = COALESCE(@messagebody.value('(/EVENT_INSTANCE/ServerName)[1]','varchar(128)'),'UNKNOWN'),
						@postTime = COALESCE(CAST(@messagebody.value('(/EVENT_INSTANCE/PostTime)[1]','datetime') AS VARCHAR),'UNKNOWN');
					 
					SELECT  @emailBody = 'The following event occurred:' + CHAR(10) 
						+ CAST('Event Type: ' AS CHAR(25)) + @EventType + CHAR(10)
						+ CAST('ServerName: ' AS CHAR(25)) + @ServerName + CHAR(10) 
						+ CAST('PostTime: ' AS CHAR(25)) + @PostTime + CHAR(10);
                    
					-- Now the custom XML fields depending on the Event Type
					IF (@EventType like '%_FILE_AUTO_GROW')
					BEGIN
						SELECT @duration = COALESCE(@messagebody.value('(/EVENT_INSTANCE/Duration)[1]','varchar(128)'),'UNKNOWN'),
							@growthPages = COALESCE(@messagebody.value('(/EVENT_INSTANCE/IntegerData)[1]', 'int'),'UNKNOWN'),
							@databaseName = COALESCE(@messagebody.value('(/EVENT_INSTANCE/DatabaseName)[1]','varchar(128)'),'UNKNOWN');
                    
						SELECT @emailBody = @emailBody
							+ CAST('Duration: ' AS CHAR(25)) + @Duration + CHAR(10) 
							+ CAST('GrowthSize_KB: ' AS CHAR(25)) + CAST(( @GrowthPages * 8 ) AS VARCHAR(20)) + CHAR(10)
							+ CAST('DatabaseName: ' AS CHAR(25)) + @DatabaseName + CHAR(10);
					END
					ELSE IF (@EventType like '%_DATABASE')
					BEGIN
						SELECT @userName = COALESCE(@messageBody.value('/EVENT_INSTANCE[1]/LoginName[1]', 'varchar(128)'),'UNKNOWN'),
							@DatabaseName = COALESCE(@messagebody.value('(/EVENT_INSTANCE/DatabaseName)[1]','varchar(128)'),'UNKNOWN');
					
						SELECT @emailBody = @emailBody 
							+ CAST('User: ' AS CHAR(25)) + @userName + CHAR(10)
							+ CAST('DatabaseName: ' AS CHAR(25)) + @DatabaseName + CHAR(10);
					END
					ELSE IF (@EventType like '%_LOGIN')
					BEGIN
						SELECT @userName = COALESCE(@messageBody.value('/EVENT_INSTANCE[1]/LoginName[1]', 'varchar(128)'),'UNKNOWN'),
							@loginInfo = COALESCE(@messageBody.value('/EVENT_INSTANCE[1]/ObjectName[1]', 'varchar(256)'),'UNKNOWN'),
							@SID = COALESCE(@messageBody.value('/EVENT_INSTANCE[1]/SID[1]', 'varchar(128)'),'UNKNOWN');
					
						SELECT @emailBody = @emailBody
							+ CAST('User: ' AS CHAR(25)) + @userName + CHAR(10)
							+ CAST('New User: ' AS CHAR(25)) + @loginInfo + CHAR(10)
							+ CAST('New SID: ' AS CHAR(25)) + @SID + CHAR(10);
					END
					ELSE IF (@EventType like '%_ROLE_MEMBER')
					BEGIN
						DECLARE @roleName VARCHAR(128);
						DECLARE @command VARCHAR(128);
						SELECT @userName = COALESCE(@messageBody.value('/EVENT_INSTANCE[1]/LoginName[1]', 'varchar(128)'),'UNKNOWN'),
							@loginInfo = COALESCE(@messageBody.value('/EVENT_INSTANCE[1]/ObjectName[1]', 'varchar(256)'),'UNKNOWN'),
							@roleName = COALESCE(@messageBody.value('/EVENT_INSTANCE[1]/RoleName[1]', 'varchar(256)'),'UNKNOWN'),
							@command = COALESCE(@messageBody.value('/EVENT_INSTANCE[1]/TSQLCommand[1]/CommandText[1]', 'varchar(256)'),'UNKNOWN');
						SELECT @emailBody = @emailBody
							+ CAST('User: ' AS CHAR(25)) + @userName + CHAR(10)
							+ CAST('Affected User: ' AS CHAR(25)) + @loginInfo + CHAR(10)
							+ CAST('New Role: ' AS CHAR(25)) + @roleName + CHAR(10)
							+ CAST('Command issued: ' AS CHAR(25)) + @command + CHAR(10);
					END
					ELSE  -- TRAP ALL OTHER EVENTS AND SPIT OUT JUST THE XML - We can pretty it up later :)
					BEGIN
						SELECT @emailBody = CAST(@messagebody AS VARCHAR(max));
					END

					-- Send email using Database Mail
					SELECT @subject = @eventType + ' on ' + @serverName;
					EXEC msdb.dbo.sp_send_dbmail                
						@profile_name = 'DBA Email', -- your defined email profile 
						@recipients = @emailTo, -- your email
						@subject = @subject,
						@body = @emailBody;               
				END              
				IF (@messagetypename = 'http://schemas.microsoft.com/SQL/ServiceBroker/Error')            
				BEGIN                        
					DECLARE @errorcode INT;                          
					DECLARE @errormessage NVARCHAR(3000) ;                 
					-- Extract the error information from the sent message                  
					SET @errorcode = (SELECT @messagebody.value(                        
						N'declare namespace brokerns="http://schemas.microsoft.com/SQL/ServiceBroker/Error";                         
						(/brokerns:Error/brokerns:Code)[1]', 'int'));                  
					SET @errormessage = (SELECT @messagebody.value(                        
						N'declare namespace brokerns="http://schemas.microsoft.com/SQL/ServiceBroker/Error";                        
						(/brokerns:Error/brokerns:Description)[1]', 'nvarchar(3000)'));                  
					-- Log the error 
					END CONVERSATION @ch WITH CLEANUP;                             
				END
				IF (@messagetypename = 'http://schemas.microsoft.com/SQL/ServiceBroker/EndDialog')                
				BEGIN                       
					-- End the conversation                        
					END CONVERSATION @ch WITH CLEANUP;                
				END                                 
			COMMIT TRANSACTION;   
		END TRY        
		BEGIN CATCH             
			ROLLBACK TRANSACTION;                
			DECLARE @ErrorNum INT;                
			DECLARE @ErrorMsg NVARCHAR(3000);                
			SELECT @ErrorNum = ERROR_NUMBER(), @ErrorMsg = ERROR_MESSAGE();                
			-- log the error                
			BREAK;        
		END CATCH   
	END
GO

Finally, let’s activate the new Stored Procedure by altering the Queue. Again this is pretty boiler plate.

-- Activate the procedure with the Queue
ALTER QUEUE [DBAEventQueue]
   WITH STATUS=ON, 
      ACTIVATION 
         (STATUS=ON,
          PROCEDURE_NAME = [ProcessEvents],
          MAX_QUEUE_READERS = 1,
          EXECUTE AS OWNER);
GO

Thanks, I hope that helps anyone interested in Event Notifications.

Merge replication crash dump

Ran into an interesting issue with Merge replication that had been set up from a vendor. This has been up and running in my environment with a central publisher that is not accessed by any client systems, and three subscribers which are placed regionally and used by client systems exclusively. The publisher simply acts are the publisher and synchronizes changes between the subscribers. The subscribers are pull subscriptions and everything is SQL Server 2008 SP2 CU6.

We added a new subscriber and left it unused by client systems for a few weeks. Things were fine, it was syncing all changes occurring at the other subscribers without issue. Suddenly after some maintenance work the merge process started crashing on this subscriber. In C:\Program Files\Microsoft SQL Server\100\Share\ErrorDumps\ we were getting minidump files every time we restarted the merge agent. I did some analysis of the dump files, and from the public symbols could see that it was a access exception coming from ReplRec.dll

FAULTING_IP:
replrec!CReplRowChange::GetSourceRowData+19
00000000`70e8e469 48833a00 cmp qword ptr [rdx],0

EXCEPTION_RECORD: ffffffffffffffff -- (.exr 0xffffffffffffffff)
ExceptionAddress: 0000000070e8e469 (replrec!CReplRowChange::GetSourceRowData+0x0000000000000019)
ExceptionCode: c0000005 (Access violation)
ExceptionFlags: 00000000
NumberParameters: 2
Parameter[0]: 0000000000000000
Parameter[1]: 0000000000000002
Attempt to read from address 0000000000000002

DEFAULT_BUCKET_ID: NULL_CLASS_PTR_READ

PROCESS_NAME: replmerg.exe

ERROR_CODE: (NTSTATUS) 0xc0000005 - The instruction at 0x%08lx referenced memory at 0x%08lx. The memory could not be %s.

EXCEPTION_CODE: (NTSTATUS) 0xc0000005 - The instruction at 0x%08lx referenced memory at 0x%08lx. The memory could not be %s.

EXCEPTION_PARAMETER1: 0000000000000000

EXCEPTION_PARAMETER2: 0000000000000002

READ_ADDRESS: 0000000000000002

What I didn’t realize at the time was that it was actually ssrmin.dll, a custom SQL Replication resolver that says if there is a conflict between two values, the lowest value wins. Looking back at the minidump, and the stack trace, I can see it now…

STACK_TEXT:
00000000`0a1bee60 00000000`70d13482 : 00000000`00000000 00000000`00000000 00000000`00000000 00000000`71f0796c : replrec!CReplRowChange::GetSourceRowData+0x19
00000000`0a1beea0 00000000`70e8deac : 00000000`04192ee0 00000000`00000000 00000000`041adf40 00000000`00000000 : ssrmin!MinResolver::Reconcile+0x1b2
00000000`0a1bfad0 00000000`70e3f807 : 00000000`00000000 00000000`00000000 00000000`00000000 00000000`00000000 : replrec!CReplRowChange::Reconcile+0x123c
00000000`0a1bfc40 00000000`70e66592 : 00000000`04212a08 00000000`00000001 00000000`0b87e4d0 00000000`084e2610 : replrec!CDatabaseReconciler::DoArticleLoopDest+0x167
00000000`0a1bfcc0 00000000`70e7432f : 00000000`00000000 00000000`00000000 00000000`00000001 00000000`0000005e : replrec!CDatabaseReconciler::DestThreadProcessQueue+0x9d2
00000000`0a1bfe80 00000000`738d37d7 : 00000000`04390e00 00000000`00000000 00000000`00000000 00000000`00000000 : replrec!DestThreadProc+0x1af
00000000`0a1bff00 00000000`04390e00 : 00000000`00000000 00000000`00000000 00000000`00000000 00000000`00000000 : msvcr80!endthreadex+0x47
00000000`0a1bff08 00000000`00000000 : 00000000`00000000 00000000`00000000 00000000`00000000 00000000`738d3894 : 0x4390e00

Since I couldn’t dig anything further into the dll’s or the debug, I opened a PSS case. In the mean time, I also started some profile traces on both the publisher and the subscriber. I caught where I thought the last TSQL statements were running before crashing, and in hind-sight it was also showing ssrmin.dll, since the article that was being compared was using that custom Minimum resolver.

I have to say, my experience with PSS (MSDN support contract) prior to this has not been pleasant. Long cycle times and delays after sending massive amounts of data to PSS were normal. This time, that was not the case. I opened the incident with as much detail as I could give, including some of the minidump files and my analysis similar to above. Within a few hours I had an email that the case was assigned and that I should expect a call soon. An hour later I had a voicemail on my work phone from Akbar at PSS. He was reviewing the crash dump files and other details without asking me to re-upload the data!

After a few days of some little back and forth, gathering details of the replication topology and gathering version numbers of key files on all the systems, Akbar came back with his analysis of the crash dump files using the private debugging symbols that are available to PSS. He was able to trace through and see that where he expected a function call to jump into SSRMIN.DLL, it was not occurring as expected. He had me compare the version of SSRMIN.DLL and it was not matched REPLREC.DLL. SSRMIN.DLL was at 10.0.4321.0 (SQL 2008 SP2 CU6) and REPLREC.DLL was at 10.50.1600.1.

SSRMIN.DLL version

SSRMIN.DLL file properties - version showing 10.0.4321.0

SSRMIN.DLL file properties – version showing 10.0.4321.0

REPLREC.DLL file properties - version showing 10.50.1600.1

REPLREC.DLL file properties – version showing 10.50.1600.1

This subscriber also has a side-by-side install of SQL Server 2008 R2 which is why some versions were at 10.50.2500.0. What is odd is that two other subscribers were set up the same and also had side-by-side installs of 2008 and 2008 R2, and their versions of the custom resolvers were all at 10.50.1600.1

As a quick test, I copied SSRMIN.DLL from another subscriber and replaced the 10.0.4321.0 version on the bad subscriber. Merge replication was off and running again without crashing.

So we had our problem, we needed a root cause, and we needed a real fix. What had caused this state were part of the Replication bits were updated when installing SQL Server 2008 R2 to a named instance, and how were we going to properly insure that all the bits got updated properly. Akbar recommended running SP1 for SQL Server 2008 R2, which should update all the bits to 10.50.2500.0. After running SP1, I checked the file versions and SSRMIN.DLL (and all the other SSR*.DLL files) were still at 10.0.4321.0.

After reviewing all setup log for the SQL 2008 R2 install in C:\Program Files\Microsoft SQL Server\100\Setup Bootstrap\Log\ Akbar noticed that the SQL Server 2008 R2 install had only included the Engine, and not Replication. That’s why SP1 did not touch any of the Replication bits. I ran SQL Server 2008 R2 install again, and this time selected Replication. After completing, and checking file versions, all the DLL’s in C:\Program Files\Microsoft SQL Server\100\COM\ were updated to 10.50.2500.0… Yeah, to SP1 version! So we had our fix. We also had the root cause.

Installed bits for SQL Server 2008 and 2008 R2

Showing the bits that are installed on both the SQL 2008 instance and the SQL 2008 R2 instance.

Since then, I have been able to reproduce this state on a lab machine. I installed SQL Server 2008 with the Engine and Replication selected, then I installed SP2, then I installed CU6. Almost all the files in the COM directory were at 10.0.4321.0 (some were at 10.0.1600.0). Then I installed SQL Server 2008 R2 to a named instance and selected only Engine. The results were that most everything was at 10.50.1600.1, but a number of DLL’s were still at 10.0.4321.0. Here’s the list of what was mismatched.
Ssrup.dll 10.0.4321.0
SSRPUB.dll 10.0.4321.0
SSRMIN.dll 10.0.4321.0
SSRMAX.dll 10.0.4321.0
SSRDOWN.dll 10.0.4321.0
SSRAVG.dll 10.0.4321.0
SSRADD.dll 10.0.4321.0
SPRESOLV.DLL 10.0.4321.0
MERGETXT.DLL 10.0.4321.0
Sqlfthndlr.dll 10.0.4321.0

Personally, I think this is caused by having both releases of SQL Server 2008 and SQL Server 2008 R2 share the same C:\Program Files\Microsoft SQL Server\100\ folder. SQL Server 2005 used the \90\ folder and SQL Server 2000 used the \80\ folder. Akbar is still testing things out in his lab to get me a final answer to my hypothesis. Until then, just something to keep in mind when running SQL Server 2008 and 2008 R2 side-by-side on the same server.