Building a SQL Server Inventory – Part 1


Ok folks, you asked for it. Well, a few of you did, anyway. So today I’m going to share with you my script for gathering server-level information as part of my SQL Server inventory. Now, I want to make one thing perfectly clear before we begin, and I can’t emphasize this enough: I am not a PowerShell guru. I know one of my resolutions this year was to learn PowerShell in a month of lunches, but I haven’t actually done that yet. So please, as you go through this, try to temper your disappointment, m’kay?

Credits

You know who is a PowerShell guru? Allen White. In fact, my script is based heavily on Allen’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

I use a domain account with admin rights to the target servers I’m inventorying. If you don’t have access to such an account, for example if you have a separate domain account as admin to each box, or if you have access to local admin accounts only, you can specify credentials in your WMI connection.

You’ll also need SQL Server login with VIEW SERVER STATE on your target servers to query sys.dm_os_cluster_nodes. I’ll explain more about why this is necessary shortly.

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. #####

$query = "select server_name_short into #cmm from Servers where Environment in ('$ssi_env') ;
      DELETE FROM Server.OS_Info WHERE server_name in (Select server_name_short from #cmm);
      DELETE FROM Server.Memory_Info WHERE server_name in (Select server_name_short from #cmm);
      DELETE FROM Server.Disk_Info WHERE server_name in (Select server_name_short from #cmm);
      DELETE FROM Server.PhysicalNodes WHERE server_name in (Select server_name_short from #cmm);
      DELETE FROM Server.System_Info WHERE server_name in (Select server_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-wmiinfo 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 = Get-WMIObject -query "select StatusCode from Win32_PingStatus where Address = '$svr'"
       $responds = $false
    if ($result.statuscode -eq 0) {
        $responds = $true
    }

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

    If ($responds) {
        get-wmiinfo $svr $svrshort $inst $instshort

    }
    else {

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

}

Get-wmiinfo Function

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

First we’ll go after the ComputerSystem info.  I originally used NumberOfProcessors, NumberOfLogicalProcessors from Win32_ComputerSystem to get the processors and cores, but this wasn’t always accurate in all systems.  I’ve found a better option was to query win32_processor as described here by Richard Fryar, and from whom I borrowed this section of code from.  Although he mentions having problems with this on Windows 2008 servers, I’ve had good results.  YMMV.


    ##### Get the ComputerSystem Info #####

    $ssi_table="Server.System_Info"

    $processors = get-wmiobject -computername $server win32_processor

    if (@($processors)[0].NumberOfCores)
        {
            $cores = @($processors).count * @($processors)[0].NumberOfCores
        }
        else
        {
            $cores = @($processors).count
        }
        $sockets = @(@($processors) |
        % {$_.SocketDesignation} |
        select-object -unique).count;

    $dt=Get-WMIObject -query "select * from Win32_ComputerSystem" -computername $server | select @{n="ServerName";e={$servershort}}, Model, Manufacturer, Description, DNSHostName, Domain, DomainRole, PartOfDomain, @{n="NumberofProcessors";e={$sockets}}, @{n="NumberofCores";e={$cores}}, SystemType, TotalPhysicalMemory, UserName, Workgroup | out-datatable

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

I wanted to list all of the nodes that make up a cluster.  So far, I haven’t found a way to do this via WMI.  (If anyone knows of one, I’d love to hear it.)  My workaround was to first see if the server name I was connecting to (the cluster name) was the same as the physical name according to Win32_ComputerSystem.  If they were different, I knew it was a cluster and I had to query sys.dm_os_cluster_nodes in SQL.


##### ... and the Physical Node Info #####

    $ssi_table = "Server.PhysicalNodes"
    $conn = "server=$instance;database=master;User ID=$ssi_userid;password=$ssi_passwd"
    $s=Get-WMIObject -query "select * from Win32_ComputerSystem" -ComputerName $server | select name
    if ($s.name -ne $servershort) {

        $query = "select `'$servershort`' as server_name, NodeName from sys.dm_os_cluster_nodes"

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

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

    } else {

        $query = "select `'$servershort`' as server_name, `'$servershort`' as NodeName"

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

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

    }

The remainder of the function gathers the OS, Memory, and Logical Disk info.  These are all straightforward WMI queries.


##### ... and the OperatingSystem Info #####

$ssi_table="Server.OS_Info"

$dt=Get-WMIObject Win32_OperatingSystem -computername $server | select @{n="ServerName";e={$servershort}}, Name, Version, OSLanguage, OSProductSuite, OSType, ServicePackMajorVersion, ServicePackMinorVersion |out-datatable

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

##### ... and the PhysicalMemory Info #####

$ssi_table="Server.Memory_Info"

$dt=Get-WMIObject -query "select * from Win32_PhysicalMemory" -computername $server | select @{n="ServerName";e={$servershort}},Name, Capacity, DeviceLocator, Tag | out-datatable

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

##### ... and the LogicalDisk Info #####

$ssi_table="Server.Disk_Info"

$dt=Get-WMIObject -query "select * from Win32_Volume where DriveType=3 and not name like '%?%'" -computername $server |select @{n="ServerName";e={$servershort}},Name, Label, DriveLetter, Capacity, FreeSpace | out-datatable

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

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-ServerInfo.ps1
# usage: ./get-ServerInfo.ps1 <Inventory Instance name> <Inventory Login> <Inventory Password> <Environment>
# Collects all Server information into MyInventory database
# Including:
#     Server.System_Info;
#     Server.OS_Info;
#     Server.Memory_Info;
#     Server.Disk_Info;

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

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

$ssi_database = "MyInventory"

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

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

    ##### Get the ComputerSystem Info #####

    $ssi_table="Server.System_Info"

    $processors = get-wmiobject -computername $server win32_processor

    if (@($processors)[0].NumberOfCores)
        {
            $cores = @($processors).count * @($processors)[0].NumberOfCores
        }
        else
        {
            $cores = @($processors).count
        }
        $sockets = @(@($processors) |
        % {$_.SocketDesignation} |
        select-object -unique).count;

    $dt=Get-WMIObject -query "select * from Win32_ComputerSystem" -computername $server | select @{n="ServerName";e={$servershort}}, Model, Manufacturer, Description, DNSHostName, Domain, DomainRole, PartOfDomain, @{n="NumberofProcessors";e={$sockets}}, @{n="NumberofCores";e={$cores}}, SystemType, TotalPhysicalMemory, UserName, Workgroup | out-datatable

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

    ##### ... and the Physical Node Info #####

    $ssi_table = "Server.PhysicalNodes"
    $conn = "server=$instance;database=master;User ID=$ssi_userid;password=$ssi_passwd"
    $s=Get-WMIObject -query "select * from Win32_ComputerSystem" -ComputerName $server | select name
    if ($s.name -ne $servershort) {

        $query = "select `'$servershort`' as server_name, NodeName from sys.dm_os_cluster_nodes"

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

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

    } else {

        $query = "select `'$servershort`' as server_name, `'$servershort`' as NodeName"

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

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

    }

    ##### ... and the OperatingSystem Info #####

    $ssi_table="Server.OS_Info"

    $dt=Get-WMIObject Win32_OperatingSystem -computername $server | select @{n="ServerName";e={$servershort}}, Name, Version, OSLanguage, OSProductSuite, OSType, ServicePackMajorVersion, ServicePackMinorVersion |out-datatable

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

    ##### ... and the PhysicalMemory Info #####

    $ssi_table="Server.Memory_Info"

    $dt=Get-WMIObject -query "select * from Win32_PhysicalMemory" -computername $server | select @{n="ServerName";e={$servershort}},Name, Capacity, DeviceLocator, Tag | out-datatable

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

    ##### ... and the LogicalDisk Info #####

    $ssi_table="Server.Disk_Info"

    $dt=Get-WMIObject -query "select * from Win32_Volume where DriveType=3 and not name like '%?%'" -computername $server |select @{n="ServerName";e={$servershort}},Name, Label, DriveLetter, Capacity, FreeSpace | out-datatable

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

} # get-wmiinfo

$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. #####

$query = "select server_name_short into #cmm from Servers where Environment in ('$ssi_env') ;
      DELETE FROM Server.OS_Info WHERE server_name in (Select server_name_short from #cmm);
      DELETE FROM Server.Memory_Info WHERE server_name in (Select server_name_short from #cmm);
      DELETE FROM Server.Disk_Info WHERE server_name in (Select server_name_short from #cmm);
      DELETE FROM Server.PhysicalNodes WHERE server_name in (Select server_name_short from #cmm);
      DELETE FROM Server.System_Info WHERE server_name in (Select server_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 = Get-WMIObject -query "select StatusCode from Win32_PingStatus where Address = '$svr'"
       $responds = $false
    if ($result.statuscode -eq 0) {
        $responds = $true
    }

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

    If ($responds) {
        get-wmiinfo $svr $svrshort $inst $instshort

    }
    else {

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

}

Edit: Here’s the code to create the Server tables.

 USE [MyInventory]
GO

CREATE TABLE [dbo].[Servers](
[instance_name] [nvarchar](128) NOT NULL,
[instance_name_short] [nvarchar](128) NOT NULL,
[server_name] [nvarchar](128) NOT NULL,
[server_name_short] [nvarchar](128) NOT NULL,
[Environment] [varchar](7) NOT NULL,
CONSTRAINT [PK_Server_Instance] PRIMARY KEY CLUSTERED
(
[instance_name_short] 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 SCHEMA [Server] AUTHORIZATION [MyInventoryLogin]
GO

CREATE TABLE [Server].[PhysicalNodes](
[Server_Name] [nvarchar](128) NOT NULL,
[Node_Name] [nvarchar](128) NOT NULL
) ON [PRIMARY]

GO

CREATE TABLE [Server].[System_Info](
[Server_Name] [nvarchar](128) NOT NULL,
[Model] [varchar](200) NULL,
[Manufacturer] [varchar](50) NULL,
[Description] [varchar](100) NULL,
[DNSHostName] [varchar](30) NULL,
[Domain] [varchar](30) NULL,
[DomainRole] [smallint] NULL,
[PartOfDomain] [varchar](5) NULL,
[NumberOfProcessors] [smallint] NULL,
[NumberOfCores] [smallint] NULL,
[SystemType] [varchar](50) NULL,
[TotalPhysicalMemory] [bigint] NULL,
[UserName] [varchar](50) NULL,
[Workgroup] [varchar](50) NULL,
CONSTRAINT [PK_Server_Name] PRIMARY KEY CLUSTERED
(
[Server_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 [Server].[Memory_Info](
[Server_Name] [nvarchar](128) NOT NULL,
[Name] [varchar](50) NULL,
[Capacity] [bigint] NULL,
[DeviceLocator] [varchar](20) NULL,
[Tag] [varchar](50) NULL
) ON [PRIMARY]

GO

CREATE TABLE [Server].[OS_Info](
[Server_Name] [nvarchar](128) NOT NULL,
[OSName] [varchar](200) NULL,
[OSVersion] [varchar](20) NULL,
[OSLanguage] [varchar](5) NULL,
[OSProductSuite] [varchar](5) NULL,
[OSType] [varchar](5) NULL,
[ServicePackMajorVersion] [smallint] NULL,
[ServicePackMinorVersion] [smallint] NULL
) ON [PRIMARY]

GO

CREATE TABLE [Server].[Disk_Info](
[Server_Name] [nvarchar](128) NOT NULL,
[Disk_Name] [varchar](50) NULL,
[Label] [varchar](50) NULL,
[DriveLetter] [varchar](5) NULL,
[Capacity] [bigint] NULL,
[FreeSpace] [bigint] NULL,
[Run_Date] [date] NULL
) ON [PRIMARY]

GO 

Also recommended:


Leave a comment

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>

27 thoughts on “Building a SQL Server Inventory – Part 1

    • Colleen M. Morrow Post author

      Good question, Bill. When I originally started building the inventory, I was running it all from a dev server. So I needed to store both the servername and the fully qualified server name, so I could cross domains. The server_name and instance_name columns are the fully qualified names, i.e. MyServer.Domain.com and MyServer.Domain.Com\MyInstance. The server_name_short and instance_name_short are the server and instance names without the full qualification, Myserver and MyServer\MyInstance. These days I keep a separate inventory for each environment, so technically I don’t need all 4 variations, but I keep them anyway, just in case I change it yet again.

  • Bill Soranno

    Colleen,
    Thank you for the create table script.
    Could you provide the create table script for the SQL tables?

    I have been trying to use the Add-SqlTable.ps1 from Chad Miller, but I am running into column size issues.

    Thanks Bill

  • Chris Yates

    Colleen, this is great. Appreciate you taking the time to post about this. We went the same root on inventory, but used linked servers and some scripts to dump all our data back to a CMS system. All in all it’s worked out pretty well. I’m looking forward to diving further into your post.

    Thanks again! Kind Regards

  • Ned Otter

    Hi Colleen,

    Very nice set of inventory scripts, thanks for your great effort. I ran your inventory in my lab on a named instance of SQL 2008 R2, running on Windows Server 2008 R2. I encountered a couple of issues that you might want to be aware of.

    1. select * from Win32_PhysicalMemory returns nothing on my system. I had to change it to: select * from Win32_ComputerSystem and reference TotalPhysicalMemory from that query.

    2. Connectivity was a problem using a named instance. I didn’t investigate it too much at this point.

    Again, thanks for your effort.

    Best wishes,

    Ned

    • Colleen M. Morrow Post author

      Hi Ned – Thanks for the feedback. Hmm, I’m also running SQL2008R2 named instances on Server 2008 R2, and I’m not experiencing the same issues. For the connectivity, can you verify that you can connect to the named instance from your inventory server with another tool, like SSMS? As far as the Win32_PhysicalMemory I’ll have to keep looking into that. Do you think it’s possibly a permissions issue?

    • Colleen M. Morrow Post author

      Hi Bin – I apologize for the delayed response. To execute the script, at a PowerShell prompt, navigate to the directory where the script is located and run the following command:
      ./get-ServerInfo.ps1

      Where is the instance where your Inventory database is located, and are the login and password with access to the Inventory tables, and is the environment you’re loading (i.e. Dev, QA, Prod, whatever you used in the Environment column of your Servers table). Hope this helps!

  • john

    Hi Colleen,
    just came across your site and this all encompassing article. Trying to get this running and having an issue with Import-Module FailoverClusters failing import because it cannot find it – which I suspect is because I am running this set of scripts on a Windows7 machine.
    Can you confirm this please – do these have to be run on a windows Server machine?

    I have been looking for a long time for an encompassing inventory system to go along with the SQL monitoring system I have cobbled together – like you also not a powershell programmer

    thanks
    john

    • Colleen M. Morrow Post author

      Hi John – Yes, the failover clustering feature needs to be installed in order to get this PowerShell module, so you’ll need to be on a server machine to use it. HOWEVER, after reviewing the rest of the script, I believe that’s left over from attempts to use cmdlets to get the physical nodes of the cluster. It doesn’t look like I’m actually using that module. Try commenting it out and see what happens.

  • Abs

    First of all, many thanks for sharing this. It does work for some of my servers but not all. It seems to work on SQL servers installed on Windows 2003 but not completely for Windows 2008 R2 servers.

    I am getting 2 errors:

    error 1 for all Windows 2008 R2 servers:

    Exception calling “Fill” with “1” argument(s): “Login failed for user ‘xxxxxxx’.”
    At D:\Scripts\get-ServerInfo.ps1:66 char:19
    + $da.fill <<<< ($dat) | out-null
    + CategoryInfo : NotSpecified: (:) [], MethodInvocationException
    + FullyQualifiedErrorId : DotNetMethodException

    Error 2 just once but not sure which server:

    Exception calling "Fill" with "1" argument(s): "A network-related or instance-specific error occurred while establishin
    g a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct
    and that SQL Server is configured to allow remote connections. (provider: Named Pipes Provider, error: 40 – Could not o
    pen a connection to SQL Server)"
    At D:\Scripts\get-ServerInfo.ps1:66 char:19
    + $da.fill <<<< ($dat) | out-null
    + CategoryInfo : NotSpecified: (:) [], MethodInvocationException
    + FullyQualifiedErrorId : DotNetMethodException

    Any ideas?

    Many Thanks,

    Abs

    • Colleen M. Morrow Post author

      Hi Abs – Well, I’m not sure why it would only happen on your 2008 servers, but have you confirmed that for all of those servers you can connect to the instance a) as the inventory login/password you’re using in your script and b) that you can make that connection from the server you’re running the script from?

  • JD

    Colleen – great post. I, like youself am not a Powershell programmer. I just have a gnack for picking up on things. I have a silly question. how are you passing the parameters to the get-serverinfo.ps1? I am assuming it’s just looking for the get-serverinfo.ps1 instancename login password Env. but I think I may be using the wrong format. should I be first referencing and argument (ie. -instancename %actual_instanacename%…etc.). I know this sounds a little silly but I am almost there and just need to know “where to aim”. thanks for all your help.

    JD

  • JD

    Hey Colleen, thanks for your reply, I was able to work through my previous issue. one other issue I am dealing with. I am to getting any data to populate in the Backup_info table. I verified that the account I’m using has “sa” on all servers, including the “My_Inventory” database server. after running the get-sql-info.ps1 I have to data in the backup_info table. can you suggest anything else I might want to check?

  • Jason D

    Hello Colleen, First of all, excellent stuff. I have this script inventorying our SQL environment. it works great for most information, but I am unable to retrieve data regarding Database_Role, Database_users, even though I do have all the necessary Stored Procs in place under MyInventory database.
    Also, I have tried to alter this script to use “Windows Authentication” to no avail. Are you planning to present a copy of this script, using Windows Authentication? maybe you can assist. I get a host of errors similar to what Abs reported back in November

    Exception calling “Fill” with “1″ argument(s): “Login failed for user ‘xxxxxxx’.”
    At D:\Scripts\get-ServerInfo.ps1:66 char:19
    + $da.fill <<<< ($dat) | out-null
    + CategoryInfo : NotSpecified: (:) [], MethodInvocationException
    + FullyQualifiedErrorId : DotNetMethodException

    once I changed the authentication, all my Instance_Info comes back with NULL data except for the instance name, itself. here is a snippet from my script… any thoughts?

    param(
    [string]$ssi_instance="XXXXXXXX",
    # [string]$ssi_userid="XXXXXXXX",
    # [string]$ssi_passwd="XXXXXXX",
    [string]$ssi_env="Prod"
    )
    . .\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(“server=$instance;Integrated Security=true”);
    $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;Integrated Security=true;");
    $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'

    Thank You for this help

    Jason D

  • Colleen M. Morrow Post author

    Jason D – When you created the procedures for gathering the database users and roles, you created that on the target instance, not in the inventory database, right? And you signed them with a certificate so they execute with sysadmin privs? If not, are you using a login with sysadmin privs?

    Regarding the error you posted, was that the actual error from your environment? I ask because the error references the ServerInfo script, but the code you posted is from the SQL inventory script. If you want, create a test version of the script with just the SQL Instance Info collection, see if you get the error(s). If you do, send me the script and errors and I’ll to try to help.

  • Ole

    Hi Colleen,

    Thank you for your post about server inventory. I’m new to PowerShell and I have learned quite a few things from your scipts.
    I have a question regarding population of the [dbo].[Servers] table. This is the table as you use to control which servers are being inventoried. I have a situation, where I have multiple SQL Server Instances on a server. Every time I execute the get-ServerInfo.ps1 script, I get primary key violation in the Server.System_Info table. Can you please explain how I polulate the [dbo].[Servers] in my situation.

    Instance 1:
    server_name MyServer.Domain.com
    instance_name MyServer.Domain.Com\MyInstance1
    server_name_short Myserver
    instance_name_short MyServer\MyInstance1

    Instance 2:
    server_name MyServer.Domain.com
    instance_name MyServer.Domain.Com\MyInstance2
    server_name_short Myserver
    instance_name_short MyServer\MyInstance2

    Thanks,
    Ole