🥇Automation of SQL server in Jenkins: exitum pulchre reddens

rursus perdurantes materiam dispositionis Nulla Tactus PROD sub RDS. Future DBAs coniungere cum PROD servientibus directe non poterit, sed uti poterit Imago jobs certum ordinem operationum. DBA officium movet et post aliquod tempus epistulam accipit cum relatione huius operationis completae. Inspice vias ut hos eventus usori exhibeant.

Clarae verbae

Cum minimis sit amet mauris. Prima methodus tam simplex est ut de re nihil loquatur (auctor in sequentibus jobs FreeStyle adhibet);

sqlcmd facit aliquid et exhibemus illud usori. Specimen pro, verbi gratia, jobs tergum:

Noli oblivisci obiter, quod sub RDS tergum/restituendi asynchronum est, sic expectare debes:

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

Secundus modus, CSV

Omnia hic etiam simplicissima sunt;

Sed haec methodus solum operatur si notitia in CSV reddita est "simplex". Si redire conaris, exempli gratia, intensiva quaestionis index TOP N CPU hoc modo, CSV "corrode" erit ex eo quod textus query continere potest quaslibet personas - commata, virgulas, atque etiam lineas frangit. Ergo eget aliquid magis complicatum.

Pulchra signa in HTML

Praecisum codicem tibi dabo ilicet

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

Viam attende ad lineam cum System.Management.Automation.PSCustomObject, magicum est: si una prorsus linea est in malesuada euismod, aliquae difficultates ortae sunt. Solutio in Interreti sine multa intellegentia sublata est. Quam ob rem sic aliquid formatos habebis:

drawing graphs

Monitum: codicem kinky infra!
Ridiculum quaesitum est de servo SQL quod CPU ostendit pro ultimis N minutis - evenit ut comes Maior omnia meminerit! Hoc proba quiz:

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

Nunc hac forma utens ($Fragment variabilis)

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

Corpus epistolae formare possumus;

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

Quod sic erit:

Ita, Monsieur multum scit de perversionibus! Interestingly est hunc codicem continere: Powershell (in eo scriptum), SQL, Xquery, HTML. Misericordia est quod Javascript ad HTML addere non possumus (ex quo scripto est), sed codicem Python polire (quod in SQL adhiberi potest) omnium officium est!

SQL profiler vestigium output

Patet vestigium in CSV non congruere propter campum TextData. Sed vestigium euismod in litteris exhibens mirum est - tum propter magnitudinem tum quia haec notitia saepe pro ulteriori analysi adhibetur. ergo haec facimus: vocamus via invocare-SqlCmd quoddam scriptum, in cuius profunditate factum est

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

Deinde, on alium In calculonis servi pervia DBA, vestigium datorum cum inani template, exemplar laminae, paratas ad omnes certas columnas recipiendas esse. Exemplar hoc ad novam mensam cum unico nomine effingimus:

$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

Et nunc scribere possumus vestigium nostrum in eo utendo Data.SqlClient.SqlBulkCopy — Exemplum huius supra dedi. Ita, etiam esset nice personas constantes in TextData:

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

Numeros plus quam unam characterem longam cum 999 restituimus, et chordas longiores quam characterem unum cum "str" substituimus. Numeri ab 0 ad 9 saepe usi sunt ut vexilla, eosque non attingimus, ac chordarum indolem inanes et simplices - 'Y', 'N' etc. saepe apud eos inveniuntur.

Addamus aliquem colorem ad vitam nostram (stricte 18+)

In tabulis, saepe cellas illustrare vis quae attentionem requirunt. For example, Deficio, egregie, ruptionis, etc. Scilicet, hoc fieri potest in nudo SQL, HTML utens PRINT generans et tabellae genus ad HTML in Jenkins collocans;

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

Cur talia scripsi codice?

Sed pulchrior est solutio. ConvertTo-HTML non cellas coloremus, sed hoc facto possumus. Exempli gratia, cellulas eligere volumus cum gradu ruptionis plus quam 80 et plus quam 90. stylos adde:

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

In ipsa interrogatione columnam phantasticam apponemus statim ante columnae volumus colorem. Columpna vocari debet SQLmarkup-aliquid;

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,

Nunc, acceptis HTML ab Powershell genitis, columnam phantasticam a capite removebimus, et in corpore notitiae valorem ab columna ad stylum transferemus. Hoc fit duobus iustis substitutionibus;

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

effectus:

Estne elegans? Etsi non, hic color me commemorat aliquid

Source: www.habr.com

Automation of SQL server in Jenkins: reverti eventum pulchre