Att arbeta med MS SQL från Powershell på Linux

Den här artikeln är helt praktisk och handlar om min sorgliga historia

Inför Zero Touch PROD för RDS (MS SQL), om vilket vi fått höra allt för mycket, gjorde jag en presentation (POC – Proof Of Concept) för automatisering: en uppsättning powershell-skript. Efter presentationen, när de stormiga och långvariga applåderna avtog och övergick i oavbrutna ovationer, sa de till mig – allt detta är bra, men av ideologiska skäl arbetar alla våra Jenkins slaves på Linux!

Kan man verkligen göra så? Ta en så vänlig, ljus DBA från Windows och slänga in honom i det brinnande infernot av powershell på Linux? Är inte det grymt?

Jag var tvungen att dyka ner i denna märkliga kombination av teknologier. Självklart slutade alla mina 30+ skript att fungera. Till min förvåning lyckades jag under en arbetsdag fixa allt. Jag skriver medan jag fortfarande har det färskt i minnet. Så, vilka fallgropar kan ni stöta på när ni flyttar powershell-skript från Windows till Linux?

sqlcmd vs Invoke-SqlCmd

Låt mig påminna er om den grundläggande skillnaden mellan dem. Den gamla hederliga verktygen sqlcmd fungerar även på Linux, med nästan identisk funktionalitet. Frågan som ska utföras anger vi med -Q, indatafilen som -i och utdata som -o. Det enda är att filnamnen, förstås, blir skiftlägeskänsliga. Om du använder -i, skriv i slutet av filen:

GO
EXIT

Om slutet inte har EXIT, så kommer sqlcmd att vänta på inmatning, och om innan EXIT inte GO, så kommer sista kommandot inte att köras. All utdata, selects, meddelanden, print och så vidare går till utdatafilen.

Invoke-SqlCmd ger resultatet som DataSet, DataTables eller DataRows. Så om du kan behandla resultatet av en enkel select, kan du göra det även genom sqlcmd, men att få ut något mer komplicerat är nästan omöjligt: det är det Invoke-SqlCmd. Men den här kommandot har sina egna knep:

• Om du skickar en fil via -InputFile, så EXIT är inte nödvändigt, dessutom ger det en syntaktisk fel
• -OutputFile nej, kommandot ger dig resultatet som ett objekt
• För att ange server finns det två syntaxer: -ServerInstance -Username -Password -Database och via -ConnectionString. Konstigt nog kan vi inte ange en port som skiljer sig från 1433 i första fallet.
• Textutdata, såsom PRINT, som enkelt kan "fångas" sqlcmd, för Invoke-SqlCmd är ett problem
• Och viktigast av allt: det är troligt att detta cmdlet inte finns i din Linux!

Och det är det största problemet. Först i mars blev detta cmdlet tillgängligt för icke-windows plattformar, och äntligen kan vi gå framåt!

Variabelersättning

I sqlcmd finns det variabelinmatning via -v, till exempel så här:

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

I ett SQL-skript använder vi inmatningar:

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

Så här är det. I *nix fungerar inte variabelinmatningar. Parametern -v ignoreras. Hos Invoke-SqlCmd ignoreras -Variabler. Även om parametern som definierar variablerna ignoreras, fungerar inmatningarna — du kan använda vilka variabler som helst från Shell. Men jag blev besviken på variablerna och bestämde mig för att helt avstå från dem, och gjorde det enkelt och primitivt, tack och lov är SQL-skript korta:

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

Det här, som du förstår, är ett test med den unix-versionen.

Filuppladdning

I Windows-versionen följdes varje operation av en granskning: vi körde sqlcmd, fick något klagomål i output-filen, fäste den filen till granskningstabellen. Tack och lov körde SQL-servern på samma server som Jenkins, så här gjordes det:

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

Således laddar vi hela BCP-filen och placera den i nvarchar(max)-fältet i granskningsbordet. Naturligtvis kollapsade hela detta system eftersom jag istället för SQL-server fick RDS, och BULK INSERT fungerar överhuvudtaget inte via UNC på grund av försöket att ta en exklusiv lås på filen, och med RDS är det överhuvudtaget från början dömt att misslyckas. Så jag beslutade mig för att förändra systemdesignen, och lagra granskning rad för rad:

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

Och skriva till denna tabell så här:

function WriteAudit([string]$Filename, [string]$ConnStr, 
     [string]$Tabname, [string]$Jobname)
{
  # hämta $lastid från senaste körning  -- hoppar över för artikeln
	
  # skapa grid och fylla den med data från fil
  $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)   
    } 

  # skriv till tabellen
  $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() 
  }  

För att hämta innehållet måste man göra en select baserat på ID, i ordning n (identity).

I nästa artikel kommer jag att gå mer i detalj på hur allt detta interagerar med Jenkins.

Källa: habr.com