🥇 ʻO ke kikowaena o SQL server ma Jenkins: hoʻihoʻi maikaʻi i ka hopena

Eia hou e hoomau ana i ke kumuhana o ka hoonohonoho ana Zero Touch PROD ma lalo o RDS. ʻAʻole hiki i nā DBA e hiki mai ana ke hoʻopili pololei i nā kikowaena PROD, akā hiki ke hoʻohana ʻO Jenkins nā hana no kahi hoʻonohonoho palena o nā hana. Hoʻomaka ka DBA i ka hana a ma hope o kekahi manawa loaʻa kahi leka me kahi hōʻike e pili ana i ka pau ʻana o kēia hana. E nānā kākou i nā ala e hōʻike ai i kēia mau hopena i ka mea hoʻohana.

Kupu Plain

E hoʻomaka kākou me ka mea liʻiliʻi loa. He mea maʻalahi loa ke ala mua ʻaʻohe mea e kamaʻilio e pili ana (ua hoʻohana ka mea kākau i nā hana FreeStyle):

sqlcmd hana i kekahi mea a hōʻike mākou i ka mea hoʻohana. Kūpono no, no ka laʻana, nā hana hoʻihoʻi:

Mai poina, ma ke ala, aia ma lalo o RDS backup / restore he asynchronous, no laila pono ʻoe e kali no ia:

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

ʻO ke ala ʻelua, CSV

Maʻalahi loa nā mea a pau ma aneʻi:

Eia naʻe, hana wale kēia ʻano inā "maʻalahi" ka ʻikepili i hoʻihoʻi ʻia i ka CSV. Inā ho'āʻo ʻoe e hoʻi, no ka laʻana, kahi papa inoa o nā nīnau koʻikoʻi TOP N CPU ma kēia ʻano, e "corrode" ka CSV ma muli o ka hiki ke loaʻa i ka kikokikona nīnau kekahi mau huaʻōlelo - nā koma, nā ʻōlelo a me nā laina laina. No laila, pono mākou i kahi mea paʻakikī.

Nā hōʻailona nani ma HTML

E hāʻawi koke wau iā ʻoe i kahi snippet code

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

Ma ke ala, e hoʻolohe i ka laina me System.Management.Automation.PSCustomObject, he mea kupanaha, inā aia hoʻokahi laina i loko o ka pahu, a laila ua kū mai kekahi mau pilikia. Ua lawe ʻia ka hoʻonā mai ka Pūnaewele me ka ʻike ʻole. ʻO ka hopena, e loaʻa iā ʻoe ka hoʻohālikelike ʻana i kahi mea e like me kēia:

Ke kaha kiʻi kiʻi

'Ōlelo Aʻo: kinky code ma lalo!
Aia kekahi nīnau ʻakaʻaka ma ka server SQL e hōʻike ana i ka CPU no nā minuke N hope loa - ʻike ʻia ua hoʻomanaʻo ʻo Comrade Major i nā mea āpau! E ho'āʻo i kēia nīnau nīnau:

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

I kēia manawa, e hoʻohana ana i kēia hōpili ($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>

Hiki iā mākou ke hana i ke kino o ka leka:

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

ʻO ka mea e like me kēia:

ʻAe, ua ʻike nui ʻo Monsieur e pili ana i nā hoʻololi! He mea hoihoi ia i loko o kēia code: Powershell (kākau ʻia i loko), SQL, Xquery, HTML. He mea minamina ʻaʻole hiki iā mākou ke hoʻohui i ka Javascript i HTML (no ka mea no ke kākau ʻana), akā ʻo ka polishing Python code (hiki ke hoʻohana ʻia ma SQL) ʻo ia ke kuleana o kēlā me kēia kanaka!

Hōʻike ʻia nā huaʻōlelo SQL profiler

Ua maopopo ʻaʻole e komo ka trace i ka CSV ma muli o ke kahua TextData. Akā ʻo ka hōʻike ʻana i kahi mākaʻikaʻi i loko o kahi leka he mea ʻē hoʻi - no ka nui a no ka hoʻohana pinepine ʻia ʻana o kēia ʻikepili no ka nānā hou ʻana. No laila, hana mākou penei: kelepona mākou ma kahea-SqlCmd kekahi palapala, i ka hohonu o ka hana ana

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

A laila, ma hoa Ma ke kikowaena hiki ke loaʻa e ka DBA, aia kahi waihona Traces me kahi maʻamau ʻole, ka pā Model, mākaukau e ʻae i nā kolamu āpau i kuhikuhi ʻia. Hoʻopili mākou i kēia kumu hoʻohālike i kahi papaʻaina hou me kahi inoa kūʻokoʻa:

$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

A i kēia manawa hiki iā mākou ke kākau i kā mākou trace i loko me ka hoʻohana ʻana Data.SqlClient.SqlBulkCopy - Ua hāʻawi mua wau i kahi laʻana o kēia ma luna. ʻAe, maikaʻi nō hoʻi e uhi i nā mea mau ma TextData:

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

Hoʻololi mākou i nā helu ʻoi aku ma mua o hoʻokahi huaʻōlelo me 999, a hoʻololi mākou i nā kaula lōʻihi ma mua o hoʻokahi huaʻōlelo me ka 'str'. Hoʻohana pinepine ʻia nā helu mai ka 0 a hiki i ka 9 ma ke ʻano he hae, ʻaʻole mākou e hoʻopā iā lākou, a me nā kaula ʻokoʻa a me hoʻokahi ʻano - 'Y', 'N', etc.

E hoʻohui i kekahi kala i ko mākou ola (ʻoi aku ka 18+)

I nā papa, makemake pinepine ʻoe e hōʻike i nā cell e pono ai ka nānā. No ka laʻana, FAILS, kiʻekiʻe o ka ʻāpana, etc. ʻOiaʻiʻo, hiki ke hana i kēia ma ka SQL wale, e hana ana i HTML me PRINT, a me ka hoʻonohonoho ʻana i ke ʻano faila i HTML ma 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>'

No ke aha wau i kākau ai i kēlā code?

Akā, aia kahi ʻoi aku ka nani. Hoʻololi i-HTML ʻAʻole ʻae iā mākou e kala i nā cell, akā hiki iā mākou ke hana ma hope o ka ʻoiaʻiʻo. No ka laʻana, makemake mākou e koho i nā cell me kahi pae ʻāpana ʻoi aku ma mua o 80 a ʻoi aku ma mua o 90. E hoʻohui i nā ʻano:

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

Ma ka nīnau pono'ī mākou e hoʻohui i kahi kolamu dummy ma mua koke kolamu makemake mākou e kala. Pono e kapa ʻia ke kolamu SQLmarkup-kekahi mea:

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,

I kēia manawa, i ka loaʻa ʻana o ka HTML i hana ʻia e Powershell, e wehe mākou i ke kolamu dummy mai ke poʻo, a ma ke kino o ka ʻikepili e hoʻololi mākou i ka waiwai mai ke kolamu i ke ʻano. Hana ʻia kēia me ʻelua mau hoʻololi:

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

Hualoaʻa:

ʻAʻole anei he nani? ʻOiai ʻaʻole, hoʻomanaʻo mai kēia kala iaʻu i kekahi mea

Source: www.habr.com

ʻO ke kikowaena o SQL server ma Jenkins: hoʻihoʻi maikaʻi i ka hopena