ProHoster > Blog > Utongi > Kushanda neMS SQL kubva kuPowershell paLinux

Kushanda neMS SQL kubva kuPowershell paLinux

Ichi chinyorwa chinoshanda chete uye chakatsaurirwa kunyaya yangu inosuruvarisa

Kugadzirira Zero Bata PROD yeRSS (MS SQL), iyo nzeve dzedu dzese dzanga dzichirira, ndakaita mharidzo (POC - Proof Of Concept) ye automation: seti yezvinyorwa zvepowershell. Mushure memharidzo, apo dutu, kuombera maoko kwenguva refu kwakapera, kushanduka kuita kuombera kusingaperi, vakandiudza - zvese izvi zvakanaka, asi nekuda kwezvikonzero zvepfungwa, vese varanda vedu veJenkins vanoshanda paLinux!

Izvi zvinogoneka here? Tora inodziya, mwenje DBA kubva pasi peWindows uye uinamire mukupisa chaiko kwesimba remagetsi pasi peLinux? Hahusi hutsinye here uhwu?

Kushanda neMS SQL kubva kuPowershell paLinux
Ndaifanira kunyura mumubatanidzwa uyu wechienzi wetekinoroji. Ehe, ese angu 30+ zvinyorwa zvakamira kushanda. Zvakandishamisa ndezvokuti ndakakwanisa kugadzirisa zvinhu zvose muzuva rimwe chete rekushanda. Ndiri kunyora ndichitsvaga. Saka, ndeapi misungo yaungasangana nayo kana uchiendesa powershell zvinyorwa kubva kuWindows kuenda kuLinux?

sqlcmd vs Invoke-SqlCmd

Rega ndikuyeuchidze nezve musiyano mukuru pakati pavo. Utility yakanaka yekare sqlcmd Iyo inoshandawo pasi peLinux, ine inenge yakafanana mashandiro. Isu tinopfuudza mubvunzo wekuita -Q, iyo yekuisa faira se -i, uye yakabuda se -o. Asi iwo mazita emafaira, hongu, anogadzirwa-sensitive. Kana iwe ukashandisa -i, saka mufaira nyora kumagumo:

GO
EXIT

Kana pasina EXIT kumagumo, ipapo sqlcmd ichaenderera mberi nekumirira kupinza, uye kana zvisati zvaitika Buda hazvingadaro GO, ipapo murairo wekupedzisira hauzoshande. Iyo yakabuda faira ine zvese zvinobuda, zvinosarudzwa, mameseji, kudhindwa, nezvimwe.

Invoke-SqlCmd inogadzira mhedzisiro seDataSet, DataTables kana DataRows. Saka, kana iwe ukagadzirisa mhedzisiro yesarudzo yakapusa, unogona kushandisa sqlcmd, yapatsanura kuburitsa kwayo, hazvigoneke kutora chimwe chinhu chakaoma: nekuti ichi chiripo Invoke-SqlCmd. Asi chikwata ichi chinewo nyambo dzacho:

  • Kana iwe uchiendesa faira kwaari kuburikidza -InputFile, ipapo Buda haidiwi, uyezve, inoburitsa chikanganiso che syntax
  • -OutputFile kwete, murairo unokudzosera mhedzisiro sechinhu
  • Pane zvirevo zviviri zvekutsanangura sevha: -ServerInstance -Username -Password -Database uye kuburikidza -ConnectionString. Oddly zvakakwana, mune yekutanga kesi hazvigone kutsanangura chiteshi kunze kwe1433.
  • zvinyorwa zvinobuda, nyora PRINT, inongo "kubatwa" sqlcmdnokuti Invoke-SqlCmd idambudziko
  • Uye zvinonyanya kukosha: Zvingangodaro Linux yako haina cmdlet iyi!

Uye iri ndiro dambudziko guru. Chete muna March iyi cmdlet yakatanga kuwanikwa kune asiri-Windows mapuratifomu, uye pakupedzisira tinogona kufambira mberi!

Variable Substitution

sqlcmd ine shanduko yakasiyana-siyana uchishandisa -v, semuenzaniso seizvi:

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

Mune SQL script tinoshandisa zvinotsiva:

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

Saka hezvinoi. Mu *nix zvinochinja-chinja hazvishande. Parameter -v kufuratirwa. U Invoke-SqlCmd kufuratirwa -Variables. Kunyangwe iyo parameter inotsanangudza machinjiro pachawo ichifuratirwa, iyo inotsiva pachayo inoshanda-iwe unogona kushandisa chero shanduko kubva kuShell. Nekudaro, ndakagumburwa nezvinosiyana uye ndikafunga kusatsamira pazviri zvachose, uye ndakaita hutsinye uye neprimitively, sezvo zvinyorwa zveSQL zvipfupi:

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

Izvi, sezvaunonzwisisa, muedzo watove kubva kuUnix vhezheni.

Kuisa mafaira

MuWindows vhezheni, chero kuvhiya kwaiperekedzwa nekuongorora: isu takamhanya sqlcmd, takagamuchira imwe mhando yekushungurudzwa mufaira rekubuda, yakanamatira iyi faira mundiro yekuongorora. Neraki, SQL server yakashanda pane imwechete server seJenkins, yakaitwa seizvi:

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

Nekudaro, isu tinomedza iyo BCP faira zvachose uye toisundira mu nvarchar(max) ndima yetafura yekuongorora. Ehe, iyi system yese yakaparara, nekuti pachinzvimbo cheSQL server ndakawana RDS, uye BULK INSERT haishande zvachose kuburikidza neUNC nekuda kwekuyedza kutora kiyi yakasarudzika pafaira, uye neRSS izvi zvinowanzo parara kubva. kutanga chaiko. Saka ndakafunga kushandura dhizaini yehurongwa, ndichichengeta mutsara wekuongorora nemutsara:

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

Uye nyora mutafura iyi seizvi:

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 usarudze zvirimo, unofanirwa kusarudza neID, uchisarudza mukurongeka n (kuzivikanwa).

Muchinyorwa chinotevera ini ndichaenda mune zvakadzama nezvekuti izvi zvese zvinodyidzana sei naJenkins.

Source: www.habr.com

Voeg