By: Siddharth Mehta
Overview
SQL Server 2017 is in CTP as of the writing of this tutorial. We can use CTP 2 or 2.1 to install and configure Machine Learning Server as well as Machine Learning Services (In-Database). We will need SQL Server 2017 which can be downloaded from here.
Installing SQL Server 2017 CTP2 with Machine Learning Services
The first step in the installation wizard will check whether the setup is up-to-date or whether new updates are available to download and install.
The next step will install setup files and check installation rules. You may get a warning for the firewall if your Windows firewall is turned on.
Assuming that SQL Server 2017 has never been installed, select new installation as shown below.
You may provide a license key if you have one or else you can just use the evaluation edition.
Read and accept the license terms and proceed to the next step.
Select In-Database Machine Learning Services under the Database Engine Services and Machines Learning Server with R under Shared Features as shown below. We need an R server as well as extensions to communicate with this server from the database engine using T-SQL.
Provide the instance name for the SQL Server being installed. You can make it the default if it’s the first time you are installing SQL Server on your machine. Alternatively you can also install it as a named instance.
Configure the services account settings. In the case where you do not wish to make any changes, you can proceed with the default options.
Select Windows Authentication as the authentication mode for the Database Engine. You also need to assign an administrator of the server. If you will be the admin of the server, you can just click on Add Current User, assuming that you are installing SQL Server.
Now you are required to provide your consent to download and install Microsoft R Open. Click on the Accept button and proceed to the next step.
Finally you are ready to install SQL Server with Machine Learning Services as well as Microsoft R Open server. Click on Install button and this should complete the installation process.
After you complete SQL Server 2017 CTP2 installation, install SQL Server Management Studio from the installation wizard as we will be using this to execute T-SQL queries as well as R scripts.
Installing Visual Studio 2017 with Data science and analytical applications features
Microsoft has been using Visual Studio shell for development tools and environments like SSMS and SSDT. For developing data science solutions, Microsoft has made provisions in Visual Studio 2017 by adding a category of solution development named “Data science and analytical applications”. For developing R solutions and working directly with R server, these feature can be extremely useful.
You can download Visual Studio 2017 Community Edition from here, provided you are just using it for learning purposes. After downloading the web installer, on the feature selection page you can select the R related features as shown below. This will install components in VS2017 that provide a development environment for R solutions.
After you have successfully installed Visual Studio 2017 with data science features, you can open the it and create a new R project. Once the blank R project opens, you will be able to see the R development environment as shown below.
The Solution Explorer window shows the different files in the solution. The script window in the top left section is where a developer would develop R scripts. The bottom left section shows the interactive window which can be considered as the R command prompt. You will get intellisense as you type R commands as shown below. You can also choose whether you want the environment to be connected as an R server or R client as shown in the bottom right section.
Our focus is working with R scripts from T-SQL, so we will limit our discussion of developing R solutions using Visual Studio 2017.
Explore and test execution of R Scripts
We have completed installation of all the tools related to R development in SQL Server. Now it’s time to test whether we are able to execute R scripts from T-SQL using SSMS. By default, external scripts are not enabled in the database engine. You need to enable the external scripts option using a simple command as shown in this article. You may have to restart the server for the changes to take effect. After the external scripts execution is enabled, we can use sp_execute_external_script to execute R scripts as shown below. Execute the below script using SSMS.
--R Version execute sp_execute_external_script @language = N'R', @script = N' print(R.version) '
It is the same command we used in Visual Studio and the same output is rendered as well. We will look at the usage of this stored procedure to execute external scripts in upcoming chapters. For now, we have successfully verified the integration of Microsoft R Open server with the SQL Server Database Engine as well as Visual Studio 2017.
Now that we have completed a successful installation, configuration and acceptance testing of components for executing R scripts from SQL Server, let us learn some basic concepts of R programming in the next lesson.
Additional Information
Consider exploring R related options and features in Visual Studio environment after you open an R project.
