🥇Delo z MS SQL iz Powershell-a na Linuxu

Ta članek je povsem praktičen in posvečen moji žalostni zgodbi.

Ob pripravi na Zero Touch PROD za RDS (MS SQL), o katerem so nam govorili vsi, sem pripravil predstavitev (POC — Proof Of Concept) avtomatizacije: nabor powershell skript. Po predstavitvi, ko so že utišali burni, dolgotrajni aplavz, ki se je prelevil v neprekinjene ovacije, so mi rekli — vse to je dobro, a zaradi ideoloških razlogov vsi naši Jenkins slaves delujejo pod Linuxom!

Ali je to sploh mogoče? Vzeti takega prijaznega DBA iz Windows in ga potisniti v sama pekla powershell pod Linuxom? Ali to ni kruto?

Moral sem se potopiti v to čudno kombinacijo tehnologij. Seveda so vse moje skripte 30+ prenehale delovati. Na moje presenečenje mi je v enem delovnem dnevu uspelo vse popraviti. Pišem takoj po dogodku. Torej, katerih pasti se lahko srečate pri prenosu powershell skriptov iz Windows na Linux?

sqlcmd proti Invoke-SqlCmd

Spomnim se osnovne razlike med njima. Stara dobra pripomoček sqlcmd deluje tudi pod Linuxom, z skoraj identično funkcionalnostjo. Pošiljamo poizvedbo za izvajanje z -Q, vhodno datoteko kot -i, in izhod -o. Samo imena datotek so seveda občutljiva na velike in male črke. Če uporabljate -i, potem v datoteki na koncu napišite:

GO
EXIT

Če na koncu ne bo EXIT, bo sqlcmd prešel v način pričakovanja vnosa, in če je pred EXIT не будет GO, zadnja ukaz ne bo izvedena. V izhodno datoteko pride celoten izhod, selects, sporočila, print itd.

Invoke-SqlCmd vrne rezultat v obliki DataSet, DataTables ali DataRows. Zato, če lahko obdelate rezultat enostavnega select preko sqlcmd, zahtevka, potem praktično ni mogoče izvesti nekaj zapletenega: za to obstaja Invoke-SqlCmd. Ampak ta ukaz ima tudi svoje posebnosti:

Če ji posredujete datoteko preko -InputFile, potem EXIT ni potrebno, poleg tega, da daje sintaktično napako
-OutputFile ni, ukaz vam vrne rezultat v obliki objekta.
Za določitev strežnika obstajata dva sintaktična načina: -ServerInstance -Username -Password -Database in preko -ConnectionString. Presenetljivo, v prvem primeru ne morete navesti vrat, ki ni 1433.
Besedilni izhod, tipa PRINT, ki se ga preprosto "ujame" sqlcmd, za Invoke-SqlCmd je težava.
In glavna stvar: verjetno v vašem Linuxu tega cmdleta ni!

In to je največja težava. Šele marca je ta cmdlet postal na voljo za ne-windows platforme, in končno lahko nadaljujemo naprej!

Nadomeščanje spremenljivk.

V sqlcmd je možno používat substituci proměnných pomocí -v, například takto:

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

Ve skriptu SQL používáme substituce:

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

Takže. V *nix substituce proměnných nefungují. Parameter -v je ignorováno. U Invoke-SqlCmd se ignoriše -Variables. Ačkoli se parametr, který nastavuje samotné proměnné, ignoruje, substituce fungují — můžete používat jakékoli proměnné ze Shellu. Nicméně jsem se na proměnné naštval a rozhodl se na nich úplně nezáviset, zvolil jsem hrubší a primitivnější přístup, protože skripty SQL jsou krátké:

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

To je, jak jste pochopili, test již z unixové verze.

Nahrávání souborů

Ve verzi pro Windows mi každá operace podléhala auditu: spustili jsme sqlcmd, obdrželi nějakou chybu do výstupního souboru, přiložili tuto chybovou zprávu k auditní tabulce. Naštěstí SQL server běžel na stejném serveru jako Jenkins, takže se to provádělo přibližně 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 přejímáme celý soubor BCP a vkládáme ho do pole nvarchar(max) tabulky auditu. Samozřejmě, celé toto řešení se zhroutilo, protože místo SQL serveru jsem dostal RDS, a BULK INSERT vůbec nefunguje přes UNC kvůli pokusu o získání exkluzivního zámku na soubor, což je s RDS od začátku odsouzeno k neúspěchu. Proto jsem se rozhodl změnit návrh systému a uchovávat audit po řádcích:

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

A zapisovat do této tabulky takto:

function WriteAudit([string]$Filename, [string]$ConnStr, 
     [string]$Tabname, [string]$Jobname)
{
  # získání $lastid posledního provedení  -- vynecháno pro článek
	
  # vytvoření mřížky a naplnění jej daty ze souboru
  $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)   
    } 

  # zapište to do tabulky
  $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() 
  }

Pro výběr obsahu je nutné provést select podle ID, vybírat v pořadí n (identity).

V následujícím článku se podrobněji podívám na to, jak to vše interaguje s Jenkins.

Izvor: habr.com