Building a SQL Server Inventory – Part 2

Last time I went through my script to gather server information for my inventory. Today I’m going to go through my script to collect SQL Server instance data. So, I think I made it clear last time that I’m not a Powershell guru, and there are probably countless ways in which this script can be improved, yada yada. ‘Nuff said.

Credits

Like the server info script, this script is based heavily on Allen White’s inventory script on Simple-Talk. If you haven’t already seen it, I highly recommend checking out the entire article. I’ve made some changes to Allen’s base script, to suit my own needs. Whereas Allen writes his output to a csv file, I wanted to load directly into my inventory database. Allen also reads in his list of servers to inventory from a file, I keep mine in a master table, also in my inventory database.

Tools you’ll need

In order to load your inventory data directly into SQL Server, you’ll need to download a couple of PowerShell functions.

Name: Write-DataTable.ps1
Author: Chad Miller
http://gallery.technet.microsoft.com/scriptcenter/2fdeaf8d-b164-411c-9483-99413d6053ae
Loads data into from a datatable object into SQL Server tables

Name: Out-DataTable.ps1
Author: Chad Miller
http://gallery.technet.microsoft.com/scriptcenter/4208a159-a52e-4b99-83d4-8048468d29dd
Formats input into a datatable object which can then be imported into SQL Server using Write-DataTable.

Permissions required

You’ll need a SQL Server login on each instance you plan to inventory. The permissions for that login will depend on what data you’re collecting. You can use the principle of least permissions and only grant the minimum required, which is what I do. In fact I use certificate-signed procedures to gather any info that requires sysadmin-level rights. This method requires creating procedures on all target servers, which was a larger footprint than I would have preferred, but I felt it was a better option from a security standpoint. Or you can simply use a sysadmin login and be done with it. Potayto potahto.

The moment you’ve all been waiting for – The script

Parameters

My script accepts 4 parameters:

Inventory database instance
Inventory login
Inventory password (I use the same login to collect my info and store it)
Environment (Which environment do I want to inventory?)

A word of explanation about the Environment parameter. As I mentioned earlier, I store my master instance list in a table inside my inventory database. That table holds all instances across all of my environments, Production, Development, etc., and the environment for each instance is also stored in this master table. When I run this script I use this parameter to specify which environment I want to inventory.

Clean out tables pre-run

This script performs a full load every time, so my first step is simply to clear out any existing data.


$connection = new-object system.data.sqlclient.sqlconnection( `
    "Data Source=$ssi_instance;Initial Catalog=$ssi_database;User Id=$ssi_userid; Password=$ssi_passwd;");
$connection.Open()
$cmd = $connection.CreateCommand()

##### Clean out all tables pre-run. Move Jobs to Jobs_Compare, that way we can see what jobs were dropped #####

$query = "select instance_name_short into #cmm from Servers where Environment in ('$ssi_env')
      DELETE FROM [SQL].Login_Info where instance_name in (select instance_name_short from #cmm);
      DELETE FROM [SQL].Database_Users where instance_name in (select instance_name_short from #cmm);
      DELETE FROM [SQL].Database_Files where instance_name in (select instance_name_short from #cmm);
      DELETE FROM [SQL].Database_Roles where instance_name in (select instance_name_short from #cmm);
      DELETE FROM [SQL].Server_Roles where instance_name in (select instance_name_short from #cmm);
      DELETE FROM [SQL].Credentials where instance_name in (select instance_name_short from #cmm);
      DELETE FROM [SQL].Linked_Server_Logins where instance_name in (select instance_name_short from #cmm);
      DELETE FROM [SQL].Linked_Servers where instance_name in (select instance_name_short from #cmm);
      DELETE FROM [SQL].Job_Schedules where instance_name in (select instance_name_short from #cmm);
      DELETE FROM [SQL].Schedule_Info where instance_name in (select instance_name_short from #cmm);
      DELETE FROM [SQL].Job_Info_Compare where instance_name in (select instance_name_short from #cmm);
      INSERT INTO [SQL].Job_Info_Compare SELECT * FROM [SQL].Job_Info where instance_name in (select instance_name_short from #cmm);
      DELETE FROM [SQL].Job_Info where instance_name in (select instance_name_short from #cmm);
      DELETE FROM [SQL].Job_Proxies where instance_name in (select instance_name_short from #cmm);
      DELETE FROM [SQL].Proxies where instance_name in (select instance_name_short from #cmm);
      DELETE FROM [SQL].Backup_Info where instance_name in (select instance_name_short from #cmm);
      DELETE FROM [SQL].Database_Info where instance_name in (select instance_name_short from #cmm);
      DELETE FROM [SQL].Instance_Info where instance_name in (select instance_name_short from #cmm);
      "

$cmd.CommandText = $query
$null = $cmd.ExecuteNonQuery()

Get list of servers to inventory

The next step in the process is to read the master table to get the list of servers to inventory. Then we’ll step through them and for each one we’ll check and make sure it’s online and if it is, we’ll execute the get-sqlinfo function.


##### Get the list of servers to inventory #####

$query = "SELECT DISTINCT server_name as ServerName, server_name_short as ServerNameShort, instance_name as InstanceName, instance_name_short as InstanceNameShort FROM Servers WHERE Environment IN ('$ssi_env')"
$cmd.CommandText = $query
$reader = $cmd.ExecuteReader()

##### For every server gather data #####

while($reader.Read()) {

    ##### See if the server is alive #####

    $svr = $reader['ServerName']
    $svrshort = $reader['ServerNameShort']
    $inst = $reader['InstanceName']
    $instshort = $reader['InstanceNameShort']
    $result = new-object Microsoft.SqlServer.Management.Common.ServerConnection($inst, $ssi_userid, $ssi_passwd)
       $responds = $false
    if ($result.isopen -eq 0) {
        $responds = $true
    }

    ##### If it is alive ... #####

    If ($responds) {
        #Write-Output "$inst is alive"
        get-sqlinfo $svr $svrshort $inst $instshort

    }
    else {

              # Let the user know we couldn't connect to the server
              Write-Output "$svr does not respond"
       }

}

Get-sqlinfo Function

The last part of the script is to actually gather the information.

First up on the list: Instance info. As you’ll soon see, most of my script uses the same basic format, over and over. The instance info section is the exception. Here I’m making use of the Server SMO object class to get additional instance-level information, like the version, edition, collation, whether it’s clustered, and database/log paths. I could have used T-SQL to get all of this info, like the rest of the script, but this way seemed easier and it let me add another tool to the arsenal. I then combine this information with some additional fields I’ve obtained using T-SQL. To load the data into our Inventory database, we use the same Write-DataTable and Out-Datatable functions we used in the server inventory script.


# Create an ADO.Net connection to the instance

    $ServerConn = new-object Microsoft.SqlServer.Management.Common.ServerConnection
    $ServerConn.ServerInstance="$instance"
    $ServerConn.LoginSecure = $false
    $ServerConn.Login = $ssi_userid
    $ServerConn.Password = $ssi_passwd
    $ServerConn.Connect()

    $s = new-object Microsoft.SqlServer.Management.SMO.Server($ServerConn)
    ##### Get SQL Instance Info #####
    $ssi_table="SQL.Instance_Info"

    $connection2 = new-object system.data.sqlclient.sqlconnection( `
            "Data Source=$instance;Initial Catalog=master;User Id=$ssi_userid; Password=$ssi_passwd;");
    $connection2.Open()
    $cmd2 = $connection2.CreateCommand()

    $query = "DECLARE @ServiceAccount varchar(100)
            EXECUTE master.dbo.xp_instance_regread
            N'HKEY_LOCAL_MACHINE',
            N'SYSTEM\CurrentControlSet\Services\MSSQLSERVER',
            N'ObjectName',
            @ServiceAccount OUTPUT,
            N'no_output'
            SELECT @ServiceAccount as svc_acct
            ,(select value from sys.configurations where name = 'cost threshold for parallelism') as parallel_threshold
            ,(select value from sys.configurations where name = 'max degree of parallelism') as max_dop
            ,(select value from sys.configurations where name = 'min server memory (MB)') as min_memory
            ,(select value from sys.configurations where name = 'max server memory (MB)') as max_memory
            ,(select value from sys.configurations where name = 'xp_cmdshell') as xp_cmdshell_enabled"

    $cmd2.CommandText = $query
    $reader2 = $cmd2.ExecuteReader()
    if ($reader2.Read() )
    {
        $svcacct = $reader2['svc_acct']
        $parallel_threshold = $reader2['parallel_threshold']
        $max_dop = $reader2['max_dop']
        $min_memory = $reader2['min_memory']
        $max_memory = $reader2['max_memory']
        $xp_cmdshell_enabled = $reader2['xp_cmdshell_enabled']
    }

    $dt= $s.Information | SELECT @{n="InstanceName";e={$instanceshort}}, VersionString, ProductLevel, EngineEdition, Collation, IsClustered, MasterDBPath, MasterDBLogPath, RootDirectory, @{n="ServiceAccount";e={$svcacct}},@{n="parallel_threshold";e={$parallel_threshold}}, @{n="max_dop";e={$max_dop}}, @{n="min_memory";e={$min_memory}}, @{n="max_memory";e={$max_memory}}, @{n="xp_cmdshell_enabled";e={$xp_cmdshell_enabled}} | Out-DataTable

    Write-DataTable -ServerInstance $ssi_instance -Database $ssi_database -TableName $ssi_table -Data $dt -Username $ssi_userid -Password $ssi_passwd

For login information, I’m simply defining a connection string and a query string that gets me the login data I want. I create a SQLDataAdapter object ($da), use it to fill a DataTable object ($dt), and load it up into my Inventory table.


##### Get Login Info #####

    $conn = "server=$instance;database=master;User ID=$ssi_userid;password=$ssi_passwd"

    $ssi_table="SQL.Login_Info"

    $query = "SELECT @@SERVERNAME as instance_name, sp.[name] as LoginName, sp.create_date AS AccountCreateDate,
                sp.modify_date AS LastTimeAccountModified,
                LOGINPROPERTY( sp.[name], 'PasswordLastSetTime' ) AS PasswordLastSetTime,
                sp.default_database_name as DefaultDatabase,
                sp.default_language_name as DefaultLanguage,
                sl.is_policy_checked as IsPolicyChecked,
                sl.is_expiration_checked as IsExpirationChecked,
            LOGINPROPERTY( sp.[name], 'IsExpired' ) AS IsExpired,
            LOGINPROPERTY (sp.[name], 'DaysUntilExpiration' ) AS DaysUntilExpiration,
            LOGINPROPERTY( sp.[name], 'IsLocked' ) AS IsLocked,
                LOGINPROPERTY( sp.[name], 'IsMustChange' ) AS IsMustChange,
               LOGINPROPERTY( sp.[name], 'LockoutTime' ) AS LockoutTime,
                LOGINPROPERTY( sp.[name], 'BadPasswordCount' ) AS BadPasswordCount,
                LOGINPROPERTY( sp.[name], 'BadPasswordTime' ) AS BadPasswordTime,
                LOGINPROPERTY( sp.[name], 'HistoryLength' ) AS HistoryLength,
                sp.sid, sp.type
        FROM sys.server_principals sp
        LEFT OUTER JOIN sys.sql_logins sl on sl.sid = sp.sid
        WHERE sp.type not in ('C', 'R')"
    $da = new-object System.Data.SqlClient.SqlDataAdapter ($query, $conn)
      $dt = new-object System.Data.DataTable
      $da.fill($dt) | out-null
    Write-DataTable -ServerInstance $ssi_instance -Database $ssi_database -TableName $ssi_table -Data $dt -Username $ssi_userid -Password $ssi_passwd

For the remainder of the tables, I use the same basic format. The $ssi_table variable specifies my target table, and the $query variable holds the T-SQL query to execute. The rest is all the same, over and over. Could I have simplified it more by creating a function to handle the loading process and simply passed in the $ssi_table and $query? Absolutely. In fact, I’ll leave that for you to do. Call it homework. (And this is why I will probably *not* be the next Ola.) Here’s an example.


##### Get Credential Info #####

    $ssi_table="SQL.Credentials"

    $query = "select @@servername as instance_name, credential_id, name, credential_identity, create_date, modify_date
            from msdb.sys.credentials"
    $da = new-object System.Data.SqlClient.SqlDataAdapter ($query, $conn)
      $dt = new-object System.Data.DataTable
      $da.fill($dt) | out-null
    Write-DataTable -ServerInstance $ssi_instance -Database $ssi_database -TableName $ssi_table -Data $dt -Username $ssi_userid -Password $ssi_passwd

One last thing

The last thing this script is execute a stored procedure in my inventory database called usp_StoreDBGrowth. All this procedure does is archive the newly inserted datafile information into a separate table for historical growth analysis.

The complete script

So here’s the complete script. Give it a whirl and let me know what you think. If you come up with some improvements, I’d love to hear them, too.


# get-SQLInfo.ps1
# usage: ./get-SQLInfo.ps1
# Collects all Inventory information into MyInventory database
# Including:
#    [SQL].Instance_Info;
#    [SQL].Database_Info;
#    [SQL].Login_Info;
#    [SQL].Database_Users;
#    [SQL].Database_Files;
#    [SQL].Server_Roles;
#    [SQL].Database_Roles;
#    [SQL].Linked_Servers;
#    [SQL].Linked_Server_Logins;

param(
    [string]$ssi_instance=$null,
    [string]$ssi_userid=$null,
    [string]$ssi_passwd=$null,
    [string]$ssi_env=$null
     )

. .\write-datatable.ps1
. .\out-datatable.ps1

$ssi_database = "MyInventory"

[System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.SMO') | out-null

[System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.ConnectionInfo') | out-null

function get-sqlinfo ($server, $servershort, $instance, $instanceshort) {

    # Create an ADO.Net connection to the instance

    $ServerConn = new-object Microsoft.SqlServer.Management.Common.ServerConnection
    $ServerConn.ServerInstance="$instance"
    $ServerConn.LoginSecure = $false
    $ServerConn.Login = $ssi_userid
    $ServerConn.Password = $ssi_passwd
    $ServerConn.Connect()

    $s = new-object Microsoft.SqlServer.Management.SMO.Server($ServerConn)
    ##### Get SQL Instance Info #####
    $ssi_table="SQL.Instance_Info"

    $connection2 = new-object system.data.sqlclient.sqlconnection( `
            "Data Source=$instance;Initial Catalog=master;User Id=$ssi_userid; Password=$ssi_passwd;");
    $connection2.Open()
    $cmd2 = $connection2.CreateCommand()

    $query = "DECLARE @ServiceAccount varchar(100)
            EXECUTE master.dbo.xp_instance_regread
            N'HKEY_LOCAL_MACHINE',
            N'SYSTEM\CurrentControlSet\Services\MSSQLSERVER',
            N'ObjectName',
            @ServiceAccount OUTPUT,
            N'no_output'
            SELECT @ServiceAccount as svc_acct
            ,(select value from sys.configurations where name = 'cost threshold for parallelism') as parallel_threshold
            ,(select value from sys.configurations where name = 'max degree of parallelism') as max_dop
            ,(select value from sys.configurations where name = 'min server memory (MB)') as min_memory
            ,(select value from sys.configurations where name = 'max server memory (MB)') as max_memory
            ,(select value from sys.configurations where name = 'xp_cmdshell') as xp_cmdshell_enabled"

    $cmd2.CommandText = $query
    $reader2 = $cmd2.ExecuteReader()
    if ($reader2.Read() )
    {
        $svcacct = $reader2['svc_acct']
        $parallel_threshold = $reader2['parallel_threshold']
        $max_dop = $reader2['max_dop']
        $min_memory = $reader2['min_memory']
        $max_memory = $reader2['max_memory']
        $xp_cmdshell_enabled = $reader2['xp_cmdshell_enabled']
    }

    $dt= $s.Information | SELECT @{n="InstanceName";e={$instanceshort}}, VersionString, ProductLevel, EngineEdition, Collation, IsClustered, MasterDBPath, MasterDBLogPath, RootDirectory, @{n="ServiceAccount";e={$svcacct}},@{n="parallel_threshold";e={$parallel_threshold}}, @{n="max_dop";e={$max_dop}}, @{n="min_memory";e={$min_memory}}, @{n="max_memory";e={$max_memory}}, @{n="xp_cmdshell_enabled";e={$xp_cmdshell_enabled}} | Out-DataTable

    Write-DataTable -ServerInstance $ssi_instance -Database $ssi_database -TableName $ssi_table -Data $dt -Username $ssi_userid -Password $ssi_passwd

    ##### Get Login Info #####

    $conn = "server=$instance;database=master;User ID=$ssi_userid;password=$ssi_passwd"

    $ssi_table="SQL.Login_Info"

    $query = "SELECT @@SERVERNAME as instance_name, sp.[name] as LoginName, sp.create_date AS AccountCreateDate,
                sp.modify_date AS LastTimeAccountModified,
                LOGINPROPERTY( sp.[name], 'PasswordLastSetTime' ) AS PasswordLastSetTime,
                sp.default_database_name as DefaultDatabase,
                sp.default_language_name as DefaultLanguage,
                sl.is_policy_checked as IsPolicyChecked,
                sl.is_expiration_checked as IsExpirationChecked,
            LOGINPROPERTY( sp.[name], 'IsExpired' ) AS IsExpired,
            LOGINPROPERTY (sp.[name], 'DaysUntilExpiration' ) AS DaysUntilExpiration,
            LOGINPROPERTY( sp.[name], 'IsLocked' ) AS IsLocked,
                LOGINPROPERTY( sp.[name], 'IsMustChange' ) AS IsMustChange,
               LOGINPROPERTY( sp.[name], 'LockoutTime' ) AS LockoutTime,
                LOGINPROPERTY( sp.[name], 'BadPasswordCount' ) AS BadPasswordCount,
                LOGINPROPERTY( sp.[name], 'BadPasswordTime' ) AS BadPasswordTime,
                LOGINPROPERTY( sp.[name], 'HistoryLength' ) AS HistoryLength,
                sp.sid, sp.type
        FROM sys.server_principals sp
        LEFT OUTER JOIN sys.sql_logins sl on sl.sid = sp.sid
        WHERE sp.type not in ('C', 'R')"
    $da = new-object System.Data.SqlClient.SqlDataAdapter ($query, $conn)
      $dt = new-object System.Data.DataTable
      $da.fill($dt) | out-null
    Write-DataTable -ServerInstance $ssi_instance -Database $ssi_database -TableName $ssi_table -Data $dt -Username $ssi_userid -Password $ssi_passwd

    ##### Get Credential Info #####

    $ssi_table="SQL.Credentials"

    $query = "select @@servername as instance_name, credential_id, name, credential_identity, create_date, modify_date
            from msdb.sys.credentials"
    $da = new-object System.Data.SqlClient.SqlDataAdapter ($query, $conn)
      $dt = new-object System.Data.DataTable
      $da.fill($dt) | out-null
    Write-DataTable -ServerInstance $ssi_instance -Database $ssi_database -TableName $ssi_table -Data $dt -Username $ssi_userid -Password $ssi_passwd

    ##### Jobs #####

    $ssi_table = "SQL.Job_Info"

    $query = ";with laststatus as (
            select job_id, start_execution_date, stop_execution_date from
            (select job_id, start_execution_date, stop_execution_date, dense_rank() over (partition by job_id order by start_execution_date desc) as rownum
            from msdb..sysjobactivity where start_execution_date is not null) a
                where a.rownum = 1)
          , maxinstance as (
               select max(instance_id) maxinst_id, job_id from msdb.dbo.sysjobhistory
                        group by job_id)
          , laststep as (
            select j.job_id, max(j.instance_id) as laststep_id
                from msdb.dbo.sysjobhistory j
                join maxinstance mi on j.job_id = mi.job_id
                    and j.instance_id < mi.maxinst_id                  join laststatus ls on ls.job_id = j.job_id                      and CAST( CONVERT(VARCHAR, j.run_date) + ' ' + STUFF(STUFF(RIGHT('000000'                       + CONVERT(VARCHAR,j.run_time),6),5,0,':'),3,0,':') AS DATETIME )     >= ls.start_execution_date
            where run_status = 0
            group by j.job_id)
          select @@SERVERNAME as instance_name, j.name as job_name, j.enabled, j.description, j.date_created, j.date_modified
             , l.start_execution_date as last_run_date
             , isnull(case
                when l.stop_execution_date is null then 'In-Progress'
                else case jh.run_status
                        when 0 then 'Failed '
                        when 1 then 'Succeeded '
                        when 2 then 'Retry'
                        when 3 then 'Canceled '
                        when 4 then 'In-progress'
                        when 5 then 'Unknown ' END END , 'Unknown') as 'last_run_status'
             , jhm.message, j.job_id
            FROM msdb.dbo.sysjobs j
                left outer join laststatus l
                    on j.job_id = l.job_id
                left outer join msdb.dbo.sysjobhistory jh
                join maxinstance jh2
                ON jh2.maxinst_id = jh.instance_id
                    and jh2.job_id = jh.job_id
                ON j.job_id = jh.job_id
                LEFT JOIN laststep ls
                JOIN msdb.dbo.sysjobhistory jhm
                    on ls.laststep_id = jhm.instance_id
                    on ls.job_id = jh.job_id
            WHERE j.enabled = 1 and l.start_execution_date is not null"
      $da = new-object System.Data.SqlClient.SqlDataAdapter ($query, $conn)
      $dt = new-object System.Data.DataTable
      $da.fill($dt) | out-null

      Write-DataTable -ServerInstance $ssi_instance -Database $ssi_database -TableName $ssi_table -Data $dt -Username $ssi_userid -Password $ssi_passwd

    ##### Get Schedule Info #####

    $ssi_table="SQL.Schedule_Info"

    $query = "select @@SERVERNAME as instance_name, s.schedule_id, s.enabled, s.freq_type, s.freq_interval,
            s.freq_subday_type, s.freq_subday_interval, s.freq_relative_interval, s.freq_recurrence_factor, s.active_start_time, s.active_end_time
            from msdb.dbo.sysschedules s"
    $da = new-object System.Data.SqlClient.SqlDataAdapter ($query, $conn)
      $dt = new-object System.Data.DataTable
      $da.fill($dt) | out-null
    Write-DataTable -ServerInstance $ssi_instance -Database $ssi_database -TableName $ssi_table -Data $dt -Username $ssi_userid -Password $ssi_passwd

    ##### Get Job Schedule Info #####

    $ssi_table="SQL.Job_Schedules"

    $query = "select @@SERVERNAME as instance_name, js.job_id, js.schedule_id
            from msdb.dbo.sysjobschedules js"
    $da = new-object System.Data.SqlClient.SqlDataAdapter ($query, $conn)
      $dt = new-object System.Data.DataTable
      $da.fill($dt) | out-null
    Write-DataTable -ServerInstance $ssi_instance -Database $ssi_database -TableName $ssi_table -Data $dt -Username $ssi_userid -Password $ssi_passwd

    ##### Get Proxy Info #####

    $ssi_table="SQL.Proxies"

    $query = "select @@servername as instance_name, proxy_id, name, credential_id, enabled, description, user_sid, credential_date_created
            from msdb.dbo.sysproxies"
    $da = new-object System.Data.SqlClient.SqlDataAdapter ($query, $conn)
      $dt = new-object System.Data.DataTable
      $da.fill($dt) | out-null
    Write-DataTable -ServerInstance $ssi_instance -Database $ssi_database -TableName $ssi_table -Data $dt -Username $ssi_userid -Password $ssi_passwd

    ##### Get Job Step Proxy Info #####

    $ssi_table="SQL.Job_Proxies"

    $query = "select @@servername as instance_name, j.job_id, j.name as job_name, s.step_name, s.proxy_id
            from msdb.dbo.sysjobs j
            join msdb.dbo.sysjobsteps s on j.job_id = s.job_id and s.proxy_id is not null"
    $da = new-object System.Data.SqlClient.SqlDataAdapter ($query, $conn)
      $dt = new-object System.Data.DataTable
      $da.fill($dt) | out-null
    Write-DataTable -ServerInstance $ssi_instance -Database $ssi_database -TableName $ssi_table -Data $dt -Username $ssi_userid -Password $ssi_passwd

    ##### Server Roles #####

    $ssi_table = "SQL.Server_Roles"

    $query = "select @@Servername as instance_name, r.name as role_name, m.name as member_name, m.sid
        from sys.server_principals r
        join sys.server_role_members rm on r.principal_id = rm.role_principal_id
        join sys.server_principals m on m.principal_id = rm.member_principal_id"
      $da = new-object System.Data.SqlClient.SqlDataAdapter ($query, $conn)
      $dt = new-object System.Data.DataTable
      $da.fill($dt) | out-null

      Write-DataTable -ServerInstance $ssi_instance -Database $ssi_database -TableName $ssi_table -Data $dt -Username $ssi_userid -Password $ssi_passwd

    ##### Linked Servers #####

    $ssi_table = "SQL.Linked_Servers"

    $query = "declare
         @alias varchar(40)
        ,@regbase nvarchar(30)
        ,@regkey nvarchar(100)
        ,@dirbasepath nvarchar(250)
            ,@dirbasepath2 nvarchar(250)
        SET @regbase = 'HKEY_LOCAL_MACHINE'
        SET @regkey = 'Software\Microsoft\MSSQLServer\Client\ConnectTo\'
        create table #aliases (
        alias varchar(40),
        resolved nvarchar(250))
        declare a1 cursor for select data_source from sys.servers
        open a1
        fetch a1 into @alias
        while @@FETCH_STATUS = 0
        begin
        set @dirbasepath = NULL
        EXECUTE master..xp_regread @regbase
            , @regkey
            , @alias
            , @dirbasepath OUTPUT
            insert into #aliases
            SELECT @alias, substring(@dirbasepath,CHARINDEX(',',@dirbasepath)+1,LEN(@dirbasepath))
        fetch a1 into @alias
        end
        close a1
        deallocate a1
        select distinct @@servername as [Instance], s.name as [Linked_Server], ISNULL(tmp.resolved, s.data_source) as [Remote_Instance], s.provider as [Provider], s.catalog as [Default_Database]
            from master.sys.servers s
            LEFT OUTER JOIN #aliases tmp ON tmp.alias = s.name
            where is_data_access_enabled = 1"

      $da = new-object System.Data.SqlClient.SqlDataAdapter ($query, $conn)
      $dt = new-object System.Data.DataTable
      $da.fill($dt) | out-null

      Write-DataTable -ServerInstance $ssi_instance -Database $ssi_database -TableName $ssi_table -Data $dt -Username $ssi_userid -Password $ssi_passwd

    ##### Linked Server Logins #####

    $ssi_table = "SQL.Linked_Server_Logins"

    $query = "SELECT @@servername as [instance_name]
        , s.name as [linked_server]
        , CASE l.uses_self_credential
            WHEN 1 THEN 'UNMAPPED LOGINS'
            ELSE ISNULL(p.name, 'UNMAPPED LOGINS')
            END AS [local_login]
        , CASE l.uses_self_credential
            WHEN 1 THEN 'USE SELF'
            ELSE l.remote_name
            END AS [remote_login]
        FROM sys.linked_logins l
            JOIN sys.servers s ON s.server_id = l.server_id AND is_data_access_enabled = 1
            LEFT OUTER JOIN sys.server_principals p ON p.principal_id = l.local_principal_id
        ORDER BY [linked_server], [local_login]"

      $da = new-object System.Data.SqlClient.SqlDataAdapter ($query, $conn)
      $dt = new-object System.Data.DataTable
      $da.fill($dt) | out-null

      Write-DataTable -ServerInstance $ssi_instance -Database $ssi_database -TableName $ssi_table -Data $dt -Username $ssi_userid -Password $ssi_passwd

    ##### Get SQL Database Info #####
    $ssi_table="SQL.Database_Info"

    $query = "select @@SERVERNAME as [instance_name], d.database_id, d.name as [database_name], d.state, d.recovery_model, d.compatibility_level, d.collation_name,
            b.bkup_dt as last_backup_date,
            b.diff_dt as last_differential_backup_date,
            b.log_dt as last_log_backup_date,
            d.create_date,
            --data space usage, index space usage
            isnull(dm.mirroring_role, 0) as is_mirroring_enabled,
            p.name as [owner]
            --primary file path, backup dir
        from master.sys.databases d
        join master.sys.database_mirroring dm on d.database_id = dm.database_id
        join master.sys.server_principals p on d.owner_sid = p.sid
        left join
            (SELECT bs.database_name,
            MAX(case bs.type when 'D' then bs.backup_start_date end) as bkup_dt,
            MAX(case bs.type when 'I' then bs.backup_start_date end) as diff_dt,
            MAX(case bs.type when 'L' then bs.backup_start_date end) as log_dt
            FROM msdb.dbo.backupset bs
            WHERE bs.server_name = @@Servername
            and bs.is_copy_only = 0
            group by bs.database_name) b on b.database_name = d.name"

      $da = new-object System.Data.SqlClient.SqlDataAdapter ($query, $conn)
      $dt = new-object System.Data.DataTable
      $da.fill($dt) | out-null

      Write-DataTable -ServerInstance $ssi_instance -Database $ssi_database -TableName $ssi_table -Data $dt -Username $ssi_userid -Password $ssi_passwd

    ##### Get Database User Info #####

    $ssi_table="SQL.Database_Users"

    $query = "exec sp_get_database_users"
    $da = new-object System.Data.SqlClient.SqlDataAdapter ($query, $conn)
      $dt = new-object System.Data.DataTable
      $da.fill($dt) | out-null
    Write-DataTable -ServerInstance $ssi_instance -Database $ssi_database -TableName $ssi_table -Data $dt -Username $ssi_userid -Password $ssi_passwd

    ##### Database Roles #####

    $ssi_table = "SQL.Database_Roles"

    $query = "exec sp_get_database_roles"
      $da = new-object System.Data.SqlClient.SqlDataAdapter ($query, $conn)
      $dt = new-object System.Data.DataTable
      $da.fill($dt) | out-null

      Write-DataTable -ServerInstance $ssi_instance -Database $ssi_database -TableName $ssi_table -Data $dt -Username $ssi_userid -Password $ssi_passwd

    ##### Database    Files #####

    $ssi_table = "SQL.Database_Files"

    $query = "select @@SERVERNAME as instance_name, DB_Name(database_id) as database_name, file_id, type_desc, name, physical_name, (size)*8/1024 as size_mb
        ,case (is_percent_growth) WHEN 1 THEN growth ELSE 0 END  as growth_percent
        ,case (is_percent_growth) WHEN 0 THEN growth*8/1024 ELSE 0 END  as growth_mb
        from sys.master_files
        WHERE type in (0, 1)"
      $da = new-object System.Data.SqlClient.SqlDataAdapter ($query, $conn)
      $dt = new-object System.Data.DataTable
      $da.fill($dt) | out-null

      Write-DataTable -ServerInstance $ssi_instance -Database $ssi_database -TableName $ssi_table -Data $dt -Username $ssi_userid -Password $ssi_passwd

    ##### Backup Info #####

    $ssi_table = "SQL.Backup_Info"

    $query = "DECLARE @db_name sysname ,@restore_to_datetime  datetime , @server_name nvarchar(512);
select @restore_to_datetime = GETDATE();
set @server_name = cast(serverproperty(N'Servername') as nvarchar(512));

        CREATE TABLE #backupset(
            backup_set_id            INTEGER    NOT NULL,
            is_in_restore_plan        BIT NOT NULL,
            backup_start_date        DATETIME NOT NULL,
            type                CHAR(1)    NOT NULL,
            database_name            NVARCHAR(256) NOT NULL,
            database_guid            UNIQUEIDENTIFIER,
            family_guid            UNIQUEIDENTIFIER,
            first_recovery_fork_guid    UNIQUEIDENTIFIER,
            last_recovery_fork_guid        UNIQUEIDENTIFIER,
            first_lsn            NUMERIC(25, 0),
            last_lsn            NUMERIC(25, 0),
            checkpoint_lsn            NUMERIC(25, 0),
            database_backup_lsn        NUMERIC(25, 0),
            fork_point_lsn            NUMERIC(25, 0),
            restore_till_lsn        NUMERIC(25, 0),
            backup_set_uuid            UNIQUEIDENTIFIER,
            differential_base_guid        UNIQUEIDENTIFIER
        );

         SELECT backupset_outer.database_name
            ,    backupset_outer.backup_set_id
              ,backupset_outer.backup_start_date
          into #most_recent
          FROM msdb.dbo.backupset backupset_outer
             WHERE  backupset_outer.server_name = @server_name
           AND backupset_outer.type <> 'L' -- Full and differential Database Backups
           AND backupset_outer.backup_start_date 
                 = (  SELECT MAX(backupset_inner.backup_start_date)
                      FROM msdb.dbo.backupset backupset_inner
                      WHERE backupset_inner.database_name = backupset_outer.database_name
                      AND backupset_inner.server_name = @server_name
                      AND backupset_inner.type = backupset_outer.type
                      AND backupset_inner.backup_start_date <= @restore_to_datetime
                      AND backupset_inner.is_copy_only = 0 )
           AND backupset_outer.is_copy_only = 0;

        INSERT #backupset(
           backup_set_id
          ,is_in_restore_plan
          ,backup_start_date
          ,type
          ,database_name
          ,last_recovery_fork_guid
        )
        SELECT msdb.dbo.backupset.backup_set_id
              ,1                   --  The full database backup is always needed for the restore plan
              ,msdb.dbo.backupset.backup_start_date
              ,msdb.dbo.backupset.type
              ,msdb.dbo.backupset.database_name
              ,msdb.dbo.backupset.last_recovery_fork_guid
          FROM msdb.dbo.backupset
          JOIN #most_recent m
         on msdb.dbo.backupset.backup_set_id = m.backup_set_id
         AND msdb.dbo.backupset.server_name = @server_name;
        select @server_name as instance_name, btmp.database_name, bkmf.media_set_id, bkmf.family_sequence_number, bkmf.physical_device_name
            ,  bkps.backup_start_date, bkps.backup_finish_date, bkps.first_family_number, bkps.last_family_number, bkps.type as backup_type
        from
        #backupset AS btmp
INNER JOIN msdb.dbo.backupset AS bkps ON bkps.backup_set_id = btmp.backup_set_id
INNER JOIN msdb.dbo.backupmediafamily bkmf ON bkps.media_set_id = bkmf.media_set_id;

        drop table #backupset;
        drop table #most_recent;"

      $da = new-object System.Data.SqlClient.SqlDataAdapter ($query, $conn)
      $dt = new-object System.Data.DataTable
      $da.fill($dt) | out-null

      Write-DataTable -ServerInstance $ssi_instance -Database $ssi_database -TableName $ssi_table -Data $dt -Username $ssi_userid -Password $ssi_passwd

} #get-sqlinfo

$connection = new-object system.data.sqlclient.sqlconnection( `
    "Data Source=$ssi_instance;Initial Catalog=$ssi_database;User Id=$ssi_userid; Password=$ssi_passwd;");
$connection.Open()
$cmd = $connection.CreateCommand()

##### Clean out all tables pre-run. Move Jobs to Jobs_Compare, that way we can see what jobs were dropped #####

$query = "select instance_name_short into #cmm from Servers where Environment in ('$ssi_env')
      DELETE FROM [SQL].Login_Info where instance_name in (select instance_name_short from #cmm);
      DELETE FROM [SQL].Database_Users where instance_name in (select instance_name_short from #cmm);
      DELETE FROM [SQL].Database_Files where instance_name in (select instance_name_short from #cmm);
      DELETE FROM [SQL].Database_Roles where instance_name in (select instance_name_short from #cmm);
      DELETE FROM [SQL].Server_Roles where instance_name in (select instance_name_short from #cmm);
      DELETE FROM [SQL].Credentials where instance_name in (select instance_name_short from #cmm);
      DELETE FROM [SQL].Linked_Server_Logins where instance_name in (select instance_name_short from #cmm);
      DELETE FROM [SQL].Linked_Servers where instance_name in (select instance_name_short from #cmm);
      DELETE FROM [SQL].Job_Schedules where instance_name in (select instance_name_short from #cmm);
      DELETE FROM [SQL].Schedule_Info where instance_name in (select instance_name_short from #cmm);
      DELETE FROM [SQL].Job_Info_Compare where instance_name in (select instance_name_short from #cmm);
      INSERT INTO [SQL].Job_Info_Compare SELECT * FROM [SQL].Job_Info where instance_name in (select instance_name_short from #cmm);
      DELETE FROM [SQL].Job_Info where instance_name in (select instance_name_short from #cmm);
      DELETE FROM [SQL].Job_Proxies where instance_name in (select instance_name_short from #cmm);
      DELETE FROM [SQL].Proxies where instance_name in (select instance_name_short from #cmm);
      DELETE FROM [SQL].Backup_Info where instance_name in (select instance_name_short from #cmm);
      DELETE FROM [SQL].Database_Info where instance_name in (select instance_name_short from #cmm);
      DELETE FROM [SQL].Instance_Info where instance_name in (select instance_name_short from #cmm);
      "

$cmd.CommandText = $query
$null = $cmd.ExecuteNonQuery()

##### Get the list of servers to inventory #####

$query = "SELECT DISTINCT server_name as ServerName, server_name_short as ServerNameShort, instance_name as InstanceName, instance_name_short as InstanceNameShort FROM Servers WHERE Environment IN ('$ssi_env')"
$cmd.CommandText = $query
$reader = $cmd.ExecuteReader()

##### For every server gather data #####

while($reader.Read()) {

    ##### See if the server is alive #####

    $svr = $reader['ServerName']
    $svrshort = $reader['ServerNameShort']
    $inst = $reader['InstanceName']
    $instshort = $reader['InstanceNameShort']
    $result = new-object Microsoft.SqlServer.Management.Common.ServerConnection($inst, $ssi_userid, $ssi_passwd)
       $responds = $false
    if ($result.ProcessID -ne $null) {
	$responds = $true
    }	
 

    ##### If it is alive ... #####

    If ($responds) {
        #Write-Output "$inst is alive"
        get-sqlinfo $svr $svrshort $inst $instshort

    }
    else {

              # Let the user know we couldn't connect to the server
              Write-Output "$svr does not respond"
       }

}

##### Execute usp_StoreDBGrowth procedure #####

$connection = new-object system.data.sqlclient.sqlconnection( `
    "Data Source=$ssi_instance;Initial Catalog=$ssi_database;User Id=$ssi_userid; Password=$ssi_passwd;");
$connection.Open()
$cmd = $connection.CreateCommand()

$query = "exec [SQL].usp_StoreDBGrowth"
$cmd.CommandText = $query
$null = $cmd.ExecuteNonQuery()

Edit: Here’s the script to create the SQL tables.

 USE [MyInventory]
GO

CREATE TABLE [SQL].[Job_Schedules](
	[instance_name] [nvarchar](128) NULL,
	[job_id] [uniqueidentifier] NULL,
	[schedule_id] [int] NULL
) ON [PRIMARY]

GO

CREATE TABLE [SQL].[Schedule_Info](
	[instance_name] [nvarchar](128) NULL,
	[schedule_id] [int] NOT NULL,
	[enabled] [int] NOT NULL,
	[freq_type] [int] NOT NULL,
	[freq_interval] [int] NOT NULL,
	[freq_subday_type] [int] NOT NULL,
	[freq_subday_interval] [int] NOT NULL,
	[freq_relative_interval] [int] NOT NULL,
	[freq_recurrence_factor] [int] NOT NULL,
	[active_start_time] [int] NOT NULL,
	[active_end_time] [int] NOT NULL
) ON [PRIMARY]

GO

CREATE TABLE [SQL].[Backup_Info](
	[instance_name] [nvarchar](128) NOT NULL,
	[database_name] [nvarchar](128) NOT NULL,
	[media_set_id] [int] NOT NULL,
	[family_sequence_number] [tinyint] NOT NULL,
	[physical_device_name] [nvarchar](260) NULL,
	[backup_start_date] [datetime] NULL,
	[backup_finish_date] [datetime] NULL,
	[first_family_number] [tinyint] NULL,
	[last_family_number] [tinyint] NULL,
	[backup_type] [char](1) NULL
) ON [PRIMARY]

GO

CREATE TABLE [SQL].[Database_Info](
	[instance_name] [nvarchar](128) NOT NULL,
	[database_id] [smallint] NULL,
	[database_name] [nvarchar](128) NOT NULL,
	[status] [varchar](40) NULL,
	[RecoveryModel] [varchar](20) NULL,
	[CompatibilityLevel] [varchar](20) NULL,
	[Collation] [varchar](50) NULL,
	[LastBackupDate] [datetime] NULL,
	[LastDifferentialBackupDate] [datetime] NULL,
	[LastLogBackupDate] [datetime] NULL,
	[CreateDate] [datetime] NULL,
	[IsMirroringEnabled] [varchar](5) NULL,
	[Owner] [varchar](50) NULL,
 CONSTRAINT [PK_Instance_Database] PRIMARY KEY CLUSTERED
(
	[instance_name] ASC,
	[database_name] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

CREATE TABLE [SQL].[Instance_Info](
	[instance_name] [nvarchar](128) NOT NULL,
	[Version] [varchar](20) NULL,
	[SPLevel] [varchar](10) NULL,
	[Edition] [varchar](50) NULL,
	[Collation] [varchar](50) NULL,
	[IsClustered] [varchar](5) NULL,
	[MasterDBPath] [varchar](200) NULL,
	[MasterDBLogPath] [varchar](200) NULL,
	[RootDirectory] [varchar](200) NULL,
	[ServiceAccount] [varchar](50) NULL,
	[Parallel_Threshold] [int] NULL,
	[Max_DOP] [int] NULL,
	[Min_Memory] [int] NULL,
	[Max_Memory] [int] NULL,
	[XP_Cmdshell_Enabled] [tinyint] NULL,
 CONSTRAINT [PK_Instance_Name] PRIMARY KEY CLUSTERED
(
	[instance_name] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

CREATE TABLE [SQL].[Linked_Server_Logins](
	[instance_name] [nvarchar](128) NOT NULL,
	[linked_server] [sysname] NOT NULL,
	[local_login] [nvarchar](128) NOT NULL,
	[remote_login] [nvarchar](128) NULL
) ON [PRIMARY]

GO

CREATE TABLE [SQL].[Linked_Servers](
	[Instance_name] [nvarchar](128) NOT NULL,
	[Linked_Server] [sysname] NOT NULL,
	[Remote_Instance] [nvarchar](4000) NULL,
	[Provider] [sysname] NOT NULL,
	[Default_Database] [sysname] NULL,
 CONSTRAINT [PK_instance_linked_server] PRIMARY KEY CLUSTERED
(
	[Instance_name] ASC,
	[Linked_Server] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

CREATE TABLE [SQL].[Database_Growth](
	[instance_name] [nvarchar](128) NULL,
	[database_name] [nvarchar](128) NULL,
	[datafile_mb] [int] NULL,
	[logfile_mb] [int] NULL,
	[run_date] [date] NULL
) ON [PRIMARY]

GO

CREATE TABLE [SQL].[Job_Proxies](
	[instance_name] [nvarchar](128) NOT NULL,
	[job_id] [uniqueidentifier] NOT NULL,
	[job_name] [sysname] NOT NULL,
	[step_name] [sysname] NOT NULL,
	[proxy_id] [int] NOT NULL
) ON [PRIMARY]

GO

CREATE TABLE [SQL].[Proxies](
	[instance_name] [nvarchar](128) NOT NULL,
	[proxy_id] [int] NOT NULL,
	[name] [sysname] NOT NULL,
	[credential_id] [int] NOT NULL,
	[enabled] [tinyint] NOT NULL,
	[description] [nvarchar](512) NULL,
	[user_sid] [varbinary](85) NOT NULL,
	[credential_date_created] [datetime] NOT NULL,
 CONSTRAINT [PK_Instance_Proxy] PRIMARY KEY CLUSTERED
(
	[instance_name] ASC,
	[proxy_id] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

CREATE TABLE [SQL].[Credentials](
	[instance_name] [nvarchar](128) NOT NULL,
	[credential_id] [int] NOT NULL,
	[name] [sysname] NOT NULL,
	[credential_identity] [nvarchar](4000) NULL,
	[create_date] [datetime] NOT NULL,
	[modify_date] [datetime] NOT NULL
) ON [PRIMARY]

GO

CREATE TABLE [SQL].[Database_Roles](
	[instance_name] [nvarchar](128) NOT NULL,
	[database_name] [nvarchar](128) NOT NULL,
	[role_name] [varchar](100) NULL,
	[database_user] [varchar](200) NULL,
	[dbuser_sid] [varbinary](85) NULL
) ON [PRIMARY]

GO

CREATE TABLE [SQL].[Job_Info_Compare](
	[instance_name] [nvarchar](128) NULL,
	[job_name] [sysname] NOT NULL,
	[enabled] [tinyint] NOT NULL,
	[description] [nvarchar](512) NULL,
	[date_created] [datetime] NOT NULL,
	[date_modified] [datetime] NOT NULL,
	[last_run_date] [datetime] NULL,
	[last_run_status] [varchar](20) NULL,
	[message] [nvarchar](4000) NULL,
	[job_id] [uniqueidentifier] NULL
) ON [PRIMARY]

GO

CREATE TABLE [SQL].[Database_Users](
	[instance_name] [nvarchar](128) NOT NULL,
	[database_name] [nvarchar](128) NOT NULL,
	[database_user] [varchar](100) NULL,
	[create_date] [datetime] NULL,
	[dbuser_sid] [varbinary](max) NULL,
	[dbuser_type] [char](1) NULL
) ON [PRIMARY]

GO

CREATE TABLE [SQL].[Login_Info](
	[instance_name] [nvarchar](128) NOT NULL,
	[LoginName] [sysname] NOT NULL,
	[AccountCreateDate] [datetime] NOT NULL,
	[LastTimeAccountModified] [datetime] NOT NULL,
	[PasswordLastSetTime] [sql_variant] NULL,
	[DefaultDatabase] [sysname] NULL,
	[DefaultLanguage] [sysname] NULL,
	[IsPolicyChecked] [bit] NULL,
	[IsExpirationChecked] [bit] NULL,
	[IsExpired] [sql_variant] NULL,
	[DaysUntilExpiration] [sql_variant] NULL,
	[IsLocked] [sql_variant] NULL,
	[IsMustChange] [sql_variant] NULL,
	[LockoutTime] [sql_variant] NULL,
	[BadPasswordCount] [sql_variant] NULL,
	[BadPasswordTime] [sql_variant] NULL,
	[HistoryLength] [sql_variant] NULL,
	[LoginSid] [varbinary](85) NULL,
	[LoginType] [char](1) NULL
) ON [PRIMARY]

GO

CREATE TABLE [SQL].[Server_Roles](
	[instance_name] [nvarchar](128) NOT NULL,
	[RoleName] [nvarchar](128) NULL,
	[LoginName] [nvarchar](128) NULL,
	[LoginSid] [varbinary](85) NULL
) ON [PRIMARY]

GO

CREATE TABLE [SQL].[Database_Files](
	[instance_name] [nvarchar](128) NOT NULL,
	[database_name] [nvarchar](128) NOT NULL,
	[file_id] [int] NOT NULL,
	[type_desc] [nvarchar](60) NULL,
	[name] [sysname] NOT NULL,
	[physical_name] [nvarchar](260) NOT NULL,
	[size_mb] [int] NULL,
	[growth_percent] [int] NOT NULL,
	[growth_mb] [int] NULL
) ON [PRIMARY]

GO

CREATE TABLE [SQL].[Job_Info](
	[instance_name] [nvarchar](128) NOT NULL,
	[job_name] [sysname] NOT NULL,
	[enabled] [tinyint] NOT NULL,
	[description] [nvarchar](512) NULL,
	[date_created] [datetime] NOT NULL,
	[date_modified] [datetime] NOT NULL,
	[last_run_date] [datetime] NULL,
	[last_run_status] [varchar](20) NULL,
	[message] [nvarchar](4000) NULL,
	[job_id] [uniqueidentifier] NULL
) ON [PRIMARY]

GO

Also recommended:

Using Powershell to Deploy Performance Monitor

SQL Audit 302 - Deploying the Audit Solution with Powershell

I've got mad SQLSkills

May 31, 2012 Tags: inventory, powershell, SQL Server Posted in: Powershell

18 Responses

Jason - June 1, 2012

Another amazing post! Will you be sharing your Scheduled and On-demand reports that you run against your inventory database in another post (say yes…say yes!)?
Colleen M. Morrow - June 4, 2012

Thanks Jason! I’ll think about how to turn the reports (or at least the queries) into a post.
Darren Stanger - June 6, 2012

Colleen, this is exciting stuff. I’ve been building up my own solutions but nothing like this.

Would you please post the SQL to create the tables to store the SQL Server instance data?

Also, ditto on the request for the SSRS reports.

Thank you so much for sharing.
Mark - June 8, 2012

Yes, this has been very helpful!
Please post the script to creat the database tables.
Thanks!
Colleen M. Morrow - June 8, 2012

Ok folks. The code to create the tables has been added to the end of the post. Happy Inventorying!
Mark - June 11, 2012

Hate to ask for more when you’ve already given us so much, but would you also post the stored procedures: usp_StoreDBGrowth, sp_get_database_users, and sp_get_database_roles?

Thanks!
Mark
J - August 3, 2012

Colleen,
Could you give me an idea of how to set this up and configure it? I have all the code, the db and tables created…just trying to get the powershell script to work. I would love to play around with this idea and see if it would be useful for me. I tried running the “complete script” passing in my own parameters but failed. Any help would be nice.

Thanks,

J
Colleen M. Morrow - August 3, 2012

Jeremy – If you email me the details of the error you’re getting, I’ll be happy to help. colleen.morrow@gmail.com
Ian - November 27, 2012

Colleen, this script is great thanks..
i am trying to use this to collect some information from instances across our environment but want to use windows authentication as opposed to sql logins.

i am struggling with the validation of the instances (for each one that is online run the process) as thinks they are all online (but 1 is valid), any idea how i can test \ resolve this?
Colleen M. Morrow - November 28, 2012

Hi Ian – So you’re using windows authentication and all of them are coming back as being online but only one is actually running? Can you post the validation portion of the code here? I’m assuming you altered it for the windows auth.
Ian - November 29, 2012

Hi Colleen,

Many thanks for your quick response, my script is an extermely cut down type compared to your own, but as i am learning powershell on the go think may pf made a silly mistake. All my script wants to do is use an instance list, i want to attempt to connect and if online carry on, if not online flag up (we have 100+ instances) – my problem is everything is coming up as being online (even a fake entry in my instance list)so data loads in from valid instances but attempts the fake entry also..

Script below, thanks in advance for your time )

. .\write-datatable.ps1
. .\out-datatable.ps1

$ssi_instance=”testinstance\testinstance”
$ssi_database = “DailyChecks”

[System.Reflection.Assembly]::LoadWithPartialName(‘Microsoft.SqlServer.SMO’) | out-null
[System.Reflection.Assembly]::LoadWithPartialName(‘Microsoft.SqlServer.ConnectionInfo’) | out-null

function get-DailyChecks ($instance) {

# Create an ADO.Net connection to the instance
$ServerConn = new-object Microsoft.SqlServer.Management.Common.ServerConnection(“server=$instance;Integrated Security=true”);
$s = new-object (‘Microsoft.SqlServer.Management.Smo.Server’) $ServerConn

##### Get Server Roles #####

$conn = “server=$instance;Integrated Security=true”

$ssi_table = “dbo.Server_Roles”

$query = “select @@Servername as instance_name, r.name as role_name, m.name as member_name, m.sid
from sys.server_principals r
join sys.server_role_members rm on r.principal_id = rm.role_principal_id
join sys.server_principals m on m.principal_id = rm.member_principal_id”
$da = new-object System.Data.SqlClient.SqlDataAdapter ($query, $conn)
$dt = new-object System.Data.DataTable
$da.fill($dt) | out-null

Write-DataTable -ServerInstance $ssi_instance -Database $ssi_database -TableName $ssi_table -Data $dt

} #get-DailyChecks

$connection = new-object system.data.sqlclient.sqlconnection(“server=$ssi_instance;database=$ssi_database;Integrated Security=true;”);
$connection.Open()
$cmd = $connection.CreateCommand()

##### Get the list of servers to inventory #####

$query = “SELECT DISTINCT Instance_Name AS InstanceName FROM Servers”
$cmd.CommandText = $query
$reader = $cmd.ExecuteReader()

##### For every server gather data #####

while($reader.Read()) {

##### See if the server is alive #####

$inst = $reader['InstanceName']
$result = new-object Microsoft.SqlServer.Management.Common.ServerConnection($inst)

Write-Output “$responds”
$responds = $false
if ($result.isopen -eq 0) {
$responds = $true
}

##### If it is alive … #####

If ($responds) {

#run the procedure against the instances online
get-DailyChecks $inst
}
else {

# Let the user know we couldn’t connect to the instance
Write-Output “$inst not online”
}

}
Colleen M. Morrow - November 29, 2012

Hi Ian –

Good news for you: you weren’t doing anything wrong. Bad news for me: I was. My use of the isOpen function to test for the existence of the instance wasn’t working as I expected. I don’t know why this never came up before, I know I’ve run this against servers that were offline. Interesting. Anyway, I’ve come up with a fix, which is basically checking for a process id, rather than using isopen. Give this a whirl and let me know if it works for you. If everything looks good I’ll be sure to update the original post and the version in my toolbox. Thanks for the heads up!

while($reader.Read()) {

##### See if the server is alive #####

$inst = $reader['InstanceName']
$result = new-object Microsoft.SqlServer.Management.Common.ServerConnection($inst)

$responds = $false

if ($result.ProcessID -ne $null) {
$responds = $true
}

##### If it is alive … #####

If ($responds) {

#run the procedure against the instances online
Write-Output “$inst is alive!!”
#get-DailyChecks $inst
}
else {

# Let the user know we couldn’t connect to the instance
Write-Output “$inst not online”
}

}
Ian - November 30, 2012

Colleen, you are a star, thanks it works now as you expected! This is a real milestone for what i want to use this process for and really appreciate your help..

for my own info (and what it looks like when i have ran it) when the script finishes its run it closes the connection to each sql instance itself? – is there a need for any $connection.close anywhere or that is taken care of at the end of run of the function to the online instance?

Last but not least! for instances online i am running the function to obtain the info from the instances, as i aim to run this powershell script from one central server via an agent job would it be easy to write the information for those not online back into my $ssi_instance db?
JD - January 18, 2013

Hello Colleen, having trouble locating the stored procedures required for the SQL inventory scripts.
sp_get_database_roles, usp_StoreDBGrowth and sp_get_database_users. would you happen to have those handy?
Colleen M. Morrow - January 24, 2013

JD – If you go to my Toolbox page you can download all the scripts in one zip file.
Chandhra - February 13, 2013

Help required,

How to gather / list a report of all the sql servers and versions in my environment Prod / Dev / Test / UAT / SYST using a PS script or SQL Scripts.

Provide a version report for the SQL Servers in each environment. We just want to know which ones have SQL Server need, SP1 and which ones need it.
Shannon - April 17, 2013

Is there something missing within the inner query around line 405? The where statement reads “AND backupset_inner.backup_start_date” then a bunch of white space before picking backup up again with “AND backupset_inner.is_copy_only = 0 )”.
Colleen M. Morrow - April 17, 2013

Shannon – Yep, the end of that line went AWOL. It’s been updated, thanks for pointing it out!

Cleveland DBA

SQL Server and Oracle database administration in sunny Cleveland, OH

Building a SQL Server Inventory – Part 2

Credits

Tools you’ll need

Permissions required

The moment you’ve all been waiting for – The script

Parameters

Clean out tables pre-run

Get list of servers to inventory

Get-sqlinfo Function

One last thing

The complete script

Also recommended:

18 Responses

Leave a Reply

Search

Archives

Blogroll

Meta

Follow Me

Categories

Recent Posts

Cleveland DBA

SQL Server and Oracle database administration in sunny Cleveland, OH

Building a SQL Server Inventory – Part 2

Credits

Tools you’ll need

Permissions required

The moment you’ve all been waiting for – The script

Parameters

Clean out tables pre-run

Get list of servers to inventory

Get-sqlinfo Function

One last thing

The complete script

Also recommended:

Share this:

18 Responses

Leave a Reply

Search

Archives

Blogroll

Meta

Follow Me

Categories

Recent Posts

Tags