🥇Automatisierung von SQL Server in Jenkins: Ergebnisse ansprechend zurückgeben

Wieder Das Thema der Gestaltung fortführend Zero Touch PROD unter RDS. Zukünftige DBA werden nicht in der Lage sein, sich direkt mit PROD-Servern zu verbinden, sondern können Jenkins Jobs für eine eingeschränkte Anzahl von Operationen verwenden. Der DBA startet einen Job und erhält nach einiger Zeit eine E-Mail mit einem Bericht über die Durchführung dieser Operation. Lassen Sie uns die Möglichkeiten zur Präsentation dieser Ergebnisse für den Benutzer betrachten.

Reiner Text

Beginnen wir mit dem banalsten. Die erste Methode ist so einfach, dass man eigentlich nichts dazu sagen sollte (der Autor verwendet hier und im Folgenden FreeStyle-Jobs):

sqlcmd etwas ausführt, und wir präsentieren es dem Benutzer. Ideal geeignet zum Beispiel für Backup-Jobs:

Vergessen Sie übrigens nicht, dass das Backup/Restore unter RDS asynchron ist, daher sollten wir darauf warten:

declare @rds table
  (id int, task_type varchar(128), database_name sysname, pct int, duration int, 
   lifecycle varchar(128), taskinfo varchar(max) null, 
   upd datetime, cre datetime,
   s3 varchar(256), ovr int, KMS varchar(256) null)
waitfor delay '00:00:20' 
insert into @rds exec msdb.dbo.rds_task_status @db_name='{db}'
select @xid=max(id) from @rds

again:
waitfor delay '00:00:02'
delete from @rds
insert into @rds exec msdb.dbo.rds_task_status @db_name='{db}' 
# {db} wird durch den Datenbanknamen in PowerShell ersetzt
select @stat=lifecycle,@info=taskinfo from @rds where id=@xid
if @stat not in ('ERROR','SUCCESS','CANCELLED') goto again

Die zweite Methode, CSV

Hier ist alles auch sehr einfach:

Diese Methode funktioniert jedoch nur, wenn die Daten, die im CSV zurückgegeben werden, „einfach“ sind. Wenn Sie versuchen, auf diese Weise eine Liste der TOP N CPU-intensiven Abfragen zurückzugeben, wird das CSV „zerfallen“, da der Abfragetext beliebige Zeichen enthalten kann — Kommas, Anführungszeichen und sogar Zeilenumbrüche. Daher benötigen wir etwas Komplexeres.

Schöne Tabellen in HTML

Ich werde gleich einen Codeausschnitt angeben

$Header = @"
<style>
TABLE {border-width: 1px; border-style: solid; border-color: black; border-collapse: collapse;}
TH {border-width: 1px; padding: 3px; border-style: solid; border-color: black; background-color: #6495ED;}
TD {border-width: 1px; padding: 3px; border-style: solid; border-color: black;}
</style>
"@
  
$Result = invoke-Sqlcmd -ConnectionString $jstr -Query "select * from DbInv" `
  | Select-Object -Property * -ExcludeProperty "ItemArray", "RowError", "RowState", "Table", "HasErrors"
if ($Result -eq $null) { $cnt = 0; }
elseif ($Result.getType().FullName -eq "System.Management.Automation.PSCustomObject") { $cnt = 1; }
else { $cnt = $Result.Rows.Count; } 
if ($cnt -gt 0) {
  $body = "<h2>Meine Tabelle</h2>"
  $Result | ConvertTo-HTML -Title "Zeilen" -Head $header -body $body `
    | Out-File "res.log" -Append -Encoding UTF8
  } else {
    "<h3>Keine Daten</h3>" | Out-File "res.log" -Append -Encoding UTF8
  }

Übrigens, achten Sie auf die Zeile mit System.Management.Automation.PSCustomObject, sie ist magisch, wenn im Grid genau eine Zeile vorhanden ist, gab es Probleme. Die Lösung wurde aus dem Internet übernommen, ohne genauer darauf einzugehen. Infolgedessen erhalten Sie eine Ausgabe, die etwa so aussieht:

Wir zeichnen Grafiken

Achtung: abgedrehter Code unten!
Es gibt eine interessante Abfrage auf SQL-Server, die die CPU der letzten N Minuten ausgibt — es stellt sich heraus, dass der Kamerad Major alles speichert! Probieren Sie diese Abfrage aus:

DEKLARIEREN @ts_now bigint = (SELECT cpu_ticks/(cpu_ticks/ms_ticks) 
  FROM sys.dm_os_sys_info WITH (NOLOCK)); 
SELECT TOP(256) 
  DATEADD(ms, -1 * (@ts_now - [timestamp]), GETDATE()) AS [EventTime],
  SQLProcessUtilization AS [SQLCPU], 
  100 - SystemIdle - SQLProcessUtilization AS [OtherCPU]
FROM (SELECT record.value('(.\/Record\/@id)[1]', 'int') AS record_id, 
  record.value('(.\/Record\/SchedulerMonitorEvent\/SystemHealth\/SystemIdle)[1]', 'int') 
    AS [SystemIdle], 
  record.value('(.\/Record\/SchedulerMonitorEvent\/SystemHealth\/ProcessUtilization)[1]', 'int') 
    AS [SQLProcessUtilization], [timestamp] 
  FROM (SELECT [timestamp], CONVERT(xml, record) AS [record] 
  FROM sys.dm_os_ring_buffers WITH (NOLOCK)
  WHERE ring_buffer_type = N'RING_BUFFER_SCHEDULER_MONITOR' 
    AND record LIKE N'%%' ) AS x) AS y 
ORDER BY 1 DESC OPTION (RECOMPILE);

Jetzt, unter Verwendung solcher Formatierung (Variable $Fragment)

<table style="width: 100%"><tbody><tr style="background-color: white; height: 2pt;">
  <td style="width: SQLCPU%; background-color: green;"></td>
  <td style="width: OtherCPU%; background-color: blue;"></td>
  <td style="width: REST%; background-color: #C0C0C0;"></td></tr></tbody>
</table>

Wir können den Inhalt der E-Mail erstellen:

$Result = invoke-Sqlcmd -ConnectionString $connstr -Query $Query `
  | Select-Object -Property * -ExcludeProperty `
  "ItemArray", "RowError", "RowState", "Table", "HasErrors"
if ($Result.HasRows) {
  foreach($item in $Result) 
    { 
    $time = $itemEventTime 
    $sqlcpu = $item.SQLCPU
    $other = $itemOtherCPU
    $rest = 100 - $sqlcpu - $other
    $f = $fragment -replace "SQLCPU", $sqlcpu
    $f = $f -replace "OtherCPU", $other
    $f = $f -replace "REST", $rest
    $f | Out-File "res.log" -Append -Encoding UTF8
    }

Das wird so aussehen:

Ja, monsieur kennt sich mit Abstrusität aus! Interessant ist, dass dieser Code beinhaltet: PowerShell (darauf basiert er), SQL, Xquery, HTML. Schade, dass wir zu HTML kein Javascript hinzufügen können (da dies für die E-Mail ist), aber den Python-Code (der in SQL genutzt werden kann) zu verfeinern, ist die Pflicht eines jeden!

Ausgabe des SQL Profiler-Traces

Es ist klar, dass der Trace nicht in CSV aufgrund des Feldes TextData passt. Aber den Trace als Gitter in der E-Mail auszugeben, ist auch seltsam – sowohl wegen der Größe als auch, weil diese Daten oft für weitere Analysen verwendet werden. Daher machen wir Folgendes: Wir rufen über invoke-SqlCmd ein gewisses Skript auf, in dessen Inneren folgendes gemacht wird:

select 
  SPID,EventClass,TextData,
  Duration,Reads,Writes,CPU,
  StartTime,EndTime,DatabaseName,HostName,
  ApplicationName,LoginName
   from ::fn_trace_gettable ( @filename , default )

Weiter, auf Freund beraten Server, verfügbar für DBA, gibt es eine Datenbank Traces mit einer leeren Vorlage, einer Tabelle Model, die bereit ist, alle angegebenen Spalten aufzunehmen. Dieses Modell kopieren wir in eine neue Tabelle mit einem einzigartigen Namen:

$dt = Get-Date -format "yyyyMMdd"
$tm = Get-Date -format "hhmmss"
$tableName = $srv + "_" + $dt + "_" + $tm
$copytab = "select * into " + $tableName + " from Model"
invoke-SqlCmd -ConnectionString $tstr -Query $copytab

Und jetzt können wir unseren Trace mit Hilfe von Data.SqlClient.SqlBulkCopy — dieses Beispiel habe ich bereits oben angeführt. Ja, es wäre auch gut, die Konstanten in TextData zu maskieren:

# mask data
foreach ($Row in $Result)
{ 
  $v = $Row["TextData"]
  $v = $v -replace "'([^']{2,})'", "'str'" -replace "[0-9][0-9]+", '999'
  $Row["TextData"] = $v
}

Wir ersetzen Zahlen mit mehr als einem Zeichen durch 999, und Strings mit mehr als einem Zeichen ersetzen wir durch 'str'. Zahlen von 0 bis 9 werden häufig als Flags verwendet und bleiben unberührt, ebenso wie leere und einstellige Strings – darunter finden sich oft 'Y', 'N' etc.

Lasst uns Farbe in unser Leben bringen (nur für 18+)

In Tabellen möchte man oft Zellen hervorheben, die besondere Aufmerksamkeit erfordern. Zum Beispiel FAILS, ein hoher Fragmentierungsgrad usw. Natürlich kann man dies auch mit reinem SQL erreichen, indem man HTML mit PRINT formt und in Jenkins den Dateityp HTML setzt:

declare @body varchar(max), @chunk varchar(max)
set @body='<font face="Lucida Console" size="3">'
set @body=@body+'<b>Servername: '+@@servername+'</b><br>'
set @body=@body+'<br><br>'
set @body=@body+'<table><tr><th>Job</th><th>Letzte Ausführung</th><th>Durchschnittliche Dauer, Sek.</th><th>Letzte Ausführung, Sek.</th><th>Letzter Status</th></tr>'
print @body

DECLARE tab CURSOR FOR SELECT '<tr><td>'+name+'</td><td>'+
  LastRun+'</td><td>'+
  convert(varchar,AvgDuration)+'</td><td>'+
  convert(varchar,LastDuration)+'</td><td>'+
    case when LastStatus<>'Erfolgreich' then '<font color="red">' else '' end+
      LastStatus+
      case when LastStatus<>'Erfolgreich' then '</font>' else '' end+
     +'</td><td>'
  from #j2
OPEN tab;  
FETCH NEXT FROM tab into @chunk
WHILE @@FETCH_STATUS = 0  
BEGIN
  print @chunk
  FETCH NEXT FROM tab into @chunk;  
END  
CLOSE tab;  
DEALLOCATE tab;
print '</table>'

Warum habe ich diesen Code geschrieben?

Aber es gibt eine schönere Lösung. ConvertTo-HTML gibt uns nicht die Möglichkeit, Zellen zu färben, aber wir können dies nachträglich tun. Zum Beispiel möchten wir Zellen mit einem Fragmentierungsgrad von über 80 und 90 hervorheben. Fügen wir Stile hinzu:

.SQLmarkup-red { color: red; background-color: yellow; }
.SQLmarkup-yellow { color: black; background-color: #FFFFE0; }
.SQLmarkup-default { color: black; background-color: white; }

In der Abfrage fügen wir eine fiktive Spalte hinzu direkt vor der Spalte, die wir färben möchten. Die Spalte sollte heißen SQLmarkup-irgendetwas:

case  
  when ps.avg_fragmentation_in_percent>=90.0 then 'SQLmarkup-red'
  when ps.avg_fragmentation_in_percent>=80.0 then 'SQLmarkup-yellow'
  else 'SQLmarkup-default' 
  end as [SQLmarkup-1], 
ps.avg_fragmentation_in_percent,

Jetzt, da wir das von Powershell erstellte HTML haben, entfernen wir die fiktive Spalte aus dem Header und übertragen den Wert aus der Spalte in den Stil im Datenkörper. Dies geschieht mit nur zwei Ersetzungen:

$html = $html `
  -ersetzen "<th>SQLmarkup[^<]*</th>", "" `
  -ersetzen "<td>SQLmarkup-(.+?)</td><td>",'<td class="SQLmarkup-$1">'

Ergebnis:

Ist es nicht elegant? Auch wenn ich das Gefühl habe, dass diese Farbkodierung mich an etwas erinnert

Quelle: habr.com

Automatisierung von SQL-Server in Jenkins: Ergebnisse ansprechend zurückgeben