🥇Дженкинстегі SQL серверін автоматтандыру: нәтижені әдемі қайтару

Қайтадан аранжировка тақырыбын жалғастыру Zero Touch PROD RDS астында. Болашақ DBA-лар PROD серверлеріне тікелей қосыла алмайды, бірақ пайдалана алады Дженкинс операциялардың шектеулі жиынтығына арналған тапсырмалар. DBA жұмысты бастайды және біраз уақыттан кейін осы операцияның аяқталғаны туралы есеппен хат алады. Осы нәтижелерді пайдаланушыға ұсыну жолдарын қарастырайық.

Қарапайым мәтін

Ең тривиальдыдан бастайық. Бірінші әдіс өте қарапайым, бұл туралы айтудың қажеті жоқ (автор бұдан әрі FreeStyle тапсырмаларын пайдаланады):

sqlcmd бірдеңе жасайды және біз оны пайдаланушыға ұсынамыз. Мысалы, сақтық көшірме тапсырмалары үшін өте қолайлы:

Айтпақшы, RDS сақтық көшірмесін жасау/қалпына келтіру асинхронды екенін ұмытпаңыз, сондықтан оны күту керек:

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 сұрау мәтінінде кез келген таңбалар - үтірлер, тырнақшалар және тіпті жол үзілімдері болуы мүмкін болғандықтан, «тот басады». Сондықтан бізге күрделірек нәрсе керек.

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 жолына назар аударыңыз, бұл сиқырлы, егер торда дәл бір жол болса, онда кейбір мәселелер туындады. Шешім көп түсінбестен интернеттен алынды. Нәтижесінде сіз келесідей пішімделген шығысты аласыз:

Графиктерді салу

Ескерту: төменде жасырын код!
SQL серверінде процессорды соңғы N минутта көрсететін күлкілі сұрау бар - жолдас майор бәрін есте сақтайды екен! Бұл викторинаны қолданып көріңіз:

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
    }

Бұл келесідей болады:

Иә, мырза бұзақылық туралы көп біледі! Бір қызығы, бұл кодта мыналар бар: Powershell (онда жазылған), SQL, Xquery, HTML. Біз Javascript-ті HTML-ге қоса алмайтынымыз өкінішті (өйткені ол жазу үшін), бірақ Python кодын жылтырату (SQL-де қолдануға болады) әркімнің міндеті!

SQL профиль жасаушы бақылау шығысы

TextData өрісіне байланысты із CSV-ге сәйкес келмейтіні анық. Бірақ әріптегі жол торын көрсету де таңқаларлық - өлшеміне байланысты және бұл деректер көбінесе әрі қарай талдау үшін пайдаланылады. Сондықтан біз келесі әрекеттерді орындаймыз: арқылы қоңырау шаламыз invoke-SqlCmd белгілі бір сценарий, оның тереңдігінде ол жасалады

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, фрагментацияның жоғары деңгейі және т.б. Әрине, мұны PRINT көмегімен HTML жасау және Дженкинсте HTML файл түрін орнату, жалаң SQL тілінде жасауға болады:

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>

Сұраудың өзінде біз жалған баған қосамыз бірден бұрын біз бояғымыз келетін баған. Бағанды шақыру керек 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,

Енді Powershell жасаған HTML-ді алғаннан кейін біз тақырыптан жалған бағанды алып тастаймыз, ал деректер мәтінінде мәнді бағаннан стильге тасымалдаймыз. Бұл тек екі ауыстыру арқылы жасалады:

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

нәтижесі:

Бұл талғампаз емес пе? Жоқ дегенмен, бұл бояу маған бір нәрсені еске түсіреді

Ақпарат көзі: www.habr.com

Дженкинстегі SQL серверін автоматтандыру: нәтижені әдемі қайтару