Pause Idle Azure SQL Data Warehouse Instances

  • Article

Azure SQL Data Warehouse (SQL DW) instances are quite expensive to keep running, so it makes sense to Pause them if they are not being used.

This script will NOT resume them if connection attempts are made to a paused SQL DW instance.

The easiest way to check if it has been awhile since the SQL DW has 'done anything' is to query a system view called 'dm_pdw_exec_requests'.
The script (found here Pause-Idle-SQL-DW-Instances ) will

  • Get all the SQL Servers defined in a subscription
  • Loop through these SQL Servers and get all the online SQL DW instances
  • Connect to the SQL DW and query the dm_pdw_exec_requests system view to get the last time a query ended
  • If a specified threshold of minutes has elapsed, then pause the SQL DW instance.

You will need to add SQL usernames and passwords into a KeyVault as secrets in order to connect to the SQL DW instances.
The KeyVault Secret Name for UserName and Password is in the format

  • UserName: '{SQL Server Name}{SQL DW Instance Name}UserName'
  • Password: '{SQL Server Name}{SQL DW Instance Name}Password'