I’ve grown up reading Tom Clancy and probably most of you have at least seen Red October, so this book caught my eye when browsing used books for a recent trip. It’s a fairly human look at what’s involved in sailing on a Trident missile submarine…
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.
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.