R-script Migration to SQL Server 2016

    0
    47

    Introduction

    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:

    1. Install R Server for Windows 9.0.1 (or later version)
    2. 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.

    1. As it is guided in the manual, I was trying to find out Setup.exe – No such one. The one I used: RServerSetup.exe.
    2. I did double click and manual installation. First image:

    3. After installing mentioned components, an installation guide is shown with multiple confirmations.
    4. Finally, you are asked to select installation path

    5. 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…

    LEAVE A REPLY