🥇Jenkins-en SQL zerbitzariaren automatizazioa: emaitza ederki itzuliz

berriro antolaketaren gaiari jarraipena emanez Zero Touch PROD RDS pean. Etorkizuneko DBA-ek ezingo dute PROD zerbitzarietara zuzenean konektatu, baina erabili ahal izango dute Jenkins eragiketa multzo mugatu baterako lanpostuak. DBAk lana abiarazten du eta denbora pixka bat igaro ondoren, eragiketa hau amaitzeari buruzko txosten batekin gutun bat jasotzen du. Ikus ditzagun emaitza hauek erabiltzaileari aurkezteko moduak.

Testu arrunta

Has gaitezen hutsalenetik. Lehen metodoa hain da sinplea, ezen ez dagoela ezer buruz hitz egiteko (aurrerantzean egileak FreeStyle lanak erabiltzen ditu):

sqlcmd zerbait egiten du eta erabiltzaileari aurkezten diogu. Aproposa, adibidez, babeskopia-lanetarako:

Ez ahaztu, bide batez, RDS babeskopia/berreskurapena asinkronoa dela, beraz, itxaron behar duzu:

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

Bigarren metodoa, CSV

Hemen ere dena oso erraza da:

Hala ere, metodo honek CSVn itzultzen diren datuak "sinpleak" badira soilik funtzionatzen du. Modu honetan, adibidez, TOP N CPU intentsiboko kontsulta-zerrenda bat itzultzen saiatzen bazara, CSV-a "korrondu" egingo da, kontsultaren testuak edozein karaktere izan ditzakeelako: komak, komatxoak eta baita lerro-jauziak ere. Horregatik, zerbait konplikatuagoa behar dugu.

Errotulu ederrak HTMLn

Kode zati bat emango dizut berehala

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

Bide batez, arreta System.Management.Automation.PSCustomObject-ekin lerroari, magikoa da; sarean lerro bat zehatz badago, arazo batzuk sortu ziren. Irtenbidea Internetetik hartu zuten asko ulertu gabe. Ondorioz, irteera formatua izango duzu honelako zerbait:

Grafikoak marraztea

Abisua: kode kinky behean!
SQL zerbitzarian azken N minutuetan CPUa bistaratzen duen kontsulta dibertigarri bat dago - Burkide Nagusiak dena gogoratzen duela ematen du! Probatu galdetegi hau:

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

Orain, formatu hau erabiliz ($Fragment aldagaia)

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

Gutunaren gorputza osatu dezakegu:

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

Honela izango dena:

Bai, Monsieurek asko daki perbertsioei buruz! Interesgarria da kode honek edukitzea: Powershell (bertan idatzia), SQL, Xquery, HTML. Pena da HTMLra Javascript-a gehitu ezin izana (idazteko baita), baina Python kodea leuntzea (SQLn erabil daitekeena) guztion betebeharra da!

SQL profiler arrastoaren irteera

Argi dago arrastoa ez dela CSVn sartuko TextData eremua dela eta. Baina aztarna-sare bat gutun batean bistaratzea ere bitxia da, bai tamainagatik, bai datu hauek azterketa gehiago egiteko erabiltzen direlako. Horregatik, honako hau egiten dugu: bidez deitzen dugu invoke-SqlCmd gidoi jakin bat, zeinen sakontasunean egiten den

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

Hurrengoa, on Lagun DBAk eskura dezakeen zerbitzarian, Traces datu-base bat dago txantiloi huts batekin, Modelo plaka, zehaztutako zutabe guztiak onartzeko prest. Eredu hau izen bakarra duen taula berri batera kopiatzen dugu:

$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

Eta orain gure aztarna idatzi ahal izango dugu bertan erabiliz Data.SqlClient.SqlBulkCopy - Dagoeneko eman dut horren adibide bat goian. Bai, ondo legoke TextData-n konstanteak ezkutatzea ere:

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

Karaktere bat baino gehiagoko zenbakiak 999rekin ordezkatzen ditugu eta karaktere bat baino luzeagoak diren kateak 'str'-rekin ordezkatzen ditugu. 0tik 9ra arteko zenbakiak bandera gisa erabiltzen dira sarri, eta ez ditugu ukitzen, baita karaktere bakarreko kateak ere -'Y', 'N', etab. aurkitu ohi dira haien artean.

Eman diezaiogun kolore pixka bat gure bizitzari (zorrozki 18 urtetik gorakoak)

Tauletan, askotan arreta eskatzen duten gelaxkak nabarmendu nahi dituzu. Adibidez, FAILS, zatiketa maila handia, etab. Jakina, hau SQL hutsean egin daiteke, PRINT erabiliz HTML sortuz eta fitxategi mota HTML moduan ezarriz Jenkins-en:

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

Zergatik idatzi nuen halako kodea?

Baina irtenbide ederrago bat dago. BihurtuTo-HTML ez digu uzten zelulak koloreztatzen, baina egin dezakegu ondoren. Adibidez, 80 eta 90 baino gehiagoko zatiketa-maila duten gelaxkak hautatu nahi ditugu. Gehi ditzagun estiloak:

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

Kontsultan bertan zutabe finko bat gehituko dugu berehala lehenago koloreztatu nahi dugun zutabea. Zutabeari deitu behar zaio SQL markaketa-zerbait:

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,

Orain, Powershell-ek sortutako HTMLa jasota, goiburuko zutabe fikzioa kenduko dugu, eta datuen gorputzean zutabetik estilora transferituko dugu balioa. Hau bi ordezkapenekin egiten da:

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

Emaitza:

Ez al da dotorea? Ezetz arren, kolore honek zerbait gogorarazten dit

Iturria: www.habr.com

Jenkinsen SQL zerbitzariaren automatizazioa: emaitza ederki itzuliz