ProHoster > Blog > administration > Travailler avec MS SQL à partir de Powershell sous Linux

Travailler avec MS SQL à partir de Powershell sous Linux

Cet article est purement pratique et est dédié à ma triste histoire

Se prépare pour Zéro Touch PROD pour RDS (MS SQL), dont toutes nos oreilles bourdonnaient, j'ai fait une présentation (POC - Proof Of Concept) d'automatisation : un ensemble de scripts PowerShell. Après la présentation, lorsque les applaudissements orageux et prolongés se sont calmés pour se transformer en applaudissements incessants, ils m'ont dit : tout cela est bien, mais seulement pour des raisons idéologiques, tous nos esclaves Jenkins travaillent sous Linux !

Est-ce possible? Prendre une lampe DBA aussi chaude sous Windows et la coller dans la chaleur même de PowerShell sous Linux ? N'est-ce pas cruel ?

Travailler avec MS SQL à partir de Powershell sous Linux
J'ai dû m'immerger dans cette étrange combinaison de technologies. Bien sûr, tous mes plus de 30 scripts ont cessé de fonctionner. À ma grande surprise, j'ai réussi à tout réparer en une journée ouvrable. J'écris à ma poursuite. Alors, quels pièges pouvez-vous rencontrer lors du transfert de scripts PowerShell de Windows vers Linux ?

sqlcmd et Invoke-SqlCmd

Permettez-moi de vous rappeler la principale différence entre eux. Bon vieil utilitaire sqlcmd Il fonctionne également sous Linux, avec des fonctionnalités quasiment identiques. Nous transmettons la requête pour exécuter -Q, le fichier d'entrée comme -i et la sortie comme -o. Mais les noms de fichiers, bien entendu, sont sensibles à la casse. Si vous utilisez -i, alors dans le fichier, écrivez à la fin :

GO
EXIT

S'il n'y a pas de EXIT à la fin, alors sqlcmd attendra l'entrée, et si avant EXIT pas GO, alors la dernière commande ne fonctionnera pas. Le fichier de sortie contient toutes les sorties, sélections, messages, impressions, etc.

Invoke-SqlCmd produit le résultat sous forme de DataSet, DataTables ou DataRows. Par conséquent, si vous traitez le résultat d’une simple sélection, vous pouvez utiliser sqlcmd, après avoir analysé sa sortie, il est presque impossible d'en dériver quelque chose de complexe : pour cela, il y a Invoquer-SqlCmd. Mais cette équipe a aussi ses propres blagues :

  • Si vous lui transférez un fichier via -Fichier d'entréepuis EXIT pas nécessaire, de plus, cela produit une erreur de syntaxe
  • -Fichier de sortie non, la commande vous renvoie le résultat sous forme d'objet
  • Il existe deux syntaxes pour spécifier un serveur : -ServerInstance -Nom d'utilisateur -Mot de passe -Base de données et à travers -Chaîne de connexion. Curieusement, dans le premier cas il n'est pas possible de spécifier un port autre que 1433.
  • sortie de texte, tapez PRINT, qui est simplement « attrapé » sqlcmd, pour Invoquer-SqlCmd est un problème
  • Et surtout: Il est fort probable que votre Linux ne dispose pas de cette applet de commande !

Et c'est là le principal problème. Seulement en mars, cette applet de commande est devenu disponible pour les plates-formes non Windows, et enfin nous pouvons avancer !

Substitution de variables

sqlcmd a une substitution de variable en utilisant -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)

Alors voilà. Dans *nix les substitutions de variables ne fonctionnent pas. Paramètre -v ignoré. U Invoquer-SqlCmd ignoré -Variables. Bien que le paramètre qui spécifie les variables elles-mêmes soit ignoré, les substitutions elles-mêmes fonctionnent : vous pouvez utiliser n'importe quelle variable de Shell. Cependant, j'ai été offensé par les variables et j'ai décidé de ne pas en dépendre du tout, et j'ai agi de manière grossière et primitive, car 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"

Comme vous l'avez compris, il s'agit d'un test déjà issu de la version Unix.

Télécharger des fichiers

Dans la version Windows, toute opération était accompagnée d'un audit : nous avons exécuté sqlcmd, reçu une sorte d'abus dans le fichier de sortie, joint ce fichier à la plaque d'audit. Heureusement, le serveur SQL fonctionnait sur le même serveur que Jenkins, cela ressemblait à ceci :

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 avalons entièrement le fichier BCP et le plaçons dans le champ nvarchar(max) de la table d'audit. Bien sûr, tout ce système s'est effondré, car au lieu d'un serveur SQL, j'ai eu RDS, et BULK INSERT ne fonctionne pas du tout via UNC en raison d'une tentative de verrouillage exclusif sur un fichier, et avec RDS, cela est généralement voué à l'échec. au tout début. 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 écrivez dans ce tableau comme ceci :

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

Pour sélectionner du contenu, vous devez sélectionner par ID, en choisissant dans l'ordre n (identité).

Dans le prochain article, j'entrerai plus en détail sur la manière dont tout cela interagit avec Jenkins.

Source: habr.com

Ajouter un commentaire