Planning new scenarios and use cases for Azure SQL Database local development experience!

Silvano Coriani

At Microsoft Build 2022 we’ve announced the new Azure SQL Database local development experience to the world. The combination of Visual Studio Code and Azure Data Studio extensions in addition to the new Azure SQL Database emulator for local and offline database development are aimed to significantly improve developers’ experience and boost their productivity.

This release though is just scratching the surface of what we have in mind in this space, and we’re considering to cover many new scenarios and use cases on several key areas of application and database development workflows.

Azure SQL Database emulator and local development

Those of you that are more familiar with running SQL Server engine on Docker may have noticed that current Azure SQL Database emulator Lite and Full images are based on existing container images for Azure SQL Edge and Microsoft SQL Server 2019. The reason why we are providing 2 distinct images is based on few key aspects:

  • Processor architectures supported
  • Programmability surface
  • Container image size (and related pull and spin up times)

Azure SQL Database emulator Lite is based on Azure SQL Edge container image, and provides a lightweight, cross-platform (it supports both X64 and ARM64 processor architectures) while offering a good programmability trade off in terms of most common T-SQL DML/DDL commands and engine capabilities. For those developers that are looking at a general purpose SQL engine that they can quickly spin up offline and deploy/validate their applications against, this is definitely a great option. The drawback is that it doesn’t support some of Azure SQL Database capabilities like In-memory OLTP optimised tables, geospatial or hierarchyid data types.

Azure SQL Database emulator Full is based on Microsoft SQL Server 2019 container image, and it offers a complete coverage of Azure SQL Database public service features and more, but it doesn’t currently run on ARM64 processors (such as Apple M1-based computers) and it has a larger on-disk footprint and longer pull times.

We’re planning to evolve these container images over the next months and create dedicated container images, providing a more consistent and an high-fidelity experience when developing locally and publish your code to Azure SQL Database public service in terms of T-SQL runtime syntax checking and query execution behaviours.

Today developers can fully manage Azure SQL Database emulator through Docker CLI and tooling (e.g. Visual Studio Code extensions), but we’re planning to provide a dedicated management experience for creating, configuring and running multiple emulator instances on developers’ workstations.

Database development and testing productivity

Unit testing, integration testing and such are pretty accepted and consolidated practices within application development workflows. But how much in database development?

How can i make sure that separate portions of my application will work together when using a database by having one available that contains the data to validate the scenario to be tested?

How can i make sure that application’s data are congruent across one or more databases and that i can validate data integrity as part of my application testing workflows?

Some fragmented tools and practices exist in this space, but we think that there’s plenty of room for improving developer toolset and integrate them into regular application and database development process.

Schema changes/evolution for single databases and fleets

Many developers have an application-centric view of their databases. In some cases, databases get deployed, managed and queries as part of the application lifecycle rather than exist as independent entities to be used, for example, by multiple applications and systems.

When this happens, in most cases the very essence of the data model itself is defined in the application’s domain model, and mapped to database objects through an Object Relational Mapper framework (some of the most commonly used, depending on your programming language of choice, are Entity Framework Core, Sequelize, SQLAlchemy, Prisma, etc.).

In addition of generating SQL code to query and insert data in database tables, all of these tools are also exposing capabilities called “database migrations”, as the ability to create and evolve database schemas and objects by generating SQL’s Data Definition Language commands (DDL) that can be used to create or alter the state of application’s databases.

Today, developers can leverage these migrations to trigger database deployments or schema changes and evolutions as part of their application’s CI/CD pipelines, but it is still a complex and error-prone process. We are planning to look deeper into this problem space and create tools and procedures that can significantly simplify developer efforts.

Now, while these activities are already quite complex when targeting few databases that are part of an independent solution, think about the same task but with a successful multi-tenant SaaS application where you may need to deploy and evolve your schemas and objects across potentially 10000 identical copies of the same database (as the approach used by most successful players in this solution space).

Having a tool that lets you define how to roll out these schema changes in a controlled, safe and scheduled manner on a portion or the entire database fleet, with the ability to automatically roll back in case of an issue is something that would make your job so much easier and simple.

This is an area of research that we’re planning to tackle and offer as combination of tooling and platform evolution.

In summary

These are just some of the scenarios and use cases we’re considering, but we would love to hear your thoughts on what is important for you in this space.

Please use the comments section below to ask questions or provide specific feedback on these subjects and we can start from here to have an open and productive forum and drive database development experience forward!

Looking forward to hear your thoughts.