State of the SQL Server tools
This week we’re announcing the general availability of SQL Server 2019, a significant milestone for Azure Data and for SQL Server customers. This presents a good moment to give an update on the state of tooling for SQL Server.
Since SQL Server 2016, the tools for SQL Server have been released independently “out of box” from the server product. This allows us to be more agile to the needs of our users, get both features and bug fixes shipped more quickly, stay aligned with the more continuous release cycle of Azure SQL, and in general allows the tools team to innovate in exciting ways. However, one side effect is that it can be difficult to understand what’s happening across the tools landscape, as things change quickly in multiple products that are releasing as frequently as every month.
The SQL Server client tools cover the full breadth and depth of the product, and can be broadly categorized as follows:
- SQL Server drivers and connectivity APIs, such as Microsoft.Data.SqlClient, ODBC, JDBC, etc.
- SQL Server tooling APIs and scripting support, including SQL Server Management Objects (SMO), DacFX, and PowerShell
- Command line tools for SQL Server like sqlcmd, bcp, mssql-cli, and sqlpackage
- Jupyter Notebooks, a new entrant in this space that bridges the CLI-to-GUI divide
- Graphical tools for SQL Server, including SQL Server Management Studio, Visual Studio, Azure Data Studio, and Visual Studio Code
- The Azure portal experiences supporting the Azure SQL family of products
Each of these areas has had important updates since the last major release of SQL Server.
SQL Server drivers
Microsoft maintains or contributes to a number of drivers for SQL Server supporting a wide variety of languages. As new features are added to Azure and to SQL Server, some require updates at the protocol and driver level, including features like UTF-8 support, Always Encrypted, and data classification. The most significant strategic update in this area has been the change from System.Data.SqlClient to Microsoft.Data.SqlClient as the premier driver for the .NET ecosystem. You can read more about this in the .NET announcement blog. The Microsoft.Data.SqlClient driver and its associated NuGet package are now generally available, and while System.Data.SqlClient will continue to be supported, the new out-of-the-box Microsoft.Data.SqlClient is the recommended assembly for .NET developers targeting SQL Server in the future.
SQL Server APIs and scripting
There is continuous work in the important SQL Server Management Objects (SMO) and DacFX libraries, which are the application interfaces used by most of the rest of the tooling stack to interact with SQL Server. With SQL Server 2017 introducing a need for cross-platform tooling, a lot of effort has been spent in the past few years in updating these assemblies so that they can be used on MacOS, Linux, and in containers. The SQL Assessment API is a new offering that allows for best practice checks to be performed with SQL Server Management Objects and with PowerShell. And the SQL Server PowerShell module continues with very regular updates, having had 17 releases in the past two years. These capabilities are widely used, and the SQL PowerShell module has been downloaded over 2.3 million times since 2017.
Command line tools
The SQL Server command line tools were some of the first and most important tools to be taken cross-platform for users of containers, Linux, and MacOS, owing to their important role in non-graphical and automation use cases. The open source mssql-cli is a new command line tool with a more user-friendly interactive querying experience compared to sqlcmd, and important work has been done in sqlpackage.exe to allow cross platform extract and build for dacpacs. This work is not only foundational to bringing more cross-platform developer experiences into the graphical tools, it has proven to bring significant performance improvements as well.
Jupyter Notebooks
The flexible and portable format of Jupyter Notebooks has been popular in the data science and big data communities for a number of years. However, with the introduction of full fidelity T-SQL support to notebooks in Azure Data Studio, this is a technology that has become relevant to a wider community of SQL Server users including developers, IT professionals, database administrators, analysts, and more. The nature of a Jupyter notebook creates a “documented CLI” experience which can be used for development, data exploration, troubleshooting, documentation, and more. Through integration with Microsoft tools like Azure Data Studio, PowerShell, and Azure Data, in combination with open source platforms like Papermill, it’s possible for notebooks to be used as both interactive and non-interactive, operationalized experiences. The SQL Server team is investing heavily into notebooks and the related Jupyter Book concept for product documentation, deployments, and more. Azure Data Studio offers a full Jupyter Notebook editor with support for SQL, Python, Apache Spark™, Scala, R, and PowerShell.
Graphical tools
The graphical tools for SQL Server are the most visible and recognizable tools in the portfolio. The flagship graphical interface for SQL Server is SQL Server Management Studio (SSMS), a tool that has been used by millions for over 15 years. Since SQL Server 2016, SQL Server Management Studio has been available as a separate download from SQL Server. In the past year, the major version 18.0 shipped, followed by a number of minor releases on an approximately bimonthly cadence. Regular updates and improvements to SQL Server Management Studio will continue, but most of the net new innovations in the graphical tooling space, such as notebook support, can be expected to ship in Azure Data Studio. As a cross-platform tool designed to support multiple database systems, Azure Data Studio operates in a slightly different space from SQL Server Management Studio. Azure Data Studio is open source and ships monthly, with major features in the last year including Jupyter Notebooks, Dacpac import and export, Schema Comparison, external table creation, data visualization in SandDance, and extensions provided by the community and commercial partners. Azure Data Studio is also investing in deployment experiences for SQL Server, which is an inversion from the days when a SQL Server CD was used to install SQL tools.
For our SQL Server developers, the SQL Project functionality in Visual Studio has been supported and maintained, but without a great deal of visible change in the last couple of years, as work has been going into the API and CLI levels. This lower level work is coming toward completion, and new experiences around development for Azure SQL Database Edge are imminent. Additionally, we have a vibrant userbase of the SQL Server (mssql) extension for Visual Studio Code, that’s very actively maintained and has recently shipped a major update.
Azure portal
In the Azure portal, a great deal of work is being done to consolidate, standardize, and simplify the process of working with the Azure SQL family of products including SQL Server on Azure Virtual Machines, Azure SQL Database, and Azure SQL Managed Instance. The past few months have brought a new unified deployment and management experience and moving forward, we intend to bring more features pertaining to management at scale and beyond.
Looking forward
The SQL Server tooling stack has never been deeper or stronger. The agile nature of tooling development combined with the active involvement of the SQL Server community creates an environment where it’s possible to iterate more quickly than ever. As SQL Server spreads across an ever widening combination of deployment options and platforms, from ground to cloud, expect to see changes in the SQL Server tools as well to support the new workflows and experiences that users need to do their best work.
