I was looking forward to do some work with Azure SQL Data Warehouse (DWH) when I hit the new query button and had the following message pop up –

Cannot connect to database when it is paused. (.Net SqlClient Data Provider)

I totally forgot that with Azure SQL DWH you can pause and resume compute, to save money because it is expensive. Question is how do you go about resuming compute? TSQL is not possible and sure you can do the change via Azure portal but what about PowerShell?

By the way if you are curious as to what sys.databases shows, well it shows that it is online and quite normal but I promise you, you cannot connect to it.

 SELECT name, create_date, user_access_desc, state_desc FROM sys.databases
WHERE database_id = 14

When in SQL Server Management Studio > databases node, it shows a nice DWH icon.

Anyway, back to the point of the post. Resuming. I use the in-built Cloud Shell.

Get-AzureRmSubscription

I have multiple subscriptions so I pick the right one.

 Select-AzureRmSubscription -SubscriptionName "Visual Studio Enterprise"

Now I am in the “right area” I issue the RESUME code.

$database = Get-AzureRmSqlDatabase –ResourceGroupName "My_RG" `
–ServerName "AzureSQLSERVER01" –DatabaseName "AzureDWH"
$resultDatabase = $database | Resume-AzureRmSqlDatabase
$resultDatabase

 

 

What about SSMS?

I am good to continue now, time to check out those execution plans.