Setting up SQL Server R service (In Database) and R Server(Standalone)

Hello all,

Recently, I had worked on a requirement to install R services during the SQL 2016 installation. In this blog, I am covering the steps followed to install and configure R services (In database). Also, I have included steps to install R server (standalone) shared feature as part of SQL installation.

Please note that this blog is applicable to SQL 2016 installations only, From SQL 2017 installations, the R Services (In database) feature is called as machine learning service (In database) and R server (Standalone) is named machine learning server (Standalone). Also, please note that SQL 2017 also supports Python as programing language for statistics.

R services (In -database) and R Server (Standalone) are the new components introduced in SQL Server 2016 installations.  The purpose of these components is entirely different:

R Services (In-Database) To enable secure execution of R scripts on the local SQL Server computer. When you select this feature, extensions are installed in the database engine to support execution of code written in R. If you need to run your R code in SQL Server, either by using stored procedures or by using the SQL Server instance as the compute context,  R Services (In-Database) feature need to be installed.

Microsoft R Server (Standalone)  If the requirement is not to  use SQL Server as the compute context for developing R solutions, R server (Standalone) feature can be installed. Its recommended to install R Server (Standalone) on a laptop or other remote computer used for development.

Avoid installing  both R Services (In-Database) and R Server (Standalone) on the same computer.

Procedure to Install/Configure R Services (In database):

If the requirement is to use SQL Server as the compute context and to execute R code in SQL Server, select the component as below:

R services (In database) creates a new SQL service called "SQL Server Launchpad".

Once the setup is completed, ensure that SQL Server Launchpad service is started. Launchpad service has a dependency on SQL Server database Engine Service.

Once the installation is complete, to enable external script (R script) inside SQL server, “external scripts enabled” server configuration setting must be set to 1. Default value is 0.

sp_configure 'external scripts enabled'

-- Enabling the configuration setting

sp_configure 'external scripts enabled', 1

reconfigure with override

Once the above setting is set to 1, restart the SQL service. Otherwise, the “external scripts enabled” will be still in disabled state till SQL service is restarted. SQL service restart will also initiate restart of SQL launchpad service restart.

Post SQL service restart, test the below query to check if external R script can be executed.

If the above query execution which executes an external R code script is successful, it indicates that configuration of R services (In database) is successful.

Procedure to Install R Server (Standalone):

If the requirement is not to use SQL Server as the compute context to develop R solutions, install R server (Standalone) feature.

Applying Service pack/Cumulative updates when R Server (Standalone) is installed is tricky. If the machine where R Server (Standalone) doesnt have internet access to install Microsoft R Open and Microsoft R Server, offline installation option is available.

SRO (R Open) and SRS (R Server ) cab files can be downloaded from the location mentioned as per below. Download the files and copy the files to the server and provide the path via browse option.

Click on next and complete the installation. If this step is not followed, the setup installation would fail for R Server (Standalone) feature. If the server has internet access, setup automatically downloads the cab files during the installation.

Known issues reported with R services and R Server:

FIX: Version of R Client is incompatible with the Microsoft R server version 8.0.3
https://support.microsoft.com/en-us/help/3210262/fix-version-of-r-client-is-incompatible-with-the-microsoft-r-server-ve

FIX: Cannot install SQL Server R Services during an offline installation of SQL Server 2016 updates
https://support.microsoft.com/en-us/help/3210708/fix-cannot-install-sql-server-r-services-during-an-offline-installatio

Hope the above steps mentioned will help you in configure SQL Server R Services.

Please share your feedback, questions and/or suggestions.

Thanks,

Don Castelino | Premier Field Engineer | Microsoft

Disclaimer: All posts are provided AS IS with no warranties and confer no rights. Additionally, views expressed here are my own and not those of my employer, Microsoft.