ProHoster > Π‘Π»ΠΎΠ³ > Kev tswj hwm > Ua haujlwm nrog MS SQL los ntawm Powershell ntawm Linux

Ua haujlwm nrog MS SQL los ntawm Powershell ntawm Linux

Kab lus no yog cov tswv yim dawb huv thiab muab siab rau kuv zaj dab neeg tu siab

Npaj rau Zero Kov PROD rau RDS (MS SQL), uas tag nrho peb lub pob ntseg tau buzzing, kuv tau ua ib qho kev nthuav qhia (POC - Pov Thawj Ntawm Lub Tswv Yim) ntawm automation: ib txheej ntawm powershell scripts. Tom qab qhov kev nthuav qhia, thaum muaj cua daj cua dub, lub suab nrov ntev ntev tuag, tig mus rau hauv kev npuaj tes tsis tu ncua, lawv tau hais rau kuv - tag nrho cov no yog qhov zoo, tab sis tsuas yog rau kev xav, tag nrho peb cov qhev Jenkins ua haujlwm ntawm Linux!

Qhov no puas ua tau? Siv qhov sov so, teeb DBA los ntawm hauv qab Windows thiab lo rau hauv lub tshav kub ntawm powershell hauv Linux? Qhov no tsis yog phem?

Ua haujlwm nrog MS SQL los ntawm Powershell ntawm Linux
Kuv yuav tsum raus kuv tus kheej nyob rau hauv no coj txawv txawv ua ke ntawm technologies. Tau kawg, tag nrho kuv 30+ scripts nres ua haujlwm. Ua rau kuv xav tsis thoob, kuv tau kho txhua yam hauv ib hnub ua haujlwm. Kuv sau ntawv kub nrhiav. Yog li, dab tsi pitfalls koj tuaj yeem ntsib thaum hloov powershell scripts los ntawm Windows rau Linux?

sqlcmd vs Invoke-SqlCmd

Cia kuv nco koj txog qhov sib txawv tseem ceeb ntawm lawv. Kev siv hluav taws xob zoo qub sqlcmd ua Nws kuj ua haujlwm nyob rau hauv Linux, nrog yuav luag zoo tib yam kev ua haujlwm. Peb dhau cov lus nug kom ua -Q, cov ntaub ntawv nkag raws li -i, thiab cov zis li -o. Tab sis cov npe ntawm cov ntaub ntawv, tau kawg, yog ua rau cov ntaub ntawv-sensitive. Yog tias koj siv -i, ces hauv cov ntaub ntawv sau thaum kawg:

GO
EXIT

Yog tias tsis muaj EXIT thaum kawg, ces sqlcmd yuav mus tos rau cov tswv yim, thiab yog ua ntej TAWM yuav tsis GO, ces cov lus txib kawg yuav tsis ua haujlwm. Cov ntaub ntawv tso zis muaj tag nrho cov zis, xaiv, lus, luam tawm, thiab lwm yam.

Invoke-SqlCmd ua qhov tshwm sim raws li DataSet, DataTables lossis DataRows. Yog li ntawd, yog tias koj ua cov txiaj ntsig ntawm kev xaiv yooj yim, koj tuaj yeem siv sqlcmd ua, muaj parsed nws cov zis, nws yog yuav luag tsis yooj yim sua kom muab tau tej yam complex: rau qhov no muaj Invoke-SqlCmd. Tab sis pab neeg no kuj muaj nws tus kheej tso dag:

  • Yog tias koj hloov cov ntaub ntawv rau nws ntawm -InputFile, ces TAWM tsis xav tau, tsis tas li ntawd, nws ua qhov yuam kev syntax
  • -OutputFile tsis yog, cov lus txib xa rov qab rau koj qhov tshwm sim ua ib yam khoom
  • Muaj ob lub syntax rau kev qhia ib tus neeg rau zaub mov: -ServerInstance -Username -Password -Database thiab dhau -ConnectionString. Oddly txaus, nyob rau hauv thawj rooj plaub nws tsis muaj peev xwm qhia ib qho chaw nres nkoj lwm yam tshaj li 1433.
  • ntawv tso zis, ntaus PRINT, uas tsuas yog "caught" sqlcmd uarau Invoke-SqlCmd yog ib qho teeb meem
  • Thiab qhov tseem ceeb tshaj plaws: Feem ntau yuav koj Linux tsis muaj cmdlet no!

Thiab qhov no yog qhov teeb meem loj. Tsuas yog lub Peb Hlis no cmdlet tau muaj rau cov tsis yog Windows platforms, thiab thaum kawg peb tuaj yeem txav mus tom ntej!

Hloov pauv hloov pauv

sqlcmd muaj qhov hloov pauv hloov pauv siv -v, piv txwv li no:

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

Hauv SQL tsab ntawv peb siv hloov pauv:

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

Yog li no nws yog. Ib* nix kev hloov pauv hloov pauv tsis ua haujlwm. Parameter -v tsis quav ntsej. U Invoke-SqlCmd tsis quav ntsej -Variables. Txawm hais tias qhov ntsuas uas qhia txog qhov sib txawv ntawm lawv tus kheej tsis quav ntsej, kev hloov pauv lawv tus kheej ua haujlwm - koj tuaj yeem siv txhua qhov hloov pauv ntawm Plhaub. Txawm li cas los xij, kuv tau npau taws los ntawm qhov sib txawv thiab txiav txim siab tsis nyob ntawm lawv txhua qhov, thiab ua tsis ncaj ncees thiab ua ntej, txij li SQL scripts luv luv:

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

Qhov no, raws li koj nkag siab, yog ib qho kev xeem dhau los ntawm Unix version.

Upload ntaub ntawv

Hauv Windows version, ib qho kev ua haujlwm tau nrog los ntawm kev tshawb xyuas: peb tau khiav sqlcmd, tau txais qee yam kev tsim txom hauv cov ntaub ntawv tso tawm, txuas cov ntaub ntawv no mus rau daim ntawv tshuaj xyuas. Hmoov zoo, SQL neeg rau zaub mov ua haujlwm ntawm tib lub server li Jenkins, nws tau ua qee yam zoo li no:

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

Yog li, peb nqos cov ntaub ntawv BCP tag nrho thiab thawb nws mus rau hauv nvarchar(max) teb ntawm lub rooj tshawb xyuas. Tau kawg, tag nrho cov kab ke no poob sib nrug, vim tias tsis yog SQL neeg rau zaub mov kuv tau txais RDS, thiab BULK INSERT tsis ua haujlwm ntawm UNC vim yog kev sim ua kom lub xauv tshwj xeeb rau ntawm cov ntaub ntawv, thiab nrog RDS qhov no feem ntau yog doomed los ntawm qhov pib. Yog li kuv txiav txim siab hloov qhov kev tsim qauv, khaws cia cov kab ntawv tshawb xyuas los ntawm kab:

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

Thiab sau rau hauv lub rooj zoo li no:

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

Txhawm rau xaiv cov ntsiab lus, koj yuav tsum xaiv los ntawm ID, xaiv hauv kev txiav txim n (tus kheej).

Hauv tsab xov xwm tom ntej no kuv yuav mus rau hauv kev nthuav dav ntxiv txog yuav ua li cas qhov no cuam tshuam nrog Jenkins.

Tau qhov twg los: www.hab.com

Ntxiv ib saib