How can use Azure Machine Learning notebooks to connect to SQL Server and Azure SQL databases?

Question

I'm trying to find out how I can use a Microsoft Azure Machine Learning notebook to connect to SQL Server and Azure SQL databases.

I am aware of how to connect to SQL Server databases with regular Jupyter files with the use of ODBC connections. But, it looks like I have to try something different when using Azure Machine Learning notebooks.

Could someone describe the best approach to accomplish this?

Note: I am referring to the new Microsoft Azure Machine Learning service, which is currently in preview mode as of May 2020.

Answer 1

great question -- the answer depends on whether or not your data sources are in Azure.

data in Azure

for Azure based storage (blob, data lake, Azure SQL, Azure Databricks) you're in luck with Azure ML Datasets, and abstraction on top of azureml-dataprep, a component package of azureml-sdk. IMHO, Azure ML Datasets are slick, TabularDatasets in particular with their to_pandas_dataframe() and .to_spark_dataframe() methods.

Check out the following articles for guidance on how to:

• How to connect to data and register as a Dataset
• How to access data during training Follow the recommendations in this tutorial recommendation would be to make a TabularDataset FileDataset

data not in Azure

For on-premise or IaaS SQL servers, you've got two options that I'm aware of:

• Put your SQL server inside the same network at the Azure ML service and ComputeTarget and access the server directly with pyodbc library.
• Use ADF to move the SQL server data to Azure Storage, (you'll need an ADF integration runtime on the SQL server)