ProHoster > Blog > Administración > Traballando con MS SQL de Powershell en Linux

Traballando con MS SQL de Powershell en Linux

Este artigo é puramente práctico e está dedicado á miña triste historia

Preparándonos para PROD Zero Touch para RDS (MS SQL), sobre o que todos os nosos oídos zumbaban, fixen unha presentación (POC - Proof Of Concept) de automatización: un conxunto de scripts de powershell. Despois da presentación, cando os tormentosos e prolongados aplausos apagaron, converténdose en aplausos incesantes, dixéronme: todo isto é bo, pero só por razóns ideolóxicas, todos os nosos escravos de Jenkins traballan en Linux.

Isto é posible? Tomar un DBA tan cálido e lámpado de Windows e pégalo no mesmo calor do Powershell baixo Linux? Non é cruel?

Traballando con MS SQL de Powershell en Linux
Tiven que mergullarme nesta estraña combinación de tecnoloxías. Por suposto, todos os meus máis de 30 scripts deixaron de funcionar. Para a miña sorpresa, conseguín arranxar todo nun día laborable. Escribo en busca. Entón, que trampas podes atopar ao transferir scripts de Powershell de Windows a Linux?

sqlcmd vs Invoke-SqlCmd

Permíteme recordarche a principal diferenza entre eles. Boa utilidade antiga sqlcmd Tamén funciona baixo Linux, cunha funcionalidade case idéntica. Pasamos a consulta para executar -Q, o ficheiro de entrada como -i e a saída como -o. Pero os nomes dos ficheiros, por suposto, distinguen entre maiúsculas e minúsculas. Se usas -i, escribe ao final no ficheiro:

GO
EXIT

Se non hai EXIT ao final, entón sqlcmd procederá a esperar a entrada, e se antes SAIR non o fará GO, entón o último comando non funcionará. O ficheiro de saída contén toda a saída, seleccións, mensaxes, impresión, etc.

Invoke-SqlCmd produce o resultado como DataSet, DataTables ou DataRows. Polo tanto, se procesa o resultado dunha simple selección, pode usar sqlcmd, unha vez analizada a súa saída, é case imposible derivar algo complexo: para iso hai Invocar-SqlCmd. Pero este equipo tamén ten as súas propias bromas:

  • Se transfire un ficheiro a ela vía -Ficheiro de entrada, Entón SAIR non é necesario, ademais, produce un erro de sintaxe
  • -Ficheiro de saída non, o comando devolveche o resultado como un obxecto
  • Hai dúas sintaxes para especificar un servidor: -ServerInstance -Nome de usuario -Contrasinal -Base de datos e a través -Cadea de conexión. Curiosamente, no primeiro caso non é posible especificar un porto que non sexa 1433.
  • saída de texto, escriba PRINT, que simplemente é "capturado" sqlcmdpor Invocar-SqlCmd é un problema
  • E o máis importante: O máis probable é que o teu Linux non teña este cmdlet!

E este é o principal problema. Só en marzo este cmdlet quedou dispoñible para plataformas non Windows, e por fin podemos seguir adiante!

Substitución de variables

sqlcmd ten substitución de variables usando -v, por exemplo como este:

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

No script SQL usamos substitucións:

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

Entón, aquí está. En *nix as substitucións de variables non funcionan... Parámetro -v ignorado. U Invocar-SqlCmd ignorado -Variables. Aínda que se ignora o parámetro que especifica as propias variables, as propias substitucións funcionan; podes usar calquera variable de Shell. Non obstante, ofendéronme as variables e decidín non depender delas en absoluto, e actuei de forma groseira e primitiva, xa que os scripts SQL son curtos:

# 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"

Esta, como entendes, é unha proba xa da versión de Unix.

Cargando ficheiros

Na versión de Windows, calquera operación ía acompañada dunha auditoría: executamos sqlcmd, recibimos algún tipo de abuso no ficheiro de saída, adxuntamos este ficheiro á placa de auditoría. Afortunadamente, o servidor SQL funcionou no mesmo servidor que Jenkins, fíxose algo 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

Así, tragamos o ficheiro BCP por completo e o introducimos no campo nvarchar(max) da táboa de auditoría. Por suposto, todo este sistema desmoronouse, porque en lugar dun servidor SQL conseguín RDS, e BULK INSERT non funciona en absoluto a través de UNC debido a un intento de bloquear un ficheiro exclusivo, e con RDS isto é xeralmente condenado. o comezo mesmo. Entón decidín cambiar o deseño do sistema, almacenando a auditoría liña por liña:

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

E escribe nesta táboa 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 contido, cómpre seleccionar por ID, escollendo por orde n (identidade).

No seguinte artigo vou entrar en máis detalles sobre como todo isto interactúa con Jenkins.

Fonte: www.habr.com

Engadir un comentario