Getting R to work in SQL Server 2016 is not straightforward.

Step 1

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.

Step 2

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:

Any other output may mean the installation did not go as expected.

Step 3

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!

  1. Hi Rick! Unfortunately I gave the SQL 2016 instance an explicit name, but ran the post-installation script as follows:

    C:\Program Files\RRO\RRO-3.2.2-for-RRE-7.5.0\R-3.2.2\library\RevoScaleR\rxLibs\x
    64>RegisterRExt /install /instance:SQL2016CTP
    Source directory to pick the RExtension binaries determined to be “C:\Program Fi
    les\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.SQL2
    016CTP\MSSQL\Binn”.
    Sql server log directory is “C:\Program Files\Microsoft SQL Server\MSSQL13.SQL20
    16CTP\MSSQL\Log”.
    Creating user account pool…
    User account pool already exists. Skipping user account pool creation.
    User account pool with 20 users created.
    Settings file C:\Program Files\Microsoft SQL Server\MSSQL13.SQL2016CTP\MSSQL\Bin
    n\rlauncher.config already exists.
    Stopping service MSSQLLaunchpad$SQL2016CTP…
    Stopping service MSSQL$SQL2016CTP…
    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.
    SQL2016CTP\MSSQL\Binn.
    Copied RLauncher.dll from C:\Program Files\RRO\RRO-3.2.2-for-RRE-7.5.0\R-3.2.2\l
    ibrary\RevoScaleR\rxLibs\x64\ to C:\Program Files\Microsoft SQL Server\MSSQL13.S
    QL2016CTP\MSSQL\Binn.
    Starting service MSSQLLaunchpad$SQL2016CTP…
    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_r
    rerole.
    Added extended stored procedure xp_ScaleR_query_status.
    Granted Execute for extended stored procedure xp_ScaleR_query_status to db_rrero
    le.
    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.

  2. What OS did you attempt this installation on? I am attempting this on a local Win8 machine (making sure I always run things as admin etc), but currently I am getting errors like:

    Error in try({ :
    ODBC statement error: [Microsoft][ODBC SQL Server Driver][SQL Server]Could not load the DLL C:\Program Files\Microsoft SQL Server\MSSQL13.SQL2016CTP\MSSQL\Binn\xp_callrre.dll, or one of the DLLs it references. Reason: 1114(Initieringen av en DLL-fil misslyckades.).
    Error in rxStartClusterJob(hpcServerJob, timesIsValidated = TRUE, continueOnFailure = FALSE) :
    Error in try({ :
    ODBC statement error: [Microsoft][ODBC SQL Server Driver][SQL Server]Could not load the DLL C:\Program Files\Microsoft SQL Server\MSSQL13.SQL2016CTP\MSSQL\Binn\xp_callrre.dll, or one of the DLLs it references. Reason: 1114(Initieringen av en DLL-fil misslyckades.).

    The swedish error message is ofcourse eq to “error 1114 dynamic link library (DLL) intialization routine failed”. I am currently stuck at this point and not sure what to attempt next. What would you do in this situation? From what I can see, everything is set up correctly from within SSMS regarding extended stored procedures, their roles etc. it’s the actual attempt to instantiate the DLL that fails… Dependency checker reveals that 5 or so dependencies seem to be “missing” for this DLL, but I can not interpret that information any further at this point… Thanks for an awesome blog btw! /Joakim, Sweden

  3. When checking the DLL xp_callrre.dll in Dependency Walker, I get:

    Error: At least one required implicit or forwarded dependency was not found.
    Error: At least one module has an unresolved import due to a missing export function in an implicitly dependent module.
    Error: Modules with different CPU types were found.
    Warning: At least one delay-load dependency module was not found.
    Warning: At least one module has an unresolved import due to a missing export function in a delay-load dependent module.

    🙁

  4. Hi Joakim,

    I installed SQL 2016 on Win10. I do remember getting a very similar series of messages as you did in your first comment. Unfortunately, these look like it mostly succeeded, but I was unable to get anything to work after that. Even though it talks about installing a named instance, I do not believe it is possible to get it to run successfully in that configuration.

    I uninstalled the named instance and installed with the default, unnamed service. Doing this will produce the series of messages described in the post. I was unsuccessful getting it to work in any other fashion.

    Looking at the logs, it appears the following was not executed. This took a while to execute on my (fast) machine:

    Creating user account pool...
    Creating user accounts.
    Processing 20/20
    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.

    It is only when this effort occurred (you could see it process each one up to 20) did this work.

    Also, make sure everything is done in the exact order specified. Steps I thought I could execute out of order cause other failures.

    Good luck!

Comments are closed.