ProHoster > Blog > Pangangasiwa > Paggawa gamit ang MS SQL mula sa Powershell sa Linux

Paggawa gamit ang MS SQL mula sa Powershell sa Linux

Ang artikulong ito ay purong praktikal at nakatuon sa aking malungkot na kwento

Paghahanda para sa Zero Touch PROD para sa RDS (MS SQL), tungkol sa kung saan ang lahat ng aming mga tainga ay buzz, gumawa ako ng isang pagtatanghal (POC - Patunay Ng Konsepto) ng automation: isang set ng mga script ng powershell. Pagkatapos ng pagtatanghal, nang humina ang mabagyo, matagal na palakpakan, na naging walang humpay na palakpakan, sinabi nila sa akin - lahat ng ito ay mabuti, ngunit para lamang sa mga kadahilanang ideolohikal, lahat ng aming mga alipin na Jenkin ay nagtatrabaho sa Linux!

posible ba ito? Kumuha ng tulad ng isang mainit, lamp DBA mula sa ilalim ng Windows at ilagay ito sa pinakainit ng powershell sa ilalim ng Linux? Hindi ba ito malupit?

Paggawa gamit ang MS SQL mula sa Powershell sa Linux
Kinailangan kong isawsaw ang aking sarili sa kakaibang kumbinasyong ito ng mga teknolohiya. Siyempre, lahat ng 30+ script ko ay tumigil sa paggana. Sa aking sorpresa, nagawa kong ayusin ang lahat sa isang araw ng trabaho. Nagsusulat ako sa mainit na pagtugis. Kaya, anong mga pitfalls ang maaari mong makaharap kapag naglilipat ng mga script ng powershell mula sa Windows patungo sa Linux?

sqlcmd kumpara sa Invoke-SqlCmd

Hayaan akong ipaalala sa iyo ang pangunahing pagkakaiba sa pagitan nila. Magandang lumang utility sqlcmd Gumagana rin ito sa ilalim ng Linux, na may halos magkaparehong pag-andar. Ipinapasa namin ang query upang maisagawa ang -Q, ang input file bilang -i, at ang output bilang -o. Ngunit ang mga pangalan ng file, siyempre, ay ginawang case-sensitive. Kung gumagamit ka ng -i, pagkatapos ay sa file isulat sa dulo:

GO
EXIT

Kung walang EXIT sa dulo, ang sqlcmd ay magpapatuloy na maghintay para sa input, at kung bago EXIT ay hindi GO, kung gayon ang huling utos ay hindi gagana. Ang output file ay naglalaman ng lahat ng output, pinipili, mensahe, print, atbp.

Ang Invoke-SqlCmd ay gumagawa ng resulta bilang isang DataSet, DataTables o DataRows. Samakatuwid, kung ipoproseso mo ang resulta ng isang simpleng pagpili, maaari mong gamitin sqlcmd, na na-parse ang output nito, halos imposibleng makakuha ng isang bagay na kumplikado: para dito mayroon Invoke-SqlCmd. Ngunit ang pangkat na ito ay mayroon ding sariling mga biro:

  • Kung ililipat mo ang isang file sa kanya sa pamamagitan ng -InputFile, Pagkatapos EXIT hindi kinakailangan, bukod dito, ito ay gumagawa ng isang syntax error
  • -OutputFile hindi, ibinabalik sa iyo ng utos ang resulta bilang isang bagay
  • Mayroong dalawang syntax para sa pagtukoy ng isang server: -ServerInstance -Username -Password -Database at sa pamamagitan ng -ConnectionString. Kakatwa, sa unang kaso, hindi posible na tukuyin ang isang port maliban sa 1433.
  • output ng text, i-type ang PRINT, na simpleng "nahuli" sqlcmdpara sa Invoke-SqlCmd ay problema
  • At higit sa lahat: Malamang na ang iyong Linux ay walang ganitong cmdlet!

At ito ang pangunahing problema. Sa Marso lamang ang cmdlet na ito naging available para sa mga non-Windows platform, at sa wakas maaari na tayong sumulong!

Variable Substitution

Ang sqlcmd ay may variable na pagpapalit gamit ang -v, halimbawa tulad nito:

# $conn содСрТит Π½Π°Ρ‡Π°Π»ΠΎ ΠΊΠΎΠΌΠ°Π½Π΄Ρ‹ sqlcmd
$cmd = $conn + " -i D:appsSlaveJobsKillSpid.sql -o killspid.res 
  -v spid =`"" + $spid + "`" -v age =`"" + $age + "`""
Invoke-Expression $cmd

Sa script ng SQL gumagamit kami ng mga pamalit:

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

Kaya eto na. Sa *nix Ang mga variable na pagpapalit ay hindi gumagana. Parameter -v hindi pinansin. U Invoke-SqlCmd hindi pinansin -Mga variable. Bagama't ang parameter na tumutukoy sa mga variable mismo ay binabalewala, ang mga pagpapalit mismo ay gumaganaβ€”maaari kang gumamit ng anumang mga variable mula sa Shell. Gayunpaman, nasaktan ako ng mga variable at nagpasya na huwag umasa sa kanila, at kumilos nang walang pakundangan at primitive, dahil ang mga script ng SQL ay maikli:

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

Ito, tulad ng naiintindihan mo, ay isang pagsubok na mula sa bersyon ng Unix.

Pag-upload ng mga file

Sa bersyon ng Windows, ang anumang operasyon ay sinamahan ng isang pag-audit: nagpatakbo kami ng sqlcmd, nakatanggap ng ilang uri ng pang-aabuso sa output file, naka-attach ang file na ito sa audit plate. Sa kabutihang palad, ang SQL server ay nagtrabaho sa parehong server bilang Jenkins, ito ay ginawa tulad nito:

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

Kaya, lunukin namin ang BCP file at itinulak ito sa nvarchar(max) field ng audit table. Siyempre, ang buong sistemang ito ay bumagsak, dahil sa halip na isang SQL server ay nakakuha ako ng RDS, at ang BULK INSERT ay hindi gumagana sa pamamagitan ng UNC dahil sa isang pagtatangka na kumuha ng eksklusibong lock sa isang file, at sa RDS ito ay karaniwang napapahamak mula sa ang pinakasimula. Kaya't nagpasya akong baguhin ang disenyo ng system, na iniimbak ang linya ng pag-audit ayon sa linya:

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

At isulat sa talahanayang ito tulad nito:

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

Upang pumili ng nilalaman, kailangan mong pumili ayon sa ID, pagpili sa pagkakasunud-sunod n (pagkakakilanlan).

Sa susunod na artikulo ay pupunta ako sa higit pang detalye tungkol sa kung paano ito nakikipag-ugnayan sa Jenkins.

Pinagmulan: www.habr.com

Magdagdag ng komento