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.
Pingback: Brand new SQL Server Inventory system | SQL Blog
amazing collection of scripts.
thank you