What is SQL Server Machine Learning Services?
SQL Server Machine Learning Services is an embedded, predictive analytics and data science engine that can execute R and Python code within a SQL Server database as stored procedures, as T-SQL script containing R or Python statements, or as R or Python code containing T-SQL.
The key value proposition of Machine Learning Services is the power of its proprietary packages to deliver advanced analytics at scale, and the ability to bring calculations and processing to where the data resides, eliminating the need to pull data across the network.
There are two options for using machine learning capabilities in SQL Server:
- SQL Server Machine Learning Services (In-Database) operates within the database engine instance, where the calculation engine is fully integrated with the database engine. Most installations are this option.
- SQL Server Machine Learning Server (Standalone) is Machine Learning Server for Windows that runs independently of the database engine. Although you use SQL Server Setup to install the server, the feature is not instance-aware. Functionally, it is equivalent to the non-SQL-Server Microsoft Machine Learning Server for Windows.
R and Python packages
Support for each language is through proprietary Microsoft packages used for creating and training models of various types, scoring data, and parallel processing using the underlying system resources.
Because the proprietary packages are built on open-source R and Python distributions, script or code that you run in SQL Server can also call base functions and use third-party packages compatible with the language version provided in SQL Server (Python 3.5 and recent versions of R, currently 3.3.3).
|RevoScaleR||revoscalepy||Functions in these libraries are among the most widely used. Data transformations and manipulation, statistical summarization, visualization, and many forms of modeling and analyses are found in these libraries. Additionally, functions in these libraries automatically distribute workloads across available cores for parallel processing, with the ability to work on chunks of data that are coordinated and managed by the calculation engine.|
|MicrosoftML||microsoftml||Industry-leading machine learning algorithms for image featurization, classification problems, and more.|
|olapR||none||Build or execute an MDX query in R script.|
|sqlRUtils||none||Functions for putting R scripts into a T-SQL stored procedure, registering a stored procedure with a database, and running the stored procedure from an R development environment.|
|mrsdeploy||none||Primarily used on a non-SQL installation of Machine Learning Server, such as the (Standalone) version. Use this package to deploy and host web services, build scale-out topologies with dedicated web and compute nodes, toggle between local and remote sessions, run diagnostics, and more. For an (In-Database) installation, use this package in a client capacity: for example, to access a web service on a remote server dedicated to running just Machine Learning Services workloads.|
Portability of your custom R and Python code is addressed through package distribution and interpreters that are built into multiple products. The same packages that ship in SQL Server are also available in several other Microsoft products and services, including a non-SQL version called Microsoft Machine Learning Server. Free clients that include our R and Python interpreters include Microsoft R Client and the Python libraries.
Developers and analysts often have code running on top of a local SQL Server instance. If you have SQL Server and an IDE such as Visual Studio with R or Visual Studio with Python on the same computer, you can build, train, and test models locally in either language. Local access simplifies package management: as an admin, you can load additional third-party packages using built-in permissions for that role.
From any client workstation that has an IDE, install Microsoft R Client or the Python libraries, and then write code that pushes execution (referred to as a remote compute context) to data and operations to a remote SQL Server.
Similarly, if you are using the Developer edition, you can build solutions on a client workstation using the same libraries and interpreters, and then deploy production code on SQL Server Machine Learning Services (In-Database).
SQL Server 2017 Machine Learning Services is the next generation of SQL Server 2016 R Services, enhanced to include Python. The following table is a complete list of all versions, from inception to the current release.
|Product name||Engine version||Release date|
|SQL Server 2017 Machine Learning Services (In-Database)||R Server 9.2.1
Python Server 9.2
|SQL Server 2017 Machine Learning Server (Standalone)||R Server 9.2.1
Python Server 9.2
|SQL Server 2016 R Services (In-Database)||R Server 9.1||July 2017|
|SQL Server 2016 R Server (Standalone)||R Server 9.1||July 2017|
Documentation for each version
Recent releases of SQL Server documentation are version-agnostic. For SQL Server Machine Learning Services, Python is only available in 2017 and later, while R support is in all versions. Unless noted otherwise, you can assume R documentation applies to both 2016 and 2017 versions.
Related machine learning products
The Azure marketplace includes multiple virtual machine images that include Machine Learning Server or R Server. Creating a virtual machine in Microsoft Azure is the fastest way to get to development and deployment of predictive models. Images come with features for scaling and sharing already configured, which makes it easier to embed analytics inside applications and to integrate with backend systems.
The latest version of the Data Science Virtual machine includes Machine Learning Server, SQL Server, plus an array of the most popular tools for machine learning, all preinstalled and tested. Create Jupyter notebooks, develop solutions in Julia, and use GPU-enabled deep learning libraries like MXNet, CNTK, and TensorFlow.
Step 1: Install and configure the software.
Step 2: Get started with code using either one of these tutorials:
Step 3: Add your favorite R and Python packages and use them together with packages provided by Microsoft