Werken met MS SQL vanuit Powershell op Linux

Dit artikel is puur praktisch en is gewijd aan mijn trieste verhaal

Ter voorbereiding op Zero Touch PROD voor RDS (MS SQL), waar we de oren mee vol gepraat zijn, heb ik een presentatie (POC — Proof Of Concept) van automatisering gemaakt: een set van PowerShell-scripts. Na de presentatie, toen het tumultueuze, langdurige applaus, dat overging in niet aflatende ovaties, eindelijk was verstomd, werd me gezegd — dit is allemaal goed, maar om ideologische redenen draaien al onze Jenkins slaves onder Linux!

Is dat wel mogelijk? Zo'n warme, knusse DBA van Windows halen en hem in de meest verantwoordelijke PowerShell-uitvoering onder Linux stoppen? Is dat niet wreed?

Ik moest me verdiepen in deze vreemde combinatie van technologieën. Uiteraard stopten al mijn 30+ scripts met werken. Tot mijn verbazing is het me gelukt om alles in één werkdag te repareren. Ik schrijf dit direct na de feiten. Dus, wat voor obstakels kunnen er op uw pad komen bij het verplaatsen van PowerShell-scripts van Windows naar Linux?

sqlcmd vs Invoke-SqlCmd

Ik herinner me het belangrijkste verschil tussen hen. De oude goede tool sqlcmd werkt ook op Linux met vrijwel identieke functionaliteit. De vraag om uit te voeren geven we door met -Q, het inputbestand met -i en de output met -o. Maar, de bestandsnamen zijn vanzelfsprekend hoofdlettergevoelig. Als je -i gebruikt, schrijf dan aan het einde in het bestand:

GO
EXIT

Als er geen EXIT aan het einde staat, zal sqlcmd wachten op invoer, en als vóór EXIT de oorspronkelijke lege document vervangen door een ander document dat asynchroon is geladen, maar in plaats daarvan het load-evenement voor het oorspronkelijke document synchroon starten. GO, dan zal de laatste opdracht niet worden uitgevoerd. Alle output, selects, meldingen, print etc. worden in het uitvoerbestand geplaatst.

Invoke-SqlCmd geeft het resultaat terug als DataSet, DataTables of DataRows. Daarom, als je het resultaat van een eenvoudige select kan verwerken via sqlcmd, is het vrijwel onmogelijk om iets complex uit te voeren: daarvoor is er Invoke-SqlCmd. Maar deze opdracht heeft ook zijn eigenaardigheden:

• Als je een bestand doorgeeft via -InputFile, dan EXIT is niet nodig, bovendien geeft het een syntactische fout terug
• -OutputFile is niet, de opdracht geeft je resultaat terug als een object
• Om de server aan te geven, zijn er twee syntaxen: -ServerInstance -Username -Password -Database opent en via -ConnectionString. Vreemd genoeg, in het eerste geval is het niet mogelijk om een poort die niet 1433 is op te geven.
• de tekstoutput, zoals PRINT, die simpelweg 'gevangen' kan worden sqlcmdis er een wrapper: Invoke-SqlCmd is een probleem
• En belangrijk: waarschijnlijk is deze cmdlet niet op jouw Linux beschikbaar!

En dat is het grote probleem. Pas in maart werd deze cmdlet beschikbaar voor niet-Windows platforms, en eindelijk kunnen we verder!

Variabelen substitueren

In sqlcmd is variable substitution using -v, for example, like this:

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

In the SQL script, we use substitutions:

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

So, in *nix variable substitutions do not work. De parameter -v is ignored. In Invoke-SqlCmd wordt genegeerd -Variables. Although the parameter that defines the variables themselves is ignored, the substitutions work — you can use any variables from Shell. However, I was upset with the variables and decided not to depend on them at all, and acted roughly and primitively, fortunately, SQL scripts are short:

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

This, as you understood, is already a test with the Unix version.

File upload

In the Windows version, any operation was accompanied by auditing: we executed sqlcmd, received some error in the output file, and attached this file to the audit table. Fortunately, the SQL server was on the same server as Jenkins, it was done approximately like this:

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

Thus, we completely ingest the BCP file and stuff it into the nvarchar(max) field of the audit table. Naturally, this whole system fell apart, as instead of SQL server I got RDS, and BULK INSERT doesn't work at all via UNC due to the attempt to take an exclusive lock on the file, and with RDS that's initially doomed. So I decided to redesign the system, keeping the audit line by line:

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

And write to this table like this:

function WriteAudit([string]$Filename, [string]$ConnStr, 
     [string]$Tabname, [string]$Jobname)
{
  # get $lastid of the last execution  -- skipped for the article
	
  #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() 
  }  

To select the content, you need to do select by ID, selecting in order n (identity).

In the next article, I will elaborate more on how all this interacts with Jenkins.

Bron: habr.com