Conectando PowerShell a un Servidor SQL Server

Introducción

PowerShell (también llamado Posh o simplemente PS) cada día se vuelve con más fuerza una herramienta de soporte popular para el sistema operativo y en algunos casos para la implementación. Si usted necesita extraer o insertar los datos en SQL Server, PS resulta ser la manera más práctica de lograrlo de forma automatizada y de una sola vez.

Hay distintas formas de poder conectarse a SQL Server utilizando PS. En este artículo, lo que deseo es repasar todas las opciones disponibles existentes para usted. Si usted utilizó PS durante muchos meses o años, debe saber que existen múltiples formas de realizar una tarea. Por lo tanto, no es necesario mencionar que está creando scripts para usarlos en producción para probarlo, probarlo y probarlo… y luego probarlo una vez más.

Lista de Opciones

En resumen, la siguiente lista de opciones será examinada en el presente artículo:

SQL Server PowerShell (SQLPS)
Objetos de administración de SQL Server (SMO)
.NET (System.Data.SqlClient):

SQL Server PowerShell

SQLPS es un utilitario que se incluyó la primera vez en SQL Server 2008, y se puede acceder al mismo de diferentes formas. Se lo usa como (1) utilitario y (2) como un módulo de PS. El utilitario y el módulo se instalan junto con las herramientas de SQL Server Management de SQL Server del 2008 o superior. Existen pocas maneras de conectarse a SQL Server utilizando este utilitario, y cada una de estas tiene sus fortalezas y debilidades.

SQLPS.exe

Este es un utilitario que se debería poder abrir escribiendo en el comando de ejecución (Inicio> Ejecutar). La segunda opción para lograrlo es hacer con un clic derecho en un nodo en el Explorardor de objetos, dentro del SQL Server Management Studio (SSMS), y seleccionar “Iniciar PowerShell”. El principal punto de acceso del utilitario SQLPS es utilizar el proveedor “SQLSERVER: \” para poder examinar el SQL Server como una carpeta de archivos. De esta manera, iniciando en este nodo desde el cual se abre el SQLPS, lo ubicará dentro de la ruta del proveedor. Debajo de cada una de estas “carpetas” usted se encontrará aprobado para que el proveedor ofrezca las propiedades para poder leer o establecer algunos métodos para usar y administrar.

Una cosa que debe tener en cuenta al usar este utilitario es que la versión de PS con la que usted opera es la 2.0. La cual es ya una debilidad obvia. Por ejemplo, yo uso en el sistema operativo Windows 10 con PS en la versión 5.0 y SQL Server 2012:

Es muy importante tenerlo en cuenta debido a que un cmdlet (se pronuncia comand-let) en versión 5.0 puede no estar presente en SQLPS.exe o pueden faltar las nuevas características que se incorporaron.

Este método que se utiliza para acceder a una instancia de SQL Server es útil para esas situaciones en las que se requiere una sola línea, pero hay que mencionar que SQLPS es bastante lento para trabajar a veces, así que no se apresure demasiado. Como un ejemplo, de uso común de una sola línea para la utilidad SQLPS, voy a refrescar el entorno de desarrollo y solo quiero configurar todas las bases de datos en modo de recuperación SIMPLE:

1	Get-ChildItem SQLSERVER:\SQL\LOCALHOST\SQL12\Databases \| foreach { $_.RecoveryModel = “SIMPLE”; $_.Alter() }

Módulo de SQLPS

Cuando se importa el módulo SQLPS en la sesión PS, se proporciona el mismo acceso usando el utilitario de igual forma, pero le permite operar en la versión PS del sistema operativo que está usando. En SQL Server 2008 y también en 2008 R2 se cargará al SQLPS como un complemento (Add-PSSnapin), y luego con SQL Server 2012 y versiones superiores que son importados (Import-Module).

Cuando se importe el módulo, se cargará el proveedor SQLSERVER: \ y también se cambiará su ubicación a esa misma ruta. También se tendrá acceso a todos los cmdlets que se ofrecen en este módulo, a los que también se puede acceder a través del utilitario. Es posible obtener una lista de todos los cmdlets utilizando el comando Get-Command:

El cmdlet más conocido o usado en este módulo es el Invoke-Sqlcmd. De manera general, se lo considera como un reemplazo del PS de la antigua utilidad de línea de comandos sqlcmd, que hasta el momento, todavía se encuentra disponible en todas las versiones soportadas de SQL Server. Es aún utilizado este cmdlet para poder ejecutar cualquier consulta T-SQL que desee en una o múltiples instancias. La gran ventaja que se tiene al usar el Invoke-Sqlcmd sobre la utilidad de la línea de comando sqlcmd, es la capacidad de manejar la salida en PS. Los resultados del cmdlet se crean como una DataTable (System.Data.DataRow que es el tipo exacto de dato).

Solo mandando el comando completo a Get-Member se podrá mostrar el TypeName:

También se podrá ver en el resultado tus columnas de tu instrucción SELECT, que aparecerán como propiedades en esa matriz. Puede aprovechar de las diferentes conversiones de datos que están disponibles en PS, como por ejemplo ConvertTo-Csv, o también ConvertTo-HTML si se pueden utilizar para generar un informe HTML.

Objetos de administración de SQL Server (SMO)

SMO es un dolor de cabeza para algunos, pero una vez que se aprende a investigar con los nombres de espacios de SMO en MSDN, sus ojos se abrirán a un mundo de posibilidades. SMO se utiliza comúnmente para poder realizar diferentes tareas de administración en las instancias de SQL Server. Puede utilizarse para verificar la instancia de configuración donde puede realizar comparaciones de la configuración con respecto a la configuración estándar o para situaciones en las que se requiere una auditoría. Incluye cierta flexibilidad en la ejecución de sentencias T-SQL equivalente utilizando Invoke-Sqlcmd, si hay una opción a través de T-SQL.

Es posible también ejecutar T-SQL a utilizando SMO si es usted lo requiere y si existe algún beneficio al usar este método en vez de utilizar .NET. El beneficio que se obtiene es la ejecución de la sentencia T-SQL que pueden incluir un separador de grupos “GO”. Utilizar el método .NET haría que se produzca un error al conectar el primer “Go” al realizar una secuencia de comandos, debido a que no es la verdadera sintaxis de SQL. Puede ser útil en implementaciones donde alguien haya creado los objetos a través de SSMS, que se podrán incluir en la instrucción “GO”. Si se tiene un script largo, o un archivo comprimido que contiene cientos de secuencias de comandos, el uso de SMO puede ahorrar un tremendo dolor de cabeza.

Cargando SMO

Para poder utilizar SMO, primero se debe cargar los ensamblados SMO en su sesión de PowerShell. Esto se puede hacer simplemente al cargar el módulo completo de SQLPS, debido a que eso cargará automáticamente los ensamblados. Otro método, que es más común en los artículos en línea, consisten en agregar el ensamblado usando System.Reflection.Assembly : LoadWithPartialName (). Este es un método más apropiado para cargar SMO, a partir de la versión PS 3.0, donde se utiliza el complemento de cmdlet Add-Type.

1	Add-Type -AssemblyName "Microsoft.SqlServer.Smo,Version=11.0.0.0,Culture=neutral,PublicKeyToken=89845dcd8080cc91"

Conectándose con SMO

Luego de cargarse el ensamblado, usted crea un objeto para la instancia de SQL Server con la cual se desea trabajar:

1 2	$srv = New-Object Microsoft.SqlServer.Management.Smo.Server “localhost\sql12” $srv.Databases \| select name

Luego tendrá que determinar la tarea que se desea realizar y poder encontrar la clase de objetos y métodos necesaria. Obviamente, este no es el método utilizado para realizar una tarea de manera rápida (al menos mientras se familiariza con el funcionamiento de SMO).

.NET Framework

PS se basa en .NET, por lo que se tiene un acceso a esa estructura que se puede usar para realizar trabajos en lugar del SQL Server. Se la utiliza más comúnmente para trabajar con los datos en lugar de realizar tareas administrativas. También le puedo adelantar que la herramienta requiere un poco más de taipeo para poder utilizarla, por lo que no es una opción para los que les gusta utilizar sola línea de comandos.

Existe sin embargo una ventaja que se puede lograr al utilizar esta herramienta en vez de las mencionados anteriormente. Esta ventaja es la portabilidad. Usted encontrará .NET en cualquier sistema operativo que utilice Windows, por lo cual, no existe una dependencia con las herramientas de SQL Server ya instaladas, al menos en la máquina en la que cual se ejecuta el script. No obstante, la versión de .NET puede llegar a depender de la versión que existe en el sistema operativo en el cual se ejecute, así que deberá asegurarse de poder realizar pruebas antes de poder migrar el script a producción.

También existen actualmente varias maneras de hacerlo, pero, en general, verá que la mayoría de los ejemplos crean los siguientes objetos para poder conectarse a una instancia de SQL Server:

Crear una conexión
Crear su comando (el cual T-SQL se ejecutará)
Crear un adaptador de datos (si desea recuperar datos)
Crear un conjunto de datos (donde el adaptador llena este objeto)

Crear una conexión

Simplemente se crea un objeto del tipo System.Data.SqlClient.SqlConnection y se manda la cadena de conexión a utilizar para poder conectarse a la instancia de SQL Server determinada… no olvide que debe abrirla.

$sqlConn = New-Object System.Data.SqlClient.SqlConnection

$sqlConn.ConnectionString = “Server=localhost\sql12;Integrated Security=true;Initial Catalog=master”

$sqlConn.Open()

Crear su comando

A continuación, se muestran algunas opciones debido a que SqlConnection tiene un método que puede ser utilizado para crear su comando objeto, o es posible crear un objeto separado con todo en uno. He visto utilizar las dos opciones, por lo que cualquiera de las dos opciones depende de su preferencia.

$sqlcmd = $sqlConn.CreateCommand()

<# or #>

$sqlcmd = New-Object System.Data.SqlClient.SqlCommand

$sqlcmd.Connection = $sqlConn

$query = “SELECT name, database_id FROM sys.databases”

$sqlcmd.CommandText = $query

Crear su adaptador de datos

Por definición, el objeto “representa un conjunto de comandos de datos y una conexión a la base de datos que se utilizan para poder llenar el DataSet”. Se crea el SqlDataAdapter y lo pasa a un objeto de comando anterior creado, $sqlcmd.

1	$adp = New-Object System.Data.SqlClient.SqlDataAdapter $sqlcmd

Crear su DataSet (y llénalo de datos)

Este objeto es del tipo System.Data.DataSet y, tal como se define, es solamente “un caché de datos en memoria”. Esto es algo para tener en cuenta. La consulta que se está ejecutando se cargará en memoria, por lo que, a mayor conjunto de datos, mayor cantidad de memoria será requerida.

1 2	$data = New-Object System.Data.DataSet $adp.Fill($data) \| Out-Null

Esta recomendación es algo que personalmente prefiero hacer, pero cuando utilizo el método “Rellenar” canalizo esto a Out-Null. Eso debido a que este método podrá generar la mayor cantidad de registros que fueron llenados. Si quiere lograr este resultado, simplemente debe eliminar Out-Null..

Recuperando sus datos

Una vez realizados estos pasos, es muy probable que se pregunte de qué manera generará esos datos para poder verlos. Los datos se ubican en una colección de tablas dentro de la propiedad Tabla en el objeto objeto DataSet. Por lo tanto, dependiendo de la versión que tenga de .NET, se necesitará especificar el índice de la colección (por ejemplo, Tablas [0]), pero esto normalmente solo se necesita en versiones anteriores a la versión .NET 4.0.

$data.Tables

<# or #>

$data.Tables[0]

Si el procedimiento o el script T-SQL que se ejecuta contiene más de un conjunto de datos, esta colección solo contendrá el primer conjunto de resultados devuelto por SQL Server.

Una vista de todo el comando:

Por último, jamás debe olvidar en cerrar la conexión siguiendo las buenas prácticas de programación. Una vez que se termine de utilizar la conexión, simplemente utilice el método Close () para poder cerrar la conexión a la instancia de SQL Server.

Resumen

Utilizando las opciones anteriores, espero que usted tenga mayores conocimientos ahora de las opciones para poder lograr acceder a SQL Server utilizando PowerShell. Sea cual fuera el método que usted elija depende de la acción o el procesamiento requerido. Se pueden buscar secuencias de comandos para poder lograr un proceso repetible o simplemente se necesita un proceso que contenga una sola línea (one-liner) para un proceso único.

El próximo artículo de esta serie es el siguiente:

Connecting PowerShell to SQL Server – Using a Different Account

Shawn Melton

Shawn Melton es un consultor de SQL Server en Pythian, una compañía global de servicios de IT basada en Ottawa – Canadá.
Después de pasar 6 años en el mundo de la administración de sistemas y redes, descubrió que disfrutaba aprender y trabajar con SQL Server. Desde 2010, él ha estado involucrado en SQL Server.
Él es un apasionado acerca de PowerShell y la automatización alrededor de SQL Server y Windows. Su experiencia está enfocada en la administración de SQL Server y algo de desarrollo BI (SSIS, SSRS).

Ver todas las publicaciones de Shawn Melton

Últimas entradas de Shawn Melton (ver todo)

Conectando PowerShell a SQL Server usando una cuenta diferente - May 24, 2018
Conectando PowerShell a un Servidor SQL Server - April 11, 2018
Introducción de Visual Studio Code para DBAs - February 28, 2017