R Services (SQL Server 2016) or Machine Learning Services (2017 and 2019 CTP) provide you with the ability to perform data analysis from the database itself using T-SQL.

You can learn a little more about what you can do in the SQL Server blog.

On this post, I will show you how to setup and configure it so you can start using it right away in your applications and reports.

To install it

You have to choose it from instance features on your SQL server setup window, you then choose the languages you want to work (R, Python)

Note: if your computer does not have access to the internet, you will have to download two packages separately and then locate them in the setup window.

Continue the setup as usual and finish it.

After you run the setup, please make sure that SQL Server LauchPad service is running for the instance you have installed the Machine Learning Services.

I recommend you to patch the instance with the latest update available in case you encounter any issue with the service startup.

To configure it

Just connect to the instance where the Machine Learning Services was added, using your favorite tool, and run the following T-SQL to enable the code execution:

EXEC sp_configure  'external scripts enabled', 1
RECONFIGURE WITH OVERRIDE

You must see the following output:

After that, you have to restart the SQL Server service for it to work.

our last step is just to test it...

Testing the script execution

To execute scripts you must use the sp_execute_external_script procedure, you can find the official documentation here.

This is the most basic command I can think you can execute to see if everything is ok, displaying the license information of the R language:

EXEC sp_execute_external_script  
@language =N'R',
@script= N'license()'

Note that the first execution after a service restart will take some time to complete, but after waiting you should see the license information in the Messages window:

And now... a simple example with real data

For this example, we will take Application.Countries table from WideWorldImporters test database and display it sorted by population in descendent order with an R script, running this T-SQL:

USE WideWorldImporters
GO


EXEC sp_execute_external_script  @language =N'R',
@script=N'
dsTest<- InputDataSet
OutputDataSet <- dsTest[order(-dsTest$LatestRecordedPopulation),]
',
@input_data_1 =N'
SELECT 
 CountryName,
 FormalName,
 LatestRecordedPopulation,
 Continent
FROM [Application].[Countries]
WHERE Region = ''Americas'''
WITH RESULT SETS 
 (
  ( 
  [CountryName] [nvarchar](60) NOT NULL,
  [FormalName] [nvarchar](60) NOT NULL,
  [LatestRecordedPopulation] [bigint] NULL,
  [Continent] [nvarchar](30) NOT NULL
  )
 );
GO

If you have the database installed and run it, you can see the output with the data sorted from the R script:

You have learned how to execute simple R scripts against SQL server tables from the database engine, so now you can make more complex examples and do your own data analysis scripts.

For more in-depth information visit this link.