Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

Installing Extensions in SQL Operations Studio


By:   |   Last Updated: 2018-09-18   |   Comments   |   Related Tips: More > 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 is currently in the preview and below are some of the available releases.

Release Date Release Version Description
November 15, 2017 0.23.6 Initial release of SQL Operations Studio
December 19,2017 0.24.1 December Public Preview
January 17,2018 0.25.4 January Public Preview
February 15,2018 0.26.7 February Public Preview
March 28,2018 0.27.3 March Public Preview
April 25, 2018 0.28.6 April Public Preview
May 7, 2018 0.29.3 May Public Preview
June 20,2018 0.30.6 June Public Preview
July 19, 2018 0.31.4 July Public Preview
August 30, 2018 0.32.8 August Public Preview

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.

march

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.

sql query

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.

sql query

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

insights

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.

sql server agent

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.

S. No. Extension Name Extension description
1 Red Gate SQL Search Search across multiple databases
2 SQL Server Agent Manage and troubleshoot SQL Agent jobs
3 SQL Server Profiler SQL Server profiler for SQL Operations Studio
4 Server Reports Server reports for SQL Operations Studio provide server health insights
5 Whoisacive Sp_whoisactive configurations for SQL Operations Studio
6 SSMS keymap This extension ports popular SSMS keyboard shortcuts to SQL Operations Studio.
7 Azure SQL Data Warehouse Insights Azure SQL Data Warehouse Insights for SQL Operations Studio
8 MSSQL Instance Insights   This extension provides SQL Server Instance insights
9 AlwaysOn-Insights   This extension provides SQL Server Always On insights

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.

server reports
reports
cpu

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

contributions

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.

server reports

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

server reports

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

server reports

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

server reports

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

server reports

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

mssqltips

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

new query

We can see two tabs on the top.

  1. Home
  2. Server Reports
server dashboard

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.

server reports

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
space usage
  • Top 10 DB Buffer Usage
buffer usage
  • CPU Utilization and Backup Growth Trend
cpu utilization

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.

server reports

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

server reports

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

insights

Install WhoIsActive extension in SQL Operations Studio

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

who is active

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

documentation

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

1 - Install sp_WhoIsActive stored procedure.

sql query

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.

get plans

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.
if exists
  • 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.
exists

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.

redgate

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

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

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.

sql search

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

mssqltips

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
redgate
  • Alternatively, we can type the shortcut ctrl+shift+p and type Extensions and choose Extensions: Install from VSIX
extensions

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

redgate foundry

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

reload now

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

sql operations studio

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

redgate

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

Download the .Net Core runtime set up.

windows

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

microsoft

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

net core
net core

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

sql search

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.

purchase orders

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

object name

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

reveal

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

object name

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

purchase orders

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

Next Steps


Last Updated: 2018-09-18


next webcast button


next tip button



About the author
MSSQLTips author Rajendra Gupta Rajendra is a Consultant DBA with 9+ years of extensive experience in database administration including large critical OLAP, OLTP, Reporting and SharePoint databases.

View all my tips
Related Resources




Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Email me updates 


Signup for our newsletter
 I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



    



Learn more about SQL Server tools