Enhanced capabilities to monitor, manage, and integrate SQL Data Warehouse in the Azure Portal

Posted on 12 April, 2018

Program Manager, SQL Engineering

Azure SQL Data Warehouse (SQL DW) continues to introduce updates to the Azure portal to provide a seamless user experience when monitoring, managing, and integrating your data warehouse.

Support for Azure Monitor metrics

SQL DW now supports Azure Monitor which is a built-in monitoring service that consumes performance and health telemetry for your data warehouse. Azure monitor not only enables you to monitor your data warehouse within the Azure portal, but its tight integration between Azure services also enables you to monitor your entire data analytics solution within a single interface. For this release, data warehouse metrics have been enabled to enables you to identify performance bottlenecks and user activity:

  • Successful/Failed/Blocked by firewall connections
  • CPU
  • IO
  • DWU Limit
  • DWU Percentage
  • DWU used

These metrics now have a one-minute frequency for near real-time visibility into resource bottlenecks of your data warehouse. There is a default retention period of 90 days for all data warehouse metrics with Azure Monitor.

Configure metric charts in the Azure monitor service through the Azure Portal or programmatically query for metrics via PowerShell or REST:

AzureMonitor_Support

Pin configured charts for your data warehouse through Azure dashboards:

AzureMonitorDashboard_Support (002)

Safely manage costs by pausing

The pause feature for SQL DW enables you to reduce and manage operating costs for your data warehouse by turning off compute during times of little to no activity. We have enhanced this feature within the Azure portal by detecting active running queries and providing a warning before issuing the pause command. Pausing will cancel all sessions to immediately quiesce your data warehouse before shutting it down. This can sometimes lead to interruptions to your end user applications. Now with a simple click of the pause button in the Azure portal, you can detect the number of running queries to you can make an informed decision on when to pause:

Pause_DW (003)

You can also leverage the “dataWarehouseUserActivities” REST API to programatically integrate query detection in your applications.

Integrate with Azure Analysis Services

SQL DW is tightly integrated across many Azure services enabling you to develop an advanced modern data analytics solution. One common pattern is to leverage Azure Analysis Services (AAS) with SQL DW in a hub and spoke pattern to optimize for cost, performance, and concurrency. To enable seamless access to AAS, a tighter integration point within the Azure Portal has been enabled. Click on the “Model and Cache Data” button within the Task panel to immediately begin building and hosting semantic models of your data.

AAS_Portal (002)

 

AAS_view (002)

If you need help for a POC, contact us directly. Stay up-to-date on the latest Azure SQL DW news and features by following us on Twitter @AzureSQLDW.