Yes, it's a SQL Performance Monitoring in Real-Time, developed in Power BI, Json, C#, T-SQL, RestAPI and CLR.
It has been building to show up the health performance environments:
Web Version:
Through the Power BI Apps, we can access the indicators online for SQL monitoring.
Mobile Version:
This is the full development:
I developed these steps:
- Built a Database in each machine to collect the CPU percentage usage
- Built a CLR in C# to collect the CPU usage and saved into a SQL table
- Built a data source in Power BI for Streaming Real-Time Database
- Built an ETL in C# to Push the data from SQL to Power BI in Cloud
- Built a dashboard to show up the indicators
DATABASE PERMISSIONS:
I built an operational database called DBA:
To make CLR able to run in DBA database:
EXEC sp_changedbowner 'sa'
GO
ALTER DATABASE DBA SET trustworthy ON
GO
sp_configure 'clr enabled'
GO
sp_configure 'clr enabled', 1
GO
RECONFIGURE
GO
sp_configure 'clr enabled'
GO
MY CLR C# Code - Server Performance Indicator - Collector:
References:
File: SqlFunction.cs
===============================================================
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;
using System.Diagnostics;
using System.Linq;
public partial class UserDefinedFunctions
{
[Microsoft.SqlServer.Server.SqlFunction]
public static SqlString fnDBAWEBCPU()
{
var processorCategory = PerformanceCounterCategory.GetCategories()
.FirstOrDefault(cat => cat.CategoryName == "Processor");
var countersInCategory = processorCategory.GetCounters("_Total");
//First Reading
string strResult = null;
var strResultFloat = countersInCategory.First(cnt => cnt.CounterName == "% Processor Time").NextValue();
System.Threading.Thread.Sleep(1000);
// Second Reading
strResultFloat = countersInCategory.First(cnt => cnt.CounterName == "% Processor Time").NextValue();
//SqlContext.Pipe.Send(strResult.ToString());
strResult = strResultFloat.ToString();
return new SqlString(strResult);
}
}
Create a repository:
==================================================================
USE [DBA]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[ServerPerformance](
[ServerPerformanceID] [int] IDENTITY(1,1) NOT NULL,
[ServerName] [varchar](100) NOT NULL,
[PerformanceIndicatorCategoryName] [varchar](100) NOT NULL,
[PerformanceIndicatorName] [varchar](100) NOT NULL,
[PerformanceAVGIndicatorValue] [float] NULL,
[PerformanceIndicatorValue] [float] NOT NULL,
[CreatedDateOn] [datetime] NOT NULL,
[IsOperational] [bit] NULL,
CONSTRAINT [ServerPerformance_pk] PRIMARY KEY CLUSTERED
(
[ServerPerformanceID] DESC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 100) ON [PRIMARY]
) ON [PRIMARY]
GO
SQL Code to save the CLR to SQL:
==================================================================
Declare @PerformanceIndicatorCategoryName Varchar(100),
@PerformanceIndicatorName Varchar(100),
@ServerName Varchar(100),
@IsOperational bit
Set @PerformanceIndicatorCategoryName = 'Processor'
Set @PerformanceIndicatorName = 'Processor Time/Total'
Set @ServerName = @@SERVERNAME
Set @IsOperational = (Case When (datepart(hour, getdate()) Between 8 and 18) Then 1 Else 0 End);
With AVG5MinuteValue
As
(
SELECT
PerformanceAVGIndicatorValue = AVG(PerformanceIndicatorValue)
FROM DBO.ServerPerformance A
WHERE ServerPerformanceID >= (IDENT_CURRENT('DBO.ServerPerformance') - 35)
)
Insert DBO.ServerPerformance (ServerName, PerformanceIndicatorCategoryName, PerformanceIndicatorName, PerformanceAVGIndicatorValue, PerformanceIndicatorValue, CreatedDateOn, IsOperational)
Select @ServerName, @PerformanceIndicatorCategoryName, @PerformanceIndicatorName, PerformanceAVGIndicatorValue, dbo.fnDBAWEBCPU(), GetDate() , @IsOperational from AVG5MinuteValue
MY ETL in C# Code - Push the Data from SQL to Power BI in Cloud using Jason and RestApi:
Reference:
File: DBAWeb.cs
============================================================================
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.Diagnostics;
using System.Linq;
using System.ServiceProcess;
using System.Text;
using System.Threading.Tasks;
using System.Timers;
namespace DBAWebPowerBILoading
{
public partial class DBAWeb : ServiceBase
{
Timer aTimer = new System.Timers.Timer(10000);
public DBAWeb()
{
InitializeComponent();
}
protected override void OnStart(string[] args)
{
// Hook up the Elapsed event for the timer.
aTimer.Elapsed += new ElapsedEventHandler(OnTimedEvent);
aTimer.Interval = int.Parse(ConfigurationManager.AppSettings["IntervalTime"]);
aTimer.Start();
}
private void OnTimedEvent(object source, ElapsedEventArgs e)
{
SendDataToBI("Server=TG-SVR-SQL4;Database=DBA;Trusted_Connection=yes;", "https://api.powerbi.com/beta/6a38d3ca-e45b-49d7-8a3d-680a588096ac/datasets/2d946377-f378-49e1-a5ba-bdf8feb3a4cd/"); // -- (You need to fill it up with your Power BI streaming dataset (check it on the next steps)
}
private void SendDataToBI(string connection,string url)
{
string sql = "Select JSonResult = (Select ServerName, CategoryIndicatorName, MinimalValue, MaximumValue, TargetValue, PerformanceIndicatorValue, PerformanceAVGIndicatorValueCG1, PerformanceAVGIndicatorValueCG2, PerformanceAVGIndicatorValueSQL4, CreatedDateOn from DBAWEB.POW.vwServerIndicatorPowerBI for json path)";
using (SqlConnection cnnCG = new SqlConnection(connection))
{
cnnCG.Open();
using (SqlCommand cmdCG = new SqlCommand(sql, cnnCG))
{
using (SqlDataReader drCG = cmdCG.ExecuteReader())
{
while (drCG.Read())
{
new PowerBILoading().PowerBIPushDataAsyncAsync(url, drCG["JSonResult"].ToString());
}
}
}
}
}
protected override void OnStop()
{
aTimer?.Stop();
}
}
}
Build the project, create a folder and load the Windows Services:
Use InstallUtil to load the services:
C:\DBAWeb\Services>c:\Windows\Microsoft.NET\Framework64\v4.0.30319\installutil.exe DBAWebPowerBILoading.exe
POWER BI STREAMING DATASET - REAL-TIME: