SQL Server Operations Studio - Where Does It Fit In?

By: Graham Okely | Updated: 2018-03-28 | Comments | Related: > SQL Operations Studio

Problem

You were writing some T-SQL in your high privilege account and Oh No! Production data was modified. How can you set up your environment to safely write T-SQL and still administer databases?

Solution

Microsoft has released a new product SQL Operations Studio that can help in this scenario.

We can do the following:

Use SQL Server Operations Studio (SSOS) for developers, by using it with a low privilege account.
Use SQL Server Management Server (SSMS) for DBAs, by using it with a high privilege account.

By setting up your environment like this you ‘develop’ scripts in your, safe, low privilege account in SSOS and do your DBA work in your high privilege account using SSMS.

Review of the main features of SQL Server Operations Studio (SSOS)

SSOS is a MSSQL database management tool for Windows, Mac and Linux. (It does not replace SSMS)
You can create server groups for managing connections to systems.
It has an integrated terminal, or connection to the host operating system.
It has script version control using GIT. (You need to install that.)
It has a configurable widget system built in for making dashboards.
It has a KEYBOARD centric focus.
With the editor you can “Change all occurrences” of a word in a file. Two ticks ✓ ✓ Very helpful!
With the editor you can use or create T-SQL code snips.
With the editor you can double click an object to ‘peek’ at a definitions of database objects.
With the dashboard you can easily search for databases on an instance.
With the dashboard you can easily search for tables and stored procedures in a database.
SSOS is based on and like VS Code.
See this review by Rajendra Gupta for more details.

Heads up on the way it works

SSOS has its main menu on the left-hand side. Clicking on the icons takes you the following areas:

	Server connection management.
	Task monitoring (Like backups.)
	Workspaces of files and folders. The (1) indicates one unsaved file.
	Search over workspaces files and folders.
	Version control. The (4) indicates you have 4 scripts to commit to version control.

Here are some connections organized into groups. Just click on that area to see the options.

From the search icon, easily search over your scripts.

SSOS uses GIT version control for your files.

Editor Tips:

Press Control and K then Control and C will comment out selected lines.
Press Control and K then Control and U will UN-comment selected lines.
Press Control Shift P for the command pallet.

Remember it is a keyboard centric system. Press Control Shift P for this command pallet.

As a keyboard centric editor, it has plenty of scope for configuring keyboard shortcuts.

Here are the details from Microsoft on configuring keyboard shortcuts in SQL Server Operations Studio.

When using SSOS it will pop up handy advice and choices for you:

An introductory video

Here is the Senior Program Manager Eric Kang introducing SQL Operations Studio.

Details on the download and installation

If you would like to install SSOS, go to this GIT site or this Microsoft site and download the installation file for your system.

Tip on installing sqlops on your Mac

Note: When installing this product on Windows and Linux I found no issues, but installing it on my Mac meant an Operating system upgrade. It must be >=10.12.

That meant I had to create space on the Mac by removing unused files. Then SSOS installed without issue. But then I found MacVIM and MySQL WorkBench did not work so they required a fresh installation. But after all that I could use SSOS on my Mac and connect to a SQL Server Express instance on a Windows laptop.

$C:\Users\graham.okely\AppData\Local\Microsoft\Windows\INetCache\Content.Word\Screen Shot 2018-02-15 at 7.11.57 PM.PNG$

SSOS does run on Mac systems and can connect to SQL Server on Windows systems.

$C:\Users\graham.okely\AppData\Local\Microsoft\Windows\INetCache\Content.Word\Screen Shot 2018-02-17 at 11.59.43 am.png$

Version Control and Git

SSOS is an environment for developers and/or part-time developers and you can use Git for version control. This article is not a Git tutorial, so for those who do not know Git you should check out some tutorials. For those who know Git then you will enjoy using Git in SSOS.

TIP: Create a local repository first. Don’t create a public repository in GitHub unless you are sure you want to do that.

Rolling back or ‘pulling’ a previous version of a script can be done once you have a repository.

Here is one short article on Git in SSOS by a DBA. Here is a list of SQL Server projects in GitHub.

Here is a script ready to commit to version control.

SSOS can display T-SQL versions alongside each other.

And it can manage other language files such as PHP.

Conclusion

SQL Server Operations Studio is a rich and helpful environment for developing code and working with databases. It ‘fits’ as another tool for the DBA and it is not a replacement for SSMS.

Summary of tools

The tools we have as DBAs has come a long way since I began in 1984. It is getting a little crowded. Here is a table to help sort it all out.

Graphical User Interface	Icon	Platforms	Link	Aimed at	Summary (from the web site)
SQL Server Operations Studio			SSOS	Developers	SQL Operations Studio (preview) is a free tool that runs on Windows, macOS, and Linux, for managing SQL Server, Azure SQL Database, and Azure SQL Data Warehouse; wherever they're running. SQL Operations Studio (preview) offers a modern, keyboard-focused T-SQL coding experience that makes your everyday tasks easier with built-in features, such as multiple tab windows, a rich T-SQL editor, IntelliSense, keyword completion, code snippets, code navigation, and source control integration (Git). Run on-demand T-SQL queries, view and save results as text, JSON, or Excel. Edit data, organize your favorite database connections, and browse database objects in a familiar object browsing experience.
SQL Server Management Studio			SSMS	Database Administrators	SSMS is an integrated environment for managing any SQL infrastructure, from SQL Server to SQL Database. SSMS provides tools to configure, monitor, and administer instances of SQL. Use SSMS to deploy, monitor, and upgrade the data-tier components used by your applications, as well as build queries and scripts. Use SQL Server Management Studio (SSMS) to query, design, and manage your databases and data warehouses, wherever they are - on your local computer, or in the cloud.
SQL Server Data Tools			SSDT	Developers Data integration	SQL Server Data Tools (SSDT) transforms database development by introducing a ubiquitous, declarative model that spans all the phases of database development inside Visual Studio. You can use SSDT Transact-SQL design capabilities to build, debug, maintain, and refactor databases. You can work with a database project, or directly with a connected database instance on or off-premise.
mssql for VS Code			VS Code	Developers	An extension for developing Microsoft SQL Server, Azure SQL Database and SQL Data Warehouse everywhere with a rich set of functionalities
Command Line Interface	Icon	Platforms	Link	Aimed at	Summary (from the web site)
Sqlcmd			sqlcmd	Anyone	The sqlcmd utility lets you enter Transact-SQL statements, system procedures, and script files at the command prompt, in Query Editor in SQLCMD mode, in a Windows script file or in an operating system (Cmd.exe) job step of a SQL Server Agent job. This utility uses ODBC to execute Transact-SQL batches.
Bcp			bcp	Bulk data loaders	The bulk copy program utility (bcp) bulk copies data between an instance of Microsoft SQL Server and a data file in a user-specified format.
Mssql-scripter			Scripter	Developers	The mssql-scripter tool enables developers, DBAs, and sysadmins to generate CREATE and INSERT T-SQL scripts for database objects in SQL Server, Azure SQL DB, and Azure SQL DW from the command line.
Mssql-cli			Mssql-cli	Developers	A command-line client for SQL Server with auto-completion and syntax highlighting
Sqlpackage			Sqlpackage.exe	Developers	SqlPackage.exe is a command line utility that automates database development tasks.

Next Steps

See this review of SSOS by Rajendra Gupta for more details.
Are you into source code? Then check out the source code for sqlopsstudio in github.
Start SSMS with your dot one account for high access work and give it a go starting SSOS with your non-dot one account for your ‘developer’ work.
Check out more of my tips on MSSQLTips.com
Check out this tip by Rajendra Gupta on MSSQL-cli.

About the author

Graham Okely is a SQL Server DBA and has been working with database systems since 1984 and has been specializing in SQL Server since 2007.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips

Article Last Updated: 2018-03-28