1

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
}
  • 1
    have you looked at dbatools? This is an entire suite of PowerShell commands designed specifically for SQL Server: dbatools.io – DimUser Feb 21 '18 at 12:19
  • Yes, but I have yet to find anything that will print out all instances on the server. – tobulos1 Feb 21 '18 at 12:24
  • @tobulos1 We are actually working on a Find-DbaInstance now, hopefully it will hit release this week. You can see code for it in the active PR here – Shawn Melton Mar 6 '18 at 16:05
  • To get system/OS level information you can currently use Get-DbaComputerSystem and/or Get-DbaOperatingSystem they both pull various bits of information for a given server or array of servers you pass in. – Shawn Melton Mar 6 '18 at 16:07
2

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)
  • Sorry, my bad. I see what the problem is now. You store only one server in the variable $ServerName, and it works. I fetch two server names with the variable $QueryFetchServerNames. That's why it's not working for me, I believe. – tobulos1 Feb 22 '18 at 5:51
  • Never mind, I just had to change $ServerName to $Server in the replace arguments! Now it works as expected. Full solved code in my description. Thank you so much for your help :) – tobulos1 Feb 22 '18 at 6:19

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.