carloscastilla - Fotolia

Get started Bring yourself up to speed with our introductory content.

Microsoft SQL Operations Studio eases SQL Server admin tasks

SQL Operations Studio simplifies routine administration of SQL Server and Azure SQL databases, making database development and management easier for users who aren't full-time DBAs.

Microsoft SQL Operations Studio is a free database development and management tool for working with SQL Server and Azure SQL databases. Although the tool is still in preview mode, it already offers a robust environment -- but one that's meant to be used by what Microsoft calls nonprofessional database administrators.

SQL Ops Studio, as the tool is informally known, supports many of the same features as the more sophisticated SQL Server Management Studio (SSMS). However, Microsoft designed it to simplify many of the basic tasks performed by database administrators (DBAs), making routine database operations easier for developers and other stand-in DBAs who might not be as familiar with SQL Server as full-time admins are.

Initially made available in November 2017, Microsoft SQL Operations Studio can connect to SQL Server instances running on Windows and Linux computers or within Docker containers. The tool, which runs on Windows, macOS and Linux clients, can also be used to manage Azure SQL Database and Azure SQL Data Warehouse implementations on Microsoft's cloud platform.

Microsoft forked SQL Operations Studio from Visual Studio Code, a cross-platform source code editor that serves as a lightweight counterpart to Visual Studio, just like the database tool does to SSMS. Built on a microservices architecture for increased extensibility, SQL Ops Studio provides a user-friendly environment with extensive support for customizable keyboard shortcuts.

SQL Operations Studio's user interface

The user interface in SQL Operations Studio includes five management panes that users open from the left menu bar.

  • Servers: A hierarchical tree for accessing server and database objects, similar to Object Explorer in SSMS.
  • Task History: A history of recent activity, such as database backups or restores.
  • Explorer: Mapped folders and opened tabs, such as the dashboard and query tabs in the image.
  • Search: Tools for searching or replacing text in open editor tabs.
  • Source Control: An interface to a Git version control system, if SQL Ops Studio is connected to one.

Users can access most features from the left management panes or directly through shortcut keys. They can then carry out more detailed operations in the main window, which displays multiple tabs specific to individual database operations.

For example, the screenshot below shows Microsoft SQL Operations Studio with the Servers pane displayed on the left side of the user interface and two tabs open in the main window. The displayed tab is the management dashboard for the SQL Server instance; the second one, visible only by the tab itself, is a T-SQL query editor, similar to the query tabs in SSMS.

Screenshot of Microsoft's SQL Operations Studio tool
A screenshot of the user interface in Microsoft's SQL Operations Studio tool, with the management dashboard for a SQL Server system displayed.

The left menu bar also includes a Settings option to access the features needed to configure the environment, modify shortcut keys, select themes and carry out other tasks. From the bottom menu bar, users can access several additional features, including an integrated terminal that supports command-line tools, including Bash and PowerShell, plus sqlcmd, bcp and ssh.

Managing and querying in SQL Ops Studio

SQL Operations Studio provides a wide range of options for viewing information about servers and the databases on them, as well as managing connections and components. For example, from the Servers pane, users can undo or delete connections, refresh the object tree or individual nodes, script table definitions, and directly update table data.

The Servers pane also enables users to launch server or database dashboard tabs that provide additional options for viewing information and carrying out administrative tasks. On the server dashboard, for example, users can restore and search databases or launch database dashboards. On the latter, they can back up databases, search tables, generate table creation scripts or edit a table's data.

Users build and run queries on individual query tabs in Microsoft SQL Operations Studio, similar to in SSMS. Also like in SSMS, a user can automatically generate a SELECT statement that retrieves the top 1,000 rows from a SQL Server database table or view.

When a query is run, SQL Operations Studio displays three panes in a stack. The T-SQL statement runs from the top pane, the query results are shown in the middle pane and system messages about the query are displayed in the bottom pane.

From the results pane, users can click a button to save the returned data to a CSV, XLSX or JavaScript Object Notation (JSON) file. The query results can also be viewed in various types of charts, which can be copied to the clipboard or saved to a PNG file.

Widgets add visual insight on systems

Additionally, users can create an insight widget based on the T-SQL query and a selected chart type. Insight widgets are charts and graphs that display information about aspects of a server or its databases, typically with the ability to drill into more detailed data. The widgets are auto-generated JSON snippets that can be added to SQL Ops Studio's configuration file, and then to a dashboard.

Microsoft also provides several prebuilt insight widgets. For example, the server dashboard includes the Database Size widget, with a bar chart that shows the usage of a server's data storage capacity.

Microsoft SQL Operations Studio also includes features for working with T-SQL code. For example, if a user right-clicks the name of a table in a T-SQL statement and selects Peek Definition, the tool will display a pop-up window that shows the table's definition. If the user instead selects the Go to Definition option, the definition will open in a separate tab.

Another useful feature is the T-SQL code snippet library, which users can access from any line in the top query pane. To retrieve a code snippet, a user need only start typing sql, followed by a statement type. For example, if the user types sqladd, the sqladdcolumn option pops up. If the user then clicks that option, SQL Ops Studio will add an ALTER TABLE statement to the editing tab, complete with placeholder code for defining a new column.

Because Microsoft SQL Operations Studio is free, users who don't need all the functionality in SSMS have little to lose by trying it out. Also, given that SQL Server 2017 can run on Linux and in Docker containers, it is now possible to host and manage a SQL Server instance entirely in a non-Windows environment -- a significant milestone in the evolution of both SQL Server and Microsoft.

Next Steps

Learn how to get the most out of SQL Operations Studio

Dig Deeper on Microsoft SQL Server Tools and Utilities