🥇 Trabajar con MS SQL desde Powershell en Linux

Este artículo es puramente práctico y está dedicado a mi triste historia.

Preparándome para Zero Touch PROD para RDS (MS SQL), del cual nos han hablado hasta el cansancio, hice una presentación (POC — Prueba de Concepto) de automatización: un conjunto de scripts de PowerShell. Después de la presentación, cuando se apagaron los aplausos prolongados, que se convirtieron en ovações incesantes, me dijeron: — todo esto está muy bien, pero solo por razones ideológicas, ¡todos nuestros Jenkins slaves funcionan bajo Linux!

¿Es posible? ¿Tomar a un DBA tan cálido y acogedor de Windows y lanzarlo al infierno de PowerShell en Linux? ¿No es eso cruel?

Tuve que sumergirme en esta extraña combinación de tecnologías. Por supuesto, todos mis más de 30 scripts dejaron de funcionar. Para mi sorpresa, logré repararlos todos en un solo día laboral. Estoy escribiendo a calientito. Entonces, ¿qué escollos pueden encontrarse al trasladar scripts de PowerShell de Windows a Linux?

sqlcmd vs Invoke-SqlCmd

Recuerden la principal diferencia entre ellos. La antigua y buena herramienta sqlcmd funciona también en Linux, con una funcionalidad casi idéntica. Pasamos la consulta para ejecutar con -Q, el archivo de entrada como -i, y la salida -o. Solo que los nombres de los archivos, por supuesto, son sensibles a mayúsculas y minúsculas. Si usas -i, entonces en el archivo escribe al final:

GO
EXIT

Si no hay EXIT al final, sqlcmd entrará en espera de entrada, y si antes de EXIT reemplazará GO, la última orden no se ejecutará. En el archivo de salida entra toda la salida, selects, mensajes, print, etc.

Invoke-SqlCmd devuelve el resultado en forma de DataSet, DataTables o DataRows. Así que, si puedes procesar el resultado de un simple select a través de sqlcmd, descomponiendo su salida, es casi imposible imprimir algo complejo: para eso existe Invoke-SqlCmd. Pero esta orden también tiene sus peculiaridades:

Si le pasas un archivo a través de -InputFile, entonces EXIT no es necesario, además, genera un error de sintaxis.
-OutputFile no, el comando te devuelve el resultado como un objeto.
Para especificar el servidor hay dos sintaxis: -ServerInstance -Username -Password -Database y a través de -ConnectionString. Como curiosamente, en el primer caso no se puede especificar un puerto diferente de 1433.
la salida de texto, como PRINT, que se captura fácilmente sqlcmd, para Invoke-SqlCmd es un problema.
Y lo más importante: probablemente ¡no tienes ese cmdlet en tu Linux!

Y ese es el principal problema. Solo en marzo, este cmdlet se volvió disponible para plataformas no-Windows, ¡y finalmente podemos avanzar!

Sustitución de variables.

En sqlcmd hay sustitución de variables con -v, por ejemplo, así:

# $conn содержит начало команды sqlcmd
$cmd = $conn + " -i D:appsSlaveJobsKillSpid.sql -o killspid.res 
  -v spid =`"" + $spid + "`" -v age =`"" + $age + "`""
Invoke-Expression $cmd

En el script SQL utilizamos sustituciones:

set @spid=$(spid)
set @age=$(age)

Así que, en *nix las sustituciones de variables no funcionan. El parámetro -v se ignora. En Invoke-SqlCmd se ignora -Variables. Aunque el parámetro que define las propias variables se ignora, las sustituciones funcionan: puedes usar cualquier variable del Shell. Sin embargo, me molestaron las variables y decidí no depender de ellas en absoluto, así que tomé un enfoque grosero y primitivo, afortunadamente, los scripts en sql son cortos:

# prepend the parameters  
"declare @age int, @spid int" | Add-Content "q.sql"
"set @spid=" + $spid | Add-Content "q.sql"
"set @age=" + $age | Add-Content "q.sql"

foreach ($line in Get-Content "Sqlserver/Automation/KillSpid.sql") { 
  $line | Add-Content "q.sql" 
  }
$cmd = "/opt/mssql-tools/bin/" + $conn + " -i q.sql -o res.log"

Esto, como habrás entendido, es una prueba ya de la versión de unix.

Carga de archivos

En la versión de Windows, cualquier operación venía acompañada de auditoría: ejecutamos sqlcmd, obtuvimos algún error en el archivo de salida, adjuntamos este archivo a la tabla de auditoría. Afortunadamente, el servidor SQL estaba en el mismo servidor que Jenkins, esto se hacía más o menos así:

CREATE procedure AuditUpload
  @id int, @filename varchar(256)
as
  set nocount on
  declare @sql varchar(max)

  CREATE TABLE #multi (filer NVARCHAR(MAX))
  set @sql='BULK INSERT #multi FROM '''+@filename
    +''' WITH (ROWTERMINATOR = '' '',CODEPAGE = ''ACP'')'
  exec (@sql)
  select @sql=filer from #multi
  update JenkinsAudit set multiliner=@sql where ID=@id
  return

De este modo, absorbemos el archivo BCP en su totalidad y lo introducimos en el campo nvarchar(max) de la tabla de auditoría. Por supuesto, todo este sistema se desmoronó, ya que en lugar del servidor SQL obtuve RDS, y BULK INSERT no funciona en absoluto por UNC debido a la tentativa de tomar un bloqueo exclusivo en el archivo, y con RDS eso está condenado desde el principio. Así que decidí cambiar el diseño del sistema, almacenando la auditoría línea por línea:

CREATE TABLE AuditOut (
  ID int NULL,
  TextLine nvarchar(max) NULL,
  n int IDENTITY(1,1) PRIMARY KEY
  )

Y escribir en esta tabla así:

function WriteAudit([string]$Filename, [string]$ConnStr, 
     [string]$Tabname, [string]$Jobname)
{
  # obtener $lastid de la última ejecución  -- omitido para el artículo
	
  # crear una cuadrícula y poblarla con datos del archivo
  $audit =  Get-Content $Filename
  $DT = new-object Data.DataTable   

  $COL1 =  new-object Data.DataColumn; 
  $COL1.ColumnName = "ID"; 
  $COL1.DataType =  [System.Type]::GetType("System.Int32") 

  $COL2 =  new-object Data.DataColumn; 
  $COL2.ColumnName = "TextLine"; 
  $COL2.DataType =  [System.Type]::GetType("System.String") 
  
  $DT.Columns.Add($COL1) 
  $DT.Columns.Add($COL2) 
  foreach ($line in $audit) 
    { 
    $DR = $dt.NewRow()   
    $DR.Item("ID") = $lastid
    $DR.Item("TextLine") = $line
    $DT.Rows.Add($DR)   
    } 

  # escribirlo en la tabla
  $conn=new-object System.Data.SqlClient.SQLConnection 
  $conn.ConnectionString = $ConnStr
  $conn.Open() 
  $bulkCopy = new-object ("Data.SqlClient.SqlBulkCopy") $ConnStr
  $bulkCopy.DestinationTableName = $Tabname 
  $bulkCopy.BatchSize = 50000
  $bulkCopy.BulkCopyTimeout = 0
  $bulkCopy.WriteToServer($DT) 
  $conn.Close() 
  }

Para seleccionar el contenido, debes hacer select por ID, eligiendo en orden n (identidad).

En el próximo artículo, me detendré más en cómo todo esto interactúa con Jenkins.

Fuente: habr.com