🥇Lavorare con MS SQL da Powershell su Linux

Questo articolo è puramente pratico e racconta la mia triste storia

Preparandomi a Zero Touch PROD per RDS (MS SQL), di cui ci hanno parlato incessantemente, ho realizzato una presentazione (POC — Proof Of Concept) di automazione: un insieme di script PowerShell. Dopo la presentazione, quando si erano placati gli applausi fragorosi e prolungati, che si sono trasformati in un'ovazione ininterrotta, mi hanno detto: - va tutto bene, ma per motivi ideologici tutti i nostri Jenkins slaves funzionano sotto Linux!

È davvero possibile? Prendere un DBA così caloroso, proveniente da Windows e metterlo nel caos di PowerShell su Linux? Non è forse crudele?

Ho dovuto immergermi in questa strana combinazione di tecnologie. Naturalmente, tutti i miei oltre 30 script hanno smesso di funzionare. Con mia sorpresa, sono riuscito a correggerli tutti in un giorno lavorativo. Scrivo a caldo. Quindi, quali insidie potreste incontrare nel trasferire gli script PowerShell da Windows a Linux?

sqlcmd vs Invoke-SqlCmd

Ricordo la principale differenza tra di loro. La vecchia e buona utility sqlcmd funziona anche su Linux, con funzionalità quasi identiche. Passiamo la query da eseguire con -Q, il file di input come -i, e l'output con -o. Solo che i nomi dei file, ovviamente, sono case-sensitive. Se usate -i, dovete scrivere alla fine del file:

GO
EXIT

Se alla fine non c'è EXIT, sqlcmd entrerà in attesa di input, e se prima di EXIT non funzionerà GO, l'ultima comando non verrà eseguito. Tutto l'output, inclusi selects, messaggi, print, ecc., va nel file di output.

Invoke-SqlCmd restituisce il risultato sotto forma di DataSet, DataTables o DataRows. Pertanto, se potete elaborare il risultato di un semplice select anche attraverso sqlcmd, analizzandone l'output, è praticamente impossibile restituire qualcosa di complesso: a questo scopo esiste Invoke-SqlCmd. Ma ci sono delle peculiarità in questo comando:

Se gli passate un file tramite -InputFile, allora EXIT non è necessario, anzi, genera un errore di sintassi
-OutputFile no, il comando restituisce il risultato come oggetto
Per specificare il server ci sono due sintassi: -ServerInstance -Username -Password -Database e tramite -ConnectionString. Stranamente, nel primo caso non è possibile specificare una porta diversa da 1433.
l'output testuale, come PRINT, che è facilmente "catturato" sqlcmd, per Invoke-SqlCmd è un problema
E la cosa più importante: probabilmente in Linux non c'è questo cmdlet!

E questa è la principale problema. Solo a marzo questo cmdlet è diventato disponibile per piattaforme non Windows, e finalmente possiamo andare avanti!

Sostituzione delle variabili

In sqlcmd ci sono sostituzioni di variabili tramite -v, per esempio in questo modo:

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

Nel script SQL utilizziamo le sostituzioni:

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

Ecco. In *nix le sostituzioni di variabili non funzionano. Il parametro -v è ignorato. In Invoke-SqlCmd è ignorato -Variabili. Anche se il parametro che definisce le variabili stesse viene ignorato, le sostituzioni funzionano: puoi utilizzare qualsiasi variabile della Shell. Tuttavia, mi sono offeso per le variabili e ho deciso di non dipenderne affatto, e ho agito in modo rude e primitivo, dato che gli script SQL sono brevi:

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

Questo, come hai capito, è un test già con la versione unix.

Caricamento di file

Nella versione Windows, ogni operazione era accompagnata da un audit: eseguito sqlcmd, ricevuta qualche lamentela nel file di output, allegato quel file alla tabella audit. Per fortuna SQL server girava sullo stesso server di Jenkins, si faceva più o meno in questo modo:

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

In questo modo abbiamo caricato l'intero file BCP e lo abbiamo inserito nel campo nvarchar(max) della tabella audit. Naturalmente, tutto questo sistema è crollato, poiché invece di SQL server ho ottenuto RDS, e BULK INSERT non funziona affatto tramite UNC a causa del tentativo di ottenere un lock esclusivo sul file, e con RDS è sostanzialmente destinato a fallire. Quindi ho deciso di cambiare il design del sistema, memorizzando l'audit riga per riga:

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

E di scrivere in questa tabella in questo modo:

function WriteAudit([string]$Filename, [string]$ConnStr, 
     [string]$Tabname, [string]$Jobname)
{
  # get $lastid of the last execution  -- saltato per l'articolo
	
  #crea la griglia e popolala con i dati dal 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)   
    } 

  # scrivi nella tabella
  $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() 
  }

Per selezionare il contenuto, è necessario eseguire un select per ID, ordinando in base a n (identity).

Nell'articolo successivo approfondirò come tutto ciò interagisce con Jenkins.

Fonte: habr.com