Getting Started with SQL Operations Studio

I’m a fan of new shiny things, and as a former SQL Server DBA the release of SQL Operations Studio definitely peaked my interest.

So, in the words of the Cat from Red Dwarf, “So what is it?”

(It’s not a White Hole…just in case you were wondering…)

The best description comes straight from the product team themselves, see here:

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.

So, it’s a lightweight, slimmed down, better looking cousin of SQL Server Management Studio. In fact, my first thoughts when I saw this made me think of the approach that’s been taken with Visual Studio Code, wherein we’ve stripped out all the “bloat” of Visual Studio to focus primarily on the code side of things, making it a much faster, simpler experience.

It’s still in preview, but is already looking really slick. I’ve had a play around with it on a project I’m working on right now and here’re some stand out thoughts:

It Looks Really Nice

Server Dashboard

Server Dashboard

Like Visual Studio Code it’s got a fresh, clean feel about it.  You can quickly dive into deeper content (similar to SQL Server Management Studio) and also customize the look and feel and suit your personal tastes.

Intellisense Works (I used to have so much trouble with this – thank the heavens for Red Gate)

intellisense

Intellisense Example

Intellisense works out of the box, as should be expected.

You Can Create Your Own Insight Widgets and Dashboards

addedwidget

Database Dashboard Showing Slow Query Widget

You can create your own custom widgets, and there’s also some Out-of-the-Box ones already available. The widget above shows the Top Worse Performing Queries, and is already available to use. See here for information on setting up the widget.

Which can be Further Customized:

sqlopswithdatabasetasks

Database Dashboard

In the above example, I’ve customized the Database Dashboard for my TutorialDB to show both the Tasks widget and Slow Queries widget. This is based on the tutorial here.

Source Control Integration

A must have is source control integration, and the preview offering of SQL Operations Studio supports Git for version/source control.

GitIntegration

Git Integration

See here for more information on setting up with Git.

Integrated Terminal

And finally, just like Visual Studio Code, you have support for Integrated Terminal, so you can interact directly with Powershell, Command Prompt and GitBash (at the time of writing). A lot of my Azure work involves interacting with Powershell so this is a really useful feature, plus the ability to work natively with SQLCMD, bcp etc is great.

In the example below I’ve also customized the theme as I prefer the darker themes in VS to the light ones.

TerminalSQLOps

Integrated Terminal Set For Powershell

Other Stuff

I’ve only lightly touched on the features available at Preview. As well as those above, the current version includes, amongst others:

  • Code Snippets
  • Save Results as Text, JSON or Excel
  • Server Groups
  • Edit Data

 

Summary

SQL Operations Studio is in public preview (my version is 0.23) still, so it’s not fair to compare it directly to SQL Server Management Studio (SSMS) at this point. I think even when generally available, it should still be considered a separate beast to SSMS, as Visual Studio is considered different to Visual Studio Code.

As it stands right now, it’s very clean and slick, and has some great functionality already. I can see definite use cases for this where developers and DBAs want to work in a lightweight code-focused editor that provides all the key functionality that’s required to work with and administer SQL instances. Time will tell if it’s sufficient to pull people away from the tried and trusted SSMS, and I’ll look to do a follow up post when we’re at GA.

Further Reading

SQL Operations Studio (Preview)

A Lap Around SQL Operations Studio – Channel 9

So What’s SQL Operations Studio?

SQL Operations Studio FAQs

Bugs/Suggestions

Optimizing SSIS Data Loads with Azure SQL Data Warehouse & PolyBase

To gain the best loading performance with Azure SQL Data Warehouse, you want to leverage the power of PolyBase, which allows for massively parallel data loading into the underlying storage.

In standard scenarios, ETL developers using SSIS will leverage a data flow that takes data from a source (Database, File, etc) and load it directly into the target database:

SSIS Simple Data Flow

Simple SSIS Data Flow (Table to Table)

Unfortunately, adopting this pattern with an SSIS data flow into Azure SQL Data Warehouse will not naturally leverage the power of PolyBase. In order to understand this we need to take a little peek under the covers of SQL Data Warehouse:

Azure SQL DWH Overview

Azure SQL Data Warehouse Overview

As part of the underlying architecture, SQL DW has at its head the Control Node. This node manages and optimizes queries, and serves as the access point for client applications when accessing the SQL DW cluster. What this means for SSIS, is that when it makes its connection to the SQL DW using standard data connectors, this connection is made to the Control Node:

SSIS Front Load

SSIS Front Loading

This method introduces a bottleneck as the Control node serves as the single throughput point for the data flow. As the Control node does not scale out, this limits the speed of your ETL flow.

As stated earlier, the most efficient way of loading into Azure SQL DW is to leverage the power of PolyBase in a process known as “Back Loading”:

SSIS Back Load

Back loading via Polybase

Using this method, data is bulk loaded in parallel from staging files held in Blob Storage or Azure Data Lake Store, which allows for the full data processing power of PolyBase to be used.

Leveraging Polybase Within SSIS

Previously, ingesting data from files into SQL DW using PolyBase was a slightly drawn out process of uploading files to storage, defining credentials, file formats and external table definitions before finally loading the data into SQL DW using a CTAS (Create Table As Select) statement. This works perfectly well, but introduces a lot of extra SQL coding on top of the norm.

Thankfully, with the SSIS Azure Feature Back, the above is now made much, much easier with the introduction of the Azure SQL DW Upload task. This process automates the creation of the SQL scripts required, allows you to seamlessly incorporate this task into an SSIS ETL process that will fully leverage Polybase.

Azure DW Upload Control Flow

Azure SQL DW Upload Control Flow

Azure DW Upload Task

Azure SQL DW Upload Task

Further Reading

Azure SQL DW Best Practices

Azure SQL DW With Azure Data Lake Store (ADLS)

Polybase Guide

First post

Greetings. This my first post for the SQL of the North blog. This blog will serve as a platform for sharing the latest and greatest from the Azure data & analytics world, as well as sharing hints, tips and best practices obtained in the field.

By way of background, my name’s Mike and I’m a DSA (Data Solutions Architect) at Microsoft, where I specialise in helping customers on their data journey into the cloud. I have over 15 years in the data and analytics space, mainly focused on the Microsoft platform, however I’ve picked up a few other useful skills and technologies along the way.

Finally – why “SQL of the North”? Well, I’m a northern (UK) lad by origin, and am also an avid Games of Thrones fan. I wanted something that had a bit of a link to that,  and unfortunately the “Data Watch” was already taken 🙂