Práca s MS SQL z Powershell na Linuxe

Tento článok je čisto praktický a je venovaný mojej smutnej histórii

Pri príprave na Zero Touch PROD pre RDS (MS SQL), o ktorom nám unavene hučali do uší, som pripravil prezentáciu (POC — Proof Of Concept) automatizácie: súbor powershell skriptov. Po prezentácii, keď utíchli búrlivé, dlhé potlesky, prechádzajúce do neutíchajúcich ovácií, mi povedali – všetko je to skvelé, ale z ideologických dôvodov všetci naši Jenkins slaves fungujú na Linuxe!

Veď to predsa nie je možné? Vziať takého príjemného, domáceho DBA z Windows a zatiahnuť ho do tej najtemnejšej časti powershell na Linuxe? Nie je to kruté?

Práca s MS SQL z Powershell na Linuxe
Musel som sa ponoriť do tejto zvláštnej kombinácie technológií. Samozrejme, všetky moje 30+ skriptov prestali fungovať. K mojej údivu sa mi za jeden pracovný deň podarilo všetko opraviť. Píšem z horúcej stopy. Takže aké úskalia sa vám môžu prihodiť pri prenose powershell skriptov z Windows na Linux?

sqlcmd vs Invoke-SqlCmd

Pripomínam základný rozdiel medzi nimi. Starý dobrý nástroj sqlcmd funguje aj na Linuxe s takmer identickou funkčnosťou. Dotaz na vykonanie preposielame pomocou -Q, vstupný súbor ako -i, a výstup -o. Len názvy súborov sú, pochopiteľne, case-sensitive. Ak používate -i, tak do súboru napíšte na konci:

GO
EXIT

Ak na konci nebude EXIT, potom sqlcmd prejde do režimu čakajúceho na vstup, a ak pred EXIT nebude GO, tak posledný príkaz sa nevykoná. Do súboru výstupu sa zapisuje celý výstup, selects, správy, print atď.

Invoke-SqlCmd vracia výsledok vo forme DataSet, DataTables alebo DataRows. Preto, ak spracovanie výsledku jednoduchého selectu môžete vykonať aj cez sqlcmd, rozobraním jeho výstupu, tak výstup niečoho zložitějšího je prakticky nemožné: na to je Invoke-SqlCmd. Ale táto príkaz má aj svoje špecifiká:

  • Ak jej prenášate súbor cez -InputFile, tak EXIT nie je potrebný, viac ako to, vracia syntaktickú chybu
  • -OutputFile nie, príkaz vám vráti výsledok vo forme objektu
  • Pre určenie servera existujú dva formáty: -ServerInstance -Username -Password -Database a cez -ConnectionString. Ako prekvapivo znie, v prvom prípade nie je možné uviesť port odlišný od 1433.
  • textový výstup, typu PRINT, ktorý sa jednoducho "chytí" sqlcmd, pre Invoke-SqlCmd je problémom
  • A hlavne: pravdepodobne na vašom Linuxe tento cmdlet nie je!

A to je hlavný problém. Iba v marci sa tento cmdlet stal dostupným pre ne-windows platformy, a konečne môžeme pokračovať!

Zamieňanie premenných

V sqlcmd je možné používať zástupné premenné pomocou -v, napríklad takto:

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

V SQL skripte používame zástupné premenné:

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

Takže. V *nix zástupné premenné nefungujú. Parameter -v sa ignoruje. Pri Invoke-SqlCmd sa ignoruje -Variables. Hoci parameter, ktorý definuje samotné premenné, je ignorovaný, zástupné premenné fungujú — môžete používať akékoľvek premenné zo Shellu. Avšak som sa urazil na premenné a rozhodol sa na nich celkom nezávisieť, a rozhodol som sa to urobiť jednoducho a primitivne, ale skripty na SQL sú krátke:

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

Toto, ako ste pochopili, je test už s unixovou verziou.

Nahrávanie súborov

Vo verzii pre Windows bola každá operácia sprevádzaná auditom: vykonali sme sqlcmd, dostali sme nejakú chybu v súbori výstupu, priložili sme tento súbor k audítorskej tabuľke. Našťastie SQL server bežal na tom istom serveri ako Jenkins, robilo sa to približne takto:

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

Takto zavádzame súbor BCP úplne a napĺňame pole nvarchar(max) tabuľky auditu. Samozrejme, celý tento systém sa rozpadol, pretože namiesto SQL servera som dostal RDS, a BULK INSERT vôbec nefunguje cez UNC kvôli pokusu o získanie exkluzívneho uzamknutia na súbor, a s RDS to je od začiatku odsúdené na neúspech. Takže som sa rozhodol zmeniť dizajn systému, uchovávajúc audit po riadkoch:

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

A zapisovať do tejto tabuľky takto:

function WriteAudit([string]$Filename, [string]$ConnStr, 
     [string]$Tabname, [string]$Jobname)
{
  # získať $lastid poslednej exekúcie  -- preskočené pre článok
	
  # vytvoriť mriežku a naplniť ju údajmi zo súboru
  $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)   
    } 

  # zapísať to do tabuľky
  $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() 
  }  

Na výber obsahu je potrebné robiť select podľa ID, vyberajúc v poradí n (identity).

V nasledujúcom článku sa podrobnejšie zameriam na to, ako to všetko interaguje s Jenkinsom.

Zdroj: habr.com

Kúpte si spoľahlivý hosting pre webové stránky s ochranou proti DDoS, VPS VDS servery 🔥 Kúpte si spoľahlivý hosting pre webové stránky s ochranou proti DDoS, VPS VDS servery | ProHoster