Getting Started With R Services and Forecasting

This is a guest post by Eduardo Castro. R Services and forecasting meet when a data scientist’s R script must run next to the data. These steps are for SQL Server 2016, where the feature is called R Services.

Eduardo Castro is a database expert and a partner at Linchpin People. Eduardo has a gift for learning new subjects and teaching them in a clear way.

Why Run R Inside the Database

New techniques from data science are now part of a data professional’s job. Recently I worked on a project with a new requirement. We had to apply forecasting algorithms to data that was already in our database. Data mining and Analysis Services came to mind first. Then came a restriction: a company data scientist had already written the algorithm in R Studio.

So we asked the project manager for approval to use a new feature of SQL Server 2016. It lets you include R scripts inside the database. The R code could move over, and this post shows how.

Install R Services and Test the Script First

First, you must use SQL Server 2016 and install the Advanced Analytics Extensions, also known as R Services. You choose it during setup. You can also install the Microsoft R Server standalone as a shared feature. Setup asks to install some supporting libraries, and you can accept the defaults.

Before you move any code, check that the R script works in R Studio. R Studio is a free tool for creating R scripts. Copy the script there, run it, and correct every problem first. Our sample is a simple script that uses a well-known data set called AirPassengers, which holds passenger statistics. A forecasting function then predicts the next values in the time series. Run in R Studio, it gives the forecasted values for the passenger data.

Enable External Scripts

After the install, you must configure the instance to allow R scripts. R scripts run as external scripts. Activate the feature with EXEC sp_configure 'external scripts enabled', 1; followed by RECONFIGURE;. Then restart SQL Server.

Install the Packages Your Script Needs

R scripts commonly need libraries that setup doesn’t install. The tseries package is one example, and our script needs it before it can run. Install it with RGui, which comes with the Analytics Libraries you chose during setup. Start RGui as an administrator, or the install fails. For a default instance of SQL Server 2016, the command is install.packages("tseries", lib = "C:/Program Files/Microsoft SQL Server/MSSQL13.MSSQLSERVER/R_SERVICES/library").

R Services and Forecasting With sp_execute_external_script

The R script is already tested, so we can port it as an in-database script. You run R with EXEC sp_execute_external_script, and you set the language to R. Four parameters matter. @language names the language. @script holds the body of the script, which you copy from R Studio. @input_data_1 holds a T-SQL query whose result the script reads, and this script needs none. @output_data_1_name names the data frame that holds the forecast. The optional WITH RESULT SETS clause gives the names and data types of the output columns.

EXEC sp_execute_external_script
    @language = N'R',
    @script = N'
library(tseries)
f    <- decompose(AirPassengers)
fit  <- arima(AirPassengers, order = c(1, 0, 0), list(order = c(2, 1, 0), period = 12))
fore <- predict(fit, n.ahead = 24)
U    <- fore$pred + 2 * fore$se
L    <- fore$pred - 2 * fore$se
df_U <- data.frame(U, L, fore$pred)',
    @input_data_1 = N'',
    @output_data_1_name = N'df_U'
WITH RESULT SETS (([U] float, [L] float, [Forecast] float));

Run in Management Studio, the script gives the same result as in R Studio. The main difference is that the script is now part of the database solution. It runs on Microsoft R Server and SQL Server 2016.

What to Remember

Test the R script in R Studio before you port it. Install R Services, enable external scripts, restart, and add the packages the script needs. Then paste the script into sp_execute_external_script and name the output columns.

There are new requirements for the DBA, and many of them come from the data science area. These steps show how to include R scripts inside the database in an integrated way. R Services and forecasting belong together when the data is already in SQL Server.

Note from Pinal: R is not installed on my SQL Server 2025 test server. The R Services and forecasting demo was not run. Later versions renamed R Services to Machine Learning Services and added Python. Their setup screens and library paths differ, so read the setup page for your version. If R won’t start, check the Launchpad service first. You could argue that R Services and forecasting belong on a separate server. It runs in its own process, but on the same machine, so it shares CPU and memory.

A forecast is not a separate system, it is a script that can live beside the data.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.