Using SSMS or Azure Data Studio for SQL Server on Linux

The SQL Server on Linux release is a milestone for the open-source world, particularly for those organizations that run their entire business on Linux but want to use SQL Server. It now means they can leverage a powerful, enterprise-level relational SQL Server database on Linux.

In the below-listed articles, we explored SQL Server on Linux, its comparison with Windows SQL, and the support of ClusterControl for standalone SQL Server on Linux.

• ClusterControl now supports SQL Server 2019
• Install SQL Server on Linux using ClusterControl
• Installing SQL Server 2019 on RedHat Linux
• SQL Server On Linux Installation and Configuration
• Overview of SQL Server on Linux requirements and comparison with Windows SQL Server
• Introduction to SQL Server on Linux
• Enabling Transparent Data Encryption (TDE) for SQL Server on Linux

Once you’ve installed SQL Server on Linux, you can then choose to use one of the popular client tools — SQL Server Management Studio (SSMS) or Azure Data Studio (ADS) — to manage your SQL Server database.

This article will walk through the features of both SSMS and ADS to hopefully help you choose the appropriate tool for your specific use case.

SQL Server Management Studio

SQL Server Management Studio, popularly known as SSMS, is a standalone client utility for most database administration and development work. It is the best option if you are looking for a graphical tool (GUI) running on Windows.

Download Options

Microsoft provides the regular releases for the SSMS, and you can download and install the latest general availability (GA) SSMS from the Download SQL Server Management Studio (SSMS).

The latest SSMS GA version (as of January 1, 2022) is SSMS 18.10, released on October 5, 2021.

Note that SSMS only runs on a Windows operating system. It means you can install it on a Windows machine and connect to SQL Server on Linux remotely.

The most helpful features of the SSMS tool include:

• Always On Dashboards: SSMS has built-in rich graphical dashboards for viewing availability group health, data transfer, synchronization states, availability group failover, and availability group configurations. If you’ve configured an availability group for high availability or disaster recovery, you can utilize these dashboards for your monitoring.
• Query Store: Query store enables users to view the historical behavior of queries, their execution plans, and force the best execution plans for optimized performance. The SSMS query store represents data in a graphical format to investigate performance issues quickly.
•  SQL Server Agent: SQL Server agent configurations, jobs, alerts, operator, and proxy configurations are available in SSMS for scheduling and automating various tasks.
• Maintenance Plans: The Maintenance Plans allow configuration of index maintenance, statistics, CHECKDB, backup, and various other tasks for regular database maintenance.
•  Live Query Statistics: The live query stats are helpful to monitor a long-running transaction. You can view the live query execution plan while the query is running. It helps you find bottlenecks or steps in query execution most of the time.
• Server Configuration: You can configure SQL Server instance configurations with the SSMS server or database property windows.

Azure Data Studio

Azure Data Studio (popularly known as ADS) is also a standalone tool that runs on Windows, macOS, and Linux. Therefore, if you need to install the ADS on the Linux servers and connect locally, Azure Data Studio is the best choice.

Microsoft Azure Data Studio gives you a rich experience with the following exciting features:

• IntelliSense, an intelligent code completion feature that reduces typing, provides quick access to syntax information, and makes it easy to view the delimiters of complex expressions.
• Code snippets from standard .NET libraries.
• Code completion for SQL statements and table schema.
• Support for all major source control systems like Git or SVN.
• An integrated terminal for PowerShell script execution
• Easy customization of dashboards so you can quickly keep track of important metrics.

Azure Data Studio Installation

You can download and install the latest ADS here. The latest Azure Data Studio version is 1.34.0, released on December 15, 2021.

Note: The latest SSMS 18.7 installs the Azure Data Studio automatically on Windows.

The valuable features of the Azure Data Studio include:

• Dashboards: ADS has built-in server and database dashboards such as SQL instance edition, version, database sizes (used and free space), and backup statistics. You can also customize a dashboard that can present t-SQL query results into a dashboard.
• Quick data export to CSV, JSON, or Excel: We can export results quickly into the CSV, JSON, Excel, or XML format using ADS. Azure Data Studio also supports pie, line, bar, scatter, and plot charts to visualize results without complex configurations.
• Source Control: Azure Data Studio has integrated Git source control functionality. The source control is helpful for developers in managing their source code and releases and using a centralized code repository.
• Extensions: Azure Data Studio can be customized using the extensions available in the marketplace. You can install extensions for your specific requirements and start using them. The popular extensions in ADS are:
• Admin Pack for SQL Server extension – The admin pack extension combines the SQL Server Agent, Profiler, Import, and DACPAC files.
• Azure SQL Migration – The Azure SQL Migration lets you migrate your SQL Server instance to Azure SQL.
• Managed Instance Dashboard – The Azure Managed Instance dashboard provides reports for Managed Instance properties, CPU/storage usage, the status of database replicas, and storage usage for local SSD storage and remote Azure Premium storage.
• PowerShell – This extension provides rich PowerShell language support for ADS that helps write and run PowerShell scripts using the excellent editor.
• SQL Server Agent – This extension enables you to manage and troubleshoot SQL Server agents.
• SQL Server Schema Compare – You can use the schema compare extension for comparing DACPAC files and databases and apply the changes from source to target.
• Server Reports – You can use server reports extensions for DB Space Usage, DB Buffer Usage, CPU Utilization, Backup Growth Trend, and Waits.
• Jupyter Notebooks – The ADS supports Jupyter interactive notebooks for SQL, Python, Spark, or Scala scripts. They can have the code, text, and results in a single notebook. You can specify connection details for executing scripts and sharing the results. It also supports Jupyter books that can embed multiple Jupyter notebooks altogether.

Wrapping Up

You can use either Azure Data Studio or SQL Server Management Studio to manage your SQL Server database. Which one to choose depends on your specific requirements and usage scenario. Azure Data Studio is an excellent choice for those primarily editing and executing queries. In contrast, SQL Server Management Studio is the best option if you’re looking for a graphical tool (GUI) to run on Windows.

Take time to explore both feature sets to better understand whether ADS or SSMS are the best fit for you.

For a proper overview of running SQL Server on Linux, check out this Introduction to SQL Server. ClusterControl now supports SQL Server 2019, so you can be sure that we will be posting even more helpful articles in the near future. Be sure to subscribe to our blog, RSS feed, and follow us on LinkedIn and Twitter to stay in the loop, and we will see you in the next one.