Microsoft Azure : Benchmarking SQL Databases

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!

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?

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

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

Apart from the “basic”-edition, all editions seem to be on par.

Secondary Read

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.

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 ;

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.

More 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.

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 ;
  
• Configure a storage account “multiplenicskvaes” ;
• Link the storage account to your subscription in Azure Powershell ;

  PS C:\> Get-AzureStorageAccount "multiplenicskvaes"
PS C:\> Set-AzureSubscription -SubscriptionName "Gratis proefversie" -CurrentStorageAccount multiplenicskvaes
PS C:\> Get-AzureSubscription

  

• 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

  

 

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

• Let’s add the machine to a given subnet

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

  

• 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

  

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

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

  
  

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” .
  
• And let’s connect to it…
  
• Startup the console and type “ipconfig” to see what network interfaces are available
  

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.