{"id":353668,"date":"2024-08-27T16:01:52","date_gmt":"2024-08-27T16:01:52","guid":{"rendered":"https:\/\/www.ninjaone.com\/script-hub\/trouver-et-documenter-les-instances-de-serveur-sql\/"},"modified":"2024-10-13T19:09:57","modified_gmt":"2024-10-13T19:09:57","slug":"trouver-et-documenter-les-instances-de-serveur-sql","status":"publish","type":"script_hub","link":"https:\/\/www.ninjaone.com\/fr\/script-hub\/trouver-et-documenter-les-instances-de-serveur-sql\/","title":{"rendered":"Comment utiliser PowerShell pour trouver et documenter les instances de serveur SQL"},"content":{"rendered":"

Vous souhaitez apprendre \u00e0 g\u00e9rer<\/strong> les instances de serveur et MSSQL \u00e0 l’aide de PowerShell<\/strong>? <\/span>Dans le monde de l’informatique, la gestion et la maintenance des instances SQL Server<\/a> est une t\u00e2che essentielle pour les professionnels de l’informatique et les fournisseurs de services g\u00e9r\u00e9s (MSP).<\/a> Savoir comment recueillir efficacement des informations sur les instances de serveur SQL peut consid\u00e9rablement am\u00e9liorer les t\u00e2ches administratives et rationaliser les processus. Cet article se penche sur un puissant script PowerShell con\u00e7u pour r\u00e9cup\u00e9rer une liste d’instances de serveurs MSSQL, en fournissant des informations d\u00e9taill\u00e9es et des applications pratiques.<\/p>\n

Contexte<\/h2>\n

Les instances SQL Server constituent l’\u00e9pine dorsale de nombreuses applications commerciales, car elles stockent et g\u00e8rent de grandes quantit\u00e9s de donn\u00e9es. Les professionnels de l’informatique doivent souvent identifier et g\u00e9rer ces instances pour garantir des performances et une s\u00e9curit\u00e9 optimales. Le script PowerShell fourni est un outil pr\u00e9cieux pour r\u00e9cup\u00e9rer rapidement des informations sur les instances de serveur SQL sur un syst\u00e8me. Cette capacit\u00e9 est cruciale pour des t\u00e2ches telles que les audits, le d\u00e9pannage et la surveillance du syst\u00e8me.<\/p>\n

Le script\u00a0:<\/h2>\n
#Requires -Version 5.1\r\n\r\n<#\r\n.SYNOPSIS\r\n    Gets a list of MSSQL server instances and optionally save the results to a custom field.\r\n.DESCRIPTION\r\n    Gets a list of MSSQL server instances and optionally save the results to a custom field.\r\n    The custom field can be either\/both a multi-line or WYSIWYG custom field.\r\n\r\n    SQL Server, SQL Server Developer and SQL Express are supported.\r\n\r\n    SQL \"Local\" that are built into an application are not supported as they aren't an SQL Server instance.\r\n\r\n    SQL service name that don't start with \"MSSQL$\" will not get detected.\r\n\r\n    PS > Get-Service -Name \"MSSQL`$*\"\r\n    Status   Name               DisplayName\r\n    ------   ----               -----------\r\n    Running  MSSQL$DB           SQL Server (DB)\r\n    Running  MSSQL$DB01         SQL Server (DB01)\r\n    Running  MSSQL$DB02         SQL Server (DB02)\r\n\r\n.EXAMPLE\r\n    (No Parameters)\r\n    ## EXAMPLE OUTPUT WITHOUT PARAMS ##\r\n     Status Name              Instance Path\r\n     ------ ----              -------- ----\r\n    Running SQL Server (DB01) DB01     C:\\Program Files\\Microsoft SQL Server\\MSSQL16.DB01\\MSSQL\r\n    Running SQL Server (DB02) DB02     C:\\Program Files\\Microsoft SQL Server\\MSSQL16.DB02\\MSSQL\r\n\r\nPARAMETER: -CustomFieldName \"ReplaceMeWithAnyMultilineCustomField\"\r\n    Saves an text table to a multi-line Custom Field with a list of SQL instances.\r\n.EXAMPLE\r\n    -CustomFieldName \"ReplaceMeWithAnyMultilineCustomField\"\r\n    ## EXAMPLE OUTPUT WITH CustomFieldName ##\r\n     Status Name              Instance Path\r\n     ------ ----              -------- ----\r\n    Running SQL Server (DB01) DB01     C:\\Program Files\\Microsoft SQL Server\\MSSQL16.DB01\\MSSQL\r\n    Running SQL Server (DB02) DB02     C:\\Program Files\\Microsoft SQL Server\\MSSQL16.DB02\\MSSQL\r\n\r\nPARAMETER: -CustomFieldParam \"ReplaceMeWithAnyWysiwygCustomField\"\r\n    Saves an html table to a Wysiwyg Custom Field with a list of SQL instances.\r\n.EXAMPLE\r\n    -WysiwygCustomFieldName \"ReplaceMeWithAnyWysiwygCustomField\"\r\n    ## EXAMPLE OUTPUT WITH WysiwygCustomFieldName ##\r\n     Status Name              Instance Path\r\n     ------ ----              -------- ----\r\n    Running SQL Server (DB01) DB01     C:\\Program Files\\Microsoft SQL Server\\MSSQL16.DB01\\MSSQL\r\n    Running SQL Server (DB02) DB02     C:\\Program Files\\Microsoft SQL Server\\MSSQL16.DB02\\MSSQL\r\n.OUTPUTS\r\n    None\r\n.NOTES\r\n    Release Notes: Initial Release\r\nBy using this script, you indicate your acceptance of the following legal terms as well as our Terms of Use at https:\/\/www.ninjaone.com\/terms-of-use.\r\n    Ownership Rights: NinjaOne owns and will continue to own all right, title, and interest in and to the script (including the copyright). NinjaOne is giving you a limited license to use the script in accordance with these legal terms. \r\n    Use Limitation: You may only use the script for your legitimate personal or internal business purposes, and you may not share the script with another party. \r\n    Republication Prohibition: Under no circumstances are you permitted to re-publish the script in any script library or website belonging to or under the control of any other software provider. \r\n    Warranty Disclaimer: The script is provided \u201cas is\u201d and \u201cas available\u201d, without warranty of any kind. NinjaOne makes no promise or guarantee that the script will be free from defects or that it will meet your specific needs or expectations. \r\n    Assumption of Risk: Your use of the script is at your own risk. You acknowledge that there are certain inherent risks in using the script, and you understand and assume each of those risks. \r\n    Waiver and Release: You will not hold NinjaOne responsible for any adverse or unintended consequences resulting from your use of the script, and you waive any legal or equitable rights or remedies you may have against NinjaOne relating to your use of the script. \r\n    EULA: If you are a NinjaOne customer, your use of the script is subject to the End User License Agreement applicable to you (EULA).\r\n#>\r\n\r\n[CmdletBinding()]\r\nparam (\r\n    [String]$CustomFieldName,\r\n    [String]$WysiwygCustomFieldName\r\n)\r\n\r\nbegin {\r\n    function Test-IsElevated {\r\n        $id = [System.Security.Principal.WindowsIdentity]::GetCurrent()\r\n        $p = New-Object System.Security.Principal.WindowsPrincipal($id)\r\n        $p.IsInRole([System.Security.Principal.WindowsBuiltInRole]::Administrator)\r\n    }\r\n    function Set-NinjaProperty {\r\n        [CmdletBinding()]\r\n        Param(\r\n            [Parameter(Mandatory = $True)]\r\n            [String]$Name,\r\n            [Parameter()]\r\n            [String]$Type,\r\n            [Parameter(Mandatory = $True, ValueFromPipeline = $True)]\r\n            $Value,\r\n            [Parameter()]\r\n            [String]$DocumentName\r\n        )\r\n    \r\n        $Characters = $Value | Measure-Object -Character | Select-Object -ExpandProperty Characters\r\n        if ($Characters -ge 10000) {\r\n            throw [System.ArgumentOutOfRangeException]::New(\"Character limit exceeded, value is greater than 10,000 characters.\")\r\n        }\r\n        \r\n        # If we're requested to set the field value for a Ninja document we'll specify it here.\r\n        $DocumentationParams = @{}\r\n        if ($DocumentName) { $DocumentationParams[\"DocumentName\"] = $DocumentName }\r\n        \r\n        # This is a list of valid fields that can be set. If no type is given, it will be assumed that the input doesn't need to be changed.\r\n        $ValidFields = \"Attachment\", \"Checkbox\", \"Date\", \"Date or Date Time\", \"Decimal\", \"Dropdown\", \"Email\", \"Integer\", \"IP Address\", \"MultiLine\", \"MultiSelect\", \"Phone\", \"Secure\", \"Text\", \"Time\", \"URL\", \"WYSIWYG\"\r\n        if ($Type -and $ValidFields -notcontains $Type) { Write-Warning \"$Type is an invalid type! Please check here for valid types. https:\/\/ninjarmm.zendesk.com\/hc\/en-us\/articles\/16973443979789-Command-Line-Interface-CLI-Supported-Fields-and-Functionality\" }\r\n        \r\n        # The field below requires additional information to be set\r\n        $NeedsOptions = \"Dropdown\"\r\n        if ($DocumentName) {\r\n            if ($NeedsOptions -contains $Type) {\r\n                # We'll redirect the error output to the success stream to make it easier to error out if nothing was found or something else went wrong.\r\n                $NinjaPropertyOptions = Ninja-Property-Docs-Options -AttributeName $Name @DocumentationParams 2>&1\r\n            }\r\n        }\r\n        else {\r\n            if ($NeedsOptions -contains $Type) {\r\n                $NinjaPropertyOptions = Ninja-Property-Options -Name $Name 2>&1\r\n            }\r\n        }\r\n        \r\n        # If an error is received it will have an exception property, the function will exit with that error information.\r\n        if ($NinjaPropertyOptions.Exception) { throw $NinjaPropertyOptions }\r\n        \r\n        # The below type's require values not typically given in order to be set. The below code will convert whatever we're given into a format ninjarmm-cli supports.\r\n        switch ($Type) {\r\n            \"Checkbox\" {\r\n                # While it's highly likely we were given a value like \"True\" or a boolean datatype it's better to be safe than sorry.\r\n                $NinjaValue = [System.Convert]::ToBoolean($Value)\r\n            }\r\n            \"Date or Date Time\" {\r\n                # Ninjarmm-cli expects the GUID of the option to be selected. Therefore, the given value will be matched with a GUID.\r\n                $Date = (Get-Date $Value).ToUniversalTime()\r\n                $TimeSpan = New-TimeSpan (Get-Date \"1970-01-01 00:00:00\") $Date\r\n                $NinjaValue = $TimeSpan.TotalSeconds\r\n            }\r\n            \"Dropdown\" {\r\n                # Ninjarmm-cli is expecting the guid of the option we're trying to select. So we'll match up the value we were given with a guid.\r\n                $Options = $NinjaPropertyOptions -replace '=', ',' | ConvertFrom-Csv -Header \"GUID\", \"Name\"\r\n                $Selection = $Options | Where-Object { $_.Name -eq $Value } | Select-Object -ExpandProperty GUID\r\n        \r\n                if (-not $Selection) {\r\n                    throw [System.ArgumentOutOfRangeException]::New(\"Value is not present in dropdown\")\r\n                }\r\n        \r\n                $NinjaValue = $Selection\r\n            }\r\n            default {\r\n                # All the other types shouldn't require additional work on the input.\r\n                $NinjaValue = $Value\r\n            }\r\n        }\r\n        \r\n        # We'll need to set the field differently depending on if its a field in a Ninja Document or not.\r\n        if ($DocumentName) {\r\n            $CustomField = Ninja-Property-Docs-Set -AttributeName $Name -AttributeValue $NinjaValue @DocumentationParams 2>&1\r\n        }\r\n        else {\r\n            $CustomField = Ninja-Property-Set -Name $Name -Value $NinjaValue 2>&1\r\n        }\r\n        \r\n        if ($CustomField.Exception) {\r\n            throw $CustomField\r\n        }\r\n    }\r\n    if ($env:multilineCustomFieldName -and $env:multilineCustomFieldName -notlike \"null\") {\r\n        $CustomFieldName = $env:multilineCustomFieldName\r\n    }\r\n    if ($env:WysiwygCustomFieldName -and $env:WysiwygCustomFieldName -notlike \"null\") {\r\n        $WysiwygCustomFieldName = $env:WysiwygCustomFieldName\r\n    }\r\n}\r\nprocess {\r\n    if (-not (Test-IsElevated)) {\r\n        Write-Error -Message \"Access Denied. Please run with Administrator privileges.\"\r\n        exit 1\r\n    }\r\n\r\n    try {\r\n        $InstanceNames = $(Get-ItemProperty -Path \"HKLM:\\SOFTWARE\\Microsoft\\Microsoft SQL Server\\\" -ErrorAction Stop).InstalledInstances\r\n        $SqlInstances = $InstanceNames | ForEach-Object {\r\n            $SqlPath = $(Get-ItemProperty -Path \"HKLM:\\SOFTWARE\\Microsoft\\Microsoft SQL Server\\$_\\Setup\" -ErrorAction Stop).SQLPath\r\n            $SqlServices = Get-Service -Name \"MSSQL`$$_\" -ErrorAction Stop\r\n            $SqlService = $SqlServices | Where-Object { $_.Name -notlike $SqlServices.DependentServices.Name -and $_.Name -notlike \"SQLTelemetry*\" }\r\n            [PSCustomObject]@{\r\n                Status   = $SqlService.Status\r\n                Service  = $SqlService.DisplayName\r\n                Instance = $_\r\n                Path     = $SqlPath\r\n            }\r\n        }\r\n    }\r\n    catch {\r\n        Write-Host \"[Error] $($_.Message)\"\r\n        Write-Host \"[Info] Likely no MSSQL instance found.\"\r\n        exit 1\r\n    }\r\n\r\n    $SqlInstances | Out-String | Write-Host\r\n\r\n    if ($CustomFieldName) {\r\n        Write-Host \"Attempting to set Custom Field '$CustomFieldName'.\"\r\n        Set-NinjaProperty -Name $CustomFieldName -Value ($SqlInstances | Out-String)\r\n        Write-Host \"Successfully set Custom Field '$CustomFieldName'!\"\r\n    }\r\n\r\n    if ($WysiwygCustomFieldName) {\r\n        try {\r\n            Write-Host \"Attempting to set Custom Field '$WysiwygCustomFieldName'.\"\r\n            $htmlReport = New-Object System.Collections.Generic.List[String]\r\n            $htmlReport.Add(\"<h1>SQL Server Instances<\/h1>\")\r\n            $htmlTable = $SqlInstances | ConvertTo-Html -Fragment \r\n            $htmlTable = $htmlTable -replace \"<tr><td>Running<\/td>\", '<tr class=\"success\"><td>Running<\/td>'\r\n            $htmlTable = $htmlTable -replace \"<tr><td>StartPending<\/td>\", '<tr class=\"other\"><td>StartPending<\/td>'\r\n            $htmlTable = $htmlTable -replace \"<tr><td>ContinuePending<\/td>\", '<tr class=\"other\"><td>ContinuePending<\/td>'\r\n            $htmlTable = $htmlTable -replace \"<tr><td>Paused<\/td>\", '<tr class=\"other\"><td>Paused<\/td>'\r\n            $htmlTable = $htmlTable -replace \"<tr><td>PausePending<\/td>\", '<tr class=\"other\"><td>PausePending<\/td>'\r\n            $htmlTable = $htmlTable -replace \"<tr><td>Stopped<\/td>\", '<tr class=\"danger\"><td>Stopped<\/td>'\r\n            $htmlTable = $htmlTable -replace \"<tr><td>StopPending<\/td>\", '<tr class=\"danger\"><td>StopPending<\/td>'\r\n            $htmlTable | ForEach-Object { $htmlReport.Add($_) }\r\n            Set-NinjaProperty -Name $WysiwygCustomFieldName -Value ($htmlReport | Out-String)\r\n            Write-Host \"Successfully set Custom Field '$WysiwygCustomFieldName'!\"\r\n        }\r\n        catch {\r\n            Write-Error $_\r\n            Write-Host \"[Error] $($_.Message)\"\r\n            exit 1\r\n        }\r\n    }\r\n    exit 0\r\n}\r\nend {\r\n    \r\n    \r\n    \r\n}<\/pre>\n
 <\/p>\n\n
<\/h2>
<\/p><\/div>
<\/a><\/div><\/div>\n
Description d\u00e9taill\u00e9e<\/h2>\n
Le script commence par v\u00e9rifier s’il s’ex\u00e9cute avec des privil\u00e8ges d’administrateur, ce qui est n\u00e9cessaire pour acc\u00e9der \u00e0 certaines propri\u00e9t\u00e9s du syst\u00e8me. Il d\u00e9finit une fonction, Test-IsElevated, pour le v\u00e9rifier. Si le script n’est pas ex\u00e9cut\u00e9 en tant qu’administrateur, il se termine par une erreur.<\/p>\n
Ensuite, il tente de r\u00e9cup\u00e9rer les instances SQL Server install\u00e9es dans le registre Windows<\/a>. Pour ce faire, la cmdlet Get-ItemProperty permet d’acc\u00e9der au chemin d’acc\u00e8s au registre concern\u00e9. Le script parcourt ensuite chaque instance, en r\u00e9cup\u00e9rant des informations telles que le nom de l’instance, l’\u00e9tat du service et le chemin d’installation.<\/p>\n
Les informations recueillies sont structur\u00e9es dans un objet personnalis\u00e9 et affich\u00e9es. S’ils sont sp\u00e9cifi\u00e9s, les r\u00e9sultats peuvent \u00e9galement \u00eatre enregistr\u00e9s dans des champs personnalis\u00e9s, sous forme de texte brut ou de code HTML, \u00e0 l’aide de la fonction Set-NinjaProperty. Cette fonction traite diff\u00e9rents types de donn\u00e9es et veille \u00e0 ce que les valeurs soient conformes aux limites de caract\u00e8res et \u00e0 d’autres contraintes.<\/p>\n
Cas d’utilisation potentiels<\/h2>\n
Prenons l’exemple d’un professionnel de l’informatique qui g\u00e8re plusieurs serveurs au sein d’une entreprise. En utilisant ce script, il peut rapidement rassembler et documenter toutes les instances SQL Server fonctionnant sur chaque serveur. Ces informations sont essentielles pour des t\u00e2ches telles que la mise \u00e0 jour des logiciels, la r\u00e9alisation d’audits de s\u00e9curit\u00e9 et la planification des capacit\u00e9s. Par exemple, lors d’un cycle de mise \u00e0 niveau, le fait de conna\u00eetre toutes les instances actives permet de planifier et de minimiser les temps d’arr\u00eat.<\/p>\n
Comparaisons<\/h2>\n
Ce script PowerShell offre un moyen rationalis\u00e9 et automatis\u00e9 de collecter des informations sur les instances de serveur SQL par rapport aux m\u00e9thodes manuelles. Traditionnellement, les administrateurs peuvent utiliser SQL Server Management Studio (SSMS) ou v\u00e9rifier manuellement les services via la console Windows Services. Bien qu’efficaces, ces m\u00e9thodes prennent du temps et sont sujettes \u00e0 des erreurs humaines<\/a>. En revanche, le script fournit un processus coh\u00e9rent et reproductible qui peut \u00eatre facilement int\u00e9gr\u00e9 dans des flux de travail plus larges de gestion des technologies de l’information.<\/p>\n
FAQ<\/h2>\n
Q\u00a0: Ce script peut-il d\u00e9tecter les instances SQL Server install\u00e9es dans le cadre d’une application ?<\/h3>\n
R\u00a0: Non, le script est con\u00e7u pour d\u00e9tecter les instances standard du serveur SQL. Les instances int\u00e9gr\u00e9es dans les applications ne sont pas prises en charge.<\/p>\n
Q\u00a0: Que se passe-t-il si le nom d’un champ personnalis\u00e9 d\u00e9passe la limite de caract\u00e8res ?<\/h3>\n
R\u00a0: Le script comprend une v\u00e9rification pour s’assurer que les valeurs ne d\u00e9passent pas 10 000 caract\u00e8res. Si cette limite est d\u00e9pass\u00e9e, une erreur est g\u00e9n\u00e9r\u00e9e.<\/p>\n
Q\u00a0: Ce script peut-il \u00eatre ex\u00e9cut\u00e9 avec n’importe quelle version de PowerShell ?<\/h3>\n
R\u00a0: Le script n\u00e9cessite la version 5.1 ou plus r\u00e9cente de PowerShell.<\/p>\n
Implications<\/h2>\n
L’ex\u00e9cution de ce script permet aux professionnels de l’informatique d’obtenir des informations d\u00e9taill\u00e9es sur les d\u00e9ploiements de serveurs SQL dans leur infrastructure. Ces informations sont essentielles pour garantir la conformit\u00e9, optimiser les performances et renforcer la s\u00e9curit\u00e9. En sachant exactement o\u00f9 tournent les instances SQL Server, les administrateurs peuvent mieux prot\u00e9ger les donn\u00e9es sensibles et s’assurer que les syst\u00e8mes sont correctement configur\u00e9s et entretenus.<\/p>\n
Recommandations<\/h2>\n