ProHoster > Log > administrasjon > Arbeide med MS SQL fra Powershell på Linux

Arbeide med MS SQL fra Powershell på Linux

Denne artikkelen er rent praktisk og er dedikert til min triste historie

Gjør seg klar til Zero Touch PROD for RDS (MS SQL), som alle våre ører surret om, laget jeg en presentasjon (POC - Proof Of Concept) av automatisering: et sett med powershell-skript. Etter presentasjonen, da den stormende, langvarige applausen stilnet og ble til ustanselig applaus, sa de til meg - alt dette er bra, men bare av ideologiske grunner jobber alle Jenkins-slavene våre på Linux!

Er dette mulig? Ta en slik varm, lampe DBA fra under Windows og stikk den inn i selve heten av powershell under Linux? Er ikke dette grusomt?

Arbeide med MS SQL fra Powershell på Linux
Jeg måtte fordype meg i denne merkelige kombinasjonen av teknologier. Selvfølgelig sluttet alle mine 30+ skript å fungere. Til min overraskelse klarte jeg å fikse alt på en arbeidsdag. Jeg skriver i jakten. Så, hvilke fallgruver kan du støte på når du overfører powershell-skript fra Windows til Linux?

sqlcmd vs Invoke-SqlCmd

La meg minne deg om hovedforskjellen mellom dem. Godt gammelt verktøy sqlcmd Det fungerer også under Linux, med nesten identisk funksjonalitet. Vi sender spørringen for å utføre -Q, inngangsfilen som -i, og utgangen som -o. Men filnavnene skilles selvfølgelig med store og små bokstaver. Hvis du bruker -i, så skriv på slutten i filen:

GO
EXIT

Hvis det ikke er EXIT på slutten, vil sqlcmd fortsette å vente på inndata, og hvis før EXIT vil ikke GO, da vil ikke den siste kommandoen fungere. Utdatafilen inneholder alle utdata, valg, meldinger, utskrift osv.

Invoke-SqlCmd produserer resultatet som et datasett, datatabeller eller datarader. Derfor, hvis du behandler resultatet av et enkelt utvalg, kan du bruke sqlcmd, etter å ha analysert utgangen, er det nesten umulig å utlede noe komplekst: for dette er det Invoke-SqlCmd. Men dette laget har også sine egne vitser:

  • Hvis du overfører en fil til henne via -Inputfilderetter EXIT ikke nødvendig, dessuten produserer det en syntaksfeil
  • -Output-fil nei, kommandoen gir deg resultatet som et objekt
  • Det er to syntakser for å spesifisere en server: -ServerInstance -Brukernavn -Passord -Database og gjennom -ConnectionString. Merkelig nok, i det første tilfellet er det ikke mulig å spesifisere en annen port enn 1433.
  • tekstutgang, skriv PRINT, som ganske enkelt "fanges" sqlcmdtil Invoke-SqlCmd er et problem
  • Og viktigst: Mest sannsynlig har din Linux ikke denne cmdleten!

Og dette er hovedproblemet. Bare i mars denne cmdlet ble tilgjengelig for ikke-Windows-plattformer, og endelig kan vi gå videre!

Variabel substitusjon

sqlcmd har variabel substitusjon ved å bruke -v, for eksempel slik:

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

I SQL-skriptet bruker vi substitusjoner:

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

Så her er det. I *nix variable erstatninger fungerer ikke. Parameter -v ignorert. U Invoke-SqlCmd ignorert - Variabler. Selv om parameteren som spesifiserer selve variablene ignoreres, fungerer selve erstatningene – du kan bruke alle variabler fra Shell. Jeg ble imidlertid fornærmet av variablene og bestemte meg for ikke å være avhengig av dem i det hele tatt, og handlet frekt og primitivt, siden SQL-skriptene er korte:

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

Dette er, som du forstår, en test allerede fra Unix-versjonen.

Laster opp filer

I Windows-versjonen ble enhver operasjon ledsaget av en revisjon: vi kjørte sqlcmd, mottok en slags misbruk i utdatafilen, festet denne filen til revisjonsplaten. Heldigvis fungerte SQL-server på samme server som Jenkins, det ble gjort noe slikt:

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

Dermed svelger vi BCP-filen fullstendig og skyver den inn i nvarchar(max)-feltet i revisjonstabellen. Selvfølgelig falt hele dette systemet fra hverandre, for i stedet for en SQL-server fikk jeg RDS, og BULK INSERT fungerer ikke i det hele tatt via UNC på grunn av et forsøk på å ta en eksklusiv lås på en fil, og med RDS er dette generelt dømt fra selve begynnelsen. Så jeg bestemte meg for å endre systemdesignet, og lagre revisjonen linje for linje:

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

Og skriv i denne tabellen slik:

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

For å velge innhold, må du velge etter ID, velge i rekkefølge n (identitet).

I den neste artikkelen vil jeg gå mer i detalj om hvordan alt dette samhandler med Jenkins.

Kilde: www.habr.com

Legg til en kommentar