Python Setup


The algorithm for correlated item and similar item is ran with Python. To be able to run Python in SSMS you need to add the feature Machine Learning Services to SQL Server.
It is only available for SQL Server 2017 (14.x) or later.


Install SQL Server Machine Learning Services

Following are detailed instructions how to install SQL Server Machine Learning Services. These instructions are taken from instructions from Microsoft, so if you run into problems you can check out the instructions from Microsoft here. Also it is possible to follow this video.


1. Get the installation media / setup wizard
Visit this site: https://www.microsoft.com/en-US/sql-server/sql-server-downloads
Choose “Download now” under Developer.

Download installation media


2. Start the setup wizard that you just downloaded
Under installation, choose “New SQL Server stand-alone installation or add features to an existing installation”

Start installation media


3. Press “Next” until you get to the step “Installation Type” (see image) Here we want to choose “Add features to an existing instance”, and then press Next. (SQL Server should have already been installed to the computer, so we want to add the ML Services to that instance)

Installation Type


4. On the “Feature Selection” page - select these options:

  • Database Engine Services - To use R or Python with SQL Server, you must install an instance of the database engine. You can use either a default or a named instance.

  • Machine Learning Services (In-Database) - This option installs the database services that support R and Python script execution.

  • Python - Check this option to add the Microsoft Python packages, the Python 3.5 executable, and select libraries from the Anaconda distribution.

Feature selection


5. Finish the installation process by going through the rest of the steps in the setup wizard.


Enable script execution

1. Open SQL Server Management Studio (SSMS)

2. Open new query window (new query) and run the following command:

sp_configure

The value for the property, external scripts enabled, should be 0 at this point. The feature is turned off by default. The feature must be explicitly enabled by an administrator before you can run Python scripts.


3. Enable the external scripts feature
Run the following:

EXEC sp_configure  'external scripts enabled', 1
RECONFIGURE WITH OVERRIDE

Restart the service

It is possible to restart the database engine in three ways:

  1. Right-click Restart command for the instance in SSMS
  2. Use Services panel in Control Panel
  3. Use SQL Server Configuration Manager

Verify installation

1. Go to SSMS and run the following:

EXECUTE sp_configure 'external scripts enabled'

run_value should be set to 1.


2. Open the ‘Service’ panel or SQL Server Configuration Manager
Verify SQL Server Launchpad service is running. You should have one service for every database engine instance that has Python installed


3. Run python code
If Launchpad is running, you can run simple Python scripts to verify that external scripting runtimes can communicate with SQL Server. Run the following command, and if you get the outcome as the image below you are all set to run correlated and similar items using Python.

EXEC sp_execute_external_script @language =N'Python',
@script=N'
print("Hello World")

Python execution code



Python package install for Similar Item

Similar items depend on two python packages which need to be installed. The packages are stop-words and bidict, and can be installed using pip-install. Microsoft provides instruction on how to install python packages.
Using SQL Server 2017 you can follow these instructions.
Using SQL Server 2019 you can follow these instructions.


Brief overview of the steps on how to pip install:

1. Open command prompt/console and locate to the default Python library location used by SQL Server
The path can look similar to this:

cd "C:\Program Files\Microsoft SQL Server\MSSQL14.MSSQLSERVER\PYTHON_SERVICES"

2. Install the packages using pip installer
Run these commands in the console:

scripts\pip.exe install stop-words
scripts\pip.exe install bidict

3. Open SSMS to verify whether the packages were installed
Run the following code in SSMS. If no error occurs the packages have been successfully installed.

EXEC sp_execute_external_script 
@language =N'Python',
@script=N'
from stop_words import get_stop_words
from bidict import bidict
'