Edit

Machine Learning Services in Azure SQL Managed Instance

  • Article

Machine Learning Services is a feature of Azure SQL Managed Instance that provides in-database machine learning, supporting both Python and R scripts. The feature includes Microsoft Python and R packages for high-performance predictive analytics and machine learning. The relational data can be used in scripts through stored procedures, T-SQL script containing Python or R statements, or Python or R code containing T-SQL.

What is Machine Learning Services?

Machine Learning Services in Azure SQL Managed Instance lets you execute Python and R scripts in-database. You can use it to prepare and clean data, do feature engineering, and train, evaluate, and deploy machine learning models within a database. The feature runs your scripts where the data resides and eliminates transfer of the data across the network to another server.

Use Machine Learning Services with R/Python support in Azure SQL Managed Instance to:

  • Run R and Python scripts to do data preparation and general purpose data processing - You can now bring your R/Python scripts to Azure SQL Managed Instance where your data lives, instead of having to move data out to some other server to run R and Python scripts. You can eliminate the need for data movement and associated problems related to latency, security, and compliance.

  • Train machine learning models in database - You can train models using any open source algorithms. You can easily scale your training to the entire dataset rather than relying on sample datasets pulled out of the database.

  • Deploy your models and scripts into production in stored procedures - The scripts and trained models can be operationalized simply by embedding them in T-SQL stored procedures. Apps connecting to Azure SQL Managed Instance can benefit from predictions and intelligence in these models by just calling a stored procedure. You can also use the native T-SQL PREDICT function to operationalize models for fast scoring in highly concurrent real-time scoring scenarios.

Base distributions of Python and R are included in Machine Learning Services. You can install and use open-source packages and frameworks, such as PyTorch, TensorFlow, and scikit-learn, in addition to the Microsoft packages revoscalepy and microsoftml for Python, and RevoScaleR, MicrosoftML, olapR, and sqlrutils for R.

How to enable Machine Learning Services

You can enable Machine Learning Services in Azure SQL Managed Instance by enabling extensibility with the following SQL commands (SQL Managed Instance will restart and be unavailable for a few seconds):

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

For details on how this command affects SQL Managed Instance resources, see Resource Governance.

Enable Machine Learning Services in a failover group

In a failover group, system databases are not replicated to the secondary instance (see Limitations of failover groups for more information).

If the SQL Managed Instance you're using is part of a failover group, do the following:

  • Run the sp_configure and RECONFIGURE commands on each instance of the failover group to enable Machine Learning Services.

  • Install the R/Python libraries on a user database rather than the master database.

Next steps