Search code examples
rsql-serversql-server-2017sql-server-2019

SQL Server machine learning services r version 3.5

According to this link, SQL Server machine learning service's highest R version is 3.3. Does anyone know when one can upgrade to version 3.5 or later?

Or is my only option to switch to sql server 2019?

Thanks.

Solution

  • Well cs0815, let's take a look.

    According to your post, you tagged this with sql-server-2017, so I'll be basing this on that version.

    After reading through your link and this one, I was thinking there might be two options:

    • Hack a new version of R into the R-Services module of SQL server.
    • Setup an R-Client with version 3.5

    However, it looks like R-Client is for a different use case than you might need so we'll focus on an attempt to replace R in R-Services with a higher version directly.

    After installing a new instance of 2017 with the following features: enter image description here

    R Services natively installs to: 

    "C:\Program Files\Microsoft SQL Server\MSSQL14.MSSQLSERVER\R_SERVICES"

    Using WinDiff, I pulled the following comparisons vs CRAN R 3.5.3 and Microsoft Open R 3.5.3 and not surprisingly the Microsoft Open R is much closer.

    Full diffs: R_Services vs CRAN R R_Services vs MS OPEN R

    Personally, I'm not at the level to understand all the differences in R Core / runtime between R 3.3 (R_Services) and R 3.5.3 so of main interest to me is the library diff.

    What exists in R_Services which does not in Open R is what I'm assuming to be the core of what makes R_Services work with SQL Server so that will be where we need to fill in those gaps with our "replacement" R_Services but with v3.5.3.

    Libraries which are completely missing - not just added docs or some small difference:

    • compatibilityapi
    • dorsr
    • microsoftml
    • mrsdeploy
    • olapr
    • revopemar
    • revoscaler
    • revotreeview
    • sqlrutils

    And that information leads to us to the Microsoft MLS Docs "R Packages" resource:

    https://learn.microsoft.com/en-us/machine-learning-server/r-reference/introducing-r-server-r-package-reference

    Which, no, it looks like those packages are not publicly available through MRAN, CRAN, or even directly via devtools from github.

    Still, searching brought me onto this possibility: https://learn.microsoft.com/en-us/sql/advanced-analytics/install/upgrade-r-and-python?view=sql-server-2017#offline-binding-no-internet-access

    Download after signing up with Visual Studio Dev Services: https://learn.microsoft.com/en-us/machine-learning-server/install/machine-learning-server-windows-install#download-machine-learning-server-installer

    So - binding this version of MLS to our existing instance: MLS 9.4.7 installation

    After that installation is fully complete, let's check and see what's available on our server!

    So first things first, this being a new instance of SQL Server - Set to enable external scripts: 

    sp_configure 'external scripts enabled', 1;
RECONFIGURE WITH OVERRIDE;

    And then to bring this soup to nuts: 

    EXECUTE sp_execute_external_script @language = N'R'
    , @script = N'
print(R.Version())
';

    output below:

    $language
[1] "R"

$version.string
[1] "R version 3.5.2 (2018-12-20)"

$nickname
[1] "Eggshell Igloo"

    TLDR: Yes - download the MLS 9.4.7 packages from Visual Studio Dev Services and install on server host.