Azure Cloud, IOPS, DTU and VM equivalence, PaaS vs IaaS MSSQL

I am building the next generation of Data Warehousing for a client and because the cloud’s sky is the limit it doesn’t mean they can afford to spend. Millions (25 million) of records need to be analyzed to produce meaningful reports and dashboard.

Cranking the power all the way up gives us the information we need in seconds. The highest database tier offered is something call P11. But what behind P11? Did I say this PaaS option was expensive?

First, it is not readily translatable into regular machines as the various tiers use a mix of computing, memory and disk IOPS and as per Microsoft: “a blended measure of CPU, memory, and data I/O and transaction log I/O…”. This blend unit is the Database Transaction Unit aka DTU.

Based on the performance trending it comes to something like this (courtesy of What the heck is a DTU?):

Number Cores IOPS Memory DTUs Service Tier Comparable Azure VM Size
1 core, 5% utilization 10 ??? 5 Basic Standard_A0, barely used
<1 core 150 ??? 100 Standard S0-S3 Standard_A0, not fully utilized
1 core up to 4000 ??? 500 Premium – P4 Standard_DS1_v2
2-3 cores up to 12000 ??? 1000 Premium – P6 Standard_DS3_v2
4-5 cores up to 20000 ??? 1750 Premium – P11 Standard_DS4_v2

Notice that this is a comparable size…and that P11 DTU is about CAD$15,000/month!!! And while PaaS is great, the price tag is a little too much for the common mortals getting into DWH.

Anyhow, trying to recreate a cheaper P11 I went on a picked a VM size of E8s_v3, because it said  “Great for relational database servers, medium to large caches, and in-memory analytics”. More sizes here: https://docs.microsoft.com/en-us/azure/virtual-machines/windows/sizes 

Size vCPU Memory: GiB Temp storage (SSD) GiB Max data disks Max cached and temp storage throughput: IOPS / MBps (cache size in GiB) Max uncached disk throughput: IOPS / MBps Max NICs / Expected network bandwidth (Mbps)
Standard_E8s_v3 8 64 128 16 16,000128 (200) 12,800 / 192 4 / 4,000

And so to host my data, I am adding the best disk I find on Azure:

This one, because it is closer to the size I need.
P20 disk is 512 GB, it can achieves 2300 IOPS and up to 150 MB/s.

When I could have gone for the highest:
P30 disk is 1024 GB, it can achieves 5000 IOPS and up to 200 MB/s. And only comes in 1TB size

The key thing* to understand is that the VM tier will limit the IOPS of what you connect to it.

And so here, while my disk is rated at 2300 IOPS and 150 MB/s, the machine specifications are going to limit me at 16,000 IOPS (no problem here) / but only 128 MB/s which is ok because P20 is only 150 MB/s anyway.

Once the VM is brought up online and the disk attached, a quick benchmark will match the specifications given:

iaas iops

 

 

135 MB/s for 150 MB/s advertised…

But wait, is this fast? My laptop gives me way more

laptop iops.png

How to attach faster disks to my VMs then?  What if I want to create a VHD with 600 GB and 400 MB/s of throughput?

You will not obtain such throughput if you just create a 600 GB VHD, because Azure will create a 600 GB VHD on a P30 Disk, and then you will have only 200 MB/s.

To achieve that, you should use stripping, and to do that, you can proceed with different ways:

  • Create 2 600 GB VHDs. Azure will create them using P30 disks. Then you use your stripping tool (Storage spaces) to create a 1200 GB volume. This volume will permit 400 MB/s and 10000 IOPS. But in this case, you will have 600 un-needed GB
  • Create 3 VHDs with 200 GB each. Azure will create them using P20 disks. Then you use your stripping tool (Storage spaces) to create a 600 GB volume. This volume will permit 450 MB/s (150 MB/s *3) and 6900 IOPS (2300 IOPS *3).

Wait, I need more! What if I want to create a VHD with 600 GB and 600 MB/s of throughput? Unfortunately, we can’t just dream, and ask Azure to do it, not till today. In fact, the maximum throughput possible is 512 MB/s, we can’t do better.

Ok. You have created the stripped volumes and you are still not getting what you want? Remember the key thing I mentioned above * ? The total data storage, the IOPS and the throughput are limited by the VM series and size. Each Azure Virtual Machine type is limited by a number of disks (total storage size), a maximum IOPS (IOPS) and a maximum throughput (Throughput). For example, you  may achieve a 128 MB/s only in a Standard_E8s_v3.  All the other VM types will throttle your IOPS or throughput when you reach the threshold.

When I was looking at the Memory optimized server size, there are Storage optimized VM sizes.

Size vCPU Memory: GiB Temp storage (SSD) GiB Max data disks Max cached and temp storage throughput: IOPS / MBps (cache size in GiB) Max uncached disk throughput: IOPS / MBps Max NICs / Expected network bandwidth (Mbps)
Standard_E8s_v3 8 64 128 16 16,000128 (200) 12,800 / 192 4 / 4,000
Standard_L8s 8 64 32 32 40,000 / 400 10,000 / 250 4 / 8,000

And so from the disk creation steps above, you will also want to get a VM that will be able to provide you with the throughput you need.

Advertisements

#azure, #database, #dtu, #iaas, #mssql, #paas, #sql

SCCM2012 (R2) new application creation fails

I had recently migrated my DBs from one volume to another volume due to space concerns, all was successful and life was good 🙂

However the other day, I wanted to create a new application and got the following “unknown” error.

The SMS Provider reported an error connecting to the ConfigMgr site database server. Verify that the SQL Server is online and that ConfigMgr site server computer account is an administrator on the ConfigMgr site database server.

SmsAdminUI.log would something unknown as well.

Description = “CSspConfigurationItem: SQL_ERROR”;
File = “e:\\qfe\\nts\\sms\\siteserver\\sdk_provider\\smsprov\\sspconfigurationitem.cpp”;
SQLMessage = “*** Unknown SQL Error!”;

Scratched my head a few times and started DDGing as the error was pretty self explanatory and found the following KB/Blog entry.

Basically, after such a DB files location move the SQL TRUSTWORTHY setting gets reset and the dbowner may change.

I hope on the MSSQL and executed the following queries to save the day – well only the creation of new packages.

ALTER DATABASE CM_CIE SET TRUSTWORTHY ON;
EXEC sp_changedbowner ‘sa’;

Then tried to create a new application and voilà…

#2012, #2012r2, #application, #creation, #error, #mssql, #package, #sccm, #system-center