SQL Server Machine Learning Using Python – Step by Step

By | June 3, 2020

In this series of post we are going to cover all the steps required to be able to perform Machine learning on Microsoft SQL Server. We will start off with installing and configuring Machine learning on MS SQL Server. Followed by adding packages to SQL Server using sqlmlutils and finally using data from SQL Server table as input to a machine learning model. This series on going to focus on using Python as it is by far the most popular language for these kind of tasks and fairly easy for beginners to understand.

The series will not be focusing on theory and explaining how the different models work but sufficient context will be provided where needed.

We highly recommend not using SQL Server machine learning service on a production environment while following this series. You are also encouraged to get familiar with Python Basics such as syntax as it will help understand the code better.

With that said let’s begin with the first step.

Installing Machine learning on an SQL instance

We are going to add the services to an existing instance of SQL Server. For details on how to perform a fresh installation of the database engine click here.

Start the installation and in the feature selection page select machine learning services followed by the checkbox for python.

Press next and leave the service accounts screen on defaults for now.

On the consent to install Python click Accept and Next

Process till you reach Finish

The final screen

Enabling External Scripts

Once the features are installed we need to enable them in the server settings using sp_configure

sp_configure 'external scripts enabled', 1


Once run the screen should show

Testing the installation

Once enabled you can begin running python code in the SQL Server instance. Some basic module comes preinstalled with Python so you can run the below script to get output similar to the screen below

EXECUTE sp_execute_external_script @language = N'Python'
    , @script = N'OutputDataSet = InputDataSet'
    , @input_data_1 = N'SELECT 1 AS hello'
WITH RESULT SETS(([Hello World] INT));


More Info

More info on the execute procedure which we will cover next is available here.