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.
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.
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.
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.
The installation itself is straight-forward and doesn't need extra explanation.
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.
The first step is to check the 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.
During the set-up, you can optionally install the R packages rpart and lattice.
- 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.
Using this GUI, you can easily write, test or debug your R scripts on the server.
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;
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
For more information and for more possible - optional - post-installation steps, check out the MSDN page Post-Installation Server Configuration (SQL Server R Services).
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:
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.
- Try it out yourself! Download and install the SQL Server 2016 preview and follow the steps in this tip to configure your server for R Services.
- For more info on R Services:
- For more SQL Server 2016, read these other SQL Server 2016 Tips.
Last Update: 2015-12-15
About the author
View all my tips