🥇Arbeiten mit MS SQL aus PowerShell unter Linux

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

Vorbereitend auf Zero Touch PROD für RDS (MS SQL), über den wir ständig informiert wurden, habe ich eine Präsentation (POC — Proof Of Concept) zur Automatisierung erstellt: eine Sammlung von PowerShell-Skripten. Nach der Präsentation, als die stürmischen, langanhaltenden Applauswellen in unaufhörliche Ovationen übergingen, wurde mir gesagt – das ist alles gut, aber aus ideologischen Gründen arbeiten unsere Jenkins-Slaves alle unter Linux!

Kann man das wirklich machen? Einen so warmherzigen, gemütlichen DBA von Windows nehmen und ihn ins kalte Wasser von PowerShell unter Linux werfen? Ist das nicht grausam?

Ich musste mich in diese seltsame Kombination von Technologien vertiefen. Natürlich hörten all meine 30+ Skripte auf zu funktionieren. Zu meinem Erstaunen gelang es mir, alles an einem Arbeitstag zu reparieren. Ich schreibe aus erster Hand. Also, welche Fallstricke könnten Ihnen beim Transfer von PowerShell-Skripten von Windows nach Linux begegnen?

sqlcmd vs Invoke-SqlCmd

Ich erinnere an den grundlegenden Unterschied zwischen ihnen. Das gute alte Utility sqlcmd läuft 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. Dabei sind die Dateinamen natürlich case-sensitive. Wenn Sie -i verwenden, schreiben Sie am Ende in die Datei:

GO
EXIT

Wenn am Ende kein EXIT steht, wird sqlcmd auf Eingabe warten, und wenn vor EXIT angezeigt werden GO, wird der letzte Befehl nicht ausgeführt. In die Ausgabedatei gelangt die gesamte Ausgabe, selects, Nachrichten, print usw.

Invoke-SqlCmd gibt das Ergebnis als DataSet, DataTables oder DataRows aus. Daher können Sie das Ergebnis eines einfachen selects auch über sqlcmd, wenn Sie dessen Ausgabe zerlegen, ist es praktisch unmöglich, etwas Komplexes auszugeben: dafür gibt es Invoke-SqlCmd. Aber dieses Kommando hat auch seine Eigenheiten:

Wenn Sie ihm eine Datei übergeben mit -InputFile, dann EXIT ist nicht nötig, vielmehr generiert es einen Syntaxfehler
-OutputFile nein, das Kommando gibt Ihnen das Ergebnis als Objekt zurück
Um den Server anzugeben, gibt es zwei Syntaxmöglichkeiten: -ServerInstance -Username -Password -Database und über -ConnectionString. Komischerweise können Sie im ersten Fall keinen Port angeben, der von 1433 abweicht.
textliche Ausgaben, wie PRINT, die ganz einfach "abgefangen" werden können, sqlcmd, für Invoke-SqlCmd stellen ein Problem dar.
Und das Wichtigste: Wahrscheinlich ist dieser Cmdlet in Ihrem Linux nicht verfügbar!

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

Variablenersetzung

In sqlcmd gibt es Platzhalter für Variablen 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

In einem SQL-Skript verwenden wir Platzhalter:

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

So ist es. In *nix funktionieren Platzhalter für Variablen nicht.. Parameter -v wird ignoriert. Bei Invoke-SqlCmd wird ignoriert -Variables. Obwohl der Parameter, der die Variablen definiert, ignoriert wird, funktionieren die Platzhalter – Sie können beliebige Variablen aus der Shell verwenden. Ich war jedoch frustriert über Variablen und wollte mich ganz von ihnen loslösen, daher habe ich es grob und primitiv gemacht. Zum Glück sind die SQL-Skripte kurz:

# 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, wie Sie verstanden haben, ist ein Test bereits mit der Unix-Version.

Datei-Uploads

In der Windows-Version begleitete mich jede Operation mit Audits: Ausführung von sqlcmd, Erhalt einer Fehlermeldung in der Ausgabedatei, Anheftung dieser Datei an die Audit-Tabelle. Glücklicherweise lief der SQL-Server auf demselben Server wie Jenkins, das wurde ungefähr so gemacht:

ERSTELLEN Sie das Verfahren AuditUpload
  @id int, @dateiname varchar(256)
AS
  set nocount on
  deklarieren Sie @sql varchar(max)

  ERSTELLEN SIE DIE TABELLE #multi (filer NVARCHAR(MAX))
  set @sql='BULK INSERT #multi FROM '''+@dateiname
    +''' MIT (ROWTERMINATOR = '' '',CODEPAGE = ''ACP'')'
  exec (@sql)
  wählen Sie @sql=filer von #multi
  aktualisieren Sie JenkinsAudit set multiliner=@sql WHERE ID=@id
  zurückgeben

Auf diese Weise speichern wir die BCP-Datei vollständig und fügen sie in das Feld nvarchar(max) der Audittabelle ein. Natürlich ist dieses System gescheitert, da ich anstelle von SQL Server RDS erhalten habe und BULK INSERT nicht über UNC funktioniert, weil es versucht, einen exklusiven Lock auf die Datei zu nehmen, was mit RDS von vornherein zum Scheitern verurteilt ist. Daher habe ich beschlossen, das Systemdesign zu ändern und die Audits zeilenweise zu speichern:

ERSTELLEN SIE DIE TABELLE AuditOut (
  ID int NULL,
  TextLine nvarchar(max) NULL,
  n int IDENTITY(1,1) PRIMARY KEY
  )

Und so schreiben Sie in diese Tabelle:

function WriteAudit([string]$Filename, [string]$ConnStr, 
     [string]$Tabname, [string]$Jobname)
{
  # last execution $lastid abridged 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 the 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 auszuwählen, muss man eine Abfrage nach ID durchführen, wobei man in der Reihenfolge n (Identity) auswählt.

In dem nächsten Artikel werde ich detaillierter darauf eingehen, wie alles mit Jenkins zusammenarbeitet.

Quelle: habr.com