ProHoster > Blog > Administration > Arbeiten mit MS SQL aus Powershell unter Linux

Arbeiten mit MS SQL aus Powershell unter Linux

Dieser Artikel ist rein praktisch und erzählt meine traurige Geschichte

In Vorbereitung auf Zero Touch PROD für RDS (MS SQL), über den wir alle Ohren vollgeredet haben, habe ich eine Präsentation (POC — Proof Of Concept) zur Automatisierung gemacht: eine Sammlung von PowerShell-Skripten. Nach der Präsentation, als der stürmische, langanhaltende Applaus, der in unaufhörlichen Ovationen überging, verklungen war, sagte man mir — das ist alles gut, aber aus ideologischen Gründen laufen alle unsere Jenkins-Slaves unter Linux!

Kann man das wirklich so machen? Einen so warmen, gemütlichen DBA von Windows zu nehmen und ihn mitten in die Hölle von PowerShell unter Linux zu stecken? Ist das nicht grausam?

Arbeiten mit MS SQL aus Powershell unter Linux
Ich musste mich in diese seltsame Kombination von Technologien vertiefen. Natürlich hörten alle meine 30+ Skripte auf zu funktionieren. Zu meiner Überraschung gelang es mir an einem Arbeitstag, alles zu beheben. Ich schreibe noch im Eifer des Gefechts. Also, welche Fallstricke könnten Ihnen beim Transfer von PowerShell-Skripten von Windows zu Linux begegnen?

sqlcmd vs Invoke-SqlCmd

Ich erinnere an den grundlegenden Unterschied zwischen ihnen. Das alte, bewährte Tool sqlcmd funktioniert auch unter Linux mit nahezu identischer Funktionalität. Wir übergeben die Abfrage zur Ausführung mit -Q, die Eingabedatei als -i und die Ausgabe als -o. Allerdings sind die Dateinamen, wie zu erwarten, case-sensitive. Wenn Sie -i verwenden, fügen Sie am Ende der Datei hinzu:

GO
EXIT

Wenn am Ende kein EXIT steht, erwartet sqlcmd Eingaben, und wenn vor dem EXIT Informationen GO, wird der letzte Befehl nicht ausgeführt. In die Ausgabedatei gelangt die gesamte Ausgabe, Selektierungen, Meldungen, Druck usw.

Invoke-SqlCmd gibt das Ergebnis als DataSet, DataTables oder DataRows aus. Daher können Sie das Ergebnis eines einfachen Select über sqlcmd, indem Sie seine Ausgabe analysieren, verarbeiten, während es nahezu unmöglich ist, etwas Komplexes auszugeben: dazu gibt es Invoke-SqlCmd. Aber auch bei diesem Befehl gibt es einige Eigenheiten:

  • Wenn Sie ihm eine Datei übergeben, -InputFile, dann EXIT ist nicht erforderlich, vielmehr gibt er einen Syntaxfehler zurück.
  • -OutputFile nein, der Befehl gibt Ihnen das Ergebnis als Objekt zurück.
  • Für die Angabe des Servers gibt es zwei Syntaxen: -ServerInstance -Username -Password -Database und über -ConnectionString. Seltsamerweise lässt sich im ersten Fall kein Port angeben, der von 1433 abweicht.
  • Der textliche Output, wie PRINT, der einfach «gefangen» werden kann, sqlcmd, für Invoke-SqlCmd stellt ein Problem dar.
  • Und das Wichtigste: Wahrscheinlich gibt es diesen Cmdlet in Ihrem Linux nicht!

Und das ist das Hauptproblem. Erst im März wurde dieser Cmdlet für Nicht-Windows-Plattformen verfügbar., und endlich können wir vorankommen!

Variablenersetzung

In sqlcmd gibt es eine Variablenersetzung mit -v, zum Beispiel so:

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

Im SQL-Skript verwenden wir Platzhalter:

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

So. In *nix funktioniert die Variablenersetzung nicht. Der Parameter -v wird ignoriert. Bei Invoke-SqlCmd wird ignoriert -Variables. Obwohl der Parameter, der die Variablen selbst festlegt, ignoriert wird, funktioniert die Ersetzung — Sie können jede Variable aus der Shell verwenden. Ich habe mich jedoch von den Variablen abgewandt und beschlossen, nicht von ihnen abhängig zu sein, und bin grob und primitiv vorgegangen, da die SQL-Skripts kurz sind:

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

Das ist, wie Sie verstanden haben, ein Test bereits mit der Unix-Version.

Datei-Upload

In der Windows-Version war jede Operation mit einem Audit verbunden: sqlcmd ausgeführt, irgendeinen Fehler in der Ausgabedatei erhalten, diese Datei zur Audit-Tabelle hinzugefügt. Glücklicherweise lief der SQL-Server auf demselben Server wie Jenkins, das wurde etwa so gemacht:

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

Auf diese Weise laden wir die BCP-Datei vollständig und stecken sie in das Feld nvarchar(max) der Audit-Tabelle. Natürlich ist dieses System gescheitert, denn anstelle des SQL-Servers erhielt ich RDS, und BULK INSERT funktioniert über UNC nicht, da versucht wird, ein exklusives Lock auf die Datei zu nehmen, und mit RDS ist das von Anfang an zum Scheitern verurteilt. Daher habe ich beschlossen, das System-Design zu ändern, indem ich das Audit zeilenweise speichere:

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

Und wir schreiben in diese Tabelle so:

function WriteAudit([string]$Filename, [string]$ConnStr, 
     [string]$Tabname, [string]$Jobname)
{
  # get $lastid of the last execution  -- für den Artikel übersprungen
	
  #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() 
  }

Um den Inhalt zu wählen, muss ein select nach ID in der Reihenfolge n (identity) durchgeführt werden.

Im nächsten Artikel werde ich genauer darauf eingehen, wie das alles mit Jenkins interagiert.

Quelle: habr.com

60GB SSD 8Gb DDR4