Using SQL Server 2016 R Services vs. Standalone Microsoft R Server

rsql-server-2016

I've been experimenting with SQL Server 2016 and its R Services. I also have a standalone Microsoft R Server installed on my machine.

What is a good use-case to use SQL Server R Services over a standalone Microsoft R Server, since both feature properties of an enterprise scale R platform.

Best Answer

Possible a slightly subjective question but sometimes it's helpful to think of these things in scenarios, for example:

A predominantly SQL Server project, run by DBAs/devs (who may or may not know some R). A data scientist or R professional writes some R for them or they do it themselves and test on their local machines, eg using R Studio or the Microsoft R IDE. When they are happy with the R, they plug it into their data workflow using sp_execute_external_script. They can easily incorporate this into other stored procedures, SSIS etc. This might be a good candidate for SQL Server 2016 with R Services.
A secure project, the data is in your secured SQL Server and you want to keep the data close to the analytics and not use the cloud. This might be a good candidate for an on-premise installation of SQL Server 2016 with R Services.
A predominantly data science project which requires data but does not require SQL Server access. This might be a good candidate for Microsoft R Server only.
High performance is required for a big data project. You do not want to share the box with the traditionally memory-hungry SQL Server. This might be a good candidate for Microsoft R Server only.

Just a few examples there. What examples can you think of?

HTH

Related Solutions

Sql-server – Can’t uninstall Microsoft SQL Server 2016 RC3

Thanks to Aaron Bertrand, I was able to resolve the issue following the manual steps outlined in his blog:

Fun with software : uninstalling SQL Server 2008 R2 Evaluation Edition

...in spite of the title, it's not just for 2008 R2 or Evaluation Edition.

In case the link ever stops working, the essential steps are:

Locate SQL Server related GUIDs under:

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Windows\CurrentVersion\Uninstall\

Also look for GUIDs in folders under Uninstall called e.g. "Microsoft SQL Server 2008 R2"
Run for each item:
```
msiexec /x "{guid}"
```

SQL Server 2016 R Services – How to Connect to Hadoop Data

The fastest way to import data into SQL Data Warehouse is to use PolyBase...

https://azure.microsoft.com/en-gb/documentation/articles/sql-data-warehouse-overview-load/

I do not think the same recommendation exists for SQL Server 2016, but it still makes sense. The two options are slightly different in that Polybase allows you to access Hadoop or Azure Blob storage directly and has been optimised for scale, eg PolyBase scale-out groups. You write ordinary T-SQL to access these external tables which is one of the main advantages - no Map Reduce jobs for you! Using R in SQL Server 2016, you would have to use stored procedure sp_execute_external_script to call these libraries, create the connection, and import the data into a dataframe. You would then have to expose the dataframe back to SQL Server as an output resultset, then write it to a table, something like ...

--NB This is pseduo-code / will not execute!

INSERT INTO dbo.someTable ( ... 
EXEC dbo.sp_execute_external_script
    @language = N'R', 
    @script = N'
library(RxHDFSConnect)
OutputDataSet <- some code that allows RxHDFSConnect to get some data ...'
WITH RESULT SETS ( ( col1 INT NOT NULL, col2 FLOAT NOT NULL ... etc ) );

Just in terms of number of steps it feels more complicated. I haven't done any scale testing on this but instinct tells me this is not meant for massive volumes. Interestingly I could only find this page on importing data with RevoScaelR in googlecache.

What kind of use cases are you looking out, just out of interest?

Best Answer

Related Solutions

Sql-server – Can’t uninstall Microsoft SQL Server 2016 RC3

SQL Server 2016 R Services – How to Connect to Hadoop Data

Related Question