Overview and Installation Guide of SQL Server R Services

Naveen Sharma | Modified: July 4th, 2016 | SQL Server 2016 |

SQL Server R Services offers a platform in developing as well as organizing applications, which uncovers new insights. It supports open source R language along with the complete set of SQL Server tools and technologies that provides security, reliability, performance, and manageability. Users can organize R solutions by utilizing familiar and convenient tools. Users can call the R runtime and regain predictions as well as visual by utilizing Transact-SQL. They can get ScaleR libraries for the improvement of scale and performance of R solutions. It offers both Server and client components in SQL Server 2016 as mentioned below:

  • R Services
  • Microsoft R Client
  • Microsoft R

In the following section, a guide through set-up and configuration of server components will be discussed.

Approach to Install SQL Server R Services

Several steps are involved in installing SQL Server R Services as mentioned below. It will make easy for users to have systematic guide.

  • Advanced Analytics Extension
  • The components of database engine are known as Advanced Analytics Extensions. Through SQL Server set-up, user can choose the Advance Analytics Extension option from the instance features. There is one of the prerequisites to install .NET 3.5 SP1 framework. Otherwise, user will get a warning message that after installation of extensions, user needs to perform some post installation steps. This service will run under MSSQLLaunchpad service and it will have its own service account. This service is not registered in SQL Server Configuration Manager. At the time of installation of SQL Server, user should be sure to configure mixed mode authentication, as there are some features of R services that are involved in SQL Server authentication.

  • R Packages and Providers
  • In this, installation of software of Revolution Analytics that is offered by Microsoft is required. This application installs an enhanced distribution of R that includes the connectivity to interact with Revolution R enterprise software as mentioned.

    • Launch Revolution R Open 3.2.2
    • User can download the software and run as an Administrator on the system. Its installation is direct, as no extra explanations are required.

    • Run Revolution Enterprise 7.5
    • User can install the tool on their system. It is a platform for scaling R codes, which includes various enhanced R packages and functions. While downloading, the user can select between the server and client software. There should be fully developed environment for R on the server and the node software should be sufficient. After downloading the software, user can run it on their system as an administrator and check for the prerequisites. If all the prerequisites are installed on the system then, Revolution R enterprise set-up will automatically launch. At the time of set-up, user can optionally install R packages, i.e.

      • rpart: It is for the recursive partition for regression, classification, and survival tress.
      • lattice: It is used for data visualization that are inspired on Trellis graphics.

      After selecting the installation of the folder, the wizard is completed. Though, there will simple IDE installed on the server, known as Connector RGUI 7.5.0 64-bit. By utilizing this, user can easily test, write, or debug the R scripts.

  • Final Installation for Server Configuration
  • The final step is to execute some scripts to complete the R services set-up. Firstly, user needs to enable the external scripts in SQL Server, which makes easy to run R scripts. It can be done by the following scripts to set the correct option for server.

    Exec sp_configure ‘external scripts enable’, 1;
    Reconfigure;

    After that, user can run the script on elevated command prompt and it affects various things as mentioned:

    • Ends the SQL Server Services
    • To run R scripts in SQL bin path, it copies the required code
    • It creates a bunch of Windows user’s account that is of low privilege for running an individual session of R runtime. It creates 20 accounts by default
    • In the master database, it creates a database role
    • Finally, it restarts the SQL Server Services

Conclusion

In the above discussion, a proper guidance is given through the server-side set-up of SQL Server R Services. According to experts, if the users follow the guided steps properly, then they can execute R scripts on the server easily.