Written by 15:11 Tools & technologies, Utilities & Extensions

Microsoft SQL Operations Studio: Understanding and Installation

SQL Server Management Studio is considered as the default integrated graphical user interface tool that has been used for many years to configure, manage, monitor and administrate the SQL Server instances hosted on the local machines, on remote servers or in the cloud by all SQL Server administrators and developers. It provides us with editing, debugging and deploying environment for many languages including T-SQL, XML, MDX and DMX languages. Due to the fact that Microsoft SQL Server can be installed now on the Linux platform, and that the SQL Server Management Studio tool is not compatible with any operating system outside Microsoft Windows, the need for a new cross-platform graphical user interface appears.

During the PAS SUMMIT 2017 keynote, Microsoft announced a new free lightweight and cross-platform database development and administration tool, built on top of VS Code, that provides the SQL Server developers and administrators with a simple environment to manage the databases and perform their daily tasks quickly without the need to have a deep knowledge in SQL Server administration field, with the ability to install this tool on Windows, macOS, and Linux operating systems to connect to SQL Server, Azure SQL Database, and Azure SQL Data Warehouse instances. This new tool is called Microsoft SQL Operations Studio.

Microsoft SQL Operations Studio provides you with an easy way to develop, and administrate your SQL Server instances and boost your productivity by offering a modern T-SQL coding experience that includes multiple tab windows, rich T-SQL coding editor with built-in active IntelliSense, code navigation and snippets, with the ability to integrate your code directly with the source control. The smart T-SQL Code Snippets allows you to easily create and modify the database objects within your database and create a copy of your database for testing purposes quickly. SQL Operations Studio allows you also to view and save the T-SQL queries result as text, CSV, Excel and JSON formats. It provides you also with the ability to manage your favorite server connections and browse the database objects easily.

SQL Operations Studio, which is compatible with all supported versions and editions of Microsoft SQL Server, also offers a rich customizable monitoring dashboard that helps in the performance troubleshooting and tuning process. You can also use the Server Groups to organize and manage the connections information for different SQL servers. Within the Integrated Terminal window, you can take benefits from the SQL Operations Studio to use the command line tool that you prefer to manage the SQL Server components, such as the sqlcmd, bcp, PowerShell, SSH, PSQL and Bash scripting languages.

In addition to all mentioned development features, the Management Dashboard of the SQL Operations Studio provides you with an easy way to administrate your database tasks and gain the same database insight, similar to the database properties window when using the SQL Server Management Studio, with the ability to perform other tasks such as the database backup and restore, execute new queries from the Management Dashboard and explore the different database objects.

SQL Operations Studio is considered as a complete solution for both, the SQL Server developers and the DEVOPS engineers. For the SQL Server database administrators, there still no user interface that can help them to create a new database or add database objects. Also, there is no support for the SQL Server Agent service to create, configure or schedule the SQL Server jobs, that can be only performed using the T-SQL commands.

As we mentioned previously in the introduction of this article, the SQL Operations Studio can be run on Windows, macOS, and Linux operating systems. The supported platforms include Windows 7 and later, Windows Server 2008 R2 and later, macOS 10.12 Sierra and later, Ubuntu 16.04, SUSE Linux Enterprise Server V12 SP2, Red Hat Enterprise Linux 7.3 and later.

The latest version of the SQL Operations Studio was released on this January, with release number 0.25.4, that can be downloaded for the three platforms from the Microsoft Download page for free. In this new release, Microsoft fixed a number of bugs and included some features, such as the Saved Server connections that are available in the Connection Dialog, the Tab-coloring based on Server Group is also added and the Server name is changed to Server in the Connection Dialog. The release can be downloaded in different extensions, with .exe and .zip for Windows platform, which we will work with in this article, as shown in the snapshot below:

To install the SQL Operations Studio on macOS, download the SQL Operations Studio compressed installation file (.zip) for macOS, then double-click it to expand the zip file content. After that, drag the sqlops.app file to the Applications folder to make the SQL Operations Studio available in the Launchpad.

To install the SQL Operations Studio on Linux, download the SQL Operations Studio installation file from the Microsoft download page, then use the bash script that is mentioned in the download page to extract the files after saving it to your machines. Taking into consideration that you install the additional dependencies, the script depends on the version of the Linux platform that you are using. Once the files are extracted, install the SQL Operations Studio on your Redhat or Ubuntu machine using the bash installation scripts listed on the same download page.

In this article, we will go through installing the SQL Operations Studio on the Windows machine.When the file is download successfully, the latest released (.exe) installation file will be like the below:

If you download the .zip SQL Operation Studio compressed installation file, unzip the compressed sqlops-windows folder locally on your machine, then locate and double-click on the sqlops.exe installation file to start the installation process.

To start the SQL Operations Studio tool installation process, double-click the (.exe) installation file and the setup Welcome window will appear. Read the instructions on the Welcome page then click the Next button to go to the next step:

On the License Agreement window, select the agreement acceptance choice “I accept the agreement” after reviewing the agreement, then click on the Next button to proceed:

On this window, you will choose where you will install the SQL Operations Studio tool. Click  Browse to change the default path to another folder, or click the Next button to continue:

The next window will allow you to choose the folder in which a SQL Operations Studio tool shortcut will be created. Click Browse to change the specified path, enable the check box if you don’t want to create a start menu folder or click  the Next button to continue:

On the following window, you will check if additional tasks will be performed during the SQL Operations Studio process is running, which is creating the previously chosen path. Click Next to continue:

On the Ready to Install window, review all chosen options then click Install to proceed:

Once the installation process completed successfully without any error, the below window will appear, from which you can choose to start the SQL Operations Studio directly or later. Click the Finish button to exit from the installation wizard:

This will open the SQL Operations Studio starting page, that will display a Welcome message with a choice to go to the Microsoft SQL Operations Studio Quickstart tutorial, or close the window, as shown below:

After that, the Connection window will be displayed allowing you to connect to any SQL Server instance. If the connection window does not appear automatically when you open the SQL Operations Studio, click the New Connection icon shown below:

In the connection dialog, type the name of the SQL Server instance that you want to connect to, with the authentication type that you will use, Windows Authentication that will take the logged in user credential automatically or SQL Authentication that will ask for a username and a password, with the ability to connect to a specific database by writing the database name. After filling out the connection information, click the Connect button to connect to your SQL Server instance and start working on it:

In the next article, we will discuss the components of the SQL Operations Studio, all the options that are available in each pane and finally we will see practically how we can take benefits from the SQL Operations Studio to perform our daily development and administration tasks. For now, to be in the same line with us, I suggest downloading the SQL Operations Studio form the Microsoft download page and install it on your machine.

Useful Links

Tags: , , , , Last modified: October 06, 2022