In the beginning of 2015 Microsoft purchased Revolution Analytics and with that they were able to incorporate R (the most popular open source programming language for advanced analytics) inside of SQL Server bringing R processing closer to the data and eliminating the need to export SQL Server data in order to perform R processing against it.
By incorporating R processing into SQL Server 2016, Microsoft is offering R Services as a new feature that supports enterprise-scale data science. Data scientists are now able to take their existing R code and run it right inside the SQL Server database engine to support advance analytics against big data.
This is the first version of SQL Server to have an integrated support for Revolution R for statistical analysis of data embracing the highly popular open source R language with the business to facilitate a deeper insight into the data.
Let see how to setup it.
R Services for SQL Server 2016 step by step setup (only showing the relevant steps for R Service).
R Services (In-Database) feature can be installed by using the SQL Server setup wizard. Run SQL Server 2016 setup and on the Installation tab, click New SQL Server stand-alone installation or add features to an existing installation.
NOTE: R Services for SQL Server 2016 is not cluster aware, so it can't be installed in a failover cluster.
On the Feature Selection page, select Database Engine Services and R Services (In-Database) options. This option configures the database services used by R jobs and installs the extensions that support external scripts and processes.
NOTE: R Server (Standalone) is an option that lets you use the Scale R libraries on a Windows computer that isn't running SQL Server. Only use this option to install Microsoft R Server (Standalone) on a computer that is used for R development, to create R solutions that later can be deployed to an instance of SQL Server running R Services (In-Database).
After all the configurations are set, on the page Consent to Install Microsoft R Open, click Accept.
This license agreement is required to download Microsoft R Open, which includes a distribution of the open source R base packages and tools, together with enhanced R packages and connectivity providers from Revolution Analytics and by clicking on Accept the Next > button will be enabled.
NOTE: Check the next step if the computer you are using does not have internet access.
The following page (Offline Installation of Microsoft R Open and Microsoft R Server) will only appear if the computer you are using does not have internet access. It provides the links to download the necessary files, so this setup can be paused while you download separately the Microsoft R Open and Microsoft R Server.
After downloading the files, click Browse and provide the correct location to the previously downloaded files.
NOTE: Alternatively you can download the necessary files from this link (when performing a slipstream install provide the respective Microsoft R Open and Microsoft R Server files).
On the Ready to Install page, verify what selections are included and confirm by clicking on Install and when installation is complete, restart the computer.
After restarting the computer the R Services feature needs to be enabled otherwise it will not be possible to invoke R scripts even if the feature has been installed by setup.
For that, open SQL Server Management Studio and connect to the instance where you installed R Services (In-Database) and run the following command to explicitly enable the R Services feature:
EXEC sp_configure 'external scripts enabled', 1 GO RECONFIGURE WITH OVERRIDE GO
For the changes to take effect you will need to restart the SQL Server service for the SQL Server instance. If SQL Server Trusted Launchpad service is stopped then start it also. You can restart the services by using SQL Server Configuration Manager.
NOTE: At this point if you are facing errors because SQL Server Launchpad service cannot be started please read this article that I wrote to solve the issue.
After the SQL Server Launchpad starts you should be able to run simple R scripts like the following in SQL Server Management Studio.
NOTE: If the R script returns an "Unable to communicate with the runtime for 'R' script in SQL Server" error please read this article that I wrote to solve this issue.
- References: Set up SQL Server R Services (In-Database)
Last Update: 2017-02-02
About the author
View all my tips