ProHoster > Blog > administración > Trabajar con MS SQL desde Powershell en Linux

Trabajar con MS SQL desde Powershell en Linux

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

Alistandose para Cero toque PROD para RDS (MS SQL), sobre el cual todos estábamos zumbando, hice una presentación (POC - Prueba de concepto) de automatización: un conjunto de scripts de PowerShell. Después de la presentación, cuando los aplausos tormentosos y prolongados amainaron, convirtiéndose en aplausos incesantes, me dijeron: todo esto está bien, pero solo por razones ideológicas, ¡todos nuestros esclavos Jenkins trabajan en Linux!

es posible? ¿Tomar una lámpara DBA tan cálida de Windows y colocarla en el calor de PowerShell en Linux? ¿No es esto cruel?

Trabajar con MS SQL desde Powershell en Linux
Tuve que sumergirme en esta extraña combinación de tecnologías. Por supuesto, mis más de 30 scripts dejaron de funcionar. Para mi sorpresa, logré arreglarlo todo en un día laborable. Estoy escribiendo en una intensa persecución. Entonces, ¿qué dificultades puede encontrar al transferir scripts de PowerShell de Windows a Linux?

sqlcmd frente a Invocar-SqlCmd

Déjame recordarte la principal diferencia entre ellos. Buena utilidad antigua sqlcmd También funciona bajo Linux, con una funcionalidad casi idéntica. Pasamos la consulta para ejecutar -Q, el archivo de entrada como -i y la salida como -o. Pero los nombres de los archivos, por supuesto, distinguen entre mayúsculas y minúsculas. Si usa -i, entonces en el archivo escriba al final:

GO
EXIT

Si no hay EXIT al final, entonces sqlcmd procederá a esperar la entrada, y si antes SALIR no te despiertes GO, entonces el último comando no funcionará. El archivo de salida contiene todos los resultados, selecciones, mensajes, impresiones, etc.

Invoke-SqlCmd produce el resultado como DataSet, DataTables o DataRows. Por lo tanto, si procesa el resultado de una selección simple, puede usar sqlcmd, después de analizar su resultado, es casi imposible derivar algo complejo: para esto existe Invocar-SqlCmd. Pero este equipo también tiene sus propios chistes:

  • Si le transfieres un archivo a través de -Fichero de entradaentonces SALIR no es necesario, además, produce un error de sintaxis
  • -Archivo de salida no, el comando te devuelve el resultado como un objeto
  • Hay dos sintaxis para especificar un servidor: -Instancia del servidor -Nombre de usuario -Contraseña -Base de datos y mediante -Cadena de conexión. Curiosamente, en el primer caso no es posible especificar otro puerto que no sea el 1433.
  • salida de texto, escriba PRINT, que simplemente se “capta” sqlcmdpara Invocar-SqlCmd es un problema
  • Y lo más importante: ¡Lo más probable es que tu Linux no tenga este cmdlet!

Y este es el principal problema. Sólo en marzo este cmdlet estuvo disponible para plataformas que no son Windows¡Y finalmente podemos seguir adelante!

Sustitución de variables

sqlcmd tiene sustitución de variables usando -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 usamos sustituciones:

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

Asi que aqui esta. En *nada las sustituciones de variables no funcionan. Parámetro -v ignorado. Ud. Invocar-SqlCmd ignorado -Variables. Aunque se ignora el parámetro que especifica las variables en sí, las sustituciones en sí funcionan; puede utilizar cualquier variable de Shell. Sin embargo, me ofendieron las variables y decidí no depender de ellas en absoluto, y actué de manera grosera y primitiva, ya que los scripts 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 comprenderá, ya es una prueba de la versión Unix.

Subiendo archivos

En la versión de Windows, cualquier operación iba acompañada de una auditoría: ejecutamos sqlcmd, recibimos algún tipo de abuso en el archivo de salida y adjuntamos este archivo a la placa de auditoría. Afortunadamente, el servidor SQL funcionó en el mismo servidor que Jenkins, se hizo algo como esto:

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

Por lo tanto, nos tragamos el archivo BCP por completo y lo colocamos en el campo nvarchar(max) de la tabla de auditoría. Por supuesto, todo este sistema se vino abajo, porque en lugar de un servidor SQL obtuve RDS, y BULK INSERT no funciona en absoluto a través de UNC debido a un intento de bloquear exclusivamente un archivo, y con RDS esto generalmente está condenado al fracaso. el principio. Entonces 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 escribe en esta tabla así:

function WriteAudit([string]$Filename, [string]$ConnStr, 
     [string]$Tabname, [string]$Jobname)
{
  # get $lastid of the last execution  -- проскипано для статьи
	
  #create grid and populate it with data from file
  $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)   
    } 

  # write it to table
  $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 contenido, debe seleccionar por ID, eligiendo en orden n (identidad).

En el próximo artículo entraré en más detalles sobre cómo interactúa todo esto con Jenkins.

Fuente: habr.com

Añadir un comentario