ProHoster > Блог > Whakahaerenga > Mahi tahi me MS SQL mai i Powershell i runga i te Linux

Mahi tahi me MS SQL mai i Powershell i runga i te Linux

He tino mahi tenei tuhinga, he mea whakatapua ki taku korero pouri

Te whakarite mo Kore Touch PROD mo te RDS (MS SQL), e haruru ana o matou taringa katoa, i hanga e au he whakaaturanga (POC - Tohu O te Ariā) o te aunoatanga: he huinga tuhinga hiko. I muri i te whakaaturanga, i te wa i mate ai te ngaru, te pakipaki roa, ka huri ki te pakipaki mau tonu, ka kii mai ratou ki ahau - he pai katoa, engari mo nga take whakaaro anake, kei te mahi a tatou pononga Jenkins katoa i runga i te Linux!

Ka taea tenei? Tangohia he DBA mahana, rama mai i raro i te Matapihi ka mau ki roto i te wera o te powershell i raro i te Linux? Ehara tenei i te nanakia?

Mahi tahi me MS SQL mai i Powershell i runga i te Linux
Me ruku au ki tenei huinga hangarau rerekee. Ae ra, ka mutu aku tuhinga 30+ katoa i te mahi. Ki taku ohorere, i kaha ahau ki te whakatika i nga mea katoa i te ra mahi kotahi. Kei te tuhi ahau i roto i te whai wera. Na, he aha nga raru ka pa ki a koe i te wa e whakawhiti ana i nga tuhinga hiko mai i Windows ki Linux?

sqlcmd vs Invoke-SqlCmd

Kia whakamahara ahau ki a koe mo te rereketanga nui i waenga i a raatau. He pai nga taputapu tawhito sqlcmd Ka mahi ano i raro i te Linux, he rite tonu te mahi. Ka tukuna e matou te patai ki te mahi -Q, te konae whakauru hei -i, me te putanga hei -o. Engari ko nga ingoa o nga konae, ko te tikanga, he take-tairongo. Mena ka whakamahi koe -i, ka tuhia ki te konae i te mutunga:

GO
EXIT

Mena karekau he PUTA i te mutunga, ka haere tonu a sqlcmd ki te tatari mo te whakaurunga, a mena i mua Waiho kaore GO, ka kore te whakahau whakamutunga e mahi. Kei roto i te konae putanga nga putanga katoa, nga kowhiringa, nga karere, te ta, me era atu.

Invoke-SqlCmd ka whakaputa i te hua hei Raraunga Raraunga, Ripanga Raraunga, RaraungaRaraunga ranei. Na reira, ki te tukatuka koe i te hua o te kowhiringa ngawari, ka taea e koe te whakamahi sqlcmd, ka tohatohahia tana whakaputanga, kaore e taea te whakaputa i tetahi mea uaua: mo tenei kei reira Tonoa-SqlCmd. Engari he kata ano ta tenei kapa:

  • Mena ka whakawhiti koe i tetahi konae ki a ia ma te -Kōnae Whakauru, ka Waiho e kore e hiahiatia, i tua atu, ka puta he hapa wetereo
  • -Kōnae Huaputa kāo, ka whakahokia e te whakahau te hua hei ahanoa
  • E rua nga wetereo mo te tautuhi i te tūmau: -ServerInstance -Ingoa Kaiwhakamahi -Kupuhipa -Patengi Raraunga a puta noa -Kua Hononga. He mea whakamiharo, i te keehi tuatahi kaore e taea te tohu i tetahi tauranga i tua atu i te 1433.
  • putanga kuputuhi, momo TAIA, ka "hopu" noa sqlcmdmo Tonoa-SqlCmd he raruraru
  • A ko te mea nui: Ko te mea pea kaore to Linux i tenei cmdlet!

A koinei te tino raruraru. No Maehe anake tenei cmdlet i waatea mo nga papaaho kore-Windows, ka mutu ka anga whakamua tatou!

Whakakapinga taurangi

sqlcmd he whakakapinga rereke ma te whakamahi -v, hei tauira penei:

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

I roto i te tuhinga SQL ka whakamahia e matou nga whakakapinga:

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

Na konei ko tenei. Kei *nix kare e mahi nga whakakapinga taurangi. Tawhā -v kare i arohia. U Tonoa-SqlCmd kare i arohia -Taupee. Ahakoa ka warewarehia te tawhā e tohu ana i nga taurangi ake, ka mahi tonu nga whakakapinga—ka taea e koe te whakamahi i nga taurangi mai i Shell. Heoi, i whakaparahako ahau ki nga taurangi me te whakatau kia kaua e ti'aturi ki runga i a raatau, me te mahi poauau me te ahua o mua, na te mea he poto nga tuhinga SQL:

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

Ko tenei, e mohio ana koe, he whakamatautau mai i te putanga Unix.

Tukuake kōnae

I roto i te putanga Windows, ko tetahi mahi i haere tahi me te arotake: i whakahaerehia e matou te sqlcmd, i whiwhi i etahi momo mahi kino i roto i te konae whakaputa, i piri tenei konae ki te pereti arotake. Waimarie, i mahi te tūmau SQL i runga i te tūmau rite ki a Jenkins, he penei te mahi:

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

No reira, ka horomia katoatia te konae BCP ka panaia ki te mara nvarchar(max) o te ripanga kaute. Ae ra, kua pakaru katoa tenei punaha, na te mea he tūmau SQL ka whiwhi ahau i te RDS, karekau te BULK INSERT e mahi ma te UNC na te ngana ki te tango raka motuhake i runga i tetahi konae, me te RDS ka mate tenei. te timatanga rawa. Na ka whakatau ahau ki te whakarereke i te hoahoa punaha, te penapena i te rarangi kaute ma te raina:

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

A ka tuhi ki tenei ripanga penei:

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

Hei kowhiri i nga ihirangi, me kowhiri e koe ma te ID, te whiriwhiri i te raupapa n (tuakiri).

I roto i te tuhinga e whai ake nei ka haere ahau ki nga korero mo te pehea o enei mea katoa ki a Jenkins.

Source: will.com

Tāpiri i te kōrero