SQL Operations Studio for All

The first cross-platform SQL Operations Studio tool from Microsoft for database developers and administrators.

Microsoft SQL Operations Studio (or SOS, as the product was called) is the first tool to interact with SQL Server in more than a decade, the next step to upgrading the outdated SQL Server Management Studio. It is intended primarily for developers, but SOS also has features for database administrators that are not yet available in SQL Server Management Studio, the main database administration tool of SQL Server. Until recently, when the migration of a code base to a new version of the code to VS was performed and the monthly update rhythm was not coordinated with the release of the Azure and Power BI fixes, SQL Server Management Studio has not seen any significant improvements since its release in 2005, except for the support of new Microsoft SQL Server features.

Cross-platform client

Microsoft SQL Operations Studio is available for Windows, MacOS and Linux from the central repository at Microsoft Docs (at docs.microsoft.com). Installation instructions for each client version are available from the same source, so it is easy to download any version for different devices. Most of my colleagues work on many different platforms, depending on the circumstances, so this approach simplifies the download process.

Open Source

The source code for SQL Operations Studio (preview version) is available at GitHub website (github.com/Microsoft/sqlopsstudio). According to the official question and answer document for SQL Microsoft SQL Operations Studio, there are no open source plans for the current SQL Server Management Studio or SQL Server Data Tools (included in Visual Studio). The initial view of the Microsoft SQL Operations Studio interface is shown on the screen above.

Screen. Interface of Microsoft SQL Operations Studio

Simplicity

Microsoft SQL Operations Studio is positioned as a simplified alternative to interacting with SQL Server databases, among others:

• locally hosted on Microsoft Windows and backwards compatible with all supported versions of Microsoft SQL Server;
• running on Microsoft Azure Stack in the cloud for an Azure SQL Server database or SQL Azure data storage;
• Microsoft SQL Server 2017 on Linux;
• Microsoft SQL Server 2017 running in a Docker container on Windows or Linux.

In fact, Microsoft SQL Operations Studio can interact with any version or edition of Microsoft SQL Server wherever it can be hosted, using a client that is compatible with most commonly used modern platforms. It is a simplified alternative to Microsoft SQL Server Management Studio for many (though not all) of the functions for which SQL Server Management Studio is used today. Thanks to the simplified architecture, you can run multiple instances of the tool in parallel.

Customizable control panel

One of the most interesting features that deserves attention is the ability to customize the control panel graphic elements. In addition to all features intended for developers, this tool can be used to solve many typical administrative and analytical tasks. To access the control panel, you should connect to the database, right-click and select Manage from the context menu.

By default, you can get information about a database similar to what we get from the database properties window in SQL Server Management Studio from the control panel. You can also run database backup and restore operations and query from the control panel, as well as explore the various objects that SQL Server databases consist of. In the near future I will prepare a separate article with a more detailed description of the configuration process.

Using the scripting tool of your choice

From Microsoft SQL Operations Studio you can interact with databases using any of the following scripting languages: Bash, PowerShell, sqlcmd, SSH, PSQL and BCP. You do not need to use several tools to create scripts.

What’s missing

The answer to this question depends on your role and the type of interaction with Microsoft SQL Server. For developer and engineer, the answer is simple: “nothing”. The tool fills the gaps that existed in the set of functions of these roles. For a database administrator, the answer is “there are quite a few gaps”. Currently, you can view database and server-level objects such as tables and login names, but do not add new objects through any user interface. For any administrative tasks, you need to use Transact-SQL. There is also no SQL Server agent support. Scheduling tasks, setting alerts, creating operators, installing the Database Mail component – all these tasks also need to be solved through requests t-sql.

In general, the first version of the tool makes a good impression. It is a new Microsoft product that provides basic functions for interaction with Microsoft SQL Server from operating systems other than Windows and meets the basic needs of developers and engineers. The product does not have the capabilities to address the full range of tasks faced by database administrators, but it will be an acceptable alternative SQL Server Management Studio for all basic (and many of the medium complexity) administrative needs. Administrators who confidently manage their environment primarily on the basis of code, will be able to solve even the most complex tasks.

First Look at SQL Operations Studio