Travailler avec MS SQL à partir de Powershell sur Linux

Cet article est purement pratique et consacré à mon histoire triste.

En préparant Zero Touch PROD pour RDS (MS SQL), dont nous avons beaucoup entendu parler, j'ai réalisé une présentation (POC — Proof Of Concept) d'automatisation : un ensemble de scripts PowerShell. Après la présentation, lorsque les applaudissements bruyants et prolongés se sont tus, devenant des ovations incessantes, on m'a dit — tout cela est très bien, mais pour des raisons idéologiques, tous nos esclaves Jenkins fonctionnent sous Linux !

Est-ce vraiment possible ? De prendre un DBA chaleureux et convivial sous Windows et de l'envoyer dans le monde impitoyable de PowerShell sous Linux ? N'est-ce pas cruel ?

Il a fallu que je plonge dans cette étrange combinaison de technologies. Bien sûr, tous mes 30+ scripts ont cessé de fonctionner. À ma grande surprise, en une journée de travail, j'ai réussi à tout corriger. J'écris dans le feu de l'action. Alors, quels pièges pourriez-vous rencontrer lors du transfert de scripts PowerShell de Windows à Linux ?

sqlcmd vs Invoke-SqlCmd

Je rappelle la principale différence entre eux. La vieille et bonne utilitaire sqlcmd fonctionne également sous Linux, avec presque la même fonctionnalité. Nous transmettons la requête à exécuter via -Q, le fichier d'entrée par -i, et la sortie par -o. Cependant, les noms de fichiers sont, bien sûr, sensibles à la casse. Si vous utilisez -i, assurez-vous d'écrire à la fin du fichier :

GO
EXIT

Si EXIT n'est pas présent à la fin, sqlcmd attendra une entrée, et si avant EXIT il n'y a pas GO, la dernière commande ne s'exécutera pas. La sortie dans le fichier contient tout, y compris les selects, les messages, les print, etc.

Invoke-SqlCmd renvoie le résultat sous forme de DataSet, DataTables ou DataRows. Donc, si vous pouvez traiter le résultat d'un simple select avec sqlcmd, en analysant sa sortie, il est presque impossible d'extraire quelque chose de complexe : pour cela, il existe Invoke-SqlCmd. Mais cette commande a aussi ses particularités :

• Si vous lui transmettez un fichier via -InputFile, alors EXIT , cela n'est pas nécessaire, de plus, cela génère une erreur de syntaxe.
• -OutputFile non, la commande vous renvoie le résultat sous forme d'objet.
• Pour spécifier le serveur, il y a deux syntaxes : -ServerInstance -Username -Password -Database et via -ConnectionString. Étrangement, dans le premier cas, il n'est pas possible d'indiquer un port différent de 1433.
• La sortie texte, comme PRINT, qui est facilement 'attrapée', sqlcmd, pour Invoke-SqlCmd pose problème.
• Et surtout : il est probable que ce cmdlet n'existe pas dans votre Linux !

Et c'est le principal problème. Ce cmdlet est devenu accessible pour les plateformes non-Windows seulement en mars,, et enfin, nous pouvons avancer !

Substitution de variables

Dans sqlcmd, il y a le remplacement de variables avec -v, par exemple, comme ceci :

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

Dans le script SQL, nous utilisons des substitutions :

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

Eh bien. Dans *nix les substitutions de variables ne fonctionnent pas. Le paramètre -v est ignoré. Dans Invoke-SqlCmd est ignoré -Variables. Bien que le paramètre qui définit les variables soit ignoré, les substitutions fonctionnent — vous pouvez utiliser n'importe quelle variable de Shell. Cependant, je me suis fâché contre les variables et j'ai décidé de ne pas en dépendre du tout, et j'ai agi de manière rude et primitive, heureusement que les scripts SQL sont courts :

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

C'est, comme vous l'avez compris, un test déjà avec la version Unix.

Téléchargement de fichiers

Dans la version Windows, chaque opération était accompagnée d'un audit : j'ai exécuté sqlcmd, j'ai obtenu des erreurs dans le fichier de sortie, et j'ai joint ce fichier à la table d'audit. Heureusement, SQL Server fonctionnait sur le même serveur que Jenkins, cela se faisait à peu près comme ça :

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

Ainsi, nous pourrions ingérer le fichier BCP dans son intégralité, et le placer dans le champ nvarchar(max) de la table d'audit. Évidemment, tout ce système s'est effondré, car au lieu de SQL Server, j'ai eu RDS, et BULK INSERT ne fonctionne pas du tout avec UNC à cause d'une tentative d'obtenir un verrou exclusif sur le fichier, et avec RDS, c'était voué à l'échec dès le départ. J'ai donc décidé de modifier la conception du système, en stockant l'audit ligne par ligne :

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

Et d'écrire dans cette table comme suit :

function WriteAudit([string]$Filename, [string]$ConnStr, 
     [string]$Tabname, [string]$Jobname)
{
  # obtenir $lastid de la dernière exécution  -- omis pour l'article
	
  # créer une grille et la remplir avec des données du fichier
  $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)   
    } 

  # écrire dans la 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() 
  }  

Pour sélectionner le contenu, il faut faire un select par ID, en choisissant dans l'ordre n (identity).

Dans l'article suivant, je vais m'arrêter plus en détail sur la façon dont tout cela interagit avec Jenkins.

Source : habr.com