Joey on SQL Server

Everything You Need To Know About Azure SQL Database Emulator

Microsoft's Asad Khan breaks down some details on the recently announced feature.

• By Joey D'Antoni
• 06/27/2022

One of the Azure SQL-related announcements that did not get a lot of publicity at May's Microsoft Build conference was the Azure SQL Database local database experience. With all the hype around SQL Server 2022, I wanted to make sure this feature wasn't overlooked.

Even though Azure SQL Database has made it easy for developers to deploy a database to support their application, there was no local way to develop using it. While having a small Azure SQL Database for development purposes doesn't cost a lot, it does increase developer friction -- especially if the developer does not have the permissions to create resources in their cloud environment (or has to manage other infrastructure components like virtual networks).

While Docker containers for SQL Server make it easier to deploy, previous incarnations did not allow the same functionality that Azure SQL Database supports. As part of the local database experience, Microsoft introduced the Azure SQL Database emulator, which allows developers to effectively have an Azure SQL Database in their environment.

I, along with a few developers I spoke to, were really excited about this feature. To get more information for Redmond readers, I reached out to Microsoft to discuss the details. I spoke with Asad Khan, Partner Director for Program Management in Microsoft's Azure Data team.

D'Antoni: What type of user requests drove the development of the emulator feature?
Khan: Modern developers are looking for ways to increase velocity and reduce time to market. These public preview announcements help them do that by reducing the amount of hardcoded "boilerplate" code, providing a risk-free offline local development experience and expanding support for more data formats. 

Unit testing, integration testing and such are accepted and often consolidated practices within application development workflows, but less so in database development. Users want to test separate portions of their applications and databases on their local development workstations before checking their artifacts into their shared CI/CD pipelines. 

What are capability differences between the emulator and Azure SQL Database? Are there any plans to close those gaps?
The current release of the Azure SQL Database emulator is leveraging existing container images for Azure SQL Edge (Lite) and Microsoft SQL Server 2019 (Full). The former offers cross-hardware platform compatibility and smaller image size, while the latter provides higher compatibility but only runs on Intel x64 processor architectures. The emulator provides the same development experience as SQL DB, thus enabling developers to build/test apps locally. Both are based on the same SQL engine, therefore it offers the same development surface area. There are some minor differences, but so far we have not gotten any feedback about the lack of feature compatibility.

Developers can also leverage SQL Project extension and select Azure SQL Database as target platform for building and deploying their databases to benefit from T-SQL compatibility checking at design and build time. In future releases, we may increase feature parity and provide higher fidelity with the Azure SQL Database public service. 

I feel like the container workflow really enabled a lot of DevOps workflows that would have been otherwise challenging. Do you have any feedback from preview users on this?
Preview users are reporting positive feedback on how Azure SQL Database emulator is helping them to automate and streamline their application and database development and testing pipelines on their local workstations. This, in addition to Azure SQL Deploy action in Github Actions, provides a great end-to-end experience to manage the entire database lifecycle process.

Does a developer need to have any Azure resources to get started?
No, Azure resources are not required to get started. The local development environment for Azure SQL Database uses the Azure SQL Database emulator, a containerized database with close fidelity to the Azure SQL Database public service. The Azure SQL Database emulator is implemented as a Docker container. Azure resources are not required to set up a SQL Database Project and publish test it against a local Azure SQL Database emulator instance. But it would be required when the developer will decide to publish the project to a cloud environment directly or through a CI/CD pipeline.

This is supported on ARM64. What tech makes that possible?
Azure SQL Database emulator Lite is based on Azure SQL Edge container image, which is available for both x64 and ARM64 processor architectures. 

How does GitHub Codespaces play into this stack and experience?
Database projects can be checked into source control systems, such as GitHub repos, and accessed from GitHub Codespaces together with other code artifacts and projects, providing a true "database as code" experience for designing, implementing and maintaining databases as part of the entire end-to-end solution. From there, developers and DevOps teams can leverage features like GitHub Actions for publishing a new database or a schema change to a test or production environment in Azure SQL Database public service.

Is the SQL Database Project extension required? How does it better facilitate development?
SQL Database Project extension is part of the Azure SQL Database local development experience. While not strictly required to use Azure SQL Database emulator, it does provide design and build time validation for database objects and procedures' T-SQL syntax and features compatibility before deploying a project on Azure SQL Database public service. Developers can then deploy SQL Database projects locally on the Azure SQL Database emulator for additional runtime and validation testing before publishing them ultimately on Azure SQL Database public service.

 *****

The Azure SQL Database service is one of the earliest services in the Azure ecosystem. During its existence, administrators and developers have seen it evolve over time into the robust service that it is now. As the evolution has happened, the developer experience hasn't always matched the level of other improvements. The Azure SQL Database emulator changes that and provides an easy way for a developer to build a multi-tier application on their laptop and then deploy it directly into a production cloud environment.