Managing Machine Learning Services for SQL Server 2017 and R

This chapter will tackle managing R Services and the steps necessary to get R Services running from a SQL Server database. In detail, this chapter will cover the following topics:

• Installing SQL Server with R services
• Configuring the environment and installing R Tools for Visual Studio (RTVS)
• Resource Governor
• Security
• Sessions and logs
• Installing new R packages
• Managing SQL Server R services with PowerShell
• Getting to know the sp_execute_external_script external procedure

With SQL Server 2017 installation, configuration, and setting up the working environment aren't that much different, compared with how SQL Server 2016 operated when R was originally introduced. If there are differences between the versions, they will be pointed out.

Installing SQL Server with machine learning R Services using SQL Server 2016...

Minimum requirements

Basic R integration is available in almost all SQL Server editions, except the Express edition (unless it is Express with Advanced Services). This means that a standard SQL Server will support R services to the extent of using vanilla and native R code, but no advanced R integration or pushing computations to other computational contexts. The same logic will apply to SQL Server 2017 (at the time of writing), and editions and features are already made public at the following URL:

https://docs.microsoft.com/en-us/sql/sql-server/editions-and-components-of-sql-server-2017

In terms of the operating system, many Microsoft OS versions (such as Windows Server 2012 and higher, Windows 8 and higher) are supported and, starting with SQL Server 2016 and higher, Linux is also supported. To have R Services running, a minimum of 1 GB is required, but 4 GB would be recommended...

Choosing the edition

SQL Server is no longer just a database, but has grown into a database platform - an ecosystem - which consists of many additional services (such as SSRS, SSAS, and SSIS) that supports and also extends the capabilities of modern database usage. When installing Machine Learning R Services (in a database), one should think about the ecosystem environment and which additional services would be used along with R Services. If the business need requires advanced R (or Python) integration and analytics, the Enterprise edition is the right one. If only basic R integration is needed, the standard version will cover the needs. Also, think along the lines of other analytical tools if you need analysis services or reporting services, and which developments tools would also be needed for that (for example, MDX on top of OLAP cubes and running R code against the same data...

Configuring the environment and installing R Tools for Visual Studio (RTVS)

Once the installation is completed, there are some post installation processes that need to be executed.

The external script server must be enabled so that stored procedures can call external script. In order to do so, run the following command against your SQL Server instance, where you have installed R Services (in-database):

EXEC sp_configure 'show advanced options',1;
GO
RECONFIGURE;
GO
EXEC sp_configure 'external scripts enabled';
GO  

If you are running this for the first time, it is disabled by default so enabling it is a must; otherwise, running the sp_execute_external_script procedure will not be possible:

EXEC sp_configure 'external scripts enabled', 1;
GO
RECONFIGURE WITH OVERRIDE;
GO  

You can always check whether the run_value of external scripts enabled is set...

Security

After configuring the database and any other additional settings used in your ecosystem, you will want to think about security, in terms of who will have access to run sp_execute_external_script.

You can directly create security settings on the external procedure. In this case, you will need to add database permissions to execute external script to the user.

A simple SQL login will look like this:

USE [master]
GO
CREATE LOGIN [RR1] WITH PASSWORD=N'Read!2$17', DEFAULT_DATABASE=[SQLR], CHECK_EXPIRATION=ON, CHECK_POLICY=ON
GO
ALTER SERVER ROLE [sysadmin] ADD MEMBER [RR1]
GO
CREATE DATABASE SQLR;
GO
    
USE [SQLR]
GO
CREATE USER [RR1] FOR LOGIN [RR1]
GO
USE [SQLR]
GO
ALTER USER [RR1] WITH DEFAULT_SCHEMA=[dbo]
GO

And now, let's start the external procedure:

    
EXECUTE AS USER = 'RR1';
GO
    
EXEC sp_execute_external_script
      @language = N&apos...

Package information

Packages are always saved in the library folder but, depending on your version of R (Open, Client, or Server), SQL Server instance names and paths can be different.

In general, the Client or Server versions will store your libraries on your main drive. For the Client version, the default path is C:\Program Files\Microsoft\R Client\R_SERVER\library. You can see the folder contents in the following screenshot:

Figure 13

In the R Server version, you will find libraries on the path of your default SQL Server instance: C:\Program Files\Microsoft SQL Server\MSSQL14.MSSQLSERVER\R_SERVICES\library. The following are the contents of a Server installation:

Figure 14

Sub-folders represent the name of installed and available packages. To find the default path to your packages, you can execute the following code:

-- Path to libraries on your computer/server
EXECUTE sp_execute_external_script...

Summary

This chapter has covered the installation of Machine Learning Services (in-database), the configuration of the services and how to administrate the services. It has also explored the installation of missing packages and covered security and the resource governor. In the last section, the chapter also gives an explanation of how to use external procedures and the sp_execute_external_script with all arguments. Several examples have been covered through digging into security issues and the installation of missing packages. The installation of missing packages was heavily dependent on the article in SQLServerCentral.

An introduction to machine learning services and using an external procedure will be the foundation for all of the following chapters, which will all heavily rely on a good understanding of configuring and using this procedure.

...