Webinar Recap
Azure Data Studio (ADS) is a lightweight, browser-based query tool that enables access to all Microsoft on-premises and cloud data platforms from clients running Windows, macOS, and Linux. SentryOne Plan Explorer, the free and wildly popular SQL Server query tuning tool, now has a powerful extension for ADS, giving you the ability to run as a standalone application, from SSMS, and from ADS.
I recently teamed up with Warwick Rudd (@Warwick_Rudd), Data Platform MVP and Principal Consultant at SQL Masters Consulting, for the webinar, “Introduction to Azure Data Studio and the Plan Explorer Extension.” We introduced attendees to ADS and Plan Explorer and provided an informative demo that illustrated:
- Dashboards
- T-SQL Snippets
- Code Definitions
- Query Executions and Result Outputs
- Custom Insights for Your Dashboards
- Source Control
- Extensions
- SQL Notebooks
If you missed the webinar, you can view it here.
During the webinar, Warwick and I received a lot of great questions, which we answered below in case they are helpful to you.
Webinar Q&A
Q: With the availability of Power Query in Excel, why would anyone use ADS for reporting/graphing? Excel is so much better.
A: There are over 300 million users of Excel worldwide, and many business analysts are using SQL Server. We don’t have any idea how many users there are who are active with Power Query. So, it’s logical to assume that many people still do things the old-fashioned way of loading data into Excel and then graphing it, rather than the new way of using Power Query to pull the data into an Excel spreadsheet directly.
Q: What is the replacement for creating SQL Server Agent jobs in ADS?
KK: Warwick covered that during the webinar. There is a SQL Server Agent extension you can plug into ADS.
Q: Does ADS support Central Management Servers (CMS)?
WR: CMS is supported in ADS as an extension that you can download and install. Currently—as of the September 2019 release of ADS—this extension is available from the extension marketplace and is in preview.
Q: What about PowerShell? Can dbatools be added as an extension?
WR: PowerShell is supported in ADS, and, as shown in the webinar, there is a specific PowerShell extension. At this point in time, there is no extension for dbatools. You download and install dbatools on your workstation or laptop and from within ADS you are able to run PowerShell that is available as part of the download from dbatools.io.
Q: Does ADS provide SQL code formatting functionality?
WR: ADS does provide code formatting, and not just for T-SQL. You can right-click on your script and select Format Document. If you have installed the PostgreSQL extension, you will need to select your default format. There is also the Poor SQL Formatter extension available for installation. This extension uses the Poor Man’s T-SQL Formatter.
Q: Is there a query designer like you have in SSMS?
WR: To my knowledge, I do not believe there is a query designer like what is in SSMS. This could be created as an extension.
Q: Can you search within the result set returned?
WR: I do not believe this is a supported functionality in either SSMS or in ADS.
Q: Can the output be saved or printed directly?
WR: ADS does allow you to save the result to any of the following output types:
- CSV
- Excel
- JSON
- XML
- Chart
- SandDance Visualizer
Q: Is ADS using Profiler or Extended Events in the back end?
A: The SQL Server Profiler extension in ADS uses Extended Events. You can even tweak the setup if needed (as long as the name is the same).
Q: Does ADS work with Extended Events?
WR: ADS does not have the same Extended Events interface as is available in SSMS. Depending on demand, this could potentially be created as an extension.
Q: Can you filter with Profiler when running it in ADS?
WR: When you launch ADS Profiler in the menu bar of the Profiler tab, you do have the option to set filters. All the same options that are in SSMS are available in ADS Profiler.
Q: Is it possible to set all of this up on one computer, and then pass a config file to other people on my team to save time?
WR: While I haven’t tried this personally, it potentially could be done. However, there are caveats around this due to the installation of extensions. I believe if you are using extensions, they may need to be installed on the relevant workstations. That said, as this product evolves on a month by month basis, depending on the requirements, the ability group or common setup and configuration may be made available.
Q: Is the query executed again or is the report updated dynamically each time?
KK: Assuming you mean an Insight Widget, you must manually execute the queries to retrieve the widget information by pressing the F5 key or clicking the Run button. The widgets do not auto-refresh if I remember correctly.
Q: Can I interpret this as the Profiler extension displaying the UI using SQL Profiler style, but it’s collecting data using SQL Extended Events in the background?
A: Yes, it uses xEvents in the background.
Q: How does ADS work with CI/CD – DevOps?
WR: While DevOps does utilize various tools sets, DevOps is not about tooling. The tooling assists with the process. ADS allows you to get started with DevOps, as it has the functionality out of the box to work directly with source control and has Git commands built-in and allows for command line use of the commands also.
Q: What are Notebooks?
A: Notebooks originated in data science circles. They are similar to T-SQL projects and/or DACPACs in SSMS. However, you can include a lot more in a Notebook than just scripts and schema definitions. Another cool aspect of Notebooks, such as a Jupyter Notebook, is that you can both display code and execute the code directly within the Notebook.
Q: Do you need to use SQL Server 2019 to use Notebooks?
WR: To use Notebooks in ADS, you do require the SQL Server 2019 extension, but it is not necessary to have SQL Server 2019 installed.
Q: Can you set up ADS with a white background instead of black?
WR: Out of the box, the default ADS color theme is white. From the settings COG, File Menu, or the Command Palette there are a number of color themes available.
Q: Is the Notebooks feature in the demo only for SQL Server 2019 or is it an ADS feature that works for all versions of SQL Server?
WR: Notebooks are supported on versions prior to SQL Server 2019. I have run them against 2014 through 2019.
Q: How should I learn more on this to get employed using ADS possibly? I have tons of SQL Server experience. Should I follow the recommendations on the last webinar slide before the summary?
WR: ADS is another tool that allows you to more easily manage your SQL Server estate. The resource slides do provide starting points for the content that was covered in the webinar.
Q: Is ADS a free product?
WR: Yes. ADS is a free download, the same as SSMS.
Q: Can you import a CMS group of servers into ADS?
WR: I have not tried this personally, and as the CMS extension is still in preview, this option at the moment may not be available. I would need to test this out.
Q: Do you have to press F5 or ALT%2BX to execute a query?
WR: F5 does work or there is the Run button in the query window. There are many shortcut keys pre-configured and you have the ability to configure your own shortcut keys.
Q: I am trying to install Plan Explorer but keep receiving the error “This extension is not installable on any currently installed products.” I have ADS in Version 1.10.0 and .NET Core version 2.1.701 and 2.1.801, too. Any Ideas?
A: To install the extension, you have to go to File > Install Extension from VSIX Package. Many people try to double-click the VSIX file, but this is not how these files work. This is outlined in the preview blog post.
Q: Is there any way to generate scripts for objects or whole databases? Also, how does it work with BACPACs?
WR: Yes. If you right-click on the database, you will see the Generate Scripts option. This invokes the same generate scripts thatare used in SSMS. If you install the SQL Server Import extension, you can right-click on the database and you will see the Data Tier Application Wizard, which provides you with four options to choose from:
- Deploy Dacpac
- Extract Dacpac
- Import Bacpac
- Export Bacpac
Q: What extension do I need for Profiler and Jupyter Notebook?
WR: You’ll need the SQL Server Profiler extension for Profiler and SQL Server 2019 extension for Notebooks.
Q: Why do I need a SentryOne “cloud” account to use the Plan Explorer extension?
A: This is a simple gate like you see with most free software. It is a way we can tell you about updates, provide access to our support forums, and lets us know who’s using the extension.
Q: I was looking at a “feature comparison table” between ADS and SSMS and it says that SSMS can handle XE Management, but it “seems” that ADS doesn’t. How is that?
WR: ADS is not a direct replacement for SSMS. Think of ADS as coder-oriented and SSMS as admin-oriented. As the comparison table shows, there are several features that are supported in SSMS that are not in ADS. ADS gets its flexibility from the ability to pick and choose the types of extensions; for example, SQL versus Python. In time, various administrative functions may make their way into ADS.
Q: Are the maintenance plans that are available on SSMS also available on ADS?
WR: Maintenance plans are not currently available in ADS. They may or may not be included in the future. Ola Hallengren’s scripts are a much better approach for database maintenance over maintenance plans.
Additional Information
For more information, visit the SentryOne Plan Explorer Extension for ADS web page. Also, be sure to read the Plan Explorer extension for ADS blog post on SQLPerformance.com, which shares how to get the extension and what it entails.
For a general overview of Plan Explorer, be sure to view our demo or download it for free today.
