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, we will explore how to create a database dashboard using SQL Server Reporting Services. The database dashboard will contain important details about the SQL Server instance, such as server/host name, operating system, SQL Server version, authentication type, clustering setup, total databases, total SQL jobs, and failed SQL jobs in the last 7 days.
Setting up the Demo Environment
Before we begin, let’s set up a demo environment to work with. We will create two virtual machines, one with Windows Server 2019 and SQL Server 2019, and another with Ubuntu 20.0 and SQL Server 2019. We will also create a separate SQL Server instance on the local machine that connects to both SQL Server instances using a Linked Server. Finally, we will create a database named SQLDashboard to store the details of both SQL Server instances.
Populating Data with Stored Procedures
To populate the data for our database dashboard, we will create two stored procedures named sp_get_server_details on the SQLWindows and SQLUbuntu database servers. These stored procedures will use a linked server to execute stored procedures on remote servers. The output of both stored procedures will be stored in a table named tbl_server_detail.
We will also create another stored procedure named sp_get_all_server_detail on the local machine, which will retrieve data from both SQL Server instances and store it in the tbl_server_detail table.
Creating an SSRS Report
Now that we have populated the data, we can create an SSRS report to display the details of the server. We will be using SQL Server Data Tools 2017 to create the report.
First, we need to create a Reporting Service project named DatabaseDashboard and add a new report named rpt_server_details. We will then add a data source to the report, specifying the connection string to connect to the DBATools database on the local machine.
Next, we will add a dataset to the report, using the sp_get_all_server_detail stored procedure as the data source. We will then design the report using the table tool in the report designer, dragging and dropping the fields from the dataset onto the table.
Deploying the SSRS Report
Once the report is prepared, we can deploy it to the report server configured on the local machine. We will specify the TargetServerURL and TargetReportFolder for the deployment. The report server URL can be found in the Reporting Service Configuration Manager.
After deploying the report, we can access it through the Web Portal URL of the report server. Here, we will be able to view the server detail report, which will display the information from the tbl_server_detail table in a visually appealing format.
Conclusion
In this article, we have learned how to create a database dashboard using SQL Server Reporting Services. By following the steps outlined in this article, you can create a customized report to show the details of your SQL Server instances. In the next article, we will explore how to provide more insights, such as a list of databases and their properties, a storage summary, and insights of SQL Server Agent Jobs.