The ugly truth when working with SQL Server is that there usually isn’t much money left over for monitoring once you pay for the license. Most DBAs accept this reality by implementing a custom data collection. But what happens when all you want is to visualize that data quickly?
Since I’m not a SQL Server DBA, I need some help determining what data elements were essential enough to start collecting into some custom tables. And that’s when I found SQLFacts.
SQLFacts is a comprehensive suite of 32 tools that includes queries for database development, administration, and performance tuning. After spending a few minutes getting up to speed with the tool, I ran the appropriate T-SQL, created my target tables, scheduled the agent task. About 10 minutes after that, I was able to start making some dashboards!
With just 20 minutes of work in SquaredUp, I could visualize all the important metrics you need as a SQL Admin. And, for those who are monitoring SQL with SCOM, SquaredUp gives you the opportunity to supplement and extend your SCOM monitoring with the additional metrics you want to add or are already collecting. Using SquaredUp lets you have your cake and eat it as a SQL Admin.
With less than an hour's worth of work, I was able to install SQLFacts and create four dashboards.
First, I read through then imported the needed T-SQL code (never import stray SQL code!). Then I fired up my MSMS, put my data in the master, and it created my file history, monitor, wait counts, etc. I pulled it up and everything was there.
If I’d had a special query or was looking for special data, SQLFacts will let you add it to the collection. For example, you may have a specific application on a weird server and you need to collect certain values, this will do it. Then, SquaredUp will let you visualize that and publish it (for free, even!). Some of the SquaredUp tiles even just call the stored procedures on demand.
Then, in SquaredUp, when you add a new tile you simply need to add a new provider, if it’s not already listed. It’ll take you to the integrations list. Choose SQLFacts. Then add in the connection string.
If you have multiple servers, just clone the dashboard, create a new data source, change which one it’s pointing too, and you have a new dashboard.
Here are the 4 dashboards I created:
The SQLFacts Master dashboard
This dashboard details how to get started using SQLFacts. It also includes an example Scalar tile that will change state based on a clever T-SQL statement with SQLFacts. I also added the Page Life Expectancy (of questionable value these days), but neat to see. And several server metrics that SQLFacts collects, like how your server is running so you don’t have to go back and forth with your Windows team.
Wait Status dashboard
The Wait Status dashboard includes a lovely table of Observed Wait Summaries and two graphs of Wait History (in percentage and seconds).
File History dashboard
File History is a graph heavy dashboard that shows all of the databases in your SQL Server Instance. You can see vital metrics like storage, which is huge for SQL. Data from this dashboard is so important, you could display this on a NOC or mission control dashboard easily.
SQL Server keeps track of its backup status, but even the most diligent Admins don’t check it daily. This dashboard brings the backup data goodness to one place to share with the world so you always know whose backup is the most recent. Share this with your App Admins, and they will be glad to check this every day for you!
GET STARTED TODAY
Zero to dashboard hero in 60 seconds
Start now with hundreds of customizable dashboards.