Microsoft Azure : Benchmarking SQL Databases

8-4-2013-1-47-24-am1

Introduction
Azure also provides a PaaS-environment for SQL databases. In my effort to get familiar with them, I also ran a benchmark between all editions, which I wanted to share with you. So today we will run a benchmark versus each database edition to see how they will cope!

2015-02-05 11_22_39-Azure SQL Database Service Tiers and Performance Levels

Test/Benchmark Environment

Test System

  • Specs : Azure Basic A3 (4 cores, 7 GB memory)
  • Software : Database Benchmark + SQL Mgmt Studio
  • Network : Internal Virtual Network + Public Internet Connectivity

Test Databases

  • Basic Edition – “BASIC-B0” – 5 DTU
  • Standard Edition S0 – “STD-S0” – 10 DTU
  • Standard Edition S1 – “STD-S1” – 20 DTU
  • Standard Edition S2 – “STD-S2” – 50 DTU
  • Standard Edition S3 – “STD-S3” – 100 DTU
  • Premium Edition P1 – “PREMIUM-P1” – 100 DTU
  • Premium Edition P2 – “PREMIUM-P2” – 200 DTU
  • Premium Edition P3 – “PREMIUM-P3” – 800 DTU

How does that look from the SQL Management Studio?
2015-02-04 14_49_58-191.233.82.148_55325 - Remote Desktop Connection

Test Method

  • All the test were run sequentially
  • 5 tables of 10.000 records were used as test environment
  • The SQL2012 benchmark was used. (so not the compact one)

Results
And now on to the results…

Write
2015-02-04 23_19_44-191.233.84.122_55325 - Remote Desktop Connection
A difference is visible between all database editions, except the “premium”-edition looks to to be on par with eachother. At this time I cannot judge to the cause of this, where I’m assuming the test system or test method reached a limit (to be updated!).

Read
2015-02-04 23_20_12-191.233.84.122_55325 - Remote Desktop Connection
Apart from the “basic”-edition, all editions seem to be on par.

Secondary Read
2015-02-04 23_20_30-191.233.84.122_55325 - Remote Desktop Connection
After the cache has set in, even the “basic”-edition catches up.

And what can I do with it?
You can now run the benchmarks on your own systems and see how they compare… It will provide you with an insight that will render you capable of comparing performance beforehand in regards to a possible implementation.

Implementation Consideration
Once considering the Azure Databases, be ware that you are limited to SQL authentication. Many enterprises have integrated their SQL rights management in Active Directory, which is not possible with the current implementation of Azure.

2015-02-04 14_47_17-Azure SQL Database Security Guidelines and Limitations

Advertisements

Microsoft Azure : How to create a virtual machine with multiple network interfaces

When you are designing a network, the ability to have a secure gateway between subnets is vital. Therefor we’ll be looking into the possibility to create a virtual machine with multiple network interfaces.

What is possible?

Be aware that the size of your virtual machine will influence the amount of NICs you can create / configure ;

2015-02-02 09_52_25-Create a VM with Multiple NICs

It was also a surprise for me that it is (currently) not possible to add/remove NICs after the creation of the virtual machine. Trust me, I’ve tried and a nice warning tells me that this is not supported.

2015-02-02 09_52_36-Create a VM with Multiple NICsMore details can be found on the Azure documentation page “Create a VM with Multiple NICs“.

 Let’s try this one out!

 

Our Test Environment

We’ll have a virtual network with three subnet and we’ll create a machine that is linked to two of them.

kvaes-azure-multiple-nics-topology

Possible use cases?

  • Firewall
  • Load Balancer
  • Back-Up System
  • Management System

 

Step One ; Configure your Azure PowerShell to use your Azure Account

  • Open an Azure PowerShell.
  • To download your publishsettingsfile, enter:
    Get-AzurePublishSettingsFile
  • The download pop-up of your browser opens. Save the file.
  • Import the publishsettingsfile by entering:
    Import-AzurePublishSettingsFile PATH_TO_FILE

 

Step Two ; Setup the basics

  • Via the portal, create a virtual network “MULTIPLENICS” with three subnets ;
    2015-02-02 11_28_36-Networks - Windows Azure
  • Configure a storage account “multiplenicskvaes” ;2015-02-02 11_30_11-Storage - Windows Azure
  • Link the storage account to your subscription in Azure Powershell ;

    PS C:\> Get-AzureStorageAccount "multiplenicskvaes"2015-02-02 11_36_58-Microsoft Azure PowerShell
    PS C:\> Set-AzureSubscription -SubscriptionName "Gratis proefversie" -CurrentStorageAccount multiplenicskvaes
    PS C:\> Get-AzureSubscription

    2015-02-02 11_37_15-Microsoft Azure PowerShell

  • Get the image we want to use for our deployment. In this example, we’ll take the most recent Windows image ;

    PS C:\> $image = Get-AzureVMImage | where { $_.ImageFamily -Match "Windows*"} | sort PublishedDate -Descending | select -ExpandProperty ImageName -First 1

    2015-02-02 11_40_15-Microsoft Azure PowerShell

 

Step 3 ; Let’s create some VMs!

  • Create a basic configuration (plus account to login)
    PS C:\> $vm01 = New-AzureVMConfig -Name "KVAES01" -InstanceSize ExtraLarge -Image $image -AvailabilitySetName "KVAESNICSCLUSTER"
    PS C:\> Add-AzureProvisioningConfig -Windows -AdminUsername "demouser" -Password "Demo1234?" -VM $vm01

2015-02-02 11_53_15-Microsoft Azure PowerShell

  • Let’s add the machine to a given subnet

    PS C:\> Set-AzureSubnet -SubnetName "TIER000" -VM $vm01

    2015-02-02 11_53_24-Microsoft Azure PowerShell

  • Now let’s add an additional NIC from another subnet

    PS C:\> Add-AzureNetworkInterfaceConfig -Name "NIC1" -SubnetName "TIER001" -StaticVNetIPAddress "192.168.1.100" -VM $vm01

    2015-02-02 11_54_19-Microsoft Azure PowerShell

  • And finally, let’s press “create” 🙂

    PS C:\> New-AzureVM -ServiceName "KVAES01" -VM $vm01 -Location "West Europe" -VNetName "MULTIPLENICS"

    2015-02-02 11_58_36-Microsoft Azure PowerShell
    2015-02-02 12_00_50-Virtual machines - Windows Azure

Step 4 ; Verify that everything went according to plan!

  • Wait for the machine to leave the “Provisioning” state
    (good time to get some coffee/tea/… whatever)
  • Let’s note down the public ip & port which we’ll need to use for the “Remote Desktop Connection” .
    2015-02-02 12_12_06-Virtual machines - Windows Azure 2015-02-02 12_12_22-Virtual machines - Windows Azure
  • And let’s connect to it…
    2015-02-02 12_13_29-Remote Desktop Connection
  • Startup the console and type “ipconfig” to see what network interfaces are available
    2015-02-02 12_15_21-104.40.221.90_64205 - Remote Desktop Connection

Import things to remember

  • (Currently) you can only add/remove network interfaces upon creation.
  • Despite that the feature is not visible via the GUI, you are able to use multiple NICs in different subnets.
  • The limitation in regards to the number of NICs is related to the machine type.