Script PowerShell-Inventaire des instances SQL Server

Il existe une besoin récurrent d’inventorier els instances SQL sur el réseau. Ne serait-ce que pour valider la conformité au niveau des licences acquises, en fonction des éditions, du nombre de cœurs, etc …

Mais on peut aussi avoir un simple besoin de lister les bases de différentes instances, de valider les modèles de récupération ou d’autres paramètres fondamentaux comme Page_Verify, Auto_Close et Auto_Shrink, mais encore la taille ou même la nombre de connexions et la date de dernière activité recensée …

Pour poursuivre la série de petits scripts PowerShell, je vous propose ce petit bout de code permettant d’inventorier les instances et les bases … Notez que l’on peut faire des exclusions mais également ajouter des instances non “découvrables” (pour cause de Firewall, WAN, etc..) au travers d’un fichier texte.



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

clear-host 

#$DiscoveredInstanceList = sqlcmd -L
# or
$DiscoveredInstanceTable = [System.Data.Sql.SqlDataSourceEnumerator]::Instance.GetDataSources()
$DiscoveredInstanceList = @()
ForEach ($InstanceName in $DiscoveredInstanceTable) {
    $s = $InstanceName.ServerName
    if (-not ([string]::IsNullOrEmpty($InstanceName.Instancename))) {
        $s += "\" + $InstanceName.Instancename
    }
    $DiscoveredInstanceList += $s
}
# or
# $DiscoveredInstanceList = "srv1","srv2"

try {
    $AdditionalInstancesListFile = Get-Content "C:\temp\AdditionalServers.txt" 
    $DiscoveredInstanceList += $AdditionalInstancesListFile
}
catch {}


$ExcludedHostList = "devsql2017","(local)"


[int]$global:TotalDatabaseStorage = 0
[int]$global:TotalDatabaseCount = 0

$ListInstances = @()
$ListDatabases = @()
$InstanceErrorList = @()
$DisplayInstanceErrorList = $false

$Debug = $false

$CheckDatabaseDetails = $true 
$CheckLastUserAccess = $true
$CheckSystemDatabases = $true 

$OutGridView = $true
$ExportCSV = $true
$ExportCSVFile = "c:\temp\SQLInstancesInventory.csv"

ForEach ($InstanceName in $DiscoveredInstanceList) {
    $InstanceName = $InstanceName.trim()
    if ($InstanceName -eq "") {continue}
    if ($InstanceName -eq "Servers:") {continue}
    
    
    # Check excluded instances
    if ($ExcludedHostList -contains $InstanceName) {
        if ($Debug) {
            Write-Host $InstanceName " excluded" -ForegroundColor yellow
        }
        continue
    }

    $Server = New-Object -TypeName  Microsoft.SQLServer.Management.Smo.Server($InstanceName)
    $Server.ConnectionContext.ConnectTimeout = 1

    if (!($Server.ComputerNamePhysicalNetBIOS)) {
        $InstanceErrorList +=  "Error connecting $InstanceName"
        continue
    }
    else {

        $Databases = $server.Databases | Where-Object Status -eq "normal" | sort-object ID

        Write-Host $InstanceName "-"$Server.Edition "-" $Server.VersionString "(" $Server.ProductLevel ") -" $Server.collation `

        $InstanceStorage = 0
        $DatabaseCount = 0
        foreach ($Database in $Databases) {
             try {
                 If (($CheckSystemDatabases) -or ($Database.iD -gt 4)) {
                    $InstanceStorage += $Database.size
                    $DatabaseCount += 1
                    if ($CheckDatabaseDetails) {
                        if ($debug) {
                            Write-Host "  " $Database.Name "- Owner" $Database.Owner "- RecoveryModel" $Database.RecoveryModel "- Size" $Database.Size.ToString("N") "MB"
                        }

                        if ($CheckLastUserAccess) {
                            $tSQL = "SELECT database_id , 
	                                       CASE WHEN max(last_user_seek) > max(last_user_scan) THEN max(last_user_seek)
	                                            ELSE max(last_user_scan)
	                                       END AS LastUserRead,
                                           max(last_user_update) as LastUserWrite
                                    FROM sys.dm_db_index_usage_stats
                                    WHERE database_id = " + $Database.ID + "
                                    GROUP BY database_id "
                            
                            $LastUserRead = $Database.ExecuteWithResults($tSQL).Tables[0].LastUserRead
                            $LastUserWrite = $Database.ExecuteWithResults($tSQL).Tables[0].LastUserWrite

                            if (-not ([string]::IsNullOrEmpty($LastUserRead))) {$LastUserRead = $LastUserRead.ToString("yyyy-MM-dd HH:mm:ss")}
                            if (-not ([string]::IsNullOrEmpty($LastUserWrite))) {$LastUserWrite = $LastUserWrite.ToString("yyyy-MM-dd HH:mm:ss")}

                        }
                        else {
                            $LastUserRead = ""
                            $LastUserWrite = ""
                        }


                        $LastKnownGood = $($Database.ExecuteWithResults("DBCC DBINFO() WITH TABLERESULTS").Tables[0] | Where-Object {$_.Field -eq "dbi_dbccLastKnownGood"} | Select-Object -First 1).value

                        $ListDatabases += New-Object PSObject -Property @{InstanceName=$Server.name;`
                                                                          VersionMajor=$Server.VersionMajor;`
                                                                          DatabaseName=$Database.Name;`
                                                                          CompatibilityLevel=$Database.CompatibilityLevel.ToString().replace("Version","");`
                                                                          RecoveryModel=$Database.RecoveryModel;`
                                                                          Size=$Database.Size.ToString("N");`
                                                                          Owner=$Database.Owner;`
                                                                          Collation=$Database.collation;`
                                                                          AutoClose=$Database.AutoClose;` 
                                                                          AutoShrink=$Database.AutoShrink;`
                                                                          IsReadCommittedSnapshotOn=$Database.IsReadCommittedSnapshotOn;`
                                                                          PageVerify=$Database.PageVerify;`
                                                                          ActiveConnections=$Database.ActiveConnections;`
                                                                          CreateDate=$database.CreateDate.ToString("yyyy-MM-dd HH:mm:ss");`
                                                                          LastFullBackupDate=$database.LastBackupDate.ToString("yyyy-MM-dd HH:mm:ss");`
                                                                          LastLogBackupDate=$database.LastLogBackupDate.ToString("yyyy-MM-dd HH:mm:ss");`
                                                                          LastKnownGood=$LastKnownGood;`
                                                                          LastUserRead=$LastUserRead;`
                                                                          LastUserWrite=$LastUserWrite;`
                                                                          }
                    }
                 }
            }
            catch {
                Write-host -ForegroundColor Red $_.Exception.Message
            }
        }
        $global:TotalDatabaseStorage += $InstanceStorage
        $global:TotalDatabaseCount += $DatabaseCount

        if ($Debug) {
        
            Write-Host $InstanceName ": " $DatabaseCount " Databases ("$InstanceStorage.ToString("N") "MB )" 
        }

        $TFList = $Server.EnumActiveGlobalTraceFlags() | Where-Object Global -EQ 1 | Select-Object TraceFlag
        if (-not ([string]::IsNullOrEmpty($TFList))) {
            $TraceFlags = [string]::Join(",",$TFList.TraceFlag)
        }
        else {$TraceFlags = ""} 
        
        

        $ListInstances += New-Object PSObject -Property @{NetName=$Server.NetName;`
                                                         InstanceName=$Server.name;`
                                                         Edition=$Server.Edition;`
                                                         VersionMajor=$Server.VersionMajor;`
                                                         Version=$Server.VersionString;`
                                                         ProductLevel=$Server.ProductLevel;`
                                                         Collation=$Server.collation;`
                                                         Processors=$server.Processors;` 
                                                         PhysicalMemory=$Server.PhysicalMemory;`
                                                         MaxServerMemory=$Server.Configuration.MaxServerMemory.RunValue;`
                                                         DatabaseCount=$DatabaseCount;`
                                                         TotalSizeMB=$InstanceStorage.ToString("N");`
                                                         ServiceAccount=$Server.ServiceAccount;`
                                                         LoginMode=$Server.LoginMode;`
                                                         DatabaseEngineType=$Server.DatabaseEngineType;`
                                                         ActiveSessions=$server.EnumProcesses($false).Rows.Count;`
                                                         TraceFlags=$TraceFlags;`
                                                         }
    }

}


if ($OutGridView) {
    $ListInstances | Sort-Object InstanceName | Select-Object NetName, InstanceName,Edition,VersionMajor,Version,ProductLevel,`
                                                             Collation,Processors,PhysicalMemory,MaxServerMemory,DatabaseCount,`
                                                             TotalSizeMB,ServiceAccount,LoginMode,DatabaseEngineType,ActiveSessions,TraceFlags |   `
                                                Out-GridView

    if ($CheckDatabaseDetails) {
        $ListDatabases | Sort-Object InstanceName,DatabaseName | Select-Object InstanceName,VersionMajor,DatabaseName,CompatibilityLevel,`
                                                                               ActiveConnections,RecoveryModel,Collation,AutoClose,AutoShrink,`
                                                                               IsReadCommittedSnapshotOn,PageVerify,Size,Owner,CreateDate,`
                                                                               LastFullBackupDate,LastLogBackupDate,LastKnownGood,LastUserRead,LastUserWrite | `
                                                                 Out-GridView
    }
}

 
if ($ExportCSV) {

    $ListInstances | Sort-Object InstanceName | Select-Object NetName, InstanceName,Edition,VersionMajor,Version,ProductLevel,`
                                                             Collation,Processors,PhysicalMemory,MaxServerMemory,DatabaseCount,`
                                                             TotalSizeMB,ServiceAccount,LoginMode,DatabaseEngineType,ActiveSessions,TraceFlags |   `
                                                Export-Csv $ExportCSVFile -NoTypeInformation  -Force -Delimiter ";"

    if ($CheckDatabaseDetails) {
        $ListDatabases | Sort-Object InstanceName,DatabaseName | Select-Object InstanceName,VersionMajor,DatabaseName,CompatibilityLevel,`
                                                                               ActiveConnections,RecoveryModel,Collation,AutoClose,AutoShrink,`
                                                                               IsReadCommittedSnapshotOn,PageVerify,Size,Owner,CreateDate,`
                                                                               LastFullBackupDate,LastLogBackupDate,LastKnownGood,LastUserRead,LastUserWrite | `
                                                                 Export-Csv $ExportCSVFile -NoTypeInformation -Force -Delimiter ";"
    }
}

# Display grand total 
if ($global:TotalDatabaseCount -gt 0) {
    write-host ""
    write-host "Grand Total :"
    Write-Host $global:TotalDatabaseCount " Databases ("$global:TotalDatabaseStorage.ToString("N") "MB )" 
}

if ($DisplayInstanceErrorList) {
    write-host ""
    write-host "Errors :"
    $InstanceErrorList
}


Bien entendu je vous encourage à modifier ce bout de code pour qu’il s’adapte à vos attentes … Merci cependant de conserver les crédits …

Happy PowerShell

Publicités

A propos Christophe

Consultant SQL Server Formateur certifié Microsoft MVP SQL Server MCM SQL Server 2008
Cet article, publié dans PowerShell, SQL Server, est tagué . Ajoutez ce permalien à vos favoris.

Laisser un commentaire

Entrez vos coordonnées ci-dessous ou cliquez sur une icône pour vous connecter:

Logo WordPress.com

Vous commentez à l'aide de votre compte WordPress.com. Déconnexion /  Changer )

Photo Google+

Vous commentez à l'aide de votre compte Google+. Déconnexion /  Changer )

Image Twitter

Vous commentez à l'aide de votre compte Twitter. Déconnexion /  Changer )

Photo Facebook

Vous commentez à l'aide de votre compte Facebook. Déconnexion /  Changer )

w

Connexion à %s