December 2018
Volume 33 Number 12
[Data Points]
Manage Data Across Multiple Sources with Azure Data Studio
By Julie Lerman | December 2018
.jpg "Julie Lerman")
Just about a year ago, I introduced you to the new SQL Operations Studio, a cross-platform, lightweight IDE for working with various flavors of SQL Server (bit.ly/2RlZKuW). SQL Operations Studio was in preview at the time, and has since gone through many transformations. It was officially released at the Microsoft Ignite conference in September, renamed as Azure Data Studio. While the new name doesn’t mean the elimination of non-Azure SQL Server usage, I’m personally hoping it does suggest that, someday, we’ll be able to use the product for other types of data stores on Azure. Compared to the version I wrote about a year ago, the biggest story regarding the new release is the extensions that are now available.
Rather than repeat information about features that existed in the earlier version, in this article I’ll provide a light overview for those of you who are totally new to the application and then explore some of the extensions that piqued my developer brain.
Azure Data Studio is billed as a complementary app to SQL Server Management Studio (SSMS), with a focus on being a low-impact way to execute queries. I think there will be a subset of folks who use both, with Azure Data Studio additionally finding a user base of developers who’ve never used SSMS. While there are definitely features that will be of interest to DBAs, SSMS will always be the deep-dive administration tool.
Azure Data Studio grew from the intersection of the cross-platform Visual Studio Code (VS Code) and its mssql extension—which I wrote about in my June 2017 column (msdn.com/magazine/mt809115)—eventually becoming its own application. Also, like VS Code, Azure Data Studio is open source and you can get involved at github.com/microsoft/azuredatastudio.
You can download the relevant installers for Windows, macOS or Linux from bit.ly/2Rl14yl. To give you an idea of how different the installation experience is from SSMS, the installer file for the Windows app is 76MB, compared to 800MB for SSMS 17.9, and it installed on my laptop in less than two minutes. Like VS Code, Azure Data Studio is easily configurable through its JSON configuration files, as well as being quite extensible. Azure Data Studio already has more than a dozen extensions, some from Microsoft and some from the community.
Another important feature inherited from VS Code is the interactive terminal where you can run commands from CLIs like PowerShell, Bash and the cross-platform sqlcmd command-line utility. Azure Data Studio also inherits the VS Code File Explorer, which lets you interact with folder-based projects that contain files such as SQL you’ve written or text-based data files. And you can use the built-in source control to manage and share those projects.
I won’t go over the Azure Data Studio capabilities I wrote about in the earlier SQL Operations Studio or mssql articles. There, you can read about such features as the built-in SQL snippets, the ability to create your own custom snippets, and the amazing graphing feature that lets you visualize query results and even embed them as custom widgets on the dashboards for a server or a database.
The experience of using Azure Data Studio is the same on any supported OS. I’ll use my MacBook because it’s a fun change of venue for me. As I walk through some of the features, I’ll assume you have some familiarity with getting around VS Code or earlier versions of SQL Operations Studio.
Azure Data Studio allows you to connect to a variety of SQL Server types. You can connect to an on-premises server on your machine or network, a localdb instance, SQL Server for Linux, SQL Server running in a Docker or Windows container, SQL Azure, or SQL Azure Data Warehouse. There’s also preview support for the recently released Azure SQL Database Managed Instances.
I’ll start here by creating a new connection to a SQL Server on my network. The new connection wizard provides a connection form where I’ll fill in the required information. I’m using SQL authentication and can prompt it to remember my password. After I’ve connected, the Azure Data Studio Server Explorer lists the available databases (Figure 1), with nodes for Tables, Views, Programmability, Security and others you’re used to seeing if you use SSMS. You can right-click on a table to view or edit data and build scripts, features that already existed in SQL Operations Studio.
.png "The Server Explorer Showing a Connected Network SQL Server with Its Default Management Window")
Figure 1 The Server Explorer Showing a Connected Network SQL Server with Its Default Management Window
Flat File Imports Using AI
The SQL Server Import extension, which is the result of a hackathon Microsoft held this past summer, is currently in preview and can already import flat data TXT and CSV files. Eventually, it will also be able to import JSON files.
This importing ability is no small feat and is driven by artifical intelligence (AI) using a new technology from Microsoft Research called Program Synthesis Using Examples, or PROSE (microsoft.github.io/prose). As Alan Yu, a SQL Server Program Manager, says, PROSE “can automatically detect a file’s data types, delimiters, column names and file structure without the user having to explicitly define the configuration.”
Although the export to CSV, JSON or XLSX features aren’t new (though their icons are slicker than before), I’m going to start with an export in order to show off the flat file import. And in order to get some data to export, I’ve used the table context menu’s SELECT TOP 1000 option to select all of the rows from the HumanResources.Employee table of the tried-and-true AdventureWorks database. Figure 2 lets you see the context menu, along with the query pane, results pane and messages pane that resulted from the selection. The display and formatting of all of these panes can be easily configured in the same way you configure VS Code (bit.ly/2lEaFoc). In fact, there are more than 350 customizations you can make in the settings to affect how Azure Data Studio behaves! A red arrow in the image notes the export to CSV icon, which I used to create a file called HREmp.csv. The default settings for exporting CSV files include the column names in the output. With that in hand, I can now show off the flat file import extension that I’ve already installed.
.png "A Full View of Azure Data Studio While Exporting Selected Rows to CSV")
Figure 2 A Full View of Azure Data Studio While Exporting Selected Rows to CSV
The extension adds an “Import wizard” menu item to the context menu you get when right-clicking a database in the Server Explorer. As in VS Code, every function has a keyboard command (this one is Ctrl+I) and is accessible from the Command Palette (F1).
The import feature is for importing data into new tables, not existing ones. A form lets you specify the server and database (defaulting to the one you selected earlier), the file to import, and the name and schema for the new table. It then displays a preview of its interpretation of the first 50 rows from the file. The extension makes this look easy, but remember, that magic is thanks to the use of AI by PROSE, which I described earlier.
The next step after previewing the data gives you a chance to change how the data is mapped to columns. You can modify the inferred column names and data types, as well as set primary keys and column nullability. While you can’t currently do things like remove or add a column, remember that the extension is built within the GitHub repository for the app that I linked to earlier. The team is eager to know what file formats and other related features you’d like to see. You can participate in the discussion at bit.ly/2IXv8wd.
OMG, Cross-Platform SQL Profiling!
Another extension that’s also an early preview as I’m writing this is the SQL Server Profiler extension. Database profiling is a critical step in developing any app. I’ve made heavy use of the Windows-based SSMS Profiler stand-alone application for decades. Even so, I’m still incredibly clumsy using it—getting the columns I want, applying filters with “magic strings” and more. I don’t know if it has ever been updated. I also take advantage of the .NET Core logging capabilities in my apps to see the SQL sent to the database. In fact, my last column was about EF Core logging (msdn.com/magazine/mt830355). But sometimes I really want the database’s perspective. And if I’m not on Windows or not on a machine that has the profiler available, I’m at a loss. So, the fact that this extension is part of the cross-platform application feels almost magical to me.
The profiler extension was written using a SQL Server database feature called Extended Events (XEvents). There’s also a flavor of XEvents for SQL Azure. You can read more about XEvents at bit.ly/2LfWMoj. The extension’s ReadMe indicates the subset of SQL Profiler use cases on which it focuses:
- Stepping through problem queries to find the cause of the problem.
- Finding and diagnosing slow-running queries.
- Capturing the series of Transact-SQL statements that lead to a problem.
- Monitoring the performance of SQL Server to tune workloads.
- Correlating performance counters to diagnose problems.
Before you can start a profiling session, you need to first connect to a database. Then you can open the profiler extension (Alt+P or Ctrl+Opt+P). You’ll be prompted to select a session template and provide a name for the session. Three templates are included with Azure Data Studio: Standard_OnPrem, Standard_Azure or TSQL_OnPrem. The pre-defined standard templates display all events while TSQL displays only the logged TSQL. These templates are defined in the settings, specifying which events are relayed and which filters are applied. You can create your own session templates in the settings, as well.
The profiler will open in its own editor window and automatically start. You can stop and restart the profiler any time using its buttons or a keyboard shortcut—Alt+S (Windows) or Ctrl+Opt+S (Mac).
Initially, when I attempted to start the profiler, I got an error stating “the user does not have permission to perform this action.” That’s because the SQL Login I had created had limited permissions. Remember, I’m a dev, not a DBA. I fixed that by granting the ALTER TRACE permission to my login. In addition to profiling my network database, I later switched to profiling the SQL Server for Linux database running in a Docker container on the same MacBook where Azure Data Studio was running. (See my article at msdn.com/magazine/mt784660 to learn more about SQL Server in Docker.)
As Figure 3 shows, standard output will display all events, which, if you’ve used the SSMS profiler, you know can be very chatty. Once you’ve created the session you can fine-tune what’s displayed with two dropdowns. The first dropdown lets you select from your custom sessions or from three predefined sessions (AlwaysOn_health, system_health and telemetry_xevents) that are built into SQL Server. You can find the details on what’s tracked in the system_health session, for example, at bit.ly/2xZcFuP.
.png "Standard View Displaying Many Details from All of the Events from a Query")
Figure 3 Standard View Displaying Many Details from All of the Events from a Query
Note that you can’t change a session when the profiler is running.
The second dropdown provides different views of the captured events: Standard, TSQL, Tuning, TSQL_Locks or TSQL_Duration. Each view is a combination of event filters and the columns that are displayed. For example, the TSQL view displays only EventClass, TextData, SPID and StartTime, as shown in Figure 4. The Tuning View shows those four columns, as well as DatabaseID, DatabaseName, ObjectType and LoginName. Like the session templates, the view templates are also predefined in settings so you can edit the settings to create your own views that will then be available on the dropdown.
.png "TSQL View Shows Minimal Info, Only About SQL Events")
Figure 4 TSQL View Shows Minimal Info, Only About SQL Events
There’s more to learn about using the profiler, but because it’s still an early preview, I expect that by the time you’re reading this, there will be new features for you to explore.
Data Science with the SQL Server 2019 Preview Extension
SQL Server 2019, which is currently in preview, has some interesting new features related to Big Data. To go with that, there’s an Azure Data Studio extension for SQL Server 2019 (also in preview) that allows you to leverage these features within the IDE. With this combination, Azure Data Studio also becomes a tool for data scientists. SQL Server 2019 offers data clusters with Spark Hadoop Distributed File System (HDFS) clusters, and Azure Data Studio lets you query this data (using SQL) alongside your relational data. SQL Server 2019 also allows you to connect to and virtualize external data, for example, from MongoDB or Oracle. Once connected you can query this data, as well. With Azure Data Studio you can query across both relational and scalable data sources, even joining those resources in your queries. Azure Data Studio also lets you create and use Spark notebooks where you can encapsulate and share your resource connections and queries. These big data features are very new to me and if they are to you, too, I’d highly recommend starting with the five-minute video at bit.ly/2zTgkMl, in which the SQL Server team uses Azure Data Studio and this extension to demonstrate the big data features.
Browse Azure Resources from Azure Data Studio
Azure Data Studio and its predecessor have always been able to connect to SQL Azure. But there’s a new feature for browsing your SQL Azure resources and easily connecting to them. This feature started in the SQL Server 2019 extension but is now part of the core application. There’s an Azure logo icon on the Activity Bar. The first time you use it you’ll be prompted to log in to Azure and allow Azure Data Studio to connect. Then, as shown in Figure 5, this view will list all of your accounts and, underneath, all of the Azure SQL Servers and databases within those servers. Then you can just select the database to connect to, which will prepopulate the connection form. All you should need to provide is the password for connecting to the database. In my case, I also had to enable my IP address to connect, but the extension walked me through that. I didn’t have to open up the Azure Portal to sort it out.
.png "Browsing Your SQL Azure Resources with the SQL Server 2019 Extension")
Figure 5 Browsing Your SQL Azure Resources with the SQL Server 2019 Extension
Speaking of connecting to SQL Azure, when I was working on the previous SQL Operations Studio article, I added a suggestion in GitHub that the team give users a way to copy Azure SQL connection strings from the portal and somehow paste them into Azure Data Studio as another simple way to connect. This is now a feature built into Azure Data Studio, not part of the extension. And it’s super cool. If you copy the connection string from the portal, and then in Azure Data Studio click the Create new connection icon (or do that via command palette), the connection form will be prepopulated with all of the details from the connection string. When I did this, the password was populated, even though it wasn’t in the connection string I created. Azure Data Studio had remembered that from the previous connection I’d made to another Azure SQL database.
More Extensions
I’ve highlighted three of the extensions that appealed to me as a developer. But I do want to be sure you’re aware of some of the other extensions, some of which will appeal to both devs and DBAs. RedGate created a SQL Search extension. Microsoft built an extension around Adam Machanic’s sp_whoisactive activity monitoring tools. It also created extensions for SQL Server Agent, Azure SQL Warehouse Data Insights and Server Reports, which includes, for example, database space and buffer usage, CPU utilization and wait counts. There are extensions from MVPs for gaining more insights and merging scripts. And if you’re a keyboard-shortcut fan, you’ll be interested in the extension to import popular keymaps from SSMS into Azure Data Studio that was created by Kevin Cunnane from the SQL Server tools team.
If you’re interested in building extensions, you can find help in the documentation at bit.ly/2zTkROR.
I now have Azure Data Studio installed on every single computer on which I work. Desktops and laptops, Windows and macOS. I even have it on the Windows desktop computer where I have full-blown SQL Server and SSMS installed and find that I’m reaching for Azure Data Studio on that machine much more frequently than SSMS because of the nature of the tasks I am typically performing.
Julie Lerman is a Microsoft Regional Director, Microsoft MVP, software team coach 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 bit.ly/PS-Julie.
Thanks to the following Microsoft technical expert for reviewing this article: Alan Yu
Alan Yu is a Program Manager on the Azure Data team at Microsoft. He works on creating open source and cross-platform tooling for SQL Server and cloud databases, including Azure Data Studio, command line tools like mssql-cli, and cluster admin portal experiences for SQL Server 2019. To follow the exciting things his team is working on, follow @AlanYuSQL on Twitter.