Setting up R Services in SQL Server 2017

Acquisition of Revolution analytics helps Microsoft to incorporate R (most popular open source programming language for advance Analytics) inside SQL Server bringing R closer to the world of advance Analytics.

Data scientist can take there existing R code and run it right into SQL Server database engine to support advance analytics on the data already existing in Microsoft's RDBMS system.

R services in SQL Server 2017 step by step setup.

Step 1:

R Services features can be installed by using SQL Server Wizard. Run SQL Server 2017 setup and on the installation tab. choose New SQL Server stand-alone installation or add features to an existing installation.

Step 2

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.

Step 3:

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.

Step 4:

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.

Step 5

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.

Step 6

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.

Enabling R Services 

Open SSMS(SQL Server Management Studio) and connect to the instance where you have installed R Services  and run the following command to explicitly enable the R Services feature.

We can get the current status of R services by checking the value of ‘external scripts enabled’ property.

To enable the property run the below command.

Again run the External scrips enabled command to see if the property has been changed. run_value should be changed to 1.

Still run_value is 0. Now it time to head over SQL Server configuration manger and restart the below services in order to bring query updates into effect.

• SQL Server(MSSQLServer)
• SQL Server Launchpad(MSSQLServer)

In property status "run_value" has been changed to 1.

Now it's time to play with some exciting R codes in order to test R Services. Lets's start with the all time favorite Hello world script 😋 

Inputs to this stored procedure include:

• @language parameter defines the language extension to call, in this case, R.
• @script parameter defines the commands passed to the R runtime. Your entire R script must be enclosed in this argument, as Unicode text. You could also add the text to a variable of type nvarchar and then call the variable.
• @input_data_1 is data returned by the query, passed to the R runtime, which returns the data to SQL Server as a data frame.
• WITH RESULT SETS clause defines the schema of the returned data table for SQL Server, adding "First Table" as the column name, varchar for the data type.

This is how we can integrate R Service in SQL Server (In Database) mode with R Scripts. Stay tuned for the next blog on Installation of R packages using SQL Server 2017.

Till then happy analyzing😊.