By: Tim Ford | Comments (10) | Related: 1 | 2 | 3 | 4 | 5 | > Reporting Services Dynamic Reports
Problem
I have a report that lists all databases for which a full database backup has not been applied in the previous 24 hours from the time the report is run. When I originally created the report I had a manageable number of databases to support and I was the sole DBA. The environment has grown significantly since then. Furthermore, I now have a junior Database Administrator that will eventually be responsible for making sure backups are completing as required. When I was just in the DBA role I knew which databases were important and needed to have backup failures addressed immediately and which databases were test or training versions, or were of lesser importance. In short, I need to upgrade my report to provide a triage mechanism to responsible staff members that are not as familiar with the SQL Server environment as I am. How can I do so?
Solution
Let's start by taking a look at the initial design of the report as shown below. As you can see, due to the length of the report it may be difficult to discern which database backup failure I should look into first, unless I have a first-hand understanding of each SQL Server database (server and database names have been changed to protect the innocent - and myself).
To address the need, I came up with the idea of adding a priority field to a couple tables within my SQL Server metadata repository. I added this field on the tables that contain my job, instance and database metadata. I could not simply set one priority status at the instance level, since I may (and do) have databases of varying importance on my SQL Server instances. However, I could not set the priority at only the database level since some SQL Server Agent jobs for a "Very Important" database may not be all that important. I think of the importance of backing up a database versus a job that posts current connection settings to Reporting Services. Finally, I could not simply set the priority at the job level, since there are many uses for reports that require a triaging at the database or instance level that have absolutely nothing to do with SQL Server Agent jobs.
That being said, I added this priority field to tables at the three levels of Instance, Database, and Job that persisted between refreshes of the SQL DBA Repository (the SSIS job that feeds this repository runs three times per day and wipes clean most tables). Once the database table structure was modified to account for importance of a database I incorporated this new field into the data set that serves as the source of the report's data.
The final step, was to add the conditional formatting code to the BackgroundColor and Color properties for the text box that contains the aging of the last database backup. This code looks to the value of the Priority field in the report's data set. Where the value of this field is equal to "High", the text box in the report will show as red with white text for readability. Where priority is equal to Low, the text box will be green with white lettering. Finally, all other values for Priority would show as yellow, with black lettering. Below are two screen shots with the logic.
BackgroundColor Property
Color Property
Now that the conditional formatting is in place and the Priority field has been added to the report's data set, the same report provides better insight towards triaging my response for backup assessment as shown below.
A few commands in Reporting Services goes along way to focus my team on the critical backups that need to be addressed on a daily basis.
Next Steps
- Review additional tips on Microsoft SQL Server Reporting Services for more ways to improve the efficiency of your reports.
- MSSQLTips.com also has information on this specific SQL Server DBA Repository. That information is available here.
- Add reporting to your metadata repository via SQL Server Reporting Services.
About the author
Tim Ford is a Senior Database Administrator with MindBody.This author pledges the content of this article is based on professional experience and not AI generated.
View all my tips
