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.

2 thoughts on “New DBA Inventory – Database Details

  1. Pingback: Brand new SQL Server Inventory system | SQL Blog

Leave a Reply

Your email address will not be published.

*

This site uses Akismet to reduce spam. Learn how your comment data is processed.