Arbeiten mit MS SQL von Powershell unter Linux

Dieser Artikel ist rein praktischer Natur und meiner traurigen Geschichte gewidmet

Bereit machen für Zero Touch PROD Für RDS (MS SQL), von dem alle Ohren schwärmten, habe ich eine Präsentation (POC – Proof Of Concept) der Automatisierung gemacht: eine Reihe von Powershell-Skripten. Nach der Präsentation, als der stürmische, anhaltende Applaus nachließ und sich in unaufhörlichen Applaus verwandelte, sagten sie mir: Das ist alles gut, aber nur aus ideologischen Gründen, alle unsere Jenkins-Sklaven arbeiten unter Linux!

Ist das möglich? Nehmen Sie einen so warmen Lampen-DBA unter Windows und stecken Sie ihn in die heiße Powershell unter Linux? Ist das nicht grausam?

Ich musste in diese seltsame Kombination von Technologien eintauchen. Natürlich funktionierten alle meine über 30 Skripte nicht mehr. Zu meiner Überraschung gelang es mir, alles an einem Arbeitstag zu reparieren. Ich schreibe in heißer Verfolgungsjagd. Auf welche Fallstricke können Sie also stoßen, wenn Sie Powershell-Skripte von Windows auf Linux übertragen?

sqlcmd vs. Invoke-SqlCmd

Ich möchte Sie an den Hauptunterschied zwischen ihnen erinnern. Gutes altes Dienstprogramm sqlcmd Es funktioniert auch unter Linux, mit nahezu identischer Funktionalität. Wir übergeben die auszuführende Abfrage -Q, die Eingabedatei als -i und die Ausgabe als -o. Aber bei den Dateinamen wird natürlich die Groß-/Kleinschreibung beachtet. Wenn Sie -i verwenden, schreiben Sie am Ende in die Datei:

GO
EXIT

Wenn es am Ende keinen EXIT gibt, wartet sqlcmd weiter auf die Eingabe, und wenn schon vorher EXIT wird nicht GO, dann funktioniert der letzte Befehl nicht. Die Ausgabedatei enthält alle Ausgaben, Auswahlen, Nachrichten, Drucke usw.

Invoke-SqlCmd erzeugt das Ergebnis als DataSet, DataTables oder DataRows. Wenn Sie also das Ergebnis einer einfachen Auswahl verarbeiten, können Sie es verwenden sqlcmdNach der Analyse seiner Ausgabe ist es fast unmöglich, etwas Komplexes abzuleiten: Dafür gibt es Aufruf-SqlCmd. Aber dieses Team hat auch seine eigenen Witze:

• Wenn Sie ihr eine Datei über übertragen -Eingabedateidann EXIT nicht erforderlich, außerdem führt es zu einem Syntaxfehler
• -Ausgabedatei Nein, der Befehl gibt Ihnen das Ergebnis als Objekt zurück
• Es gibt zwei Syntaxen zum Angeben eines Servers: -ServerInstanz -Benutzername -Passwort -Datenbank Und durch -ConnectionString. Seltsamerweise ist es im ersten Fall nicht möglich, einen anderen Port als 1433 anzugeben.
• Textausgabe, Typ PRINT, der einfach „abgefangen“ wird sqlcmdfür Aufruf-SqlCmd ist ein Problem
• Und vor allem: Höchstwahrscheinlich verfügt Ihr Linux nicht über dieses Cmdlet!

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

Variablensubstitution

sqlcmd verfügt über 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 Ersetzungen:

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

Also. In *nix Variablenersetzungen funktionieren nicht. Parameter -v ignoriert. U Aufruf-SqlCmd ignoriert -Variablen. Obwohl der Parameter, der die Variablen selbst angibt, ignoriert wird, funktionieren die Ersetzungen selbst – Sie können beliebige Variablen aus Shell verwenden. Ich war jedoch von den Variablen beleidigt und beschloss, mich überhaupt nicht auf sie zu verlassen, und verhielt mich unhöflich und primitiv, da die SQL-Skripte 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"

Dies ist, wie Sie wissen, ein Test bereits aus der Unix-Version.

Hochladen von Dateien

In der Windows-Version wurde jeder Vorgang von einer Prüfung begleitet: Wir führten sqlcmd aus, erhielten eine Art Missbrauch in der Ausgabedatei und hängten diese Datei an die Prüfplatte an. Glücklicherweise funktionierte der SQL-Server auf demselben Server wie Jenkins. Dies geschah in etwa so:

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

Daher schlucken wir die BCP-Datei vollständig und schieben sie in das Feld nvarchar(max) der Audit-Tabelle. Natürlich ist das ganze System auseinandergefallen, denn anstelle eines SQL-Servers habe ich RDS bekommen, und BULK INSERT funktioniert über UNC überhaupt nicht, da versucht wird, eine exklusive Sperre für eine Datei zu übernehmen, und mit RDS ist dies im Allgemeinen zum Scheitern verurteilt ganz am Anfang. Deshalb habe ich beschlossen, das Systemdesign zu ändern und die Prüfung Zeile für Zeile zu speichern:

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

Und schreiben Sie in diese Tabelle so:

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() 
  }  

Um Inhalte auszuwählen, müssen Sie nach ID auswählen und in der Reihenfolge n (Identität) auswählen.

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

Source: habr.com