ProHoster > blogg > administration > Arbeta med MS SQL från Powershell på Linux

Arbeta med MS SQL från Powershell på Linux

Den här artikeln är rent praktisk och tillägnad min sorgliga historia

Gör sig redo för Zero Touch PROD för RDS (MS SQL), som alla våra öron surrade om, gjorde jag en presentation (POC - Proof Of Concept) av automatisering: en uppsättning powershell-skript. Efter presentationen, när de stormiga, långvariga applåderna tystnade och förvandlades till oupphörliga applåder, sa de till mig - allt detta är bra, men bara av ideologiska skäl arbetar alla våra Jenkins-slavar på Linux!

Är detta möjligt? Ta en sådan varm, lamp DBA från Windows och håll den i själva hettan av powershell under Linux? Är inte detta grymt?

Arbeta med MS SQL från Powershell på Linux
Jag var tvungen att fördjupa mig i denna märkliga kombination av teknologier. Naturligtvis slutade alla mina 30+ skript att fungera. Till min förvåning lyckades jag fixa allt på en arbetsdag. Jag skriver i jakten. Så, vilka fallgropar kan du stöta på när du överför powershell-skript från Windows till Linux?

sqlcmd vs Invoke-SqlCmd

Låt mig påminna dig om den huvudsakliga skillnaden mellan dem. gamla goda verktyget sqlcmd Det fungerar även under Linux, med nästan identisk funktionalitet. Vi skickar frågan för att exekvera -Q, indatafilen som -i och utdata som -o. Men filnamnen görs naturligtvis skiftlägeskänsliga. Om du använder -i, skriv i slutet i filen:

GO
EXIT

Om det inte finns någon EXIT i slutet, fortsätter sqlcmd att vänta på inmatning, och om före UTGÅNG kommer inte GO, då kommer det sista kommandot inte att fungera. Utdatafilen innehåller alla utdata, markeringar, meddelanden, utskrifter etc.

Invoke-SqlCmd producerar resultatet som en DataSet, DataTables eller DataRows. Därför, om du bearbetar resultatet av ett enkelt urval, kan du använda sqlcmd, efter att ha analyserat dess utdata, är det nästan omöjligt att härleda något komplext: för detta finns det Invoke-SqlCmd. Men det här laget har också sina egna skämt:

  • Om du överför en fil till henne via -Indatafil, Sedan UTGÅNG inte behövs, dessutom producerar det ett syntaxfel
  • -Utdatafil nej, kommandot returnerar resultatet som ett objekt
  • Det finns två syntaxer för att ange en server: -ServerInstance -Användarnamn -Lösenord -Databas och igenom -ConnectionString. Konstigt nog är det i det första fallet inte möjligt att ange en annan port än 1433.
  • textutmatning, skriv PRINT, som helt enkelt "fångas" sqlcmdför Invoke-SqlCmd är ett problem
  • Och viktigast av allt: Troligtvis har din Linux inte denna cmdlet!

Och detta är huvudproblemet. Först i mars denna cmdlet blev tillgänglig för icke-Windows-plattformar, och äntligen kan vi gå vidare!

Variabel substitution

sqlcmd har variabel substitution med -v, till exempel så här:

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

I SQL-skriptet använder vi ersättningar:

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

Så här är det. I *nix variabla substitutioner fungerar inte. Parameter -v ignoreras. U Invoke-SqlCmd ignoreras -Variabler. Även om parametern som anger själva variablerna ignoreras, fungerar själva substitutionerna – du kan använda alla variabler från Shell. Men jag blev förolämpad av variablerna och bestämde mig för att inte vara beroende av dem alls, och agerade oförskämt och primitivt, eftersom SQL-skripten är korta:

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

Detta är, som du förstår, ett test redan från Unix-versionen.

Laddar upp filer

I Windows-versionen åtföljdes varje operation av en granskning: vi körde sqlcmd, fick någon form av missbruk i utdatafilen, bifogade den här filen till granskningsplattan. Lyckligtvis fungerade SQL-servern på samma server som Jenkins, det gjordes ungefär så här:

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

Således sväljer vi BCP-filen helt och hållet och skjuter in den i nvarchar(max)-fältet i granskningstabellen. Hela det här systemet föll förstås isär, för istället för en SQL-server fick jag RDS, och BULK INSERT fungerar inte alls via UNC på grund av ett försök att ta ett exklusivt lås på en fil, och med RDS är detta i allmänhet dömt från själva början. Så jag bestämde mig för att ändra systemdesignen och lagra revisionen rad för rad:

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

Och skriv i den här tabellen så här:

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

För att välja innehåll måste du välja efter ID, välja i ordning n (identitet).

I nästa artikel kommer jag att gå in mer i detalj om hur allt detta interagerar med Jenkins.

Källa: will.com

Lägg en kommentar