Upgrade SQL Server 2016 R Service (in-database)

Question

I am just wondering whether this is doable. I have installed R service (in-database) together with SQL Server 2016 (patched at SP1+ CU1).

I notice R service version is at 3.2.2, which you can run the following script to check

declare @v varchar(100);
exec sp_execute_external_script @language=N'R'
, @script = N'v <- toString(getRversion())'
, @params = N'@v varchar(100) output'
, @v = @v out;

print @v;

-- returns 3.2.2

But I also installed Microsoft R client, and notice its R service engine is versioned at: 3.3.2.

So my question is "does SQL Server R service (in-database) now support R version 3.3.2 ?" if so, how can I upgrade it? if not, I guess I will wait until MS ships the update.

I read MSDN, and in it, it mentions using sqlBindR.exe to do the update, but this tool is available only with Windows R server, which I did not install and I even doubt that if I installed it, whether it would update the in-database R service.

Answer 1

I faced the very same problem (when trying to follow the steps described in Use sqlBindR.exe to Upgrade an Instance of R Services). I could not find any SQLBindR.exe.

A friend of mine pointed out to me, that I had to explicitly install "Microsoft R Server", not as part of the SQL Server install bits and pieces, but as a separate download.

All options about how you can download Microsoft R Server are described in a blog post at MSDN, Run Microsoft R Server for Windows by Heidi Steen.

Download and install was done in some minutes. SQLBindR took some more minutes. Voilá: Now the following script is showing me, what I was looking for. :-)

declare @v varchar(100);
exec sp_execute_external_script @language=N'R'
, @script = N'v <- toString(getRversion())'
, @params = N'@v varchar(100) output'
, @v = @v out;

print @v;

-- returns 3.3.2

Answer 2

SQL Server 2016 CU14 and later will upgrade R Services (in database) to 3.5.2 or later. You can download CU14 here: catalog.update.microsoft.com/....

You will then need to Change the default R language runtime version. This can be done using the C:\Program Files\Microsoft SQL Server\MSSQL13.xx\R_SERVICES.x.x\library\RevoScaleR\rxLibs\x64\RegisterRext.exe command line program. Be sure to use the RegisterRext.exe program in the folder that contains the newest version of R_SERVICES. Here's an example that assumes a default instance of SQL Server is installed and that R_SERVICES is version 3.5:

cd "C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\R_SERVICES.3.5\library\RevoScaleR\rxLibs\x64"

.\RegisterRext.exe /configure /rhome:"C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\R_SERVICES.3.5" /instance:MSSQLSERVER

You can also remove the previous runtime version with the /cleanup option.

The tutorial from the Microsoft Documentation also applies to SQL Server 2017 (CU22 and later), for both Python (2017 only) and R.

Answer 3

_{Community wiki answer:}

I think the MSDN is fairly clear. The R Installer is on the SQL Server 2016 install media; it's the last item on the 'Installation' tab called 'New R Server (Standalone) installation'.

Alternately you could follow the instructions for Installing R Components without Internet Access. Practice on a VM if you want to confirm.

Answer 4

I agree with the answer by MEhrenmueller, but from my understanding the binding service comes with R-server, which means the expensive Enterprise license. Further than that, when I "unbinded" my R in-database, my R in-database folder WAS COMPLETELY ERASED. I had to re-install SQL Server.

I was expecting R in-database to be updated with SP1 or CU, but actually this seems not to be true from my tests. Unfortunately I don't remember the source that told me this.

So actually from my tests (upgrade via SP or CU, or binding) I see no solution for the question at the moment.