SQL Operations Studio, what is it and why should I use it?

Tuesday, September 25, 2018 sql, sql server, sql operations studio, azure data studio

SQL Operations Studio, looking very similar to VIsual Studio Code!

Since the availability of a preview version of SQL Operations Studio (SOS, for short) was announced in November last year, It's been forever on my to-do list to download and take a look. Like Visual Studio Code it's based on Electron and is a light-weight alternative to SQL Server Management Studio. Whilst it's lighter, it's also cross-platform with released binaries for Windows, macOS and Linux, so a great tool for anyone, especially with the advent of SQL Server for Linux. It's telling that SOS looks very similar to Visual Studio Code, looking in Help > About calls out VS Code 1.26.1 which gives confirmation of its heritage.

Dark Theme

By default SOS starts in a light theme. I know Dark UI versus Light UI is almost as much of a controversy as tabs/spaces and braces on the same line/next line but I also know which one I prefer. Can you guess?

To change to the correct theme, hit CTRL-SHIFT-P to bring up the Command Palette and type the word theme into it to find the command to change the theme:

Using the SQL Operations Studio command palette to search for the theme configuration option

The command palette will also tell you when there's a keyboard shortcut mapped to the command you're looking for which is useful information for commands you're likely to perform often.

Now that the command we're looking for is in the search list, use the up/down arrow keys to navigate to Preferences: Color Theme and press ENTER to open the list of available themes (or use the mouse to click on the command). The list of themes displayed is helpfully divided into three categories (there may be options for more categories if you install additional themes that don't fit into any of them) which are light themes, dark themes and high contrast themes. The theme I've used while creating screenshots for this post is Dark SQL Operations Studio (default dark). One compelling reason to use the keyboard when looking at the list of available themes is that as you use the UP/DOWN arrow keys to navigate between them, SOS will immediately re-style itself to show you the theme that's currently under selection. Pressing ESC to dismiss the command palette will revert you to your previously selected theme.

Connecting to SQL Server

The big blue (if you're sensibly using the Dark Theme) 'Add Connection' button gets you connected to a SQL Server

The icon at the top-left of the window is the jumping off point for connecting to a server, clicking on it pops-out a pane that's similar in purpose to the Registered Servers tool window in SQL Server Management Studio whilst also being the place where you connect to a server, unlike the separate Connect to Server window in SSMS. Clicking the 'Add Connection' button brings up a pane on the right-hand side of SOS where you can enter connection details or manage saved connections.

The choices are slightly more limited for authentication types, only offering Windows Authentication or SQL Login unlike SSMS which offers options that let you connect using Multi-Factor Authentication and other goodies. The very interesting option that's shown is Connection Type which only has Microsoft SQL Server as an option at present. It could be assumed that this is to allow the addition of other connection types SSMS offers (i.e. Analysis, Reporting and Integration Services along with Azure Storage) but given the cross-platform nature of the product and the energetic support for a GitHub issue requesting multi-RDBMS support, fingers crossed...! There's also better support than SSMS offers for some of the more esoteric things you can set when connecting to SQL Server hiding behind the Advanced button.

Completing the relevant deails about your server and hitting Connect then brings up something quite awesome, the SERVER DASHBOARD, which I've capitalised because that's what it looks like in the user interface in a little but of a throw-back to Visual Studio 2012s capitalised menus.

The Server Dashboard

The first thing you see when you connect to a server is the Server Dashboard, which gives you a brief overview of some elements of the servers health, a search box (gotta be useful for those servers with hundreds of databases) and a couple of tasks

SQL Operations Studio showing the Server Dashboard for a new connection to a server

One of the tasks that's available is Learn How To Configure The Dashboard which takes you to a page on the ever awesome docs.microsoft.com that goes into a decent amount of detail (check out the linked just under the screenshot showing an example of a slightly fancier dashboard).

Going into more detail around tweaking the Server Dashboard is a post, or three, itself so I'll just mention that you can drag/drop to move pieces of the dashboard around by clicking on the pencil icon at the top-right of the screen, it's just out of shot in the screenshot above and doesn't look like it's actually associated with the dashboard due to its location. You'll know it's doing its thing when you click on it because each of the widgets on the dashboard will gain a X icon next to the triple-dot icon they already have at the top-right.

As well as the tutorials and information on docs.microsoft.com there are a few tutorials and guides to customising widgets like this one by David R. Longnecker. Conceptually it makes for an interesting idea, using SQL Operations Studio as the "shell" for monitoring solutions, especially for smaller organisations that may not have the budget to put in place large scale monitoring solutions.

For now though, the button I'm going to use from the dashboard is the 'New Query' one which, surprise surprise, opens a query window connected to the server.

Running Queries

A tool for making requests of a database wouldn't be much use without strong support for writing and otherwise manipulating queries. One of the things I spotted straight away is SOS supports snippets so you can type, for example sqlSelect and press tab to be given the template for a SELECT query as showin in the screenshot below. Be careful though, if you press tab too many times you'll be dropped out the end of the replaceable fields, unlike SSMS which cycles you through them repeatedly.

SQL Operations Studio showing a to be completed snippet of SQL

This is a nice to have feature, but what's even more surprising is that there's a decent amount of feature parity with bits of SSMS, the intellisense when typing seems complete, there's the ability to look at an execution plan (click on the 'Explain' icon that's at the top-middle in the screenshot), results can be saved to CSV/Excel/JSON, there even appears to be a built-in option to generate a chart from the data which comes with a UI surfaced hook to store the query as an insight/dashboard widget. All in all, it's a pretty good alternative to SSMS.

So far?

So far I've played around with SOS a little bit, and whilst it seems reasonable enough there's one glaring black mark against it at the moment which is memory use:

Memory use, as shown by Task Manager of SSMS and SOS

Ouch! Both SOS and SSMS have a single connection open to the same database server, with one query window open and the same query having been executed. The usual expectation would be that SSMS, being based on Visual Studio, would be the memory hog but it looks like it's gone dramatically the other wayin this instance! I know, I know, the numbers shown in Task Manager aren't the be all and end all of explaining what and how an app is using memory, but it's good enough for now. Ouch - hopefully this is due to the early stage the product's at, an instance of Visual Studio Code loaded up with multiple open editors uses less than 2/3 of the memory.