Welcome to IDERA virtual education for SQL Diagnostic Manager. Today I am going to show you how to add a custom SQL script to measure SQL Server uptime. First, we are going to going to jump over here to Management Studio where I have the script laid out. This is basically running a select date diff against certain the service runtime for tempdb. When you execute, it displays the number of minutes that tempdb has been online. So the thing is with SQL Diagnostic Manager if you plan to add a SQL script as a custom counter there is a caveat to using that feature. And that is that it must return a single numeric value. As a matter of fact, when we go to Administration and add the custom counter that is one of the things that pops up here is that it must return a single numeric value. And that is because we need to be able to assign a threshold to whatever a custom script we add. So single numeric value keeps the reliability around that particular feature. So I have the script here that is measuring the uptime of tempdb basically. So I am going to copy that out. And then I can go over and plug that right into a script spot here on the SQL script custom counter. Now I have already added this script as a custom counter. So behind the screen here you will see in my list of custom counters. There is the SQL Server uptime. So I will just walk through the wizard here. And on the next screen, we have the option to use collected value each time or the per second value since the last collection. I am going to capture the actual value each time so that I can see that accumulate and go up over time. There are also some additional scaling options if we were dealing with decimals for example. But I am going to keep with a scaling of one and not mess with that. You can also run a test if you want just to make sure that the results are coming back the way you expect. And sure enough, it is coming back with a single numeric value as I expect. Next, we can give it a friendly name and categorize the custom counter. I have already performed that operation. So it is grayed out. Here I have already added the custom counter. So moving right along, we have the ability now to define whether lower values are worse than higher values or vice versa. Higher values or worse and lower values obviously when we are talking about SQL Server service uptime. Lower is going to be worse than higher values. If we see it drops off to zero. That is going to be something that is considered critical. So that is why I have selected lower values or worse than higher values and defined thresholds of 1 for critical 5 for warning and 7 for informational. And that is in minutes. So next we have the final screen. We select Finish and deploy that custom counter. It can also be linked to different instances that we want to measure the uptime. Once the counter is in place and over time, you may want to add that custom counter to a chart or a custom report for that matter. So to do that we will go back over to the instance. And on the dashboard, we can customize the dashboard. And drag a custom counter out to any of the chart windows. So I have grabbed the custom counter chart panel. And I am dropping it on the screen here. Then I am going to hit the little drop down and remove the current custom counter. And instead, we will put in SQL Server uptime. And then we can close the designer. It will prompt and ask you do you want to save this as a permanent change to the dashboard. I do not want to in this case. But you could save it if you want to. And now you are able to see the current server uptime. And like I said this should be going up constantly over time. So the next thing is the report. Maybe I want to build a report to show the uptime over the last 7 days. Well, I can go to the reporting section. And under the custom report feature, we have the ability to create new custom reports which allow us to add any of our custom counters as well as any of the out-of-the-box metrics. Up to 10 different metrics to a single custom report. I have already created an uptime report. But if I wanted to I could go ahead and edit that. And add additional metrics. Here is a list. Like I said of all the custom counters all the operating system metrics, SQL Server counters, virtual counters, and virtual machine counters. And in this case, I have selected custom counters and plugged in here on the right-hand side a single custom counter being the SQL Server uptime that I have added on the next screen. If we added more than one metric to be reported against. We can move up the list on the report different metrics that are a higher priority than others to be displayed. And it is as simple as that. Then in the background, we have the uptime report. We can select the instance that we want to view the uptime for. Choose the date range. I will look at the last seven days. In this case, you can go with hourly or daily sampling. And then we run the report. And we can see the details of you know how long the SQL instance has been up and running each day for the last seven days. This report can also be exported to Excel or PDF [portable document format] where you can print from the console to share this with other audiences. So that shows you how to add a SQL Server uptime custom counter. And then to apply it to a chart and a report. Hopefully, this has been helpful. If you have any questions, feel free to reach out to us. And other than that I hope this is useful to you. And you have a nice day.
- Whitepaper: I Do Declare
- Webcast: T-SQL Coding Techniques – Are You Playing with Fire?
- Webcast: 10 T-SQL Development Tips for SQL Server
- Video: How to Secure Database Code with Rapid SQL
- Video: How To Manage Objects and SQL Code with SQL Admin Toolset
- Video: How To Edit SQL with DB Optimizer
Topics : Database Monitoring,Database Performance,
Products : SQL Diagnostic Manager for SQL Server,