I-ProHoster > Блог > Ukuphatha > Ukusebenza nge-MS SQL kusuka ku-Powershell ku-Linux

Ukusebenza nge-MS SQL kusuka ku-Powershell ku-Linux

Lesi sihloko sisebenza ngokuphelele futhi sigxile endabeni yami edabukisayo

Ukuzilungiselela I-Zero Touch PROD ye-RDS (MS SQL), lapho zonke izindlebe zethu zazinkeneneza ngakho, ngenze isethulo (i-POC - Ubufakazi Bomqondo) sokuzenzakalela: isethi yemibhalo ye-powershell. Ngemva kwesethulo, lapho isivunguvungu, ihlombe elide liphela, liphenduka ihlombe elingapheli, bangitshela - konke lokhu kuhle, kodwa ngenxa yezizathu zemibono kuphela, zonke izigqila zethu zeJenkins zisebenza ku-Linux!

Ingabe lokhu kungenzeka? Thatha i-DBA enjalo efudumele, yesibani ngaphansi kweWindows futhi uyinamathisele ekushiseni kwegobolondo lamandla ngaphansi kweLinux? Ingabe akusona isihluku lesi?

Ukusebenza nge-MS SQL kusuka ku-Powershell ku-Linux
Kwadingeka ngicwilise kule nhlanganisela eyinqaba yobuchwepheshe. Impela, zonke izikripthi zami ezingama-30+ ziyekile ukusebenza. Ngamangala lapho ngikwazile ukulungisa yonke into ngosuku olulodwa lomsebenzi. Ngibhala ngijaha. Ngakho-ke, yiziphi izingibe ongahlangabezana nazo lapho udlulisela imibhalo ye-powershell isuka ku-Windows iye ku-Linux?

sqlcmd vs Invoke-SqlCmd

Ake ngikukhumbuze umehluko omkhulu phakathi kwabo. Isisetshenziswa esidala esihle sqlcmd Isebenza futhi ngaphansi kwe-Linux, ngokusebenza okucishe kufane. Sidlulisa umbuzo wokusebenzisa -Q, ifayela lokufaka njenge -i, kanye nokukhishwayo ngokuthi -o. Kodwa amagama amafayela, vele, enziwa azwela kakhulu. Uma usebenzisa -i, bhala efayeleni ekugcineni:

GO
EXIT

Uma ingekho i-EXIT ekugcineni, i-sqlcmd izoqhubeka nokulinda okokufaka, futhi uma ngaphambili PHUMA ngeke GO, khona-ke umyalo wokugcina ngeke usebenze. Ifayela eliphumayo liqukethe konke okukhiphayo, okukhethiwe, imilayezo, ukuphrinta, njll.

I-Invoke-SqlCmd ikhiqiza umphumela njenge-DataSet, DataTables noma DataRows. Ngakho-ke, uma ucubungula umphumela wokukhetha okulula, ungasebenzisa sqlcmd, ngemva kokuhlaziya okukhiphayo, cishe akunakwenzeka ukuthola okuthile okuyinkimbinkimbi: ngoba lokhu kukhona I-Invoke-SqlCmd. Kodwa leli qembu linawo amahlaya alo:

  • Uma udlulisela ifayela kuye nge -InputFile, khona-ke PHUMA ayidingeki, ngaphezu kwalokho, ikhiqiza iphutha le-syntax
  • -OutputFile cha, umyalo ukubuyisela umphumela njengento
  • Kunama-syntaxes amabili okucacisa iseva: -ServerInstance -Igama lomsebenzisi -Iphasiwedi -Isizindalwazi futhi ngokusebenzisa -ConnectionString. Ngokudabukisayo, esimweni sokuqala akunakwenzeka ukucacisa ichweba ngaphandle kwe-1433.
  • okukhiphayo kombhalo, thayipha i-PRINT, okuvele "ibanjwe" sqlcmdngoba I-Invoke-SqlCmd kuyinkinga
  • Futhi okubaluleke kakhulu: Kungenzeka ukuthi i-Linux yakho ayinayo le cmdlet!

Futhi lena inkinga enkulu. Kuphela ngo-March le cmdlet yatholakala kumapulatifomu angewona ama-Windows, futhi ekugcineni singaqhubekela phambili!

Ukushintshwa okuguquguqukayo

sqlcmd inokushintshwa okuguquguqukayo kusetshenziswa -v, isibonelo kanje:

# $conn содержит начало команды sqlcmd
$cmd = $conn + " -i D:appsSlaveJobsKillSpid.sql -o killspid.res 
  -v spid =`"" + $spid + "`" -v age =`"" + $age + "`""
Invoke-Expression $cmd

Kuskripthi se-SQL sisebenzisa okunye okunye:

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

Ngakho nakhu. Ku-*nix ukushintshwa okuguquguqukayo akusebenzi. Ipharamitha -v indiva. U I-Invoke-SqlCmd indiva -Okuguquguqukayo. Nakuba ipharamitha ecacisa okuguquguqukayo ngokwayo ishaywa indiva, ukushintshwa ngokwako kuyasebenza—ungasebenzisa noma yiziphi izinguquko ezisuka kuShell. Kodwa-ke, ngacasulwa yizimo eziguquguqukayo futhi nganquma ukunganciki kuzo nhlobo, futhi ngenza ngokudelela futhi ngasese, njengoba imibhalo ye-SQL mifushane:

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

Lokhu, njengoba uqonda, kuwukuhlola kakade okuvela enguqulweni ye-Unix.

Ilayisha amafayela

Kunguqulo ye-Windows, noma yikuphi ukusebenza kwakuhambisana nokuhlolwa kwamabhuku: sasebenzisa i-sqlcmd, sathola uhlobo oluthile lokuhlukumeza efayeleni lokukhipha, sanamathisela leli fayela epuleti lokucwaninga. Ngenhlanhla, iseva ye-SQL isebenze kuseva efanayo ne-Jenkins, yenziwe into enjengale:

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

Ngakho-ke, sigwinya ifayela le-BCP ngokuphelele bese silihlohla endaweni ye-nvarchar(max) yethebula lokuhlola. Vele, lonke lolu hlelo lwahlakazeka, ngoba esikhundleni seseva ye-SQL ngathola i-RDS, futhi i-BULK INSERT ayisebenzi nhlobo nge-UNC ngenxa yomzamo wokuthatha ilokhi ekhethekile efayeleni, futhi nge-RDS lokhu ngokuvamile ngeke kusebenze. ekuqaleni. Ngakho-ke nginqume ukushintsha idizayini yesistimu, ngigcine umugqa wokuhlola ngomugqa:

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

Bese ubhala kuleli thebula kanje:

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

Ukuze ukhethe okuqukethwe, udinga ukukhetha nge-ID, ukhethe ngokulandelana n (ubunikazi).

Esihlokweni esilandelayo ngizongena ngemininingwane eyengeziwe mayelana nokuthi konke lokhu kusebenzisana kanjani noJenkins.

Source: www.habr.com

Engeza amazwana