Azure Data Studio provide rich functions to connect to SQL Server. Its Jupyter Notebook enables you to perform all sorts of analytics in Jupyter Notebook easily. You can use run your books with SQL, Python, PySpark, Spark R, Spark Scala and other kernels.
This article shows you how to run a SQL Server 2019 instance using Docker Desktop for Windows and then connect to it via Azure Data Studio.
Run SQL Server 2019 instance via Docker
Before you can run SQL Server image in docker, please install Docker Desktop. The version I'm using is the latest 4.0.0 version.
For 4.0.0, you can use WSL 2 as engine instead of relying on Hyper-V.
After installation is completed, run the following command to run SQL Server 2019 image:
docker run -e "ACCEPT_EULA=Y" -e "SA_PASSWORD=P@SS0rd!" -p 14330:1433 -d mcr.microsoft.com/mssql/server:2019-latest
We are mapping 14330 to 1433 in the container in case you have other SQL Server instances running on your computer. The image is the latest 2019 version. The password for supper account sa is set as "P@SS0rd!".
Test the connection
If you have sqlcmd tool installed on your computer, run the following command to verify:
sqlcmd -S localhost,14330 -U sa -P P@SS0rd!
Connect to SQL Server via Azure Data Studio
You can download and install Azure Data Studio from here: Download and install Azure Data Studio.
Open Azure Data Studio, click add connection button to establish a new connection.
Input the following details:
- Server: localhost,14330
- Authentication type: SQL Login
- User name: sa
- Password: P@SS0rd!
Click Connect button to connect to the server.
If the connection is successful, you can see the following window:
Create new notebook
Click New Notebook button to create a new notebook.
By default, the kernel is SQL and you can use SQL to query the data.
Use SQL kernel
Click + Cell button to insert a code cell with the following content:
select * from master.sys.tables;
Click F5 to run the cell and the output looks like the following screenshot:
You can click the chart icon above the result table to create charts on top of the result set.
Use Python kernel
Create a new notebook with Python 3 as kernel. It will install Python for you or allow you to choose a local Python if it is already installed.
You can install Python packages via Manage Packages icon (on the right top corner):
Use Spark kernels
You can also choose Spark as kernel if your SQL Server instance has big data cluster (BDC) feature enabled. For this container image, it is not supported. This is very useful when combining with SQL Server Machine Learning Services.
Azure Data Studio is a modern tool with a focus with interactive analysis. It is much better than the traditional SSMS if you want to use it to perform all sorts of analytics with SQL Server.