A database dashboard is a graphical user interface (GUI) that summarizes the key performance indicators (KPIs) for a SQL Server database. It presents the data in a visually appealing format, such as charts, graphs, tables, and other types of visualizations, to help users easily monitor and understand the status of their database.
In this article, I will show how we can create a database dashboard using SQL Server Reporting Services. The database dashboard contains the following details:
- Server / Host Name: Hostname / Server name on which the SQL Server is installed.
- Operating system: The operating system of the server on which the SQL Server is installed.
- SQL Server version: Version and edition of SQL Server.
- Authentication type: Authentication type used to access SQL Server instance.
- Clustering setup: Whether the SQL Server instance is clustered or stand-alone.
- Database: Total databases created in SQL Server instance.
- Total SQL Jobs: Count of SQL Server agent jobs.
- Failed SQL Jobs: Count of failed SQL Jobs in the last 7 days.
For demonstration, I have created two virtual machines, named SQLWindows and SQLUbuntu. I have installed Windows Server 2019 and SQL Server 2019 developer edition on SQLWindows. I have installed Ubuntu 20.0 and SQL Server 2019 on Linux on SQLUbuntu. I have created a separate SQL Server instance on the local machine named Nisarg-PC, which connects to both SQL Server instances using Linked Server. I have created a database named SQLDashboard, storing the details of both SQL Server instances.
The stored procedure to populate data
We will create a stored procedure named sp_get_server_details on SQLWindows and SQLUbuntu database servers. We will use a linked server to execute stored procedures on remote servers. The output of both stored procedures will be stored on a table named tbl_server_detail. We will create another stored procedure that stores the data populated from the linked server named sp_get_all_server_detail. You can read about the linked server here. We can query the sys.server_details system catalog view to populate the list of linked servers. You can execute the query using SQL Server Management studio or dbForge Studio for SQL Server.
USE master GO SELECT name 'Server Name' , product 'Product Name' ,data_source 'Data Source Name',s.modify_date 'Modified Date', CASE WHEN is_linked =1 THEN 'Lineked Sever' WHEN is_linked=0 THEN 'Local Server' END 'Server Type' FROM sys.servers s
The Code of the sp_get_server_detail stored procedure. This procedure will be created on SQLWindows and SQLUbuntu.
USE DBATools Go IF EXISTS (SELECT name FROM dbatools.sys.procedures WHERE name='sp_get_server_detail') DROP PROCEDURE sp_get_server_detail Go create procedure sp_get_server_detail as begin SELECT Convert(varchar,SERVERPROPERTY('ServerName')) AS ServerName ,Convert(varchar,isnull(SERVERPROPERTY('InstanceName'), 'Default')) AS [SQLServer InstanceName] ,Convert(varchar,SUBSTRING(@@VERSION,0,CHARINDEX('(',@@VERSION)-1)) as [SQLServer Version] ,Convert(varchar,SERVERPROPERTY('EDITION')) AS [SQLServer Edition] ,Convert(varchar,SERVERPROPERTY('InstanceDefaultDataPath')) AS [Default DataPath] ,Convert(varchar,SERVERPROPERTY('InstanceDefaultLogPath')) AS [DEFAULT LogPath] ,Convert(varchar,iif(SERVERPROPERTY('IsIntegratedSecurityOnly') = 0, 'Windows and SQL Server Authentication', 'Windows Authentication')) AS [Authentication Type] ,cpu_count AS [Total Processor], round(physical_memory_kb / 1024.0 / 1024.0, 2) AS [Physical Memory_GB] ,sqlserver_start_time AS [LastStartTime], (select count(name) from sys.databases where database_id>4) as 'Count of Databases', (select count(name) from msdb..sysjobs where enabled=1) as 'Count of Jobs', (SELECT count(1) FROM msdb.dbo.sysjobhistory h INNER JOIN msdb.dbo.sysjobs j ON h.job_id = j.job_id INNER JOIN msdb.dbo.sysjobsteps s ON j.job_id = s.job_id AND h.step_id = s.step_id AND h.run_date > CONVERT(int , CONVERT(varchar(10), DATEADD(DAY, -7, GETDATE()), 112)) and run_status = 0) 'Count of failed SQL Job' FROM sys.dm_os_sys_info ,sys.dm_os_windows_info End
Code of tbl_all_server_detail table. This table will be created in Nisarg-PC.
IF EXISTS (SELECT NAME FROM dbatools.sys.tables WHERE NAME = 'tbl_all_server_detail') DROP TABLE tbl_all_server_detail go CREATE TABLE dbatools..tbl_all_server_detail ( id INT IDENTITY(1, 1), servername VARCHAR(50), instancename VARCHAR(100), sqlserverversion VARCHAR(50), sqlserveredition VARCHAR(500), defaultdatafile VARCHAR(max), defaultlogfile VARCHAR(max), authenticationtype VARCHAR(200), cpucount INT, physicalmemory NUMERIC(10, 2), lastsqlstarttime DATETIME, databasecount INT, sqljobcount INT, sqlfailedjobcount INT )
Code of sp_get_all_server_detail stored procedure. The procedure will be created on Nisarg-PC.
USE dbatools go CREATE PROCEDURE Sp_get_all_server_detail AS BEGIN TRUNCATE TABLE dbatools..tbl_all_server_detail INSERT INTO dbatools..tbl_all_server_detail EXEC [SQLWINDOWS].[DBAtools].[dbo].[Sp_get_server_details] INSERT INTO dbatools..tbl_all_server_detail EXEC [SQLUBUNTU].[DBAtools].[dbo].[Sp_get_server_details] END
Now, Execute the sp_get_all_server_detail. The procedure will populate data from both servers and store it in the table.
Once the procedure is executed, run the following query to view data.
SELECT * FROM dbatools..tbl_all_server_detail
Now, let us create an SSRS report to view the details of the server.
Create an SSRS report to show the data
We are using SQL Server data tools 2017 to create an SSRS report. First, create a Reporting Service project named DatabaseDashboard and add a new report named rpt_server_details. You can view the Report in Solution Explorer.
Now, add a data source to the Report. To do that, Open Report Data --> Right-click on DataSource --> Add Data Source. In the Data Source properties dialog box, specify the data source name, select Microsoft SQL Server as a Type, and Specify the connection string to connect to the DBATools database of Nisarg-PC. You can view the configured data source in Report Data pan.
Now, add a dataset. To do that, Right-click on Dataset --> Add Data Set. Specify the Dataset name, and select use dataset embedded in the report option. Select DsServerDetails as the DataSource name and enter the following SQL query.
Save the dataset by closing the dialog box. You can view the dataset in Report Data.
The report designer tools are in Toolbox. We are creating a list report; hence we will use the table tool of the report designer. Drag and drop the table tool in the report designer pan.
Now, drag and drop all fields of Dataset_server_details in a table tool which looks like the following image:
Once Report is prepared, click on Preview to view the Report, which looks like the below image:
Deploy the SSRS report
Let us deploy the Report on the report server configured in Nisarg-PC. You can read Configure a Report Server to learn more about the deployment process of SSRS reports on a Report Server. To deploy the reports, we must specify the TargetServerURL and TargetReportFolder. You can find the Report Server URL in Reporting Service Configuration Manager. Following image is for reference.
In our case, the report server URL is nisarg-pc/ReportServer/. To deploy the Report, right-click on Database Dashboard --> Properties. Specify the above URL in TargetServerURL and Database Dashboard as TargetReportFolder. Following image is for reference.
To begin the deployment, Select Build from top menu and click Deploy Solution.
Once the deployment completes, open Web Portal URL of Report Server which is nisarg-pc/Reports/report/DatabaseDashboard/rpt_server_details. Here you can see the server detail report. See following image for reference.
This article explains how to create a basic SSRS report to show the details of the database servers. Also, we learn the basic process of creating a list report and deploying it on the Report Server. This article can be a good start to creating a customized report to show the SQL Server details. In the next article, I will show you how we can provide more insights, like a list of databases and their properties, a storage summary, and insights of SQL Server Agent Jobs.