Pulogalamu ya ProHoster > Blog > Ulamuliro > Kugwira ntchito ndi MS SQL kuchokera ku Powershell pa Linux

Kugwira ntchito ndi MS SQL kuchokera ku Powershell pa Linux

Nkhaniyi ndi yothandiza komanso yokhudzana ndi nkhani yanga yomvetsa chisoni

Kukonzekera Zero Touch PROD kwa RDS (MS SQL), pomwe makutu athu onse anali kumva, ndidapanga chiwonetsero (POC - Proof Of Concept) ya automation: seti ya zolemba zamphamvu. Pambuyo pa chiwonetserochi, pamene mphepo yamkuntho, kuwomba m'manja kwa nthawi yaitali kunatha, kusanduka kuwomba m'manja kosalekeza, anandiuza - zonsezi ndi zabwino, koma pazifukwa zamalingaliro, akapolo athu onse a Jenkins amagwira ntchito pa Linux!

Kodi izi zingatheke? Tengani DBA yotentha, nyali kuchokera pansi pa Windows ndikuyiyika pakutentha kwambiri kwamphamvu pansi pa Linux? Kodi izi si zankhanza?

Kugwira ntchito ndi MS SQL kuchokera ku Powershell pa Linux
Ndinayenera kumizidwa mumgwirizano wachilendowu wa matekinoloje. Inde, zolemba zanga zonse za 30+ zinasiya kugwira ntchito. Ndinadabwa kuti ndinakwanitsa kukonza zonse m’tsiku limodzi logwira ntchito. Ndikulemba zomwe ndikufuna. Ndiye, ndi zovuta ziti zomwe mungakumane nazo mukasamutsa zolemba za Powershell kuchokera ku Windows kupita ku Linux?

sqlcmd vs Invoke-SqlCmd

Ndiroleni ndikukumbutseni kusiyana kwakukulu pakati pawo. Ubwino wakale sqlcmd Imagwiranso ntchito pansi pa Linux, yokhala ndi magwiridwe antchito ofanana. Timadutsa funso kuti tipange -Q, fayilo yolowera monga -i, ndi zotsatira monga -o. Koma mafayilo amafayilo, amapangidwa kukhala omvera. Ngati mugwiritsa ntchito -i, ndiye mufayilo lembani kumapeto:

GO
EXIT

Ngati palibe EXIT kumapeto, ndiye sqlcmd ipitilira kudikirira kulowa, ndipo ngati kale POTULUKIRA sadzatero GO, ndiye kuti lamulo lomaliza siligwira ntchito. Fayilo yotulutsa ili ndi zotuluka zonse, zosankhidwa, mauthenga, kusindikiza, ndi zina.

Invoke-SqlCmd imapanga zotsatira ngati DataSet, DataTables kapena DataRows. Choncho, ngati mutakonza zotsatira za kusankha kosavuta, mungagwiritse ntchito sqlcmd, atasanthula zotsatira zake, ndizosatheka kupeza chinthu chovuta: chifukwa ichi pali Invoke-SqlCmd. Koma timuyi ilinso ndi nthabwala zake:

  • Mukasamutsa fayilo kwa iye kudzera -InputFile, ndiye POTULUKIRA zosafunikira, komanso, zimatulutsa zolakwika za syntax
  • -OutputFile ayi, lamulo limakubwezerani zotsatira ngati chinthu
  • Pali ma syntaxes awiri ofotokozera seva: -ServerInstance -Username -Password -Database ndi kudzera -ConnectionString. Zodabwitsa ndizakuti, koyamba sikutheka kufotokoza doko lina kupatula 1433.
  • mawu otuluka, lembani PRINT, yomwe imangokhala "kugwidwa" sqlcmdza Invoke-SqlCmd ndi vuto
  • Ndipo chofunika kwambiri: Mwina Linux yanu ilibe cmdlet iyi!

Ndipo ili ndilo vuto lalikulu. Only mu March izi cmdlet idapezeka pamasamba omwe si a Windows, ndipo potsiriza tikhoza kupita patsogolo!

Kusintha kosinthika

sqlcmd ili ndi kusintha kosinthika pogwiritsa ntchito -v, mwachitsanzo monga chonchi:

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

Mu SQL script timagwiritsa ntchito m'malo:

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

Kotero ndi izi. Mu *nix zosintha zosiyanasiyana sizigwira ntchito. Parameter -v kunyalanyazidwa. U Invoke-SqlCmd kunyalanyazidwa - Zosintha. Ngakhale magawo omwe amafotokozera zosinthazo samanyalanyazidwa, zosinthazo zimagwira ntchito - mutha kugwiritsa ntchito zosintha zilizonse kuchokera ku Shell. Komabe, ndidakhumudwa ndi zosinthazo ndipo ndidaganiza kuti ndisadalire konse, ndipo ndidachita mwamwano komanso mwachiyambi, popeza zolemba za SQL ndi zazifupi:

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

Izi, monga mukumvetsetsa, ndi kuyesa kale kuchokera ku Unix version.

Kukweza mafayilo

Mu mtundu wa Windows, ntchito iliyonse idatsagana ndi kafukufuku: tidathamanga sqlcmd, tidalandira nkhanza zamtundu wina mu fayilo yotulutsa, ndikuyika fayiloyi ku mbale yowerengera. Mwamwayi, seva ya SQL inagwira ntchito pa seva yomweyo monga Jenkins, zidachitidwa motere:

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

Chifukwa chake, timameza fayilo ya BCP kwathunthu ndikukankhira m'munda wa nvarchar(max) wa tebulo lowerengera. Zachidziwikire, dongosolo lonseli lidasokonekera, chifukwa m'malo mwa seva ya SQL ndidapeza RDS, ndipo BULK INSERT sigwira ntchito konse kudzera pa UNC chifukwa choyesera kutenga loko pafayilo, ndipo ndi RDS izi nthawi zambiri sizingachitike. chiyambi pomwe. Chifukwa chake ndidaganiza zosintha dongosolo, ndikusunga mzere wowerengera ndi mzere:

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

Ndipo lembani mu gome ili motere:

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

Kuti musankhe zomwe zili, muyenera kusankha ndi ID, kusankha mu dongosolo n (chidziwitso).

M'nkhani yotsatira ndifotokoza mwatsatanetsatane momwe zonsezi zimagwirizanirana ndi Jenkins.

Source: www.habr.com

Kuwonjezera ndemanga