In SQL Server suite, SQL Server Analysis Services (SSAS) supports data mining from version 2000. SSAS includes some of the most popular algorithms with very explanatory visualizations. SSAS data mining is very simple to use. However, the number of algorithms is limited, and the whole statistical analysis is missing in the SQL Server suite. By introducing R in SQL Server, Microsoft made a quantum leap forward in statistics, data mining, and machine learning.
Of course, the R language and engine have their own issues. For example, installing packages directly from code might not be in accordance with the security policies of an enterprise. In addition, most calculations are not scalable. Scalability might not be an issue for statistical and data mining analyses, because you typically work with samples. However, machine learning algorithms can consume huge amounts of data.
With SQL Server 2016 and 2017, you get a highly scalable R engine. Not every function and algorithm is rewritten as a scalable one. Nevertheless, you will probably find the one you need for your analysis of a big dataset. You can store an R data mining or machine learning model in a SQL Server table and use it for predictions on new data. You can even store graphs in a binary column and use it in SQL Server Reporting Services (SSRS) reports. Finally, R support is not limited to SQL Server only. You can use R code also in Power BI Desktop and Power BI Service, and in Azure Machine Learning (Azure ML) experiments.
Installing packages is not that simple, and must be done by a DBA. In SQL Server, you call R code through a stored procedure. This way, a DBA can apply all SQL Server security to R code as well. In addition, you need a SQL Server, a Windows login, or Windows to run the code that uses SQL Server R Machine Learning Services. This login must also have enough permissions on SQL Server objects. It needs to access the database where you run the R code, permissions to read SQL Server data, and potentially, if you need to store the results in a SQL Server table, permissions to write data.
This section introduces R support in SQL Server, including:
- Architecture
- Using R code in T-SQL
- Scalable solutions
- Security
- Deploying R models in SQL Server