Putting together Powershell script to document SQL Server with Instances

Question

Ok, this is a messy one, so bear with me.

We have a PowerShell script that runs from our CMS/Master SQL Managment Server that fetches information from all servers we have, specified in a one column table ([SQLServerInformation].[dbo].[server_list]). The script will then go through the list with a foreach loop and query the servers and write whatever information it receives back into another table in the same database ([SQLServerInformaton].[dbo].[$ServerName_server_trunc], so the table name will be whatever server was just queried in the loop. E.g. [sqlserver01_server_trunc] or [managementsql11_server_trunc]).

The issue I'm having is that I'm also trying to extract what instances are running on the server. The instances will always run with the same SQL Server version, so I don't need any advanced script that will go through the registry (which I've read is not a good idea). The script (which works completely fine without the instance related bits) I've created so far is this:

# We store all SQL Servers in this table. Here we fetch them into a variable.
$QueryFetchServerNames = "SELECT ServerName AS Name FROM [SQLServerInformation].[dbo].[server_list]"

# Make sure we don't run into the "System.Data.DataRow" problem.
$ServerName = (Invoke-Sqlcmd -ServerInstance CBSWSQLMGT01 -Query $QueryFetchServerNames).Name

foreach ($Server in $ServerName) {
    # Fetch the instances under the server.
    $Instances = Get-ChildItem -Path "SQLSERVER:\SQL\$Server"

    Invoke-Sqlcmd -ServerInstance $Server -Query "
        SELECT SERVERPROPERTY('MachineName') AS [Server], 
        "$Instances | foreach-object { $_ }" AS [Instance(s)],
           (SELECT Server_type = CASE 
                                   WHEN virtual_machine_type = 1 THEN 'Virtual' 
                                   ELSE 'Physical' 
                                 END 
            FROM   sys.dm_os_sys_info) AS [Virtual/Physical], 
           (SELECT ( cpu_count / hyperthread_ratio ) 
            FROM   sys.dm_os_sys_info) AS [CPU(s)], 
           (SELECT CASE 
                     WHEN hyperthread_ratio = cpu_count THEN cpu_count 
                     WHEN ( ( cpu_count - hyperthread_ratio ) / ( 
                            cpu_count / hyperthread_ratio ) ) 
                          = 0 THEN 1 
                   END 
            FROM   sys.dm_os_sys_info) AS [Core(s)], 
           (SELECT ( [total_physical_memory_kb] / 1024 ) AS [RAM (MB)] 
            FROM   [master].[sys].[dm_os_sys_memory]) AS [RAM (MB)], 
           LEFT(@@VERSION, Charindex(')', @@VERSION)) AS [Version], 
           SERVERPROPERTY('ProductVersion') AS [Build], 
           SERVERPROPERTY('Edition') AS [Edition];"| 

           # Write it all into a table.
           Write-SqlTableData -ServerInstance CBSWSQLMGT01 -DatabaseName SQLServerInformation -SchemaName dbo -TableName $ServerName_server_trunc -Force
}

I get the output:

PS D:\PowerShell\ServerInformation\cbswsqlmgt01> D:\PowerShell\ServerInformation\cbswsqlmgt01\test.ps1
Invoke-Sqlcmd : A positional parameter cannot be found that accepts argument 'System.Object[]'.
At D:\PowerShell\ServerInformation\cbswsqlmgt01\test.ps1:30 char:5
+     Invoke-Sqlcmd -ServerInstance $Server -Query "
+     ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo          : InvalidArgument: (:) [Invoke-Sqlcmd], ParameterBindingException
    + FullyQualifiedErrorId : PositionalParameterNotFound,Microsoft.SqlServer.Management.PowerShell.GetScriptCommand
 
Invoke-Sqlcmd : A positional parameter cannot be found that accepts argument '[CBSWSQLMGT01]'.
At D:\PowerShell\ServerInformation\cbswsqlmgt01\test.ps1:30 char:5
+     Invoke-Sqlcmd -ServerInstance $Server -Query "
+     ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo          : InvalidArgument: (:) [Invoke-Sqlcmd], ParameterBindingException
    + FullyQualifiedErrorId : PositionalParameterNotFound,Microsoft.SqlServer.Management.PowerShell.GetScriptCommand

If I run just this, the instances will display fine:

# We store all SQL Servers in this table. Here we fetch them into a variable.
$QueryFetchServerNames = "SELECT ServerName AS Name FROM [SQLServerInformation].[dbo].[server_list]"

# Make sure we don't run into the "System.Data.DataRow" problem.
$ServerName = (Invoke-Sqlcmd -ServerInstance CBSWSQLMGT01 -Query $QueryFetchServerNames).Name

foreach ($Server in $ServerName) {
    # Fetch the instances under the server.
    $Instances = Get-ChildItem -Path "SQLSERVER:\SQL\$Server"
    $Instances | foreach-object { $_ }
    Write-Host "--------"
}

PS D:\PowerShell\ServerInformation\cbswsqlmgt01> D:\PowerShell\ServerInformation\cbswsqlmgt01\test.ps1

Instance Name                                                                   
-------------                                                                   
ATLASSIAN                                                                       
COGNOS                                                                          
DEFAULT                                                                         
METAFORCE                                                                       
NAVISION                                                                        
PIVOTAL                                                                         
QUANTUM                                                                         
ZERVICEPOINT                                                                    
--------
DEFAULT                                                                         
--------

So, my question is, how can I print out the instances of the server, and at the same time, write them to the database table [SQLServerInformation].[dbo].[$ServerName_server_trunc]?

Solution by Scott Hodgin:

# We store all SQL Servers in this table. Here we fetch them into a variable.
$ServerName = (Invoke-Sqlcmd -ServerInstance CBSWSQLMGT01 -Query "SELECT ServerName AS Name FROM [SQLServerInformation].[dbo].[server_list]").Name

foreach ($Server in $ServerName) {
    # Fetch the instances under the server.
    $Instances = Get-ChildItem -Path "SQLSERVER:\SQL\$Server"
    $InstanceString = ($Instances.Name -join "`r`n" | Out-String).Trim()
    $InstanceString = $InstanceString.Replace("$Server`r`n","MSSQLSERVER`r`n")
    $InstanceString = $InstanceString.Replace("$Server\","")

    Invoke-Sqlcmd -ServerInstance CBSWSQLMGT01 -Query "
        TRUNCATE TABLE [SQLServerInformation].[dbo].[$Server`_server]
    "

    Invoke-Sqlcmd -ServerInstance $Server -Query "
        SELECT SERVERPROPERTY('MachineName') AS [Server], 
    '$InstanceString' AS [Instance(s)],
           (SELECT Server_type = CASE 
                                   WHEN virtual_machine_type = 1 THEN 'Virtual' 
                                   ELSE 'Physical' 
                                 END 
            FROM   sys.dm_os_sys_info) AS [Virtual/Physical], 
           (SELECT ( cpu_count / hyperthread_ratio ) 
            FROM   sys.dm_os_sys_info) AS [CPU(s)], 
           (SELECT CASE 
                     WHEN hyperthread_ratio = cpu_count THEN cpu_count 
                     WHEN ( ( cpu_count - hyperthread_ratio ) / ( 
                            cpu_count / hyperthread_ratio ) ) 
                          = 0 THEN 1 
                   END 
            FROM   sys.dm_os_sys_info) AS [Core(s)], 
           (SELECT ( [total_physical_memory_kb] / 1024 ) AS [RAM (MB)] 
            FROM   [master].[sys].[dm_os_sys_memory]) AS [RAM (MB)], 
           LEFT(@@VERSION, Charindex(')', @@VERSION)) AS [Version], 
           SERVERPROPERTY('ProductVersion') AS [Build], 
           SERVERPROPERTY('Edition') AS [Edition];"| 

           # Write it all into a table.
           Write-SqlTableData -ServerInstance CBSWSQLMGT01 -DatabaseName SQLServerInformation -SchemaName dbo -TableName $Server`_server -Force
}

Answer 1

Here is something you could tweak to your preferences.

I created a comma separated string of the instances and included that as a literal into the invoke-sqlcmd. In the following example, I hardcoded my local computer name (sc-hgrckb2) which has 3 instances.

Per your comment, I used a couple of REPLACE functions to make the DEFAULT instance show up as "DEFAULT" and also removed the server name and slash from the named instances. (In my testing, the Default Instance always showed up first in the $InstanceString.)

$ServerName = "sc-hgrckb2"

foreach ($Server in $ServerName) {
    # Fetch the instances under the server.
    $Instances = Get-ChildItem -Path "SQLSERVER:\SQL\$Server"
    $InstanceString = ($Instances.Name -join "," | Out-String).Trim()
    $InstanceString = $InstanceString.Replace("$ServerName,","DEFAULT,")
    $InstanceString = $InstanceString.Replace("$ServerName\","")

    Invoke-Sqlcmd -ServerInstance $Server -Query "
        SELECT SERVERPROPERTY('MachineName') AS [Server], 
    '''$InstanceString''' AS [Instance(s)],
           (SELECT Server_type = CASE 
                                   WHEN virtual_machine_type = 1 THEN 'Virtual' 
                                   ELSE 'Physical' 
                                 END 
            FROM   sys.dm_os_sys_info) AS [Virtual/Physical], 
           (SELECT ( cpu_count / hyperthread_ratio ) 
            FROM   sys.dm_os_sys_info) AS [CPU(s)], 
           (SELECT CASE 
                     WHEN hyperthread_ratio = cpu_count THEN cpu_count 
                     WHEN ( ( cpu_count - hyperthread_ratio ) / ( 
                            cpu_count / hyperthread_ratio ) ) 
                          = 0 THEN 1 
                   END 
            FROM   sys.dm_os_sys_info) AS [Core(s)], 
           (SELECT ( [total_physical_memory_kb] / 1024 ) AS [RAM (MB)] 
            FROM   [master].[sys].[dm_os_sys_memory]) AS [RAM (MB)], 
           LEFT(@@VERSION, Charindex(')', @@VERSION)) AS [Version], 
           SERVERPROPERTY('ProductVersion') AS [Build], 
           SERVERPROPERTY('Edition') AS [Edition];"
}

The results were:

Server           : SC-HGRCKB2
Instance(s)      : 'DEFAULT,RESTOREENCRYPT,SQL2017'
Virtual/Physical : Physical
CPU(s)           : 1
Core(s)          : 12
RAM (MB)         : 16306
Version          : Microsoft SQL Server 2017 (RTM-CU3-GDR)
Build            : 14.0.3015.40
Edition          : Enterprise Edition: Core-based Licensing (64-bit)