Automation of SQL server in Jenkins: we return the result beautifully

Again continuing the theme of arranging Zero Touch PROD under RDS . Future DBAs will not be able to connect to the PROD servers directly, but will be able to use Jenkins jobs for a limited set of operations. DBA starts job and after a while receives the letter with the report on performance of this operation. Let's look at how to present these results to the user.

Plain text

Let's start with the most trivial. The first way is so simple that there’s nothing to talk about (the author uses FreeStyle jobs hereinafter):


')
sqlcmd does something, and we will present it to the user. Ideal for, for example, backup jobs:



Do not forget, by the way, that under RDS backup / restore asnhronen, so we should wait for it:

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 

Second way CSV

Here, everything is also very simple:



However, this method only works if the data returned in CSV is “simple”. If you try to return, for example, the list of TOP N CPU intensive queries, then the CSV will “break up” because the query text may contain any characters - commas, quotes, and even line breaks. Therefore, we need something more complicated.

Beautiful signs on HTML

I'll give you a snippet right away.

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

By the way, pay attention to the line with System.Management.Automation.PSCustomObject, it is magic, if there is exactly one line in the grid, then there were some problems. The decision is taken from the Internet without really understanding. As a result, you get a conclusion that looks like this:

Draw graphics

Warning: perverted code below!

There is a funny query to the SQL server, which displays the CPU for the last N minutes - it turns out, Comrade Major remembers everything! Try this round:

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

Now, using this formatting ($ 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> 

We can form the body of the letter:

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

Which will look like this:



So yes, monsieur knows a lot about perversions! Interestingly, this code contains: Powershell (written on it), SQL, Xquery, HTML. It is a pity that we cannot add Javascript to HTML (as it is for writing), but polishing Python code (which can be used in SQL) is everyone’s duty!

SQL profiler trace output

It is clear that the trace "does not climb" in CSV because of the TextData field. But it’s also strange to display the trace in the letter, both because of its size and because this data is often used for further analysis. Therefore, we do the following: call through invoke-SqlCmd a certain script, in the depths of which is done

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

Then, on another server accessible by DBA, there is a Traces database with an empty stub, a Model plate, ready to accept all the specified columns. We copy this model into a new table with a unique name:

 $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 

And now we can write our trace into it using Data.SqlClient.SqlBulkCopy - I already gave an example of this above. Yes, it would be nice to make masking constants 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 } 

We replace numbers with more than one character that is 999 long, and we replace strings longer than one character with 'str'. Numbers from 0 to 9 are often used as flags, and we do not touch them, as well as empty and single-character strings - among them there are often 'Y', 'N', etc.

Let's add colors to our life (strictly 18+)

In the plates often want to highlight the cells that require attention. For example, FAILS, high fragmentation, and so on. Of course, this can be done on bare SQL, generating HTML using PRINT, and putting the HTML file type in 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>' 

Why did I write such code?



But there is a more beautiful solution. ConvertTo-HTML does not allow us to color the cells, but we can do it after the fact. For example, we want to select cells with a level of fragmentation of more than 80 and more than 90. Add styles:

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

We'll add a dummy column directly to the column we want to color. The column should be called SQLmarkup- something:

 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, 

Now, when we get the HTML created by Powershell, we will remove the dummy column from the header, and in the data body we will transfer the value from the column to the style. This is done with just two substitutions:

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

Result:



Isn't it elegant? No, this coloring reminds me