While many folks observe April 1 as April Fool’s Day, we in the SQL Tools team celebrate April 1 as April Tools Day, a day when we debunk myths surrounding the tools for SQL Server. We recommend having some good chocolate to honor the occasion.
Now, you may wonder how we are aware of the misconceptions that exist for the different tools for SQL Server. Some thoughts we hear from direct conversation, for example at conferences and on social media. But one of the best resources is the comments that folks leave when responding to a survey.
For example, you may have been using Azure Data Studio and had a dialogue pop-up that asked if you would recommend ADS to a friend. While we would love for folks to provide a 9 or 10 when asked, what’s more interesting are the comments shared after selecting a score. We read every. single. comment. that is entered. These replies create a picture of what users find challenging and what they would love to see, and ultimately they help us make the product better. They also reveal misunderstandings that exist about a given tool, which leads to our celebration of April Tools Day.
Myth #1 Azure Data Studio is the only standalone solution now that SSMS is deprecated.
SQL Server Management Studio (SSMS) isnot deprecated. We thought about writing that in ALL CAPS, but figured bold is sufficient. SSMS has not been deprecated, and we are not planning on deprecating it. You will see new functionality being added to Azure Data Studio, but we have a fair number of things lined up for SSMS, including migration to the Visual Studio 2022 shell, which brings 64-bit support.
Myth #2 No new work is happening with drivers.
You may have missed the debut of the new sqlcmd (aka go-sqlcmd) a couple weeks ago during the SQLBits conference. This tool is based on the Go driver mssqldb. The new sqlcmd is still in preview, but version 1.0 will GA soon, and in time we expect it to replace the existing sqlcmd while also surpassing it in capabilities. For example, it can be used on Linux and Mac, as well as Windows, can be quickly downloaded or updated, and works for Azure SQL Database and on-prem installations of SQL Server. Once you have it installed, run ‘sqlcmd create mssql’ to create a SQL Server container based on the latest image - it's that easy. If you want to learn more, check out the documentation or the GitHub repository. (Yep, it’s open source, too.)
Myth #3 The SQL Database Projects extension features are lacking.
We have made significant strides in adding functionality to the Database Projects extension. With GA on the horizon, the extension will sport a new backend and in time will include new features such as moving/renaming files, saving publish profiles, and more. We also will have a brand new Microsoft.SqlServer.DacFx.Projects nuget package that supports programmatically editing SQL projects wherever you want, including CI/CD pipelines!
Myth #4 Azure Data Studio runs slower on Mac and the functionality for the Linux-based version is much less compared to the Windows version.
If you’re using macOS or Windows on a machine with arm64 processors, make sure you are using the native build of Azure Data Studio, highlighted with the yellow arrows in the screenshot below, for optimal performance.
Azure Data Studio Install Packages
In the most recent release, ADS 1.42, we added native arm64 SqlToolsService support for arm64 Windows and macOS. Fun fact: the SqlToolsService (often abbreviated STS) is what provides SQL Server support in Azure Data Studio.
Further, the functionality in Azure Data Studio is the same across all platforms: Windows, Linux, and macOS. There is no feature or capability that is “different” or “less” for any operating system.
Myth #5 In Azure Data Studio, on the Connections pane, the Servers are currently at the database level, so each database has to be connected separately. Ideally, the full Server could be traversed within the tree (similar to SSMS).
In Azure Data Studio, you have the option of connecting to a server (e.g. on-premises or SQL in an Azure VM) or connecting to the database directly. You configure this within the connection. In the first screenshot, we connect to a local server (SQL2022 is a named instance):
Screenshot of connection configuration for server
Note that we named the connection “Connect to server”. Next, we’ll create a connection for a specific database, WideWorldImporters:
Screenshot of connection configuration for database
This connection is named, creatively, “Connect to database.” When we look in the Servers view, we see both connections:
Screenshot of server view with server and database connections
The "Connect to database" connection only shows Tables, Views, etc. in Object Explorer for the WideWorldImporters database, while the "Connect to server" connection shows the Databases node, as well as other server-level nodes, just as you see in SSMS.
Myth #6 The hardcoded 45 second object explorer timeout is a huge pain in my *readacted* and a horrible design choice. No competent engineer would implement something like that for a SQL server interface.
Good news! Thanks to our competent engineers ;) we added the ability to increase that timeout in Azure Data Studio 1.42. Within Settings ( CTRL/CMD + , ) search for expand timeout to see both an Object Explorer timeout setting, as well as one for the Server tree:
Screenshot of timeout settings
Modify those as needed! There is also a new Command timeout in the Advanced pane of the Connection dialog, just below the Connect timeout.
Myth #7 One of the key tools I still use from the SSMS program that is missing (as far as I can tell) from Azure Data Studio is the ability to use the table designer to modify existing tables.
Table Designer for Azure Data Studio was GA’d in November’s 1.40 release, and an existing table can be modified using Table Designer. Simply right-click on the table and select Design:
Screenshot of right-click option to modify design of an existing table
Table Designer will open, and from there you can add a column, add an index, create a new constraint, and more!
Myth #8 Grouping of schemas has unfortunately still not been implemented, otherwise very modern.
We could go on, but in the interest of chocolate that may melt soon, we’ll wrap it up. We hope this has been helpful for those of you that use any of the SQL Server tools, and feel free to add a comment below if you know of other myths that need clarification. As any good DBA would say, “Trust, but verify.”