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.

The post State of the SQL Server tools appeared first on Microsoft SQL Server Blog.

Research has shown that users spend an order of magnitude more time working on query editing than on any other task with SQL Server Management Studio. For that reason, Azure Data Studio has been designed to focus deeply on the functionality that is used the most, with additional experiences made available as optional extensions into the product. This allows every user to customize their environment to the workflows that they use most often. Today we are pleased to announce the GA of the product, which will continue to be released on a monthly basis.

The vision of the product is to create a unified experience across heterogenous data sources regardless of their form or location: structured or unstructured, on-premises or cloud. Azure Data Studio currently offers built-in support for SQL Server on-premises and on the cloud and Azure SQL Database, along with preview support for Azure SQL Managed Instance, Azure SQL Data Warehouse and SQL Server 2019 Big Data Clusters. Other preview experiences include Azure Data Studio Notebooks, Azure Resource Explorer, SQL Server Profiler, SQL Server Agent, SQL Server Import Wizard, and SQL Server PolyBase Create External Table Wizard. Due to the extensible nature of the product, Azure Data Studio also offers third party partners and community members to contribute their own experiences to the tool, including Redgate’s SQL Search extension.

We are proud to offer a preview of the first ever notebook experience for SQL Server in the Azure Data Studio SQL Server 2019 Preview Extension. Notebooks are one of the most common code development environments for data and serve multiple purposes in a modern data development workflow. Notebooks combine human readable documentation with executable code and resultsets, greatly improving the process of collaborating on data analysis. The Azure Data Studio notebook viewer uses the open source Jupyter server and file format, but adds in the modern, keyboard-focused coding environment and rich editor experience of Azure Data Studio, allowing users to write code in the language of their choice. Having a notebook embedded with Azure Data Studio allows seamless in-context operations such as launching notebook analysis on an HDFS file from the Object Explorer and connection to remote SQL Server big data clusters. In the CTP 2.0 Preview, notebooks may be run locally or against SQL Server big data clusters using Python and Scala, with additional language and endpoint support coming in a future preview, including a planned pure T-SQL notebook experience for the SQL Server user.

Azure Data Studio shares a heritage and a roadmap with SQL Server Management Studio, which has been a phenomenally successful and well-liked tool in its own right. Over the course of time, all of the management features of SQL Server Management Studio will be made available in Azure Data Studio and the two products will integrate smoothly with each other. At present, Azure Data Studio is tightly focused on the experiences around query editing and data development. Additional high-value administrative features such as backup, restore, agent job management, and server profiling are also available as extensions in Azure Data Studio. Azure Data Studio is also cross-platform, allowing users to work on their platform of choice. However, SQL Server Management Studio still offers the broadest range of administrative functions and remains the flagship tool for platform management tasks.

Azure Data Studio may be downloaded from here. You can participate in the future of the tool by entering or voting on feature suggestions, reporting bugs, or by contributing your own pull requests or extensions into the product. The team welcomes your feedback and will be adding capabilities on a monthly basis based on community requests.

When Should I Use Azure Data Studio vs SQL Server Management Studio?

Use Azure Data Studio if you:

• Need to run on macOS or Linux
• Are connecting to a SQL Server 2019 big data cluster
• Spend most of your time editing or executing queries
• Need the ability to quickly chart and visualize result sets
• Can execute most administrative tasks via the integrated terminal using sqlcmd or Powershell
• Have minimal need for wizard experiences
• Do not need to do deep administrative configuration

Use SQL Server Management Studio if you:

• Spend most of your time on database administration tasks
• Are doing deep administrative configuration
• Are doing security management, including user management, vulnerability assessment, and configuration of security features
• Make use of the Reports for SQL Server Query Store
• Need to make use of performance tuning advisors and dashboards
• Are doing import/export of DACPACs
• Need access to Registered Servers and want to control SQL Server services on Windows

Feature comparison

The post Azure Data Studio for SQL Server appeared first on Microsoft SQL Server Blog.