🥇Kudzichitira nokha kwa seva ya SQL ku Jenkins: kubweretsa zotsatira zake mokongola

Ndiponso kupitiriza mutu wa makonzedwe Zero Touch PROD pansi pa RDS. Ma DBA amtsogolo sangathe kulumikizana ndi ma seva a PROD mwachindunji, koma azitha kugwiritsa ntchito Jenkins ntchito zamagulu ochepa a ntchito. DBA imayambitsa ntchito ndipo patapita nthawi imalandira kalata yokhala ndi lipoti lakutha kwa ntchitoyi. Tiyeni tiwone njira zoperekera zotsatira izi kwa wogwiritsa ntchito.

Malembo Oyera

Tiyeni tiyambe ndi zazing'ono kwambiri. Njira yoyamba ndiyosavuta kotero kuti palibe chonena (wolemba pambuyo pake amagwiritsa ntchito ntchito za FreeStyle):

sqlcmd amachita china chake ndipo timachiwonetsa kwa wogwiritsa ntchito. Zoyenera, mwachitsanzo, ntchito zosunga zobwezeretsera:

Musaiwale, mwa njira, kuti pansi pa zosunga zobwezeretsera za RDS ndizovuta, chifukwa chake muyenera kudikirira:

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

Njira yachiwiri, CSV

Chilichonse apa ndi chophweka kwambiri:

Komabe, njirayi imagwira ntchito ngati zomwe zabwezedwa mu CSV ndi "zosavuta". Ngati muyesa kubwereranso, mwachitsanzo, mndandanda wa mafunso ozama a TOP N CPU motere, CSV "idzawononga" chifukwa chakuti malembawo akhoza kukhala ndi zilembo zilizonse - koma, ndemanga, ngakhale kusweka kwa mizere. Choncho, tikufunika chinachake chovuta kwambiri.

Zizindikiro zabwino mu HTML

Ndikupatsani kachidutswa ka code nthawi yomweyo

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

Mwa njira, tcherani khutu pamzerewu ndi System.Management.Automation.PSCustomObject, ndi zamatsenga; ngati pali mzere umodzi ndendende mu gululi, ndiye kuti mavuto ena adawuka. Yankho linatengedwa kuchokera pa intaneti popanda kumvetsa zambiri. Zotsatira zake, mupeza linanena bungwe formatted motere:

Kujambula ma graph

Chenjezo: kinky code pansipa!
Pali funso loseketsa pa seva ya SQL yomwe imawonetsa CPU kwa mphindi zomaliza za N - zikuwoneka kuti Comrade Major amakumbukira chilichonse! Yesani mafunso awa:

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);

Tsopano, pogwiritsa ntchito masanjidwe awa ($ Fragment variable)

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

Titha kupanga thupi la kalatayo:

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

Zomwe zidzawoneka motere:

Inde, Monsieur amadziwa zambiri za zosokoneza! Ndizosangalatsa kuti code iyi ili ndi: Powershell (yolembedwa mmenemo), SQL, Xquery, HTML. Ndizomvetsa chisoni kuti sitingathe kuwonjezera Javascript ku HTML (popeza ndizolemba), koma kupukuta code ya Python (yomwe ingagwiritsidwe ntchito mu SQL) ndi ntchito ya aliyense!

SQL profiler trace output

Zikuwonekeratu kuti kufufuzako sikungagwirizane ndi CSV chifukwa cha gawo la TextData. Koma kuwonetsa gululi m'kalata ndikodabwitsanso - chifukwa cha kukula kwake komanso chifukwa datayi imagwiritsidwa ntchito nthawi zambiri kuwunikiranso. Chifukwa chake, timachita izi: timayimbira kudzera kuitanitsa-SqlCmd script inayake, mkati mwake momwe imachitikira

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

Kenako, pa bwenzi Pa seva yofikiridwa ndi DBA, pali database ya Traces yokhala ndi template yopanda kanthu, mbale ya Model, yokonzeka kuvomereza mizati yonse. Timakopera chitsanzochi patebulo latsopano lomwe lili ndi dzina lapadera:

$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

Ndipo tsopano tikhoza kulemba tsatanetsatane wathu pogwiritsa ntchito Data.SqlClient.SqlBulkCopy - Ndapereka kale chitsanzo cha izi pamwambapa. Inde, zingakhalenso zabwino kubisa zokhazikika mu TextData:

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

Timasintha manambala opitilira chilembo chimodzi ndi 999, ndipo timasintha zingwe zazitali kuposa chilembo chimodzi ndi 'str'. Manambala kuyambira 0 mpaka 9 amagwiritsidwa ntchito ngati mbendera, ndipo sitimawakhudza, komanso zingwe zopanda kanthu komanso zamtundu umodzi - 'Y', 'N', ndi zina zambiri.

Tiyeni tiwonjezeko mtundu wina m'miyoyo yathu (zambiri 18+)

M'matebulo, nthawi zambiri mumafuna kuwunikira ma cell omwe amafunikira chidwi. Mwachitsanzo, FAILS, kugawanika kwakukulu, etc. Zachidziwikire, izi zitha kuchitika mu SQL yopanda kanthu, kupanga HTML pogwiritsa ntchito PRINT, ndikuyika mtundu wa fayilo ku HTML ku 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>'

N’chifukwa chiyani ndinalemba zimenezi?

Koma pali njira yokongola kwambiri. ConvertTo-HTML sichimatilola kuti tizikongoletsa ma cell, koma titha kuchita izi zitachitika. Mwachitsanzo, tikufuna kusankha ma cell omwe ali ndi mulingo wogawikana wopitilira 80 ndi kupitilira 90. Tiyeni tiwonjeze masitayelo:

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

Mufunso lokha tidzawonjezera dummy column nthawi yomweyo pamaso ndime tikufuna kupaka utoto. Mzere uyenera kutchedwa SQLmarkup-kanthu:

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,

Tsopano, titalandira HTML yopangidwa ndi Powershell, tidzachotsa gawo la dummy pamutu, ndipo mu thupi la deta tidzasamutsa mtengo kuchokera pamndandanda kupita ku kalembedwe. Izi zimachitika ndi zolowa ziwiri zokha:

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

Zotsatira:

Kodi si zokongola? Ngakhale ayi, kupaka utoto uku kumandikumbutsa zina

Source: www.habr.com

Kukonzekera kwa seva ya SQL ku Jenkins: kubwezera zotsatira zake bwino