ProHoster > Ibhulogi > Ulawulo > Ukusebenza nge-MS SQL kwi-Powershell kwi-Linux

Ukusebenza nge-MS SQL kwi-Powershell kwi-Linux

Eli nqaku lisebenziseka ngokupheleleyo kwaye lizinikele kwibali lam elilusizi

Ukulungiselela Zero Touch PROD ye-RDS (MS SQL), malunga nazo zonke iindlebe zethu zazibetha, ndenze umboniso (i-POC - Ubungqina beNgcaciso) ye-automation: isethi yezikripthi ze-powershell. Emva komboniso, xa isaqhwithi, ihlombe elide laphela, lijika libe yihlombe elingapheliyo, bandixelela-konke oku kulungile, kodwa ngenxa yezizathu zengqondo, onke amakhoboka ethu eJenkins asebenza kwiLinux!

Ngaba oku kunokwenzeka? Thatha i-DBA enjalo efudumeleyo, yesibane phantsi kweWindows kwaye uyincamathele kubushushu be-powershell phantsi kweLinux? Ngaba asiyonkohlakalo le?

Ukusebenza nge-MS SQL kwi-Powershell kwi-Linux
Kwafuneka ndizijule kule ndibaniselwano ingaqhelekanga yetekhnoloji. Ewe kunjalo, zonke iiscript zam ezingama-30+ zayeka ukusebenza. Okwandothusayo kukuba, ndakwazi ukulungisa yonke into ngosuku olunye lokusebenza. Ndibhala ndisukela. Ke, yeyiphi imigibe onokudibana nayo xa uhambisa izikripthi ze-powershell ukusuka kwiWindows ukuya kwiLinux?

sqlcmd vs Invoke-SqlCmd

Makhe ndikukhumbuze ngomahluko ophambili phakathi kwabo. Uncedo oludala oluhle sqlcmd Ikwasebenza phantsi kweLinux, ngokusebenza phantse okufanayo. Sigqithisa umbuzo wokuphumeza -Q, ifayile yegalelo njenge -i, kunye nemveliso njenge -o. Kodwa amagama eefayile, ngokuqinisekileyo, enziwa ukuba angabi nangxaki. Ukuba usebenzisa -i, ngoko kwifayile bhala ekupheleni:

GO
EXIT

Ukuba akukho PHUMA ekupheleni, ngoko sqlcmd iya kuqhubeka nokulinda igalelo, kwaye ukuba ngaphambili PHUMA ngeke GO, ngoko umyalelo wokugqibela awuyi kusebenza. Ifayile yemveliso iqulethe yonke imveliso, ukhetho, imiyalezo, ushicilelo, njl.

I-Invoke-SqlCmd ivelisa iziphumo njengeSeti yedatha, iiTayibhile zeDatha okanye iiRow zeDatha. Ke ngoko, ukuba uqhuba isiphumo sokhetho olulula, ungasebenzisa sqlcmd, emva kokwahlula imveliso yayo, phantse akunakwenzeka ukufumana into enzima: kuba oku kukho Biza-SqlCmd. Kodwa eli qela linazo iziqhulo zalo:

  • Ukuba uthumela ifayile kuye nge -InputFile, ngoko PHUMA ayidingeki, ngaphezu koko, ivelisa impazamo yesintaksi
  • -OutputFile hayi, umyalelo ukubuyisela isiphumo njengento
  • Zimbini ii-syntaxes zokuchaza umncedisi: -ServerInstance -Igama lomsebenzisi -Password -Database kwaye uqhubeke -ConnectionString. Ngokungaqhelekanga, kwimeko yokuqala akunakwenzeka ukucacisa izibuko ngaphandle kwe-1433.
  • imveliso yombhalo, chwetheza PRINT, β€œebanjwe” ngokulula sqlcmdze Biza-SqlCmd yingxaki
  • Kwaye okona kubalulekileyo: Kusenokwenzeka ukuba iLinux yakho ayinayo le cmdlet!

Kwaye le yeyona ngxaki iphambili. Kuphela ngoMatshi le cmdlet yafumaneka kumaqonga angengo-Windows, yaye ekugqibeleni sinokuqhubela phambili!

Ukutshintsha okuguquguqukayo

sqlcmd inotshintsho oluguquguqukayo usebenzisa -v, umzekelo onje:

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

Kwiskripthi se-SQL sisebenzisa ezinye iindawo:

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

Ngoko nantsi. Kwi *nix ukutshintshwa okuguquguqukayo akusebenzi... Ipharamitha -v ingahoywa. U Biza-SqlCmd ingahoywa -Eziguquguqukayo. Nangona iparameter echaza izinto eziguquguqukayo ngokwazo zingahoywa, iinguqu ngokwazo ziyasebenza-ungasebenzisa naziphi na izinto eziguquguqukayo ezivela kwaShell. Nangona kunjalo, ndacatshukiswa zizinto eziguquguqukayo kwaye ndagqiba ekubeni ndingaxhomekeki kuzo kwaphela, kwaye ndenza ngoburhalarhume kwaye okokuqala, kuba izikripthi zeSQL zifutshane:

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

Oku, njengoko uqonda, luvavanyo esele luvela kwinguqulo ye-Unix.

Kufakwa iifayile

Kwinguqulo yeWindows, nayiphi na imisebenzi yayihamba kunye nophicotho-zincwadi: siqhube i-sqlcmd, safumana uhlobo oluthile lokuxhatshazwa kwifayile yokuphuma, iqhotyoshelwe le fayile kwi-audit plate. Ngethamsanqa, iseva yeSQL isebenze kwiseva efanayo neJenkins, yenziwe into enje:

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

Ngaloo ndlela, siginya ifayile ye-BCP ngokupheleleyo kwaye siyityhale kwintsimi ye-nvarchar (max) yetafile yophicotho. Ewe, yonke le nkqubo yawa yahlukana, kuba endaweni yeseva yeSQL ndifumene iRDS, kwaye iBULK INSERT ayisebenzi kwaphela nge UNC ngenxa yomzamo wokuthatha iqhaga elikhethekileyo kwifayile, kwaye nge RDS oku kutshabalala kwasekuqaleni. Ke ndiye ndagqiba ekubeni nditshintshe uyilo lwenkqubo, ukugcina umgca wophicotho ngomgca:

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

Kwaye ubhale kule theyibhile ngolu hlobo:

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

Ukukhetha umxholo, kufuneka ukhethe nge-ID, ukhetha ngokulandelelana n (ubunikazi).

Kwinqaku elilandelayo ndiza kungena kwiinkcukacha ezithe kratya malunga nendlela oku kusebenzisana ngayo noJenkins.

umthombo: www.habr.com

Yongeza izimvo