Run Microsoft Azure SQL Locally!

6 min readJul 6, 2022

Disclaimer: the image mentioned in this article is currently marked as preview. Opinions expressed therein are solely my own and do not express the views or opinions of my employer.

I am hoping to learn more about the Azure SQL Server offering. The best way I know how to learn a new technology is hands-on experience! Azure offers a bunch of services in a “free” tier, but what if you want to develop solely on your own hardware or even offline?

Wouldn’t it be great if you could run Azure SQL on your development machine? I mean you wouldn’t have to worry about racking up a substantial bill. Wouldn’t it be great to be on a flight or train — away from any Wi-Fi — and work or learn Azure SQL Server?

After some research on Google, I decided to put together a “quick start guide” and share my findings! In essence, with Azure SQL running on your development machine, you should be able to design, edit, build & validate, and run Azure SQL Server database schemas for databases while working offline!

Prerequisites

Let’s get setup to run Azure SQL on your development machine! I assume you have the level of access required to install and configure the following software on the target machine.

Please refer to each application’s minimum requirements for hardware requirements:

Visual Studio Code or Azure Data Studio (if host is Windows, MacOS or Linux)

SQL Server Management Studio (if host is Windows)

REQUIRED:

Docker Desktop

The article is not meant to provide detailed installation instructions for the above software, please use your search engine of choice to search for installation instructions.

At the time of writing this article, I am performing these steps on a machine with Microsoft Windows 11 with Visual Studio Code v1.68.1, Azure Data Studio v1.37.0, SSMS v18.12.1 and Docker Desktop v4.9.1.

Installation

Installation is relatively simple. You must pull down the Azure SQL Database emulator (preview) docker image using the following docker command:

$ docker pull mcr.microsoft.com/azure-sql-edge

Run the following docker command to create and run a container:

-i = Keep STDIN open even if not attached
-t = Allocate a pseudo-TTY
-d = Run container in background and print container ID

$ docker run -d -e "ACCEPT_EULA=Y" -e "MSSQL_SA_PASSWORD=$ecurePassword1234!" -p 1401:1433 --name edge -d mcr.microsoft.com/azure-sql-edge

Important Note!
Please substitute your own secure password for the one included in the previous command: $ecurePassword1234!

Limitations

Please note that the current implementation of the Azure SQL Database emulator derives from an Azure SQL Edge base image. When you run in Docker, it offers a cross-hardware platform compatibility with a smaller image size, compared to the Azure SQL Database public service. PLEASE NOTE: some specific features may not be available and the local instance limitations include:

Spatial data types
Memory-optimized tables in in-memory OLTP
HierarchyID data type
Full-text search
Azure Active Directory Integration

While lack of compatibility with some of these features can be impactful to your work given the above limitations, the emulator is still a great tool for local development and testing, supporting most of the Azure SQL Database programmability surface.

Please refer to the official Azure SQL Edge documentation for further details.

Connecting to Your Docker-based Local Instance

Now that you are up and running, you can use Visual Studio Code (or Azure Data Studio) on Windows, Linux or MacOS to connect to your instance or SSMS on Windows-only.

If you decide to connect using Visual Studio Code, you will need the following plug-in:

The mssql extension for Visual Studio Code enables Visual Studio Code to connect and run queries and test scripts against a database. The database may be running in the Azure SQL Database emulator locally or a database in the global Azure SQL Database service.

If you use Azure Data Studio, download the SQL Database Projects extension (Preview) which will provide the ability to capture an existing database schema and/or design new database objects using a declarative database design model.

The information provided in this section was referenced from the following article:

What is the local development experience for Azure SQL Database? — Azure SQL Database | Microsoft Docs

SSMS

When exclusively using Windows operating system, launch SSMS:

Open SSMS.
In the Connect to Server dialog, enter localhost,1401 in the Server name field, then select SQL Server Authentication from the Authentication dropdown list. Enter sa in the Login input then the password your set on the second docker command line (i.e., what you entered above when creating your Azure SQL Server container (in bold): “MSSQL_SA_PASSWORD=<your secure password>”).

3. Click the Connect button and you should be connected to your local docker instance.

Visual Studio Code

Open Visual Studio Code.
Select the SQL Server option in the well then click the + Add Connection button (image below)

3. Type localhost, 1401 in the command palette box then press the <Enter> key.

Press the <Enter> key after entering the command above.

4. Press the <Enter> key to confirm:

5. Select the SQL Login from the dropdown list:

6. Enter sa for the User name then press the <Enter> key:

7. Finally enter your secret password then press the <Enter> key to confirm.

8. Next option is totally up to you, You can save your password by selecting Yes, otherwise select No from the dropdown list:

9. Finally, enter a name to distinguish from other instances (totally up to you) then press the <Enter> key to confirm.

10. You can now work with the databases for this instance in the Connections panel:

Azure Data Studio

Con natively connect to the docker instance using the following connection information:

Open Azure Data Studio.
Click the New Connection button.
Enter the relevant information into the Server: localhost,1401 Select the SQL Login authentication type and User name: sa and Password: your secure password from above fields.