Before writing about pausing (and resuming) Azure SQL Data Warehouse (DW) it makes sense to discuss the architecture of this product. At a high level it involves a control node, a MPP (Massively Parallel Processing) engine compromising of compute nodes and storage. Perfectly summarised by this image.

Reference: https://docs.microsoft.com/en-us/azure/sql-data-warehouse/massively-parallel-processing-mpp-architecture

Why would you want to pause compute resources? Well majority of the time it will be because of costs (you will still be charged storage costs but not compute resources). So what happens when we pause SQL DW? It basically causes the storage (Azure based) to detach from the compute nodes hence the compute nodes are released. During the pause resources are freed up and sent back to the pool within the data centre and most importantly your data is NOT removed.

To pause resource, you simply click a button using the Azure portal or just as easy, a PowerShell script.

Suspend-AzSqlDatabase –ResourceGroupName “myResourceGroup” `

–ServerName “sqlserverxyz” –DatabaseName “mydwh”

What happens to your transactions?

You will get the following message if your query is running whilst a pause is issued.

Msg 10054, Level 20, State 0, Line 5 A transport-level error has occurred when receiving results from the server.  (provider: TCP Provider, error: 0 – An existing connection was forcibly closed by the remote host.)

If the query was a basic select statement expect the message to appear quickly, this is due to the nature of the rollback for select statements. What about transactions that change data? Well it could take a long time because pausing has to wait for the rollback to complete before it can proceed, so if rollback has a lot to do then naturally it will take longer.

When you resume, at a high level SQL DW will re-allocate the compute and memory resources to adhere to your DWU (Data warehouse unit) setting, this means charges resumes and the Data warehouse will be online and available for end users.  Quite simply, execute the below.

Resume-AzSqlDatabase –ResourceGroupName “myResourceGroup” `

–ServerName “sqlserverxyz” –DatabaseName “mydwh”