New DBA Inventory – Database 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 Database level, to get Database Properties, Database File information and also Backup details.

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 script to create the needed tables for the Database details, including the Database File Info table and the DatabaseBackups table.

Here’s the script to create these tables — Database_Details_Schema

This script also uses the AltSqlCredentials to allow connections to instances not on the domain… 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.

Here’s the full Powershell script

[void] [System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SMO")
Add-PSSnapin SqlServerCmdletSnapin100 -ErrorAction SilentlyContinue
Add-PSSnapin SqlServerProviderSnapin100 -ErrorAction SilentlyContinue
import-module "C:\DBATools\dbatoolsPost2008.psm1"  -ErrorAction SilentlyContinue

$localPath =  $PSScriptRoot
Import-Module "$localPath\LoadConfig.psm1"
$cfg = LoadConfig "$localPath\InventoryConfig.xml"

$dbaToolsServer = $cfg.Configuration.InventoryDBServer
$inventoryDB = $cfg.Configuration.InventoryDB

Function Get-DBDatabaseInfo ($SQLInstance, $altCredential)
{
	$instance = new-object ('Microsoft.SqlServer.Management.Smo.Server') $SQLInstance

	$sqlQuery="
		SELECT  '$SQLInstance' as 'InstanceName',
                        d.database_id as 'DatabaseID',
                        d.name AS 'DatabaseName' ,
                        d.create_date as 'CreateDate',
                        cast(drs.database_guid as varchar(36)) as 'DatabaseGuid',
                        cast(drs.family_guid as varchar(36)) as 'FamilyGuid',
                        suser_sname(d.owner_sid) as 'Owner',
                        d.user_access_desc AS 'AccessDesc' ,
                        d.recovery_model_desc AS 'RecoveryModel',
                        d.compatibility_level AS 'CompatibilityLevel',
                        d.state as 'State',
                        d.state_desc as 'StateDesc',
                        d.page_verify_option_desc AS 'PageVerify' ,
                        d.snapshot_isolation_state_desc AS 'AllowSnapshotIsolation' ,
                        cast(d.is_ansi_null_default_on as tinyint) AS 'ANSINULLDefault' ,
                        cast(d.is_ansi_nulls_on as tinyint) AS 'ANSINULLSEnabled' ,
                        cast(d.is_ansi_padding_on as tinyint) AS 'ANSIPaddingsEnabled' ,
                        cast(d.is_ansi_warnings_on as tinyint) AS 'ANSIWarningsEnabled' ,
                        cast(d.is_arithabort_on as tinyint) AS 'ArithmeticAbortEnabled' ,
                        cast(d.is_auto_close_on as tinyint) AS 'AutoCLOSE' ,
                        cast(d.is_auto_create_stats_on as tinyint) AS 'AutoCreateStats' ,
                        cast(d.is_auto_shrink_on as tinyint) AS 'AutoShrink' ,
                        cast(d.is_auto_update_stats_async_on as tinyint) AS 'AutoUpdateStatsAsync' ,
                        cast(d.is_auto_update_stats_on as tinyint) AS 'AutoUpdateStats' ,
                        cast(d.is_cursor_close_on_commit_on as tinyint) AS 'CloseCursorsOnCommitEnabled' ,
                        cast(d.is_concat_null_yields_null_on as tinyint) AS 'ConcatenateNullYieldsNull' ,
                        cast(d.is_db_chaining_on as tinyint) AS 'CrossDatabaseOwnershipChainingEnabled' ,
                        cast(d.is_date_correlation_on as tinyint) AS 'DateCorrelationOptimizationEnabled' ,
                        cast(d.is_read_only as tinyint) AS 'DatabaseReadOnly' ,
                        cast(d.is_local_cursor_default as tinyint) AS 'DefaultCursor' ,
                        cast(d.is_encrypted as tinyint) AS 'EncryptionEnabled' ,
                        cast(d.is_arithabort_on as tinyint) AS 'NumericRoundAbort' ,
                        cast(d.is_parameterization_forced as tinyint) AS 'Parameterization' ,
                        cast(d.is_quoted_identifier_on as tinyint) AS 'QuotedIdentifiersEnabled' ,
                        cast(d.is_read_committed_snapshot_on as tinyint) AS 'ReadCommittedSnapshot' ,
                        cast(d.is_recursive_triggers_on as tinyint) AS 'RecursiveTriggersEnabled' ,
                        cast(d.is_broker_enabled as tinyint) AS 'ServiceBrokerEnabled' ,
                        cast(d.is_trustworthy_on as tinyint) AS 'Trustworthy',
                        cast(d.is_published as tinyint) AS 'Published',
                        cast(d.is_subscribed as tinyint) AS 'Subscribed',
                        cast(d.is_merge_published as tinyint) AS 'MergPublish',
                        cast(d.is_distributor as tinyint) AS 'Distributor',
                        d.collation_name AS 'CollationName',
                        d.log_reuse_wait_desc AS 'LogReuseWait'
        	FROM    sys.databases d
        	INNER JOIN sys.database_recovery_status drs
        	 ON d.database_id = drs.database_id ;
	"	

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

			$user=$altCredential.UserName
			$pass = $altCredential.GetNetworkCredential().Password
			$splat = @{
				UserName = $user
				Password = $pass
				ServerInstance = $SQLInstance
				Database = 'master'
				Query = $sqlQuery
			}
			$dbs = Invoke-SqlCmd  @splat
		} else { 
			$dbs = QuerySql $SQLInstance 'master' $sqlQuery 60000
		}
		if ($dbs.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 = "DatabaseDetails_STAGE"
			$bc.WriteToServer($dbs)
			$cn.Close()
		}
   
	} catch {
		$errMessage = $_.Exception
		$jobName = Split-Path -Path $myInvocation.ScriptName -Leaf
		write-output "Error connecting: $SQLInstance - $errMessage" 

		$errorSQL = "INSERT INTO INVJobErrors 
			(auditdt, jobname, error) 
			values ('$startDate','$jobName','$SQLInstance - $errMessage')"
		QuerySql $dbaToolsServer $inventoryDB $errorSQL 60000
	} ###  try

}


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

$startDate = get-date

$sqlQuery = "TRUNCATE TABLE [DatabaseDetails_STAGE]"

	
QuerySql $dbaToolsServer $inventoryDB $sqlQuery 60000
#$statusID =$results[0]
#$rangeStart = $results[1]

$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)
{
	$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 
		$pass
		$altCredential = New-Object -TypeName System.Management.Automation.PsCredential `
			-ArgumentList $user, $pass
	}

	Get-DBDatabaseInfo $instanceName $altCredential 
}

$sqlQuery="
BEGIN TRAN
MERGE DatabaseDetails as T
USING DatabaseDetails_STAGE as S
ON (T.InstanceName = S.InstanceName
	AND T.DatabaseID = S.DatabaseID)
WHEN NOT MATCHED BY TARGET
	THEN INSERT 
	(
		[InstanceName],
		[DatabaseID],
		[DatabaseName],
		[CreateDate],
		[DatabaseGuid],
		[FamilyGuid],
		[Owner],
		[AccessDesc],
		[RecoveryModel],
		[CompatibilityLevel],
		[State],
		[StateDesc],
		[PageVerify],
		[AllowSnapshotIsolation],
		[ANSINULLDefault],
		[ANSINULLSEnabled],
		[ANSIPaddingsEnabled],
		[ANSIWarningsEnabled],
		[ArithmeticAbortEnabled],
		[AutoCLOSE],
		[AutoCreateStats],
		[AutoShrink],
		[AutoUpdateStatsAsync],
		[AutoUpdateStats],
		[CloseCursorsOnCommitEnabled],
		[ConcatenateNullYieldsNull],
		[CrossDatabaseOwnershipChainingEnabled],
		[DateCorrelationOptimizationEnabled],
		[DatabaseReadOnly],
		[DefaultCursor],
		[EncryptionEnabled],
		[NumericRoundAbort],
		[Parameterization],
		[QuotedIdentifiersEnabled],
		[ReadCommittedSnapshot],
		[RecursiveTriggersEnabled],
		[ServiceBrokerEnabled],
		[Trustworthy],
		[Published],
		[Subscribed], 
		[MergPublish],
		[Distributor],
		[CollationName],
		[LogReuseWait]
	) VALUES (
		S.[InstanceName],
		S.[DatabaseID],
		S.[DatabaseName],
		S.[CreateDate],
		S.[DatabaseGuid],
		S.[FamilyGuid],
		S.[Owner],
		S.[AccessDesc],
		S.[RecoveryModel],
		S.[CompatibilityLevel],
		S.[State],
		S.[StateDesc],
		S.[PageVerify],
		S.[AllowSnapshotIsolation],
		S.[ANSINULLDefault],
		S.[ANSINULLSEnabled],
		S.[ANSIPaddingsEnabled],
		S.[ANSIWarningsEnabled],
		S.[ArithmeticAbortEnabled],
		S.[AutoCLOSE],
		S.[AutoCreateStats],
		S.[AutoShrink],
		S.[AutoUpdateStatsAsync],
		S.[AutoUpdateStats],
		S.[CloseCursorsOnCommitEnabled],
		S.[ConcatenateNullYieldsNull],
		S.[CrossDatabaseOwnershipChainingEnabled],
		S.[DateCorrelationOptimizationEnabled],
		S.[DatabaseReadOnly],
		S.[DefaultCursor],
		S.[EncryptionEnabled],
		S.[NumericRoundAbort],
		S.[Parameterization],
		S.[QuotedIdentifiersEnabled],
		S.[ReadCommittedSnapshot],
		S.[RecursiveTriggersEnabled],
		S.[ServiceBrokerEnabled],
		S.[Trustworthy],
		S.[Published],
		S.[Subscribed], 
		S.[MergPublish],
		S.[Distributor],
		S.[CollationName],
		S.[LogReuseWait]
	)
WHEN MATCHED AND S.DatabaseName is not null
	THEN UPDATE 
	SET 	T.[InstanceName] = S.[InstanceName],
		T.[DatabaseID] = S.[DatabaseID],
		T.[DatabaseName] = S.[DatabaseName],
		T.[CreateDate] = S.[CreateDate],
		T.[DatabaseGuid] = S.[DatabaseGuid],
		T.[FamilyGuid] = S.[FamilyGuid],
		T.[Owner] = S.[Owner],
		T.[AccessDesc] = S.[AccessDesc],
		T.[RecoveryModel] = S.[RecoveryModel],
		T.[CompatibilityLevel] = S.[CompatibilityLevel],
		T.[State] = S.[State] ,
		T.[StateDesc] = S.[StateDesc],
		T.[PageVerify] = S.[PageVerify],
		T.[AllowSnapshotIsolation] = S.[AllowSnapshotIsolation],
		T.[ANSINULLDefault] = S.[ANSINULLDefault],
		T.[ANSINULLSEnabled] = S.[ANSINULLSEnabled],
		T.[ANSIPaddingsEnabled] = S.[ANSIPaddingsEnabled] ,
		T.[ANSIWarningsEnabled] = S.[ANSIWarningsEnabled] ,
		T.[ArithmeticAbortEnabled] = S.[ArithmeticAbortEnabled],
		T.[AutoCLOSE] = S.[AutoCLOSE] ,
		T.[AutoCreateStats] = S.[AutoCreateStats],
		T.[AutoShrink] = S.[AutoShrink],
		T.[AutoUpdateStatsAsync] = S.[AutoUpdateStatsAsync],
		T.[AutoUpdateStats] = S.[AutoUpdateStats],
		T.[CloseCursorsOnCommitEnabled] = S.[CloseCursorsOnCommitEnabled],
		T.[ConcatenateNullYieldsNull] = S.[ConcatenateNullYieldsNull],
		T.[CrossDatabaseOwnershipChainingEnabled] = S.[CrossDatabaseOwnershipChainingEnabled],
		T.[DateCorrelationOptimizationEnabled] = S.[DateCorrelationOptimizationEnabled],
		T.[DatabaseReadOnly] = S.[DatabaseReadOnly],
		T.[DefaultCursor] = S.[DefaultCursor],
		T.[EncryptionEnabled] = S.[EncryptionEnabled],
		T.[NumericRoundAbort] = S.[NumericRoundAbort],
		T.[Parameterization] = S.[Parameterization],
		T.[QuotedIdentifiersEnabled] = S.[QuotedIdentifiersEnabled],
		T.[ReadCommittedSnapshot] = S.[ReadCommittedSnapshot],
		T.[RecursiveTriggersEnabled] = S.[RecursiveTriggersEnabled],
		T.[ServiceBrokerEnabled] = S.[ServiceBrokerEnabled],
		T.[Trustworthy] = S.[Trustworthy],
		T.[Published] = S.[Published],
		T.[Subscribed] = S.[Subscribed], 
		T.[MergPublish] = S.[MergPublish],
		T.[Distributor] = S.[Distributor],
		T.[CollationName] = S.[CollationName],
		T.[LogReuseWait] = S.[LogReuseWait],
		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
		('DATABASESTATUS','C','$startDate', '$endDate', '', '', '')"



QuerySql $dbaToolsServer $inventoryDB $sqlQuery 60000

exit

In the main function, we truncate the DatabaseDetails_STAGE table. Then we query the InstanceDetails table and join to the AltSqlCredentials for the list of instances we want to pull database info from.

$sqlQuery = "TRUNCATE TABLE [DatabaseDetails_STAGE]"

	
QuerySql $dbaToolsServer $inventoryDB $sqlQuery 60000
#$statusID =$results[0]
#$rangeStart = $results[1]

$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

Next we iterate through the list of Instances returned and call Get-DBDatabaseInfo function for each.

foreach ($instance in $instances)
{
	$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 
		$pass
		$altCredential = New-Object -TypeName System.Management.Automation.PsCredential `
			-ArgumentList $user, $pass
	}

	Get-DBDatabaseInfo $instanceName $altCredential 
}

Inside the function we connect and execute this query which gets us all the database configuration we need. We use the alternate credentials if passed as well.

	$sqlQuery="
		SELECT  '$SQLInstance' as 'InstanceName',
                        d.database_id as 'DatabaseID',
                        d.name AS 'DatabaseName' ,
                        d.create_date as 'CreateDate',
                        cast(drs.database_guid as varchar(36)) as 'DatabaseGuid',
                        cast(drs.family_guid as varchar(36)) as 'FamilyGuid',
                        suser_sname(d.owner_sid) as 'Owner',
                        d.user_access_desc AS 'AccessDesc' ,
                        d.recovery_model_desc AS 'RecoveryModel',
                        d.compatibility_level AS 'CompatibilityLevel',
                        d.state as 'State',
                        d.state_desc as 'StateDesc',
                        d.page_verify_option_desc AS 'PageVerify' ,
                        d.snapshot_isolation_state_desc AS 'AllowSnapshotIsolation' ,
                        cast(d.is_ansi_null_default_on as tinyint) AS 'ANSINULLDefault' ,
                        cast(d.is_ansi_nulls_on as tinyint) AS 'ANSINULLSEnabled' ,
                        cast(d.is_ansi_padding_on as tinyint) AS 'ANSIPaddingsEnabled' ,
                        cast(d.is_ansi_warnings_on as tinyint) AS 'ANSIWarningsEnabled' ,
                        cast(d.is_arithabort_on as tinyint) AS 'ArithmeticAbortEnabled' ,
                        cast(d.is_auto_close_on as tinyint) AS 'AutoCLOSE' ,
                        cast(d.is_auto_create_stats_on as tinyint) AS 'AutoCreateStats' ,
                        cast(d.is_auto_shrink_on as tinyint) AS 'AutoShrink' ,
                        cast(d.is_auto_update_stats_async_on as tinyint) AS 'AutoUpdateStatsAsync' ,
                        cast(d.is_auto_update_stats_on as tinyint) AS 'AutoUpdateStats' ,
                        cast(d.is_cursor_close_on_commit_on as tinyint) AS 'CloseCursorsOnCommitEnabled' ,
                        cast(d.is_concat_null_yields_null_on as tinyint) AS 'ConcatenateNullYieldsNull' ,
                        cast(d.is_db_chaining_on as tinyint) AS 'CrossDatabaseOwnershipChainingEnabled' ,
                        cast(d.is_date_correlation_on as tinyint) AS 'DateCorrelationOptimizationEnabled' ,
                        cast(d.is_read_only as tinyint) AS 'DatabaseReadOnly' ,
                        cast(d.is_local_cursor_default as tinyint) AS 'DefaultCursor' ,
                        cast(d.is_encrypted as tinyint) AS 'EncryptionEnabled' ,
                        cast(d.is_arithabort_on as tinyint) AS 'NumericRoundAbort' ,
                        cast(d.is_parameterization_forced as tinyint) AS 'Parameterization' ,
                        cast(d.is_quoted_identifier_on as tinyint) AS 'QuotedIdentifiersEnabled' ,
                        cast(d.is_read_committed_snapshot_on as tinyint) AS 'ReadCommittedSnapshot' ,
                        cast(d.is_recursive_triggers_on as tinyint) AS 'RecursiveTriggersEnabled' ,
                        cast(d.is_broker_enabled as tinyint) AS 'ServiceBrokerEnabled' ,
                        cast(d.is_trustworthy_on as tinyint) AS 'Trustworthy',
                        cast(d.is_published as tinyint) AS 'Published',
                        cast(d.is_subscribed as tinyint) AS 'Subscribed',
                        cast(d.is_merge_published as tinyint) AS 'MergPublish',
                        cast(d.is_distributor as tinyint) AS 'Distributor',
                        d.collation_name AS 'CollationName',
                        d.log_reuse_wait_desc AS 'LogReuseWait'
        	FROM    sys.databases d
        	INNER JOIN sys.database_recovery_status drs
        	 ON d.database_id = drs.database_id ;

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

			$user=$altCredential.UserName
			$pass = $altCredential.GetNetworkCredential().Password
			$splat = @{
				UserName = $user
				Password = $pass
				ServerInstance = $SQLInstance
				Database = 'master'
				Query = $sqlQuery
			}
			$dbs = Invoke-SqlCmd  @splat
		} else { 
			$dbs = QuerySql $SQLInstance 'master' $sqlQuery 60000
		}
		if ($dbs.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 = "DatabaseDetails_STAGE"
			$bc.WriteToServer($dbs)
			$cn.Close()
		}
   
	} catch {
		$errMessage = $_.Exception
		$jobName = Split-Path -Path $myInvocation.ScriptName -Leaf
		write-output "Error connecting: $SQLInstance - $errMessage" 

		$errorSQL = "INSERT INTO INVJobErrors 
			(auditdt, jobname, error) 
			values ('$startDate','$jobName','$SQLInstance - $errMessage')"
		QuerySql $dbaToolsServer $inventoryDB $errorSQL 60000
	} ###  try

Next post will detail the Database File info gathering, there are two scripts we use for that based on if we want / need to use SMO. I’ll also follow up with the backup status script, which is really neat. I can use it to show “Overdue” or “Missed” backups, so we always know the status of backups. Also from the Inventory web page, I can generate a restore script to restore any backed up database to any server. It will prompt me for the Source DB, the “Restore To” date to use as a “Stop At”, the Target Instance, which it uses to generate alternate files / paths to restore the database to if needed.

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