SQL Server machine learning tutorials

This article provides a comprehensive list of the tutorials, demos, and sample applications that use machine learning features in SQL Server 2016 or SQL Server 2017. Start here to learn how to run R or Python from T-SQL, how to use remote and local compute contexts, and how to optimize your R and Python code for a SQL production environment.

Start here

For more information about requirements and how to get set up, see Prerequisites.

Samples and solutions

  • Samples

    These real-world scenarios from the SQL Server development team demonstrate how to embed machine learning in applications. All samples include code that you can download, modify, and use in production.

  • Solutions

    Templates from the Microsoft Data Science team are customizable, to get you started fast with machine learning. Each solution is tailored to a specific task or industry problem; moreover, most solutions are designed to run either in SQL Server or in a cloud environment such as Azure Machine Learning. Other solutions can run on Linux or in Spark or Hadoop clusters, by using Microsoft R Server or Machine Learning Server.

SQL Server product samples

These samples and demos provided by the SQL Server and R Server development team highlight ways that you can use embedded analytics in real-world applications.

  • Perform customer clustering using R and SQL Server

    Use unsupervised learning to segment customers based on sales data. This example uses the scalable rxKmeans algorithm from Microsoft R to build the clustering model.

    Applies to: SQL Server 2016 or SQL Server 2017

  • NEW! Perform customer clustering using Python and SQL Server

    Learn how to use the Kmeans algorithm to perform unsupervised clustering of customers. This example uses the Python language in-database.

    Applies to: SQL Server 2017

  • Build a predictive model using R and SQL Server

    Learn how a ski rental business might use machine learning to predict future rentals, which helps the business plan and staff to meet future demand. This example uses the Microsoft algorithms to build logistic regression and decision trees models.

    Applies to: SQL Server 2016 or SQL Server 2017

  • Build a predictive model using Python and SQL Server

    Build the ski rental analysis application using Python, to help plan for future demand. This example uses the new Python library, revoscalepy, to create a linear regression model.

    Applies to: SQL Server 2017

Solution templates

The Microsoft Data Science Team has provided solution templates that can be used to jump-start solutions for common scenarios. All code is provided, along with instructions on how to train and deploy a model for scoring using SQL Server stored procedures.

For more information, see Machine Learning Templates with SQL Server 2016 R Services.

More resources and reading

  • Why did we build it?

    Want to know the real story behind R Services? Read this article from the development and PM team that explains the origin and goals of SQL Server R Services.

  • Tutorials and sample data for Microsoft R

    Learn about Microsoft R, and what the RevoScaleR package offers in this collection of quick tutorials. Learn how to write R code once and deploy anywhere, using RevoScaleR data sources and remote compute contexts.

  • Get started with MicrosoftML

    Learn how to use the new algorithms in the MicrosoftML package for advanced modeling and scalable data transformations, optimized for multiple compute contexts.

Prerequisites

To run these tutorials, you must download and install the SQL Server machine learning components, as described here:

With SQL Server 2017, you can install either R or Python, or both. Otherwise the overall setup process, architecture, and requirements are the same.

After running SQL Server setup, don't forget these important steps:

  1. Enable the external script execution feature by running sp_configure 'external scripts enabled', 1. Follow the instructions to reconfigure and restart SQL Server.
  2. Ensure that the Launchpad service is running, and that the worker accounts it uses can connect to the SQL Server instance.
  3. Review the permissions associated with the users who must run R or Python scripts. Regardless of whether you use SQL logins or Windows user accounts, the user must have permission to run R or Python scripts, and must be able to connect to the instance. Depending on the tutorial, the user might also require permission to write data, create database objects, or do a bulk import of data.

For details, see this article for some common setup and configuration issues: Troubleshooting Machine Learning Services

Note

You cannot run these tutorials using another open source R or Python tool. Both your development environment and the SQL Server computer with machine learning must have the R or Python libraries provided by Microsoft, which support integration with SQL Server and use of remote compute contexts.