🥇Treballant amb MS SQL des de Powershell Linux

Aquest article és purament pràctic i està dedicat a la meva trista història

Preparant-se per Zero Touch PROD Per a RDS (MS SQL), del qual tothom parlava, vaig fer una presentació (POC - Prova de concepte) sobre automatització: un conjunt de scripts de PowerShell. Després de la presentació, quan els aplaudiments eixordadors i prolongats, que es van convertir en una ovació dempeus, es van apagar, em van dir: tot això està molt bé, però per raons ideològiques, tots els nostres esclaus de Jenkins estan funcionant sota... Linux!

És això possible? Agafar un DBA tan càlid i semblant a un tub des de sota Windows i enganxar-ho a la part molt gruixuda de la Powershell LinuxNo és cruel?

Vaig haver de submergir-me en aquesta estranya combinació de tecnologies. Naturalment, els meus més de 30 scripts van deixar de funcionar. Per a la meva sorpresa, vaig aconseguir arreglar-ho tot en un dia laborable. Escric això mentre la notícia encara és fresca. Aleshores, quins entrebancs podeu trobar en migrar scripts de PowerShell des de Windows под Linux?

sqlcmd vs Invoke-SqlCmd

Permeteu-me que us recordi la principal diferència entre ells. Bona utilitat antiga sqlcmd També funciona sota Linux, amb una funcionalitat gairebé idèntica. Passem la consulta per executar -Q, el fitxer d'entrada com -i i la sortida com -o. Però els noms dels fitxers, per descomptat, distingeixen entre majúscules i minúscules. Si utilitzeu -i, al fitxer escriviu al final:

GO
EXIT

Si no hi ha EXIT al final, sqlcmd continuarà a esperar l'entrada, i si abans Sortida no GO, aleshores l'última ordre no funcionarà. El fitxer de sortida conté tota la sortida, seleccions, missatges, impressió, etc.

Invoke-SqlCmd produeix el resultat com a DataSet, DataTables o DataRows. Per tant, si processeu el resultat d'una simple selecció, podeu utilitzar sqlcmd, havent analitzat la seva sortida, és gairebé impossible derivar alguna cosa complexa: per això n'hi ha Invocar-SqlCmd. Però aquest equip també té els seus propis acudits:

Si li transfereixes un fitxer via -Fitxer d'entrada, Llavors Sortida no cal, a més, produeix un error de sintaxi
-Fitxer de sortida no, l'ordre us retorna el resultat com a objecte
Hi ha dues sintaxis per especificar un servidor: -ServerInstance -Nom d'usuari -Contrasenya -Base de dades i a través -Cadena de connexió. Curiosament, en el primer cas no és possible especificar un port diferent del 1433.
sortida de text, escriviu PRINT, que simplement està "atrapat" sqlcmdper Invocar-SqlCmd és un problema
I el més important: El més probable és que el vostre Linux no tingui aquest cmdlet!

I aquest és el principal problema. Només al març aquest cmdlet està disponible per a plataformes que no són Windows, i per fi podem avançar!

Substitució variable

sqlcmd té una substitució de variables amb -v, per exemple com aquesta:

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

A l'script SQL fem servir substitucions:

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

Així que aquí està. En *nix les substitucions de variables no funcionen... Paràmetre -v ignorat. U Invocar-SqlCmd ignorat -Les variables. Tot i que s'ignora el paràmetre que especifica les variables, les substitucions funcionen; podeu utilitzar qualsevol variable de Shell. No obstant això, em vaig ofendre les variables i vaig decidir no dependre d'elles en absolut, i vaig actuar de manera grollera i primitiva, ja que els scripts SQL són curts:

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

Això, com enteneu, és una prova ja de la versió Unix.

Carregant fitxers

A la versió de Windows, qualsevol operació anava acompanyada d'una auditoria: vam executar sqlcmd, vam rebre algun tipus d'abús al fitxer de sortida, vam adjuntar aquest fitxer a la placa d'auditoria. Afortunadament, el servidor SQL funcionava al mateix servidor que Jenkins, es va fer alguna cosa com això:

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

Per tant, empassem el fitxer BCP completament i l'introduïm al camp nvarchar(max) de la taula d'auditoria. Per descomptat, tot aquest sistema es va ensorrar, perquè en comptes d'un servidor SQL vaig obtenir RDS, i BULK INSERT no funciona gens mitjançant UNC a causa d'un intent de bloquejar un fitxer exclusiu, i amb RDS això generalment està condemnat. el principi mateix. Així que vaig decidir canviar el disseny del sistema, emmagatzemant l'auditoria línia per línia:

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

I escriu en aquesta taula així:

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

Per seleccionar contingut, cal seleccionar per ID, escollint per ordre n (identitat).

En el proper article entraré en més detalls sobre com interactua tot això amb Jenkins.

Font: www.habr.com