🥇Práce s MS SQL z Powershell na Linuxu

Tento článek je ryze praktický a věnuje se mé smutné historii

Při přípravě na Zero Touch PROD pro RDS (MS SQL), o kterém nám všichni neustále povídali, jsem vytvořil prezentaci (POC — Proof Of Concept) automatizace: sady skriptů PowerShell. Po prezentaci, kdy utichly bouřlivé, dlouhé potlesky, přecházející v neutuchající ovace, mi bylo řečeno — všechno je dobré, ale z ideologických důvodů máme všechny Jenkins slavy běžící pod Linuxem!

Jak je to možné? Vzít tak teplého, útulného DBA z Windows a strčit ho do samého pekla PowerShell pod Linux? Není to kruté?

Bylo nutné se ponořit do této podivné kombinace technologií. Samozřejmě, že všech mých 30+ skriptů přestalo fungovat. K mému překvapení se mi vše podařilo opravit během jednoho pracovního dne. Píšu to v horké chvíli. Jaké úskalí se vám mohou objevit při přesunu skriptů PowerShell z Windows do Linuxu?

sqlcmd vs Invoke-SqlCmd

Připomínám hlavní rozdíl mezi nimi. Starý dobrý nástroj sqlcmd funguje i pod Linuxem, s téměř identickou funkcionalitou. Dotaz se vykonává pomocí -Q, vstupní soubor jako -i, a výstup -o. Jen názvy souborů pochopitelně musí být case-sensitive. Pokud použijete -i, tak v souboru napište na konci:

GO
EXIT

Pokud na konci nebude EXIT, sqlcmd přejde do režimu čekání na vstup, a pokud před EXIT nebude GO, poslední příkaz se neprovede. Do výstupního souboru se dostane celý výstup, selects, zprávy, print apod.

Invoke-SqlCmd vrací výsledek ve formě DataSet, DataTables nebo DataRows. Takže, pokud můžete zpracovat výsledek jednoduchého selectu i přes sqlcmd, a rozebrat jeho výstup, tak vypsat něco složitého je prakticky nemožné: k tomu existuje Invoke-SqlCmd. Ale tato příkaz má své vlastní zvláštnosti:

Pokud jí předáte soubor přes -InputFile, pak EXIT není potřeba, navíc, vygeneruje syntaktickou chybu
-OutputFile ne, příkaz vám vrátí výsledek ve formě objektu
Pro určení serveru existují dva syntaxy: -ServerInstance -Username -Password -Database a přes -ConnectionString. Jak je zvláštní, v prvním případě nelze zadat port jiný než 1433.
textový výstup, typu PRINT, který se jednoduše "chytá" sqlcmd, pro Invoke-SqlCmd je problém
A hlavně: pravděpodobně ve vašem Linuxu tento cmdlet není!

A to je hlavní problém. Až v březnu se tento cmdlet stal dostupným pro platformy mimo Windows, a konečně se můžeme posunout dál!

Náhrada proměnných

Ve sqlcmd je substituce 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 v SQL používáme zástupné znaky:

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

Tak tedy. V *nix zástupné znaky proměnných nefungují. Parametr -v je ignorováno. U Invoke-SqlCmd je ignorována -Proměnné. Ačkoli parametr, který určuje samotné proměnné, je ignorován, samotné zástupné znaky fungují — můžete použít jakékoli proměnné z Shell. Rozhodl jsem se však, že se na proměnné nebudu spoléhat vůbec, a to jsem udělal tak hrubě a jednoduše, naštěstí jsou skripty v SQL 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, jak jste pochopili, je test již s unixovou verzí.

Nahrávání souborů

Ve verzi pro Windows byla každá operace doprovázena auditem: vykonali jsme sqlcmd, dostali jsme nějaké chyby v output file, přiložili jsme tento soubor k tabulce auditu. Naštěstí SQL server fungoval na stejném serveru jako Jenkins, dělalo se to 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

Tímto způsobem načítáme soubor BCP celý, a vkládáme ho do pole nvarchar(max) tabulky auditu. Samozřejmě, celý tento systém se rozpadl, 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, a s RDS to bylo od začátku odsouzeno. Takže jsem se rozhodl změnit design systému, ukládat 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ískejte $lastid posledního vykonání  -- přeskočeno pro článek
	
  # vytvořte grid a naplňte ho 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 vybírání obsahu je třeba provést select podle ID, vybírat ve sledu n (identity).

V dalším článku se podrobněji zaměřím na to, jak to celé spolupracuje s Jenkins.

Zdroj: habr.com