Azure Storage for SQL Server

imageTo deploy a SQL Server instance on an Azure Virtual Machine, you will require the use of the Azure Storage service for your storage requirements. There are a few nuances of the Azure storage which requires prior knowledge so that your SQL Server instance runs without any glitches.

Before I start babbling about IOPs, data disks and regions, let’s first get a quick glimpse at what Azure storage offers. Azure storage offers blobs, tables and queues. The data disks that your database files would use are stored as blobs in Azure. One or more data disks are grouped into a single Azure Storage container. It is recommended that you store of all your data disks for a particular virtual machine in a single container. Each storage account has a limit of 500TB!


Here is the Cheat Sheet that we had shared during our session at the Microsoft India Teched 2014:

A maximum of 20,000 IOPs per storage account – Each storage account has a limit of 20k IOPs. It is recommended that your mission or business critical or virtual machines with high storage throughput requirements have their data disks residing in a single storage account. Additionally, this storage account should not be shared with another virtual machine or service. For the Standard tier, do not place more than 40 highly used VHDs in a storage account

• A maximum of 500 IOPs for each data disk – Each persistent data disk supports a maximum of 500 IOPs for the Standard Tier. It is advisable to

64-KB allocation unit size for data and log files as well as tempdb

• Use Storage Spaces on Windows Server 2012 and above – This allows you to group your data disks and leverage throughput greater than 500IOPs for the storage pool. For Windows 2008 R2 or earlier, you can use dynamic disks (OS striped volumes) and the stripe size is always 64 KB.

Do not store data on the temporary drive unless for tempdb and buffer pool extension on SSD drives (D-series VMs)

Separate data disks for data and log files – It is very important to determine your storage throughput requirements. As a best practice, having separate storage pools or data disks for your data and log files could go a long way in ensuring that you maintain optimal performance.

Caching policy = NONE – This needs to be set for all the data disks being used by the SQL Server instance

Backup to BLOB storage – When performing backups for SQL Server running in Azure virtual machines, you can use SQL Server Backup to URL. This feature is available starting with SQL Server SP1 CU2 and recommended for backing up to the attached data disks. Prior to SQL Server 2012, you can use SQL Server Backup to Azure Tool. This tool can help to increase backup throughput using multiple backup stripe targets.

Disable GEO-replication on storage account – It is advisable to use a locally redundant storage account for your SQL Server instance. The other benefit that you would get from a locally redundant storage is that the ingress and outgress limits (data moving in and out of the storage account) are higher.

In the next part of this blog post, I am going to use PowerShell magic to determine if the SQL Server instance that you have configured is following some of the best practices mentioned above.

Checking for geo-replication

The following PowerShell script used the Get-AzureStorageAccount cmdlet to get details of the storage account which hosts the virtual disks for the virtual machine. The script and the output is available below.

$StorageAccount = Get-AzureStorageAccount -StorageAccountName "<storage account name>"
if ($StorageAccount.GeoReplicationEnabled -eq $false)
    Write-Host "PASSED: Geo-replication is DISABLED" -ForegroundColor Green
   Write-Host "ISSUE: Geo-replication is ENABLED!" -ForegroundColor Green


Checking the caching policy

The PowerShell script below determines if the caching policy is enabled for the data disks using the Get-AzureVM cmdlet. This cmdlet fetches information about the virtual machine which is used to get the list of the data disks using the Get-AzureDataDisk cmdlet.

$DataDisks = Get-AzureVM -ServiceName "<service name>" -Name "<VM name>" | Get-AzureDataDisk
foreach ($disk in $DataDisks)
    if ($disk.HostCaching -eq "None")
        Write-Host "PASSED: Disk caching is DISABLED for data disk: " $disk.DiskName "(" $disk.MediaLink ")" -ForegroundColor Green
        Write-Host "ISSUE: Disk caching is ENABLED for data disk " $disk.DiskName "(" $disk.MediaLink ")" -ForegroundColor Red


I will get a few more PowerShell scripts uploaded for automating the best practices checks for Azure VMs running SQL Server workloads.


Azure Storage

Azure Subscription and Service Limits, Quotas, and Constraints

* This blog post has been written based on the service details available on 9th November, 2014.



I had really enjoyed presenting at an earlier TechEd and this time I have the privilege of presenting again.

TechEd India is Microsoft’s premier technology conference for Developers, Technology Enthusiasts and IT Professionals providing opportunities to learn, connect and explore. Join us to increase your technical expertise through deep hands-on learning, sharing of best practices and interactions with Microsoft and industry experts on November 5 & 6 at Bangalore. This year our agenda is bigger than ever! There are a number of special sessions including Microsoft Women in Tech and an exclusive CIO roundtable. We also bring you an extensive Tech Expo that will showcase the latest solutions from Microsoft and Partners.

If you are interested in Azure (Cloud), SQL Server, Data Center trends and all the things that keep today, then this is definitely the event for you. Find out more about the complete agenda from the TechEd site.

This year I will be speaking with Sourabh Agarwal [B | T] on “SQL Performance: Demystified in Azure VMs“. The session abstract is as follows:

This session will give an overview of how to harness the power of Microsoft Azure Virtual Machines for SQL Server database. A few tips and tricks is all it requires to keep a SQL Server database healthy on an Azure Virtual Machine. What’s more, we will even slip in few tricks to automate the entire deployment with the performance best practices enabled.

What can you expect from this session?

1. We will talk about how managing performance for a SQL Server instance deployed on an Azure Virtual Machine can be slightly different from maintaining an on-premise environment

2. We will demonstrate how to ensure that your Azure virtual machine and your SQL Server instance are configured optimally for performance using concepts that you use regularly for managing on-premise SQL Server instances

3. And we will also show some PowerShell magic for accomplishing common tasks related to performance

There will be other known speakers like Vinod Kumar [B | T], Balmukund Lakhani [B | T] and Arvind Shyamsundar [B | T] who will not only help you gain knowledge but also have amazing demos in their presentations!

If you haven’t already registered for TechEd yet, then you can do so by visiting the TechEd site. The Facebook event for my session is available here.

Subscribe now to stay updated with what is happening at TechEd India 2014. I look forward to meeting you at the event!

The slide deck of our presentation is embedded below.

Lessons learnt while using the Cloud Adapter

During the last week of August, I had blogged about how to get your on-premise database to your SQL Server instance running on an Azure virtual machine. I had run into a few issues while trying to run the wizard provided by Management Studio.

The First Stumble

This error is easy to circumvent and pretty much mentioned in the online documentation. The error message would read as:

Failed to locate a SQL Server of version 12.0.2000 or later installed on the remote machine. Please verify that a SQL Server of the same or higher version than the source SQL Server is installed on the remote machine.

The above error is self-explanatory. There is a requirement that the source database engine version be lower or equal to the version of the SQL Server instance running on Azure. Eg. You cannot deploy a database from a SQL Server 2014 instance to a SQL Server 2012 instance running on an Azure VM.

The Second Stumble

The second common error that you might run into is:

The Cloud Adapter port configuration is not valid. Verify the virtual machine endpoint configurations.

The above error will be encountered when the endpoint is not configured for the Azure virtual to accept connections from the outer realm! This can be easily rectified by adding a TCP endpoint to your Azure virtual machine for 11435 which is the port that the SQL Server Cloud Adapter Service is listening on. This is also mentioned in the online documentation. Once you have created the endpoint for your Azure virtual for your on-premise server to connect with the Cloud Adapter service, your endpoint configuration should look like the one in the screenshot below:


The Third Stumble

The next issue could be with permissions/authentication or it might not be as easy as it seems.

Cloud Adapter operation failed due to invalid authentication. Verify the virtual machine name, user name, and password.

So the first thing to check if you have the correct account name and password. If it is due to an authentication error, then the application event log of the Azure Virtual Machine will show the following error with the source as SQL Server Cloud Adapter service as shown in Screenshot 2. The text of the error message is mentioned below.

Access denied for user <user name>


The other error that you might encounter is when the SQL Server Cloud Adapter service tries to enumerate the database engines installed on the virtual machine. The error would still be talking about the authentication which is reported by the management studio wizard but a little investigation into the application event logs of the virtual machine will show the following error:

[Error] <ip address> Exception in GetSqlInstances(): SQL Server WMI provider is not available on <machine name>.. Stack trace:    at Microsoft.SqlServer.Management.Smo.Wmi.ManagedComputer.TryConnect()
   at Microsoft.SqlServer.Management.Smo.Wmi.WmiSmoObject.get_Proxy()
at Microsoft.SqlServer.Management.Smo.Wmi.WmiSmoObject.EnumChildren(String childTypeName, WmiCollectionBase coll)
at Microsoft.SqlServer.Management.Smo.Wmi.ServerInstanceCollection.InitializeChildCollection()
at Microsoft.SqlServer.Management.CloudAdapter.Tasks.GetSqlInstances()
at Microsoft.SqlServer.Management.CloudAdapter.CloudAdapter.GetSqlInstances(String username, String password). Inner Exception: Invalid namespace .

The above error clearly states that the GetSqlInstances() method failed. Microsoft.SqlServer.Management.Smo.Wmi namespace contains classes that provide programmatic access to the Windows Management Instrumentation (WMI) from an SMO application. I had talked about needing the shared management objects in an earlier post. The SQL Server 2014 WMI provider is also required which is available by installing the client connectivity components from any SQL Server 2014 setup including SQL Server Express. The components that I had installed were:

a. Client Tools Connectivity

b. Client Tools Backwards Compatibility

If you are not sure if you have the WMI provider, then look for the file “C:\Program Files (x86)\Microsoft SQL Server\120\Shared\sqlmgmproviderxpsp2up.mof“. This is the SQL Server 2014 MOF file. Another way to test if the WMI provider is working without running the wizard every time and have it fail is to run the PowerShell commands below on your Azure Virtual Machine. This script will tell you where the instance enumeration being performed by the deployment wizard will work or fail.

$m = New-Object ('Microsoft.SqlServer.Management.Smo.Wmi.ManagedComputer') '.'
foreach ($svi in $m.ServerInstances)

This post was intended to document that common issues that you might run into while deploying a database from an on-premise SQL Server instance to a SQL Server instance running on an Azure Virtual Machine.