Galulue ma MS SQL mai Powershell i luga o Linux

O lenei tusiga e mama atoatoa ma e tuuto atu i laʻu tala faanoanoa

Sauniuni mo Zero Touch PROD mo le RDS (MS SQL), lea na faʻalogo uma ai o matou taliga, na ou faia se faʻaaliga (POC - Faʻamaoniga O le Manatu) o le masini: o se seti o faʻamaumauga o le malosi. Ina ua uma le folasaga, ina ua mou atu le patipati faaumiumi, ma liliu atu i le patipati faifaipea, sa latou fai mai ia te aʻu - o nei mea uma e lelei, ae na o mafuaaga faʻapitoa, o matou pologa Jenkins uma e galulue i Linux!

Pe mafai ea lenei mea? Ave se moli mafanafana DBA mai lalo o Windows ma faʻapipiʻi i le vevela o le powershell i lalo o Linux? Pe le saua ea lea mea?

Sa tatau ona ou faatofuina aʻu i lenei tuufaatasiga uiga ese o tekinolosi. Ioe, o aʻu tusitusiga 30+ uma na le toe galue. Na ou teʻi, na mafai ona ou toe faaleleia mea uma i le aso faigaluega e tasi. O loʻo ou tusitusi i le sailiga vevela. O lea la, o a ni fa'alavelave e mafai ona e feagai pe a fesiita'i fa'amaumauga mana mai Windows i Linux?

sqlcmd vs Invoke-SqlCmd

Sei ou faamanatu atu ia te oe le eseesega autu i le va oi latou. Lelei tuai aoga sqlcmd E galue foi i lalo o Linux, ma toetoe lava tutusa galuega. Matou te pasia le fesili e faʻatino -Q, le faila faila e pei o -i, ma le gaioiga e pei -o. Ae o igoa faila, o le mea moni, ua faia faʻamatalaga mataʻutia. Afai e te faʻaaogaina -i, ona tusi lea i le faila ile pito:

GO
EXIT

Afai e leai se EXIT i le faaiuga, o le sqlcmd o le a faʻaauau ona faʻatali mo le faʻaoga, ma afai muamua ALUFAU e leai GO, ona le aoga lea o le poloaiga mulimuli. O le faila faila o loʻo i ai mea uma, filifiliga, feʻau, lolomi, ma isi.

Invoke-SqlCmd e maua ai le taunuʻuga o se DataSet, DataTables poʻo DataRows. O le mea lea, afai e te faʻaogaina le taunuuga o se filifiliga faigofie, e mafai ona e faʻaogaina sqlcmd, i le faʻavasegaina o lona gaosiga, e toetoe lava a le mafai ona maua se mea faigata: mo lenei mea o loʻo i ai Valaau-SqlCmd. Ae o lenei 'au e iai foi a latou lava tala malie:

• Afai e te tu'uina atu se faila ia te ia e ala i -InputFile, ona ALUFAU e le manaʻomia, e le gata i lea, e maua ai se faʻasologa o le syntax
• -Faila Fa'amatalaga leai, o le poloaiga e toe faafoi atu ia te oe le taunuuga o se mea faitino
• E lua syntax mo le faʻamaonia o se server: -ServerInstance -Username -Password -Database ma ala atu -ConnectionString. O le mea e ese ai, i le tulaga muamua e le mafai ona faʻamaonia se taulaga e ese mai i le 1433.
• tusitusiga, fa'apena LOMI, lea e na'o le "pu'ea" sqlcmdmo Valaau-SqlCmd ose faafitauli
• Ma sili ona taua: E foliga mai e le maua e lau Linux lenei cmdlet!

Ma o le faafitauli autu lea. Na'o Mati lenei cmdlet na avanoa mo tulaga e le o Windows, ma mulimuli ane e mafai ona tatou agai i luma!

Suiga Suiga

sqlcmd o loʻo i ai suiga fesuisuiai e faʻaaoga ai -v, mo se faʻataʻitaʻiga pei o lenei:

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

I totonu o le SQL script matou te faʻaaogaina mea e sui ai:

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

O lea la. I *nix e le aoga suiga fesuisuiai. Parameter -v le amanaia. U Valaau-SqlCmd le amanaia -Fesuiaiga. E ui lava e le amanaʻia le parakalafa o loʻo faʻamaonia ai ia fesuiaiga, o suiga lava ia e galue-e mafai ona e faʻaogaina soʻo se fesuiaiga mai Shell. Ae ui i lea, sa ou le fiafia i fesuiaiga ma filifili e aua le faalagolago ia i latou uma, ma sa ou amio le mafaufau ma muamua, talu ai o tusitusiga SQL e puupuu:

# 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 lenei, e pei ona e malamalama ai, o se suʻega ua uma ona mai le Unix version.

La'uina faila

I le Windows version, soʻo se taʻaloga na faʻatasi ma se suʻega: matou te tamoe sqlcmd, maua se ituaiga faʻaleagaina i le faila faila, faʻapipiʻi lenei faila i le ipu suʻega. O le mea e lelei ai, sa galue le SQL server i le server tutusa e pei o Jenkins, na faia se mea e pei o lenei:

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

O lea, matou te foloina atoa le faila BCP ma tulei i totonu o le fanua nvarchar(max) o le laulau suetusi. O le mea moni, o lenei faiga atoa na malepelepe, aua nai lo le SQL server na ou maua le RDS, ma o le BULK INSERT e le aoga i le UNC ona o se taumafaiga e ave se loka faapitoa i luga o se faila, ma faatasi ai ma le RDS e masani lava ona faʻaumatia mai. o le amataga lava. O lea na ou filifili ai e sui le mamanu o le polokalama, teuina le suʻega laina i lea laina:

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

Ma tusi i lenei laulau e pei o lenei:

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

Ina ia filifili mea, e tatau ona e filifili e ala ile ID, filifili ile faasologa n (identity).

I le isi tusiga o le a ou faʻamatalaina atili pe faʻafefea ona fegalegaleai uma ma Jenkins.

puna: www.habr.com