Ask Learn
Preview
Ask Learn is an AI assistant that can answer questions, clarify concepts, and define terms using trusted Microsoft documentation.
Please sign in to use Ask Learn.
Sign inThis browser is no longer supported.
Upgrade to Microsoft Edge to take advantage of the latest features, security updates, and technical support.
Note
Access to this page requires authorization. You can try signing in or changing directories.
Access to this page requires authorization. You can try changing directories.
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:
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:
SQLServer2016-KB3164674-x64.exe /X:C:\InstallMedia\CU1\Extracted
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:
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!
We trust you found this blog post interesting. Please leave your feedback and questions in the comments section below. Till next time, ciao!
Ask Learn is an AI assistant that can answer questions, clarify concepts, and define terms using trusted Microsoft documentation.
Please sign in to use Ask Learn.
Sign in