Ke hana nei me MS SQL mai Powershell ma Linux

He hana maoli kēia ʻatikala a hoʻolaʻa ʻia i kaʻu moʻolelo kaumaha

E hoʻomākaukau ana no Zero Touch PROD no RDS (MS SQL), kahi e kani ai ko mākou mau pepeiao a pau, ua hana au i kahi hōʻike (POC - Hōʻoiaʻiʻo o ka Manaʻo) o ka automation: kahi pūʻulu o nā palapala mana. Ma hope o ka hōʻike ʻana, i ka wā i pau ai ka ʻino, a me ka lōʻihi o ka paʻipaʻi ʻana, ua ʻōlelo mai lākou iaʻu - maikaʻi kēia mau mea, akā no nā kumu ideological wale nō, ke hana nei kā mākou mau kauā Jenkins āpau ma Linux!

Hiki paha kēia? E lawe i kahi kukui DBA mahana mai lalo o Windows a hoʻopaʻa iā ia i ka wela o ka powershell ma lalo o Linux? ʻAʻole anei kēia ʻino?

Pono wau e hoʻokomo iaʻu iho i kēia hui ʻana o nā ʻenehana. ʻOiaʻiʻo, pau kaʻu mau palapala 30+ a pau i ka hana. I koʻu kahaha, ua hiki iaʻu ke hoʻoponopono i nā mea a pau i hoʻokahi lā hana. Ke kākau nei au ma ka hahai wela. No laila, he aha nā pilikia e hiki ai iā ʻoe ke hālāwai i ka wā e hoʻololi ai i nā palapala mana powershell mai Windows a Linux?

sqlcmd vs Invoke-SqlCmd

E hoʻomanaʻo wau iā ʻoe i ka ʻokoʻa nui ma waena o lākou. Maikaʻi ka mea hoʻohana kahiko sqlcmd Hana pū ia ma lalo o Linux, me ka hana like. Hāʻawi mākou i ka nīnau e hoʻokō -Q, ka faila hoʻokomo e like me -i, a me ka hoʻopuka e like me -o. Akā ʻo nā inoa faila, ʻoiaʻiʻo, ua hana ʻia i ka hihia. Inā ʻoe e hoʻohana -i, a laila ma ka faila e kākau i ka hopena:

GO
EXIT

Inā ʻaʻohe EXIT ma ka hopena, e hoʻomau ʻo sqlcmd e kali no ka hoʻokomo, a inā ma mua Puka ʻAʻole anei GO, a laila ʻaʻole e holo ka kauoha hope. Aia i loko o ka waihona puka nā huahana a pau, nā koho, nā memo, paʻi, etc.

Hoʻopuka ʻo Invoke-SqlCmd i ka hopena ma ke ʻano he DataSet, DataTables a i ʻole DataRows. No laila, inā ʻoe e hana i ka hopena o kahi koho maʻalahi, hiki iā ʻoe ke hoʻohana sqlcmd, i ka pau ʻana o kāna huahana, aneane hiki ʻole ke loaʻa i kahi mea paʻakikī: no kēia Kāhea-SqlCmd. Akā, aia nō kēia hui i kāna mau ʻakaʻaka ponoʻī:

• Inā hoʻoili ʻoe i kahi faila iā ia ma o -Pila Hoʻokomoalaila Puka ʻAʻole pono, ʻo ia hoʻi, hana ia i kahi hewa syntax
• -Kōnaewele ʻaʻole, hoʻihoʻi ke kauoha iā ʻoe i ka hopena ma ke ʻano he mea
• ʻElua mau syntax no ka wehewehe ʻana i kahi kikowaena: -ServerInstance -Username -Password -Database a ma o -Hoʻohuihui. ʻO ka mea kupanaha, i ka hihia mua ʻaʻole hiki ke kuhikuhi i kahi awa ʻē aʻe ma mua o 1433.
• hua kikokikona, ʻano PRINT, i "hopu" wale ʻia sqlcmdno Kāhea-SqlCmd he pilikia
• A ʻo ka mea nui loa: Loaʻa paha i kāu Linux kēia cmdlet!

A ʻo kēia ka pilikia nui. Ma Malaki wale nō kēia cmdlet i loaʻa no nā paepae ʻole Windows, a i ka hopena hiki iā mākou ke neʻe i mua!

Hoʻololi hoʻololi

sqlcmd he loli hoʻololi me ka hoʻohana ʻana -v, no ka laʻana e like me kēia:

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

Ma ka palapala SQL hoʻohana mākou i nā pani:

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

No laila eia kēia. Ma *nix ʻAʻole pono nā pani ʻokoʻa... Parameter -v mālama ʻole ʻia. U Kāhea-SqlCmd mālama ʻole ʻia -Nā hoʻololi. ʻOiai ʻaʻole mālama ʻia ka ʻāpana e kuhikuhi ana i nā mea hoʻololi ponoʻī, hana nā mea hoʻololi iā lākou iho - hiki iā ʻoe ke hoʻohana i nā ʻano like ʻole mai Shell. Eia naʻe, ua hōʻeha wau i nā mea hoʻololi a ua hoʻoholo wau ʻaʻole e hilinaʻi iā lākou, a ua hana ʻino a me ka primitively, no ka mea he pōkole nā ​​palapala 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"

ʻO kēia, e like me kāu e hoʻomaopopo ai, he hoʻāʻo mai ka mana Unix.

Hoʻouka i nā faila

Ma ka Windows version, ua hui pū ʻia kekahi hana me ka loiloi: holo mākou i ka sqlcmd, loaʻa kekahi ʻano hana hoʻomāinoino i ka faila puka, hoʻopili i kēia faila i ka pā loiloi. ʻO ka mea pōmaikaʻi, ua hana ʻo SQL server ma ka server like me Jenkins, ua hana ʻia e like me kēia:

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 laila, ale mākou i ka faila BCP a hoʻokuʻu iā ia i loko o ke kahua nvarchar(max) o ka papa hoʻoponopono. ʻOiaʻiʻo, ua hāʻule kēia ʻōnaehana holoʻokoʻa, no ka mea ma kahi o kahi kikowaena SQL ua loaʻa iaʻu ka RDS, a ʻaʻole hana ʻo BULK INSERT ma o UNC ma muli o ka hoʻāʻo e lawe i kahi laka kūʻokoʻa ma kahi faila, a me RDS e hoʻopaʻa ʻia kēia mai. ka hoomaka ana. No laila ua hoʻoholo wau e hoʻololi i ka hoʻolālā ʻōnaehana, e mālama i ka laina loiloi ma ka laina:

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

A e kākau ma kēia papa e like me kēia:

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

No ke koho ʻana i ka ʻike, pono ʻoe e koho ma o ka ID, ke koho ʻana ma ke ʻano n (identity).

Ma ka ʻatikala aʻe e hele au i nā kikoʻī hou aʻe e pili ana i ka pili ʻana o kēia me Jenkins.

Source: www.habr.com