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