🥇Töö MS SQL-iga Powershelli kaudu Linuxis

See artikkel on puhtalt praktiline ja pühendatud minu kurvale loodusele.

Valmistudes Zero Touch PROD RDS (MS SQL) jaoks, millest oleme kõik pidevalt kuulnud, tegin esitluse (POC — Proof Of Concept) automatiseerimise kohta: powershelli skriptide kogum. Pärast esitlust, kui tormilised, pikad aplausid, mis muutusid lõpmatuks ovatsiooniks, vaibusid, öeldi mulle — kõik see on kena, aga ideoloogilistel põhjustel töötavad meie kõik Jenkins slaves Linuxi all!

Kas see on tõesti võimalik? Võtta selline soe, mugav DBA Windowsilt ja suruda ta Linuxi all olemise powershelli kõige kuumemasse punkti? Pole see mitte julm?

Pidi süvenema sellesse kummalisse tehnoloogiate kombinatsiooni. Loomulikult lakkasid kõik mu 30+ skripti töötamast. Üllatuseks suutsin ma ühe tööpäeva jooksul kõik korda saada. Kirjutan kuumalt. Nii, millised varjatud ohud võivad teid oodata powershelli skriptide üleviimisel Windowsilt Linuxile?

sqlcmd vs Invoke-SqlCmd

Kordan peamist erinevust nende vahel. Hea vana utiliit sqlcmd töötab ka Linuxis, peaaegu identsete funktsioonidega. Küsimuse täitmiseks edastame -Q, sisendfaili nagu -i ja väljundi -o. Ainult failinimed on loomulikult suuruse mõttes tundlikud. Kui kasutate -i, kirjutage faili lõppu:

GO
EXIT

Kui lõpus ei ole EXIT-d, siis sqlcmd läheb ootama sisendit, ja kui enne EXIT ei toimi GO, siis viimane käsk ei tööta. Väljundifaili läheb kogu väljund, selects, teated, print jne.

Invoke-SqlCmd tagastab tulemuse DataSet'is, DataTables'ina või DataRows'ina. Seega, kui töötlete tulemuse tavalise select-iga, saate seda teha ka sqlcmd, kuid selle väljundi lahtimurdmine teeb keeruka tulemuse väljastamise praktiliselt võimatuks: selleks on Invoke-SqlCmd. Aga sellel käsul on ka oma nurgad:

Kui edastate sellefaili kaudu -InputFile, siis EXIT ei ole vaja, tegelikult, see põhjustab süntaktilise vea
-OutputFile ei, käsk tagastab teile tulemuse objekti kujul
Serveri näitamisteks on kaks süntaksit: -ServerInstance -Username -Password -Database ja kaudu -ConnectionString. Oodake, kummalisel kombel ei saa esimesel juhul määrata porti, mis erineb 1433-st.
tekstiline väljund, nagu PRINT, mida on lihtne 'püüdma' sqlcmd, et Invoke-SqlCmd on probleem
Ja peamine: tõenäoliselt ei ole selle cmdlet'i Linuxis olemas!

Ja see on peamine probleem. Alles märtsis sai see cmdlet saadavaks mitte-Windows platvormidel, ja lõpuks saame edasi liikuda!

Muudatuste asendamine

sqlcmd toetab muutujate asendamist -v abil, näiteks nii:

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

SQL skriptis kasutame asendusi:

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

Nii et. *nix muutujate asendused ei tööta. Parameeter -v ignoreeritakse. Invoke-SqlCmd ignoreeritakse -Variables. Kuigi parameeter, mis määrab ise muutujad, ignoreeritakse, töötavad asendused endiselt — saate kasutada Shellist mis tahes muutujaid. Siiski, ma solvusin muutujate peale ja otsustasin neist üldse mitte sõltuda, ning käitusin karmilt ja primitiivselt, õnneks on SQL skriptid lühikesed:

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

See on, nagu te aru saite, test juba Unix'i versioonist.

Failide üleslaadimine

Windowsi versioonis kaasnes iga toiminguga auditeerimine: käideti sqlcmd, saadud mingit jama väljundfailis, lisati see fail auditi tabelisse. Õnneks töötas SQL server samas serveris, mis ka Jenkins, tehti seda umbes nii:

LOO PROSEDUURI AuditUpload
  @id int, @filename varchar(256)
KUIDAS
  SEATA NOCOUNT ON
  DEKLARE @sql varchar(max)

  LOO TABEL #multi (filer NVARCHAR(MAX))
  SEATA @sql='BULK INSERT #multi FROM '''+@filename
    +''' WITH (ROWTERMINATOR = '' '',CODEPAGE = ''ACP'')'
  EXEC (@sql)
  VALI @sql=filer FROM #multi
  UVALIDA JenkinsAudit SEAT MULTILINER=@sql KUS ID=@id
  TAGASTA

Nii saame BCP-faili tervikuna ja paneme selle nvarchar(max) tüüpi välja audititabelisse. Muidugi, kogu see süsteem lagunes, kuna sain SQL-serveri asemel RDS-i ja BULK INSERT ei tööta UNC kaudu, kuna see püüab faili üle võtta eksklusiivse lukuga, mis RDS-is on algusest peale hukule määratud. Niisiis otsustasin süsteemi disaini muuta, hoides auditit ridade kaupa:

LOO TABEL AuditOut (
  ID int NULL,
  TextLine nvarchar(max) NULL,
  n int IDENTITY(1,1) PRIMARY KEY
  )

Ja kirjutame sellesse tabelisse nii:

function WriteAudit([string]$Filename, [string]$ConnStr, 
     [string]$Tabname, [string]$Jobname)
{
  # get $lastid of the last execution  -- jäetud artikli jaoks
	
  #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() 
  }

Sisu valimiseks tuleb teha select ID alusel, valides järjekorras n (identity).

Järgmises artiklis käsitlen seda põhjalikumalt, kuidas see kõik suhtleb Jenkinsiga.

Allikas: habr.com