SQL Server PowerShell : Récupérer la volumétrie globale des serveurs SQL

Un collègue qui est chargé de rationnaliser les processus d’entreprises autour des serveurs de bases de données m’a demandé s’il était possible de connaître la volumétrie de chaque serveur SQL Server en terme d’espace disque et la volumétrie globale de l’ensemble de ces serveurs. L’idée ici est d’avoir une idée de la volumétrie d’espace disque pour chaque technologie de bases de données (SQL Server et Oracle notamment). Je vous propose ici un script PowerShell permettant de réaliser cette tâche.

Ce script s’exécute avec la version 2.0 de PowerShell. Il faut également avoir installé les outils de management de SQL Server 2008.

Ce script prend en paramètres d’entrées :

- Un fichier texte comportant la liste des serveurs SQL recensés dans l’entreprise (sql_server.txt)
- Un script SQL permettant de récupérer la volumétrie globale de chaque serveur pour l’ensemble des bases de données systèmes et utilisateurs. (size_serveur.sql)

. et il délivre en sortie :

- Un affichage dans la console pour chaque serveur joignable, la version de SQL Server et sa volumétrie globale
- Un fichier log (SqlLog.txt). Dans ce fichier existe une entrée pour chaque serveur où il s’est produit une erreur (Serveur inacessible, compte de connexion incorrect, erreur d’exécution du script SQL etc..).

Le script SQL (size_serveur.sql) :

/************************************************
* @Author = BARBARIN David                     *
* @Description =                               *
* Récupération de la version du serveur SQL +  *
* volumétrie globale de l’ensemble des bases   *
* des données (fichiers DATA + LOG)            *
************************************************/

DECLARE @version TINYINT

SELECT @version = CAST(RIGHT(LEFT(@@VERSION, CHARINDEX(‘.’, @@VERSION) – 1), 2) AS TINYINT)

– Version inférieur à SQL Server 2005
IF (@version < 9)
BEGIN
SELECT
    @@VERSION AS version_sql,
    CAST(SUM(size) * 8. / 1024 / 1024 AS DECIMAL(15,2)) AS size_GB
FROM master..sysaltfiles
END
– Version supérieure ou égale à SQL Server 2005
ELSE
BEGIN
SELECT
    @@VERSION AS version_sql,
    CAST(SUM(size) * 8. / 1024 / 1024 AS DECIMAL(15,2)) AS size_GB
FROM sys.master_files
END

 

Le script PowerShell (volumetrie_sqlserver.ps1) :

####################################
# @author = David BARBARIN                                  #
# @Description =                                                   #
# Volumétrie globale par serveur                              #
# et totale                                                            #
# @PARAM =                                                         #
# Fichier texte contenant la liste                              #
# des serveurs SQL                                                #
# @OUTPUT =                                                       #
# Nom du serveur + version                                    #
# Volumétrie par serveur                                         #
# Volumétrie globale                                             #
# Erreurs dans fichier log                                        #
# SqlLog.txt                                                         #
###################################

# Désactivation des messages d’erreurs sur la console PowerShell
$ErrorActionPreference = « SilentlyContinue »

# Chargement des snapin pour SQL Server
Add-PSSnapin SqlServerCmdletSnapin100
Add-PSSnapin SqlServerProviderSnapin100

# Récupération de la liste des serveurs
$servers = get-content « sql_server.txt »

# Path courant
$path = Get-Location

# Username et Password
$credentialsql = «  » # Pour une authentification de type Windows -> $credentialsql = « Windows »
$username = « user »
$password = « password »

$result_final = 0

# Initialisation fichier
$date = get-date
Write-output « Erreur log du $date » | out-File « $path\SqlLog.txt »

# Pour chaque serveur on récupère les informations de volumétrie
foreach($server in $servers)
{
$error.Clear()
try {
  # Exécution script sql de récupération volumétrie données.
  if ($credentialsql -eq « Windows »)
  {
   $result = Invoke-Sqlcmd -ServerInstance $server -inputFile « $path\size_serveur.sql » -OutputSqlErrors $false
  }
  else
  {
   $result = Invoke-Sqlcmd -Username $username -Password $password -ServerInstance $server -inputFile « $path\size_serveur.sql » -OutputSqlErrors $false
  }
  # Affichage du résultat dans la fenêtre Powershell
  Write-Host « ——— »
  Write-Host « Serveur :  » $server
  Write-Host « Version :  » $result.version_sql
  Write-Host « Volumétrie serveur :  » $result.size_GB « GB »
  # Comptabilisation du volume de données global
  $result_final = $result_final + $result.size_GB
}
catch
{
  # Affichage erreur dans la fenêtre PowerShell
  Write-Host « ——— »
  Write-Host « Serveur :  » $server
  Write-Host « Problème de récupération des données. Vérifiez le journal d’erreurs SqlLog.txt »
  # Enregistrement détail de l’erreur dans le fichier log
  $message = « — Erreur de récupération pour le serveur : $server . $error »
  Write-output $message | out-File -append « $path\SqlLog.txt »Â Â Â 
}
Finally {
  Continue
}   
}

# Affichage volumétrie globale dans la fenêtre Powershell
Write-Host « ——— »
Write-Host « ——— »
Write-Host « Volumétrie total :  » $result_final « GB »
Write-Host « ——— »
Write-Host « ——— »

# Suppression des snapin pour SQL Server
Remove-PSSnapin SqlServerCmdletSnapin100
Remove-PSSnapin SqlServerProviderSnapin100

 

Bon audit !!

David BARBARIN (Mikedavem)
Elève ingénieur CNAM Lyon

2 réflexions au sujet de « SQL Server PowerShell : Récupérer la volumétrie globale des serveurs SQL »

  1. Bonjour Etienne,

    Voici l’information dans le script :

    # Username et Password
    $credentialsql = «  » # Pour une authentification de type Windows -> $credentialsql = « Windows »
    $username = « user »
    $password = « password »

    Dans ce script, je pars du principe qu’il existe un compte générique pour faire cela sur l’ensemble des serveurs (Windows ou SQL). J’avais ce cas chez mon ancien client car il était beaucoup plus facile d’administrer les perf ou autre à partir d’un compte destiné à cela. Nous avions plus de 300 instances à gérer…

    Bien entendu si chez toi cela n’est pas le cas, tu seras obligé dans les mettre dans le fichier d’information des serveurs. Le désavantage est qu’il faudra maintenir cette liste à jour au fur et à mesure mais bon en général cela n’est malheureusement pas du ressort du dba :-)

    A+

  2. Salut David,
    Je pense qu’il manque une information pour ton script powershell.
    En fait comment tu recupères le $username/$password pour chaque serveur ? tu le saisis à la main au fur et à mesure de la demande de la console ?
    J’ai écrit un petit billet sur comment monitorer en temps réel les services SQL sur plusieurs serveurs.
    Pour celà j’ai utiliser en entrée de mon script Powershell, un fichier contenant toutes les infos des serveurs à monitorer puis en Splitant chaque ligne du fichier je recupère automatiquement le $username/$password entre autre.
    Regarde le lien ci-dessous, on pourra en discuter si tu veux.

    http://blog.developpez.com/zinzineti/p9065/powershell-sqlserver/monitoring-des-services-sql-de-toutes-le-2/#more9065

    A+
    Etienne ZINZINDOHOUE

Laisser un commentaire