Category Archives: Uncategorized

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

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.

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