You can install R Services on an initial installation of a SQL Server 2016 instance. You also can add R Services later by using the installation source.
The installation or addition process will install the R server and client libraries onto the SQL Server.
Note There are various considerations for installing R Services on SQL Server, and if you’re setting up a production system you should follow a complete installation planning process with your entire IT team. You can read the full installation instructions for R Services on SQL Server at
https://msdn.microsoft.com/en-us/library/mt696069.aspx.
For your research, and for any SQL Server developer, there’s a simplified installer for the free Developer Edition, which we describe in a moment.
Server
SQL Server comes in versions and editions. A version is a dated release of the software based on a complete set of features; it has a product name such as SQL Server 2016. SQL Server R Services is included with SQL Server Version 2016 and later.
An edition of SQL Server is a version with an included set of capabilities. These range from Microsoft SQL Server Express (a free offering), which provides a limited amount of memory, capabilities, and database size, to several other Editions up to SQL Server Enterprise, which contains all capabilities in the platform and can
use the maximum resources the system can provide.
More info You can learn more about which editions support each capability at
https://www.microsoft.com/cloud-platform/sql-server-pricing.
In a production environment, your IT team should help you research and decide on the proper edition of SQL Server to install. If you are installing a copy for yourself or for a
development environment, the SQL Server Developer Edition is often your best choice. It’s a free, single-user edition that contains all of the features and capabilities in SQL Server, and you can use it to work through all of the examples in this book. You can find the download for SQL Server Developer Edition at
https://www.microsoft.com/cloud-platform/sql-server-editions-developers, and you can start the installation process on your workstation or in a virtual server. But there’s a new method of installing the Developer Edition that’s even simpler: to download and install the software, go to
https://blogs.msdn.microsoft.com/bobsql/2016/
07/13/the-sql-server-basic-installer-just-install-it-2/.
If you have a previous installation of SQL Server 2016, you can add Microsoft R Server
capabilities. During the installation, on the Installation tab, click New SQL Server Stand-Alone Installation Or Add Features To An Existing Installation. On the Feature Selection page, select the options Database Engine Services and R Services (In-Database). This will configure the database services used by R jobs and install all extensions that support external scripts and processes.
Whether you’re installing for the first time or after a previous installation, there are a few steps you need to take to allow the server to run R code. You can either follow these steps yourself or get the assistance of the database
administrator.
Open the SQL Server Management Studio. Note that you can install SQL Server Management Studio directly from the installation media.
Connect to the instance where you installed R Services (In-Database), which is by default the
“Default Instance,” and then type and run (Press
the F5 key) the following commands to turn on R Services:
exec sp_configure 'external scripts enabled', 1 reconfigure with override
Restart the SQL Server service for the SQL Server instance, using the Services applet in the Windows Control Panel, or by using SQL Server Configuration Manager. Once the service restarts, you can check to make sure the setting is enabled by running this command in SSMS:
exec sp_configure 'external scripts enabled'
Now you can run a simple R script within SQL Server Management Studio:
exec sp_execute_external_script @language =N'R',
@script=N'OutputDataSet<-InputDataSet',
@input_data_1 =N'select 1 as helloworld' with result sets (([helloworld] int not null));
go
Client
When you install the R Services for SQL Server, the server contains the Microsoft R environment, including a client. However, you’ll most often use a local client environment to develop and use your R code, separate from the server.
You can use a set of ScaleR functions to set the compute context to instruct the code to run on the SQL Server instance. This method makes it possible for the data professional to use the
power of the SQL Server 2016 system to compute the data, with the added performance benefits of enhanced scale and putting the compute code directly over the data.
To set the compute context, you’ll need the Microsoft R Client software installed on the developer or data scientist’s workstation. You can learn more about how to do that and more about the ScaleR functions at
https://msdn.microsoft.com/microsoft-r/install-r-
client-windows?tduid=%2874674bbb9257612d8927ec 3c206c5172%29%28256380%29%282459594%2
9%28TnL5HPStwNw-VRuyHJhNp2D7.E7Jtg1Fiw%29%28%29&f=255&
MSPPError=-2147217396.
When you install the Microsoft R Client, whether remotely or on the server, several base packages are included by default
(https://mran.microsoft.com/rro/installed/):
stats
graphics
grDevices
utils
datasets
methods
base
Some packages (listed here) are included, but not loaded at startup. tools
compiler
parallel
splines
tcltk
grid
To load these packages, use the following command:
library("packagename")
Another method is to develop your R code locally and then send it to the database
administrator or developer to incorporate into a solution as a stored procedure—this is code that runs in the context of the SQL Server engine.
We’ll explore this more in a moment.
You have many client software options for writing and executing R code. Let’s take a quick
look at how to set up each of these to perform the examples in this book.