If you were using CTP 3.0 and later ran an in-place upgrade to CTP 3.2 this will silently break R Services. Uninstalling and reinstalling the R component will not fix the problem, but it can be fixed. There are a few interrelated issues here so bear with me.

How to install SQL Server CTP 3.0 with R Services

These are condensed from the official notes and tested thoroughly.

  • Install SQL Server 2016 CTP 3.0.
    • I used a default instance.
    • I put the instance directory on E: otherwise using the same paths.
    • I installed the Database Engine, Advanced Analytics Extensions, and SSMS.
  • Install the two required R components into the default locations.
    • RRO-3.2.2-for-RRE-7.5.0-Windows.exe
    • Revolution-R-Enterprise-Node-SQL-7.5.0-Windows.exe
  • Run the post-configuration steps:
Exec sp_configure 'external scripts enabled', 1; 
Reconfigure;

And then this as Administrator (note that there's a different command line for named instances, and both restart the instance): "%ProgramFiles%\RRO\RRO-3.2.2-for-RRE-7.5.0\R-3.2.2\library\RevoScaleR\rxLibs\x64\RegisterRExt.exe" /install

What's missing is a way to confirm that R Services is functioning properly, so to do that we'll use a simple piece of test code from the official tutorial which can be run immediately with no further configuration if you're in the sysadmin server role.

CREATE TABLE MyData ([Col1] int not null) ON [PRIMARY]
INSERT INTO MyData   Values (1);
INSERT INTO MyData   Values (10);
INSERT INTO MyData   Values (100) ;
GO
execute sp_execute_external_script
  @language = N'R'
, @script = N' OutputDataSet <- InputDataSet;'
, @input_data_1 = N' SELECT *  FROM MyData;'
WITH RESULT SETS (([NewColName] int NOT NULL));
GO

If this works then R Services are working. But likely it will fail the first time you try… no joke.

How to fix R_TempDir issues

Others have had the same problem with R_TempDir and it occurs when you have the instance files on a fresh NTFS disk which means it likely will not have the 8dot3name fsutil flag set. This means a short DOS-compatible name will not be have been generated for folders and files on the disk, and as it turns out the R configuration files in the CTPs don't allow spaces

The easiest way to work around this is to:

  • Navigate to your instance directory such as E:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL and note the NTFS security settings on the ExtensibilityData folder.

    • In CTP 3.0 these were CREATOR OWNER, SYSTEM, Administrators, and MSSQLLaunchpad with Full Control.

    • In CTP 3.2 they added Users and NT Service\MSSQLServer (or your database engine service account) with Read & Execute permission.

  • Move the folder somewhere without any spaces; e.g. E:\ExtensibilityData. Check the permissions moved successfully when you're done; in some cases I had to re-add Full Control for NT Service\MSSQLLaunchpad. In other cases I had to re-apply the folder permissions on the subfolders using "Replace all child object permission entries with inheritable permission entries from this object" in the Advanced Security Settings.

  • Take a backup of E:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\Binn\rlauncher.config, run Notepad as Administrator, open the source file, and change the WORKING DIRECTORY= line to the new path; e.g. E:\ExtensibilityData.

  • Restart the MSSQLLaunchpad service, and try again.

If you did everything right you now have R Services running under CTP 3.

Now install SQL Server 2016 CTP 3.2 as an in-place upgrade. Note how it picks up the existing instance ID of MSSQLSERVER and gives the upgraded version an instance ID of MSSQLSERVER900 (instance IDs are different to instance names). Once you're done this is the kind of error you'll get attempting to run the test query again.

What went wrong and how to fix it

I didn't find it mentioned anywhere officially but it seems that you're meant to uninstall and reinstall the R component. Do this by running this as Administrator (note that there's a different command line for named instances, and both restart the instance):

"%ProgramFiles%\RRO\RRO-3.2.2-for-RRE-7.5.0\R-3.2.2\library\RevoScaleR\rxLibs\x64\RegisterRExt.exe" /uninstall
"%ProgramFiles%\RRO\RRO-3.2.2-for-RRE-7.5.0\R-3.2.2\library\RevoScaleR\rxLibs\x64\RegisterRExt.exe" /install

But wait, it's not that easy! Your test query will still fail! The reasons behind this can be found in the log file output of the re-install process which you can find somewhere like here: C:\Program Files\RRO\RRO-3.2.2-for-RRE-7.5.0\R-3.2.2\library\RevoScaleR\rxLibs\x64\RegisterRExt.log

The specific errors causing a problem are these:

 Information: 0 : Unable to find SQL setup registry key HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL13.MSSQLSERVER\Setup with value SQLRUserGroup.
    DateTime=2016-01-13T15:21:06.8769223Z
 Information: 0 : Adding firewall rule for user account pool.
    DateTime=2016-01-13T15:21:07.1269968Z
 Information: 0 : Creating working directory for user accounts.
    DateTime=2016-01-13T15:21:07.1425698Z
 Information: 0 : Unable to find SQL setup registry key HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL13.MSSQLSERVER\Setup with value LaunchpadGroup.
    DateTime=2016-01-13T15:21:07.1425698Z
 Information: 0 : Unable to find SQL setup registry key HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL13.MSSQLSERVER\Setup with value SQLGroup.
    DateTime=2016-01-13T15:21:07.1425698Z

And upon looking at that location in Registry Editor it becomes readily apparent that Microsoft is looking in the registry key location for the pre-upgraded instance instead of the new one where the keys are actually located.

You're going to need to duplicate 3 keys to the old location just so that this process can complete properly.

  • Option click the three entries; LaunchpadGroup, SQLGroup, SQLRUserGroup; and then File -> Export to an easily accessible file.

  • Edit the registry file in Notepad to replace the new instance ID with the old one.

  • Save and import the registry file which will copy the keys to the location where R Services is expecting it.

  • Uninstall and re-install the R Component again as Administrator (note that there's a different command line for named instances, and both restart the instance):

    "%ProgramFiles%\RRO\RRO-3.2.2-for-RRE-7.5.0\R-3.2.2\library\RevoScaleR\rxLibs\x64\RegisterRExt.exe" /uninstall
"%ProgramFiles%\RRO\RRO-3.2.2-for-RRE-7.5.0\R-3.2.2\library\RevoScaleR\rxLibs\x64\RegisterRExt.exe" /install

Re-run the test query. If you're lucky this is all that was required, otherwise you may have the R_TempDir error again. In this case go through my instructions in the R_TempDir section above again. Here's what the CTP 3.2 security settings look like (I didn't examine the sub folders; if you can use xcopy or robocopy to ensure you have copied NTFS permissions so you don't need to worry about these then please do so).

But after that, it will work, and has worked for me on multiple CTP servers and my test VM.