Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

New SQL Operations Studio Installation and Overview


By:   |   Read Comments (2)   |   Related Tips: More > SQL Server Operations Studio

Attend this free live MSSQLTips webcast

Tips and Tricks to Growing Your SQL Server Estate from SMB to Enterprise Scale
Thursday, June 28, 2018 - click here to learn more


Problem

SQL Server Management Studio is used as the default tool to connect to different SQL Server versions to manage SQL Server. Prior to SQL Server 2017, SQL Server Management Studio was only on the Windows platform. Microsoft recently launched a preview version of Microsoft SQL Server Operations Studio that runs on Windows, macOS, and Linux for SQL Server, Azure SQL Database, and Azure SQL Data Warehouse. In this tip, we will get an overview of SQL Operations Studio.

Solution

SQL Operations Studio is a free, light-weight tool for developers and administrators for SQL Server on Windows, Linux and Docker, Azure SQL Database and Azure SQL Data Warehouse on Windows, Mac or Linux machines. SQL Operations Studio is built on top of Visual Studio Code with the objective to make it highly extensible. Itís built on an extensible microservices architecture and includes the SQL tools service built on .NET Core.

SQL Operations Studio allows users to run command line tools such as PowerShell, BCP, SSH, Bash, etc. in the integrated terminal window inside the interface. It is also quite easy to view, generate and modify scripts with smart T-SQL code snippets and a rich graphical interface for database objects. DBAs can create customizable dashboards for monitoring which improves efficiency and quick turnaround for performance issues.

Before we move further, let's see how to install SQL Operations Studio.

SQL Operations Studio installation

SQL Operations Studio is currently in the January Public Preview. As mentioned earlier, SQL Operations Studio can be installed on Windows, Linux, MacOS as well. In this demo, we will have a look at the Windows version. You can find details for other OS installations in the Next Steps section.

Download the Windows installer from this link SQL Operations Studio (preview) installer for Windows.

Microsoft SQL Operations Studio Installable software

I have downloaded the Windows version for SQL Operations Studio and clicked on it to start the installation.

Microsoft SQL Operations Studio Installation

Click on the installation file to launch the setup process.

 SQL Operations Studio Installation

In next screen, accept the license agreement and click next.

Microsoft SQL Operations Studio Installation agreement accept

Select the SQL Operations Studio installation path, by default it will go to C:\program files\SQL Operations Studio.

Microsoft SQL Operations Studio Installation location

Select the Start Menu folder for SQL Operations Studio. If you don't want to create a start menu folder, click on the checkbox 'Don't create a Start Menu folder'.

Microsoft SQL Operations Studio Installation start menu folder

In the next screen, it will add the SQL Operations Studio folder path to the environment variable PATH. Please note that this will be available after system restart.

Microsoft SQL Operations Studio Installation PATH

To see the PATH setting, go to Edit the system environment variables.

Microsoft SQL Operations Studio Installation PATH view

It opens the system properties windows as shown below. Click on Environment Variables and edit the PATH variable.

Microsoft SQL Operations Studio Installation PATH view
Microsoft SQL Operations Studio Installation PATH view

You can see the SQL Operations Studio folder path in the PATH variable and then click OK.

Microsoft SQL Operations Studio Installation PATH view

Now click on Next and you can see the settings, go back if you want to change any setting. Click on Install to start the installation process.

Microsoft SQL Operations Studio Installation review

Set up is now starting the install.

Microsoft SQL Operations Studio Installation PATH view

Once set up is finished, by default, it will launch SQL Operations Studio.

Microsoft SQL Operations Studio launch

Overview of SQL Operations Studio

Once the installation is complete, we are now able to connect to SQL Server. The initial screen of SQL Operations Studio looks like the below image.

Microsoft SQL Operations Studio launch screen

Fill out the connection information and click Connect. Unlike other tools, this version doesn't provide a dropdown list of databases, so if we want to connect to a specific database, we can type the database name in the database name field.

We can also click on Advanced to configure more connection options such as timeout, encrypt connection, port number, connection pooling, failover partner, etc.

Microsoft SQL Operations Studio launch screen

I have filled out the basic details like server name, authentication method as Windows authentication. In my case, I don't want to connect to a specific database, so I have kept it blank.

Microsoft SQL Operations Studio launch screen

Once connected to the server, we can see the layout of SQL Operations Studio as below. I have numbered different areas of SQL Operations Studio to explain further below.

Microsoft SQL Operations Studio layout
Microsoft SQL Operations Studio layout section

(1) Object Explorer

This area shows the servers pane where all the server connections will be listed. We can expand the server similar to SSMS and can view databases (tables, schemas, views, functions, stored procedures, etc.), Security (logins, credentials, audits, linked servers logins, server roles, etc.) and Server Objects (endpoints, triggers, linked servers, etc.).

Microsoft SQL Operations Studio object explorer

We can also create groups for the servers.  One example is to group servers according to their roles production, QA, UAT, Dev, etc.

Click on the new server group.

Microsoft SQL Operations Studio new server group

It opens a window to define the server groups and also we can choose the group color as well. This makes it easy to identify the server group based on the color code as well

Microsoft SQL Operations Studio group color and details

We can see below that the group is highlighted with the color we choose while creating it.

Microsoft SQL Operations Studio group details

Now we can add the servers in their respective group by right click on group name followed by the new connection.

Microsoft SQL Operations Studio add servers in group

Fill out the connection details and server will be listed under the group.

Microsoft SQL Operations Studio server group connections

(2) Server Dashboard

Server Dashboard gives information about connected servers such as SQL Server Version, Edition, Computer Name and Operating System Version.

Microsoft SQL Operations Studio server dashboard

(3) Common Tasks

This area shows the common tasks to be performed. These common tasks are:

  • Restore: Shortcut to launch a database restore
  • Configure: Shortcut to configure your server. We will explore this with my future tips.
  • New Query: To run a query, perform query analysis including execution plan overview, etc.

(4) Search Pane

This section provides a easy way to find database objects from the list of databases. We can simply start typing and it narrows down the database objects to match what was typed.

Microsoft SQL Operations Studio search

If we are connected to a specific database, we can search objects such as tables, stored procedures, etc.

Microsoft SQL Operations Studio search objects

(5) Backup Status

This section provides backup status for the databases on the connected server. This is also very helpful information where we can quickly view the backup stats as:

  • How many backups completed in last 24 hrs
  • How many database backups are more than 24 hrs old
  • How many databases for which no backups are present
Microsoft SQL Operations Studio layout

(6) Database Size Graph

This section provides a glance of sizes of various databases individually for the database and transaction logs file size.

Microsoft SQL Operations Studio >DB Size graph

This shows a graphical representation of the data file and log file space. If we hover the mouse over the graph on a particular database, it shows the file sizes for that database.

Microsoft SQL Operations Studio >DB Size

(7) Status Bar

This status bar is an interactive status bar to display a handful of information along with some relative information.

Microsoft SQL Operations Studio Status bar

Below we can see the information, options in the status bar are:

  • Managed linked account: If we are connecting with Azure, it shows the account information and also we can add the account here as well.
  • Problems: If any problems occur such as a syntax error, execution error, etc. it will be noted here.
  • Connection information: Connection information such as server name and database name.
  • Cursor position: Shows the cursor position. Clicking on the current cursor position takes us to the specified line in the opened file.
  • Indentation: Indentation can be changed to spaces or tabs. Clicking on Indentation opens up the below window and we can change Indentation from the list of options provided.
Microsoft SQL Operations Studio Indentation
  • Encoding: We can also view and change the encoding from here. Clicking on encoding provides an option to reopen or save with encoding.
Microsoft SQL Operations Studio Encoding

If we click on Reopen with Encoding, it provides a list of encoding options.

Microsoft SQL Operations Studio Reopen with encoding
  • End of Line Sequence: We can change the End of Line sequence to either LF or CRLF. You can read this tip for more about End of Line sequence.
Microsoft SQL Operations Studio End of Line sequence
  • Language Mode: We can select from a list of different language options. By default, it is set to SQL.
Microsoft SQL Operations Studio Lanuage mode
  • Tweet feedback: Smiley face at the end provides you a possibility to quickly tweet feedback.
Microsoft SQL Operations Studio Tweet feedback

(8) Activity bar

The activity bar contains multiple tabs as shown below.

Microsoft SQL Operations Studio Activity bar
  • Server Panel: Displays the servers group and servers listed.
  • Task History: If we performed any activities such as backups, restores or other similar tasks, it shows the history of those tasks.
  • Explorer pane: The Explorer Pane contains a list of all open files in the editor. If we have any unsaved files as well, it highlights them so that we can save them, if required.

If we right click on the file name, it provides a list of options:

  • Reveal in Explorer opens up an Explorer window.
  • Open in Terminal opens up a terminal window in the lower half of the SQL Operations Studio, by default it is PowerShell, but we can also change to a Command Prompt or a BASH terminal.
Microsoft SQL Operations Studio options explorer pane
  • Open to the Side opens the file in split screen, so we can compare two files side by side.
Microsoft SQL Operations Studio compare files
  • Search: The Search pane opens up a pane that can be used to search, or search and replace, text in the current editor window.
  • Source Control Pane: This allows us to manage various files within your source code control system.
  • Settings: In the bottom lower side, we can get a setting menu. If we click on that, we get various interesting options.
Microsoft SQL Operations Studio Settings
  • Command Palette: We get multiple options out of command palette to ease our tasks.
Microsoft SQL Operations Studio Command Palette
  • It has inline search functionality, as soon as we type, it gives suggestions to choose from.
Microsoft SQL Operations Studio Command Palette search
  • Settings: Once we click on the Settings option, it provides a JSON editor with two screens: one for the default settings and another to override the default settings.
Microsoft SQL Operations Studio settings
  • Color theme: SQL Operations Studio has many more available color themes to choose from. Once we click on a color theme, it gives color theme options as:
Microsoft SQL Operations Studio color theme options

We can simply select the color theme and it immediately changes to that particular color theme.

Microsoft SQL Operations color theme layout

  • Keyboard shortcuts: We can get list of all keyboard shortcuts after click on this option.
Microsoft SQL Operations Studio Keyboard shortcuts
  • Checking for updates: If an update is available for SQL Operations Studio, it will show that update so that we can apply it to use the latest version.
Microsoft SQL Operations Studio updates

Summary

Currently, SQL Operations Studio is released as a Preview version. The tool is very useful and contains many interesting features. We will explore further on the SQL Operations Studio options such as executing a query, exploring execution plans, monitoring dashboards, etc. in future tips.

Next Steps


Last Update:


next webcast button


next tip button



About the author
MSSQLTips author Rajendra Gupta Rajendra is a Consultant DBA with 9+ years of extensive experience in database administration including large critical OLAP, OLTP, Reporting and SharePoint databases.

View all my tips
Related Resources





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.



    



Thursday, June 07, 2018 - 9:58:55 AM - rajendra Back To Top

Hi Venu

 

Which release version of SQL operation studio you are using? Also send me the screenshot of authentication methods showing to you.

Thanks

Rajendra 

 


Thursday, June 07, 2018 - 6:40:39 AM - Venu Gopal Back To Top

 Hi Rajendra,

 Could you please let us on the following scenarion:

I am having a MAC with SQL Operations Studio installed and my requirement is to connect Azure Database (in PAAS environment). I am ale to connect to the DB (with Authentication Mode as : ActiveDirectoryPassword Authentication) through SSMS on wondows. Whereas i am not able to find the required authentocation mode while using "SQL Operations Studio". Could you please let me know, is there any way around to connect Azure SQL DB.

Thanks,

Venu Gopal K


Learn more about SQL Server tools