Microsoft's SQL Operations Studio (or SOS as it’s already being called) is the first new tool for interacting with SQL Server in over a decade. Although it's focused on use by developers, SOS has features for Database Administrators that have yet to appear inside of SQL Server Management Studio, the flagship tool for SQL Server database administration. I’m encouraged that this is the next step towards modernizing the dated SQL Server Management Studio tool. Until recently, with the porting of its code base to the newer version of VS code and a monthly update cadence to fall in line with the same cadence placed on Azure and Power BI enhancements, SQL Server Management Studio has not seen significant updates except for support of new features of Microsoft SQL Server since its release in 2005.
Cross Platform Client
Microsoft SQL Operations Studio is available for Windows, MacOS, and Linux from a central location at Microsoft’s Docs site here. Instructions for installing each of these client versions are also located at this same source so it’s quite easy to download any of the versions for your various devices. If you’re like me and many of my peers we’re working on multiple platforms depending on our circumstances so this makes for a simplified download experience.
The source code for SQL Operations Studio (preview) is available on GitHub. According to the official FAQ for SQL Microsoft SQL Operations Studio, there is no plan to move to an open source methodology for the existing SQL Server Management Studio or SQL Server Data Tools (which is incorporated into Visual Studio.) The initial view of the Microsoft SQL Operations Studio interface is shown below:
Microsoft SQL Operations Studio is being marketed as a lightweight alternative to interacting with your SQL Server databases whether they’re:
- On-prem hosted natively on Microsoft Windows and backward compatible to all supported versions of Microsoft SQL Server
- Running in the Microsoft Azure stack in the cloud for either SQL Server Azure Database or Azure SQL Data Warehouse
- Microsoft SQL Server 2017 hosted on Linux
- Microsoft SQL Server 2017 running in a Docker Container on either Windows or Linux
Effectively, Microsoft SQL Operations Studio can interact with any version or edition of Microsoft SQL Server wherever it is capable of being hosted, using a client running on the most popular platforms in play today. It is a lightweight alternative to Microsoft SQL Server Management Studio for many (but not all) functions you currently use SQL Server Management Studio for today. Its lightweight architecture allows you to fire up multiple instances of the tool running side-by-side.
Customizable Management Dashboard
One of the most interesting features I intend to explore (and write about here for you all to follow along) is the ability to customize the graphical experience of the management dashboard. In addition to all the featured targeted to Developers and Dev Ops staff, many frequent administrative tasks and insights are available through this new tool. The Management Dashboard is accessed via connecting to a database, right clicking, and selecting “Manage” from the available options. The default Management Dashboard allows you to gain similar insights into a database as you would from the database properties modal window in SQL Server Management Studio. Additionally, you’re also able to initiate backups and restores of your databases and execute queries from the Management Dashboard while also exploring all the various objects that comprise your SQL Server databases. I will go in depth into the customizing process in a dedicated article on that process soon.
Use the Scripting Tool of Your Choice
Be it Bash, PowerShell, sqlcmd, SSH, PSQL, or BCP you can interact with your databases using any of those scripting languages from inside the Microsoft SQL Operations Studio tool. There is no need to use multiple tools to accomplish your scripting tasks.
But What is Missing?
That’s a question with answers that depend on your role and typical interaction with Microsoft SQL Server. For the developer and devops engineer I’d say simply “nothing.” This is a tool filling a void for those roles. For the DBA the answer would be “quite a long list of things.” While you’re currently able to view database and server level objects like tables and logins you can’t add new objects through any sort of user interface – you need to rely on Transact-SQL for any administrative efforts such as those. There is also no support for SQL Server Agent. Scheduling jobs, configuring alerts, creating operators, setting-up Database Mail, all those tasks also would need to be accomplished through t-sql querying.
I’m quite pleased with the initial release state of this new tool. It’s the first tool released by Microsoft that can provide core functionality for interacting with Microsoft SQL Server from operating systems beyond the Windows O/S and for accessibility for the core needs of the Developer and Dev Ops Engineer. While it doesn’t have all the bells and whistles for DBAs to do all of their tasks it does also offer an alternative for SQL Server Management Studio for all the basic (and many mid-tier) administration tasks. For those Administrators comfortable with administering their environment via a code-most/first strategy it even allows for most advanced tasks as well.