Recently I’ve done several HA Cluster deployments of SQL Server into Azure, leveraging ALWAYS ON. When doing so, SQL Server Management Studio comes in handy..

While it installed with the SQL Server VM Image out of the gallery, sometimes you’d need on another machine (e.g. to test a cluster behind an Azure ILB).

So what’s the quickest way to install SSMS (SQL Server Management Studio) into your Virtual Machine? Of course CHOCOLATEY comes into our minds (again).. And INDEED, SQL Server Management Studio Express 2014 is already available through Chocolatey repository.. https://chocolatey.org/packages/MsSqlServerManagementStudio2014Express

So how to automatically run it? Through Azure VM Custom Script Extension.

The Azure Blog describes here how to install Chocolatey using the Portal. I’ll take it further and go through Powershell / Azure CLI..

1. Create a .ps1 file with the following commands:

   iex ((new-object net.webclient).DownloadString('https://chocolatey.org/install.ps1')) 
   choco install mssqlservermanagementstudio2014express

2. Upload the powershell script to a public Webserver or Azure Storage account

$storageAccount = Get-AzureRmStorageAccount `
    -ResourceGroupName <resourceGroupName> `
    -Name <storageAccountName>
$key = Get-AzureRmStorageAccountKey `
    -ResourceGroupName <resourceGroupName> `
    -Name <storageAccountName>
 
// Optional: Create an empty new container (or not, whatever you like :-))
New-AzureStorageContainer -Name scripts -Context $storageAccount.Context
 
// Upload the .ps1 file
Set-AzureStorageBlobContent -File ".\chocossme.ps1" `
    -Container scripts -BlobType Block `
    -Context $storageAccount.Context
 

3. Attach the custom script to your already running VM… (assuming you’re using Azure Resource Manager VMs)

Set-AzureRmVMCustomScriptExtension –Name ChocoSSMEInstall `
    -ResourceGroupName <resourceGroupName> `
    -VMName <vmName> `
    -ContainerName scripts `
    -StorageAccountName <storageAccountName> `
    -StorageAccountKey $key.Key1 `
    -FileName 'chocossme.ps1' `
    -Run 'chocossme.ps1'

4. Wait for the deployment to complete

Get-AzureRmResourceGroupDeployment –ResourceGroupName <resourceGroupName>

5. Once you’re done, log into the VM and enjoy!