Planning new scenarios and use cases for Azure SQL Database local development experience!
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.
Silvano
Light
Dark
9 comments
This is great! I especially like the idea of the upcoming experience to easily create instances.
Is there a roadmap for when we can expect either the Full image to run on M1 Macs, or to get spatial/hierarchyid in the Lite image? Is that something coming in 2022?
Given the growing importance of ARM64 architectures for both local and cloud scenarios, we’re certainly planning to provide a complete experience for Full image as well. Lite image is bringing some additional value as it’s about half the size of Full, and for quick pull and spin up scenarios like in CI/CD workflow it makes a lot of sense. We still don’t have an ETA to announce, but stay tuned as we will quickly evolve this area!
This is excellent. I’m really looking forward to having the full image running on arm64/M1 Macs. Our app relies on geospatial computations in sql and it has meant that we have two strategies for the development of our app: on Azure for all pages that need geolocation, and local with SQL edge for everything else. Whereas we started out on Azure only, when coding on the go, we don’t always get reliable and secure access to the cloud. Plus, as a startup we need to keep our costs low or as close to zero as possible. I’m looking forward to finding out more. Is there a way to subscribe to just this topic? And if you ever find yourself looking for alpha testers, do give me a shout! We’d love to get involved.
Thanks a lot for your feedback Eddie! Keep an eye on this blog or on Azure Updates (https://azure.microsoft.com/en-us/updates/) to stay in touch with future announcements.
Todate we are working with a local installation of Sql Server and a bunch of efcore and sqlpackage scripts to easily iterate on a schema version within a feature branch. Our guideline is at most one migration per merge to the main branch. It would be very nice if this problem space gets simplified over time. I suspect the new sql emulator is a step in the right direction!
Thanks a lot for your comment Carl, we are really interested in the scenario you mentioned and would love to get more details and requirements.
If you like, please feel free to follow up over email (scoriani at microsoft.com) and we can kick off the conversation.
Thanks in advance!
Muchas gracias! Por otro lado sería ideal pensar en una solución para conectar a Azure SQL Database usando protocolo standard HTTP 80 usando un proxy para evitar conexiones a SQL y abrir puertos en los Firewalls. Hay algún avance en este sentido? Gracias.
Hi Jesús, while we can’t announce anything in this space today, I can say that there’s active work to expose Azure SQL Database over other protocols within the next months, so stay tuned! 🙂
Yesss!!!! really good news. Thank you so much.