Install pre-trained machine learning models on SQL Server
Applies to: SQL Server 2016 (13.x), SQL Server 2017 (14.x), and SQL Server 2019 (15.x)
This article applies to SQL Server 2016 (13.x), SQL Server 2017 (14.x), and SQL Server 2019 (15.x).
This article explains how to use PowerShell to add free pre-trained machine learning models for sentiment analysis and image featurization to a SQL Server instance having R or Python integration. The pre-trained models are built by Microsoft and ready-to-use, added to an instance as a post-install task. For more information about these models, see the Resources section of this article.
Beginning with SQL Server 2022 (16.x), runtimes for R, Python, and Java, are no longer installed with SQL Setup. Instead, install your desired R and/or Python custom runtime(s) and packages. For more information, see Install SQL Server 2022 Machine Learning Services (Python and R) on Windows.
Once installed, the pre-trained models are considered an implementation detail that power specific functions in the MicrosoftML (R) and microsoftml (Python) libraries. You should not (and cannot) view, customize, or retrain the models, nor can you treat them as an independent resource in custom code or paired other functions.
To use the pretrained models, call the functions listed in the following table.
|R function (MicrosoftML)||Python function (microsoftml)||Usage|
|getSentiment||get_sentiment||Generates positive-negative sentiment score over text inputs.|
|featurizeImage||featurize_image||Extracts text information from image file inputs.|
Machine learning algorithms are computationally intensive. We recommend 16 GB RAM for low-to-moderate workloads, including completion of the tutorial walkthroughs using all of the sample data.
You must have administrator rights on the computer and SQL Server to add pre-trained models.
External scripts must be enabled and SQL Server LaunchPad service must be running. Installation instructions provide the steps for enabling and verifying these capabilities.
Download and install the latest cumulative update for your version of SQL Server. See the Latest updates for Microsoft SQL Server.
SQL Server Machine Learning Services includes both language versions of the machine learning library, so this prerequisite is met with no further action on your part. Because the libraries are present, you can use the PowerShell script described in this article to add the pre-trained models to these libraries.
MicrosoftML R package contain the pre-trained models.
SQL Server R Services, which is R only, does not include MicrosoftML package out of the box. To add MicrosoftML, you must do a component upgrade. One advantage of the component upgrade is that you can simultaneously add the pre-trained models, which makes running the PowerShell script unnecessary. However, if you already upgraded but missed adding the pre-trained models the first time around, you can run the PowerShell script as described in this article. It works for both versions of SQL Server. Before you do, confirm that the MicrosoftML library exists at
C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\R_SERVICES\library.
Check whether pre-trained models are installed
The install paths for R and Python models are as follows:
C:\Program Files\Microsoft SQL Server\MSSQL14.MSSQLSERVER\R_SERVICES\library\MicrosoftML\mxLibs\x64
C:\Program Files\Microsoft SQL Server\MSSQL14.MSSQLSERVER\PYTHON_SERVICES\Lib\site-packages\microsoftml\mxLibs
Model file names are listed below:
If the models are already installed, skip ahead to the validation step to confirm availability.
Download the installation script
Visit https://aka.ms/mlm4sql to download the file Install-MLModels.ps1.
Execute with elevated privileges
Start PowerShell. On the task bar, right-click the PowerShell program icon and select Run as administrator.
The recommended execution policy during installation is "RemoteSigned". For more information on setting the PowerShell execution policy, see Set-ExecutionPolicy. For example:
Set-ExecutionPolicy -ExecutionPolicy RemoteSigned -Scope CurrentUser
Enter a fully-qualified path to the installation script file and include the instance name. Assuming the Downloads folder and a default instance, the command might look like this:
PS C:\WINDOWS\system32> C:\Users\<user-name>\Downloads\Install-MLModels.ps1 MSSQLSERVER
On an internet-connected SQL Server Machine Learning Services default instance with R and Python, you should see messages similar to the following.
MSSQL14.MSSQLSERVER Verifying R models [22.214.171.124] Downloading R models [C:\Users\<user-name>\AppData\Local\Temp] Installing R models [C:\Program Files\Microsoft SQL Server\MSSQL14.MSSQLSERVER\R_SERVICES\] Verifying Python models [126.96.36.199] Installing Python models [C:\Program Files\Microsoft SQL Server\MSSQL14.MSSQLSERVER\PYTHON_SERVICES\] PS C:\WINDOWS\system32>
First, check for the new files in the mxlibs folder. Next, run demo code to confirm the models are installed and functional.
R verification steps
Start RGUI.EXE at C:\Program Files\Microsoft SQL Server\MSSQL14.MSSQLSERVER\R_SERVICES\bin\x64.
Paste in the following R script at the command prompt.
# Create the data CustomerReviews <- data.frame(Review = c( "I really did not like the taste of it", "It was surprisingly quite good!", "I will never ever ever go to that place again!!"), stringsAsFactors = FALSE) # Get the sentiment scores sentimentScores <- rxFeaturize(data = CustomerReviews, mlTransforms = getSentiment(vars = list(SentimentScore = "Review"))) # Let's translate the score to something more meaningful sentimentScores$PredictedRating <- ifelse(sentimentScores$SentimentScore > 0.6, "AWESOMENESS", "BLAH") # Let's look at the results sentimentScores
Press Enter to view the sentiment scores. Output should be as follows:
> sentimentScores Review SentimentScore 1 I really did not like the taste of it 0.4617899 2 It was surprisingly quite good! 0.9601924 3 I will never ever ever go to that place again!! 0.3103435 PredictedRating 1 BLAH 2 AWESOMENESS 3 BLAH
Python verification steps
Start Python.exe at C:\Program Files\Microsoft SQL Server\MSSQL14.MSSQLSERVER\PYTHON_SERVICES.
Paste in the following Python script at the command prompt
import numpy import pandas from microsoftml import rx_logistic_regression, rx_featurize, rx_predict, get_sentiment # Create the data customer_reviews = pandas.DataFrame(data=dict(review=[ "I really did not like the taste of it", "It was surprisingly quite good!", "I will never ever ever go to that place again!!"])) # Get the sentiment scores sentiment_scores = rx_featurize( data=customer_reviews, ml_transforms=[get_sentiment(cols=dict(scores="review"))]) # Let's translate the score to something more meaningful sentiment_scores["eval"] = sentiment_scores.scores.apply( lambda score: "AWESOMENESS" if score > 0.6 else "BLAH") print(sentiment_scores)
Press Enter to print the scores. Output should be as follows:
>>> print(sentiment_scores) review scores eval 0 I really did not like the taste of it 0.461790 BLAH 1 It was surprisingly quite good! 0.960192 AWESOMENESS 2 I will never ever ever go to that place again!! 0.310344 BLAH >>>
If demo scripts fail, check the file location first. On systems having multiple instances of SQL Server, or for instances that run side-by-side with standalone versions, it's possible for the installation script to mis-read the environment and place the files in the wrong location. Usually, manually copying the files to the correct mxlib folder fixes the problem.
Examples using pre-trained models
The following link include example code invoking the pretrained models.
Research and resources
Currently the models that are available are deep neural network (DNN) models for sentiment analysis and image classification. All pre-trained models were trained by using Microsoft's Computation Network Toolkit, or CNTK.
The configuration of each network was based on the following reference implementations:
For more information about the algorithms used in these deep learning models, and how they are implemented and trained using CNTK, see these articles: