SQL Server 2016: R Integration Redux

SQL Server 2016: R Integration Redux

SQL Server 2016 has now reached Release Candidate 3 (RC3). One of the new features that continues to foster interest in the analytics and data mining community is the integration of SQL Server with the open-source statistics toolset R. If you fall into this category, I’m inclined to suggest you remain patient and wait for the release version.

I rather liked the name “Revolution R”, but the toolset is now the considerably more prosaic “Microsoft R.” In the rush to rebrand this tool, a pile of new documents have appeared, but most seem to be press releases and marketing materials rather than actual technical details. Making matters worse, many of the new documents have new file names, but prove to be the same documents that have been available for years, except that the name Revolution has been replaced with the name Microsoft.

Installing the In-Database R Services

Much has changed since the community technology previews, and not in a good way. The installation is a bit different and prone to failure. The installation program wants to download R, and sometimes seems unable to figure out how and the R installation fails. (As always, I suggest you install pre-release software on a virtual machine dedicated to the purpose of testing). On the bright side, it is no longer necessary to run the RegisterRExt.exe script to complete the integration with SQL Server.

There is a very real possibility that once you install a pre-release version of SQL Server R support, you will never be able to upgrade, and there will be too many little changes for you to manually return your system to a clean state.

Upgrading (Or Trying To)

If at all possible, install RC3 on a fresh VM to achieve the greatest chances for success. If you feel you must try to install RC3 on a system that has a previous version installed, I have a few suggestions that may help.

If you are attempting to upgrade rather than do a fresh install, before you do anything else, run the RegisterRExt.exe program with /UNINSTALL.

Steps which are necessary, but not sufficient, to achieve an upgrade are documented at https://msdn.microsoft.com/en-us/library/mt653951.aspx. Be sure to read this page carefully. In a particularly egregious example of sloppiness, the instructions provide a bullet list of two programs to uninstall; Microsoft (or Revolution) Open R and Microsoft Enterprise R. Only further down the page do they mention that Enterprise R must be uninstalled first! I hope you weren’t doing the steps while you were reading!

The instructions also mention possibly needing a new version of the RegisterRExt.exe program and provide a link to the exe file. Just the exe file. If you have already deleted the original files, you won’t have the config file and the dlls, and the exe you just downloaded simply won’t run.

Feature Selection for Installation Program

Note the “R Server (Standalone)” checkbox in the Shared Features section. This option does not install anything not already installed with in-database R, and there is no reason to select both checkboxes.

A Reminder

Just as previously, the execution of external scripts must be enabled as a server configuration. SQL Server must be restarted for this configuration setting to take effect. And, just as before, shutting down the server shuts down the SQL Server Launchpad service, which will have to be restarted manually before any R scripts can be launched from T-SQL code.

 

Enabling R scripts:

sp_configure ‘External Scripts Enabled’, 1

A quick test:

EXECUTE sp_execute_external_script

@language = N’R’,

@script = N’R_data_frame <- InputDataSet’,

@input_data_1 = N’SELECT ”Hello, World!”’,

@output_data_1_name = N’R_data_frame’

WITH RESULT SETS((MyColumn char (13)))

 

The “Standalone” Server

The installation program sports a curious new option, a “standalone” R Server. The “R Server” does not represent any sort of new technology, but is simply a way of installing “enterprise” R from the DVD without having to install SQL Server. It is unclear what the server “serves” except to provide the same RevoScaleR packages found only in Microsoft Enterprise R and not available with Microsoft Open R. While Microsoft R Server could indeed play the role of a server in, say, an HPC cluster, ironically the most practical use of the “Server” for many SQL Server folks will be as a client to the in-database R Services.

Even if you do not install SQL Server, Microsoft R Server will install in an SQL Server-style directory hierarchy.

R_Server_Installation_Folder

This installation will include the standard R graphical interface for Windows; you may wish to create a shortcut on the desktop. Of course, you might prefer to install a tool such as RStudio.

Conclusion

On the inexorable march towards the release of SQL Server 2016, the installation and documentation have wavered a bit. However, perseverance is rewarded and Microsoft R seems reliable, useful, and more than a little fun.

 

 

Type to search blog.learningtree.com

Do you mean "" ?

Sorry, no results were found for your query.

Please check your spelling and try your search again.