ProHoster > Blog > podávání > Práce s MS SQL z Powershell na Linuxu

Práce s MS SQL z Powershell na Linuxu

Tento článek je ryze praktický a je věnován mému smutnému příběhu

Příprava na Zero Touch PROD pro RDS (MS SQL), o kterém nám hučelo v uších, jsem udělal prezentaci (POC - Proof Of Concept) automatizace: sadu powershellových skriptů. Po prezentaci, když bouřlivý, dlouhotrvající potlesk utichl a změnil se v neustálý potlesk, mi řekli - to všechno je dobré, ale pouze z ideologických důvodů, všichni naši otroci Jenkins pracují na Linuxu!

Je to možné? Vzít takový teplý, lampový DBA z Windows a strčit ho do samotného žáru powershell pod Linuxem? Není to kruté?

Práce s MS SQL z Powershell na Linuxu
Musel jsem se ponořit do této podivné kombinace technologií. Samozřejmě 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 v horlivém pronásledování. Na jaká úskalí tedy můžete narazit při přenosu powershell skriptů z Windows do Linuxu?

sqlcmd vs Invoke-SqlCmd

Dovolte mi, abych vám připomněl hlavní rozdíl mezi nimi. Stará dobrá utilita sqlcmd Funguje také pod Linuxem, s téměř identickými funkcemi. Dotaz předáme k provedení -Q, vstupní soubor jako -i a výstup jako -o. Ale v názvech souborů se samozřejmě rozlišují velká a malá písmena. Pokud použijete -i, pak do souboru napište na konec:

GO
EXIT

Pokud na konci není EXIT, pak sqlcmd bude čekat na vstup, a pokud předtím EXIT nebude GO, pak poslední příkaz nebude fungovat. Výstupní soubor obsahuje veškerý výstup, výběry, zprávy, tisk atd.

Invoke-SqlCmd vytvoří výsledek jako DataSet, DataTables nebo DataRows. Pokud tedy zpracujete výsledek jednoduchého výběru, můžete použít sqlcmdPo analýze jeho výstupu je téměř nemožné odvodit něco složitého: pro to existuje Invoke-SqlCmd. Ale tento tým má také své vlastní vtipy:

  • Pokud jí přenesete soubor přes -Vložte souborpak EXIT není potřeba, navíc vytváří chybu syntaxe
  • -Výstupní soubor ne, příkaz vám vrátí výsledek jako objekt
  • Existují dvě syntaxe pro specifikaci serveru: -ServerInstance -Username -Password -Database a skrz -ConnectionString. Kupodivu v prvním případě není možné zadat jiný port než 1433.
  • textový výstup, zadejte PRINT, který je jednoduše „chycen“ sqlcmdpro Invoke-SqlCmd je problém
  • A nejdůležitějsí: Váš Linux s největší pravděpodobností tuto rutinu nemá!

A to je hlavní problém. Pouze v březnu tohoto cmdlet se stal dostupným pro platformy jiné než Windowsa konečně se můžeme pohnout vpřed!

Variabilní substituce

sqlcmd má proměnnou substituci 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 tady to je. V *nix variabilní substituce nefungují. Parametr -v ignoroval. U Invoke-SqlCmd ignoroval -Proměnné. Přestože parametr, který specifikuje samotné proměnné, je ignorován, samotné substituce fungují – můžete použít libovolné proměnné z prostředí Shell. Proměnné mě však urazily a rozhodl jsem se na nich nezáviset vůbec a choval jsem se hrubě a primitivně, protože SQL skripty 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"

Jak jste pochopili, toto je test již z verze Unix.

Nahrávání souborů

Ve verzi pro Windows byla každá operace doprovázena auditem: spustili jsme sqlcmd, ve výstupním souboru se objevilo nějaké zneužití, tento soubor jsme připojili k auditnímu štítku. Naštěstí SQL server fungoval na stejném serveru jako Jenkins, udělalo se to asi 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

Soubor BCP tedy zcela spolkneme a vložíme ho do pole nvarchar(max) auditní tabulky. Celý tento systém se samozřejmě rozpadl, protože místo SQL serveru jsem dostal RDS a BULK INSERT přes UNC vůbec nefunguje kvůli pokusu vzít exkluzivní zámek na soubor a u RDS je to obecně odsouzeno k záhubě. samý začátek. Rozhodl jsem se tedy změnit design systému a uložit audit řádek po řádku:

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

A napište do této tabulky 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() 
  }

Chcete-li vybrat obsah, musíte vybrat podle ID a vybrat v pořadí n (identita).

V příštím článku půjdu podrobněji o tom, jak to vše souvisí s Jenkinsem.

Zdroj: www.habr.com

Přidat komentář