ERWIN & BUSINESS ANALYTICS

Azure SQL Data Warehouse: Reserved Capacity versus Pay as You go

by May 7, 2019

How do I use my Reserved Capacity correctly?

Update 11-11-2020: This also applies to Azure Synapse SQL Pools.

In my previous article you were introduced, how to create a Reserved Capacity for an Azure SQL Datawarehouse (SQLDW). Now it’s time to take a look at how this Reserved Capacity differs from an already working environment with an Azure SQLDW Pay as You go model where we already scale up and down during certain time periods.
 
In the example below I’m running an Azure SQLDW with the following capacity during the day.
 
Weekdays:
12:00 AM 4:00 AM 100   cDWU
5:00 AM 7:00 AM 3000 cDWU
8:00 AM 6:00 PM 1500 cDWU
7:00 PM 12:00 AM 100   cDWU

Weekenddays:

12:00 AM 4:00 AM 100   cDWU
5:00 AM 7:00 AM 3000 cDWU
8:00 AM 6:00 PM 500   cDWU
7:00 PM 12:00 AM 100   cDWU

We have separated the weekdays from the weekend days. The SQLDW is used less heavily during the weekend than during the week.

In our calculation we assume that we will purchase a Reserved Capacity of 3 years with 15 units of 100 cDWU. On the left site you will see the Pay as You go model and on the right site the Reserved Capacity.

The amount of Storage will be 8 TB.

As you can see in the example below.

Azure SQL DataWarehouse Reserved Capacity

Conclusions:

In the example we see that we have to pay extra if we exceed our Reserved Capacity. These extras are billed with the normal Pay as You go rate.

If we use the Reserved Capacity, we have 1500 cDWU available throughout the day so we don’t longer need to turn it off or scale it down during weekends or outside office hours. Otherwise the Reserved Capacity is wasted for that hour, it doesn’t carry over.
So we actually get more capacity and we pay less for it, sounds great or not!  More details can be found here.

In this example, we save nearly 2,750 euros a month, which is almost 33,000 euros a year and 100,000 euros during the 3-year Reserved Capacity period. And that is a considerable amount that you can use to develop new solutions.

 

Reserved Capacity Years Discount Month Year  Total Period Reserved Capacity Year Discount month year Total Period
1500 cDWU 3 65 2742 32914 98739,648 1500 cDWU 1 35 -2319,41 -27832,9 -27832,896
1000 cDWU 3 65 2261,952 27143,42 81430,272 1000 cDWU 1 35 -1112,83 -13354 -13353,984

In this situation we achieve the largest saving with 1500 cDWU with a Reservation of 3 years. When purchasing 10 units of 100 cDWU, we still save but slightly less. When purchasing Reserved Capacity for 1 year, a Pay as You go model will be cheaper.

 

Calculation Sheet

Since every situation is different, you will have to play with these quantities/units yourself. I have added the Excel form so that you can download it, on which I have based this article. With this form you can fill in your own situation as well as possible. And finally you can take your own conclusions for your customer or environment.

In the sheet only change the Green Marked cells. Prices are in Euro’s.

SQLDWH_-_pay-as-you-go_vs_reservedcapcity

This form has been created together with my colleague Maurice Veltman and we have used it for a solid calculation for 1 of our customers.

If you have any questions or comments about this article or the form,  just let me know.

 

Feel free to leave a comment

0 Comments

Submit a Comment

Your email address will not be published. Required fields are marked *

19 + twelve =

This site uses Akismet to reduce spam. Learn how your comment data is processed.

0 Comments

Submit a Comment

Your email address will not be published. Required fields are marked *

twelve + twelve =

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Azure Synapse Pause and Resume SQL Pool

Pause or Resume your Dedicated SQL Pool in Azure Synapse Analytics Azure Synapse Analytics went GA in beginning of December 2020, with Azure Synapse we can now also create a Dedicated SQL Pool(formerly Azure SQL DW). Please read this document to learn what a Dedicated...

Azure SQL Data Warehouse: Reserved Capacity versus Pay as You go

How do I use my Reserved Capacity correctly? Update 11-11-2020: This also applies to Azure Synapse SQL Pools. In my previous article you were introduced, how to create a Reserved Capacity for an Azure SQL Datawarehouse (SQLDW). Now it's time to take a look at how this...

Azure Synapse Analytics Power BI Integration

Creating a Linked Service for Power BI Open your Synapse Studio and select the Management Hub. Add a new Linked Service If you haven't connect to Power BI before, you will see the screen above. If you want to add another Power BI Linked Service(Workspace). Search for...

Create an Azure Synapse Analytics Apache Spark Pool

Adding a new Apache Spark Pool There are 2 options to create an Apache Spark Pool.Go to your Azure Synapse Analytics Workspace in de Azure Portal and add a new Apache Spark Pool. Or go to the Management Tab in your Azure Synapse Analytics Workspace and add a new...

How to setup Code Repository in Azure Data Factory

Why activate a Git Configuration? The main reasons are: Source Control: Ensures that all your changes are saved and traceable, but also that you can easily go back to a previous version in case of a bug. Continuous Integration and Continuous Delivery (CI/CD): Allows...

Azure SQL Data Warehouse: How to setup Reserved Capacity

Purchase your Azure SQL Datawarehouse Reservation   Since a few weeks you can buy Reserved Capacity for an Azure SQL Datawarehouse (SQLDW). This Reservation can save you up to 65% on the normal Pay as You go rates with a 3 year pre-commit. A pre-commit of 1 year...

Azure Data Factory Let’s get started

Creating an Azure Data Factory Instance, let's get started Many blogs nowadays are about which functionalities we can use within Azure Data Factory. But how do we create an Azure Data Factory instance in Azure for the first time and what should you take into account? ...

Connect Azure Synapse Analytics with Azure Purview

How do you integrate Azure Purview in Azure Synapse Analytics? This article explains how to integrate Azure Purview into your Azure Synapse workspace for data discovery and exploration. Follow the steps below to connect your Azure Purview account in your Azure Synapse...

SSMS 18.1: Schedule your SSIS Packages in Azure Data Factory

Schedule your SSIS Packages with SSMS in Azure Data Factory(ADF) This week SQL Server Management Studio version 18.1 was released, which can be downloaded from here. In version 18.1 the Database diagrams are back and from now on we can also schedule SSIS Packages in...

Create Virtual Machines with Azure DevTest Lab

A while ago I had to give a training. Normally I would roll out a number of virtual machines in Azure. Until someone brought my attention to an Azure Service, Azure DevTest Labs. With this Azure service you can easily create a basic image and use this image to roll...