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: 

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.


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

After a migration to Exchange Online Exchange Autodiscover SCP got in the way

There was once a customer who was on Exchange 2013 on-prem for their email needs, at some point they decided (thanks to Microsoft enticing pricing) to go with Office 365 and migrate all of their mailboxes and email needs to the cloud.

After the Exchange servers were put out of the equation, users and new outlook set up started seeing some uninteresting error/warning messages:

Troubleshooting Certificate Mismatch Warnings in Outlook ...

And so, while moving to Office 365, all of the DNS entries (internal and external) for autodiscover had been changed to but the old one: – in theory, the on-prem exchange server CAS – would still be around and generate the message. So where would this on-prem reference come from?

While on-prem, Exchange would have been configured to add entries to Active Directory and here in the name of a Service Connection Point (SCP) object and as the domain joined machine want to auto-configure Outlook, they would end up finding of that older reference.

Autodiscover functional process

The solution is to remove this from AD! There are multiple ways to prevent Outlook from contacting the local Exchange server first…

Using Exchange Management Shell (EMS)
The preferred way is to use the Exchange Management Shell to clear the entry for the Client Access server from the SCP.

[PS] C:\Windows\system32>Get-ClientAccessServer | fl *uri*
AutoDiscoverServiceInternalUri :
AutoDiscoverServiceInternalUri :
[PS] C:\Windows\system32>Set-ClientAccessServer -Identity cas-ex1 -AutoDiscoverServiceInternalUri $null
[PS] C:\Windows\system32>Set-ClientAccessServer -Identity cas-ex2 -AutoDiscoverServiceInternalUri $null

If the above method can no longer be used a low-level AD editor as EDSIEdit can be used to remove the SCP manually. The full path of the SCP is:

CN=ServerName,CN=Autodiscover,CN=Protocols,CN=ServerName,CN=Servers,CN=Exchange Administrative Group (FGH124FG788IYF),CN=Administrative Groups,CN=OrganizationName,CN=Microsoft Exchange,CN=Services,CN=Configuration,DC=DomainName,DC=Suffix


This object to remove has the Class type serviceConnectionPoint.

Some useful tools to help out finding out any autodiscover issues:
SARA (microsoft Support And Recovery Assistant):
Some reference from technet (yes I still call it technet):
More about SCPs:

#exchange, #office-365, #outlook

Copy windows features from a server to another

Sometimes you want to create the (almost) same server where you do not yet Chef or CF or some sort of DSC. The best resort is to use what you have: get-windowsfeature

Imagine you want to configure Server B from Server A and obviously those are microsoft windows servers…

#On server A
#export features
> Get-WindowsFeature | ? { $_.Installed -AND $_.SubFeatures.Count -eq 0 } | Export-Clixml .\serverA.xml
#copy the feature file over
> cp .\serverA.xml ‘\\serverB\c$\Files’

#On server B
PS C:\Files> ls
Directory: C:\Files
Mode LastWriteTime Length Name
—- ————- —— —-
-a—- 6/8/2017 4:15 PM 510824 ServerA.xml
PS C:\Files> Import-Module Servermanager
PS C:\Files> Import-Clixml .\ServerA.xml | Add-WindowsFeature
Success Restart Needed Exit Code Feature Result
——- ————– ——— ————–
True Yes SuccessRest… {Application Server, .NET Framework 4.5, W…
WARNING: You must restart this server to finish the installation process.

Voila, another posh timesaver.

#clone, #get-windowsfeature, #powershell, #server, #windows-2

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.

EXEC sp_changedbowner ‘sa’;

Then tried to create a new application and voilà…

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

Uninstall GP2010 and installation of GP2015

This document describes how to uninstall GP2010 and installation of GP2015.

Prerequisite: local admin rights to uninstall and install software on the machine

  1. Uninstall GP2010 following components
    1. GP2010, Mekorma MICR 2010, Integration Manager for Microsoft Dynamics GP 2010, Dexterity Shared Components 11.0 (64-bit)
    2. Remove the following folders
      1. C:\Program Files (x86)\Microsoft Dynamics\GP2010
      2. C:\Program Files (x86)\Common Files\microsoft shared\Dexterity
  2. Restart the computer
  3. Install GP2015 (includes dexterity 14) as usual.

Uninstall using WMIC

note that Mekorma not playing nice with wmic or msiexec – must uninstall manually.

wmic call Msiexec GUID
product where name=”Microsoft Dynamics GP 2010″ call uninstall /nointeractive {DC90A0A6-2D90-493E-8D13-D54AD123B9FD}
product where name=”Integration Manager for Microsoft Dynamics GP 2010″ call uninstall /nointeractive {FAFD8B80-E75F-4557-85F3-67B8D7A14E8F}
product where name=”Dexterity Shared Components 11.0 (64-bit)” call uninstall /nointeractive {F5459EB2-A662-4EB3-AD94-E771DC2F542A}
product where name=”Mekorma MICR 2010″ call uninstall /nointeractive {A45282DB-59DC-4A5D-9E1F-08A225D81A44}
To run on several nodes at the same time:
wmic:root\cli>/failfast:on /node:@”c:\temp\trainingwks.txt” product where name=”Microsoft Dynamics GP 2010″ call uninstall /nointeractive

#dynamics, #gp, #gp2010, #gp2015

Managing Certificates using Powershell

Because of my recent work with ADFS I was looking for a way to automate most of the certificate configuration by scripts. The usual run-books I write would usually include the use of the mmc and a bunch of screenshot to accompany them.

The answer is that powershell management for Certificates is there and here are some examples:


#Powershell exposes certs under cert:\
PS C:\> Get-PSDrive
Name Used (GB) Free (GB) Provider Root CurrentLocation
—- ——— ——— ——– —- —————
A FileSystem A:\
Alias Alias
C 14.37 45.29 FileSystem C:\
Cert Certificate \
D FileSystem D:\
Env Environment
Function Function
Variable Variable
PS C:\> cd cert:
PS Cert:\> dir localmachine
Name : TrustedPublisher
Name : ClientAuthIssuer
Name : Remote Desktop
Name : Root
Name : TrustedDevices
Name : CA
Name : AuthRoot
Name : TrustedPeople
Name : My
Name : SmartCardRoot
Name : Trust
Name : Disallowed
Name : AdfsTrustedDevices

#Browsing through the stores is pretty intuitive
PS Cert:\> dir Cert:\LocalMachine\My
Directory: Microsoft.PowerShell.Security\Certificate::LocalMachine\My
Thumbprint Subject
———- ——-
E31234DEF282437D167A64FD812342B650C20B42 CN=XXXXa
8912343319B07131C8FD1234E250DC67CBE08D7A CN=XXXX
69AD2C21912340919D186503631234A6F0BE9F7F CN=*,XXX..

#Exporting a cert is something a little less intuitive
PS Cert:\> $ExportCert = dir Cert:\LocalMachine\Root | where {$_.Thumbprint -eq “892F212349B07131C12347F8E250DC67CBE08D7
PS Cert:\> $ExportCryp = [System.Security.Cryptography.X509Certificates.X509ContentType]::pfx
PS Cert:\> $ExportKey = ‘pww$@’
PS Cert:\> $ExportPFX = $ExportCert.Export($ExportCryp, $ExportKey)
PS Cert:\> [system.IO.file]::WriteAllBytes(“D:\Temp\CertToExportPFXFile.PFX”, $ExportPFX)

#same mess for importing

  1. Define The Cert File To Import

$CertFileToImport = “D:\Temp\CertToImportPFXFile.PFX”

  1. Define The Password That Protects The Private Key

$PrivateKeyPassword = ‘Pa$$w0rd’

  1. Target The Cert That Needs To Be Imported

$CertToImport = New-Object System.Security.Cryptography.X509Certificates.X509Certificate2 $CertFileToImport,$PrivateKeyPassword

  1. Define The Scope And Certificate Store Within That Scope To Import The Certificate Into
  2. Available Cert Store Scopes are “LocalMachine” or “CurrentUser”

$CertStoreScope = “LocalMachine”

  1. For Available Cert Store Names See Figure 5 (Depends On Cert Store Scope)

$CertStoreName = “My”
$CertStore = New-Object System.Security.Cryptography.X509Certificates.X509Store $CertStoreName, $CertStoreScope

  1. Import The Targeted Certificate Into The Specified Cert Store Name Of The Specified Cert Store Scope


For import/export, I’d recommend using code from here:


#certificate, #certs, #manage, #pfx, #stores

ADFS Proxy Trust certificate on WAP doesn’t auto renew

Once upon a time, the web application proxy for ADFS proxy started throwing error.

The Remote Access Management console could not do much complaining with an error code “the operation stopped due to an unknown general error” as always really helpful message.

Looking at the logs, the WAP was also complaining about establishing its trust with the ADFS server.

Fairly enough the ADFS proxy was also complaining about the trust saying that the proxy trust certificate had expired.

Back to the WAP and surely enough it was. However from the GUI I could not find any way to recreate the trust and had to use my DuckDuckGo powers.

So I found that the wizard had to be tricked for reinitialization prior to doing anything as in


We need to set the ProxyConfigurationStatus REG_DWORD to a value of 1 (meaning “not configured”) instead of 2 (“configured”). Once that change is made, re-open the GUI. No reboot is required.

The Remote Access Manager should now allow you to re-run the configuration wizard.

I still don’t know why it would not renew, but given that the certification of the trust goes by every 2 weeks I will seen pretty soon.

#adfs, #certificate, #proxy, #wap