ProHoster > Blog > Administrácia > Práca s MS SQL z Powershell na Linuxe

Práca s MS SQL z Powershell na Linuxe

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 nám hučalo v ušiach, som urobil prezentáciu (POC - Proof Of Concept) automatizácie: sadu powershell skriptov. Po prezentácii, keď búrlivý, dlhotrvajúci potlesk utíchol a zmenil sa na neprestajný potlesk, mi povedali – to všetko je dobré, ale len z ideologických dôvodov, všetci naši otroci Jenkins pracujú na Linuxe!

Je to možné? Zobrať takú teplú, lampovú DBA z Windowsu a strčiť ju do horúčavy pod Linuxom? 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šetkých mojich 30+ skriptov prestalo fungovať. Na moje prekvapenie sa mi všetko podarilo opraviť za jeden pracovný deň. Píšem v horúčave. S akými úskaliami sa teda môžete stretnúť pri prenose powershell skriptov z Windowsu do Linuxu?

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

Pridať komentár