Documentation forSQL Sentry

SQL Sentry Forecasting

Overview

Disk Forecasting uses advanced predictive analytics and machine learning technology to show you when you'll run out of storage space and predict how much space you'll need going forward. 

For more information about storage forecasting in SQL Sentry, please see the follow blog posts:

Available Disk Forecasting Methods

  • Standard
    • Standard forecasts use an enhanced linear regression algorithm. It's able to analyze your disk’s usage history, identify manual interventions such as someone truncating or deleting a large file to free space, and ensures those events don’t skew your forecast.
  • Advanced
    • Advanced forecasts learn from the workload on your storage volumes and use R/ML Services (In-Database) on the SQL Server hosting the SQL Sentry database.

Getting Started

Follow these steps to configure your SQL Server (hosting the SQL Sentry database) and SQL Sentry client to use storage forecasting:

  1. Verify SQL Server requirements and configure environment as needed
  2. Configure SQL Sentry monitoring service settings

Once your environment has been configured, you'll need to wait at least 24 hours to have disk forecasting data available. Then you'll be able to use the following features with disk forecasting:

  • Disk Space Analysis view
  • Advisory conditions for disk forecasting
  • The Windows Disk Forecasting report

Step 1. Requirements & Configuration

Disk Forecasting Mode Additional Requirements
Standard There are no additional requirements for Standard Disk Forecasting. You can proceed to Step 2 to configure the monitoring service settings.
Advanced The Advanced Disk Forecasting feature requires:

  1. A SQL Sentry database on SQL Server 2016 or later with R/ML Services in-Database enabled. 
  2. You must turn on the external scripts enabled option in SQL Server for the instance hosting the SQL Sentry database. 
  3. The SQL Server Launchpad service must also be running before using Advanced Disk Forecasting. 
Note:  If any of the above features or settings were not in place, you should restart the SQL Sentry client after setting them, then select the Advanced Disk Forecasting mode in Monitoring Service Settings as described in the Settings section of this article.

Important: 

If you are configuring Advanced Disk Forecasting on SQL Server 2019, you may run into installation errors during package installation due to changes in how SQL Server 2019 handles permissions. To configure Advanced Disk Forecasting on SQL Server 2019, complete the following steps on your SQL Sentry database server:

  1. Create C:\ProgramData\Sentryone and C:\Users\Public\SentryOne folders.
  2. Give the ALL APPLICATION PACKAGES user full control over the folders created in step 1.
  3. Continue the package installation. 

SQL Server Feature:  R/ML Services

How do I know if the Advanced Analytics Extensions feature is installed?

If you're unsure about the installation status of the R/ML Services in-Database feature for your existing SQL Server installation, you'll know for certain when you attempt to turn on the external scripts enabled option and the following error appears:

Msg 39020, Level 16, State 1, Procedure sp_configure, Line 166 [Batch Start Line 0] Feature 'Advanced Analytics Extensions' is not installed. Please consult Books Online for more information on this feature.

How do I install R/ML Services in Database (Advanced Analytics Extensions)?

Follow the instructions provided from the Install SQL Server 2016 R Services article on Microsoft Docs to add this feature to your existing SQL Server installation.

SQL Server Configuration:  External Scripts Enabled

How do I verify that external scripts enabled is turned on in SQL Server?

Check external scripts enabled by running:

EXECUTE sp_configure  'external scripts enabled'

SQL Sentry SQL Server Configuration external scripts enabled example

In this example, the run_value is set to 0, meaning that external scripts enabled is false/turned off. If your run_value is set to 1, then this feature is already enabled.

Note:  The config_value displays what the run_value will be the next time that SQL Server restarts. If it is set to 0, then the feature will be disabled after a restart.

How do enable external scripts enabled in SQL Server?

Execute the following to turn on external scripts enabled and reconfigure the SQL Server option:

sp_configure 'external scripts enabled', 1;
RECONFIGURE WITH OVERRIDE;

This is the success message:

Configuration option 'external scripts enabled' changed from 0 to 1. Run the RECONFIGURE statement to install.

At this point, the config_value is changed, but a restart is required to set the run_value.

SQL Server Configuration config_value example

Do I need to restart SQL Server to complete the process?

Yes, restart the SQL Server Instance to complete setting the run_value. 

Additional Information: If needed, follow the instructions from Microsoft Docs to restart the database engine.

 Verify that the config_value and run_value are both set to 1. Check external scripts enabled by running:

EXECUTE sp_configure  'external scripts enabled'

SQL Server Configuration config_value run_value example

How do I verify that SQL Server Launchpad is running?

Open the Services window to verify that MSSQLLaunchpad / SQL Server Launchpad (MSSQLSERVER) is running:

Task Manager verify that SQL Server Launchpad is running

Additional Information: If needed, see the Microsoft article for more details on verifying and testing a successful installation.

Step 2. Monitoring Service Settings

You have the option to select either Advanced or Standard mode.

Adding a Forecast Schedule 

1. Select Global Settings in the Navigator pane, and then select Forecasts.

SQL Sentry Monitoring Service Settings Forecasts

2. Select either Standard or Advanced from the Disk Forecasting drop-down menu.
  SQL Sentry Monitoring Service Settings Forecasts Disk Forecasting drop down

3. Select the ... to open the Select a Schedule window, and then select New to add a new Forecast Schedule.  

SQL Sentry Select a Schedule New

4. Select the desired parameters from the Schedule Properties window, and then select OK to save your schedule. SQL Sentry Schedule Properties window

5. Select the number of days desired for the Days Forward to Forecast. The default is 180 days.
SQL Sentry Monitoring Service Settings Forecasts Days Forward to Forecast

6. Select the Save icon Save Icon to save the Disk Forecast.

SQL Sentry Monitoring Service Settings Forecasts Save changes

Editing or Deleting a Forecast Schedule 

Edit a Forecast Schedule by completing the following steps:

1. Open the Monitoring Service Settings (Navigator > Global Settings) and select the Forecasts tab. 
SQL Sentry Monitoring Service Settings Forecasts

2. Select the ... to open the Select a Schedule window, and then select Edit to change the Schedule Properties parameters.
Select a Schedule Edit

3. Select OK to close the save the changes, select the Save icon Save Icon , and then select Regenerate Global Forecasts to refresh the Disk Forecast. 
SQL Sentry Monitoring Service Settings Forecasts Regenerate Global Forecasts

Deleting a Forecast Schedule

To delete a Forecast Schedule, complete the following steps:

 1. Open the Monitoring Service Settings (Navigator > Global Settings) and select the Forecasts tab. Select the ... to open the Select a Schedule window. Select the desired Forecast Schedule from the list, and then select Delete.
Select a Schedule Delete Forecast

2. Select Yes to remove the Forecast Schedule from the list, or select No to keep the Forecast Schedule in the list. Select OK in the Select a Schedule window to save your changes.
Delete Schedule window

Note:  A Disk Forecast runs automatically each day even if there is no schedule selected. A Forecast Schedule is applied to better control when the forecast runs.

Disk Space Analysis View

Note:  Disk space data is not generated until 24 hours after a Disk Forecast runs.

Select your desired target from the Navigator pane, and then select the Disk Space tab. Select the desired Logical Drive, and the Disk Forecasting chart appears at the bottom of the screen. On the Disk Space chart a tool tip displays with the Free Space and Forecasted Exhaustion Date. 

SQL Sentry Disk Space Forecasting example

Note:  The cell under Forecasted Exhaustion will be highlighted orange if the Forecasted Exhaustion date is within 180 days, and it will be red if it is within 90 days.

The Free Space chart displays the following parameters:

Free Space Chart Parameters Description
Disk Free Space Amount of available (free) hard drive space on a given disk.
Prediction Interval An estimated interval that represents the range of values that a future observation will lie within at a specific point in time based on the provided probability. The forecast is generated using a 70% prediction interval, so there is a 70% probability that the actual free disk space on a given date will fall within the forecasted prediction interval.
Forecasted Free Space Estimated amount of Disk Free Space that will be available for a given disk and date.

Note:   

  • The forecast displayed is out 180 days, but it can be scheduled for up to 3 years.
  • The length of the Disk Forecasting will impact the storage and accuracy of the data.

Advisory Conditions

There are advisory conditions available (on GitHub) to work with storage forecasting:

Reports

See the Windows Disk Forecast report available under Reports > Performance Analysis > Disk/File Space:

SQL Sentry Reports Windows Disk Forecast

Additional Information: For more information about this report, see the Reports Menus article.