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 mostly a free database development and management tool for working with SQL Server…
and Azure SQL databases. Despite the fact that tool remains to be in preview mode as these writing, it already offers a robust environment — but one that’s meant to be used by what Microsoft calls nonprofessional database administrators
SQL Ops Studio, like the tool is informally known, supports many of the same features even though the more sophisticated SQL Server Management Studio (SSMS). However, Microsoft designed it to simplify nearly all the basic tasks performed by database administrators (DBAs), making routine database operations easier for developers as well as other stand-in DBAs who might not be as comfortable 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, could 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 currently being a lightweight counterpart to Visual Studio, a bit like the database tool does to SSMS. Built around 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
Users can access most features using the left management panes or directly through shortcut keys. They should then perform more detailed operations while in the main window, which displays multiple tabs specific to individual database operations.
Just like, the screenshot image below shows Microsoft SQL Operations Studio of your Servers pane displayed on the left side inside the user interface and two tabs open contained in the main window. The displayed tab is going to be management dashboard relating to the SQL Server instance; the second one, visible only mainly because of the tab itself, is a T-SQL query editor, like the query tabs in SSMS.
The user interface includes five management panes that users access coming from a 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, which includes database backups or restores.
Explorer: Mapped folders and opened tabs, just like the dashboard and query tabs through 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.
The left menu bar also includes a Settings option to access the features needed to configure the environment, modify shortcut keys, select themes and execute other tasks. Through 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 as well as the databases on them, or perhaps managing connections and components. Just like, 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. Upon the server dashboard, for example, users can restore and search databases or launch database dashboards. Along 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 highest 1,000 rows originating from a SQL Server database table or view.
If your query is run, SQL Operations Studio displays three panes in an exceedingly stack. The T-SQL statement runs out of your top pane, the query results are shown around the middle pane and system messages to the query are displayed during the bottom pane.
Widgets add visual insight on systems
Additionally, users can create an insight widget dependant upon the T-SQL query and a selected chart type. Insight widgets are charts and graphs that display information about aspects of your server or its databases, typically using ability to drill into more detailed data. The widgets are auto-generated JSON snippets that may possibly be added into SQL Ops Studio’s configuration file, and then to a new dashboard.
Microsoft also provides several prebuilt insight widgets. E . g ., the server dashboard includes the Database Size widget, with a bar chart that shows the usage of a particular server’s data storage capacity.
Microsoft SQL Operations Studio also includes features for working with T-SQL code. Such as, if a user right-clicks the name from the table in a T-SQL statement and selects Peek Definition, the tool will display a pop-up window that shows the table’s definition. Should the user instead selects the Have a look at Definition option, the definition will open in any separate tab.
Another useful feature is definitely the T-SQL code snippet library, which users can access from any line for the top query pane. To retrieve a code snippet, a user need only start typing sql, followed by a statement type. Here is an 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 with the editing tab, complete with placeholder code for defining a new column.
Because Microsoft SQL Operations Studio is free, users who don’t need every one of the functionality in SSMS have little burning by trying it out. Also, given that SQL Server 2017 can run on Linux and in Docker containers, it’s now possible to host and manage a SQL Server instance entirely within a non-Windows environment — a significant milestone in your evolution of both SQL Server and Microsoft.