Microsoft jumped feet first into R when they purchased Revolution Analytics . Microsoft’s newly acquired R functionality was quickly integrated into various client and server application such as Visual Studio 2015, Hadoop, SQL Server 2016 and R Server (standalone). Integration with Microsoft SQL Server 2016 opens new avenues for database developers to implement and operationalize R scripts in a very high performance environment. Executing R scripts on the SQL Server box minimizes the time the script requires to pull back large chunks of data, provides a bigger box to improve performance and makes it simpler for the IT department to operationalize an existing R script
This post will be a step-by-step guide to install and configure SQL Server 2016 R Services (In-Database) using the provided default values. It will be similar to the guidance provided by Microsoft to help those who might be running into installation and configuration issues. This will be an out of the box default install suitable for dev work but NOT suitable for production work. I will point out a few noteworthy tips to avoid some of the issues that I ran into.
Prep Your Server
I started with a clean Windows Server 2012 R2 instance which was updated and joined to my dev domain. Before you install SQL Server 2016 and R Services (In-Database) I suggest that you install SQL Server 2016 Management Studio and check your install drives for 8.3 filename support. Management Studio is used to configure SQL Server 2016 to enable script integration so it is simpler to have it already installed. R Services requires 8.3 filename support and if you install it or configure the data location for SQL, and therefore R Services, on a volume that does not support 8.3 filename support you will have no issue installing but you will have issues configuring and validating. There are a few work arounds to allow install on a volume without 8.3 filename support but so far I have not been able to get these work arounds to work.
To check for 8.3 filename support use FSUtil.exe in PowerShell. The following command will check the state of 8.3 filename support for the C: volume: FSUTIL.exe 8dot3name query C:.
Check all drives that you will install SQL Server and R Services on. If all the volumes support 8.3 filenames then feel free to skip over this section.
In my particular case, I attempted to set the data drives to the E: volume which did not have 8.3 filename support. SQL Server and R Services will install without error but you will not be able to communicate between SQL Server and the R runtime. Again I want to point out there is some suggested work arounds to move the R data storage to a folder that does not use long file names but I so far I have not been able to get those solutions to work. I will leave it up to the reader to determine if 8.3 filename support should be enabled on your server. I happen to be in a dev environment so I was able to simple keep the data on the C drive. I did test an install on the E drive after enabling 8.3 filename support and can say that it installed and configured without issue.
Installing SQL Server 2016 with R Services
If you are installing on a volume with 8.3 filename support the install should be very easy. In this post, I am focusing on R Services (In-Database) which means R Services will run integrated with SQL Server 2016. SQL Server 2016 does have an installation option for a standalone version which this post will not cover.
Start by launching the SQL Server 2016 installation wizard (setup.exe) and select Installation link on the left hand menu to view the installation options. Select New SQL Server stand-alone installation or add features to an existing installation option.
Enter your product key or select a free version on the Product Key page.
Read and accept the license terms.
Click Next on the Product Updates page.
Click Next on the Install Setup File page when it completes the setup of the install files.
R Services (In-Database) requires an instance of SQL Server’s database engine. Since we have not installed SQL Server yet check the R Services (In-Database) checkbox to install the database. If there was an existing instance of the database engine we could have added the R Services feature to the existing database instance. Select R Services (In-Database). Make sure R Server (Standalone) is UNCHECKED.
Select either the Default instance or Named instance and click Next. For my dev environment I selected Default instance. Select either the Default instance or Named instance and click Next. For my dev environment I selected Default instance.
For my dev environment I selected the default user accounts and passwords. Notice the SQL Server Launchpad Service. This service is used as a proxy to connect SQL Server 2016 to a scripting environment. Currently the only scripting environment supported is R. This service runs under the local NT Service\MSSQLLaunchPad account. During the install the MSSQLLaunchPad account is give specific file and security permissions.
The Server Configuration tab can be configured as your environment requires. For my dev environment I chose Windows authentication mode and clicked Add Current User to set a SQL Server Administrator.
Select the Data Directories tab.
The Data Directories tab should be chosen with caution. Earlier in this post I discussed the need for 8.3 filename support. In my dev environment I set the Data root directory to the E: drive. This set the User database directory, User database log directory and the Backup directory to the E: drive as well. This is not an issue if the E: drive has 8.3 filename support enabled. If it doesn’t ( and mine did not) the install will proceed just fine but someplace between the LaunchPad service and R Services the system will not be able to find the files it needs and you will spend a lot of time trying to figure out why. I have tested the install with an E: drive configured for 8.3 filename support and can say that it works fine. For my dev environment I kept it simple and left the Data root directory on the C: drive. Of all the issues I had in multiple installations of R Services, changing the data root directory to a volume that DID NOT support 8.3 filenames caused the most problems. The installation documentation for R Services (In-Database) should specifically call this out as a requirement or at least a big warning. This “requirement” is not called out in the installation documents. There is a paragraph about this in a troubleshooting document.
Read and accept the license for Microsoft R Open. Microsoft R Open is a free R distribution. Click Accept. Click Next.Read and accept the license for Microsoft R Open. Microsoft R Open is a free R distribution.
Click Install. Wait for the Install to complete.
Configure SQL Server 2016 to enable Scripting
With the installation successfully completed the last steps is to enable scripting on the SQL Server database instance and verify the database instance can run R scripts.
If you have not already installed SQL Server Management Studio for 2016 go ahead and install it on the dev server. Connect to the server and open a new query window. The query window to the master database.
Enter the following two commands in Management Studio and execute:
Exec sp_configure ‘external scripts enabled', 1
Reconfigure with override
The expected results are shown above. Next will need to restart the database instance and the LaunchPad instance. To restart the services, open SQL Server 2016 Configuration Manager. Double-click SQL Server Services in the left hand pane to expand the services in the detail area.
Right-click on SQL Server in the right hand pane and click Restart. This should stop and start the SQL Server instance and the Launchpad service. I restart the SQL Server Launchpad service as well just to make sure it has a clean restart. Ensure both services are in a running state after the restart.
With the services restarted return to SQL Management Studio. Verify the external scripts are enabled by running the following command in the query window:
Exec sp_configure ‘external scripts enabled’
The expected results for verification is shown above. SQL Server 2016 should be configured to run R scripts. Microsoft’s setup and configuration doc suggest the following test script to verify everything is working.
exec sp_execute_external_script @language =N’R’,
@input_data_1 =N’select 1 as hello’
with result sets (([hello] int not null));
Run the above script in the query window. Within a few seconds the results should appear.
If it takes longer than a few seconds, chances are you will get an error response.
Installing SQL Server 2016’s R Services (In-Database) is actually pretty simple as long as 8.3 filename support is enabled. I want to point out again that this is an install for a developer box and not for production. A production install would be similar. This post should help anyone who is having an issue with setting up a dev box.