ProHoster > Блог > Nchịkwa > Na-arụ ọrụ na MS SQL site na Powershell na Linux

Na-arụ ọrụ na MS SQL site na Powershell na Linux

Edemede a bụ naanị ihe bara uru ma rara ya nye akụkọ dị mwute m

Na-akwado maka Zero Touch PROD maka RDS (MS SQL), nke ntị anyị niile nọ na-ama jijiji, emere m ihe ngosi (POC - Proof Of Concept) nke akpaaka: otu edemede nke powershell. Mgbe ihe ngosi ahụ gasịrị, mgbe oké ifufe ahụ, na-eto eto ogologo oge nwụrụ, na-atụgharị n'ime ịkụ aka na-adịghị akwụsị akwụsị, ha gwara m - ihe a niile dị mma, ma ọ bụ naanị maka echiche echiche, ndị ohu Jenkins niile na-arụ ọrụ na Linux!

Nke a ọ ga-ekwe omume? Were ụdị ọkụ dị otú ahụ, oriọna DBA si n'okpuru Windows wee rapara ya na oke ọkụ nke powershell n'okpuru Linux? Nke a ọ́ bụghị obi ọjọọ?

Na-arụ ọrụ na MS SQL site na Powershell na Linux
Aghaghị m itinye onwe m n'ime usoro teknụzụ a dị ịtụnanya. N'ezie, m niile 30+ scripts kwụsịrị ịrụ ọrụ. O juru m anya na m jisiri ike dozie ihe niile n'otu ụbọchị ọrụ. Ana m ede ihe n'ịchụso ọkụ. Yabụ, kedu ọnyà ị ga-ezute mgbe ị na-ebufe scripts powershell site na Windows gaa na Linux?

sqlcmd vs oku-SqlCmd

Ka m chetara gị isi ihe dị iche n’etiti ha. Ezi ihe ochie ịba uru sqlcmd Ọ na-arụkwa ọrụ n'okpuru Linux, yana ihe fọrọ nke nta ka ọ bụrụ ọrụ ya. Anyị na-agafe ajụjụ iji mebie -Q, faịlụ ntinye dị ka -i, yana mmepụta dị ka -o. Mana aha faịlụ, n'ezie, na-eme ka ọ bụrụ nke nwere mmetụta. Ọ bụrụ na ị na-eji -i, yabụ na faịlụ dee na njedebe:

GO
EXIT

Ọ bụrụ na enweghị EXIT na njedebe, mgbe ahụ sqlcmd ga-aga n'ihu na-echere ntinye, ma ọ bụrụ na mbụ ỤZỌ ỌPỤPỤ agaghị GO, mgbe ahụ iwu ikpeazụ agaghị arụ ọrụ. Faịlụ mmepụta nwere mmepụta niile, nhọrọ, ozi, mbipụta, wdg.

Invoke-SqlCmd na-arụpụta nsonaazụ dị ka DataSet, DataTables ma ọ bụ DataRows. Ya mere, ọ bụrụ na ị hazie nsonaazụ nke nhọrọ dị mfe, ị nwere ike iji sqlcmd, N'ịkọwapụta mmepụta ya, ọ fọrọ nke nta ka ọ bụrụ na ọ gaghị ekwe omume ịmepụta ihe mgbagwoju anya: maka nke a nwere Kpọọ-SqlCmd. Mana otu a nwekwara njakịrị nke ya:

  • Ọ bụrụ na ị bufee ya faịlụ site na -File ntinyemgbe ahụ ỤZỌ ỌPỤPỤ adịghị mkpa, Ọzọkwa, ọ na-arụpụta a syntax njehie
  • -File mmepụta mba, iwu ahụ na-eweghachi gị nsonaazụ dịka ihe
  • Enwere syntaxes abụọ maka ịkọwa sava: -ServerInstance -Aha njirimara -Password -Database ma site na -ConnectionString. N'ụzọ dị ịtụnanya, na nke mbụ, ọ gaghị ekwe omume ịkọwa ọdụ ụgbọ mmiri karịa 1433.
  • mmepụta ederede, pịnye PRINT, nke a na-ejide naanị sqlcmd, n'ihi na Kpọọ-SqlCmd bụ nsogbu
  • Nke kachasị mkpa: O yikarịrị ka Linux gị enweghị cmdlet a!

Ma nke a bụ isi nsogbu. Naanị na March cmdlet a ghọrọ maka nyiwe na-abụghị Windows, na n'ikpeazụ anyị nwere ike ịga n'ihu!

Ngbanwe mgbanwe

sqlcmd nwere mgbanwe mgbanwe site na iji -v, dịka ọmụmaatụ dịka nke a:

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

N'ime edemede SQL anyị na-eji nnọchi:

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

Ya mere, ebe a. Na *nix mgbanwe mgbanwe anaghị arụ ọrụ... Oke -v leghaara anya. U Kpọọ-SqlCmd leghaara anya -Ngbanwe. Ọ bụ ezie na a na-eleghara oke nke na-akọwapụta mgbanwe ndị ahụ n'onwe ha, ngbanwe ndị ahụ n'onwe ha na-arụ ọrụ-ị nwere ike iji mgbanwe ọ bụla sitere na Shell. Agbanyeghị, enwere m iwe site na mgbanwe ndị ahụ wee kpebie ịghara ịdabere na ha ma ọlị, wee mee omume rụrụ arụ na nke mbụ, ebe ọ bụ na script SQL dị mkpụmkpụ:

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

Nke a, dịka ị ghọtara, bụ nnwale sitere na ụdị Unix.

Na -ebugote faịlụ

Na ụdị Windows, ọrụ ọ bụla sonye na nyocha: anyị gbara sqlcmd, nweta ụdị mmegbu na faịlụ mmepụta, tinye faịlụ a na efere nyocha. Ọ dabara nke ọma, SQL nkesa na-arụ ọrụ na otu ihe nkesa dị ka Jenkins, e mere ihe dị ka nke a:

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

Yabụ, anyị na-eloda faịlụ BCP kpamkpam wee tụba ya n'ọhịa nvarchar(max) nke tebụl nyocha. N'ezie, usoro a dum dara, n'ihi na kama ihe nkesa SQL enwetara m RDS, na BULK INSERT anaghị arụ ọrụ ma ọlị site na UNC n'ihi mgbalị iji were mkpọchi naanị na faịlụ, yana RDS nke a na-emebikarị site na. mmalite. Ya mere ekpebiri m ịgbanwe usoro nhazi ahụ, na-echekwa ahịrị nyocha site na ahịrị:

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

Ma dee na tebụl a dị ka nke a:

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

Ka ịhọrọ ọdịnaya, ịkwesịrị ịhọrọ site na NJ, họrọ n'usoro n ( njirimara).

N'isiokwu na-esonụ, m ga-abanyekwu nkọwa banyere otú ihe a niile si eme Jenkins.

isi: www.habr.com

Tinye a comment