On a Windows 11 machine, I recently connected Power BI Desktop to both Azure SQL Edge and SQL Server 2019, hosted on Docker. By running these servers locally, you avoid connecting to the cloud and certain Azure hosting subscription charges, e.g. Pay-AS-You-Go, when testing TSQL scripts or creating visuals in Power BI.
This post explains how to create SQL Server containers using the latest Docker images and run them locally on WLS2. Docker allows multiple SQL Server containers to run on localhost, by using different ports .I will show connecting to these containers, both inside the container and outside, with tools such as sqlcmd and SQL Server Managements Studio. Finally, I will restore Microsoft’s Adventure Works sample database from a backup file and connect it to Power BI Desktop.
Set Up
Docker is a software platform that lets you package applications and dependencies into containers that can run on any machine with Docker. Docker Desktop GUI gives you the ability to manage these containers and images from a local machine. In Windows, these containers can be Linux or Windows Server environments. This makes it ideal for development and testing.
The containers configured in this post are:
- SQL Server 2019 is based around the previous version of SQL Server Database Engine. Key feature include being able to query and analyse data from SQL Server, Oracle, MonoDB. HDFS and Spark. It has enhanced security features such as Always Encrypted , data classification and data masking. It does support distributed scalable Machine Learning solutions with supported languages such as R and Python.
- Azure SQL Edge is a relational database based around the latest version of SQL Server Database Engine. It is typically deployed in IoT application that need to process nonrelational data such as JSON, graph and time-series data. Additionally, it support the Open Neural Network Exchange (ONNX) format for machine learning applications. Note, Azure SQL Edge currently supports subset of features in SQL Server 2022 and hence, does have list of unsupported features . It is available in both Developer ( max 4 CPU / 32 GB memory) and Production ( max 4 CPU / 32 GB memory) SKUs . Alternatively, if you need the complete feature set of SQL Server 2022 , then replace the Azure SQL Edge image with the SQL Server 2022 image when creating the standalone container, in the steps below.
Pre-requisites
WSL 2
Install WSL 2 ( Linux on Windows) on either Windows 10 version 2004 (Build 19041 and higher) or Windows 11. The default installed Linux distribution is Ubuntu.
Check Windows Terminal has the instance of WSL
The documentation advises you should regularly update and upgrade packages. In Ubuntu open a bash prompt and and enter:
sudo apt update && sudo apt upgrade
If you get error like E: Malformed line 1 in source list /etc/apt/sources.list.d/vscode.list (type). I suggest you enter
rm /etc/apt/sources.list.d/vscode.list
Then run the previous command to update and upgrade the packages.
Optionally, you can enable systemd (replaces the default. systemv init ), as this provides a system and service manager – helpful when debugging issues. Once installed you can list the status of any services in your Linux distribution. Open a bash prompt and enter:
systemctl list-unit-files --type=service
Install Docker Desktop on WSL 2
Follow the steps outlined in: install Docker Desktop on Windows
You can check Ubuntu is running under WSL 2 by opening a PowerShell prompt and enter:
wsl -l -v
To avoid having to prefix everything with sudo by adding your install/admin account to the Docker group as this has elevated permissions. Open the Ubuntu/bash prompt in Windows Terminal and enter:
sudo usermod -aG docker $USER
Now you can finally check Docker is running:
docker run hello-world
Install the ODBC Driver for SQL Server
Install the desired Microsoft ODBC driver(s) for SQL Server on Windows for connecting Azure SQL Server Edge and SQL Server 2019, outside the container. Note that you can install Microsoft ODBC Driver for SQL Server for Windows in versions 17.4 and 18 alongside each other. Version 17.4 is required by the sqlcmd tool.
Install the one or more of the applications to work with the Azure SQL Edge and SQL Server 2019 containers.
With pre-requisites out of the way, you are ready to install Azure SQL Edge.
Deploy Azure SQL Edge with Docker
You are going to pull and run the Azure SQL Edge container image based on Ubuntu 18.04, so the recommendation is to use an Ubuntu 18.04 host . I had no issues when running either container on Ubuntu 22.04 host. To check your Ubuntu version , open the bash prompt and enter:
lsb_release -a
Note the remaining commands will be run from the bash prompt.
Warning
The password for Microsoft SQL Server should follow the default policy: at least 8 characters and contain three of the four sets: uppercase, letters, lowercase letters, base-10 digits and symbols. Certain symbols like “!” or “@” require using single quotes instead of double quotes when delimiting the password in the command to connect a SQL Server in the container using sqlcmd.
Pull the latest images from the Microsoft Container Registry
Azure SQL Edge
docker pull mcr.microsoft.com/azure-sql-edge:latest
SQL Server 2019
docker pull mcr.microsoft.com/mssql/server:2019-latest
Start server versions
Azure SQL Edge
docker run --cap-add SYS_PTRACE -e 'ACCEPT_EULA=1' -e 'MSSQL_SA_PASSWORD=<your_strong_password>' -p 1433:1433 --name azuresqledge -d mcr.microsoft.com/azure-sql-edge
include'MSQL_PID=Premium' if you want change from the default Developer to Premium edition.
Next you should change the password you have just entered as it is initially stored in the environment variable.
docker exec -it azuresqledge /opt/mssql-tools/bin/sqlcmd -S localhost -U SA -P '<your_strong_password>' -Q 'ALTER LOGIN SA WITH PASSWORD="<your_new_strong_password>"‘
SQL Server 2019
docker run --cap-add SYS_PTRACE -e 'ACCEPT_EULA=1' -e 'MSSQL_SA_PASSWORD=<your_strong_password>' -p 1401:1433 --name sqlserver2019b -d mcr.microsoft.com/mssql/server:2019-latest
Check one or more of the SQL Server versions are running:
docker ps -a
Note, the Container ID for both SQL Edge and SQL Server 2019 as these will be needed will be needed later on.
Connect to the server version through an interactive bash shell
Firstly, start an interactive bash shell, running inside your running container. Use separate Ubuntu /bash sessions if you need to interact with both server versions at the same time.
Azure Server Edge
docker exec -it azuresqledge "bash"
SQL Server 2019
docker exec -it sqlserver2019b "bash"
Quickly check the version of sqlcmd
/opt/mssql-tools/bin/sqlcmd -?
Run sqlcmd to connect to the SQL Instance running on localhost and issue Transact-SQL commands or system procedures or script files from the command prompt.
To get prompted with for a password:
/opt/mssql-tools/bin/sqlcmd -S localhost -U SA
To pass in the password as parameter, you may either need to delimit it with either single or double quotes, dending on the shell you are using
/opt/mssql-tools/bin/sqlcmd -S localhost -U SA -P '<your_new_strong_password>'
Once connected you will see the 1> prompt. To display the SQL Instance details on the Azure SQL Server container , open the bash prompt and enter:
1> SELECT @@SERVERNAME, @@VERSION
Create a test database
As per the documentation, you can create a test database and table.
CREATE DATABASE TestDB; GO CREATE TABLE Inventory (id INT, name NVARCHAR(50), quantity INT); GO INSERT INTO Inventory VALUES (1, 'banana', 150); GO INSERT INTO Inventory VALUES (2, 'orange', 154); SELECT * FROM Inventory WHERE quantity > 152; GO
List all the databases enter:
SELECT name from sys.databases; GO
Connect to Azure SQL Edge outside the container with SQLCmd
Open a PowerShell 7.X prompt:
Azure SQL Edge
sqlcmd -S localhost,1433 -U SA -P '<your_new_strong_password>'
SQL Server 2019
sqlcmd -S localhost,1401 -U SA -P '<your_new_strong_password>'
Connect via SQL Server Managements Studio
You should now be able to connect to the SQL Edge with
Server name: localhost
Authentication: SQL Server Authentication
Login: SA
Password: <your_new_strong_password>
Azure SQL Edge
SQL Server 2019
Copying SQL Server .Bak files in and out of the containers
The Adventure Works database samples are available as 3 separate SQL backup files (.bak) covering the following test scenarios : OLTP , Data warehouse (DW) and Lightweight (LT) . You just need to download the desired .Bak files for SQL Server 2022 and 2019 versions you here
You can copy the backup for the target SQL server by just referencing container ID which is hosting it. The syntax is almost identical for both Azure SQL Edge or SQL Server 2019, just the different backup filenames and Container IDs .
For SQL Edge Server and local (Download) folder, you can use the commands below:
docker cp C:Users<yourUser>DownloadsAdventureWorksLT2022.bak <container Id>:/var/opt/mssql/data/AdventureWorksLT2022.bak
docker cp <container Id>:/var/opt/mssql/data/TestDB.bak C:Users<yourUser>DownloadsTestDB.bak
Opening the AdventureWorks Sample in Power BI
Azure SQL Edge
Open SQL Server database
Database
Localhost
User name: SA
Password: '<your_new_strong_password>'
SQL Server 2019
You can open another instance of Power BI and connect to SQL Server 2019 container, to select the desired database.
Database
Localhost:1401
User name: SA
Password: '<your_new_strong_password>'
Installing the On-premises gateway
You can choose to enhance this development set up by installing the On-Premises gateway. This will enable the connection between the SQL Server containers and Power BI services, Power Apps, and Power Automates. The gateway is available in standard and personal mode (Power BI services only).
Attention
Power BI services gateway config settings, requires a cluster to be created, with each gateway downloaded on to different computers. Gateways are not supported on Windows containers or Docker.
Shutting down the SQL Containers
To stop Azure SQL Edge, open the bash prompt and enter :
docker stop azuresqledge
Alternatively, you can navigate to the Azure SQL Edge container and click on the top right Stop button
To remove Azure SQL Edge , enter:
docker rm azuresqledge
Note only run this when you want to permanently remove the Azure SQL Edge container.
Finally, please let me know your thoughts in the comments.
