🥇Práca s MS SQL od Powershellu Linux

Tento článok je čisto praktický a venuje sa môjmu smutnému príbehu

Príprava na Zero Touch PROD Pre RDS (MS SQL), o ktorom všetci hovorili, som urobil prezentáciu (POC - Proof of Concept) o automatizácii: sadu PowerShell skriptov. Po prezentácii, keď búrlivý, dlhotrvajúci potlesk, ktorý sa zmenil na standing ovation, utíchol, mi povedali - to je všetko pekné, ale z ideologických dôvodov všetci naši Jenkinsovci fungujú pod... Linux!

Je to vôbec možné? Vziať taký teplý, trubicovitý DBA zdola... Windows a vpichnite ho do veľmi hrubej vrstvy PowerShellu LinuxNie je to kruté?

Musel som sa ponoriť do tejto zvláštnej kombinácie technológií. Prirodzene, všetkých 30+ mojich skriptov prestalo fungovať. Na moje prekvapenie sa mi podarilo všetko opraviť za jeden pracovný deň. Píšem to, kým sú správy ešte čerstvé. Aké úskalia teda môžete naraziť pri migrácii PowerShell skriptov z... Windows pod Linux?

sqlcmd vs Invoke-SqlCmd

Pripomeniem vám hlavný rozdiel medzi nimi. Stará dobrá utilita sqlcmd Funguje aj pod Linuxom, s takmer identickou funkcionalitou. Dotaz odošleme na vykonanie -Q, vstupný súbor ako -i a výstup ako -o. Ale názvy súborov, samozrejme, rozlišujú veľké a malé písmená. Ak použijete -i, potom do súboru napíšte na koniec:

GO
EXIT

Ak na konci nie je EXIT, potom sqlcmd bude čakať na vstup, a ak predtým EXIT nebude GO, potom posledný príkaz nebude fungovať. Výstupný súbor obsahuje všetok výstup, výbery, správy, tlač atď.

Invoke-SqlCmd vytvorí výsledok ako DataSet, DataTables alebo DataRows. Preto, ak spracujete výsledok jednoduchého výberu, môžete použiť sqlcmdPo analýze jeho výstupu je takmer nemožné odvodiť niečo zložité: na to existuje Vyvolať-SqlCmd. Ale aj tento tím má svoje vtipy:

Ak jej prenesiete súbor cez - Vstupný súbor, Potom EXIT nie je potrebný, navyše vytvára chybu syntaxe
-Výstupný súbor nie, príkaz vám vráti výsledok ako objekt
Na určenie servera existujú dve syntaxe: -ServerInstance -Username -Password -Database a cez -ConnectionString. Napodiv, v prvom prípade nie je možné zadať iný port ako 1433.
textový výstup, zadajte PRINT, ktorý sa jednoducho „chytí“ sqlcmdpre Vyvolať-SqlCmd je problém
A hlavne: Váš Linux s najväčšou pravdepodobnosťou tento cmdlet nemá!

A toto je hlavný problém. Iba v marci tohto cmdlet sa stal dostupným pre platformy iné ako Windowsa konečne sa môžeme pohnúť vpred!

Variabilná substitúcia

sqlcmd má variabilnú substitúciu 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 substitúcie:

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

Takže tu to je. V *nix variabilné substitúcie nefungujú. Parameter -v ignoroval. U Vyvolať-SqlCmd ignoroval -Premenné. Hoci parameter, ktorý špecifikuje samotné premenné, je ignorovaný, samotné substitúcie fungujú – môžete použiť akékoľvek premenné zo Shell. Premenné ma však urazili a rozhodol som sa na nich vôbec nezávisieť a správal som sa hrubo a primitívne, keďže SQL skripty 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ž z verzie Unix.

Odovzdávanie súborov

Vo verzii Windows bola každá operácia sprevádzaná auditom: spustili sme sqlcmd, vo výstupnom súbore sme dostali nejaký druh zneužitia, pripojili sme tento súbor k auditnej doske. Našťastie SQL server pracoval na rovnakom serveri ako Jenkins, bolo to urobené 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

Preto úplne prehltneme súbor BCP a vložíme ho do poľa nvarchar(max) v tabuľke auditu. Samozrejme, celý tento systém sa rozpadol, pretože namiesto SQL servera som dostal RDS a BULK INSERT cez UNC vôbec nefunguje kvôli pokusu o exkluzívny zámok na súbor a s RDS je to vo všeobecnosti odsúdené na zánik. úplný začiatok. Preto som sa rozhodol zmeniť dizajn systému a uložiť audit riadok po riadku:

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

A zapíšte do tejto tabuľky takto:

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

Ak chcete vybrať obsah, musíte vybrať podľa ID v poradí n (identita).

V ďalšom článku pôjdem podrobnejšie o tom, ako to všetko interaguje s Jenkinsom.

Zdroj: hab.com