🥇ອັດຕະໂນມັດ SQL server ໃນ Jenkins: ໃຫ້ຜົນໄດ້ຮັບທີ່ສວຍງາມ

ອີກເທື່ອ ໜຶ່ງ ສືບຕໍ່ຫົວຂໍ້ຂອງການຈັດຕັ້ງ Zero Touch PROD ພາຍໃຕ້ RDS. DBAs ໃນອະນາຄົດຈະບໍ່ສາມາດເຊື່ອມຕໍ່ກັບເຊີບເວີ PROD ໂດຍກົງ, ແຕ່ຈະສາມາດໃຊ້ Jenkins ວຽກເຮັດງານທໍາສໍາລັບຊຸດປະຕິບັດງານຈໍາກັດ. DBA ເປີດຕົວວຽກແລະຫຼັງຈາກເວລາໃດຫນຶ່ງໄດ້ຮັບຈົດຫມາຍທີ່ມີບົດລາຍງານກ່ຽວກັບການສໍາເລັດການປະຕິບັດງານນີ້. ໃຫ້ເບິ່ງວິທີການນໍາສະເຫນີຜົນໄດ້ຮັບເຫຼົ່ານີ້ໃຫ້ກັບຜູ້ໃຊ້.

ຂໍ້ຄວາມທົ່ງພຽງ

ໃຫ້ເລີ່ມຕົ້ນດ້ວຍ trivial ທີ່ສຸດ. ວິທີການທໍາອິດແມ່ນງ່າຍດາຍທີ່ບໍ່ມີຫຍັງທີ່ຈະເວົ້າກ່ຽວກັບການ (ຜູ້ຂຽນຕໍ່ໄປນີ້ໃຊ້ວຽກ FreeStyle):

sqlcmd ເຮັດບາງສິ່ງບາງຢ່າງແລະພວກເຮົານໍາສະເຫນີມັນໃຫ້ຜູ້ໃຊ້. ເຫມາະສໍາລັບ, ສໍາລັບການຍົກຕົວຢ່າງ, ວຽກສໍາຮອງຂໍ້ມູນ:

ຢ່າລືມ, ໂດຍວິທີທາງການ, ພາຍໃຕ້ການສໍາຮອງຂໍ້ມູນ RDS / ການຟື້ນຟູແມ່ນ asynchronous, ດັ່ງນັ້ນທ່ານຈໍາເປັນຕ້ອງລໍຖ້າມັນ:

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

ວິທີທີສອງ, CSV

ທຸກສິ່ງທຸກຢ່າງຢູ່ທີ່ນີ້ຍັງງ່າຍດາຍຫຼາຍ:

ຢ່າງໃດກໍ່ຕາມ, ວິທີການນີ້ເຮັດວຽກພຽງແຕ່ຖ້າຂໍ້ມູນທີ່ສົ່ງຄືນໃນ CSV ແມ່ນ "ງ່າຍດາຍ". ຖ້າທ່ານພະຍາຍາມກັບຄືນ, ຕົວຢ່າງ, ບັນຊີລາຍຊື່ຂອງ TOP N CPU ແບບສອບຖາມທີ່ເຂັ້ມຂຸ້ນດ້ວຍວິທີນີ້, CSV ຈະ "corrode" ເນື່ອງຈາກຄວາມຈິງທີ່ວ່າຂໍ້ຄວາມສອບຖາມສາມາດມີຕົວອັກສອນໃດໆ - ເຄື່ອງໝາຍຈຸດ, ວົງຢືມ, ແລະແມ້ກະທັ້ງການແບ່ງແຖວ. ດັ່ງນັ້ນ, ພວກເຮົາຕ້ອງການບາງສິ່ງບາງຢ່າງທີ່ສັບສົນຫຼາຍ.

ສັນຍາລັກທີ່ສວຍງາມໃນ HTML

ຂ້ອຍຈະໃຫ້ຕົວຢ່າງລະຫັດແກ່ເຈົ້າທັນທີ

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

ໂດຍວິທີທາງການ, ເອົາໃຈໃສ່ກັບເສັ້ນທີ່ມີ System.Management.Automation.PSCustomObject, ມັນແມ່ນ magical, ຖ້າຫາກວ່າມີແທ້ຫນຶ່ງເສັ້ນໃນຕາຂ່າຍໄຟຟ້າ, ຫຼັງຈາກນັ້ນບາງບັນຫາເກີດຂຶ້ນ. ການແກ້ໄຂໄດ້ຖືກເອົາມາຈາກອິນເຕີເນັດໂດຍບໍ່ມີຄວາມເຂົ້າໃຈຫຼາຍ. ດັ່ງນັ້ນ, ທ່ານຈະໄດ້ຮັບຮູບແບບຜົນຜະລິດເຊັ່ນນີ້:

ແຕ້ມກາຟ

ຄໍາເຕືອນ: kinky code ຂ້າງລຸ່ມນີ້!
ມີການສອບຖາມຕະຫລົກຢູ່ໃນເຄື່ອງແມ່ຂ່າຍ SQL ທີ່ສະແດງ CPU ສໍາລັບ N ນາທີສຸດທ້າຍ - ປະກົດວ່າ Comrade Major ຈື່ທຸກສິ່ງທຸກຢ່າງ! ລອງແບບສອບຖາມນີ້:

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

ດຽວນີ້, ໃຊ້ການຈັດຮູບແບບນີ້ (ຕົວແປ $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>

ພວກເຮົາສາມາດປະກອບເປັນເນື້ອໃນຂອງຈົດຫມາຍ:

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

ເຊິ່ງຈະມີລັກສະນະດັ່ງນີ້:

ແມ່ນແລ້ວ, Monsieur ຮູ້ຫຼາຍກ່ຽວກັບການບິດເບືອນ! ມັນຫນ້າສົນໃຈທີ່ລະຫັດນີ້ມີ: Powershell (ຂຽນຢູ່ໃນມັນ), SQL, Xquery, HTML. ມັນເປັນການເສຍໃຈທີ່ພວກເຮົາບໍ່ສາມາດເພີ່ມ Javascript ກັບ HTML (ເພາະວ່າມັນເປັນສໍາລັບການຂຽນ), ແຕ່ການຂັດລະຫັດ Python (ເຊິ່ງສາມາດນໍາໃຊ້ໃນ SQL) ແມ່ນຫນ້າທີ່ຂອງທຸກຄົນ!

SQL profiler ຕິດຕາມຜົນໄດ້ຮັບ

ມັນເປັນທີ່ຊັດເຈນວ່າການຕິດຕາມຈະບໍ່ເຫມາະສົມກັບ CSV ເນື່ອງຈາກຊ່ອງຂໍ້ມູນ TextData. ແຕ່ການສະແດງຕາຕະລາງການຕິດຕາມໃນຈົດຫມາຍກໍ່ເປັນເລື່ອງແປກ - ທັງຂະຫນາດແລະຍ້ອນວ່າຂໍ້ມູນນີ້ມັກຈະຖືກນໍາໃຊ້ເພື່ອການວິເຄາະຕື່ມອີກ. ດັ່ງນັ້ນ, ພວກເຮົາເຮັດດັ່ງຕໍ່ໄປນີ້: ພວກເຮົາໂທຫາຜ່ານ ເອີ້ນ-SqlCmd script ທີ່ແນ່ນອນ, ໃນຄວາມເລິກຂອງສິ່ງທີ່ມັນແມ່ນເຮັດໄດ້

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

ຕໍ່ໄປ, ສຸດ ເພື່ອນ ເຄື່ອງແມ່ຂ່າຍ, ສາມາດເຂົ້າເຖິງໄດ້ໂດຍ DBA, ມີຖານຂໍ້ມູນ Traces ທີ່ມີແມ່ແບບຫວ່າງເປົ່າ, ຕາຕະລາງແບບຈຳລອງ, ພ້ອມທີ່ຈະຮັບເອົາຖັນທີ່ລະບຸໄວ້ທັງໝົດ. ພວກເຮົາຄັດລອກແບບຈຳລອງນີ້ໄປໃສ່ຕາຕະລາງໃໝ່ທີ່ມີຊື່ເປັນເອກະລັກ:

$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

ແລະຕອນນີ້ພວກເຮົາສາມາດຂຽນຮອຍຂອງພວກເຮົາເຂົ້າໄປໃນມັນໂດຍໃຊ້ Data.SqlClient.SqlBulkCopy - ຂ້າພະເຈົ້າໄດ້ຍົກຕົວຢ່າງຂ້າງເທິງນີ້ແລ້ວ. ແມ່ນແລ້ວ, ມັນຈະເປັນການດີທີ່ຈະປິດບັງຄ່າຄົງທີ່ຢູ່ໃນ TextData:

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

ພວກເຮົາແທນທີ່ຕົວເລກທີ່ຍາວກວ່າໜຶ່ງຕົວອັກສອນດ້ວຍ 999, ແລະພວກເຮົາປ່ຽນສະຕຣິງທີ່ຍາວກວ່າໜຶ່ງຕົວອັກສອນດ້ວຍ 'str'. ຕົວເລກຈາກ 0 ຫາ 9 ມັກຈະຖືກນໍາໃຊ້ເປັນທຸງ, ແລະພວກເຮົາບໍ່ໄດ້ແຕະຕ້ອງພວກມັນ, ເຊັ່ນດຽວກັນກັບສາຍຕົວອັກສອນທີ່ຫວ່າງເປົ່າແລະຕົວອັກສອນດຽວ - 'Y', 'N', ແລະອື່ນໆແມ່ນມັກຈະພົບເຫັນໃນບັນດາພວກມັນ.

ມາເພີ່ມສີສັນໃຫ້ກັບຊີວິດຂອງເຮົາ (18+ ຢ່າງເຂັ້ມງວດ)

ໃນຕາຕະລາງ, ທ່ານມັກຈະຕ້ອງການເນັ້ນໃສ່ຈຸລັງທີ່ຕ້ອງການຄວາມສົນໃຈ. ຕົວຢ່າງເຊັ່ນ FAILS, ລະດັບສູງຂອງການແຕກແຍກ, ແລະອື່ນໆ. ແນ່ນອນ, ນີ້ສາມາດເຮັດໄດ້ໃນ SQL ເປົ່າ, ສ້າງ HTML ໂດຍໃຊ້ PRINT, ແລະກໍານົດປະເພດໄຟລ໌ເປັນ HTML ໃນ 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>'

ເປັນຫຍັງຂ້ອຍຈຶ່ງຂຽນລະຫັດດັ່ງກ່າວ?

ແຕ່ມີການແກ້ໄຂທີ່ສວຍງາມກວ່າ. ແປງເປັນ HTML ບໍ່ໄດ້ປ່ອຍໃຫ້ພວກເຮົາສີຈຸລັງ, ແຕ່ພວກເຮົາສາມາດເຮັດໄດ້ຫຼັງຈາກຄວາມຈິງ. ຕົວຢ່າງ, ພວກເຮົາຕ້ອງການເລືອກເຊລທີ່ມີລະດັບການແບ່ງສ່ວນຫຼາຍກວ່າ 80 ແລະຫຼາຍກວ່າ 90. ໃຫ້ເພີ່ມຮູບແບບ:

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

ໃນການສອບຖາມຕົວມັນເອງພວກເຮົາຈະເພີ່ມຖັນ dummy ທັນທີກ່ອນ ຖັນທີ່ພວກເຮົາຕ້ອງການສີ. ຖັນຄວນຖືກເອີ້ນ SQLmarkup-ບາງສິ່ງບາງຢ່າງ:

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,

ໃນປັດຈຸບັນ, ໄດ້ຮັບ HTML ທີ່ສ້າງຂຶ້ນໂດຍ Powershell, ພວກເຮົາຈະເອົາຄໍລໍາ dummy ອອກຈາກ header, ແລະໃນຕົວຂອງຂໍ້ມູນພວກເຮົາຈະໂອນຄ່າຈາກຄໍລໍາໄປຫາຮູບແບບ. ນີ້ແມ່ນເຮັດດ້ວຍການທົດແທນພຽງແຕ່ສອງອັນ:

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

ຜົນໄດ້ຮັບ:

ມັນສະຫງ່າງາມບໍ່? ເຖິງແມ່ນວ່າບໍ່ມີ, ການໃສ່ສີນີ້ເຕືອນຂ້ອຍກ່ຽວກັບບາງສິ່ງບາງຢ່າງ

ແຫຼ່ງຂໍ້ມູນ: www.habr.com

ອັດຕະໂນມັດຂອງເຄື່ອງແມ່ຂ່າຍ SQL ໃນ Jenkins: ກັບຄືນຜົນໄດ້ຮັບທີ່ສວຍງາມ