Since the release of SQL Server® 2017 on Linux®, DBAs have been waiting for a native release of SQL Server Management Studio (SSMS) on Linux. At this point, Microsoft has stated that they have no plans for a native Linux version of SSMS. However, they have just released a preview version of SQL Operations Studio, a new cross-platform development and management tool for SQL Server. In this presentation, you’ll learn about the different platforms that are supported by SQL Operations Studio as well as its main T-SQL development and SQL Server management capabilities.
You can watch a summary of this information here:
With the newly released Linux® support in SQL Server® 2017, many DBAs have been wondering if Microsoft® is going to release a native Linux version of SQL Server Management Studio (SSMS). SSMS is the DBA’s primary management tool for SQL Server, but it currently only runs on the Windows® operating system. Microsoft has stated that they have no immediate plans to port it to Linux. This means that you need to have Windows systems running SSMS in order to manage the SQL Server 2017 instances running on Linux. SQL Operations Studio changes that requirement. Much like Visual Studio Code, which this tool is based on, the new SQL Operations Studio runs natively on multiple platforms. The preview release has native support for the following operating systems:
- Windows 10 (64-bit)
- Windows 8.1 (64-bit)
- Windows 8 (64-bit)
- Windows 7 (SP1) (64-bit) – Requires KB2533623
- Windows Server® 2016
- Windows Server 2012 R2 (64-bit)
- Windows Server 2012 (64-bit)
- Windows Server 2008 R2 (64-bit)
- macOS®13 High Sierra
- macOS 10.12 Sierra
- Red Hat® Enterprise Linux 7.4
- Red Hat Enterprise Linux 7.3
- SUSE® Linux Enterprise Server v12 SP2
- Ubuntu®04
SQL Operations Studio is intended to be a DevOps tool that both SQL Server developers and administrators can use. It can connect to SQL Server on Windows, Linux, Docker®, Azure® SQL Database, and Azure SQL Data Warehouse. For the developer, it offers a full range of T-SQL development features, including IntelliSense, keyword completion, code snippets, code navigation, source control integration and go-to definition. In spite of its name, SQL Operations Studio has a more limited set of management capabilities. It does not offer the full range of management features that SSMS does. Instead, it is more focused on an administrator’s routine tasks. However, it does allow administrators to display databases and their database objects, to back up and restore objects, and display the Query Store. We’ll take a closer look at some of these capabilities in the next section.
The initial connection to SQL Server is straight forward. Unlike Visual Studio®, there is a familiar SQL Server connection prompt displayed on the initial screen. Entering your server and authentication information will display the SQL Operations Studio Server Dashboard, as you can see in Figure 1.
The Info bar at the top provides a convenient link to a number of Quickstart guides that show you how to do common tasks. The Servers pane on the left enables you to navigate through the different databases and their objects. The Tasks pane enables you to quickly perform common tasks. As you can guess, Restore prompts you to restore a database, and New Query launches a T-SQL editing window. Surprisingly, the Configure icon does not allow you to configure SQL Server. Instead, it launches a browser window containing instructions on how to configure SQL Operations Studio Insight widgets, which are essentially saved queries and their visualizations that you can add to the SQL Operations Manager dashboard.
Writing and running queries is one of the main uses for SQL Operations Studio and you can see the T-SQL editing window in Figure 2.
As you can see in Figure 2, the T-SQL editor provides color coding. IntelliSence provides code completion and pop-up database object definitions are displayed if you hover the mouse over an object name in the query window. The Disconnect and Change Connection options allow you to disconnect from the current server or connect to a new SQL Server instance. The drop-down box allows you to change the current database to a different database. Like SSMS, you can execute queries either by clicking the Run icon or by pressing F5. Query results are displayed in the lower half of the query window. One really cool feature is the Explain button, which will display a graphical representation of the current query plan.
Server Groups and the Integrated Terminal
Like SSMS, SQL Operations Studio provides Server groups that enable you to organize the different related SQL Server systems that you work with into color coded groups for easy management. To create a new Server Group, click the New Server Group icon at the top of the Servers sidebar. Then enter a group name, select a color for the group, and optionally, add a description. Server Groups are saved in files that you can copy and transfer to another system running SQL Operations Studio.
One interesting feature that sets SQL Operations Studio apart for SSMS is the Integrated Terminal, which provides a convenient way to run commands from SQL Operations Studio. You don’t have to switch windows or bother with another terminal to run commands. You can use the Terminal windows to run Bash, PowerShell®, Windows Command Shell, sqlcmd, bcp, and ssh commands. I found the Integrated Terminal to be especially useful for using PowerShell or Bash to execute Docker commands for SQL Server containers. You can either use the Integrated Terminal option off the View menu or you can use the Command Palette (Ctrl+Shift+P) and enter the View:Toggle Integrated Terminal command. You can see the Integrated Terminal in Figure 3.
If you need to run a shell command with elevated permissions, you can use a command like runas.exe within the terminal. You can also create multiple terminals open at different locations. New Terminal instances can be added by hitting the plus icon on the top-right of the Terminal pane or by hitting the Ctrl+Shift+` keyboard shortcut. You can switch between Terminal windows using the drop-down menu. You can remove Terminal instances by pressing the trash can button.
SQL Operations Studio is a powerful multiple platform tool that can address the needs of the developer as well as most common SQL Server management functions. In addition to the main features shown in this article, SQL Operations Studio has a dashboard display of the Query Store, can show the backup history of databases, and you can customize the dashboard with your own Insights. You can download the preview release of SQL Operations Studio from GitHub® or Download and install SQL Operations Studio (preview). SQL Operations Studio is expected to be generally available in 2018.