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, use remote and local compute contexts, and 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 solution can run on Microsoft R Server or HDI Spark clusters.

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 R algorithms to build a logistic regression and decision trees model.

    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 SQL logins and Windows user accounts that will be running R or Python scripts. All need permission to run R or Python scripts, and to connect to the instance. Depending on the sample, they might also need permissions to read and write data, and to create database objects.

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.