Article
12/19/2017

Connect(); 2017

Volume 32 Number 13

Data - SQL Operations Studio: Cross-Platform SQL Server Management

By Julie Lerman; 2017

Have you ever been setting up a new Windows machine and dreaded having to download and install database tools that are several gigabytes in size? Or, on the other hand, wanted to do some work on your SQL Server database from a Mac and wished you had SQL Server Management Studio (SSMS)? Maybe you’ve been using Visual Studio Code (VS Code) and have thought, “Gee, it would be so cool if there could be a version of SSMS like this! Cross-platform, lightweight, extensible and free!” A nerd has her dreams, I guess.

Well, friends, dreams do sometimes come true. I’ve already had a lot of fun working with the mssql extension for VS Code, but that extension is mostly for executing SQL, though it also has some great features for visualizing query results. And it made me wish for more, such as, “If only I could browse through my database schema with an explorer like the one in SSMS or SQL Server Data Tools (SSDT) in Visual Studio.” Or, “If only I didn’t have to go look up the TSQL to do a database backup."

The realization of these dreams comes via the new Microsoft SQL Operations Studio. It seems that with the mssql extension for VS Code, the SQL Server Tools team was just getting warmed up. This team is very focused on having its upcoming tools be not only cross-platform, but also fill the needs of many types of users—DBAs, sys admins, accidental DBAs and developers.

VS Code provided a great starting point for SQL Operations Studio: It’s cross-platform, highly extensible and written in ElectronJS, a platform for building desktop applications in JavaScript, HTML and CSS. In fact, if you’re familiar with VS Code, you’ll recognize the surface of SQL Operations Studio because of that VS Code starting point.

SQL Operations Studio debuted at the PASS Summit in October and a public preview was launched at Connect();. It’s a free, standalone tool that works with Azure SQL Database, Azure SQL Data Warehouse and SQL Server running anywhere. You don’t need a SQL Server license to use it. SQL Operations Studio is available for Linux, macOS and Windows at aka.ms/sqlopsstudio, and takes just moments to install. You’ll find the source code for SQL Operations Studio on GitHub at github.com/microsoft/sqlopsstudio and anyone in the community can file issues or suggestions and contribute to the product on GitHub.

Just because it’s cross-platform, I had to first try it out on my MacBook where I already have a few SQL Server for Linux servers running in Docker, but can also interact with some Azure SQL Databases or even connect to a SQL Server instance on a Windows Server on my network.

The SQL Operations Studio IDE

Let’s take a look first at what you see when you start up SQL Operations Studio for the first time (see Figure 1). The arrows and text aren’t part of the IDE, just there to help with the tour.

Figure 1 The SQL Operations Studio UI

The action bar on the left, familiar to VS Code users, has 5 icons: Object Explorer, like its counterparts in SSMS and other database IDEs, lets you view and manage objects; Task History shows you the tasks, such as backup and restore, that have been performed; File Explorer provides a way to store TSQL and other assets you want to associate with a particular project; Search here focuses on the files you’re working with, but don’t worry, there’s also a way to search the database. Finally, Source Control leads to tools for the integrated source control of the project files.

Make Your First Server Connection

Like SSMS, SQL Operations Studio lets you connect to multiple servers and remembers the connections so you can easily reconnect whenever you open the application. I’ll start by connecting to a SQL Server for Linux instance in a Docker container on my machine. First, I need to be sure the container is running, so I’ll open the integrated terminal window with CTRL+~ and type the Docker command, docker ps. The response tells me the juliesqllinux container is running and I know it’s available at localhost.

Next, I’ll click on the Object Explorer icon to open the Object Explorer, and then I’ll click the Add Server icon at the top right.

This opens up a connection window that I’ll populate with my connection information. As with SSMS, you can just add the basic connection information and let the defaults do the rest of the job. Or you can click Advanced and specify more information about the connection. I’ll go with the easy option. After filling out the connection information, click Connect.

The server will appear in the Object Explorer and you’ll be presented with some information about it in the dashboard. In Figure 2, I’ve partially expanded the server and one of the databases so you can see how much detail is available in Object Explorer. Knowing what was already possible, thanks to my experience with the mssql extension for VS Code (SQL Operations Studio includes all of those features), the addition of this explorer feature sold me instantly on SQL Operations Studio. But, of course, there’s more.

Figure 2 The Object Explorer and Servers Dashboard

Dashboards Provide Easy Access to Stats and Management Tasks

Servers and databases both have dashboards and you can customize what’s on them (more on that shortly). If you right-click a database within the server database and choose Manage, you’ll see that it also has a Backup Task button.

Returning to the icons at the top of Object Explorer, the middle one is for creating server groups. I’ll click on that and use the simple form to create a group named DockerServers and assign it a color. Then I’ll create a second one called AzureServers.

Both of these groups will now show in the explorer window. I can drag the existing localhost onto the DockerServers Group bar and it will move it into that group, as shown in Figure 3.

Figure 3 Server Groups in the Servers Explorer

Next, I’ll create a connection to one of my Azure SQL servers. There are a few paths, but because I want it in the AzureServers group, I can click on the green bar and choose New Connection from its context menu. Back in the connection window, you’ll see that the last option provided is Server group. That will be auto-populated with AzureServers and then I can fill out the rest of the information to connect to my Azure SQL Database server. After I’ve made the connection, I can see that server, its databases and the dashboard, which tells me it’s an Azure SQL Database (see Figure 4). Notice that the Backup and Restore tasks aren’t available. Azure SQL Database backs up data automatically, so there’s no need to have an explicit task for triggering such operations. Restore works differently in Azure SQL Database, so I’m hoping to see a Restore task for Azure SQL Database in a future update.

Figure 4 The Servers Dashboard for an Azure SQL Database Server—SQL Operations Studio Doesn’t Show the Backup Task for Azure SQL Database Because Backups Are Automatic

Interacting with Data

Let’s work with some data! I added the tried-and-true AdventureWorksLT database into my Azure SQL Database server because it’s available as a sample when you create a new server. Another bonus is that it’s prepopulated with lots of data. I’ll do something I often do in SSMS—manually edit some data from a table. Expanding AdventureWorksLTSample again, I’ll right-click on the SalesLT.Customer table in the explorer, which displays a context menu filled with functions, as shown in Figure 5.

Figure 5 The Context Menu for a Database Table

I’ll choose Edit Data to open up the grid shown in Figure 6, which has a Max Rows dropdown defaulted to 200 (a safe bet when pulling data over the Internet. The other options are 1,000 and 10,000.) I edited the Title in the first row, which was saved automatically when I moved to another cell. To see if the table really updated to Azure, I then opened a new query window (CMD+N) and was happy to have IntelliSense help me type SELECT * from SalesLT.Customer, prompting me for the available schemas and objects. I was also pleased to discover I could use the familiar F5 keystroke to execute queries—something you can’t do in VS Code because so many tools and extensions have to share the keyboard shortcuts.

Figure 6 Editing Data in SQL Operations Studio

While you can edit data directly, you can’t yet edit the database schema visually. The Script as Create menu item is the closest you can get in these early days of SQL Operations Studio to being able to modify schema in the database.

Customizing the Dashboard

Most of the features of the mssql extension for VS Code are in SQL Operations Studio. If you’ve used that extension already, or read my article (msdn.com/magazine/mt809115) or watched my Pluralsight course (bit.ly/PS_MSSQL), you may already be familiar with the many features of the SQL editor, as well as the available snippets for writing and executing queries and commands against a SQL Server database. The query results window, with its ability to export results to CSV, JSON or Excel files, is another feature that came from the extension. In SQL Operations Studio, a new addition to the query results window allows you to tap into some of the amazing extensibility in this IDE.

Let me demonstrate and then do a big reveal of something I’ve never done in SSMS as I complete this little demo. In preparation for this functionality, it’s time to work with the file system. Create a folder on your computer where you’ll save some of the SQL you’ll be writing.

Back in SQL Operations Studio, click the File Explorer icon in the Activity Bar and then the Open Folder button to open the folder you created. The File Explorer will display the folder and any files within (currently there are none).

You can create a new file inside the folder by clicking the “new file” icon to the right of the folder name in the File Explorer window. Be sure to give it a .sql extension. As shown in Figure 7, I’ve called mine TableSizes.sql.

Figure 7 Snippets to Help with Tricky SQL Commands

I’m interested in some metadata about my AdventureWorksLT database: how many rows are in each of the tables and how much space are they taking up on my drive? Or, in this case, how much storage in my Azure account? Rather than spending hours trying to figure out how to write that query, thankfully there’s a snippet that gives me just what I need, plus a bit more metadata along the same lines.

In the editor window, start by typing SQL and you’ll see a list of the snippets. SQLGetSpaceUsed, shown in Figure 7, is the one I’m looking for. You can tab to auto-complete the snippet name and then hit enter to display the snippet’s SQL in the editor window. The snippet has a placeholder to type in a table name, but I don’t want to filter on a particular table; I want all of the tables. Scroll down to line 20 of the SQL and remove the following line:

WHERE TABL.name LIKE '%TableName%'

The query returns the following columns:

INDX.name AS index_name,
SUM(PART.rows) AS rows_count,
SUM(ALOC.total_pages) AS total_pages,
SUM(ALOC.used_pages) AS used_pages,
SUM(ALOC.data_pages) AS data_pages,
(SUM(ALOC.total_pages)*8/1024) AS total_space_MB,
(SUM(ALOC.used_pages)*8/1024) AS used_space_MB,
(SUM(ALOC.data_pages)*8/1024) AS data_space_MB

I only want two pieces of data, the rowcount, now named Rows, and a twist on the data_space_MB. I’ve deleted all the other lines above and modified the two I want to keep:

SUM(PART.rows) AS Rows,
(SUM(ALOC.total_pages)*8/1.024) AS Bytes

Notice that I changed the total space from megabytes to bytes by multiplying by 1.024 rather than 1,024. I have my reasons and you may agree when you see the results. Be sure to save the file. I’ve modified the VS Code files.AutoSave setting to save “AfterDelay” so I don’t have to remember.

Now run the query and you’ll be prompted to choose the connection on which to run it. If you don’t specify a database, the query will be run against the master database. A handy recent connections list should make it easy to set the connection without having to fill the form out manually again. I could have created the new query window from the server, database or table and avoided having to select the connection for the query to run on, but then I would have had to specify where to save the .sql file. But because I happened to start with the file, I have to explicitly choose the connection. After selecting the connection, you’ll see a grid of the query results displayed below the editor window.

To the right of the results, there are four icons. The first exports the results to a CSV file, the second to JSON, the third to Excel—all functionality that came over from the mssql extension. The fourth icon is new to SQL Operations Studio. It will create a graph from the data, with a variety of graph types to from which to choose. I was surprised by this feature—it’s one that’s reminiscent of sophisticated business intelligence (BI) tools. A horizontal graph suited my needs. In Figure 8, you can see the row count in pink and the bytes in blue. The reason I calculated bytes, not megabytes, is because the megabyte values would have been too small to appear on the chart.

Figure 8 A Graph Generated from Query Results

That’s already pretty cool, but wait, there’s more! Copy as image and Save as image are great features, but they pale in comparison to the third option. Click the Create Insight button above the graph to open a new window with JSON that describes the graph in the form of a widget (see Figure 9). Widgets are a powerful feature of SQL Operations Studio that can help you create a lot of visual customizations.

Figure 9 The Widget Code Created from the Graph View of the Query Results

{
  "name": "My-Widget",
  "gridItemConfig": {
    "sizex": 2,
    "sizey": 1
  },
  "widget": {
    "insights-widget": {
      "type": {
        "horizontalBar": {
          "dataDirection": "vertical",
          "dataType": "number",
          "legendPosition": "none",
          "labelFirstColumn": false,
          "columnsAsLabels": false
        }
      },
      "queryFile": "/Users/julialerman/Documents/sqlopsstudio/TableSizes.sql"
    }
  }
}

Notice the queryfile value pointing to the .sql file I saved in my project folder. The widget knows to execute that query when it comes into view. If you make changes to the query in the file, those changes will be reflected in the widget the next time it’s run. I want to tweak two things that I didn’t modify in the Chart Viewer, which I can do directly in the JSON. I’ll change the value of legendPosition to “top” and columnsAsLabels to “true.”

The next task is to get this widget into the SQL Operation Studio settings. Copy the full text of the JSON file, then open the settings window again (CMD+,). On the left pane where the default settings are listed, use the search box to find “dashboard” and look within the results for dashboard.database.widgets. Hover over that text and a pencil icon will appear to the left. Click the pencil icon and select the “Replace in settings” menu option that appears. This will cause the entire section of the default setting to be copied over to the user settings panel on the right. VS Code is so cool, isn’t it? Now, paste the JSON you copied above the opening brace for the Tasks widget, as shown here, and follow it with a comma:

"dashboard.database.widgets": [
  **paste your new widget here**       
  {
    "name": "Tasks",

Save the settings file and close it. Then, back in Object Explorer, right-click on one of the databases and choose Manage to see its dashboard. The new widget appears on the dashboard (see Figure 10) and on the dashboard of every database you open. The query it’s tied to will run on-demand when you open the dashboard for a current view. This widget won’t appear on the Servers dashboard because I specifically placed it in the Database dashboard settings, where it makes the most sense. But imagine the types of metadata you can expose visually on the dashboards to, for example, see which queries are running slowly or perform other health checks or view important statistics. You can also use the settings to control how widgets are laid out in the dashboard.

Figure 10 The Dashboard for the AdventureWorksLTSample Database with the New Widget in Place

We’ve Just Scratched the Surface

There is so much more to discover and do in SQL Operations Studio. Here are a few extra tidbits before wrapping up.

You may have noticed the Explain button on the query window. It will show you query plans just as you see them in SSMS, with an alternate grid view, as well.

The file you created for the TableSize query can now be tracked and shared with the integrated source control. I’ve already been doing that in this project to save some queries that I spent too much time working out. If your team is using source control already for your databases, you’ll find many more sophisticated uses for this feature.

Take a look back at the dashboard in Figure 1 and notice the search widget. A Server dashboard will show a list of its databases and you can easily search for database objects rather than perusing through the Object Explorer. The Database dashboard will show a list of tables, views, functions and procedures, and you can search for database objects by name there, as well.

You can learn so much more about SQL Operations Studio in the official docs at aka.ms/sqlopsstudio, where you’ll find detailed documents about its features, as well as walk-throughs. Remember that SQL Operations Studio was spawned from VS Code, which already has more than 4,500 extensions, most of which have come from the community. In addition to the enormous amount of work that the SQL Data Tools team is pouring into SQL Operations Studio, this new tool will likely take on a life of its own when its own ecosystem of extensions begins to evolve.

Head over to aka.ms/sqlopsstudio to download SQL Operations Studio for Linux, macOS and Windows and check out the Getting Started guides. You can watch videos of SQL Operations Studio at aka.ms/sqlopsstudio-tutorial. And don’t forget to provide feedback, file issues, make suggestions and submit pull requests to improve SQL Operations Studio at github.com/microsoft/sqlopsstudio.

Julie Lerman is a Microsoft Regional Director, Microsoft MVP, software team mentor and consultant who lives in the hills of Vermont. You can find her presenting on data access and other topics at user groups and conferences around the world. She blogs at the thedatafarm.com/blog and is the author of “Programming Entity Framework,” as well as a Code First and a DbContext edition, all from O’Reilly Media. Follow her on Twitter: @julielerman and see her Pluralsight courses at juliel.me/PS-Videos.

Thanks to the following Microsoft technical experts for reviewing this article: Eric Kang and Sanjay Nagamangalam

Discuss this article in the MSDN Magazine forum