ProHoster > blog > administratie > Werken met MS SQL van Powershell op Linux

Werken met MS SQL van Powershell op Linux

Dit artikel is puur praktisch en is gewijd aan mijn trieste verhaal

Klaarmaken voor Zero Touch PROD voor RDS (MS SQL), waar al onze oren van gonsden, maakte ik een presentatie (POC - Proof Of Concept) van automatisering: een set powershell-scripts. Na de presentatie, toen het stormachtige, langdurige applaus wegstierf en in onophoudelijk applaus veranderde, vertelden ze me: dit is allemaal goed, maar alleen om ideologische redenen werken al onze Jenkins-slaven op Linux!

Is dit mogelijk? Neem zo'n warme lamp DBA van onder Windows en plak deze in de hitte van powershell onder Linux? Is dit niet wreed?

Werken met MS SQL van Powershell op Linux
Ik moest mij verdiepen in deze vreemde combinatie van technologieën. Natuurlijk werkten al mijn 30+ scripts niet meer. Tot mijn verbazing lukte het mij om alles binnen één werkdag te repareren. Ik schrijf in een achtervolging. Welke valkuilen kun je tegenkomen bij het overbrengen van powershell-scripts van Windows naar Linux?

sqlcmd versus Invoke-SqlCmd

Laat me u herinneren aan het belangrijkste verschil tussen beide. Goede oude nutsvoorziening sqlcmd Het werkt ook onder Linux, met vrijwel identieke functionaliteit. We geven de query door om -Q uit te voeren, het invoerbestand als -i en de uitvoer als -o. Maar de bestandsnamen zijn uiteraard hoofdlettergevoelig gemaakt. Als je -i gebruikt, schrijf dan in het bestand aan het einde:

GO
EXIT

Als er aan het einde geen EXIT is, zal sqlcmd doorgaan met wachten op invoer, en indien eerder EXIT zal niet GO, dan zal het laatste commando niet werken. Het uitvoerbestand bevat alle uitvoer, selecties, berichten, afdrukken, enz.

Invoke-SqlCmd produceert het resultaat als een DataSet, DataTables of DataRows. Daarom kunt u gebruiken als u het resultaat van een eenvoudige selectie verwerkt sqlcmd, na de uitvoer ervan te hebben ontleed, is het bijna onmogelijk om iets complex af te leiden: hiervoor is er Roep-SqlCmd aan. Maar dit team heeft ook zijn eigen grappen:

  • Als u een bestand naar haar overdraagt ​​via -Invoer bestanddan EXIT niet nodig, bovendien veroorzaakt het een syntaxisfout
  • -Uitvoerbestand nee, de opdracht retourneert u het resultaat als een object
  • Er zijn twee syntaxis voor het opgeven van een server: -ServerInstance -Gebruikersnaam -Wachtwoord -Database en door -Verbindingsdraad. Vreemd genoeg is het in het eerste geval niet mogelijk om een ​​andere poort dan 1433 op te geven.
  • tekstuitvoer, typ PRINT, die eenvoudigweg wordt "gevangen" sqlcmdvoor Roep-SqlCmd aan is een probleem
  • En het belangrijkste: Hoogstwaarschijnlijk heeft uw Linux deze cmdlet niet!

En dit is het grootste probleem. Pas in maart wordt deze cmdlet beschikbaar kwam voor niet-Windows-platforms, en eindelijk kunnen we verder!

Variabele vervanging

sqlcmd heeft variabelevervanging met behulp van -v, bijvoorbeeld als volgt:

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

In het SQL-script gebruiken we vervangingen:

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

Dus hier is het. In *nix Variabele vervangingen werken niet. Parameter -v buiten beschouwing gelaten. U Roep-SqlCmd aan buiten beschouwing gelaten -Variabelen. Hoewel de parameter die de variabelen zelf specificeert, wordt genegeerd, werken de vervangingen zelf; u kunt alle variabelen van Shell gebruiken. Ik was echter beledigd door de variabelen en besloot er helemaal niet afhankelijk van te zijn, en handelde grof en primitief, aangezien de SQL-scripts kort zijn:

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

Dit is, zoals je begrijpt, al een test van de Unix-versie.

Bestanden uploaden

In de Windows-versie ging elke bewerking gepaard met een audit: we voerden sqlcmd uit, ontvingen een soort misbruik in het uitvoerbestand en voegden dit bestand toe aan de auditplaat. Gelukkig werkte de SQL-server op dezelfde server als Jenkins, het gebeurde ongeveer als volgt:

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

We slikken het BCP-bestand dus volledig in en schuiven het in het nvarchar(max)-veld van de audittabel. Natuurlijk viel dit hele systeem uit elkaar, want in plaats van een SQL-server kreeg ik RDS, en BULK INSERT werkt helemaal niet via UNC vanwege een poging om een ​​bestand exclusief te vergrendelen, en met RDS is dit over het algemeen gedoemd het allereerste begin. Daarom besloot ik het systeemontwerp te veranderen en de audit regel voor regel op te slaan:

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

En schrijf in deze tabel als volgt:

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

Om inhoud te selecteren, moet u selecteren op ID, in volgorde n (identiteit).

In het volgende artikel zal ik dieper ingaan op hoe dit allemaal samenwerkt met Jenkins.

Bron: www.habr.com

Voeg een reactie