ProHoster > Блог > Gudanarwa > Yin aiki tare da MS SQL daga Powershell akan Linux

Yin aiki tare da MS SQL daga Powershell akan Linux

Wannan labarin yana aiki ne kawai kuma an sadaukar da shi ga labarina mai ban tausayi

Ana shirya don Zero Touch PROD don RDS (MS SQL), wanda duk kunnuwanmu ke bugu, na yi gabatarwa (POC - Proof Of Concept) na aiki da kai: saitin rubutun wutar lantarki. Bayan gabatarwar, lokacin da guguwa, daɗaɗɗen tafi ya mutu, sun juya cikin tafi ba tare da tsayawa ba, sun gaya mani - duk wannan yana da kyau, amma saboda dalilai na akida, duk bayin Jenkins suna aiki akan Linux!

Shin hakan zai yiwu? Ɗauki irin wannan dumi, fitilar DBA daga ƙarƙashin Windows kuma ku manne shi a cikin tsananin zafin wutar lantarki a ƙarƙashin Linux? Wannan ba zalunci ba ne?

Yin aiki tare da MS SQL daga Powershell akan Linux
Dole ne in nutsad da kaina cikin wannan baƙon haɗin fasahar. Tabbas, duk rubutuna na 30+ sun daina aiki. Abin ya ba ni mamaki, na yi nasarar gyara komai a rana ɗaya na aiki. Ina rubutu ne a cikin zazzafan bibiya. Don haka, waɗanne matsaloli ne za ku iya fuskanta yayin canja wurin rubutun iko daga Windows zuwa Linux?

sqlcmd vs Kira-SqlCmd

Bari in tuna muku babban bambanci tsakanin su. Kyakkyawan tsohon mai amfani sqlcmd Hakanan yana aiki ƙarƙashin Linux, tare da kusan ayyuka iri ɗaya. Mun wuce tambayar don aiwatar da -Q, fayil ɗin shigarwa as -i, da fitarwa azaman -o. Amma fayilolin sunaye, ba shakka, an sanya su cikin yanayin-m. Idan kuna amfani da -i, to a cikin fayil ɗin ku rubuta a ƙarshen:

GO
EXIT

Idan babu FITA a karshen, to sqlcmd zai ci gaba da jira don shigarwa, kuma idan kafin fita ba zai GO, to, umarni na ƙarshe ba zai yi aiki ba. Fayil ɗin fitarwa ya ƙunshi duk abubuwan fitarwa, zaɓi, saƙonni, bugu, da sauransu.

Invoke-SqlCmd yana samar da sakamakon azaman DataSet, DataTables ko DataRows. Don haka, idan kun aiwatar da sakamakon zaɓi mai sauƙi, zaku iya amfani da su sqlcmd, Bayan an yi la'akari da fitowar sa, yana da wuya a sami wani abu mai rikitarwa: don wannan akwai Kira-SqlCmd. Amma kuma wannan tawaga tana da nata barkwanci:

  • Idan ka canja wurin fayil zuwa gare ta ta -InputFile, to, fita ba a buƙata, haka ma, yana haifar da kuskuren daidaitawa
  • -Fayil ɗin fitarwa a'a, umarnin yana mayar muku da sakamakon a matsayin abu
  • Akwai kalmomi guda biyu don tantance sabar: -ServerInstance -Sunan mai amfani -Password -Database kuma ta hanyar -ConnectionString. Abin ban mamaki, a cikin yanayin farko ba zai yiwu a ƙayyade tashar jiragen ruwa ba fiye da 1433 ba.
  • fitarwar rubutu, rubuta PRINT, wanda kawai “kama” sqlcmddon Kira-SqlCmd matsala ce
  • Kuma mafi mahimmanci: Wataƙila Linux ɗinku ba shi da wannan cmdlet!

Kuma wannan ita ce babbar matsalar. Kawai a cikin Maris wannan cmdlet ya zama samuwa don dandamalin da ba na Windows ba, kuma a ƙarshe za mu iya ci gaba!

Sauya Sauyawa

sqlcmd yana da canji mai canzawa ta amfani da -v, misali kamar haka:

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

A cikin rubutun SQL muna amfani da maye gurbin:

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

To ga shi nan. In *nix sauye-sauyen canji ba sa aiki. Siga -v watsi. U Kira-SqlCmd watsi -Masu canzawa. Ko da yake an yi watsi da ma'aunin da ke ayyana masu canjin da kansu, masu maye da kansu suna aiki - zaku iya amfani da kowane mai canji daga Shell. Duk da haka, na yi fushi da sauye-sauyen kuma na yanke shawarar ba zan dogara da su ba kwata-kwata, kuma na yi rashin kunya da rashin fahimta, tun da rubutun SQL gajere ne:

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

Wannan, kamar yadda kuka fahimta, gwaji ne riga daga sigar Unix.

Ana loda fayiloli

A cikin sigar Windows, kowane aiki yana tare da dubawa: mun gudu sqlcmd, mun sami wani nau'in cin zarafi a cikin fayil ɗin fitarwa, haɗe wannan fayil ɗin zuwa farantin duba. Abin farin ciki, uwar garken SQL yayi aiki akan sabar iri ɗaya kamar Jenkins, an yi wani abu kamar haka:

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

Don haka, muna haɗiye fayil ɗin BCP gaba ɗaya kuma mu tura shi cikin filin nvarchar(max) na teburin duba. Tabbas, wannan tsarin gabaɗayan ya lalace, saboda maimakon uwar garken SQL na sami RDS, kuma BULK INSERT ba ya aiki kwata-kwata ta hanyar UNC saboda yunƙurin ɗaukar keɓantaccen kullewa akan fayil, kuma tare da RDS gabaɗaya hakan ya lalace. farkon farkon. Don haka na yanke shawarar canza tsarin tsarin, adana layin duba ta layi:

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

Kuma rubuta a cikin wannan tebur kamar haka:

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

Don zaɓar abun ciki, kuna buƙatar zaɓar ta ID, zaɓin tsari n (shaida).

A cikin labarin na gaba zan yi cikakken bayani game da yadda wannan duka ke hulɗa da Jenkins.

source: www.habr.com

Add a comment