Learn more about SQL Server tools

mssqltips logo
giveaway
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

SQL Server 2016 R Services: Guide for Server Configuration


By:   |   Read Comments (6)   |   Related Tips: More > SQL Server 2016

Attend a SQL Server Conference for FREE >> click to learn more


Problem

SQL Server 2016 comes with the integration of the popular language R into the database engine. This feature has been introduced in SQL Server 2016 preview CTP3.0 and is called SQL Server R Services. This tip will guide you through the set-up and configuration of the server components.

Solution

SQL Server 2016 preview

As mentioned earlier, at the time of writing SQL Server 2016 is still in preview (at the time of writing CTP 3.0 has been released). This means that the features of R Services can still change and that functionality might change, disappear or added.

Installing SQL Server R Services

In order to install the R services on SQL Server, several steps need to be taken.

1. Install Advanced Analytics Extensions

The database engine component itself is called Advanced Analytics Extensions. During the SQL Server set-up, you can simply select this feature from the feature list.

Advanced Analytics Extensions

One of the prerequisites is to install the .NET 3.5 SP1 framework. You also get a warning that after the installation of the extensions, you need to perform some post-installation steps. These will be described in the following sections.

Feature Rules

The Advanced Analytics Extensions service will run under the MSSQLLaunchpad service and it will have its own service account. However, this service will not be listed in SQL Server Configuration Manager. During the SQL Server installation, make sure to configure mixed mode authentication, as some features of R services require SQL Server authentication.

For installation using the command line, check out the MSDN Page Install Advanced Analytics Extensions.

2. Install R Packages and Providers

This step will install the software of Revolution Analytics, a company acquired by Microsoft. This software installs an enhanced distribution of R, including connectivity tools needed to interact with the Revolution R Enterprise software (see next step).

2a. Install Revolution R Open 3.2.2

You can download the software here. Next run the installer as an Administrator.

RRO installer

The installation itself is straight-forward and doesn't need extra explanation.

RRO installer progress

2b. Install Revolution Enterprise 7.5

You can download the software here. Revolution Enterprise is a platform for scaling R code which included many enhanced R packages and R functions. At the download page, you can choose between the server software (a node, around 80MB) or the client software. Unless you want the full development environment for R on your server as well, the node software should be sufficient.

Run the installer as an administrator.

Welcome screen

The first step is to check the prerequisites.

Prerequisites

Installation of these prerequisites should be pretty quick. In my case, the installer was stuck when installing the "Visual C++ 2008 SP1 Redistributable x86". I aborted the set-up and downloaded and installed the redistributables separately. When I launched the Revolution Enterprise set-up again, it could skip those prerequisites. When all the prerequisites are installed, the Revolution R Enterprise set-up will launch automatically.

Revolution R set-up

During the set-up, you can optionally install the R packages rpart and lattice.

Revolution R set-up - packages
  • rpart is a package for recursive partitioning for classification, regression and survival trees
  • lattice is a package for data visualizations inspired on Trellis graphics

After choosing the installation folder, the wizard is finished. Although only the node software is installed, there will be a simple IDE installed on the server, called Connector RGUI 7.5.0 64-bit.

RGUI

Using this GUI, you can easily write, test or debug your R scripts on the server.

RGUI chart

3. Post-installation Server Configuration

The final step is to run some scripts to finalize the R services set-up. First of all we have to enable external scripts in SQL Server, so that stored procedures can execute R scripts. This is done with the following script which sets the correct server option:

Exec sp_configure 'external scripts enabled', 1; 
reconfigure;

enable external scripts

The next script needs to be run in an elevated command prompt and it does a number of things:

  • it stops the SQL Server services (so be careful in production!)
  • it copies the necessary code for executing R scripts in the SQL bin path
  • it creates a windows user group named SQLRUserGroup and creates a whole bunch of windows users MSSQLSERVERxx accounts. These accounts are low privilege accounts used for running individual sessions of the R runtime. By default 20 accounts are created.
  • it registers several extended stored procedures
  • it creates a database role called dbrrerole in the master database. This role has the necessary permissions to execute the extended stored procedures of the previous step.
  • it stops and restarts the SQL Server Trusted Launchpad service
  • it restarts the SQL Server services
%programfiles%\RRO\RRO-3.2.2-for-RRE-7.5.0\R-3.2.2\library\RevoScaleR\rxLibs\x64\RegisterRExt.exe" /install

post installation script

For more information and for more possible - optional - post-installation steps, check out the MSDN page Post-Installation Server Configuration (SQL Server R Services).

Conclusion

This tip guided you through the server-side set-up of SQL Server R Services. If you followed all the steps, you should be able to execute R scripts on the server. For example:

Executing an R script

In subsequent tips, the client-side installation will be covered, as well as how you can run R with SQL Server and how you can integrate results in Reporting Services.

Next Steps


Last Update:


signup button

next tip button



About the author
MSSQLTips author Koen Verbeeck Koen Verbeeck is a BI professional, specializing in the Microsoft BI stack with a particular love for SSIS.

View all my tips
Related Resources





Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Notify for updates 


SQL tips:

*Enter Code refresh code     



Friday, August 11, 2017 - 1:13:21 PM - jku Back To Top

 @VJ

 

I had the same issues as you. I'm on SQL 2016 SP1. I had a feeling it was a permissions issue. so I added "NT Service\MSSQLLaunchpad" to Local Administrators Group then ran the RegisterEXT /uninstall and /install.

Afterewards that error went away and R was working again.

 


Saturday, March 26, 2016 - 8:16:01 AM - Tiddu Back To Top

 Hi Koen ,

Thanks for very well written instructions, for SQL Server RC0, microsoft isnow using newer version of R open and R enterprise server. I had error installing the older version, may be you should update the article to reflect the chanes.

cheers

 


Thursday, March 17, 2016 - 8:53:45 PM - VJ Back To Top

 I am evaluating the SQL Server 2016 RC0 release. I ran through all the setup instructions with no issues. The RegisterRext.log shows success:

 

 Information: 0 : R extensibility installed successfully.
    DateTime=2016-03-18T00:16:53.3976899Z

 

When i run the verification code using the SQL below, i keep getting the error as stated subsequently:


exec sp_execute_external_script  @language =N'r',
@script=N'OutputDataSet<-InputDataSet', 
@input_data_1 =N'select 1 as hello'
with result sets (([hello] int not null));
go

Msg 39021, Level 16, State 1, Line 1
Unable to launch runtime for 'R' script. Please check the configuration of the 'R' runtime.
Msg 39019, Level 16, State 1, Line 1
An external script error occurred:
Unable to launch the runtime. ErrorCode 0x80070490: 1168(Element not found.).
Msg 11536, Level 16, State 1, Line 3
EXECUTE statement failed because its WITH RESULT SETS clause specified 1 result set(s), but the statement only sent 0 result set(s) at run time.

***********************************************************************************************************************

When I run R commands in the RGUI, everything works.

 

Any help or direction will be greatly appreciated. Thanks!


Friday, February 05, 2016 - 11:53:43 AM - Gigi Voinea Back To Top

 Thanks for the reply,

I have manage to install different packages changing the path  in the  install function ( install.packages ("TSclust", lib = "C:\\Program Files\\RRO\\-3.2.2.-for-ERE-7.50\\R-3.2.2\\library"). I have used cmd comand, but I haven't tried this in R console. I guess it works as well.

Good luck!

Gigi,


Friday, February 05, 2016 - 2:13:55 AM - Koen Verbeeck Back To Top

Hi Gigi,

when you have installed Microsoft R Open (or RRO as it's called in this article), a small GUI called Connector RGUI is installed as well (the icon with the monkey). I used that to quickly install new packages, just like you would in any other R IDE.


Thursday, February 04, 2016 - 8:19:56 AM - Gigi Voinea Back To Top

 Hi!

I have SQL Server 2016 on a Windows Server 2012 and I would like to install and run R scripts with  sp_execute_external_script procedure with  based on other packages availeble on Cran, like TSclust, fda, etc.

How can I intall those packages?
Thanks and good luck!

Gigi,

 


Learn more about SQL Server tools