Microsoft acquired Revolution Analytics in 2015 and integrated R in SQL Server 2016.Today I will show you how to install, configure and run R script in SSMS. Like all other Microsoft software installation of R is pretty easy. You just need to run the set up and with few click on Next button will do all your job to install. This is one of the best thing I like about Microsoft products.
Though it is very easy to install, I wanted to show you particularly what you need to select to install R. In step Feature Selection, either select R services(in-database) or Standalone R server. It is not recommended to install both on the same server.
Note: Currently, it is not possible to install R Services (In-Database) on a failover cluster.
Here I am showing installation of R Services(In-database). So only select R services and click on Next. I have selected both for demo purpose only. Till Database Engine Configuration, it is all about SQL Server specific details you need to enter. When you reach to step Consent to install Microsoft R Open.
Once you click on Accept button, if your system is connected to internet then it will install Microsoft R open (formerly known as Revolution R open). If you are not connected to internet, then you will get below step.
Copy the given link and download the installation file whenever you are connected to internet. Here you just specify the path where you have downloaded the installation files and click next. That’s all your job is done. Click next till end. Once your installation is complete. You need to enable R services (In-database). This is disable by design for surface area reduction.
To enable R Script , an administrator has to run the following statement in SQL Server Management Studio.
sp_configure 'external scripts enabled',1 reconfigure with override
After running above statement restart your instance when you enabled external script to run. Make sure SQL Server Trusted Launchpad service is running.
To run R script in SSMS you need to use system defined stored procedure sp_execute_external_script.
EXEC sp_execute_external_script @language =N'R', @script=N'OutputDataSet<-InputDataSet', @input_data_1 =N'SELECT 1 AS hello' WITH RESULT SETS (([] int not null)); GO
Great you ran your first R script in SSMS.
For further reading you can browse to path C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER2016\R_SERVICES\doc\MicrosoftRServices_Getting_Started.pdf
