SQL Server Operations Studio - Where Does It Fit In?


By:   |   Updated: 2018-03-28   |   Comments   |   Related: More > 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 Server connection management.
task monitoring Task monitoring (Like backups.)
files Workspaces of files and folders. The (1) indicates one unsaved file.
folders Search over workspaces files and folders.
version control 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.

dba connections
new connection

From the search icon, easily search over your scripts.

user name

SSOS uses GIT version control for your files.

source control

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.

toggle developer tools

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

keyboard shortcuts
command

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:

customize

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.

warn

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.

staged changes

SSOS can display T-SQL versions alongside each other.

month start

And it can manage other language files such as PHP.

working tree

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 Image result for platform icons mac linux windows 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 Image result for platform icons mac linux windows 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 Image result for platform icons mac linux windows 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 Image result for platform icons mac linux windows 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 Image result for platform icons mac linux windows 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 Image result for platform icons mac linux windows 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 Image result for platform icons mac linux windows 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 Image result for platform icons mac linux windows Mssql-cli Developers A command-line client for SQL Server with auto-completion and syntax highlighting
Sqlpackage Image result for platform icons mac linux windows Sqlpackage.exe Developers SqlPackage.exe is a command line utility that automates database development tasks.
Next Steps


Last Updated: 2018-03-28


get scripts

next tip button



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

View all my tips
Related Resources




More SQL Server Solutions











Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name
*Email
Email me updates

Signup for our newsletter

I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.






download


Recommended Reading

Backup and Restore Database Tasks Using SQL Operations Studio

SQL Operations Studio Overview and Features

Install SQL Operations Studio on Ubuntu Linux

Installing Extensions in SQL Operations Studio

Add SQL Server Agent Management to SQL Operations Studio





get free sql tips
agree to terms


Learn more about SQL Server tools