SQL Server 2016 (CTP3) R Integration


 

This is how you get R Integration in SQL Server 2016 (CTP3):

There are some samples showing how to execute R Scripts in SQL Server. However they will not work unless you install the R-Packages you use in these scripts.

image

image

Hint: You might get this error when you install a package:

install.packages("ggplot2")

Package which is only available in source form, and may need compilation of C/C++/Fortran: ‘ggplot2’

The solution is to change the mirror:

chooseCRANmirror(81)
try again:
install.packages('ggplot2', repos='http://cran.us.r-project.org')
  • Finally you can execute the sample scripts. (Everytime a library is missing, install it in RStudio as described)
  • After you ran the sample files for iris data (highlighted in the first screenshot) you can configure SQL Server 2016 Reporting Services and in the Report Builder open the Sample Reports:
image

You will also find an example for a prediction model that can be deployed to SQL Server 2016 and can be called using T-SQL Code.

There is one example showing a regression model:

image

And a second example showing a classification model:

image

Conclusion: R-Integration is a brilliant new feature in SQL Server 2016. It enables the use of more than 7000 R-Packages with prediction models, optimization models and rich visualizations. In addition to that Revolution R Packages allow full parallelism and scalability (A big issue with traditional standalone R).

We can be sure to see more of this in the future.


Comments (5)

  1. LL says:

    Lukas, Thank you for the information.  This is an exciting new feature for SQL server and I predict that there will be a lot of adoption.   My concern as a DBA is the abiltiy to have the R integration services run on a separate "App" server from SQL server rather than on the same server as the database engine.

  2. LukasSteindl says:

    Hi, not sure if it will be possible to install the R Engine on a separate server. The biggest bottleneck with traditional R is to load Data from a Database into Data Frames. The idea behind the R integration is to get the algorithm as close as possible to the data.  

  3. Update says:

    with > CTP 3 the following fix is required after you did the installation steps above:

    stackoverflow.com/…/error-of-sql-server-2016-sp-execute-external-script-with-r-integration

    start an elevated command prompt and then follow the steps

    Step 1)

    "%programfiles%RRORRO-3.2.2-for-RRE-7.5.0R-3.2.2libraryRevoScaleRrxLibsx64RegisterRExt" /uninstall

    Step 2)

    if default instance:

    "%programfiles%RRORRO-3.2.2-for-RRE-7.5.0R-3.2.2libraryRevoScaleRrxLibsx64RegisterRExt" /install

    if named instance:

    "%programfiles%RRORRO-3.2.2-for-RRE-7.5.0R-3.2.2libraryRevoScaleRrxLibsx64RegisterRExt" /install /instance:InstanceName

    Regards

    Lukas

  4. Gabriel says:

    Will SQL 2016 BI edition support the R integration? Or, will SQL 2016 Enterprise be a minimum requirement to have R integrated?

    1. LukasSteindl says:

      there is no SQL 2016 Bi Edition: https://technet.microsoft.com/en-us/windows/cc645993(v=sql.90)
      Basic R is part of Standard Edition. Adavanced R is only in Enterprise Edition. The product group did a lot of changes in the R integration between CTP 3 and the Release Version. The documentations above is no longer valid! Its a lot simpler to integrate R in the release version of SQL Server 2016. Just make sure to add the firewall rule when it comes to integrating custom r packages. All described in my new blogpost: https://blogs.msdn.microsoft.com/lukassteindl/2016/06/09/sql-2016-r-integration-install-custom-packages-unable-to-access-index-for-repository/

Skip to main content