Getting Started With R In SQL Server 2017

Prior to SQL Server 2016, running R on Microsoft platform required an installation of the R Language and its environment and an external IDE to write R codes. Then came the Microsoft R Server and its own flavor of R, the Microsoft R Open. With SQL Server 2016, and now 2017, Microsoft has brought R (and Python) closer to the database engine – SQL Server in this case.

I have a SQL Server 2016 instance that I upgraded to SQL Server 2017 with In-Database Machine Learning. I was not going to reinvent the wheel here, hence the upgrade.

Note: If you’re upgrading SQL Server 2016 Reporting Services to the 2017 version, the SQL Server 2017 RTM installer does include SSRS. It actually uninstalls the service. There is now a separate installer for SSRS 2017. So, you have to plan your SSRS migration prior to the upgrade.

Here are the general steps to install or upgrade an older version of SQL Server, 2016 in this case, to the 2017 version.

Let’s start with the SQL Server Installation Center. Select Upgrade from a previous version of SQL Server because that is what we’re doing here – upgrade from 2016 to 2017.

After  entering the product key, accepting the license terms, and  selecting the instance to upgrade, the installer asks you to confirm the separate migration of the SQL Server Reporting Services

Also, please take note that the SQL Server 2017 RTM installer does not allow changing features. So, it installs both the In-Database Machine Learning Services and the Standalone Machine Learning Server which you can access via Microsoft R Client.

The installer includes Microsoft R Open which is a good thing because you do not have to install and configure it separately.

Configure the instance, Service Accounts, and Full-Text. Check the Summary, then hit Upgrade

Before you can run R in SQL Server 2017, you need to enable external script to allow the sp_execute_external_script system stored procedure to run. This is not on by default.

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

Once that is set, let’s execute the obligatory Hello, World! in R + TSQL on SQL Operations Studio.

EXEC sp_execute_external_script

@language =N'R',

@script=N'OutputDataSet<-InputDataSet',

@input_data_1 =N'SELECT ''Hello R From TSQL'''

WITH RESULT SETS (([HelloR] VARCHAR(100) notnull));

GO

Enjoy! I hope to write more about R and Machine Learning in the future posts.

Author: Marlon Ribunal

I am SQL Server Database Administrator for a software company catering to supply chain and retail industry.