PoshCode Archive  Artifact [d51483df49]

Artifact d51483df497c6e1ac7150d0c4e22ecfea768a5908e5f12ef6dcf3dbe9b29df4a:

• File get-SQLInstanceInfo.ps1 — part of check-in [e6aab04b2c] at 2018-06-10 13:33:48 on branch trunk — Function to return the following; DisplayName, Name, Clustered, InstanceID, FileVersion, Version, VirtualName(If Clustered), Instance, Port, ServiceState, ServiceAccount, Edition, AuditLevel, LoginMode, PhysicalMemory, Processors, Product, ProductLevel, MajorVersion, MinorVersion, Build, Release, NamedPipesEnabled) (user: Rich Vantrease size: 4744)

# encoding: ascii
# api: powershell
# title: get-SQLInstanceInfo
# description: Function to return the following; DisplayName, Name, Clustered, InstanceID, FileVersion, Version, VirtualName(If Clustered), Instance, Port, ServiceState, ServiceAccount, Edition, AuditLevel, LoginMode, PhysicalMemory, Processors, Product, ProductLevel, MajorVersion, MinorVersion, Build, Release, NamedPipesEnabled)
# version: 0.1
# type: function
# author: Rich Vantrease
# license: CC0
# function: get-SQLInstanceInfo2
# x-poshcode-id: 3977
# x-archived: 2016-03-22T08:25:22
# x-published: 2013-02-21T01:55:00
#
#
function get-SQLInstanceInfo2
{
	param
	(
		[Parameter(Mandatory=$True)][string]$ComputerName
	)
	$InstanceInfos = @()

    $Instances = (new-object 'microsoft.sqlserver.management.smo.Wmi.ManagedComputer' "$ComputerName").Services | where-object{$_.type -eq 'SqlServer'}

    foreach($Instance in $Instances )
    {
        
        [psobject]$InstanceInfo = "" | Select-Object DisplayName, Name, Clustered, `
                                                        InstanceID, FileVersion, Version, `
                                                        VirtualName, Instance, Port, ServiceState, `
                                                        ServiceAccount, Edition, AuditLevel, LoginMode, `
                                                        PhysicalMemory, Processors, Product, ProductLevel, `
                                                        MajorVersion, MinorVersion, Build, Release,NamedPipesEnabled
                                                    
        write-verbose "Processing SQL Instance: $($Instance.Name)"
        
        $InstanceInfo.DisplayName    = $Instance.DisplayName
        $InstanceInfo.Name           = $Instance.Name
        $InstanceInfo.Clustered      = $Instance.AdvancedProperties['CLUSTERED'].Value
        $InstanceInfo.InstanceID     = $Instance.AdvancedProperties['INSTANCEID'].Value
        $InstanceInfo.FileVersion    = $Instance.AdvancedProperties['FILEVERSION'].Value
        $InstanceInfo.Version        = $Instance.AdvancedProperties['VERSION'].Value
        if($Instance.AdvancedProperties['VSNAME'].Value -eq ''){ $InstanceInfo.VirtualName = $(hostname) }
        else { $InstanceInfo.VirtualName    = $Instance.AdvancedProperties['VSNAME'].Value }
        if($Instance.Name.Split('$')[1] -eq $Null){ [string]$InstanceName = 'MSSQLSERVER' }
        else { [string]$InstanceName = $Instance.Name.Split('$')[1] }
        $InstanceInfo.Instance       = $InstanceName
        $InstanceInfo.Port           = $Instance.Parent.ServerInstances[$InstanceName].ServerProtocols['Tcp'].IPAddresses['IPALL'].IPAddressProperties['TcpPort'].Value
        $InstanceInfo.NamedPipesEnabled = $Instance.Parent.ServerInstances[$InstanceName].ServerProtocols['Np'].IsEnabled
        $InstanceInfo.ServiceAccount = $Instance.ServiceAccount
        $InstanceInfo.ServiceState   = $Instance.ServiceState
        
        if($InstanceInfo.Clustered){ $SQLInstanceName = "$($InstanceInfo.VirtualName),$($InstanceInfo.Port)" }
        else { $SQLInstanceName = "$ComputerName,$($InstanceInfo.Port)" }
        
        write-verbose "SQL InstanceName: $SQLInstanceName"
        
        [System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo") |out-null

        $SQL = new-object "Microsoft.SQLServer.Management.SMO.Server" $SQLInstanceName

        $InstanceInfo.Edition        = $SQL.Edition
        $InstanceInfo.AuditLevel     = $SQL.AuditLevel
        $InstanceInfo.LoginMode      = $SQL.LoginMode
        $InstanceInfo.PhysicalMemory = $SQL.PhysicalMemory
        $InstanceInfo.Processors     = $SQL.Processors
        $InstanceInfo.Product        = $SQL.Product
        $InstanceInfo.ProductLevel   = $SQL.ProductLevel
        $InstanceInfo.MajorVersion   = $SQL.Version.Major
        $InstanceInfo.MinorVersion   = $SQL.Version.Minor
        $InstanceInfo.Build          = $SQL.Version.Build
        
        if($SQL.Version.Major -eq 10)
        {
            switch($SQL.Version.Minor)
            {
                0
                {
                    $InstanceInfo.Release = 'Gold'
                }
                50
                {
                    $InstanceInfo.Release = 'R2'
                }
                else
                {
                    throw "Could not convert minor version into release.  Version number $($SQL.Versioin.Minor)"
                }
            }
        } else { $InstanceInfo.Release = 'Gold' }
         
        $InstanceInfos += $InstanceInfo
        
    }

    write-verbose "Showing results"
    $InstanceInfos
}