SQL Operations Studio – Comprehensive guide to the new database DevOps tool!

SQL Operations Studio - logo croppedSQL Operations Studio is a new tool created by Microsoft to make life easier for Database Administrators and Developers. It was first announced at the beginning of November this year, during PASS Summit conference in Seattle. Two weeks later on November 15th, the preview version was made available to the whole SQL Server community. The tool is available on three platforms (Windows, Linux, macOS) and can be downloaded for free here: Download and install Microsoft SQL Operations Studio (preview).

In this blog post, I’m going to describe SQL Operations Studio features.

Content:

  1. What is SQL Operations Studio?
  2. Installation
  3. First look at SQL Ops Studio
  4. Color Themes
  5. T-SQL editor
  6. Go to Definition
  7. Peak Definition
  8. T-SQL Code snippets
  9. SQL Query Execution Plan
  10. Results grid
  11. Status Bar
  12. Server and Database Dashboards
  13. Widgets
  14. Integrated terminal
  15. Reporting issues
What is SQL Operations Studio?

SQL Operations Studio (called also SQL Ops Studio) is new, free, and lightweight data management tool that gives you a possibility to work with SQL Server, Azure SQL DB, and SQL DW from platforms like Windows, Linux, and macOS. It offers a consistent experience across databases of your choice on your favorite operating systems. It is an open source project with source code available on GitHub. Users can contribute to SQL Operations Studio via pull requests from the repository:
https://github.com/Microsoft/sqlopsstudio.

SQL Ops Studio is built on top of Visual Studio Code shell using Electron framework. It is connecting many various functionalities from SQL Server Management Studio (SSMS) and SQL Server Data Tools (SSDT). This tool gives administrators a more insightful view of the SQL Server state.  The goal is to have one common tool for DEV and OPS users. See a slide photo shared on Twitter:

Increase your productivity with smart T-SQL code snippets and customizable dashboards to monitor and quickly detect performance bottlenecks in your SQL databases on-premises or in Azure. You’ll be able to leverage your favorite command line tools like Bash, PowerShell, sqlcmd, bcp and ssh in the Integrated Terminal window.

Does it mean in future this will replace SSMS? According to Microsoft, there is no such plan on their roadmap.

Installation

Installation is very simple. The only thing you need to do is to download a package and extract it. Just one piece of advice – on Windows use 7-zip instead of built-in functionality, it will save you some time.

First look at SQL Ops Studio

SQL Ops Studio window is divided into few section. On the left, you can find Activity Bar which allows you to switch between possible contents of Side Bar. You can explore servers list, check task history, view open editors and files, search in them, and manage your changes in Git repository.

SQL Operations Studio - First look

A major part of windows is used by tabs with opened editors and dashboards. That is the main working area which I’m going to describe more in detail later.

At the bottom of the window, you can find Status Bar which displays most important information about connection and edited file.

Color Themes

As you probably already noticed SQL Operations Studio has Dark theme available. One of the most desired features not implemented in SSMS is implemented here. But that is not all. SQL Ops Studio has many more available color themes.

You can change your color theme in the following way:

  1. Press Ctrl + Shift + P
    SQL Operations Studio - Color Theme 1
  2. Type ‘Preferences’ and choose ‘Preferences: Color Theme’ from the drop-down list (or use Ctrl+K, Ctrl+Tshortcut).
    SQL Operations Studio - Color Theme 2
  3. Choose the Color Theme you want to have.
    SQL Operations Studio - Color Theme 3

Here you can check how each color theme looks (click to enlarge):

If that is insufficient, you can install themes from VS Code Marketplace or even make your own color customizations. To do this you need to use workbench.colorCustomizationssettinguser setting. Settings file is stored in your local roaming application data folder (C:\Users\<your_user_name>\AppData\Roaming\sqlops\User\settings.json). To open up settings.json file in editor press Ctrl + ,or Ctrl + Shift + P and search for ‘Preferences: Open User Settings’. With little help of IntelliSense you can change you can adjust very quickly. To see results you just need to save your settings.json file. Changes are reflected immediately.

SQL Operations Studio - Color Theme customization

Additional details and examples are available here.

T-SQL editor

After connecting to SQL Server and opening New Query window you should see such editor. If you have experience with SSMS, then it should look familiar.

SQL Operations Studio - TSQL editor - blankOn the top of the tab is a bar which provides additional options.

SQL Operations Studio - TSQL editor - bar

It lets us:

  • execute a query,
  • cancel running one,
  • connect to,  disconnect from, or change existing connection to the server
  • change database context,
  • show estimated query execution plan.

To execute a query you just need to write it and hit F5 key or press Run button. Built-in IntelliSense can make writing much easier.

SQL Operations Studio - IntelliSense

In addition to IntelliSense which is already very known functionality, SQL Operations Studio delivers some new great features.

Go to Definition

You know this feeling while writing SQL Query when you need to check table definition? In SSMS there is no comfortable way to do it. You can look for a table in Object Explorer or select table info using Alt+F1. If you used SSDT you know, there are better solutions already known for a long time for application programmers. What if you could highlight the object and with one-click open its definition in a new tab? Now you can do it! You just need to highlight object name and press F12 or right-click and choose ‘Go to Definition’ option from context menu.

SQL Operations Studio - Go to Definition

When you do it. SQL Ops Studio looks for definition in the database and displays it in a new tab. To use this functionality, you need to be connected to the database. It doesn’t look into SSDT project files.

Peak Definition

If you find Go to Definition feature very useful, but you would like to have a more temporary view of objects source code, then no problem! SQL Ops Studio delivers second functionality which gives you the possibility to check object definition. This time, the source code is displayed in the same editor tab.

SQL Operations Studio - Peek Definition

It works quite similar to the Go to Definition function but seems to be quicker and designed for ad-hoc, one-time checks. Requirements are the same as for previous functionality. You need to be connected to the database. It doesn’t work with SSDT projects (yet?).

T-SQL Code snippets

Above functionalities can increase productivity a lot. You can speed up your development even more with build in T-SQL Code snippets – templates which make it easier to create databases and database objects. You just need to type sql in the query editor. That will open list of available snippets. Select the snippet you want to use, and it generates the proper T-SQL script. Then you can update it with the wanted values.

SQL Operations Studio - Snippets

To replace values in many places in a script you can use ‘Change All Occurrences’ (Ctrl+F2) functionality from the context menu.

SQL Query Execution Plan

Another feature well known from SSMS is ‘Show Estimated Execution Plan’. You can display it by clicking ‘Explain’ button on the bar.

SQL Operations Studio - TSQL editor - Explain

Unfortunately, as for now, there is no option to display Actual Execution Plan.

Results grid

After query execution, its results are displayed in Results Grid. A new panel appears below query editor. In addition to what we know from SSMS, SQL Operations Studio delivers users quite a nice feature. Now, not only can we see the results, but also we can also easily save them as CSV, JSON, or Excel file with just one click. But that’s not all. If that was not enough we get the possibility to display results even as a Chart.

SQL Operations Studio - TSQL editor - GridBelow you can see example chart:

SQL Operations Studio - TSQL editor - View as Chart

In my opinion, this is a great feature. With a little practice, it can mean the end of copy-pasting to Excel to get basic charts. In addition, in many cases, visualizations are more descriptive than tables with numbers.

Generated charts can be copied to the clipboard or saved as PNG file.

Status Bar

At the bottom of SQL Ops Studio window, you can see Status Bar. It delivers very useful information about currently used (checked-out) git branch, number of problems in the edited script, active database connection, or editor properties.

SQL Operations Studio - TSQL editor - Status Bar

The Status Bar doesn’t have the informative role only. It’s very interactive – almost every piece of displayed information is a button, which can be used to change something. Clicking active branch allows you to check out different branch or tag from the same repository. Icon with two arrows triggers git synchronization. Problems count toggles pane with a list of issues. Clicking on current cursor position lets you go to the specified line in the opened file. Indentation can be changed to spaces or tabs (sized 1-8). From here you can as well change file encoding and End of Line sequence. Smiley face at the end provides you a possibility to quickly tweet feedback.

Server and Database Dashboards

In addition to these all wonderful functionalities, SQL Ops Studio introduces dashboards. Concept a little bit similar to reports from SSMS, but more dynamic and much more configurable. To open dashboard you need to right-click on an instance or database name and choose “Manage” from the context menu. By default, dashboards display few general pieces of information and provide basic widgets which visualize current server and database state.

Server dashboard

SQL Operations Studio - Server Dashboard

Database dashboard

SQL Operations Studio - Database Dashboard

From the dashboard, you can quickly perform database backup or restore operations, search for database or table, check the size of the biggest databases or tables, and select TOP 100 rows or edit data in the chosen table. Each dashboard can be tweaked with additional widgets you can create.

Widgets

Widgets are the best thing in dashboards. SQL Operations Studio allows us to create our own customized view on SQL Server instance and the database state.

You can create your own widget in just a few simple steps. My example will be based on a famous Wait Statistics query created by Paul Randal (b|t). You can find it here: https://www.sqlskills.com/blogs/paul/wait-statistics-or-please-tell-me-where-it-hurts/

  1. Firstly I had to comment out part of the SELECT statement to return only basic data like WaitType and Percentage.
    SQL Operations Studio - Widget 01It returns now only two columns of data.
    SQL Operations Studio - Widget 02The script needs to be saved in a file on the disk. I named my file WaitStats.sql.
  2. In the second step, you need to decide in what visual form you would like to see it on your dashboard. For such kind of data, pie chart looks pretty well.
    SQL Operations Studio - Widget 03However, I decided to use more standard horizontal bar chart because it additionally provides values for the first look.
    SQL Operations Studio - Widget 04
  3. When you already made a decision how your data should be presented, then you can generate widget code by clicking [Create Insight] button.
    SQL Operations Studio - Widget 05
  4. Now, the next step is to modify settings.json file. As you already know, you can open it with the keyboard shortcut Ctrl + ,. Then you need to search for the server.widgets section. This whole section needs to be added to your user settings. You can do it easily with ‘Copy to Settings’ option.
    SQL Operations Studio - Widget 06When the section is added you can modify it by adding code generated for your widget.
    SQL Operations Studio - Widget 07
  5. Now you can save the configuration file, open server dashboard and see results of your work.
    SQL Operations Studio - Widget 08Isn’t that great?! We have a possibility to create our own widget in just a few very simple steps. But that’s not all.

  6. Every widget can deliver even more detailed data. Let’s assume that we would like to have quick access to more details about wait types. The first thing we need to do is to prepare a query which will select this data for us. In our example, it will be the original Wait Stats query from Paul Randal. This time with all columns in the resultset.
    SQL Operations Studio - Widget 09As with the main query we used in the widget this one needs to be saved in a file as well. I named my file WaitStatsDetails.sql.
  7. In next step, we need to modify settings file one more time by adding “details” object.
    SQL Operations Studio - Widget 10After saving modifications and opening the dashboard, you have a new option available. After clicking on ellipsis icon on the widget and choosing ‘Show Details’ option, a new details pane will show up.
    SQL Operations Studio - Widget 11SQL Operations Studio - Widget 12In this pane, you can browse all detailed data returned by the query. That is it! Simple and efficient way to create helpful widgets for your dashboards.
Integrated terminal

SQL Operations Studio provides built-in terminal. In fact, its three types:

  • Command Prompt
  • PowerShell
  • Git Bash

The easiest way to open terminal is to use the Ctrl+`shortcut. It also can be enabled from VIEW menu or by typing ‘toggle terminal’ in Command Palette.

SQL Operations Studio - Open Terminal 01 SQL Operations Studio - Open Terminal 02

When you do it, a new panel with the terminal is displayed.

SQL Operations Studio - TerminalBy default, SQL Ops Studio opens Command Prompt in its built-in terminal. This behavior can be changed. To do it, you have to type ‘terminal default’ in the Command Palette and choose ‘Terminal: Select Default Shell’. This will give you the possibility to choose which of terminal shells you want to have set as default one.

SQL Operations Studio - Terminal - default shell 01 SQL Operations Studio - Terminal - default shell 02Additional functionality is opening up to as many terminals as you really need. For these of you who extensively use terminals, this may be the very convenient feature.

SQL Operations Studio - Terminal - three types

Reporting issues

Currently, SQL Operations Studio was released as a Preview version. Microsoft uses issues on Github to collect feedback from users. The tool is very popular and many of users already reported defects they found or requested some additional features. Right now there are already more than 300 reported items. If you find it useful, but desired functionality is missing or not working correctly, do not hesitate to report that or vote up the already reported item. SQL Ops Studio has great potential and your feedback can help to make it an exceptional tool.

-Marek

Share it:
Facebooktwittergoogle_plusredditpinterestlinkedinmailFacebooktwittergoogle_plusredditpinterestlinkedinmail

One thought on “SQL Operations Studio – Comprehensive guide to the new database DevOps tool!”

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.