Share via

Azure SQL Data Warehouse Don’t do this..

  • Article

I am always one of the first to try new stuff when it comes out and so it was great for Microsoft to give me job doing this some 99 months ago.  One of the latest things I signed up for was the Azure SQL Data Warehouse (ADW), and then setup a demo ADW so I could test it against Power BI.  The good news is that had an internal cross charged Azure subscription to run this in, the bad news was that last month this cost me $1,000+..

image

Fortunately, we are allowed to make mistakes at work the trick is not to make the same one twice!!

In this case the mistake was that I made was to leave this running and at this point it’s important to understand that while an ADW server looks a bit like an ordinary Azure DB server it’s actually a very different thing (apart from the cost that is).  In Azure DB Server you can quickly scale up and down a server but you can’t completely shut down the server and leave your database parked there for later. 

However not only can you pause ADW, you can do this safe in the knowledge that your data will still be there for later when you want to resume your work.  I should also mention that of course ADW scales massively more than ADB.  In the screenshot below I have two databases running on my DeepFatDW Azure database server

image

where TechNetDW is a data warehouse and DeepFat is an “ordinary” database which cannot be paused. ( but can be scaled up and down to a certain extent).

I can pause and restart this ADW and I could have done this in PowerShell, for example

Suspend-AzureSqlDatabase`

–ResourceGroupName "ResourceGroup11"`

–ServerName "DeepFatDW" `

–DatabaseName "TechNetDW"

Plus I can also dial up/down the compute power against the database, without affecting the data itself, so I could have a truly massive DW with only minimal compute against it until I realise I need more power 

Set-AzureSQLDatabase

-DatabaseName "TechNetDW"`

-ServerName "DeepfatDW.database.windows.net" `

-ServiceObjective "DW100"

and I could go further and embed this in Azure Automation to schedule a scale down or pause overnight.

So ADW is a great example of how services should work in the cloud, where scale down to save costs is as important as scale up, while data is protected, and you are in control.