By: Mohammed Moinudheen | Updated: 2021-12-17 | Comments (2) | Related: > Azure Data Studio
Problem
There are a number of extensions available for Azure Data Studio (ADS) and in this article we will cover some of the popular ones that can be used for the purpose of SQL Server database administration.
Solution
As we know, both SQL Server Management Studio (SSMS) and Azure Data Studio are both Microsoft products.
Azure Data Studio formerly known as SQL Operations Studio was first released in 2017. Over the last few years, it has gained popularity as a cross platform tool and used mainly by developers due to several advantages over using SQL Server Management Studio (SSMS).
SSMS was first released in 2005 and is a must have tool for all SQL Server professionals due its maturity and embedded advanced functionalities especially around database administration which is not yet available in Azure Data Studio.
In this tip, we will review some extensions that can be used within Azure Data Studio that will help in performing SQL administration tasks. Azure Data Studio can be downloaded from this link. Make sure to select the correct install file based on your operating system. As this is a cross platform tool, Azure Data Studio can be installed either on Windows, macOS or Linux. However, if you already have the latest SSMS installed, you don't need to separately install Azure Data Studio as starting with SSMS 18.7, Azure Data Studio comes with the SSMS installation. You can check it from this screenshot.
Installing Azure Data Studio
Installing Azure Data Studio is simple and straight forward across all platforms. You can refer to this tip which describes the various functions available within Azure Data Studio. As you may know, Azure Data Studio is a light weight tool with minimum system requirements required for installation. Azure Data Studio when it is first installed may not have everything you need for your day-to-day activities. However, you can make use of additional functionalities by installing extensions. The extensions that are available are either provided by Microsoft or by the community. Make sure to validate third party extensions before installation and only install them if it is required.
In the next sections, we will explore some of the extensions which are quite useful from the database administration standpoint.
Install Extensions on Azure Data Studio
The option to install extensions is available on the left-hand tab as shown below. As you can see, there are no extensions installed for this base installation. If you click on the Marketplace option, you can review the available extensions.
Click on Marketplace to view the available extensions.
From the extension details in the Marketplace, you can see if the extensions are from Microsoft or somewhere else. You will also see additional details like below.
Popular extensions for SQL database administration on Azure Data Studio
As you can see, when you compare SSMS and Azure Data Studio for routine database administration tasks, the functionality appears to be limited on Azure Data Studio. You can explore the administration specific extensions by first exploring if any administration related extensions are available by just searching using the 'Admin' keyword. In this article, the demo is using Azure Data Studio installed on a Windows OS.
As you can see, there are two database administration specific extensions. Let us explore both extensions.
Admin Pack for SQL Server (Microsoft)
Microsoft has made it easier for us to use this extension as it is a collection of popular database administration specific extensions. Instead of installing these extensions one by one, our task is made easier as we only need to install this administration pack once and it includes the extensions for below items as well.
- SQL Server Agent
- SQL Server Profiler
- SQL Server Import
- SQL Server dacpac
Click on the Install option to install the admin extension pack. You may need to reload Azure Data Studio for the changes to take effect. Installation is quick and completes in seconds. Once done, you will see all the extensions installed in the admin pack.
In order to validate these extensions are installed, you can right click on the server in the Connections tab as shown.
Here, you can see the options that are available to use after installing the extensions. There is the 'Data-tier Application wizard', 'Import Wizard' and 'Launch Profiler' options. You will see the 'Data-tier Application wizard' and 'Import Wizard' options in the individual database nodes as well.
In order to validate the 'SQL Server Agent' extension, right click on the server and click on the 'Manage' option. You can see the 'SQL Agent' option under 'Administration' as shown.
Describing each of the individual options is beyond the scope of this tip. However, you can see how easily administration related functions can be added to Azure Data Studio.
Database Administration Tool Extensions for Windows
As this demo is using Azure Data Studio installed on a Windows OS, you can install this extension as well. This extension enables some SSMS features within the Azure Data Studio like the 'Properties' and the 'Generate Scripts' options.
Once this extension is installed, you will be able to right click on a server or database node to view the 'Properties' option. The 'Generate scripts' option will be visible on the database nodes as shown.
Once you click on either option, the experience will be similar to SSMS as shown below for the Database Properties window.
Central Management Servers
A very common tool used by DBA's is the Central Management Servers where multiple servers can be organized into groups and tasks like running T-SQL statements can be executed at once on these server groups. This can be easily done using SSMS by clicking on the 'Registered Servers' option in the Tools menu.
There is an extension available for this feature in Azure Data Studio by the same name.
Once this extension is installed, you will be able to view it separately once you click on the 'Connections' icon.
You can configure the Central Management Servers and then configure the server groups as per your requirements. All of the management tasks related to Central Management Servers which can be done in SSMS can also be done using Azure Data Studio.
Whoisactive
This is a popular tool used by DBAs for activity monitoring. It is basically a stored procedure that can be downloaded and installed on your SQL Server and is very handy especially when dealing with issues on a production system. The procedure is written by Adam Machanic and is available as an extension on Azure Data Studio. You can refer to this tip to learn more about sp_whoisactive.
Install the extension and when you click on the 'Manage' options in the connection tab, you will be able to see sp_whoisactive under the monitoring section. However, there will be no data collected yet.
For this to work, download the sp_whoisactive stored procedure and install it on the SQL Server. You can also click on the top right-hand corner to 'Install sp_whoisactive'. Once the stored procedure is successfully installed, refresh the section under 'Monitoring' to see the updated dashboard as shown below.
Server Reports
This is another useful Microsoft extension which provides performance related server insights for both current as well as historical activity. The report queries used in this extension are originally from Microsoft MVPs – Paul Randal, Glenn Berry, Aaron Bertrand.
Once this extension is installed, you will be able to view the performance specific dashboards. This will give you an experience similar to that of SSMS. You will be able to view the reports by clicking on the 'Manage' option under the 'Connections' Tab. There are also reports to view reports related to tempdb contention.
SQL Server Schema Compare
This extension will help to easily perform schema comparisons between a dacpac file and a database. Based on the comparison, any changes can easily be applied between the source and target instead of reviewing the schemas manually in the database.
Once this extension is installed, you will be able to see the 'Schema Compare' option when you right click on any database.
This extension is quite useful especially during database releases as schema comparisons can be done quite quickly. Once you click on the 'Schema Compare' option, you will see this window. On the left-hand side is the source which is a dacpac file and on the right-hand side is the on-premises database. Click on the 'Compare' option which will perform the schema comparison between the source and target.
As you can see, in this demo, there is one table not in the target. You can click on the 'Generate script' option to extract the script and run it on the target.
Third party extension- First Responder Kit
So far, we described Microsoft specific extensions. In this section, we will explore a third-party extension which is quite useful. You may be familiar with the 'First Responder Kit' from Brent Ozar. This is a collection of scripts and worksheets which is quite useful to perform health checks and analysis. This is available to download as an extension known as the 'First Responder Kit'.
This extension needs to be installed using the .vsix file which is available to download from this link. Once the vsix file is downloaded, you can install the extension from the 'Command Palette' which is available in the 'View' menu.
Once you click on 'Command Palette', you can install the extension by searching for the 'Extensions: Install from VSIX' option. Make sure to provide the path of the vsix file you downloaded earlier. There will be a warning asking you to confirm before you perform the install as this is a third-party extension. You can confirm to proceed with the installation.
Once this is done, you can search by using the 'First' keyword in the 'Command Palette'.
Make sure to first import the stored procedures using the import command option as shown and then run the stored procedure. You can also view the options to execute these stored procedures by right clicking on the server node or database node as shown.
A screenshot after running sp_Blitz is shown below.
Summary
In this tip, you saw a number of extensions that could be used for the purpose of performing database administration related tasks using Azure Data Studio. The extensions described in this tip will give you a start to perform your regular administration tasks using Azure Data Studio. Over the last few years Azure Data Studio has been evolving rapidly and could become as popular as the widely used SQL Server Management Studio (SSMS). But bear in mind, SSMS is quite a mature product compared to Azure Data Studio and it may take some time for all the functions available in SSMS to be fully available in Azure Data Studio. However, by making use of these extensions, we would get wider functions similar to that of SSMS. Another advantage is that these extensions are light weight and do not slow down your server. And these extensions are free to use. As you probably know this tip covers only a subset of popular extensions, there may be many more available that could be of great use for your day-to-day needs. You can review the other extensions that are available in the Azure Data Studio Marketplace or review the extensions available with this link.
Next Steps
- You can download Azure Data Studio separately from this link
- Review the extensions described in this tip
- Review the extensions available in the Marketplace, install as needed and test the functionality
About the author
Mohammed Moinudheen is a SQL Server DBA with over 6 years experience managing production databases for a few Fortune 500 companies.This author pledges the content of this article is based on professional experience and not AI generated.
View all my tips
Article Last Updated: 2021-12-17
