Darbs ar MS SQL no Powershell operētājsistēmā Linux

Šis raksts ir tīri praktisks un veltīts manam skumjam stāstam

Gatavojamies Zero Touch PROD RDS (MS SQL), par kuru mums šķindēja visas ausis, es izveidoju automatizācijas prezentāciju (POC - Proof Of Concept): Powershell skriptu komplekts. Pēc prezentācijas, kad vētrainie, ilgstošie aplausi norima, pārvēršoties nemitīgos aplausos, man teica – tas viss ir labi, bet tikai ideoloģisku apsvērumu dēļ visi mūsu Dženkinsa vergi strādā uz Linux!

Vai tas ir iespējams? Paņemt tādu siltu, lampu DBA no zem Windows un ielīmēt to pašā powershell karstumā zem Linux? Vai tas nav nežēlīgi?

Man nācās iegremdēties šajā dīvainajā tehnoloģiju kombinācijā. Protams, visi mani 30+ skripti pārstāja darboties. Man par pārsteigumu izdevās visu salabot vienas darba dienas laikā. Es rakstu, dzenoties uz karstām pēdām. Tātad, ar kādām nepilnībām jūs varat saskarties, pārsūtot Powershell skriptus no Windows uz Linux?

sqlcmd pret Invoke-SqlCmd

Ļaujiet man jums atgādināt galveno atšķirību starp tiem. Vecā labā utilīta sqlcmd Tas darbojas arī operētājsistēmā Linux ar gandrīz identisku funkcionalitāti. Mēs nododam vaicājumu, lai izpildītu -Q, ievades failu kā -i un izvadi kā -o. Bet failu nosaukumos, protams, ir reģistrjutīgi. Ja izmantojat -i, faila beigās ierakstiet:

GO
EXIT

Ja beigās nav EXIT, sqlcmd turpinās gaidīt ievadi, un, ja pirms EXIT nebūs GO, tad pēdējā komanda nedarbosies. Izvades failā ir visa izvade, atlase, ziņojumi, drukāšana utt.

Invoke-SqlCmd rada rezultātu kā DataSet, DataTables vai DataRows. Tāpēc, ja apstrādājat vienkāršas atlases rezultātu, varat to izmantot sqlcmd, parsējot tā izvadi, ir gandrīz neiespējami iegūt kaut ko sarežģītu: tas ir Invoke-SqlCmd. Bet šai komandai ir arī savi joki:

• Ja pārsūtāt viņai failu, izmantojot -Ievades failstad EXIT nav nepieciešams, turklāt tas rada sintakses kļūdu
• -Izvades fails nē, komanda atgriež rezultātu kā objektu
• Servera norādīšanai ir divas sintakses: -ServerInstance -Lietotājvārds -Parole -Datu bāze un cauri -Savienojuma virkne. Savādi, ka pirmajā gadījumā nav iespējams norādīt citu ostu, izņemot 1433.
• teksta izvade, ierakstiet PRINT, kas tiek vienkārši “noķerta” sqlcmdpar Invoke-SqlCmd ir problēma
• Un pats galvenais: Visticamāk, jūsu Linux nav šīs cmdlet!

Un tā ir galvenā problēma. Tikai martā šis cmdlet kļuva pieejams platformām, kas nav Windows, un beidzot mēs varam virzīties uz priekšu!

Mainīgā aizstāšana

sqlcmd ir mainīgo aizstāšana, izmantojot -v, piemēram, šādi:

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

SQL skriptā mēs izmantojam aizstāšanas veidus:

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

Tātad šeit tas ir. In *nix mainīgo aizstāšana nedarbojas. Parametrs -v ignorēts. U Invoke-SqlCmd ignorēts -Mainīgie. Lai gan parametrs, kas norāda pašus mainīgos, tiek ignorēts, paši aizvietojumi darbojas — jūs varat izmantot jebkuru Shell mainīgo. Tomēr mani aizvainoja mainīgie un nolēmu nebūt no tiem atkarīgs un rīkojos rupji un primitīvi, jo SQL skripti ir īsi:

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

Tas, kā jūs saprotat, ir tests jau no Unix versijas.

Failu augšupielāde

Windows versijā jebkurai darbībai tika pievienots audits: mēs palaidām sqlcmd, saņēmām sava veida ļaunprātīgu izmantošanu izvades failā, pievienojām šo failu audita plāksnei. Par laimi, SQL serveris strādāja tajā pašā serverī, kur Dženkinss, tas tika darīts apmēram šādi:

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

Tādējādi mēs pilnībā norijam BCP failu un ievietojam to audita tabulas laukā nvarchar(max). Protams, visa šī sistēma sabruka, jo SQL servera vietā es dabūju RDS, un BULK INSERT nedarbojas vispār caur UNC, jo tika mēģināts veikt ekskluzīvu bloķēšanu failam, un ar RDS tas parasti ir lemts. pats sākums. Tāpēc es nolēmu mainīt sistēmas dizainu, saglabājot auditu pēc rindas:

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

Un ierakstiet šajā tabulā šādi:

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

Lai atlasītu saturu, jāatlasa pēc ID, izvēloties secībā n (identitāte).

Nākamajā rakstā es sīkāk pastāstīšu par to, kā tas viss mijiedarbojas ar Dženkinsu.

Avots: www.habr.com