ProHoster > Blog > administração > Trabalhando com MS SQL do Powershell no Linux

Trabalhando com MS SQL do Powershell no Linux

Este artigo é puramente prático e é dedicado à minha triste história

Se preparando para Zero Touch PROD para RDS (MS SQL), sobre o qual todos estávamos atentos, fiz uma apresentação (POC - Proof Of Concept) de automação: um conjunto de scripts powershell. Depois da apresentação, quando os aplausos tempestuosos e prolongados cessaram, transformando-se em aplausos incessantes, eles me disseram - tudo isso é bom, mas apenas por razões ideológicas, todos os nossos escravos Jenkins trabalham no Linux!

Isso é possível? Pegue uma lâmpada DBA tão quente do Windows e coloque-a no calor do PowerShell no Linux? Isso não é cruel?

Trabalhando com MS SQL do Powershell no Linux
Tive que mergulhar nesta estranha combinação de tecnologias. Claro, todos os meus mais de 30 scripts pararam de funcionar. Para minha surpresa, consegui consertar tudo em um dia útil. Estou escrevendo em perseguição. Então, quais armadilhas você pode encontrar ao transferir scripts do PowerShell do Windows para o Linux?

sqlcmd vs Invoke-SqlCmd

Deixe-me lembrá-lo da principal diferença entre eles. O bom e velho utilitário sqlcmd Também funciona no Linux, com funcionalidade quase idêntica. Passamos a consulta para executar -Q, o arquivo de entrada como -i e a saída como -o. Mas os nomes dos arquivos, é claro, diferenciam maiúsculas de minúsculas. Se você usar -i, no arquivo escreva no final:

GO
EXIT

Se não houver EXIT no final, o sqlcmd continuará aguardando a entrada e, se antes SAIR não vai GO, então o último comando não funcionará. O arquivo de saída contém todas as saídas, seleções, mensagens, impressão, etc.

Invoke-SqlCmd produz o resultado como DataSet, DataTables ou DataRows. Portanto, se você processar o resultado de uma seleção simples, poderá usar sqlcmd, tendo analisado sua saída, é quase impossível derivar algo complexo: para isso existe Invocar-SqlCmd. Mas esta equipe também tem suas próprias piadas:

  • Se você transferir um arquivo para ela via -Arquivo de entradaem seguida SAIR não é necessário, além disso, produz um erro de sintaxe
  • -Arquivo de saída não, o comando retorna o resultado como um objeto
  • Existem duas sintaxes para especificar um servidor: -ServerInstance -Nome de usuário -Senha -Banco de dados e através -ConnectionString. Curiosamente, no primeiro caso não é possível especificar uma porta diferente de 1433.
  • saída de texto, digite PRINT, que é simplesmente “capturado” sqlcmdpara Invocar-SqlCmd é um problema
  • E o mais importante: Muito provavelmente o seu Linux não possui este cmdlet!

E este é o principal problema. Somente em março este cmdlet tornou-se disponível para plataformas não Windows, e finalmente podemos seguir em frente!

Substituição de Variável

sqlcmd tem substituição de variável usando -v, por exemplo assim:

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

No script SQL usamos substituições:

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

Então aqui está. Em *nix substituições de variáveis ​​não funcionam. Parâmetro -v ignorado. você Invocar-SqlCmd ignorado -Variáveis. Embora o parâmetro que especifica as variáveis ​​seja ignorado, as substituições funcionam – você pode usar qualquer variável do Shell. Porém, fiquei ofendido com as variáveis ​​​​e decidi não depender delas de forma alguma, e agi de maneira rude e primitiva, já que os scripts SQL são curtos:

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

Este, como você entende, já é um teste da versão Unix.

Carregar arquivos

Na versão Windows, qualquer operação era acompanhada de auditoria: rodamos o sqlcmd, recebemos algum tipo de abuso no arquivo de saída, anexamos esse arquivo à placa de auditoria. Felizmente, o SQL Server funcionou no mesmo servidor que o Jenkins, foi feito algo assim:

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

Assim, engolimos o arquivo BCP inteiramente e o colocamos no campo nvarchar(max) da tabela de auditoria. Claro, todo esse sistema desmoronou, porque em vez de um servidor SQL eu tenho RDS, e BULK INSERT via UNC não funciona devido a uma tentativa de obter um bloqueio exclusivo em um arquivo, e com RDS isso geralmente está condenado de bem no começo. Então resolvi mudar o design do sistema, armazenando a auditoria linha por linha:

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

E escreva nesta tabela assim:

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

Para selecionar o conteúdo, é necessário selecionar por ID, escolhendo na ordem n (identidade).

No próximo artigo entrarei em mais detalhes sobre como tudo isso interage com o Jenkins.

Fonte: habr.com

Adicionar um comentário