Events
Mar 31, 11 PM - Apr 2, 11 PM
The biggest SQL, Fabric and Power BI learning event. March 31 – April 2. Use code FABINSIDER to save $400.
Register todayInstall pretrained 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 pretrained machine learning models for sentiment analysis and image featurization to a SQL Server instance having R or Python integration. The pretrained 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 runtimes and packages. For more information, see Install SQL Server 2022 Machine Learning Services (Python and R) on Windows.
Once installed, the pretrained 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 of 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 pretrained 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.
MicrosoftML R package or microsoftml Python package contain the pretrained models.
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 pretrained models to these libraries.
MicrosoftML R package contain the pretrained 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 pretrained models, which makes running the PowerShell script unnecessary. However, if you already upgraded but missed adding the pretrained 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.
The install paths for R and Python models are as follows:
For R:
C:\Program Files\Microsoft SQL Server\MSSQL14.MSSQLSERVER\R_SERVICES\library\MicrosoftML\mxLibs\x64For Python:
C:\Program Files\Microsoft SQL Server\MSSQL14.MSSQLSERVER\PYTHON_SERVICES\Lib\site-packages\microsoftml\mxLibs
Model file names are in the following list:
AlexNet_Updated.modelImageNet1K_mean.xmlpretrained.modelResNet_101_Updated.modelResNet_18_Updated.modelResNet_50_Updated.model
If the models are already installed, skip ahead to the validation step to confirm availability.
Visit https://aka.ms/mlm4sql to download the file Install-MLModels.ps1. On the GitHub page, select Download raw file.
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:
PowerShellSet-ExecutionPolicy -ExecutionPolicy RemoteSigned -Scope CurrentUserEnter 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:
PowerShellPS C:\WINDOWS\system32> C:\Users\<user-name>\Downloads\Install-MLModels.ps1 MSSQLSERVER
Output
On an internet-connected SQL Server Machine Learning Services default instance with R and Python, you should see messages similar to the following.
PowerShell
MSSQL14.MSSQLSERVER
Verifying R models [9.2.0.24]
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 [9.2.0.24]
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.
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.
R# 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 sentimentScoresPress Enter to view the sentiment scores. Output should be as follows:
R> 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
Start Python.exe at
C:\Program Files\Microsoft SQL Server\MSSQL14.MSSQLSERVER\PYTHON_SERVICES.Paste in the following Python script at the command prompt.
Pythonimport 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:
Python>>> 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 >>>
Note
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.
The following link includes example code invoking the pretrained models.
Currently the models that are available are deep neural network (DNN) models for sentiment analysis and image classification. All pretrained models were trained by using Microsoft's Computation Network Toolkit (CNTK).
The configuration of each network was based on the following reference implementations:
ResNet-18ResNet-50ResNet-101AlexNet
For more information about the algorithms used in these deep learning models, and how they are implemented and trained using CNTK, see these articles:
Additional resources
Training
Learning path
Create machine learning models - Training
Machine learning is the foundation for predictive modeling and artificial intelligence. Learn some of the core principles of machine learning and how to use common tools and frameworks to train, evaluate, and use machine learning models.
Certification
Microsoft Certified: Azure Data Scientist Associate - Certifications
Manage data ingestion and preparation, model training and deployment, and machine learning solution monitoring with Python, Azure Machine Learning and MLflow.