0 Comments

There are a lot of benefits with managed disks and it is the preferred way to create a new VM. However because of my tight budget, I wanted to move to unmanaged. I did not expect the costs to continue when the VM was stopped. It was because of the managed storage disks(s).

I found this SO answer of Jason Ye - MSFT.

$sas = Grant-AzureRmDiskAccess -ResourceGroupName "[ResourceGroupName]" -DiskName "[ManagedDiskName]" -DurationInSecond 3600 -Access Read  
$destContext = New-AzureStorageContext –StorageAccountName "[StorageAccountName]" -StorageAccountKey "[StorageAccountAccessKey]"
$blobcopy=Start-AzureStorageBlobCopy -AbsoluteUri $sas.AccessSAS -DestContainer "[ContainerName]" -DestContext $destContext -DestBlob "[NameOfVhdFileToBeCreated].vhd"

I had to create containers in the storage account but I had copied both the OS and the Data disk to the blob storage as .vhd file.

I used this powershell script and the template to create the vhd from blob storage. The datadisk can be added later in the web gui.

  1. Login-AzureRMAccount
  2. Get-AzureRmSubscription
  3. Set-AzureRmContext -SubscriptionName "my subscription name here"
  4. $sas = Grant-AzureRmDiskAccess -ResourceGroupName "resourcegroup" -DiskName "manageddiskname" -DurationInSecond 45000 -Access Read 
    $destContext = New-AzureStorageContext –StorageAccountName "storageaccount" -StorageAccountKey "myprivatekey"
    $blobcopy=Start-AzureStorageBlobCopy -AbsoluteUri $sas.AccessSAS -DestContainer "vhd-containers" -DestContext $destContext -DestBlob "givetheunmanageddiskaname.vhd"
  5. Get-AzureStorageBlobCopyState -Container "vhd-containers" -Blob "givetheunmanageddiskaname.vhd" -Context $destContext –WaitForComplete

My mistake was to use the 3600 value for ‘DurationInSecond’ which is just an hour (60 sec, 60 minutes). The 512 gb datadisk could not be copied to blob storage within an hour (or two). Found out that an hour was also insufficient when I found ‘Get-AzureStorageBlobCopyState’.

Because I already had a vnet from my vm with managed disks, I used this template to create a new vm with the os disk from blob storage: https://github.com/Azure/azure-quickstart-templates/tree/master/201-vm-specialized-vhd-existing-vnet

If you do not have a vnet yet, you should use https://github.com/Azure/azure-quickstart-templates/tree/master/101-vm-from-user-image The deploy to azure button is a useful tool!

image

Once you have a new vm with an unmanaged disk up and running, close it to add the data disk. Once you have done that and have a remote desktop connection, go to disk management and bring the datadisk online again. It took me some time to get my head around the ASM and ARM differences in the powershell tooling. Also because there is now Azure Cli and a cross plat powershell 6.0

The cloud is moving fast, so hop on. Don’t miss out!


Good luck!

Pin on pinterest Plus on Googleplus Post on LinkedIn
0 Comments

The documentation for Azure SQL db performance tuning is great and accurate with great detail on docs.microsoft.com (instead of the old msdn location)

https://docs.microsoft.com/en-us/azure/sql-database/sql-database-query-performance

I was looking at a SQL db in Azure and noticed some spikes. Here is my graph:

snip_20170215133957

I could drill into the Azure portal to see what was causing this spike and recognized the SQL statement, so I knew which product to update the SQL for. However this was not even necessary.

Here are the client stats when I copy pasted the query causing the spikes in SQL Management Studio Express. Check out the “Total execution time”

snip_20170215120138

My next step was to take a look at the execution plan of the query. There is this exclamation mark at the sort operation:

snip_20170215120257

When you hover it, you get this context popup:

snip_20170215124310

So it used tempdb. I still had no clue how to fix this, so I reached out to stack exchange. And a user named T.H. gave me the solution to create two rather simple indexes:

CREATE NONCLUSTERED INDEX TEST ON STOCKDEBUGTRIGGERED (ChangeDate)

CREATE NONCLUSTERED INDEX TEST ON STOCKDEBUG(ProductID, StockOld, StockNew)

Here is the query plan after the two new indexes:

snip_20170215124852

No more yellow exclamation mark! and fewer steps. The client statistics also prove that this is a lot faster/better:

snip_20170215124929

From an average of 4250 down to 50!

 

This is also backed by the dramatic drop in resource usage in the Azure Portal:

snip_20170215133957

So the lesson is: do not trust the Azure db perf advisor a 100% Smile

T.H. commented on Stack Exchange:

The automatic index advice is extremely limited, and often misleading, so can only be considered as a starting point.

 

Hope this info might help someone troubleshooting Azure Db perf!

 

Good luck!

Pin on pinterest Plus on Googleplus Post on LinkedIn