Getting R to work in SQL Server 2016 is not straightforward.
First, install Install Advanced Analytics Extensions along with SQL Server 2016. Follow the direction on this page step-by-step and in order. Installation of SQL Server 2016 will also install Microsoft .NET Framework 3.5 if the advanced analytics extensions is checked. Make sure to install both together or install separately prior to installing the advanced analytics component. Make sure to set Authentication mode to Mixed Mode. It is possible to use R in SQL 2016 with WIndows Authentication, however some of the installation steps refer to Mixed Mode logins.
Note: Do not change the default instance name; it will cause the RegisterRExt step to fail. Ensure the default instance name is MSSQLSERVER.
The next step is to Install R Packages and Providers for SQL Server R Services. First, install Install Revolution R Open 3.2.2. More details can be found here. You must install the exact version number supplied a that is inextricably linked to the SQL Engine.
After that is successfully installed, Install Revolution Enterprise 7.5
Next, install the Post-Installation Server Configuration steps. You must do these steps in order. Enabling external scripts is vital to get R working with SQL Server and must be complete.
After optionally increasing the amount of memory allocated to R, run the Post-Installation Script. This can only be found after installing Revolution Enterprise 7.5. The server must be restarted prior to executing this step so that the external scripts can be run externally.
The trickiest thing is this script. Need to run in an administrator shell. To start, right-click on the Shell icon in the start menu and select Run As Administrator. In the Command Prompt window, copy/paste the following. After executing, it should have the following output:
Source directory to pick the RExtension binaries determined to be "C:\Program Files\RRO\RRO-3.2.2-for-RRE-7.5.0\R-3.2.2\library\RevoScaleR\rxLibs\x64\".
Connecting to SQL server...
Sql server binn directory is "C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\Binn".
Sql server log directory is "C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\Log".
Creating user account pool...
Creating user accounts.
Adding firewall rule for user account pool.
Creating working directory for user accounts.
Saving user account configuration.
User account pool with 20 users created.
R installation found at C:\Program Files\RRO\RRO-3.2.2-for-RRE-7.5.0\R-3.2.2.
MPI installation found at C:\Program Files\Microsoft MPI.
Settings file C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\Binn\rlauncher.config created.
Stopping service MSSQLLaunchpad...
Stopping service MSSQLSERVER...
Copied xp_callrre.dll from C:\Program Files\RRO\RRO-3.2.2-for-RRE-7.5.0\R-3.2.2\library\RevoScaleR\rxLibs\x64\ to C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\Binn.
Copied RLauncher.dll from C:\Program Files\RRO\RRO-3.2.2-for-RRE-7.5.0\R-3.2.2\library\RevoScaleR\rxLibs\x64\ to C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\Binn.
Starting service MSSQLLaunchpad...
Connecting to SQL server...
Created role db_rrerole
Added extended stored procedure xp_ScaleR_init_job.
Granted Execute for extended stored procedure xp_ScaleR_init_job to db_rrerole.
Added extended stored procedure xp_ScaleR_queue_job.
Granted Execute for extended stored procedure xp_ScaleR_queue_job to db_rrerole.
Added extended stored procedure xp_ScaleR_retrieve_results.
Granted Execute for extended stored procedure xp_ScaleR_retrieve_results to db_rrerole.
Added extended stored procedure xp_ScaleR_query_status.
Granted Execute for extended stored procedure xp_ScaleR_query_status to db_rrerole.
Added extended stored procedure xp_ScaleR_cancel_job.
Granted Execute for extended stored procedure xp_ScaleR_cancel_job to db_rrerole.
Added extended stored procedure xp_ScaleR_cleanup.
Granted Execute for extended stored procedure xp_ScaleR_cleanup to db_rrerole.
R extensibility installed successfully.
Any other output may mean the installation did not go as expected.
Finally, you’ll need to configure a Database and enable access for ScaleR functions to individual databases. If you authenticated account provides you admin access, this step may be skipped.
After these steps have run successfully, R is read to be executed within SQL Server!