SQL 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:
- What is SQL Operations Studio?
- Installation
- First look at SQL Ops Studio
- Color Themes
- T-SQL editor
- Go to Definition
- Peak Definition
- T-SQL Code snippets
- SQL Query Execution Plan
- Results grid
- Status Bar
- Server and Database Dashboards
- Widgets
- Integrated terminal
- 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:
My one photo of a slide I took, in case the slide deck is not available, from the DevOps with SQL Server 2017! Many thanks @sqldatatools! pic.twitter.com/UwDDxtukYm
— Jason E Bacani (@JEBacaniSQLDude) November 2, 2017
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.
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:
- Press
Ctrl + Shift + P
- Type ‘Preferences’ and choose ‘Preferences: Color Theme’ from the drop-down list (or use
Ctrl+K, Ctrl+Tshortcut).
- Choose the Color Theme you want to have.
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.
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.
On the top of the tab is a bar which provides additional options.
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.
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.
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.
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.
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.
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.
Below you can see example 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.
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
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/
- Firstly I had to comment out part of the SELECT statement to return only basic data like WaitType and Percentage.
It returns now only two columns of data.
The script needs to be saved in a file on the disk. I named my file WaitStats.sql. - 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.
However, I decided to use more standard horizontal bar chart because it additionally provides values for the first look.
- When you already made a decision how your data should be presented, then you can generate widget code by clicking [Create Insight] button.
- 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 theserver.widgetssection. This whole section needs to be added to your user settings. You can do it easily with ‘Copy to Settings’ option.
When the section is added you can modify it by adding code generated for your widget.
-
Now you can save the configuration file, open server dashboard and see results of your work.
Isn’t that great?! We have a possibility to create our own widget in just a few very simple steps. But that’s not all. - 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.
As 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. - In next step, we need to modify settings file one more time by adding “details” object.
After 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.
In 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.
When you do it, a new panel with the terminal is displayed.
By 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.
Additional 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.
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:
All of my questions settled-thanks!