Do it right! Deploying SQL Server R Services on computers without Internet access

Reviewed by: Sanjay Mishra, Umachandar Jayachandran, Dimitri Furman, Jeannine Nelson-Takaki, Joe Sack, Kun Cheng, Eric Burgess

One of the most exciting features in SQL Server 2016 is R Services (in-database). This feature has been getting a lot of interest and attention, as we described in a past blog post. If you are curious to know more, the Related Viewing section at the end of this post links to some useful videos.

When deploying SQL Server R Services, it is important to note that the setup components for SQL Server do not include the Microsoft R Open and Microsoft R Server components. Those ‘R Components’ (as we will refer to them later in this post) are provided as separate downloadable components. SQL Server will automatically download these when executed on computer which is connected to the Internet. But in cases where setup is done on a computer without Internet access (quite typical of many SQL Server deployments) we need to handle things differently. There is a documented process for doing this. But even with the documentation, we still had some customers with questions on the process.

Inspired by those customer engagements, this blog post walks through the process of setting up SQL Server R Services in environments without Internet access. We walk through a number of scenarios, right from the very basic scenario to the more complex ones involving unattended and ‘smart setup’.

Let’s begin with the easiest ‘offline setup’ scenario for SQL Server R Services. In this scenario, setup is launched by double-clicking setup.exe from the SQL Server 2016 installation media. After accepting the license agreement, selecting the edition etc. you are prompted for the features to install. We will select just the SQL engine and R Services (in-database) for simplicity:

Figure 1: SQL Server setup feature selection screen

At a later stage, you are prompted to accept the licensing terms for installing R Open:

Figure 2: Consent to install R Open

After accepting the license agreement for R components, if the computer were able to access the Internet, it would automatically download the requisite R components and proceed. But given that we are in an ‘offline’ scenario, setup needs additional information to proceed. This is apparent when you look at the list of steps on the left of the wizard, a new step ‘Offline installation of Microsoft R Open and Microsoft R Server’ has appeared:

Figure 3: After consenting to install R Open

This new screen (figure 5 below) is where you are prompted to direct setup to the requisite copies of the R components. In one-off scenarios, you can look at the links on the screen, and download those files on a computer which has Internet access; then move the 2 files back to the computer where SQL setup is running (this is the computer which does not have Internet access). But to be proactive you can download these CAB files ahead of time based on the Installing R Components without Internet Access page. That webpage (figure 4) lists the URLs to the CAB files containing the correct version of the R components for each version of SQL Server 2016:

Figure 4: Documentation page listing the URLs to obtain the R components for different SQL versions

Once you have downloaded and moved those 2 CAB files to the ‘offline’ computer, you need to specify the location of that folder on the screen below:

Figure 5: Screen shown only for Offline Installation of R Open

On the next screen, the correct folder (C:\RComponentsOffline\RTM) has been selected:

Figure 6: After selecting the path to the CAB files for the R components

Note: If the path does not contain the correct files, the ‘Next’ button on the above dialog is disabled.

That’s it! Once the installation succeeds, validate the installation as per the steps in this article. For example, we use the simple R script below to check that the installation functions correctly.

 exec sp_execute_external_script @language =N'R',
@script=N'OutputDataSet<-InputDataSet',
@input_data_1 =N'select 1 as hello'
with result sets (([hello] int not null));

[Update 28 Nov 2016: We are aware of an issue with this scenario when Replication has been installed as one of the sub-features within the Database Engine component. When the instance being patched has Replication installed, you will not see the screen for selecting the path to the folder containing the CAB files. In that case, it is necessary to launch the cumulative update or Service Pack 1 setup as given below (the example below shows the sample syntax for CU1):

C:\InstallMedia\CU1\SQLServer2016-KB3164674-x64.exe /Action=Patch /IACCEPTROPENLICENSETERMS /MRCACHEDIRECTORY=<path to CU1 CAB files>]

Now that you have a RTM instance installed above, let’s patch it with Cumulative Update 1. CU1 also has a different version of the R components, so the process described above (downloading the correct CAB files and moving them to the ‘offline’ computer) is still applicable. Let’s see how this goes – first, let’s launch CU1 setup as below:

 C:\InstallMedia\CU1\SQLServer2016-KB3164674-x64.exe

Figure 7: Features being patched by the SQL Server Cumulative Update

CU1 setup recognizes that we are ‘offline’ and will prompt us to provide the location of the correct version of the CAB files. We do that in the screen below:

Figure 8: Specifying the folder location for the R components

That’s it! We will then have CU1 run to completion.

‘Smart setup’ in SQL Server has been around since SQL 2012, and even prior to that we had the ability to ‘slip-stream’ a hotfix / Cumulative Update / Service Pack into SQL Server.

In SQL Server 2016, to invoke smart setup on a computer without Internet access, you start SETUP.EXE from the command line and use the /UPDATESOURCE switch to specify the location of an ‘offline’ / local copy of SQL Server updates (Cumulative Update and / or Service Pack). The advantage of ‘smart setup’ is that SQL Server is patched right at the time of setup. This way, you avoid a separate restart / reboot to install CU1 / CU2 later on. Also importantly, setup executes with an updated (CU1 / CU2 in this case) version. That helps to proactively fix any issues that the setup engine itself may have (such as this issue.)

So in our case, let’s say we have downloaded a copy of the CU2 installer (SQLServer2016-KB3182270-x64.exe) to C:\InstallMedia\CU2 on our ‘offline’ computer. Then to invoke an ‘offline smart setup’ (with interactive setup screens) you start setup from an administrator command prompt:

 C:\InstallMedia\RTM\SETUP.EXE /ACTION="Install" /UPDATESOURCE=C:\InstallMedia\CU2

When setup launches, you will notice that it has recognized the CU2 installer as shown in the screenshot below:

Figure 9: ‘Smart setup’ in action

Clicking through the wizard, note that this time we provide the path to the R Components from CU2:

Figure 10: Specifying the location of the CU2 version of the R Components

The rest of the steps are just as per any other normal installation. At this stage, you will get an instance of SQL Server which is already at CU2 patch level!

The above 3 scenarios were installations done interactively – they had a GUI popping up and prompting for input, and finally displaying progress graphically. While that works great for a doing a few installations of SQL Server, when you have to deploy SQL Server on hundreds of instances, or if you need to provision SQL Server automatically, we need to use unattended setup for SQL Server.

Unattended setup is well-known and well understood. But when deploying SQL Server R Services you do need to handle the R component dependencies which we described previously. To do this, we have two command line switches:

• IACCEPTROPENLICENSETERMS – this switch is the equivalent of pressing the ‘Accept’ button in the GUI when prompted to accept the R Open licensing terms. Note that this is in addition to the IACCEPTSQLSERVERLICENSETERMS required for any unattended SQL Server setup.
• MRCACHEDIRECTORY – this switch is critical, because it is the way we tell setup to look for the R components in a specific folder.

Putting together these switches with a sample command line shown below, we will install a named instance of SQL Server (called SQL_RTM) with just the 2 features (SQL Database Engine and R Services in-database) selected. The command line below also relies on defaults for service accounts etc. So it does need customization, but if you are reading this, you probably know how to add more parameters! If you are not sure, the unattended setup help page will be a great starting point on what other parameters you can customize the setup with. OK, let’s see the command line now:

 C:\InstallMedia\RTM\SETUP.EXE /ACTION="Install" /IACCEPTROPENLICENSETERMS="True" /IACCEPTSQLSERVERLICENSETERMS="True" /MRCACHEDIRECTORY="C:\RComponentsOffline\RTM" /FEATURES=SQLENGINE,ADVANCEDANALYTICS /INSTANCENAME="SQL_RTM" /INSTANCEID="SQL_RTM" /SQLSVCINSTANTFILEINIT="True" /SQLSYSADMINACCOUNTS="Somedomain\someuser" /SQLTEMPDBFILESIZE="8" /SQLTEMPDBFILEGROWTH="500" /SQLTEMPDBLOGFILESIZE="500" /q

That’s it! The above command line setup will install without any human intervention, and will correctly provision the required R components as well.

To add on to the previous scenario, let’s imagine that you wanted to automate the rollout of CU1 to their (existing) SQL instance which already has R Services installed. Again, they want to do this completely unattended. So you can write a script which does the following:

• Firstly, it will extract a copy of CU1 to a folder on the ‘offline’ computer as shown below. The /X: switch is a standard parameter for any servicing update packages (Cumulative Updates, Service Packs) for SQL Server:

 SQLServer2016-KB3164674-x64.exe /X:C:\InstallMedia\CU1\Extracted

• Secondly, the script will copy the CAB files for the R Components (previously downloaded from the Internet) to a folder on the ‘offline’ computer. In our case, this location is C:\RComponentsOffline\CU1.
• Finally, the script that you have created would proceed to run the extracted copy of CU1 setup, additionally passing in the 2 additional R component related switches described previously (IACCEPTROPENLICENSETERMS and MRCACHEDIRECTORY):

 C:\InstallMedia\CU1\Extracted\SETUP.EXE /MRCACHEDIRECTORY=C:\RComponentsOffline\CU1 /ACTION=Patch /IAcceptSQLServerLicenseTerms /IACCEPTROPENLICENSETERMS="True" /INSTANCEID=SQL_RTM /q

When the above script completes, SQL Server would have been patched to CU1 and our R Services installation would have also been updated correctly!

This ‘mother of all scenarios’ is common in large enterprises where there is a high degree of automation and equal emphasis on patching out-of-box. In such cases, administrators do not want to deploy an RTM installation as-is if there is already a patch available. Basically, this is Scenario 3, but done in an unattended way!

As in scenario 5, imagine that you wrote a script to do the following on the ‘offline’ computer:

• Copy the RTM bits to a folder called C:\InstallMedia\RTM
• Extract CU2 bits to the ‘offline’ computer to a folder called C:\InstallMedia\CU2
• Copy the CU2 version of the R components to a folder called C:\RComponentsOffline\CU2

Then the script would have a command line such as the below. The below command installs a named instance of SQL Server 2016 called ‘SQL_CU2’. By now, you would easily recognize the critical switches IACCEPTROPENLICENSETERMS and MRCACHEDIRECTORY:

 SETUP.EXE /ACTION="Install" /IACCEPTROPENLICENSETERMS="True" /IACCEPTSQLSERVERLICENSETERMS="True" /UPDATESOURCE="C:\InstallMedia\CU2" /MRCACHEDIRECTORY="C:\RComponentsOffline\CU2" /FEATURES=SQLENGINE,ADVANCEDANALYTICS /INSTANCENAME="SQL_CU2" /INSTANCEID="SQL_CU2" /SQLSVCINSTANTFILEINIT="True" /SQLSYSADMINACCOUNTS="Somedomain\someuser" /SQLTEMPDBFILESIZE="8" /SQLTEMPDBFILEGROWTH="500" /SQLTEMPDBLOGFILESIZE="500" /Q

This ‘offline, unattended smart setup’ results in a clean deployment of a CU2 version of SQL Server 2016, with R Services in working order!

If you are reading this post, you will most likely also run into another common issue – how do you obtain and install additional R packages (those which are not included in the standard Microsoft R distribution) on that ‘offline’ computer? There is again a well documented way to do this. Start with the official documentation here and then a related blog post here.

We think SQL Server R Services is the best thing since sliced bread J But seriously, a feature with such compelling value is really exciting. We want to ensure that there are no obstacles in your evaluation and adoption of this feature. Hopefully this blog post will complement the official documentation to make things much easier for you. If you still have questions, comments on this topic, do not hesitate to let us know!

• There are 2 videos from the ‘Data Driven’ series which are a must-watch. Here’s the first one and here’s the second.
• Bob Ward has a great talk about SQL Server R Services. View the recording here.
• Bill Jacobs and Sumit Kumar talked about SQL Server R Services at the recent Ignite conference. View the video here.
• Our customer – PROS, was an early adopter of SQL Server 2016 R Services and they share their experiences here.
• For a broader overview of Microsoft’s various offerings in the ‘Intelligence’ space, view Rafal Lukawiecki’s presentation at Ignite 2016.

We trust you found this blog post interesting. Please leave your feedback and questions in the comments section below. Till next time, ciao!

• Anonymous
  October 20, 2016
  The comment has been removed
  • Anonymous
    October 20, 2016
    The comment has been removed
• Anonymous
  December 27, 2016
  The comment has been removed
• Anonymous
  January 11, 2017
  I have SQL 2016 with SP1, but no R-Services installed, and I now need to install R-Services. When I get to the Offline Installation page and enter the install path, it only accepts the path and enables the Next button if the RTM CAB files are in the folder. These notes don't explain what to do in this situation. It looks like I would need to first apply the RTM files, then apply the SP1 Files, but since SP1 is already installed I can't see any way to do this. An alternative is to have multiple CAB files in the folder, but there is no indication that the installer will use the correct files. Or do I need to use the SP1 installer?I've found getting R-Services installed is not very robust. If anything goes wrong it doesn't seem to roll back the install properly, there is no way to do a manual uninstall, and even uninstalling SQL Server itself left a bunch of artifacts that have made a second attempt fail.
  • Anonymous
    January 26, 2017
    The comment has been removed