Automation of SQL server di Jenkins: balik hasilna beautifully

Deui nuluykeun téma susunan Nol Toél PROD handapeun RDS. DBA kahareup moal bisa nyambung ka server PROD langsung, tapi bakal bisa ngagunakeun Jenkins jobs pikeun set kawates operasi. DBA ngaluncurkeun padamelan sareng saatos sababaraha waktos nampi surat kalayan laporan ngeunaan parantosan operasi ieu. Hayu urang tingali cara nampilkeun hasil ieu ka pangguna.

Téks Polos

Hayu urang mimitian ku nu paling trivial. Metodeu anu kahiji saderhana pisan sahingga teu aya anu kedah diobrolkeun (panulis saterasna nganggo padamelan FreeStyle):

sqlcmd ngalakukeun hal sarta kami nampilkeun ka pamaké. Cocog pikeun, contona, padamelan cadangan:

Tong hilap, ku jalan kitu, yén dina cadangan / pamulihan RDS henteu sinkron, janten anjeun kedah ngantosan:

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} substituted with db name by powershell
select @stat=lifecycle,@info=taskinfo from @rds where id=@xid
if @stat not in ('ERROR','SUCCESS','CANCELLED') goto again

Métode kadua, CSV

Sadayana di dieu ogé saderhana pisan:

Nanging, metode ieu ngan ukur tiasa dianggo upami data anu dipulangkeun dina CSV "saderhana". Upami anjeun nyobian uih deui, contona, daptar patarosan intensif CPU TOP N ku cara ieu, CSV bakal "corrode" kusabab kanyataan yén téks pamundut tiasa ngandung karakter naon waé - koma, tanda petik, bahkan putus garis. Ku alatan éta, urang kudu hal nu leuwih pajeulit.

Tanda éndah dina HTML

Kuring bakal masihan anjeun snippet kode langsung

$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>My table</h2>"
  $Result | ConvertTo-HTML -Title "Rows" -Head $header -body $body `
    | Out-File "res.log" -Append -Encoding UTF8
  } else {
    "<h3>No data</h3>" | Out-File "res.log" -Append -Encoding UTF8
  }

Ku jalan kitu, nengetan garis kalawan System.Management.Automation.PSCustomObject, éta gaib, lamun aya persis hiji garis dina grid nu, sababaraha masalah timbul. Solusina dicandak tina Internét tanpa seueur pamahaman. Hasilna, anjeun bakal nampi kaluaran formatna sapertos kieu:

Ngagambar grafik

Perhatosan: kode kinky handap!
Aya query lucu dina server SQL nu mintonkeun CPU pikeun panungtungan N menit - tétéla yén Comrade Mayor apal sagalana! Coba kuis ieu:

DECLARE @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'%<SystemHealth>%') AS x) AS y 
ORDER BY 1 DESC OPTION (RECOMPILE);

Ayeuna, nganggo pormat ieu ($ variabel 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>

Urang tiasa ngabentuk awak surat:

$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
    }

Anu bakal katingali sapertos kieu:

Leres, Monsieur terang pisan ngeunaan perversions! Éta metot nu kode ieu ngandung: Powershell (ditulis di dinya), SQL, Xquery, HTML. Ieu karunya nu urang teu bisa nambahkeun Javascript ka HTML (saprak éta pikeun nulis), tapi polishing kode Python (anu bisa dipaké dina SQL) nyaeta tugas dulur urang!

SQL profiler renik kaluaran

Ieu jelas yén renik moal cocog kana CSV kusabab widang TextData. Tapi mintonkeun grid renik dina surat oge aneh - duanana kusabab ukuranana sarta alatan data ieu mindeng dipaké pikeun analisis salajengna. Kituna, urang ngalakukeun di handap: urang nelepon via nelepon-SqlCmd naskah tangtu, dina jero nu eta geus rengse

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

Salajengna, on sobat Dina server diaksés ku DBA, aya database ngambah kalawan citakan kosong, plat Modél, siap nampa sagala kolom dieusian. Urang nyalin modél ieu ka méja anyar kalawan ngaran unik:

$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 

Sareng ayeuna urang tiasa nyerat jejak urang kana éta nganggo Data.SqlClient.SqlBulkCopy - Kuring parantos masihan conto ieu di luhur. Leres, éta ogé langkung saé pikeun topéng konstanta dina TextData:

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

Urang ngaganti angka leuwih ti hiji karakter panjang kalayan 999, sarta kami ngaganti string leuwih panjang batan hiji karakter jeung 'str'. Angka ti 0 nepi ka 9 mindeng dipaké salaku bandéra, sarta kami henteu noél aranjeunna, kitu ogé string kosong tur single-karakter - 'Y', 'N', jsb mindeng kapanggih diantara aranjeunna.

Hayu urang tambahkeun sababaraha warna kana kahirupan urang (patos 18+)

Dina tabél, anjeun sering hoyong nyorot sél anu peryogi perhatian. Contona, FAILS, tingkat fragméntasi tinggi, jsb. Tangtosna, ieu tiasa dilakukeun dina SQL bulistir, ngahasilkeun HTML nganggo PRINT, sareng netepkeun jinis file kana HTML di Jenkins:

declare @body varchar(max), @chunk varchar(max)
set @body='<font face="Lucida Console" size="3">'
set @body=@body+'<b>Server name: '+@@servername+'</b><br>'
set @body=@body+'<br><br>'
set @body=@body+'<table><tr><th>Job</th><th>Last Run</th><th>Avg Duration, sec</th><th>Last Run, Sec</th><th>Last 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<>'Succeeded' then '<font color="red">' else '' end+
      LastStatus+
      case when LastStatus<>'Succeeded' 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>'

Naha kuring nyerat kode sapertos kitu?

Tapi aya solusi anu langkung saé. ConvertTo-HTML henteu ngantep urang ngawarnaan sél, tapi urang tiasa ngalakukeun éta saatos kanyataan. Salaku conto, urang hoyong milih sél anu tingkat fragméntasi langkung ti 80 sareng langkung ti 90. Hayu urang tambahkeun gaya:

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

Dina pamundut sorangan urang bakal nambahan kolom dummy geuwat sateuacan kolom kami hoyong warna. kolom kudu disebut SQLmarkup-kitu:

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, 

Ayeuna, sanggeus narima HTML dihasilkeun ku Powershell, urang bakal miceun kolom dummy tina lulugu, sarta dina awak data urang mindahkeun nilai tina kolom kana gaya. Hal ieu dilakukeun ngan ukur dua substitusi:

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

Hasil:


Éta henteu elegan? Sanajan henteu, ngawarnaan ieu reminds kuring ngeunaan hiji hal


sumber: www.habr.com