SQL Server 2016 R Services: Guide for Server Configuration

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


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


sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip



About the author
MSSQLTips author Koen Verbeeck Koen Verbeeck is a seasoned business intelligence consultant at AE. He has over a decade of experience with the Microsoft Data Platform in numerous industries. He holds several certifications and is a prolific writer contributing content about SSIS, ADF, SSAS, SSRS, MDS, Power BI, Snowflake and Azure services. He has spoken at PASS, SQLBits, dataMinds Connect and delivers webinars on MSSQLTips.com. Koen has been awarded the Microsoft MVP data platform award for many years.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips


Comments For This Article




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

 @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 (41063)

 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 (40976)

 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 5, 2016 - 11:53:43 AM - Gigi Voinea Back To Top (40609)

 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 5, 2016 - 2:13:55 AM - Koen Verbeeck Back To Top (40603)

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 4, 2016 - 8:19:56 AM - Gigi Voinea Back To Top (40594)

 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,

 














get free sql tips
agree to terms