miércoles, 10 de octubre de 2012

Modificar puertos TCP de SQL Server 2012 con Powershell


Introducción

Con SQL Server 2012 se ha incorporado el módulo SQLPS para facilitarnos las tareas de administración de nuestro servidor SQL. Antes de esta versión nos veíamos obligados a cargar ensamblados como Microsoft.SqlServer.Management.Smo.Wmi.ManagedComputer y utilizar URNs para cargar los objetos que son necesarios para realizar este tipo de cambios a nivel de instancia. Aqui teneis algunos ejemplos de como se hace cargando ensamblados:


Gracias al módulo SQLPS se ha facilitado enormemente el acceso a estos objetos.
En esta entrada vamos a desarrollar un script Powershell utilizando SQLPS para realizar el cambio de puertos a una determinada instancia de SQL Server (motor relacional) de forma que podamos ejecutarlo de forma reiterativa y evitando así utilizar interfaz gráfico.

Generando el Script

Requisitos

A parte de contar con Powershell 2.0 en nuestro sistema, los requisitos para hacer funcionar el script son los siguientes
  • Políticas de ejecución: Durante el desarrollo del script me he encontrado algunos problemas al acceder a determinadas colleciones al tener las políticas de ejecución de Powershell algo restringidas. En concreto estaba trabajando con la política RemoteSigned. Para lograr ejecutar correctamente este script tuve que modificar las políticas de ejecución a Unrestricted utilizando el siguiente comando. Puedes encontrar más ayuda sobre las políticas de ejecución buscando about_execution_policies
set-executionPolicy Unrestricted -scope CurrentUser; get-executionPolicy


  • SQL Server 2012: Como hemos mencionado antes, el módulo SQLPS se incorpora a esta versión de SQL Server. Anteriormente se utilizaba el comando SQLPS.exe pero se verá depreciado en futuras versiones



Desarrollo del script


Lo primero que necesitaremos es cargar el modulo SQLPS y esto lo conseguiremos a través del comando import-module:
import-module sqlps

Obviando el mensaje de advertencia, lo primero que podemos fijarnos es que nos encontramos en la unidad (PSDrive) SQLSERVER:\ lo cual nos permite navegar objetos de SQL Server como si se tratara de una carpeta de archivos.

Si ejecutamos el comando dir (un alias de get-childItem) nos encontraremos con los siguientes objetos:

image

Si ejecutamos el comando dir sql | gm obtendremos una listas de los miembros que conforman el objeto, además de conocer que tipo de objeto es cada propiedad:

image

Todo lo que necesitamos para hacer el cambio de puertos a nuestra instancia (o instancias) SQL server se encuentran bajo la propiedad ManagedComputer, que como podeis es un objeto de la clase Microsoft.SqlServer.Management.Smo.Wmi.ManagedComputer. La misma que se utiliza para realizar estas operaciones Powershell antes de la existencia del módulo SQLPS.

Sabiendo esto, vamos a ponernos manos a la obra. Vamos a crearnos una variable que contenga la máquina (host) y otra para las instancias que tiene registradas el host.
$SQLHost = get-childItem "SQLSERVER:\SQL\"
$Instances = $SQLHost.ManagedComputer.ServerInstances
Es posible que tengamos más de una instancia instalada, por lo que sería conveniente solicitar el nombre antes de continuar. Si sólo tenemos una nos ahorramos el paso y seguimos.
    if ($Instances.count -eq 1) {
        $Instance = $Instances[0]
        [string]$InstanceName = $Instance.name
    }
    elseif ($Instances.count -gt 1) {
        Write-host "Se han detectado " $Instances.count " :"
        $Instances.Name
        write-host "Introduzca el nombre de alguna de las siguientes instancias :" -NoNewline
        [string]$InstanceName = read-host
        $Instance = $Instances | where {$_.Name -ilike $InstanceName}
    }
    if ($Instance -eq $Null){
        Throw "No se encontraron instancias"
    }

Como habreis detectado, ya tenemos la instancia con la que vamos a trabajar en el objeto $Instance. ¿Y que miembros contiene este objecto? El que nos interesa es la propiedad ServersProtocols

image

Esta propiedad nos devuelve la colección de protocolos disponibles para la instancia, entre ellos el protocolo “tcp” (TCP/IP) que es el que vamos a utilizar para cambiar el puerto de escucha de las IPs que maneja. En Powershell 3.0 el forEach sobre colecciones se realiza de forma automatica escribiendo el miembro, pero vamos a ceñirnos a lo clásico. Ojo que el ForEach se queda abierto:
forEach ($p in $Instance.ServerProtocols | where {$_.Name -ilike "tcp"}){
        #Si el protocolo TCP se encuentra deshabilitado se habilita.
        If ($p.IsEnabled -eq $false){
            Write-Host ("[",$Instance.name,"] Habilitando protocolo ",$p.DisplayName,"...." -join "") -NoNewline -fore Green
            $p.IsEnabled = $true
        }

Recorremos los protocolos, aunque en realidad estamos filtrando de inicio por el que se denomina “tcp”, y en caso de encontrarlo comprobamos si se encuentra habilitado y, si no lo está, lo habilitamos $p.IsEnabled = $true.

El forEach se ha quedado abierto (falta un braket }) porque aún vamos a realizar acciones sobre el objeto actual $p (protocolo tcp). Hay que recorrer todas las direcciones IPs que tiene configuradas para cambiar las propiedades TcpDynamicPorts y TcpPorts. La primera la vamos a dejar vacía para deshabilitar la asignación dinámica de puertos y en la segunda vamos a establecer el puerto que queremos configurar:
        forEach ($IpAddress in $p.IpAddresses){
            write-host (("--> Modificando el puerto para la IP [", $IPAddress.Name, "] ", $IpAddress.IpAddress) -join "") -fore magenta
            forEach ($IpAddressProperty in $IpAddress.IpAddressProperties){
                switch ($IpAddressProperty.Name) {
                    "Active"{
                        write-host ("     Activo: ", $IpAddressProperty.value -join "")
                    }
                    "Enabled"{
                        write-host ("     Habilitado: ", $IpAddressProperty.value -join "")
                    }
                    "TcpDynamicPorts" {
                        $IpAddressProperty.Value=""
                    }
                    "TcpPort"{
                        $IpAddressProperty.Value= [string]$NewTCPPort
                    }
                    
                }
                write-verbose $ipaddressProperty
            } #forEach $IpAdressProperty
        } #forEach $IpAddress
        $p.Alter()
    }#ForEach $p

Fijaros en la instrucción $p.Alter(), este método realiza los cambios sobre el protocolo y si no se ejecuta no habremos hecho nada.

Por último y para que el servicio refleje los cambios que hemos realizado es necesario reiniciarlo. Para esto nos volvemos al objeto $Host.ManagedComputer y nos fijamos en la colección Services. Contiene todos los servicios instalados de SQL Server (Agente SQL, Browser, Analysis Services, etc..) así que tenemos que obtener el servicio correspondiente a la instancia con la que estamos trabajando para reiniciarlo (o pararlo y volverlo a iniciar, que no tienen un método Restart()):
If ($restart -ilike "s"){
            $SQLService = $SQLHost.ManagedComputer.Services | where-object {$_.Name -ilike (("MSSQL$",$InstanceName) -join "") }
            $SQLServiceState=$SQLService.ServiceState
            #Comprueba si el servicio esta parado
            Write-Debug ("El servicio MSSQL$",$InstanceName, " se encuentra ",$SQLServiceState -join "") 
            if ($SQLServiceState -ilike "Running"){
                $SQLService.Stop()
                Write-Host ("[", $SQLService.Name, "] Deteniendo el servicio...." -join "") -NoNewline
                While ($SQLService.ServiceState -eq $SQLServiceState)
                    {
                        $SQLService.Refresh()
                        Write-Host "." -NoNewline
                        Wait-Event -Timeout 5
                    }
                $SQLServiceState=$SQLService.ServiceState
            }
            Write-Host $SQLSErvice.ServiceState -NoNewline -fore Magenta
            write-host ""

            #Si el servicio esta parado lo iniciamos.
            $SQLService.Start()
            Write-Host ("[", $SQLService.Name, "] Iniciando el servicio...." -join "") -NoNewline
            While ($SQLService.ServiceState -eq $SQLServiceState)
                {
                    $SQLService.Refresh()
                    Write-Host "." -NoNewline
                    Wait-Event -Timeout 5
                }
            Write-Host $SQLSErvice.ServiceState -NoNewline -fore Magenta
            write-host ""
        }

Y con esto habríamos conseguido modificar el puerto TCP por el que se realizarán las comuncaciones de la instancia SQL Server y hacer efectivo el cambio.

Espero que os sirva de ayuda

Podeis descargar la versión completa del script desde la Galería de TechNet

No hay comentarios:

Publicar un comentario

Entradas populares