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:

1. Server / Host Name: Hostname / Server name on which the SQL Server is installed.
2. Operating system: The operating system of the server on which the SQL Server is installed.
3. SQL Server version: Version and edition of SQL Server.
4. Authentication type: Authentication type used to access SQL Server instance.
5. Clustering setup: Whether the SQL Server instance is clustered or stand-alone.
6. Database: Total databases created in SQL Server instance.
7. Total SQL Jobs: Count of SQL Server agent jobs.
8. Failed SQL Jobs: Count of failed SQL Jobs in the last 7 days.

Demo Setup

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 

Query output:

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.

Exec dbatools..sp_get_all_server_detail

Once the procedure is executed, run the following query to view data.

SELECT * FROM dbatools..tbl_all_server_detail

Query output

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.

Summary

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.