Document a cluster

Aug 2, 2013 at 12:11 PM
Edited Aug 2, 2013 at 12:11 PM
Hello,

I tried to run the script against a SQL Server Cluster.
.\Get-SqlServerInventoryToClixml.ps1 -ComputerName CLUSTERNAME
During the script is running, I get an error. It seems that the script tries to connect to the active node directly, which isn't working.
WARNING: Failed to connect to ACTIVENODE
WARNING: Failed to connect to ACTIVENODE
WARNING: Failed to connect to ACTIVENODE
WARNING: [ACTIVENODE] Error gathering instance information - max error threshold reached (3)
Write-Log : Failed to scan ACTIVENODE -  3 errors
At C:\Users\fox0ogk-adm\Documents\WindowsPowerShell\Modules\SqlServerInventory\SqlServerInventory.psm1:275 char:13
+             Write-Log <<<<  -Message $Message -MessageLevel $MessageLevel
    + CategoryInfo          : NotSpecified: (:) [Write-Error], WriteErrorException
    + FullyQualifiedErrorId : Microsoft.PowerShell.Commands.WriteErrorException,Write-Log
Has someone an idea how to fix this?

Greetings
Andre
Coordinator
Aug 7, 2013 at 8:19 PM
Hi Andre,
I recommend specifying the node names for the -ComputerName parameter rather than the cluster name. During the service discovery phase SQL Power Doc will check to see if the service is part of a cluster; if so it will connect to the cluster name to gather configuration details for the database engine and connect to each individual machine to gather OS details.

--Kendal
Aug 7, 2013 at 10:17 PM
Hi Kendal,

great... thanks for your response.
I will try it tomorrow and will Report the results.

Thanks a lot,

Andre
Aug 8, 2013 at 10:18 AM
Edited Aug 8, 2013 at 10:19 AM
Hi Kendal,

checked your solution for this problem, but unfortunately it will not work.
I attached you the error from the verbose.log.
2013-08-08 09:07:26.7377 ? Beginning scan of 1 instance(s)
2013-08-08 09:07:26.9008 ? Gathering information from ACTIVENODE [Instance 1 of 1]
2013-08-08 09:07:56.7193 + Failed to connect to ACTIVENODE 
2013-08-08 09:07:56.7271 + Failed to connect to ACTIVENODE 
2013-08-08 09:07:56.7330 + Failed to connect to ACTIVENODE 
2013-08-08 09:07:56.7398 + [ACTIVENODE ] Error gathering instance information - max error threshold reached (3)
2013-08-08 09:07:56.9713 ! Failed to scan ACTIVENODE -  3 errors
2013-08-08 09:07:57.2574 ? Instance scan complete (Success: 0; Failure: 1)
Script call:
.\Get-SqlServerInventoryToClixml.ps1 -ComputerName ACTIVENODE,PASSIVENODE
Cheers
Andre
Coordinator
Aug 8, 2013 at 8:21 PM
Andre,
What version of SQL Server and Windows is your cluster running?

Stupid question, but are you able to connect to your cluster using SSMS from the same machine that SQL Power Doc is running from?

--Kendal
Aug 8, 2013 at 9:07 PM
Hi Kendal,

SQL Server is 2008 SP3 CU11 and 2008R2 SP2. The Windows Versions are Windows Server 2008 R2.

I can connect to the Cluster Instances via SSMS and SQLCMD. For sure, only when I will use the virtual node name, or the IP-Adress of the cluster ressource, not when I'm using the hostname of the cluster node on which the instance is active at the moment. Also the user has sysadmins rights on SQL Server and administrator rights on windows.

Cheers
Andre
Coordinator
Aug 8, 2013 at 10:48 PM
What I'm seeing from the example call that you posted + log data + details about versions of Windows that you're running is that the discovery piece of SQL Power Doc found the clustered instance but isn't properly resolving the cluster name.

Would you mind running the code below (changing the value for $ActiveNodeName to match your active node name) on the same server where you're running SQL Power Doc and paste the results (with names & accounts in the results renamed/redacted to protect the innocent) here for me?
$ActiveNodeName = 'ACTIVENODE'


# Load SMO Assemblies
[System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.SMO') | ForEach-Object {
    if ($_.GetName().Version.Major -ge 10) {
        [System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.SMOExtended') | Out-Null
        [System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.SQLWMIManagement') | Out-Null
    }
}


# Connect to the server using the ManagedComputer object
$ManagedComputer = New-Object -TypeName 'Microsoft.SqlServer.Management.Smo.Wmi.ManagedComputer' -ArgumentList $ActiveNodeName


# Iterate through each SQL Server service and determine if it's clustered (and what the cluster name is)
$ManagedComputer.Services | ForEach-Object {

    # Initialize variables
    $IsClusteredInstance = $false
    $DomainName = $null
    $ClusterName = $null
    $FQClusterName = $null
    $ServiceIpAddress = $null

    try {

        if ($_.AdvancedProperties['CLUSTERED'].Value -eq $true) {

            $IsClusteredInstance = $true

            Get-WmiObject -Namespace root\CIMV2 -Class Win32_ComputerSystem -Property Domain -ComputerName $ActiveNodeName -ErrorAction Stop | ForEach-Object {
                $DomainName = $_.Domain
            }

            $ClusterName = $_.AdvancedProperties['VSNAME'].Value
            $FQClusterName = [String]::Join('.', @($_.AdvancedProperties['VSNAME'].Value, $DomainName)).ToUpper()

            [System.Net.Dns]::GetHostByName($ClusterName) | ForEach-Object {
                $_.AddressList | Where-Object { $_.AddressFamily -ieq 'InterNetwork' } | ForEach-Object {
                    $ServiceIpAddress = $_.IPAddressToString
                }
            }

        } else {
            $IsClusteredInstance = $false
            $ClusterName = 'NOT A CLUSTERED SERVICE'
        }
    }
    catch {
        $IsClusteredInstance = $false
        $ClusterName = 'ERROR RESOLVING CLUSTER NAME'
    }


    New-Object -TypeName PSObject -Property @{
        ComputerName = $ActiveNodeName
        DisplayName = $_.DisplayName
        Description = $_.Description
        IsClusteredInstance = $IsClusteredInstance
        ClusterName = $ClusterName
        FQClusterName = $FQClusterName
        AdvancedProperties = $_.AdvancedProperties
    }
    
} | Format-List -Property *

Remove-Variable -Name ActiveNodeName, ManagedComputer, IsClusteredInstance, DomainName, ClusterName, FQClusterName, ServiceIpAddress
Thanks,
Kendal
Aug 25, 2013 at 9:27 PM
Hi Kendal,

sorry for the late response. The last days were very busy, but now my vacation starts. :)

Here is the result of the script:
IsClusteredInstance : False
DisplayName         : SQL Server Integration Services 10.0
AdvancedProperties  : {}
ClusterName         : NOT A CLUSTERED SERVICE
ComputerName        : ACTIVENODE
Description         : Provides management support for SSIS package storage and execution.
FQClusterName       :

IsClusteredInstance : False
DisplayName         : SQL Full-text Filter Daemon Launcher (MSSQLSERVER)
AdvancedProperties  : {}
ClusterName         : NOT A CLUSTERED SERVICE
ComputerName        : ACTIVENODE
Description         : Service to launch full-text filter daemon process which will perform document filtering and word
                      breaking for SQL Server full-text search. Disabling this service will make full-text search featu
                      res of SQL Server unavailable.
FQClusterName       :

IsClusteredInstance : True
DisplayName         : SQL Server (MSSQLSERVER)
AdvancedProperties  : {Name=CLUSTERED/Type=System.Boolean/Writable=False/Value=True, Name=DATAPATH/Type=System.String/W
                      ritable=False/Value=F:\MSSQL10.MSSQLSERVER\MSSQL, Name=DUMPDIR/Type=System.String/Writable=True/V
                      alue=F:\MSSQL10.MSSQLSERVER\MSSQL\LOG\, Name=ERRORREPORTING/Type=System.Boolean/Writable=True/Val
                      ue=False...}
ClusterName         : CLUSTERNAME
ComputerName        : ACTIVENODE
Description         : Provides storage, processing and controlled access of data, and rapid transaction processing.
FQClusterName       : CLUSTERNAME.FQDN.COM

IsClusteredInstance : True
DisplayName         : SQL Server Analysis Services (default)
AdvancedProperties  : {Name=CLUSTERED/Type=System.Boolean/Writable=False/Value=True, Name=DUMPDIR/Type=System.String/Wr
                      itable=True/Value=g:\OLAP\Log, Name=ERRORREPORTING/Type=System.Boolean/Writable=True/Value=False,
                       Name=INSTANCEID/Type=System.String/Writable=False/Value=MSAS10.MSSQLSERVER...}
ClusterName         : CLUSTERNAME
ComputerName        : ACTIVENODE
Description         : Supplies online analytical processing (OLAP) and data mining functionality for business intellige
                      nce applications.
FQClusterName       : CLUSTERNAME.FQDN.COM

IsClusteredInstance : True
DisplayName         : SQL Server Browser
AdvancedProperties  : {Name=BROWSER/Type=System.Boolean/Writable=True/Value=True, Name=CLUSTERED/Type=System.Boolean/Wr
                      itable=False/Value=True, Name=DUMPDIR/Type=System.String/Writable=True/Value=g:\OLAP\Log, Name=ER
                      RORREPORTING/Type=System.Boolean/Writable=True/Value=False...}
ClusterName         :
ComputerName        : ACTIVENODE
Description         : Provides SQL Server connection information to client computers.
FQClusterName       : .FQDN.COM

IsClusteredInstance : True
DisplayName         : SQL Server Agent (MSSQLSERVER)
AdvancedProperties  : {Name=CLUSTERED/Type=System.Boolean/Writable=False/Value=True, Name=DUMPDIR/Type=System.String/Wr
                      itable=True/Value=F:\MSSQL10.MSSQLSERVER\MSSQL\LOG\, Name=ERRORREPORTING/Type=System.Boolean/Writ
                      able=True/Value=False, Name=INSTANCEID/Type=System.String/Writable=False/Value=MSSQL10.MSSQLSERVE
                      R...}
ClusterName         : CLUSTERNAME
ComputerName        : ACTIVENODE
Description         : Executes jobs, monitors SQL Server, fires alerts, and allows automation of some administrative ta
                      sks.
FQClusterName       : CLUSTERNAME.FQDN.COM
Thanks
Andre
Oct 21, 2013 at 12:43 PM
Hi Kendal,

checked you new SQL Power Doc a few hours ago.
Just want to tell you that it is not working properly with SQL Server Clusters.

Greetings,

Andre
Coordinator
Oct 21, 2013 at 3:47 PM
Andre,
Thanks - I'm getting the same error. DOH!

Working on a fix for it today and will post a new version as soon as I can.

--Kendal
Coordinator
Oct 21, 2013 at 6:24 PM
Andre, I'm getting an error when running the scripts on a machine that's NOT in the same domain as the cluster, but when running on a machine inside the domain (that's not one of the clustered servers) the scripts work for me. Are you by any chance running the scripts in a similar scenario?

--Kendal
Oct 21, 2013 at 7:59 PM
Kendal,

I tried to run the script from a workstation that is joined the same domain as the cluster. Also I tried to run the scripts against more than cluster in the same domain, everytime the same error.
It seems to be the same Problem as before, but let me describe it again:

Imagine we are on a workstation and we want to run the script against a cluster in the same domain. The cluster resource group has the NetBIOS name CLUSTER01 which is build upon the nodes NODE01 and NODE02.

I ran the script like this. .\Convert-SqlServerInventoryClixmlToExcel.ps1 -ComputerName CLUSTER01

First it seems to run normally, but after a few seconds the script stops with the following error:
WARNING: Failed to connect to NODE01
WARNING: Failed to connect to NODE01
WARNING: Failed to connect to NODE01
WARNING: [NODE01] Error gathering instance information - max error threshold reached (3)
Write-Log : Failed to scan NODE01 -  3 errors
At C:\Users\User01\Documents\WindowsPowerShell\Modules\SqlServerInventory\SqlServerInventory.psm1:275 char:13
+             Write-Log <<<<  -Message $Message -MessageLevel $MessageLevel
    + CategoryInfo          : NotSpecified: (:) [Write-Error], WriteErrorException
    + FullyQualifiedErrorId : Microsoft.PowerShell.Commands.WriteErrorException,Write-Log
So, after connecting to the NetBIOS name of the Cluster Ressource CLUSTER01, the script will not use the cluster name to connect to the SQL instance, instead it tries to use the NetBIOS name of the active node NODE01. That is not working, because in the cluster environment the SQL instance is listening only on the IP and NetBIOS name of the cluster resource.

So, to shorten that a bit, why is your scripting trying to connect to the active node and not to the cluster itself?

Greetings,
Andre
Coordinator
Oct 21, 2013 at 10:19 PM
Hi Andre,
The script should try to connect to the cluster name and not each node name. My test setup consists of an AD controller, SAN, and 2 SQL Server instances all running in virtual machines. Each VM is running Windows 2008 R2 Enterprise Edition and the SQL Servers are 2008 R2 Developer Edition. It sounds like we've got a similar setup but I can't reproduce the error you're seeing (yet).

The sample output that you posted on Aug 25 shows that the instance is being detected as clustered so something else is happening here...

Would you mind running the following on the same machine where you've got the SQL Power Doc scripts and posting the (cleansed) output for me? (of course make sure to put the proper node names in here first).
Import-Module -Name NetworkScan
Find-SqlServerService -ComputerName ACTIVENODE,PASSIVENODE | Format-List -Property *
Remove-Module -Name NetworkScan
--Kendal
Oct 22, 2013 at 8:06 AM
Edited Oct 22, 2013 at 8:07 AM
Good Morning Kendal,

I ran this script a few minutes ago:
Import-Module -Name NetworkScan
Find-SqlServerService -ComputerName ACTIVENODE,PASSIVENODE | Format-List -Property *
Remove-Module -Name NetworkScan
And I got this result back:
ServiceAccount      : NT AUTHORITY\LOCAL SERVICE
ServiceStartDate    : 29.09.2013 11:38:44
ProcessId           : 7068
StartupParameters   :
ServiceProtocols    :
ServerName          : ACTIVENODE.DOMAIN.TLD
IsNamedInstance     : False
IsDynamicPort       :
IsClusteredInstance : False
ServiceState        : Running
ServiceIpAddress    :
Description         : Service to launch full-text filter daemon process which will perform document filtering and word
                      breaking for SQL Server full-text search. Disabling this service will make full-text search featu
                      res of SQL Server unavailable.
IsHadrEnabled       :
DisplayName         : SQL Full-text Filter Daemon Launcher (MSSQLSERVER)
ComputerName        : ACTIVENODE.DOMAIN.TLD
ServiceTypeName     : SQL Full-text Filter Daemon Launcher
ComputerIpAddress   : 10.250.7.200
StartMode           : Manual
Port                :
PathName            : "D:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\Binn\fdlauncher.exe" -s MSSQL10.
                      MSSQLSERVER


ServiceAccount      : DOMAIN\SERVICEACCOUNT
ServiceStartDate    : 29.09.2013 11:38:41
ProcessId           : 9072
StartupParameters   : -dF:\MSSQL10.MSSQLSERVER\MSSQL\DATA\master.mdf;-eF:\MSSQL10.MSSQLSERVER\MSSQL\Log\ERRORLOG;-lF:\M
                      SSQL10.MSSQLSERVER\MSSQL\DATA\mastlog.ldf;-T1117
ServiceProtocols    : {@{IsEnabled=True; Name=Np; DisplayName=Named Pipes; ProtocolProperties=System.Collections.Hashta
                      ble; IPAddresses=}, @{IsEnabled=True; Name=Sm; DisplayName=Shared Memory; ProtocolProperties=Syst
                      em.Collections.Hashtable; IPAddresses=}, @{IsEnabled=True; Name=Tcp; DisplayName=TCP/IP; Protocol
                      Properties=System.Collections.Hashtable; IPAddresses=System.Object[]}, @{IsEnabled=False; Name=Vi
                      a; DisplayName=VIA; ProtocolProperties=System.Collections.Hashtable; IPAddresses=}}
ServerName          : ACTIVENODE.DOMAIN.TLD
IsNamedInstance     : False
IsDynamicPort       : False
IsClusteredInstance : False
ServiceState        : Running
ServiceIpAddress    :
Description         : Provides storage, processing and controlled access of data, and rapid transaction processing.
IsHadrEnabled       :
DisplayName         : SQL Server (MSSQLSERVER)
ComputerName        : ACTIVENODE.DOMAIN.TLD
ServiceTypeName     : SQL Server
ComputerIpAddress   : 10.250.7.200
StartMode           : Manual
Port                : 1433
PathName            : "D:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\Binn\sqlservr.exe" -sMSSQLSERVER


ServiceAccount      : NT AUTHORITY\LOCAL SERVICE
ServiceStartDate    : 29.09.2013 11:34:12
ProcessId           : 2472
StartupParameters   :
ServiceProtocols    :
ServerName          : ACTIVENODE.DOMAIN.TLD
IsNamedInstance     : False
IsDynamicPort       :
IsClusteredInstance : False
ServiceState        : Running
ServiceIpAddress    :
Description         : Provides SQL Server connection information to client computers.
IsHadrEnabled       :
DisplayName         : SQL Server Browser
ComputerName        : ACTIVENODE.DOMAIN.TLD
ServiceTypeName     : SQL Server Browser
ComputerIpAddress   : 10.250.7.200
StartMode           : Auto
Port                :
PathName            : "C:\Program Files (x86)\Microsoft SQL Server\90\Shared\sqlbrowser.exe"


ServiceAccount      : DOMAIN\SERVICEACCOUNT
ServiceStartDate    : 29.09.2013 11:38:45
ProcessId           : 6812
StartupParameters   :
ServiceProtocols    :
ServerName          : ACTIVENODE.DOMAIN.TLD
IsNamedInstance     : False
IsDynamicPort       :
IsClusteredInstance : False
ServiceState        : Running
ServiceIpAddress    :
Description         : Executes jobs, monitors SQL Server, fires alerts, and allows automation of some administrative tasks.
IsHadrEnabled       :
DisplayName         : SQL Server Agent (MSSQLSERVER)
ComputerName        : ACTIVENODE.DOMAIN.TLD
ServiceTypeName     : SQL Server Agent
ComputerIpAddress   : 10.250.7.200
StartMode           : Manual
Port                :
PathName            : "D:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\Binn\SQLAGENT.EXE" -i MSSQLSERVER


ServiceAccount      : NT AUTHORITY\LOCAL SERVICE
ServiceStartDate    :
ProcessId           : 0
StartupParameters   :
ServiceProtocols    :
ServerName          : PASSIVE.DOMAIN.TLD
IsNamedInstance     : False
IsDynamicPort       :
IsClusteredInstance : False
ServiceState        : Stopped
ServiceIpAddress    :
Description         : Service to launch full-text filter daemon process which will perform document filtering and word
                      breaking for SQL Server full-text search. Disabling this service will make full-text search featu
                      res of SQL Server unavailable.
IsHadrEnabled       :
DisplayName         : SQL Full-text Filter Daemon Launcher (MSSQLSERVER)
ComputerName        : PASSIVE.DOMAIN.TLD
ServiceTypeName     : SQL Full-text Filter Daemon Launcher
ComputerIpAddress   : 10.250.7.201
StartMode           : Manual
Port                :
PathName            : "D:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\Binn\fdlauncher.exe" -s MSSQL10.
                      MSSQLSERVER


ServiceAccount      : DOMAIN\SERVICEACCOUNT
ServiceStartDate    :
ProcessId           : 0
StartupParameters   : -dF:\MSSQL10.MSSQLSERVER\MSSQL\DATA\master.mdf;-eF:\MSSQL10.MSSQLSERVER\MSSQL\Log\ERRORLOG;-lF:\M
                      SSQL10.MSSQLSERVER\MSSQL\DATA\mastlog.ldf;-T1117
ServiceProtocols    : {@{IsEnabled=True; Name=Np; DisplayName=Named Pipes; ProtocolProperties=System.Collections.Hashta
                      ble; IPAddresses=}, @{IsEnabled=True; Name=Sm; DisplayName=Shared Memory; ProtocolProperties=Syst
                      em.Collections.Hashtable; IPAddresses=}, @{IsEnabled=True; Name=Tcp; DisplayName=TCP/IP; Protocol
                      Properties=System.Collections.Hashtable; IPAddresses=System.Object[]}, @{IsEnabled=False; Name=Vi
                      a; DisplayName=VIA; ProtocolProperties=System.Collections.Hashtable; IPAddresses=}}
ServerName          : PASSIVE.DOMAIN.TLD
IsNamedInstance     : False
IsDynamicPort       : False
IsClusteredInstance : False
ServiceState        : Stopped
ServiceIpAddress    :
Description         : Provides storage, processing and controlled access of data, and rapid transaction processing.
IsHadrEnabled       :
DisplayName         : SQL Server (MSSQLSERVER)
ComputerName        : PASSIVE.DOMAIN.TLD
ServiceTypeName     : SQL Server
ComputerIpAddress   : 10.250.7.201
StartMode           : Manual
Port                : 1433
PathName            : "D:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\Binn\sqlservr.exe" -sMSSQLSERVER


ServiceAccount      : NT AUTHORITY\LOCAL SERVICE
ServiceStartDate    : 29.09.2013 11:50:14
ProcessId           : 3152
StartupParameters   :
ServiceProtocols    :
ServerName          : PASSIVE.DOMAIN.TLD
IsNamedInstance     : False
IsDynamicPort       :
IsClusteredInstance : False
ServiceState        : Running
ServiceIpAddress    :
Description         : Provides SQL Server connection information to client computers.
IsHadrEnabled       :
DisplayName         : SQL Server Browser
ComputerName        : PASSIVE.DOMAIN.TLD
ServiceTypeName     : SQL Server Browser
ComputerIpAddress   : 10.250.7.201
StartMode           : Auto
Port                :
PathName            : "C:\Program Files (x86)\Microsoft SQL Server\90\Shared\sqlbrowser.exe"


ServiceAccount      : DOMAIN\SERVICEACCOUNT
ServiceStartDate    :
ProcessId           : 0
StartupParameters   :
ServiceProtocols    :
ServerName          : PASSIVE.DOMAIN.TLD
IsNamedInstance     : False
IsDynamicPort       :
IsClusteredInstance : False
ServiceState        : Stopped
ServiceIpAddress    :
Description         : Executes jobs, monitors SQL Server, fires alerts, and allows automation of some administrative ta
                      sks.
IsHadrEnabled       :
DisplayName         : SQL Server Agent (MSSQLSERVER)
ComputerName        : PASSIVE.DOMAIN.TLD
ServiceTypeName     : SQL Server Agent
ComputerIpAddress   : 10.250.7.201
StartMode           : Manual
Port                :
PathName            : "D:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\Binn\SQLAGENT.EXE" -i MSSQLSERVE
                      R
I also let this script run against the Cluster:
Import-Module -Name NetworkScan
Find-SqlServerService -ComputerName CLUSTERRESSOURCENAME | Format-List -Property *
Remove-Module -Name NetworkScan
Oct 22, 2013 at 8:07 AM
And I got that back:
ServiceAccount      : NT AUTHORITY\LOCAL SERVICE
ServiceStartDate    : 29.09.2013 11:38:44
ProcessId           : 7068
StartupParameters   :
ServiceProtocols    :
ServerName          : ACTIVENODE.DOMAIN.TLD
IsNamedInstance     : False
IsDynamicPort       :
IsClusteredInstance : False
ServiceState        : Running
ServiceIpAddress    :
Description         : Service to launch full-text filter daemon process which will perform document filtering and word
                      breaking for SQL Server full-text search. Disabling this service will make full-text search featu
                      res of SQL Server unavailable.
IsHadrEnabled       :
DisplayName         : SQL Full-text Filter Daemon Launcher (MSSQLSERVER)
ComputerName        : ACTIVENODE.DOMAIN.TLD
ServiceTypeName     : SQL Full-text Filter Daemon Launcher
ComputerIpAddress   : 10.250.7.204
StartMode           : Manual
Port                :
PathName            : "D:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\Binn\fdlauncher.exe" -s MSSQL10.
                      MSSQLSERVER

ServiceAccount      : DOMAIN\SERVICEACCOUNT
ServiceStartDate    : 29.09.2013 11:38:41
ProcessId           : 9072
StartupParameters   : -dF:\MSSQL10.MSSQLSERVER\MSSQL\DATA\master.mdf;-eF:\MSSQL10.MSSQLSERVER\MSSQL\Log\ERRORLOG;-lF:\M
                      SSQL10.MSSQLSERVER\MSSQL\DATA\mastlog.ldf;-T1117
ServiceProtocols    : {@{IsEnabled=True; Name=Np; DisplayName=Named Pipes; ProtocolProperties=System.Collections.Hashta
                      ble; IPAddresses=}, @{IsEnabled=True; Name=Sm; DisplayName=Shared Memory; ProtocolProperties=Syst
                      em.Collections.Hashtable; IPAddresses=}, @{IsEnabled=True; Name=Tcp; DisplayName=TCP/IP; Protocol
                      Properties=System.Collections.Hashtable; IPAddresses=System.Object[]}, @{IsEnabled=False; Name=Vi
                      a; DisplayName=VIA; ProtocolProperties=System.Collections.Hashtable; IPAddresses=}}
ServerName          : ACTIVENODE.DOMAIN.TLD
IsNamedInstance     : False
IsDynamicPort       : False
IsClusteredInstance : False
ServiceState        : Running
ServiceIpAddress    :
Description         : Provides storage, processing and controlled access of data, and rapid transaction processing.
IsHadrEnabled       :
DisplayName         : SQL Server (MSSQLSERVER)
ComputerName        : ACTIVENODE.DOMAIN.TLD
ServiceTypeName     : SQL Server
ComputerIpAddress   : 10.250.7.204
StartMode           : Manual
Port                : 1433
PathName            : "D:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\Binn\sqlservr.exe" -sMSSQLSERVER

ServiceAccount      : NT AUTHORITY\LOCAL SERVICE
ServiceStartDate    : 29.09.2013 11:34:12
ProcessId           : 2472
StartupParameters   :
ServiceProtocols    :
ServerName          : ACTIVENODE.DOMAIN.TLD
IsNamedInstance     : False
IsDynamicPort       :
IsClusteredInstance : False
ServiceState        : Running
ServiceIpAddress    :
Description         : Provides SQL Server connection information to client computers.
IsHadrEnabled       :
DisplayName         : SQL Server Browser
ComputerName        : ACTIVENODE.DOMAIN.TLD
ServiceTypeName     : SQL Server Browser
ComputerIpAddress   : 10.250.7.204
StartMode           : Auto
Port                :
PathName            : "C:\Program Files (x86)\Microsoft SQL Server\90\Shared\sqlbrowser.exe"

ServiceAccount      : DOMAIN\SERVICEACCOUNT
ServiceStartDate    : 29.09.2013 11:38:45
ProcessId           : 6812
StartupParameters   :
ServiceProtocols    :
ServerName          : ACTIVENODE.DOMAIN.TLD
IsNamedInstance     : False
IsDynamicPort       :
IsClusteredInstance : False
ServiceState        : Running
ServiceIpAddress    :
Description         : Executes jobs, monitors SQL Server, fires alerts, and allows automation of some administrative ta
                      sks.
IsHadrEnabled       :
DisplayName         : SQL Server Agent (MSSQLSERVER)
ComputerName        : ACTIVENODE.DOMAIN.TLD
ServiceTypeName     : SQL Server Agent
ComputerIpAddress   : 10.250.7.204
StartMode           : Manual
Port                :
PathName            : "D:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\Binn\SQLAGENT.EXE" -i MSSQLSERVE
                      R
Hope that can help you to find the problem and a solution for this.

Thanks

Andre
Coordinator
Oct 22, 2013 at 7:53 PM
Hi Andre,
Thanks for being so patient (and helpful) with figuring this out. I think (crossing my fingers) I know where the specific problem is occurring. Would you run one more thing for me? This should be run in a PowerShell console on the same machine where you're running SQL Power Doc and using the same credentials:
[System.Net.Dns]::GetHostByName('CLUSTERNAME.FQDN.COM')
(Obviously, replace CLUSTERNAME.FQDN.COM with the FQDN of your cluster). What I'm expecting/hoping to see is an error returned, rather than a successful call. From what I can see in my code, if this fails then that could cause the service to be marked as NOT part of a cluster and an attempt to connect to it on the node name rather than the cluster name.

--Kendal
Oct 23, 2013 at 9:45 AM
Hi Kendal,

you are welcome, I just want to Help you and the community to get a great product.

I just executed your command in the PowerShell and get the following results
PS C:\> [System.Net.Dns]::GetHostByName('CLUSTERNAME.FQDN.COM')

HostName                                Aliases                                 AddressList
--------                                -------                                 -----------
CLUSTERNAME.FQDN.COM                    {}                                      {10.1.1.25}
The IP 10.1.1.25 is the IP of the Cluster ressource.

Greetings
Andre
Dec 10, 2013 at 5:28 PM
Hello Kendal,

I was in an urgent need to document 20+ SQL Servers and was very, very happy to find this because it did exactly what I need - except it didn't work on a SQL Cluster.

So I tried around a little bit (without changing a single bit of the code) and ended up with starting the script remotely using the following command:

'Get-SqlServerInventoryToClixml.ps1 -Computername NODE_A,NODE_B -LoggingPreference Debug'

Guess what: It worked! Even it did not when running directly on a cluster node or when using '-LoggingPreference Standard' (I didn't try 'Verbose').

I just wanted to let you know about this behavior, and also to say thank you for your great work.

Greetings,
Sam
Nov 6, 2014 at 11:35 AM
I acually had the same problem in my cluster.. Or... I could document a few sql instances. I discovered that Dynamic port and TCP port was enabled in TCP/IP configuration for each instance in SQL Configuration Manager. When I "disabled" TCP port, the script went through.

Thank you for a great script!

Cheers,
Nov 10, 2014 at 6:55 PM
Management says no to disabling TCP ports....is there any way to simply use the cluster name and have the script do all the behind the scenes work.....really, really want/need to use the script for sql inventory......any help would be greatly appreciated....thanks in advance.......
Coordinator
Nov 11, 2014 at 4:49 PM
Sam, 97marno, & wjbergen: You should be able to document a cluster without having to do anything special; you just need to provide either the names of the nodes in the cluster or the cluster name itself for the -computername parameter. (I recommend the node names because you'll get service configuration details from each, whereas if you provide the cluster name you'll only get details about the node the cluster is active on)

I tested this out in my VM lab and was able to document my cluster both by cluster name and by individual machine names without having to disable TCP/IP or any other workarounds. Based on this it seems like something about my test setup is different than your environment, so I have a few questions:
  • What version of SQL Power Doc are you running?
  • What version of SMO do you have installed?
  • Are you running the SQL Power Doc scripts on one of the cluster nodes or are the scripts running on a machine that's not part of the cluster? (note, I mean I want to know the machine the scripts are running on, not the machine(s) the scripts are running against)
  • Is your cluster a default instance or a named instance?
A few more details and I'm sure we'll get to the bottom of what's going on here...

Kendal