Microsoft recently ended support for Azure Data Studio, its development tool for the SQL Server and Azure SQL databases. Part of a range of tools designed to work with Microsoft’s on-premises and in-cloud SQL databases, Azure Data Studio focused on building and testing queries rather than database administration. In other words, it was a developer-focused, lighter-weight alternative to SQL Server Management Studio.
Azure Data Studio was a useful tool for developers. It made it easy to explore databases and craft the queries that could be integrated into your code. You weren’t limited to queries, as a command line allowed you to work with lower-level and administrative services when necessary. You can still use SQL Server Management Studio with Azure data, but it’s overkill for most common query building tasks.
Bringing SQL support into Visual Studio Code
What should you use instead? Microsoft recommends switching to the MSSQL extension for Visual Studio Code. That answer shouldn’t be surprising, as Microsoft has been moving much of its Azure development tooling from stand-alone applications to Visual Studio Code extensions. If you’ve been using Azure Data Studio for other Azure-managed data offerings, they will have their own VS Code extensions, with PostgreSQL and Cosmos DB already available and MySQL in development.
It’s an approach that makes sense: Visual Studio Code has become an important part of many developers’ toolchains, whether they’re using Windows, macOS, or Linux. By shipping tools as extensions (using VS Code’s JavaScript SDKs), Microsoft needs only to write them once and they will run anywhere a developer uses their editor. VS Code is lightweight compared to the Visual Studio IDE, with easy-to-access terminals that work with both local and cloud environments.
This approach fits in with Microsoft’s long-stated policy of putting tools where the developers are — and the popularity of the free Visual Studio Code means it is already running on many developers’ desktops. Mixing and matching different extensions from Microsoft and third parties allows what would otherwise be a standard programming editor to become a custom development environment that can host most of a developer’s chosen tool chain, from languages and source code control to management and productivity tools and more.
SQL Server and Azure SQL have been around for a long time, and they benefit from many different development tools and management utilities, from Microsoft and from third parties. This new tooling builds on that heritage, as well as providing a jumping off point for modern technologies, integrating with the GitHub Copilot tools in VS Code.
Introducing the MSSQL extension for Visual Studio Code
Launched towards the end of 2024, the current version of MSSQL extension for Visual Studio Code was initially designed to support all of Microsoft’s SQL Server databases. It provides tools for connecting to both local and cloud-based databases, including exploring your Azure account and subscriptions for databases. This helps keep track of database sprawl, allowing you to find where you can combine resources and manage spend as well as tuning performance. Connections are saved by the extension, simplifying access to databases across projects.
The core functionality in the MSSQL extension will be familiar, with views that drill down into the various objects that make up a modern relational database, and tools that support database development, administration, and analysis.
Building and publishing databases
The MSSQL extension supports different roles in the database development life cycle. If you’re building out the necessary databases for an application, then you’re likely to start with its table designer. The extension takes an offline approach to table design, where you can use VS Code to design columns and indexes, as well as define the necessary keys and relationships to build out complex data structures. As the tooling is designed specifically for Microsoft’s SQL databases, it’s able to take advantage of low-level features to help you get the best performance for your data, including defining in-memory operations.
Once your table design is complete, the extension generates the necessary T-SQL to create or update your database schema. This script is available in the VS Code editor, so you can examine it and edit as necessary. Usefully the script can be saved locally and used as the basis for a template that can take you from a local development database to a deployed production system running on Azure.
If you’re satisfied with your database and table design, then the MSSQL extension will use Microsoft’s SQL Server Data-Tier Application Framework (DacFx) to publish and run the script. As well as creating databases, DacFx can support much of the database life cycle and will update existing schema for you as well. The DacFx libraries are open source, and you can build them into your own applications as well as using them from inside VS Code.
Developing and testing queries
As well as being used to design and deploy databases, the MSSQL extension provides a framework for designing and testing queries ready for use in your code. Once connected to a Microsoft SQL database, you can use its query tools to view results in a grid view inside the editor, with the option of displaying them in their own tab.
One useful feature of the MSSQL extension’s query designer is its static query performance analyzer. Going under the name of “Estimated Plan”, this analyzer can show bottlenecks and other performance issues without running the query. The output of this operation can be used to help refine your queries, and when you are happy with the estimated performance, run the “Enable Actual Plan” function to run your queries against the target database to get actual results for further analysis.
The query plan tools include a visualizer that shows the steps used to make a query, displaying them as a graphical tree that you can walk through to explore each step. The visualizer can be used to highlight expensive operations, helping you find alternate ways to get the results you need without significant delays.
Adding APIs and analysis
Once you’ve built a database and tested queries, you can use the built-in Data API builder (currently in preview) to quickly add REST APIs that make it easier to extract the data needed in your code. Other options include providing a GraphQL API for more structured queries or delivering a Model Context Protocol (MCP) endpoint that can be used by agents or other AI applications to provide natural language access to data as well as to ground AI interactions in your data.
The MSSQL extension isn’t only for developers and database administrators, as VS Code’s strong Python support has made the code editor a popular tool for business analysts and data scientists as well. These users will like the newly-added support for SQL Notebooks (another preview feature), which give you an interactive Jupyter Notebooks-like environment for interactive exploration of your data. Using markdown text around queries and results in this tool can help you produce live documentation of a database, especially if you’re using the in-memory column queries to manage large amounts of data.
There’s a lot to like in the MSSQL extension for Visual Studio Code. It’s a welcome modern replacement for older tools, and one that works well both for developers building code that interacts with SQL Server or Azure SQL databases and for designers and administrators who need tools to build and test their schemas and work with those databases across on-prem deployments and Azure cloud regions.
The way we build and manage databases has changed a lot with the advent of hyperscale platforms like Azure. Having a flexible tool that’s constantly adding features is important, as it allows our tooling to evolve at the same speed as the underlying platform. Having it in a way that drops right into our existing tool chains is a bonus, but it’s one that also makes the MSSQL extension for Visual Studio Code ready for use as soon as it’s installed.
Go to Source
Author:
