Getting Started With R In SQL Server 2017

MarlonRibunal, 2018-03-22 (first published: 2018-03-13)

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.

The post Getting Started With R In SQL Server 2017 appeared first on SQL, Code, Coffee, Etc..

Rate

Share

Share

Rate

Related content

Database Mirroring FAQ: Can a 2008 SQL instance be used as the witness for a 2005 database mirroring setup?

Question: Can a 2008 SQL instance be used as the witness for a 2005 database mirroring setup? This question was sent to me via email. My reply follows. Can a 2008 SQL instance be used as the witness for a 2005 database mirroring setup? Databases to be mirrored are currently running on 2005 SQL instances but will be upgraded to 2008 SQL in the near future.

Robert Davis

2009-02-23

1,567 reads

Networking – Part 4

You may want to read Part 1 , Part 2 , and Part 3 before continuing. This time around I’d like to talk about social networking. We’ll start with social networking. Facebook, MySpace, and Twitter are all good examples of using technology to let…

Andy Warren

2009-02-17

1,530 reads

Speaking at Community Events – More Thoughts

Last week I posted Speaking at Community Events – Time to Raise the Bar?, a first cut at talking about to what degree we should require experience for speakers at events like SQLSaturday as well as when it might be appropriate to add additional focus/limitations on the presentations that are accepted. I’ve got a few more thoughts on the topic this week, and I look forward to your comments.

Andy Warren

2009-02-13

360 reads