🥇Adaṣiṣẹ SQL olupin ni Jenkins: awọn abajade ti o npadabọ ni ẹwa

Lẹẹkansi tẹsiwaju awọn akori ti akanṣe Odo Fọwọkan PROD labẹ RDS. Awọn DBA iwaju kii yoo ni anfani lati sopọ si awọn olupin PROD taara, ṣugbọn yoo ni anfani lati lo Jenkins ise fun a fi opin si ṣeto ti mosi. DBA ṣe ifilọlẹ iṣẹ ati lẹhin igba diẹ gba lẹta kan pẹlu ijabọ lori ipari iṣẹ yii. Jẹ ki a wo awọn ọna lati ṣafihan awọn abajade wọnyi si olumulo.

Text Text

Jẹ ká bẹrẹ pẹlu awọn julọ bintin. Ọna akọkọ jẹ rọrun pupọ pe ko si nkankan lati sọrọ nipa (onkọwe lẹhinna lo awọn iṣẹ FreeStyle):

sqlcmd ṣe nkan kan ati pe a ṣafihan rẹ si olumulo. Apẹrẹ fun, fun apẹẹrẹ, awọn iṣẹ afẹyinti:

Maṣe gbagbe, nipasẹ ọna, pe labẹ afẹyinti RDS / mu pada jẹ asynchronous, nitorina o nilo lati duro fun rẹ:

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

Ọna keji, CSV

Ohun gbogbo nibi tun rọrun pupọ:

Sibẹsibẹ, ọna yii n ṣiṣẹ nikan ti data ti o pada sinu CSV jẹ "rọrun". Ti o ba gbiyanju lati pada, fun apẹẹrẹ, atokọ ti TOP N CPU awọn ibeere aladanla ni ọna yii, CSV yoo “bajẹ” nitori otitọ pe ọrọ ibeere le ni awọn ohun kikọ eyikeyi ninu - aami idẹsẹ, awọn agbasọ, ati paapaa awọn fifọ laini. Nitorina, a nilo nkankan diẹ idiju.

Lẹwa ami ni HTML

Emi yoo fun ọ ni snippet koodu kan lẹsẹkẹsẹ

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

Nipa ọna, san ifojusi si laini pẹlu System.Management.Automation.PSCustomObject, o jẹ idan ti o ba wa ni pato kan ila ni akoj, lẹhinna diẹ ninu awọn iṣoro dide. A mu ojutu naa lati Intanẹẹti laisi oye pupọ. Bi abajade, iwọ yoo gba ọna kika nkan bi eleyi:

Awọn aworan iyaworan

Ikilọ: koodu kinky ni isalẹ!
Ibeere alarinrin kan wa lori olupin SQL ti o ṣafihan Sipiyu fun awọn iṣẹju N to kẹhin - o wa ni pe Comrade Major ranti ohun gbogbo! Gbiyanju ibeere yii:

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

Bayi, ni lilo ọna kika yii ($ oniyipada 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>

A le ṣe ara ti lẹta naa:

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

Eyi ti yoo dabi eyi:

Bẹẹni, Monsieur mọ pupọ nipa awọn ipadasẹhin! O yanilenu pe koodu yii ni: Powershell (ti a kọ sinu rẹ), SQL, Xquery, HTML. O jẹ aanu pe a ko le ṣafikun Javascript si HTML (niwon o jẹ fun kikọ), ṣugbọn didan koodu Python (eyiti o le ṣee lo ni SQL) jẹ iṣẹ gbogbo eniyan!

SQL profiler itopase

O han gbangba pe itọpa naa kii yoo baamu si CSV nitori aaye TextData. Ṣugbọn iṣafihan akoj itọpa ninu lẹta kan tun jẹ ajeji - mejeeji nitori iwọn ati nitori data yii nigbagbogbo lo fun itupalẹ siwaju. Nitorina, a ṣe awọn wọnyi: a pe nipasẹ pè-SqlCmd kan awọn akosile, ninu awọn ogbun ti o ti wa ni ṣe

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

Nigbamii ti, lori ore olupin, доступном DBA, существует база Traces с пустой заготовкой, табличкой Model, готовой принять все указанные колонки. Мы эту модель копируем в новую таблицу с уникальным именем:

$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

Ati nisisiyi a le kọ wa kakiri sinu rẹ nipa lilo Data.SqlClient.SqlBulkCopy - Mo ti tẹlẹ fun apẹẹrẹ ti eyi loke. Bẹẹni, yoo tun dara lati boju boju-boju ni TextData:

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

A ropo awọn nọmba siwaju ju ọkan kikọ gun pẹlu 999, ati awọn ti a ropo awọn gbolohun ọrọ gun ju ọkan kikọ pẹlu 'str'. Awọn nọmba lati 0 si 9 ni a maa n lo bi awọn asia, ati pe a ko fi ọwọ kan wọn, bakannaa awọn gbolohun ọrọ ti o ṣofo ati ẹyọkan - 'Y', 'N', ati bẹbẹ lọ ni a maa n rii laarin wọn.

Jẹ ki a ṣafikun awọ diẹ si awọn igbesi aye wa (muna 18+)

Ni awọn tabili, o nigbagbogbo fẹ lati saami awọn sẹẹli ti o nilo akiyesi. Fun apẹẹrẹ, FAILS, ipele giga ti pipin, ati bẹbẹ lọ. Nitoribẹẹ, eyi le ṣee ṣe ni SQL igboro, ṣiṣẹda HTML nipa lilo PRINT, ati ṣeto iru faili si HTML ni 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>'

Kini idi ti MO kọ iru koodu bẹẹ?

Ṣugbọn ojutu ti o lẹwa diẹ sii wa. IyipadaTo-HTML ko jẹ ki a ṣe awọ awọn sẹẹli, ṣugbọn a le ṣe lẹhin otitọ. Fun apẹẹrẹ, a fẹ yan awọn sẹẹli pẹlu ipele pipin ti o ju 80 ati diẹ sii ju 90. Jẹ ki a ṣafikun awọn aza:

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

Ninu ibeere tikararẹ a yoo ṣafikun ọwọn idin lẹsẹkẹsẹ ṣaaju ki o to iwe a fẹ lati awọ. Awọn ọwọn yẹ ki o pe SQLmarkup-nkankan:

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,

Ni bayi, ti o ti gba HTML ti ipilẹṣẹ nipasẹ Powershell, a yoo yọ iwe idin kuro lati akọsori, ati ninu ara ti data a yoo gbe iye naa lati ọwọn si ara. Eyi ni a ṣe pẹlu awọn aropo meji nikan:

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

Esi:

Ṣe ko o yangan? Botilẹjẹpe rara, awọ yii leti mi nkankan

orisun: www.habr.com

Adaṣiṣẹ ti olupin SQL ni Jenkins: da esi pada ni ẹwa