A-rithist . Cha bhith e comasach dha DBAn san àm ri teachd ceangal gu dìreach ri frithealaichean PROD, ach bidh e comasach dhaibh a chleachdadh Jenkins obraichean airson seata cuibhrichte de ghnìomhachd. Bidh an DBA a’ cur air bhog obair agus às deidh beagan ùine gheibh e litir le aithisg mu chrìochnachadh na h-obrach seo. Bheir sinn sùil air dòighean air na toraidhean sin a thaisbeanadh don neach-cleachdaidh.
Teacs soilleir
Feuch an tòisich sinn leis an fheadhainn as miosa. Tha a’ chiad dòigh cho sìmplidh is nach eil dad ann airson bruidhinn mu dheidhinn (bidh an t-ùghdar an-seo a’ cleachdadh obraichean FreeStyle):
sqlcmd a’ dèanamh rudeigin agus bidh sinn ga thaisbeanadh don neach-cleachdaidh. Fìor mhath airson, mar eisimpleir, obraichean cùl-taic:
Na dì-chuimhnich, leis an t-slighe, gu bheil cùl-taic / ath-nuadhachadh RDS asyncronach, agus mar sin feumaidh tu feitheamh air a shon:
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 againAn dàrna dòigh, CSV
Tha a h-uile dad an seo cuideachd gu math sìmplidh:
Ach, chan obraich an dòigh seo ach ma tha an dàta a chaidh a thilleadh san CSV “sìmplidh”. Ma dh’ fheuchas tu ri, mar eisimpleir, liosta de cheistean dian TOP N CPU a thilleadh san dòigh seo, bidh an CSV “a’ corrachadh” leis gum faod caractaran sam bith a bhith ann an teacsa na ceiste - cromagan, luachan, agus eadhon briseadh loidhne. Mar sin, feumaidh sinn rudeigin nas iom-fhillte.
Soidhnichean brèagha ann an HTML
Bheir mi criomag còd dhut sa bhad
$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
}Air an t-slighe, thoir aire don loidhne le System.Management.Automation.PSCustomObject, tha e draoidheil; ma tha dìreach aon loidhne anns a’ ghriod, dh’ èirich cuid de dhuilgheadasan. Chaidh am fuasgladh a thoirt bhon eadar-lìon gun mòran tuigse. Mar thoradh air an sin, gheibh thu toradh cruth rudeigin mar seo:
A' tarraing grafaichean
Rabhadh: còd kinky gu h-ìosal!
Tha ceist èibhinn air an fhrithealaiche SQL a sheallas an CPU airson na N mionaidean mu dheireadh - tha e a 'tionndadh a-mach gu bheil Comrade Major a' cuimhneachadh air a h-uile càil! Feuch an ceisteachan seo:
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);A-nis, a’ cleachdadh an cruth seo (caochladair $ 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>Faodaidh sinn corp na litreach a chruthachadh:
$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
}Cò a bhios coltach ri seo:
Tha, tha mòran eòlach air Monsieur mu bhuaireadh! Tha e inntinneach gu bheil: Powershell (sgrìobhte ann), SQL, Xquery, HTML. Tha e duilich nach urrainn dhuinn Javascript a chur ri HTML (leis gu bheil e airson sgrìobhadh), ach tha e mar dhleastanas air a h-uile duine còd Python (a ghabhas cleachdadh ann an SQL) a ghleusadh!
Toradh lorg profiler SQL
Tha e soilleir nach tig an lorg a-steach don CSV air sgàth an raon TextData. Ach tha e neònach a bhith a’ taisbeanadh cliath lorg ann an litir - an dà chuid air sgàth meud agus leis gu bheil an dàta seo gu tric air a chleachdadh airson tuilleadh sgrùdaidh. Mar sin, bidh sinn a 'dèanamh na leanas: tha sinn a' gairm via cuir a-steach-SqlCmd sgriobtar àraidh, anns an doimhne a ta e deanta
select
SPID,EventClass,TextData,
Duration,Reads,Writes,CPU,
StartTime,EndTime,DatabaseName,HostName,
ApplicationName,LoginName
from ::fn_trace_gettable ( @filename , default ) Air adhart, air adhart caraid Air an fhrithealaiche a tha ruigsinneach leis an DBA, tha stòr-dàta Traces le teamplaid falamh, an truinnsear Model, deiseil airson gabhail ris na colbhan ainmichte gu lèir. Bidh sinn a’ dèanamh lethbhreac den mhodail seo gu clàr ùr le ainm sònraichte:
$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 Agus a-nis is urrainn dhuinn ar lorg a sgrìobhadh a-steach le bhith a’ cleachdadh Data.SqlClient.SqlBulkCopy - Tha mi air eisimpleir de seo a thoirt seachad gu h-àrd mu thràth. Tha, bhiodh e math cuideachd a bhith a’ falach stumpaichean ann an TextData:
# mask data
foreach ($Row in $Result)
{
$v = $Row["TextData"]
$v = $v -replace "'([^']{2,})'", "'str'" -replace "[0-9][0-9]+", '999'
$Row["TextData"] = $v
}
Cuiridh sinn 999 an àite àireamhan nas fhaide na aon charactar a dh’fhaid, agus cuiridh sinn ‘str’ an àite teudan nas fhaide na aon charactar. Bidh àireamhan bho 0 gu 9 gu tric air an cleachdadh mar bhrataichean, agus cha bhith sinn a’ suathadh riutha, a bharrachd air sreangan falamh agus aon-charactar - ‘Y’, ‘N’, msaa gu tric rim faighinn nam measg.
Nach cuir sinn beagan dath ri ar beatha (dìreach 18+)
Ann an clàran, bidh thu gu tric airson ceallan a tha feumach air aire a shoilleireachadh. Mar eisimpleir, FAILS, ìre àrd de bhriseadh, msaa. Gu dearbh, faodar seo a dhèanamh ann an SQL lom, a 'cruthachadh HTML a' cleachdadh PRINT, agus a 'suidheachadh an seòrsa faidhle gu HTML ann an 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>'
Carson a sgrìobh mi a leithid de chòd?
Ach tha fuasgladh nas bòidhche ann. Tionndadh gu HTML na leig leinn na ceallan a dhath, ach is urrainn dhuinn a dhèanamh às deidh an fhìrinn. Mar eisimpleir, tha sinn airson ceallan a thaghadh le ìre sgaraidh nas motha na 80 agus nas motha na 90. Nach cuir sinn stoidhlichean ris:
<style>
.SQLmarkup-red { color: red; background-color: yellow; }
.SQLmarkup-yellow { color: black; background-color: #FFFFE0; }
.SQLmarkup-default { color: black; background-color: white; }
</style>Anns a’ cheist fhèin cuiridh sinn colbh meallta dìreach roimhe colbh a tha sinn airson a dhath. Bu chòir an colbh a ghairm SQLmarkup-rudeigin:
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, A-nis, às deidh dhuinn an HTML a ghineadh Powershell fhaighinn, bheir sinn air falbh an colbh meallta bhon cheann-cinn, agus ann am bodhaig an dàta gluaisidh sinn an luach bhon cholbh chun stoidhle. Tha seo air a dhèanamh le dìreach dà neach-ionaid:
$html = $html `
-replace "<th>SQLmarkup[^<]*</th>", "" `
-replace "<td>SQLmarkup-(.+?)</td><td>",'<td class="SQLmarkup-$1">'
Toradh:
Nach eil e eireachdail? Ged nach eil, tha an dath seo a’ cur rudeigin nam chuimhne
Source: www.habr.com