ProHoster > Π‘Π»ΠΎΠ³ > Maamulka > La shaqaynta MS SQL ee Powershell ee Linux

La shaqaynta MS SQL ee Powershell ee Linux

Maqaalkani waa mid wax ku ool ah oo kaliya oo u heellan sheekadayda murugada leh

U diyaargarowga Zero Touch PROD RDS (MS SQL), oo dhegahayaga oo dhami ay ka guuxayeen, waxaan sameeyay soojeedin (POC - Proof Of Concept) ee automation: set of powershell scripts. Soo bandhigida ka dib, markii duufaankii, sacabkii daba dheeraaday ay dhinteen, oo u rogay sacab aan kala go' lahayn, waxay ii sheegeen - waxaas oo dhan waa wanaagsan yihiin, laakiin kaliya sababo fikradeed, dhammaan addoommadeenna Jenkins waxay ka shaqeeyaan Linux!

Tani suurtogal ma tahay? Ka soo qaad sida diiran, laambad DBA ah oo ka hooseysa Windows oo ku dheji kulaylka aadka u kulul ee hoosta Linux? Sow kani naxariis ma aha?

La shaqaynta MS SQL ee Powershell ee Linux
Waxa aan ku qasbanaay in aan is dhex galiyo teknooloojiyadan la yaabka leh. Dabcan, dhammaan qoraaladayda 30+ ayaa joojiyay shaqada. Waxaa yaab igu noqotay, in aan wax walba ku hagaajiyo hal maalin shaqo gudaheed. Waxaan qorayaa dabagal kulul. Marka, waa maxay cilladaha aad la kulmi karto marka aad qoraalada powershell ka wareejinayso Windows una direyso Linux?

sqlcmd vs Invoke-SqlCmd

Aan ku xasuusiyo farqiga ugu weyn ee u dhexeeya. Utility jir wanaagsan sqlcmd Waxa kale oo ay ka hoos shaqeysaa Linux, iyada oo ku dhawaad ​​isku mid ah shaqeyn. Waxaan u gudubnaa weydiinta si aan u fulino -Q, faylka gelinta sida -i, iyo soo saarista sida -o. Laakiin magacyada faylka, dabcan, ayaa laga dhigay kiis xasaasi ah. Haddii aad isticmaasho -i, markaa faylka ku qor dhamaadka:

GO
EXIT

Haddii aysan jirin wax ka bixitaan ah dhamaadka, markaas sqlcmd wuxuu sii wadi doonaa inuu sugo gelinta, iyo haddii ka hor KA BAX ma doono GO, markaas amarka ugu dambeeya ma shaqayn doono. Faylka wax soo saarka wuxuu ka kooban yahay dhammaan wax soo saarka, xulashada, fariimaha, daabacaadda, iwm.

Invoke-SqlCmd waxay soo saartaa natiijada sida DataSet, DataTables ama DataRows. Sidaa darteed, haddii aad ka baaraandegto natiijada xulashada fudud, waxaad isticmaali kartaa sqlcmdMarka la eego wax soo saarkeeda, waa wax aan macquul aheyn in la soo saaro wax adag: tan waxaa jira U yeedho-SqlCmd. Laakiin kooxdani sidoo kale waxay leedahay kaftan u gaar ah:

  • Haddii aad fayl ugu wareejiso iyada -Faylka galitaanka, ka dibna KA BAX Looma baahna, sidoo kale, waxay soo saartaa qalad syntax
  • -Faylka wax-soo-saarka maya, amarku wuxuu kuugu soo celinayaa natiijada shay ahaan
  • Waxaa jira laba eray oo lagu qeexay server: -ServerInstance -Username -Password -Database oo dhex mara -ConnectionString. Si la yaab leh, kiiska ugu horreeya suurtagal maaha in la qeexo deked aan ahayn 1433.
  • soo saarida qoraalka, ku qor PRINT, oo si fudud loo "qabtay" sqlcmdee U yeedho-SqlCmd waa dhibaato
  • Iyo tan ugu muhiimsan: Waxay u badan tahay in Linux-kaagu aanu lahayn cmdlet-kan!

Tanina waa dhibka ugu weyn. Kaliya bisha Maarso cmdle this noqday mid diyaar u ah aaladaha aan Windows-ka ahayn, oo ugu dambeyntii hore ayaan u socon karnaa!

Beddelka Beddelka ah

sqlcmd waxa uu leeyahay beddelaad doorsooma oo isticmaalaya -v, tusaale ahaan sidan:

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

Qoraalka SQL waxaan ku isticmaalnaa beddelka:

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

Markaa waa kan. In *nix beddelka doorsooma ma shaqeeyo. Halbeegga -v la iska indhatiray. U U yeedho-SqlCmd la iska indhatiray -Isbedelka. In kasta oo halbeegga qeexaya doorsoomayaasha laftooda la iska indhatiray, beddelka laftoodu waa shaqeeyaan- waxaad isticmaali kartaa doorsoomayaal kasta oo Shell ah. Si kastaba ha ahaatee, waxaa iga xumaaday doorsoomayaasha oo waxaan go'aansaday inaanan ku tiirsanayn haba yaraatee, waxaana u dhaqmay si akhlaaq darro ah oo asal ah, maadaama qoraallada SQL ay gaaban yihiin:

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

Kani, sida aad fahantay, waa imtixaan horeba uga yimid nooca Unix.

Soo dejinta faylasha

Nooca Windows-ka, hawlgal kasta waxa la socday xisaab hubin: waxaanu wadnay sqlcmd, waxaanu helnay nooc ka mid ah xadgudubka faylka wax soo saarka, waxaanu ku lifaaqnay faylkan saxanka hanti dhawrka. Nasiib wanaag, server-ka SQL wuxuu ku shaqeeyay isla server-kii Jenkins, waxaa la sameeyay sidan oo kale:

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

Markaa, waanu liqnaa gebi ahaanba faylka BCP waxaanan ku shubnay qaybta nvarchar (max) ee miiska xisaabinta. Dabcan, nidaamkan oo dhan wuu kala go'ay, sababtoo ah halkii SQL server-ka waxaan ka helay RDS, BULK INSERT kuma shaqeynayo dhammaan UNC sababtoo ah isku dayga in la qaato quful gaar ah faylka, iyo RDS tan guud ahaan waa laga takhalusay bilowga hore. Markaa waxaan go'aansaday inaan beddelo naqshadaynta nidaamka, anigoo ku kaydinaya khadka hanti dhawrka khadka:

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

Oo shaxdan ku qor sidan:

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

Si aad u doorato nuxurka, waxaad u baahan tahay inaad ku doorato aqoonsiga, adigoo dooranaya siday u kala horreeyaan n (aqoonsiga).

Maqaalka soo socda waxaan si faahfaahsan uga hadli doonaa sida ay tani u wada falgalaan Jenkins.

Source: www.habr.com

Add a comment