Installing Extensions in SQL Operations Studio

Problem

SQL Operations Studio is an open-source tool that allows us to works on cross-platform operating systems to support SQL Server. It works on both Windows and Linux operating systems. SQL Operations Studio also allows for customizing and adding features using extensions. In this tip, we will learn about extensions and how can we make use of them.

Solution

SQL Operations Studio is a free, lightweight tool for developers and administrators for SQL Server on Windows, Linux and Docker, Azure SQL Database and Azure SQL Data Warehouse on Windows, Mac or Linux machines.

Before you go further, you can go through some of the previous tips:

• SQL Operations Studio Installation and Overview
• SQL Operations Studio query editor and source control
• Customizing dashboards in SQL Operations Studio
• SQL Server Operations Studio where does it fit in

SQL Operations Studio is currently in the preview and below are some of the available releases.

Recently, I attended webinar held by the SQL Operations Studio build team.  The SQL Operations Studio build team shared information about general availability release of this tool. While writing this tip, I used the June Public Preview release of SQL Operations Studio. We can expect the general availability release of SQL Operations Studio in the coming months.

Extensions in SQL Operations Studio

Extensions in SQL Operations Studio provides the ability to add more functionality and features to SQL Operations Studio.  Since SQL Operations Studio is an open source tool, the Microsoft tools team as well as users (3rd party) can build extensions for SQL Operations Studio.

Once we launch SQL Operations Studio, go to the ‘Activity bar’ on the left side and we can see the Extensions icons.

In previous versions of SQL Operations Studio, the extensions icon was not enabled by default. To enable, right click on the activity bar and select Extensions.

Clicking on the extensions icon, it opens up Extensions: Marketplace.

We can see several extensions listed in the marketplace with the * icon. If we look at the items with * symbol, we can see these extensions are recommended by SQL Operations Studio.

As you know, SQL Operations Studio is open source; therefore, users can develop extensions and get them listed here.  SQL Operations Studio works on multiple operating systems, so the extension should also be compatible with these other Operating Systems.

Below are the extensions available in the SQL Operations Studio June release.

View details about SQL Operations Studio Extensions

If we want to view more details about a particular extension for SQL Operations Studio, click on it. This opens up new window and gives information about it with screenshots.

For example, if I click on Server Reports extension, it shows the below information.

We can also see the contribution in the creation of the extension.

Install SQL Operations Studio Extension

In this demo, we will install and review below extensions

1. Server Reports
2. Whoisactive
3. Redgate SQL Search

Install Server Reports extension in SQL Operations Studio

Let’s install the Server Reports extension for SQL Operations Studio. In order to install, click on that particular extension and click on Install.

We can also install the extension from the details.  To do this, click on the Install icon on the details page as shown below.

The status for that particular extension changes to Installing from Install.

Once the installation is complete, the extension status changes to reload. We need to click on Reload in order to implement that particular extension.

Once we click on Reload, it refreshes the SQL Operations Studio in order to implement the changes.

We can also see the extension in the Installed section. All installed extension will be reflected in the Installed section.

Now in order to view the extension, right click on the extension followed by Manage.

We can see two tabs on the top.

1. Home
2. Server Reports

In the Home tab, it shows the Server Dashboard showing the below information:

• Server Dashboard
• Tasks list
• Search
• Backup Status

Now click on SERVER REPORTS to see the extension details.

This shows the SERVER REPORTS extension. Under the Server Reports extension, on the left side it shows two icons:

1. Monitor
2. Performance

Under the Monitor section we can see the below charts.

• Top 10 DB Space Usage

• Top 10 DB Buffer Usage

• CPU Utilization and Backup Growth Trend

Now click on the ‘Performance’ icon on the left side and we can see Wait Counts by Paul Randal. This shows top waits in descending order.

Click on the top right side and select Show Details to see more details.

We can see the below details page after clicking on Show Details.

Install WhoIsActive extension in SQL Operations Studio

Click on the whoisactive extension and it opens up the details page as shown below.

The details page also provides basic information related to the extension as shown below:

We need to follow the below steps to use this extension.

1 – Install sp_WhoIsActive stored procedure.

2 – Install spwhoisactive extension.

Now install the spwhoisactive extension by clicking install and reloading the extension using the marketplace. We can see the sp_whoisactive extension in a new tab as shown below.

We can see following graphs in the whoisactive extension

• Top 10 CPU Usage
• Top 10 CPU Delta

Apart from these, we also get a Tasks tab with the following items:

• Whoisactive: Documentation: Once we click on this, it opens up the online documentation
• Whoisactive: Install: Opens the download page of sp_whoisactive
• Whoisactive: Get plans: Once we click on this, it opens up new query editor with below command to display results
  • Exec sp_whoIsActive @get_plans=1
  • Here @get_plans = 1
  • This gives you the execution plans for running queries.

• WhoisActive: Find leader of block:  This opens up a new query window with the query to show the lead blocker along with the execution plan.

Install Red Gate SQL Search Extension

The Redgate SQL Search extension is used to quickly search for objects in a SQL Server database. We can check the documentation after clicking on the Redgate SQL Search extension as shown below.

Below are the steps to install SQL Search for SQL Operations Studio.

1.  Install SQL Search extension
2.  Install .Net Core Runtime

Step 1: Install the SQL Search extension

In order to install the SQL Search extension, click on Install. This opens up the Foundry download page to download the Redgate SQL Server extension for SQL Operations Studio.

Click on Public download and it download the ‘Redgate Foundry – SQL Search Extension.vsix’ file.

Note the extension of the downloaded file is .vsix, in order to install it we can use two ways.

• Go to File > Install extension from VSIX package

• Alternatively, we can type the shortcut ctrl+shift+p and type Extensions and choose Extensions: Install from VSIX

Now go to the downloaded file path and select ‘Redgate Foundry – SQL Search Extension.vsix’.

Once installed, it gives the below message. Click on reload to make the extension active.

We can also see Redgate SQL Search in the Installed section of the Marketplace.

In the Details page it shows options to disable or uninstall SQL Search.

Step 2: Install the .Net Core Runtime

We also need to install the .Net Core Runtime in order to use this extension. Click on the link from the details page or we can do an internet search to find it.

If .Net Core Runtime is not installed, it shows the below error message.

Download the .Net Core runtime set up.

Double-click on the downloaded file to launch set up and accept the license terms and conditions.

It starts the installation of the Microsoft.Net Core 2.1.1 Windows Server Hosting.

Once the .Net Core Runtime setup is complete, in the command palette search for SQL Search: Search in Active connection.

We can search the object we want. For example, if I want to view objects for PurchaseOrders enter the keyword and then press enter to search.

We can see the results as shown below for all objects such as stored procedures, tables, foreign key constraints, etc.

In the options tab, click on the … icon and you get two options.

Reveal in Object Explorer, takes you to object explorer and highlights the object.

View definition opens up a new tab with the definition of the object.

As we can see SQL Operations Studio extensions provide greater flexibility and features to use with SQL Server.

Next Steps

• Explore extensions in SQL Operations Studio.
• Stay tuned for further tips on extensions that will explore SQL Server Agent, profiler extensions, etc.
• Download and install SQL Operations Studio (preview)
• Review the release notes of different versions of SQL Operations Studio.