Azure Storage and SQL Server – Part 4

In Part 3 of this series, I had talked about how to determine the IOPS supported by the data disks attached to the virtual machine and determine if all the data disks belong to the same storage account. In this post, I shall talk about how to determine if your compute and storage resources are located in the same region. It would not make sense to have your compute and storage resources in different data centers especially for high performance workloads. This would be equivalent of having geographically dispersed storage which would not really give you good throughput. It is recommended that you create your Azure storage account in the same data center as your SQL Server virtual machines to reduce transfer delays.

Colocation of service and storage account

Again, we are going to use Azure cmdlets to make our work easier. The first script finds out the location of your Azure service and the Azure storage account and checks to see if they are in the same location. If not, it flags an issue. The Get-AzureService cmdlet provides the location of the Azure service and the Get-AzureStorageAccount gives the location of the storage account.

The Powershell script and a screenshot of the output is shown below.


$AzureServiceName = "<Service Name>"

$AzureStorageName = "<Storage Account Name>"

$ComputeLocation = Get-AzureService -ServiceName $AzureServiceName | Select-Object Location

$StorageLocation = Get-AzureStorageAccount -StorageAccountName $AzureStorageName | Select-Object Location

if ($ComputeLocation.Location -ne $StorageLocation.Location)

{

Write-Host "ISSUE: Service and storage are NOT co-located. Storage location:" $StorageLocation.Location "Service location:" $ComputeLocation.Location -ForegroundColor Red

}

else

{

Write-Host "PASSED: Service and storage are co-located in" $StorageLocation.Location -ForegroundColor Green

}

Finding out the temporary drive

It is possible that someone might re-map the temporary drive to another drive letter other than D:. In such scenarios, it is important to find out the temporary drive letter. It would do well to not assume that the temporary drive is not the D: drive. I had blogged earlier on how to re-map the temporary drive.

Once you have logged into the Azure virtual machine, you would need to jump a few PowerShell and WMI hoops to get the temporary drive letter:

1. Find out the IDE drive which has a SCSI Target ID of 1. The C: drive would also be an IDE drive but with a SCSI Target ID = 0. This information is available from the Win32_DiskDrive class.

2. Now that you have the partition name, it is time to get the disk # and partition # using the Win32_DiskDriveToDiskPartition class.

3. And the last hoop that you need to jump is to use the disk # and partition # to get the logical drive name using the Win32_LogicalDiskToDiskPartition class.

This information will be used in future automation posts to determine if the temporary drive is being used by SQL Server or not. The temporary drive can be used for Buffer Pool Extensions and the tempdb files only on the D-Series virtual machines.

The Powershell script and screenshot of the output is shown below.


# Find out the IDE drives on the machine which has a SCSI target = 1

$Name = Get-WmiObject -Class Win32_DiskDrive -Filter "InterfaceType = `"IDE`" and SCSITargetId = 1" | Select-Object Name

# Find the parition id corresponding to the IDE temporary drive

$Antecedent = "*" + $Name.Name.Replace("\","").Replace(".","") + "*"

$Dependent = Get-WmiObject -Class Win32_DiskDriveToDiskPartition | Where-Object {$_.Antecedent -like $Antecedent} | Select-Object Dependent

# Find the logical disk which corresponds to the temporary drive partition

$Antecedent = "*" + $Dependent.Dependent.Split("`"")[1] + "*"

$TempDrive = (Get-WmiObject -Class Win32_LogicalDiskToPartition | Where-Object {$_.Antecedent -like $Antecedent} | Select-Object Dependent).Dependent.Split("`"")[1]

Write-Host "INFO: Temporary drive on the machine is:" $TempDrive -Foregroundcolor Red