Running MicrosoftML in SQL Server 2016

  • Article

MicrosoftML is the new and exciting ML packages added to Microsoft R Server 9.0.1 which offers increased speed, performance and scalability, especially for scenarios like deep neural network with GPU acceleration and highly-dimensional categorical data.

There has been some confusions on where to download MicrosoftML to get your hands on algorithms like rxFastLiner, rxFastTress and rxNeuralNet, especially when you plan to use MicrosoftML in SQL Server compute context. In this article, I will outline how can you download MicrosoftML and use MicrosoftML in SQL Server 2016.

If you installed Microsoft SQL Server VNext

You have the latest R Server bits that enable you to run MicrosoftML packages, you can start right away developing model in MicrosoftML. Just load MicrosoftML library in SSMS using the following script:-

If you installed Microsoft SQL Server 2016 RTM or SQL Server 2016 SP1

MicrosoftML shipped in R Server version 3.3.2. If you have installed and setup SQL Server 2016 RTM or SQL Server 2016 SP1, the R version shipped is version 3.2.2. You can run a simple sessionInfo script to verify in SQL Server Management Studio (SSMS).

In my machine that had SQL Server RTM installed, you can see that the R version is 3.2.2 which does not have the MicrosoftML package shipped with it.

sqlbindr8

Binding R Component

In order to upgrade R Services to run MicrosoftML, we need to use a tool called SQLBindR.exe to upgrade the R components associated with an instance of R services. This process is called binding. Binding allows you to upgrade the R instance to receive the most recent updates in Microsoft R Server packages. This is especially critical to a data scientist given R is progressing much faster and having more frequent updates in R packages. However, before explaining where to download SQLBindR and run SQLBindR, we need to understand the implication of binding from support policy perspective.

SQL Server supports 5 year mainstream and 5 year extended support. R Server and SQL Server R services adhere to the same SQL Server support policy. If you decide to receive most recent updates in R components, the support policy for R Server and R Services change from 5+5 to Modern Lifecyle Policy in which customer must stay current in the R Server product version to receive servicing updates.

For more details on binding, unbinding, and support policy implications, please refer to the official documentation on MSDN.

How to 'Bind' a R instance

Article Use sqlBindR.exe to Upgrade an Instance of R Services outlined detailed step-by-step instruction on how to upgrade R Service instance.

  1. Please note that you must have SQL Server SP1 GDR OR
    SQL Server SP1 CU2 or later OR
    SQL Server RTM and CU3 or later installed in the server.
  2. Download Microsoft R Server 9.0.1 and run RServerSetup.exe to receive SQLBindR.exe. You can download Microsoft R Server via Visual Studio Dev Essentials, Volume Licensing Service Center (VLSC), and MSDN subscription downloads.
  3. Follow steps outlined in 'How to upgrade an instance' section in the article

Refer to the known issues for SQL Server R Services for SqlBindR.exe tool if you are running into issues.

To verify if you have successfully upgraded R component to receive MicrosoftML package, simply run the same R.Version() script, you should see R version 3.3.2.

sqlbindr7

How to 'Unbind' a R instance

If you later decide not to receive further upgrade, you can unbind R instance to fall back to SQL Server support policy.

You can run the following:-

  1. SQLBindR.exe /unbind MSSQL14.MSSQLSERVER
  2. Restore the instance to its original status by doing one of the following:
    • Repair the instance. The repair operation will apply updates to all installed features.
    • Uninstall and reinstall, and then apply all service releases. The instance must be restarted.
  3. After R Server has been removed, any packages that were installed with the instance will also be removed, and therefore must be reinstalled.

Running Binding in an Offline Installation Offline installation instructions for R Server for Windows outlined very detailed steps in installing Microsoft R Server in offline environment.

Please note: you need to copy SRO_3.3.2.0_1033.cab in two locations in your system - 1. MRS90Windows which contains RServerSetup.exe. 2. Temp folder which is \Users<account-name>\AppData\Local\Temp. You may run into setup error if you do not copy to the %temp% folder.

A few good examples using different MicrosoftML algorithms:- Image Segmentation Using MicrosoftML Getting started with GPU acceleration for MicrosoftML’s rxNeuralNet Classify Yelp restaurant reviews’ food origin with MicrosoftML Predicting NYC Taxi Tips using MicrosoftML