{"id":502619,"date":"2025-07-31T10:01:52","date_gmt":"2025-07-31T10:01:52","guid":{"rendered":"https:\/\/www.ninjaone.com\/?post_type=script_hub&p=502619"},"modified":"2025-07-31T10:04:40","modified_gmt":"2025-07-31T10:04:40","slug":"documentar-instancias-de-sql-server","status":"publish","type":"script_hub","link":"https:\/\/www.ninjaone.com\/es\/script-hub\/documentar-instancias-de-sql-server\/","title":{"rendered":"C\u00f3mo utilizar PowerShell para descubrir y documentar instancias de SQL Server"},"content":{"rendered":"

\u00bfQuieres aprender a crear instancias de servidor <\/strong>y MSSQL con PowerShell<\/strong>? <\/span> En el mundo de las TI, la gesti\u00f3n y el mantenimiento de las instancias de SQL Server<\/a> es una tarea fundamental para los profesionales de TI y los proveedores de servicios gestionados (MSP)<\/a>. Saber c\u00f3mo recopilar informaci\u00f3n de forma eficaz sobre las instancias de SQL Server puede mejorar significativamente las tareas administrativas y agilizar los procesos. Este post profundiza en un potente script de PowerShell dise\u00f1ado para obtener una lista de instancias de servidor MSSQL, proporcionando informaci\u00f3n detallada y aplicaciones pr\u00e1cticas.<\/p>\n

Contexto<\/h2>\n

Las instancias de SQL Server son el eje central de muchas aplicaciones empresariales, ya que almacenan y gestionan grandes cantidades de datos. Los profesionales de TI a menudo necesitan identificar y gestionar estas instancias para garantizar un rendimiento y una seguridad \u00f3ptimos. El script PowerShell proporcionado es una herramienta muy valiosa para recuperar r\u00e1pidamente informaci\u00f3n sobre las instancias de SQL Server en un sistema. Esta capacidad es crucial para tareas como auditor\u00edas, resoluci\u00f3n de problemas y supervisi\u00f3n del sistema.<\/p>\n

El script<\/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
Accede a m\u00e1s de 300 scripts en el Dojo de NinjaOne<\/p>\n
Obt\u00e9n acceso<\/a><\/p>\n<\/div>\n
An\u00e1lisis detallado<\/h2>\n
El script comienza comprobando si se ejecuta con privilegios de administrador, necesarios para acceder a ciertas propiedades del sistema. Define una funci\u00f3n, Test-IsElevated, para comprobarlo. Si el script no se ejecuta como administrador, sale con un error.<\/p>\n
A continuaci\u00f3n, intenta recuperar del Registro de Windows<\/a> las instancias de SQL Server instaladas. Esto se consigue mediante el cmdlet Get-ItemProperty, que accede a la ruta de registro correspondiente. A continuaci\u00f3n, el script itera a trav\u00e9s de cada instancia, obteniendo detalles como el nombre de la instancia, el estado del servicio y la ruta de instalaci\u00f3n.<\/p>\n
La informaci\u00f3n recopilada se estructura en un objeto personalizado y es visible. Si se especifica, los resultados tambi\u00e9n pueden guardarse en campos personalizados, como texto sin formato o HTML, utilizando la funci\u00f3n Set-NinjaProperty. Esta funci\u00f3n maneja varios tipos de datos y garantiza que los valores se ajusten a los l\u00edmites de caracteres y otras restricciones.<\/p>\n
Posibles casos de uso<\/h2>\n
Piensa en un profesional de TI que gestiona varios servidores en una organizaci\u00f3n. Utilizando este script, pueden reunir y documentar r\u00e1pidamente todas las instancias de SQL Server que se ejecutan en cada servidor. Esta informaci\u00f3n es vital para tareas como actualizar el software, realizar auditor\u00edas de seguridad y planificar la capacidad. Por ejemplo, durante un ciclo de actualizaci\u00f3n, conocer todas las instancias activas ayuda a programar y minimizar el tiempo de inactividad.<\/p>\n
Comparaciones<\/h2>\n
Este script de PowerShell ofrece una forma simplificada y automatizada de recopilar informaci\u00f3n sobre instancias de SQL Server en comparaci\u00f3n con los m\u00e9todos manuales. Tradicionalmente, los administradores pod\u00edan utilizar SQL Server Management Studio (SSMS) o comprobar manualmente los servicios a trav\u00e9s de la consola Servicios de Windows. Aunque eficaces, estos m\u00e9todos requieren mucho tiempo y son propensos a errores humanos<\/a>. En cambio, el script proporciona un proceso coherente y repetible que puede integrarse f\u00e1cilmente en flujos de trabajo de gesti\u00f3n de TI m\u00e1s amplios.<\/p>\n
FAQ<\/h2>\n
P: \u00bfPuede este script detectar instancias de SQL Server instaladas como parte de una aplicaci\u00f3n?<\/h3>\n
R: No, el script est\u00e1 dise\u00f1ado para detectar instancias est\u00e1ndar de SQL Server. No se admiten instancias incrustadas dentro de aplicaciones.<\/p>\n
P: \u00bfQu\u00e9 ocurre si el nombre de un campo personalizado supera el l\u00edmite de caracteres?<\/h3>\n
R: El script incluye una comprobaci\u00f3n para garantizar que los valores no superan los 10.000 caracteres. Si se supera este l\u00edmite, se produce un error.<\/p>\n
P: \u00bfSe puede ejecutar este script en cualquier versi\u00f3n de PowerShell?<\/h3>\n
R: El script requiere PowerShell versi\u00f3n 5.1 o superior.<\/p>\n
Implicaciones<\/h2>\n
La ejecuci\u00f3n de este script proporciona a los profesionales de TI informaci\u00f3n detallada sobre las implantaciones de SQL Server en su infraestructura. Esta informaci\u00f3n es fundamental para garantizar el cumplimiento de la normativa, optimizar el rendimiento y mejorar la seguridad. Al saber exactamente d\u00f3nde se ejecutan las instancias de SQL Server, los administradores pueden proteger mejor los datos confidenciales y asegurarse de que los sistemas est\u00e1n correctamente configurados y mantenidos.<\/p>\n
Recomendaciones<\/h2>\n