Introducing SQL Operations Studio

Note: This is content that I originally wrote for our upcoming book, SQL Server 2017 Administration Inside Out, that did not meet the publishing deadline.

A new cross-platform tool for SQL Server and Azure SQL Database

Microsoft has announced the preview of SQL Operations Studio, an exciting addition to the administration and development tooling (DevOps) for database platforms in general, and SQL Server specifically.

SQL Operations Studio (SOS) is a tool that runs on multiple operating systems, supporting multiple database management systems.

Think of SOS as the spiritual successor to SQL Server Management Studio (SSMS). It performs many of the same tasks as SSMS; runs on Windows, macOS and Linux; and targets not only SQL Server, but Azure SQL Database and Azure SQL Data Warehouse as well.

Because it is open source, there is a very good chance that more database platforms may be supported in the future.

A comparative example would be Visual Studio Code, which is a cross-platform integrated development environment (IDE) targeting multiple programming languages. In the same way that a software developer can use any operating system for writing code, you can use SQL Operations Studio on any operating system for managing and developing databases.

Open source software

SQL Operations Studio is an open-source software (OSS) project, forked from Visual Studio Code and based on the Electron shell and Node.js, a JavaScript runtime. It is free for use, and anyone can contribute to the project.

Because it shares a similar development environment to Visual Studio Code, it can have custom themes and is fully extensible.

In other words, you can easily install existing third-party plugins and extensions to improve your workflow, or even write your own and contribute these back to the main product codebase. You should expect the same vendors who write plugins for SQL Server Management Studio to support SQL Operations Studio in the near future.

User interface

The main interface for SQL Operations Studio is made up of viewlets and tiles, similar to the docked windows in SQL Server Management Studio. These elements present information to monitor and administer your database environment.

The interface is fully customizable, with a dashboard that shows insights, performance metrics and telemetry; recent connections; an Object Explorer (similar to SSMS); and of course a query window for writing queries, complete with a results grid. Results can be exported to CSV, Excel and JSON.

SQL Operations Studio main dashboard. Certain values have been blurred out.

Flyouts and Tiles

Whereas SQL Server Management Studio uses dialog windows that pop up in the center of the application interface, the SQL Operations Studio dialog screens, called flyouts, appear from the right of the user interface until the necessary action is performed. This takes some getting used to for people who are more familiar with the Windows paradigm.

Take for example a typical database backup. In this example, we are backing up the ReportServer database on this machine. Notice that the main user interface has been greyed out, while the backup options appear on the right-hand side. This is a flyout (you can see a larger version below).

A flyout on the right, for taking a backup

The flyout is scrollable, meaning you can navigate up and down (using your mouse scroll wheel or the keyboard) to see more options. In the following image, the main database backup options can be seen, with a scroll bar on the right.

Notice that you can even script the operation, just like in SSMS.

Database backup options in SQL Operations Studio

Feature support

SQL Operations Studio includes many of the same core features for administering and developing against SQL Server that you would expect to find in SQL Server Management Studio, including:

  • managing registered servers
  • viewing server and database reports
  • writing queries
  • managing security
  • generating scripts
  • viewing and analyzing query plans
  • database consistency checks
  • index and statistics maintenance
  • backups and restores

Command line interface

One of the breakout features in SQL Operations Studio is the built-in command line interface, which allows interacting with the operating system from within the application. This improves productivity by not having to switch away from the SQL Operations Studio interface to manipulate files or execute external scripts.

On macOS and Linux, bash is currently supported, while PowerShell is supported on Windows.

Multiple database platforms

Because several platforms are targeted by this product (SQL Server, Azure SQL Database and Azure SQL Data Warehouse), not all features may work on each platform, but the underlying database API (application programming interface) hides most differences from the end-user.

In other words, the workflow to manage SQL Server will work the same as it does for Azure SQL Database, for example, and further down the road, you may even be able to work on other platforms, like MySQL.

Future roadmap

This is a very new product from Microsoft, with a lot of work ahead of it. Owing to strict publishing timelines for the book, it was not possible to include any information about SQL Operations Studio, which is why you’re reading about it on this blog.

If you prefer using macOS or Linux for your day-to-day work, it is definitely worth considering SQL Operations Studio as a replacement for SQL Server Management Studio, especially if you already develop against SQL Server 2017 on Linux or Docker on macOS.

The new Microsoft is embracing the people that make technology great. These are exciting times.

Share your thoughts on SQL Operations Studio with me on Twitter at @bornsql.

Leave a Reply

%d bloggers like this: