ProHoster > blog > amministrazione > Lavorare con MS SQL da Powershell su Linux

Lavorare con MS SQL da Powershell su Linux

Questo articolo è puramente pratico ed è dedicato alla mia triste storia

Prepararsi per Zero TouchPROD per RDS (MS SQL), di cui ci ronzavano tutte le orecchie, ho fatto una presentazione (POC - Proof Of Concept) dell'automazione: un insieme di script PowerShell. Dopo la presentazione, quando gli applausi tempestosi e prolungati si sono calmati, trasformandosi in applausi incessanti, mi hanno detto: tutto questo va bene, ma solo per ragioni ideologiche, tutti i nostri schiavi Jenkins lavorano su Linux!

È possibile? Prendere una lampada DBA così calda da Windows e inserirla nel calore di PowerShell sotto Linux? Non è questo crudele?

Lavorare con MS SQL da Powershell su Linux
Ho dovuto immergermi in questa strana combinazione di tecnologie. Ovviamente tutti i miei oltre 30 script hanno smesso di funzionare. Con mia sorpresa, sono riuscito a sistemare tutto in un giorno lavorativo. Sto scrivendo alle calcagna. Quindi, quali insidie ​​puoi incontrare quando trasferisci script PowerShell da Windows a Linux?

sqlcmd rispetto a Invoke-SqlCmd

Lascia che ti ricordi la differenza principale tra loro. Buona vecchia utilità sqlcmd Funziona anche sotto Linux, con funzionalità quasi identiche. Passiamo la query per eseguire -Q, il file di input come -i e l'output come -o. Ma i nomi dei file, ovviamente, fanno distinzione tra maiuscole e minuscole. Se usi -i, nel file scrivi alla fine:

GO
EXIT

Se non c'è EXIT alla fine, sqlcmd procederà ad attendere l'input, e se prima EXIT non lo farò GO, l'ultimo comando non funzionerà. Il file di output contiene tutto l'output, le selezioni, i messaggi, la stampa, ecc.

Invoke-SqlCmd produce il risultato come DataSet, DataTables o DataRows. Pertanto, se elabori il risultato di una semplice selezione, puoi utilizzare sqlcmd, dopo aver analizzato il suo output, è quasi impossibile ricavare qualcosa di complesso: per questo esiste Invoca-SqlCmd. Ma questa squadra ha anche le sue battute:

  • Se le trasferisci un file tramite -File di inputpoi EXIT non necessario, inoltre, produce un errore di sintassi
  • -File di uscita no, il comando ti restituisce il risultato come oggetto
  • Esistono due sintassi per specificare un server: -ServerInstance -Nome utente -Password -Database e attraverso -Stringa di connessione. Stranamente, nel primo caso non è possibile specificare una porta diversa dalla 1433.
  • output testuale, tipo PRINT, che viene semplicemente “catturato” sqlcmdper Invoca-SqlCmd è un problema
  • E, soprattutto: Molto probabilmente il tuo Linux non ha questo cmdlet!

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

Sostituzione variabile

sqlcmd ha la sostituzione delle variabili utilizzando -v, ad esempio in questo modo:

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

Nello script SQL utilizziamo le sostituzioni:

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

Quindi eccolo qui. In *nix le sostituzioni di variabili non funzionano... Parametro -v ignorato. U Invoca-SqlCmd ignorato -Variabili. Sebbene il parametro che specifica le variabili stesse venga ignorato, le sostituzioni stesse funzionano: puoi utilizzare qualsiasi variabile della Shell. Tuttavia, sono stato offeso dalle variabili e ho deciso di non dipendere affatto da esse, e ho agito in modo sgarbato e primitivo, poiché 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à dalla versione Unix.

Carica file

Nella versione Windows, qualsiasi operazione era accompagnata da un controllo: abbiamo eseguito sqlcmd, abbiamo ricevuto qualche tipo di abuso nel file di output, abbiamo allegato questo file alla targa di controllo. Fortunatamente, il server SQL funzionava sullo stesso server di Jenkins, è stato fatto 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

Pertanto, inghiottiamo interamente il file BCP e lo inseriamo nel campo nvarchar(max) della tabella di controllo. Naturalmente, l'intero sistema è andato in pezzi, perché invece di un server SQL ho ottenuto RDS e BULK INSERT non funziona affatto tramite UNC a causa di un tentativo di acquisire un blocco esclusivo su un file, e con RDS questo è generalmente condannato da proprio l'inizio. Ho quindi deciso di modificare 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 scrivi in ​​questa tabella in questo modo:

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() 
  }

Per selezionare il contenuto è necessario selezionare per ID, scegliendo nell'ordine n (identità).

Nel prossimo articolo entrerò più in dettaglio su come tutto questo interagisce con Jenkins.

Fonte: habr.com

Aggiungi un commento