Database Has Been Paused

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)

TwixServer

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

itsonline

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

ssmsDWH

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

builtIn

cloudshells1

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

This is the output from PowerShell – looks good to me.

resumed

What about SSMS?

ssmsAZUREDWH.JPG

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

 

One thought on “Database Has Been Paused

  1. Pingback: Resuming Azure SQL Data Warehouse With Powershell – Curated SQL

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s