This might be useful for those who are about to integrate standalone R-script tasks to Microsoft SQL Server infrastructure.
Background
While refactoring the existing system, one of the tasks was running R script. It takes a long time to run mainly due to a large amount of data being passed to R, processed and returned.
Steps:
- First glance to R part
- Migrate R to SQL as is - failed with differences in calculations
Some details below (mostly for myself).
Solution
If your Microsoft SQL is configured and you need to update R version:
- Install R Server for Windows 9.0.1 (or later version)
- Go to installed folder and copy/paste all the content of R_SERVER folder to R_SERVICES folder that SQL is looking to.
YES - that is simple!!! If it looks not to be - go through steps below. Hopefully, this set of links will help someone!
Details
Allow Using sp_execute_external_script on Server
This is an extraction from this article:
Exec sp_configure 'external scripts enabled', 1
Reconfigure with override
Exec sp_configure 'external scripts enabled'
USE [master]
GO
CREATE LOGIN [ServerName\SQLRUserGroup] FROM WINDOWS WITH DEFAULT_DATABASE=[master], _
DEFAULT_LANGUAGE=[us_english]
GO
Ensure that MSSQLLaunchpad is also running
EXEC sp_execute_external_script
@language =N'R',
@script=N'OutputDataSet<-InputDataSet',
@input_data_1 =N'SELECT 1 as number'
WITH RESULT SETS (([hello] int not null));
Configuration went well. I however decided to check if after SQL updates, it still will be working and led to R not working.
Migrate R Part Issues and Solutions
Here, I will put all issues and solutions found in my script. Unfortunately, I am not allowed to post the logic here, however specific operations may be useful for you.
1. SQL R Version
After migrating my R script into SQL, I noticed a warning:
(223 row(s) affected)
STDERR message(s) from external script:
During startup - Warning messages:
1: package 'rpart' was built under R version 3.3.2
2: package 'lattice' was built under R version 3.3.2
No attention was paid until QA. A speed down and difference in results calculation has been noticed.
It appears that SQL 2016 includes R version 3.2.2 which according to version releases is dated August, 2015. And we are using 3.3.2 in our project. Update is required.
2. R Stops Working After Installing SP1. Same with CU1 or CU2
As mentioned in one of the multiple forums, it has been decided to install SQL Server 2016 SP1. As a result, R stopped working at all (strange enough). Now it results in the following message:
Msg 39021, Level 16, State 1, Line 0
Unable to launch runtime for 'R' script. Please check the configuration of the 'R' runtime.
Msg 39019, Level 16, State 1, Line 0
An external script error occurred:
Unable to launch the runtime. ErrorCode 0x80070490: 1168(Element not found.).
What Did Not Help
2.1 Post-configuration installation script registerRext.exe as mentioned here:
"C:\Program Files\Microsoft SQL Server\
130\R_SERVER\library\RevoScaleR\rxLibs\x64\RegisterRExt.exe" /uninstall
"C:\Program Files\Microsoft SQL Server\
130\R_SERVER\library\RevoScaleR\rxLibs\x64\registerRext.exe" /install
pause
"C:\Program Files\Microsoft SQL Server\MSSQL13.PIXEL_MSSQLSERVER_2016\
R_SERVICES\library\RevoScaleR\rxLibs\x64\RegisterRExt.exe" /uninstall
"C:\Program Files\Microsoft SQL Server\MSSQL13.PIXEL_MSSQLSERVER_2016\
R_SERVICES\library\RevoScaleR\rxLibs\x64\registerRext.exe" /install
pause
C:\Program Files\Microsoft SQL Server\
MSSQL13.PIXEL_MSSQLSERVER_2016\R_SERVICES\library\RevoScaleR\rxLibs\x64\.
Although it has also been mentioned not to do it in the release version, I tried it.
2.2 Downloading R Server is kind of limited if not to say downloading and installing R Server is not accessible
Provided by this article, I tried to download from each of the three links from Install R Server 9.1 for Windows. It says "No results found". Try it! Maybe you have access. I even downloaded an Excel sheet with product list and their availability based on subscription - "R Studion" is not there!
2.3 Microsoft R Open Download
Microsoft R Open download link results in an archive with R distributive v. 3.3.3. I tried substituting present installation with this archive and running Post-configuration installation script registerRext.exe - same error.
I know this is very much of an overkill, though it is worse to try.
2.4 Windows R Server 9.0.1 installation
My support team provided me with distributive (both 9.0.1 and 9.1). I began with 9.0.1 to check how migration goes from each other not mentioning that we were using R 3.3.2 while R Server 9.1 is based on R 3.3.3.
Set up SQL Server R Services (In-Database) led me to Unattended Installs of R Machine Learning Services (In-Database) article where under section "Unattended Install of R Services (In-Database) in SQL Server 2016" aimed my case.
- As it is guided in the manual, I was trying to find out Setup.exe - No such one. The one I used: RServerSetup.exe.
- I did double click and manual installation. First image:
- After installing mentioned components, an installation guide is shown with multiple confirmations.
- Finally, you are asked to select installation path:
- And press Finish at the end and Restart Microsoft SQL Server.
Same error:
Msg 39021, Level 16, State 1, Line 0 Unable to launch runtime for 'R' script.
Please check the configuration of the 'R' runtime.
Msg 39019, Level 16, State 1, Line 0
An external script error occurred: Unable to launch the runtime.
ErrorCode 0x80070490: 1168(Element not found.).
The only difference, after installation, there was a RSetup file that tried running:
Strangely enough, there was not a sign of any activities - just silence. Not surprisingly - Error is in place even after restarting server.
2.5 SQL Server 2016 Repare and reinstall R
1. While Repare an error was shown:
2017-05-11T13:36:11 ERROR Error renaming source dir:
Access to the path 'C:\Program Files\Microsoft SQL Server\130\R_SERVER\' is denied.
2. I granted everybody with access (for experiment reasons) and Repare finished successfully with minor warning in installationlog:
2017-05-11T14:22:55 WARN C:\Program Files\Microsof2017-05-11T14:22:55
INFO Running command:
C:\Program Files\Microsoft SQL Server\
MSSQL13.PIXEL_MSSQLSERVER_2016\R_SERVICES\\library\RevoScaleR\
rxLibs\x64\registerRext.exe /install /sqlbinnpath:"C:\Program Files\Microsoft SQL Server\
MSSQL13.PIXEL_MSSQLSERVER_2016\R_SERVICES\\..\MSSQL\Binn"
/userpoolsize:0 /instance:"MSSQLSERVER"
2017-05-11T14:22:55 WARN
C:\Program Files\Microsoft SQL Server\
MSSQL13.PIXEL_MSSQLSERVER_2016\R_SERVICES\\library\RevoScaleR\
rxLibs\x64\registerRext.exe: Command returned exit code 0
So the R installation has not been finalized due to reason unknown to me. And this seems to be my case problem.
3. As guided here, I went again through a manual procedure with the path above:
"C:\Program Files\Microsoft SQL Server\
MSSQL13.PIXEL_MSSQLSERVER_2016\R_SERVICES\library\RevoScaleR\
rxLibs\x64\RegisterRExt.exe" /uninstall
!!! MAKE A PAUSE HERE !!!
"C:\Program Files\Microsoft SQL Server\
MSSQL13.PIXEL_MSSQLSERVER_2016\R_SERVICES\library\RevoScaleR\
rxLibs\x64\registerRext.exe" /install
With the following output at the end:
Copied RLauncher.dll from C:\Program Files\Microsoft SQL Server\MSSQL13.PIXEL_MSSQLSERVER_2016\
R_SERVICES\library\RevoScaleR\rxLibs\x64\ to C:\Program Files\Microsoft SQL Server\
MSSQL13.PIXEL_MSSQLSERVER_2016\MSSQL\Binn.
Starting service MSSQLLaunchpad...
R extensibility installed successfully.
At least no failure this time, however R still does not work.
Did I restart Server - YES!
2.6 Temp Dir Access
As mentioned in one of the posts, I granted with access to Temp Dir to everyone following the guide. I, however, was surprised that temp path was moved to SQL Server Data directory on another drive.
And sure restart LaunchPad service after granting access.
2.7 Launcher DLL RLauncher.dll not loaded! Error: 1114
Meanwhile in one of the logs, I found the following error: E:\MSSQLDATA\MSSQL13.PIXEL_MSSQLSERVER_2016\MSSQL\Log\ExtensibilityLog\EXTLAUNCHERRORLOG
2017-05-11 18:49:02.602 Launcher DLL RLauncher.dll not loaded! Error: 1114
2017-05-11 18:49:02.602 Failed to load the launcher RLauncher.dll and check satellite version
2017-05-11 18:49:02.602 No Launcher DLLs were registered!
2.8 Updating R from R (on Windows) – using the {installr} package
Unfortunately, installr as mentioned in this article is not available for version 3.2.2 (((
Loading required package: installr
Warning messages:
1: In library(package, lib.loc = lib.loc, character.only = TRUE, logical.return = TRUE, :
there is no package called 'installr'
2: package 'installr' is not available (for R version 3.2.2)
STDERR message(s) from external script:
3: In library(package, lib.loc = lib.loc, character.only = TRUE, logical.return = TRUE, :
there is no package called 'installr'
3. Useful SQL Queries
This is an extraction from repo file that is checking if all is correctly installed:
select CAST(SERVERPROPERTY('IsAdvancedAnalyticsInstalled') as int) as IsRServicesInstalled
, CAST(value_in_use as int) as ExternalScriptsEnabled
, COALESCE(SIGN(SUSER_ID(CONCAT(CAST(SERVERPROPERTY('MachineName') as nvarchar(128))
, '\SQLRUserGroup'
, CAST(serverproperty('InstanceName') as nvarchar(128)
)))), 0) as ImpliedAuthenticationEnabled
, coalesce((select cast(r.value_data as int)
from sys.dm_server_registry as r
where r.registry_key like 'HKLM\Software\Microsoft\
Microsoft SQL Server\%\SuperSocketNetLib\Tcp'
and r.value_name = 'Enabled'), -1) as IsTcpEnabled
from sys.configurations
where name = 'external scripts enabled';
if (select CAST(SERVERPROPERTY('IsAdvancedAnalyticsInstalled') as int) & _
CAST(value_in_use as int)
from sys.configurations
where name = 'external scripts enabled') = 1
begin
exec sp_execute_external_script
@language = N'R'
, @script = N'
# Retrieve properties like R.home, libPath & default packages
OutputDataSet <- data.frame(
property_name = c(
"R.home"
, "libPaths"
, "defaultPackages"),
property_value = c(
R.home()
, paste(.libPaths(), collapse=", ")
, paste(getOption("defaultPackages"), collapse=", "))
)
# Transform R version properties to data.frame
OutputDataSet <- rbind(OutputDataSet, data.frame(
property_name = names(R.version),
property_value = matrix(unlist(R.version), nrow = length(R.version), byrow = TRUE),
stringsAsFactors = FALSE)
)
'
with result sets ((property_name nvarchar(100), property_value nvarchar(4000)));
end
select (cast(value_in_use as int) ^ cast(value as int)) as IsRestartRequired
from sys.configurations
where name = 'external scripts enabled';
4. Make R Script be Runnable In-Database
Last comment in a thread helped me:
"I added the [NT Service\MSSQLLaunchpad] to the [Administrators] group and then restart the LaunchPad service. Thanks Daniel for the tips." (c)
This however didn't solve the problem with R version. Even after installing R Server - In-Database version is still 3.2.2.
5. Updating version to 3.3.2 (copy-paste)
After installing R Server for Windows 9.0.1 (that was my taget goal), a simple copy and paste helped:
- C:\Program Files\Microsoft\R Server\R_SERVER - source directory with R Server for Windows 9.0.1 installed
- C:\Program Files\Microsoft SQL Server\MSSQL13.PIXEL_MSSQLSERVER_2016\R_SERVICES - Destination folder where initially R installation was made (along with MS SQL Server 2016).
This script was helping to check the version being used:
Declare @rScript NVARCHAR(max) =N'
myPackages <- rxInstalledPackages()
OutputDataSet<-data.frame(myPackages)
version
print(getRversion())
print(Revo.version)
'
EXEC sp_execute_external_script
@language =N'R',
@script=@rScript
WITH RESULT SETS UNDEFINED;
GO
And Some More Good How-to Articles
Updates
Now R upgrade has succeeded after several days of researching www (thanks Google).
The next step is tuning R scripts...
History
- 5th May, 2017: Initial version
