Búsqueda de sitios web

Introducción al comando PowerShell Invoke-SQLCmd

No hay duda de que la gestión del servidor SQL en una plataforma GUI funciona muy bien. Pero si las consultas T-SQL tradicionales son más de su experiencia, le resultará agradable utilizar el cmdlet Invoke-SQLCmd de PowerShell.

El cmdlet Invoke-SqlCmd proporciona una experiencia más orientada a PowerShell para trabajar con bases de datos de SQL Server. Y en este tutorial, comenzará a administrar sus bases de datos SQL con el cmdlet Invoke-SqlCmd.

¡Sumérgete y comienza a optimizar la administración de tu base de datos SQL!

Requisitos previos

Antes de pasar al comando Invoke-SQLCmd de PowerShell, asegúrese de tener lo siguiente en su lugar para seguir este tutorial:

  • Una computadora para ejecutar PowerShell: este tutorial utiliza Windows Server 2022 con PowerShell v7.3.X.
  • Un servidor Microsoft SQL con una base de datos existente: este tutorial utiliza una edición estándar de SQL 2022 con una base de datos llamada EmployeesDB.
  • Un usuario de SQL Server con privilegios para conectarse al servidor y realizar acciones.

Instalación del módulo SqlServer

Con el cmdlet Invoke-SqlCmd, puede ejecutar consultas y scripts SQL dentro de una consola PowerShell. Como resultado, puede automatizar las tareas de administración de bases de datos e integrar SQL Server con otros comandos y módulos de PowerShell. Y sí, sin tener que cambiar entre diferentes herramientas e interfaces.

Pero al igual que otros cmdlets, para aprovechar el cmdlet Invoke-SQLCmd, debe instalar el módulo SqlServer. Este módulo admite las últimas funciones de SQL que le permiten automatizar el desarrollo de bases de datos y la administración del servidor.

1. Abra PowerShell como administrador y ejecute el siguiente comando Install-Module para descargar e instalar el módulo SQLServer desde la galería de PowerShell.

Install-Module SQLServer

Ingrese Y y presione Entrar cuando se le solicite continuar con la instalación.

2. Una vez instalado, ejecute el siguiente comando Import-Module, que no proporciona resultados, pero habilita todos los comandos relacionados con el módulo SqlServer.

Import-Module SQLServer

3. Ahora, ejecute cada comando a continuación, que no produce resultados pero establece variables que contienen datos apropiados para el nombre del servidor, el nombre de usuario y la contraseña. Hacerlo le permite optimizar su flujo de trabajo y evitar ingresar manualmente datos de uso común cuando sea necesario.

El uso consistente de estas variables en sus scripts y comandos ahorra tiempo y reduce los errores causados por errores tipográficos u otros errores.

# Set the SQL server name
$serverName = "mssql-vm"
# Set the SQL server credentials
$adminSqlLogin = "vmsilvamolina"
$password = "S3cur3P4ss@1"

Quizás planee que estas variables persistan. Si es así, configúrelas como variables de entorno a las que pueda llamar cuando sea necesario.

Conexión a un servidor SQL a través de PowerShell Invoke-SQLCmd

Con el módulo SqlServer instalado, está listo para comenzar con el cmdlet Invoke-SQLCmd de PowerShell. Este cmdlet le permite conectarse a su servidor SQL y ejecutar una consulta.

Antes de poder consultar su servidor SQL, asegúrese de tener a mano las variables que configuró en el último paso de la sección "Instalación del módulo SqlServer".

Ejecute el siguiente comando Invoke-SQLCmd, que no proporciona resultados pero realiza lo siguiente:

  • Conéctese a su servidor SQL ($serverName).
  • Autorice la conexión con las credenciales especificadas ($adminSqlLogin y $password).
  • Ejecute una -Query en su servidor SQL para IMPRIMIR el texto Hello world.
Invoke-SQLCmd -ServerInstance $serverName -Username $adminSqlLogin -Password $password -Query "PRINT 'Hello world'"

Tenga en cuenta que el parámetro -Username solo puede especificar una cuenta SQL, no una cuenta de Windows.

¿No pasó nada? No exactamente. Si no ves errores, todo salió bien.

Ahora, ejecute el mismo comando en el paso uno, pero esta vez, con un parámetro -Debug para exponer más información sobre la operación realizada por el comando.

Invoke-SQLCmd -ServerInstance $serverName -Username $adminSqlLogin -Password $password -Query "PRINT 'Hello world'" -Debug

¡Perfecto! En el resultado siguiente, la consulta muestra el resultado sin errores. Ingrese Y y presione Enter para confirmar la ejecución de la operación.

Conexión a un servidor SQL con una contraseña como SecureString

Se ha conectado con éxito a su servidor SQL utilizando variables que contienen sus credenciales, lo que sin duda funciona. Pero tenga en cuenta que codificar o poner su contraseña tal como está en una variable la deja en riesgo. ¿Por qué no intentas convertir tu contraseña a SecureString?

PowerShell ofrece el módulo de administración de secretos si desea cifrar su contraseña para mayor seguridad.

Ejecute los siguientes comandos para realizar lo siguiente:

  • ConvertTo-SecureString: convierte el valor de cadena de la variable $password en una cadena segura almacenada en $secStringPassword.
  • New-Object: crea un objeto PSCredential con un nombre de usuario ($adminSqlLogin) y una contraseña ($secStringPassword).
# Convert the $password variable's value to SecureString
$secStringPassword = ConvertTo-SecureString $password -AsPlainText -Force
# Create a credential object
$credObject = New-Object System.Management.Automation.PSCredential ($adminSqlLogin, $secStringPassword)

Ahora, ejecute el siguiente comando para conectarse a su servidor SQL y ejecute la misma consulta para imprimir el texto Hello world. Esta vez, utilice el objeto de credencial que creó en el paso anterior ($credObject) para autorizar la conexión.

Invoke-SQLCmd -ServerInstance $serverName -Credential $credObject -Query "PRINT 'Hello world'" -Debug

Conexión a un servidor SQL como usuario de Windows

Al consultar datos, normalmente desea ejecutar comandos con los permisos y niveles de acceso adecuados. Suponga que necesita ejecutar su comando como usuario de Windows. Si es así, debe iniciar una nueva sesión de PowerShell como ese usuario de Windows específico.

Para consultar datos como usuario de Windows sin depender del parámetro -Username:

Ejecute el siguiente comando para iniciar un trabajo en segundo plano de PowerShell (Start-Job) dentro de un -ScriptBlock.

El trabajo en segundo plano se ejecuta para -Query datos de la tabla sys.databases como un usuario de Windows () autenticado con sus credenciales.

Start-Job -ScriptBlock { `
Invoke-SQLCmd -ServerInstance $serverName -Query "select * from sys.databases"`
} -Credential (Get-Credential -Credential <USERNAME>)

Tome nota del Nombre del trabajo (es decir, Trabajo20), como se muestra a continuación. Necesitará el nombre del trabajo para verificarlo en el siguiente paso.

Ahora, ejecute el siguiente comando para obtener (Get-Job) y recibir (Receive-Job) todos los trabajos en segundo plano de PowerShell. Asegúrese de cambiar por el nombre del trabajo que anotó en el paso anterior.

El parámetro -Keep le permite recuperar los datos de flujo recopilados nuevamente después de la primera vista, impresos en formato de tabla (Format-Table).

Get-Job -Name <jobName> | Receive-Job -Keep | Format-Table

Crear y consultar datos desde una base de datos

Como ahora puede conectarse a su servidor SQL, es hora de comenzar a trabajar más profundamente con los parámetros del comando Invoke-SQLCmd. Primero, se conectará a una base de datos específica y creará una tabla con datos para consultar.

1. Ejecute los siguientes comandos para establecer un nombre de base de datos para una variable ($databaseName) y ejecute una -Query para confirmar el acceso a la base de datos.

# Add a new variable for the database name
$databaseName = "EmployeesDB"
# Run a query to confirm the access
Invoke-SQLCmd -ServerInstance $serverName `
		-Database $databaseName `
		-Credential $credObject `
		-Query "select Count(*) from sys.tables"

La consulta devuelve el número de tablas creadas en la base de datos, como se muestra a continuación:

2. A continuación, ejecute los siguientes comandos para ejecutar una -Query para CREAR una TABLE llamada EmployeeData y INSERT dos entradas (registros).

Estos comandos no proporcionan resultados, pero crean una tabla con columnas denominadas EmployeeID, EmployeeName y EmployeeHireDate.

$query = @"
CREATE TABLE EmployeeData (
    EmployeeID int,
    EmployeeName varchar(255),
    EmployeeHireDate varchar(255)
);
INSERT INTO EmployeeData(EmployeeID, EmployeeName, EmployeeHireDate) 
VALUES (1, 'Victor', '2023-01-01');
INSERT INTO EmployeeData(EmployeeID, EmployeeName, EmployeeHireDate) 
VALUES (2, 'Manuel', '2023-01-01');
"@
Invoke-SQLCmd -ServerInstance "$serverName" `
		-Database $databaseName `
		-Credential $credObject `
		-Query $query

3. Después de crear una tabla, ejecute el siguiente comando para recuperar datos de la tabla recién creada (EmployeeData). La información se almacena en $EmployeesDB, un objeto de PowerShell al que puede acceder y manipular como cualquier otro objeto de PowerShell.

Este comando no produce resultados, pero accederá a los datos en el siguiente paso.

$EmployeesDB = Invoke-SQLCmd -ServerInstance "$serverName" `
		-Database $databaseName `
		-Credential $credObject `
		-Query "SELECT * FROM Employees;"

¿Recibiste un error? En algún momento ocurren errores que debes aprender a manejar, y a continuación te presentamos algunos de ellos:

  • El siguiente error aparece porque es necesario corregir el nombre de la tabla consultada. Rectifique el nombre de la tabla especificada de Employees a EmployeeData y la consulta se realizará correctamente.

  • Quizás desee cancelar la ejecución de la consulta inmediatamente después de recibir un error. Si es así, utilice el parámetro -AbortOnError. Como en la captura de pantalla siguiente, verá información sobre el error.

  • O simplemente puede evitar que aparezcan mensajes de error con el parámetro -OutputSqlErrors con un valor $false, como se muestra a continuación. Tenga en cuenta que este parámetro sólo acepta valores booleanos.

4. Por último, ejecute la siguiente instrucción foreach para acceder y enviar los datos en el objeto $EmployeesDB a su consola PowerShell.

# Print the queried information from EmployeesDB
foreach ($Employee in $EmployeesDB) {
	Write-Output "Employee ID: $($Employee.EmployeeID)"
	Write-Output "Employee Name: $($Employee.EmployeeName)"
	Write-Output "Employee Hire Date: $($Employee.EmployeeHireDate)"
	Write-Output ""
}

Como puede ver a continuación, con los comandos Write-Output, los datos recuperados se imprimen en la consola.

Insertar datos en una base de datos desde un archivo SQL

Insertar y consultar datos con éxito se sintió bien, ¿verdad? Pero cuando tiene que insertar varias entradas, escribir la consulta para cada entrada puede resultar tedioso. Pero no te preocupes. El parámetro InputFile con el comando Invoke-SQLCmd resultará útil.

En lugar de escribir las consultas manualmente, utilice un archivo SQL con una consulta INSERT y varios valores de cada entrada, de la siguiente manera:

1. Abra su editor de texto/código preferido, complete la siguiente consulta en el archivo y guárdelo como run.sql. Observe a continuación que solo debe concentrarse en escribir los valores para cada entrada.

La siguiente consulta inserta nuevos empleados en su tabla EmployeeData.

INSERT INTO EmployeeData (
    EmployeeID,
    EmployeeName,
    EmployeeHireDate
)
VALUES
    (
        3,
        'Jhon',
        '2023-01-02'
    ),
    (
        4,
        'Mike',
        '2023-01-02'
    ),
    (
        5,
        'Peter',
        '2023-01-02'
    );

2. A continuación, ejecute el siguiente comando, que no proporciona resultados, pero ejecuta la consulta desde su archivo SQL (C:\run.sql).

$EmployeesDB = Invoke-SQLCmd -ServerInstance "$serverName" `
		-Database $databaseName `
		-Credential $credObject `
		-InputFile "C:\run.sql"

3. Ahora, ejecute la siguiente instrucción foreach para generar datos de su tabla EmployeesDB.

foreach ($Employee in $EmployeesDB) {
	Write-Output "Employee ID: $($Employee.EmployeeID)"
	Write-Output "Employee Name: $($Employee.EmployeeName)"
	Write-Output "Employee Hire Date: $($Employee.EmployeeHireDate)"
	Write-Output ""
}

A continuación, puede ver los datos de su tabla EmployeesDB, incluidos los tres datos de empleados recién insertados a través del archivo run.sql.

    Si necesita especificar el número de segundos antes de que expire el tiempo de espera de la consulta, utilice el parámetro -QueryTimeout, como se muestra a continuación. De forma predeterminada, cuando ejecuta un comando Invoke-SQLcmd sin el parámetro de tiempo de espera, las consultas no vencen el tiempo de espera. El parámetro -QueryTimeout indica cuánto tiempo espera el comando hasta que se complete una solicitud SQL.

    Invoke-SQLCmd -ServerInstance "$serverName" `
		-Database $databaseName `
		-Credential $credObject `
		-InputFile "C:\run.sql" `
		-QueryTimeout 5

    Manipulación de resultados de consultas

    En general, ha visto lo versátil y potente que es el comando Invoke-SQLCmd de PowerShell para ejecutar consultas y scripts SQL. ¿Pero sabía que puede personalizar la salida del comando Invoke-SQLCmd para adaptarla a sus requisitos específicos? Sí, agregando el parámetro -OutputAs.

    Puede integrar su salida personalizada con otros comandos y módulos de PowerShell para automatizar y optimizar sus tareas de administración de bases de datos.

    Para ver cómo puede manipular los resultados de la consulta:

    1. Ejecute el siguiente comando para -Query datos de la tabla EmployeeData y almacene el resultado como DataRows en $EmployeesDB. Este parámetro le permite mostrar los resultados de la consulta en un formato similar a una tabla.

    Este comando no imprime la salida a la consola, pero verificará los datos consultados a continuación

    # Store data in a variable
$EmployeesDB = Invoke-SQLCmd -ServerInstance "$serverName" `
		-Database $databaseName `
		-Credential $credObject `
		-Query "SELECT * FROM EmployeeData;" `
		-OutputAs DataRows

    2. A continuación, llame a la variable $EmployeesDB para verificar los datos almacenados.

    $EmpleadosDB

    O agregue un punto y siga presionando TAB para recorrer las diferentes funciones que puede usar, como se muestra a continuación.

    3. Ejecute el mismo comando a continuación que en el paso anterior, pero esta vez, reemplace el valor DataRows con DataSet. Este valor de parámetro le permite guardar los resultados de la consulta en un objeto con formato XML ($xmlEmployeeData).

    Al igual que el ejemplo anterior, este comando no produce resultados en la consola, pero verificará el resultado en el siguiente paso.

    $xmlEmployeeData = Invoke-SQLCmd -ServerInstance "$serverName" `
		-Database $databaseName `
		-Credential $credObject `
		-Query "SELECT * FROM EmployeeData;" `
		-OutputAs DataSet

    4. Finalmente, llame a $xmlEmployeeData y verifique el objeto con formato XML.

    $xmlEmployeeData

    O ejecute el siguiente comando para ver los datos en formato de tabla (.Tables) como en el paso dos.

    $xmlEmployeeData.Tables

    Conclusión

    A lo largo de este tutorial, ha dado los primeros pasos para aprender el comando Invoke-SQLCmd de PowerShell. Se ha conectado con éxito a una base de datos SQL, ha ejecutado diferentes consultas y ha manipulado los resultados.

    Trabajar dentro de una GUI, como SQL Server Management Studio, ciertamente hace que la administración de bases de datos sea menos desalentadora. Pero si conoce el comando Invoke-SQLCmd como la palma de su mano, puede lograr más con PowerShell.

    ¿Por qué no automatizar las tareas diarias con el comando Invoke-SQLCmd? ¿O extraer datos de sus servidores SQL para manipularlos con PowerShell? Cualquiera que sea el próximo paso que elija, ¡buena suerte en su viaje para convertirse en un experto en PowerShell o DBA!

    Artículos relacionados