carloscastilla - Fotolia
The new SQL Operations Studio gives database administrators a free cross-platform tool with a number of features found in Microsoft's other popular SQL database management and development tools.
The free tool, also known as SQL Ops Studio, provides database instance navigation, a T-SQL editor complete with IntelliSense, keyword completion, code snippets, code navigation and Git source control integration, in addition to support for basic SQL Server management functions. It works with SQL Server, Azure SQL Database and SQL Data Warehouse instances on Windows, macOS and Linux.
While lacking the complete breadth of management features available in SQL Server Management Studio (SSMS), SQL Ops Studio is an effective lightweight tool.
Early versions have been available for several months, and SQL Operations Studio is now in public preview mode. General availability hasn't been announced.
Here are seven tips for making the most of SQL Operations Studio.
Download and install SQL Operations Studio on Windows, Linux or Mac
The SQL Ops Studio preview is available as a free download separate from the main SQL Server installation process.
There are three different installation packages for each of the different platforms SQL Ops Studio runs on. You can download the installation packages for SQL Ops Studio from its official website or GitHub, as shown in Figure 1.
Connect SQL Ops Studio to the SQL Azure cloud
SQL Ops Studio works with both local SQL Server instances and SQL Azure instances in the cloud. To connect SQL Operations Studio to a SQL Azure database, click the Add Connection or New Connection icon in the sidebar on the left. This will display the Connection window. Supply the name of the SQL Azure server in the format servername.database.windows.net.
Next, enter your authentication information. You can choose either SQL authentication or Windows authentication depending on the requirements of the SQL Azure database. Once you've chosen the appropriate authentication, enter the account password. The Connection window is shown in Figure 2.
If your SQL Azure server doesn't have a firewall rule allowing SQL Operations Studio to connect, the Create new firewall rule form will open for you to create a new firewall rule.
Take advantage of T-SQL snippets
T-SQL snippets are code templates that make it easier to perform common actions, like creating databases and other database objects. To access SQL Ops Studio code snippets, type sql in the query editor and select the snippet you want to use from the drop-down menu. This will generate a T-SQL script that will perform the selected action.
The replaceable value in the script is enclosed in brackets and highlighted. You need to update the highlighted fields with your own specific values. If there are a number of highlighted fields, you can right-click the word you want to change and select Change all occurrences from the context menu.
An example of using T-SQL snippets is shown in Figure 3.
Using the Integrated Terminal
The Integrated Terminal allows you to easily execute commands without having to exit SQL Ops Studio to open another command window. Depending on your operating system, you can execute Windows Command Shell, PowerShell or Bash commands. The shell used in the Integrated Terminal defaults to $SHELL on Linux and macOS, PowerShell on Windows 10 and Windows Command Shell -- cmd.exe -- on earlier versions of Windows.
To open the Integrated Terminal, use the View > Integrated Terminal menu option, the Ctrl + ` keyboard combination or the View > Toggle Integrated Terminal command from the SQL Ops Studio Command Palette (Ctrl + Shift + P).
You can create multiple terminals in different locations by clicking on the plus icon on the top-right of the Terminal panel. You can remove Terminal instances by clicking the trash can icon. You can see the Integrated Terminal running PowerShell commands in Figure 4.
Customizing the dashboard using Insight widgets
SQL Operations Studio enables you to create a customizable management environment by extending the server and database dashboards using Insight widgets.
Insight widgets use T-SQL queries to return the status of servers and database objects and display the results as graphical visualizations on the SQL Ops Studio dashboard. An Insight widget query can be rendered on the dashboard as a count, chart or graph.
SQL Ops Studio includes several Insight widgets out of the box, including Table-space-DB-insight, Query-data-store-DB-insight, Backup-history-server-insight and All-database-size-server-insight.
To create a custom Insight widget, click the Create Insight option above the graph to open a new window with a JSON file that contains a T-SQL query and a description of the graph the widget will use. The widget will automatically execute that query when it comes into view and display the results in the graph. If you make changes to the T-SQL query, the new results will be displayed in the widget the next time it is run. In Figure 5, you can see the SQL Ops Studio Server Dashboard with a custom table space Insight widget.
Configuring SQL Agent
SQL Ops Studio has had eight interim releases since it was initially released in November. In that time, Microsoft has continued to include more of the management features from SSMS, in addition to improving the overall release quality.
One of the big enhancements has been the inclusion of SQL Agent support via an extension. Using the new SQL Agent extension, SQL Operations Studio has added capabilities for users to create a new job, add a new job step, add a new alert, show a job's history of past runs, and display whether it passed or failed. The SQL Agent extension also provides the ability to search for jobs. Figure 6 shows the SQL Ops Studio New Job window.
Take advantage of extensions
Built on top of the Visual Studio Code framework, SQL Ops Studio shares its ability to take advantage of extensions to customize and expand its functionality. SQL Agent support is added using extensions like Redgate SQL Search, which can extend the native functionality of SQL Ops Studio. The SQL Search extension helps you to easily find database objects and jump to an object in Object Explorer or to view an object's definition.
Adam Machanic's popular monitoring and troubleshooting tool, sp_whoisactive, is also available as a SQL Ops Studio extension. You can add extensions to SQL Ops Studio by clicking the Extensions icon in the left pane and then selecting the extensions that you want to install from the list, as shown in Figure 7.