Using PowerShell to get Azure Endpoint Ports

A quick blog today. I was reading this blog post about How to read the SQL Error Log and I thought I would try some of the examples. I started my Azure VM using the steps in my previous post

I ran

and saw my box had started


and then


and bingo I had my SQL Port to put in SSMS and can go and play some more with SQL


Starting My Azure SQL Server VMs with PowerShell


The last post about Launching Azure VMs with PowerShell made someone ask me to explain how I start my Azure VMs normally so here goes.

When I decide to write a blog post or develop and test a script or run through demos from a presentation or blog post I fire up my Azure Virtual machines with PowerShell. This is how I do it

Open PowerShell and check that I am connected to my default subscription by running Get-AzureSubscription

Note – You must have installed Windows Azure PowerShell and installed the PublishSettingsFile or used Add-AzureAccount for your subscription following the steps here

Then I run the following three Cmdlets


Get-AzureVM shows me the VMs associated with that subscription.

I then pipe to Start-AzureVM as I want to start both machines. If I only wanted one I would check that


returned the correct machine and then pipe that to Start-AzureVM

Once the VMs have started I use Get-AzureRemoteDesktopFile giving a local path for the rdp file and specifying –launch to run the RDP session


and away we go 🙂

Once I have finished simply run


and my machines are stopped and no longer running my credit down.

Launching Azure VM After Starting With PowerShell

So this morning I decided I was going to run through this blog post on understanding query plans I logged into my Azure Portal to check my balance and clicked start on the machine and then immediately clicked connect.



Of course the RDP session wouldn’t open as the machine was not up so I went and made a coffee. Whilst doing that I thought of a way of doing it with PowerShell


A little Do Until loop on the PowerState Property 🙂


Of course if I was doing it all though PowerShell I would have done this


Add Adventure Works Database to Windows Azure VM

This has been an interesting journey. The Adventure Works database is frequently used in blogs and reference books and I wanted to install it in my Windows Azure Learning Lab and I also wanted to automate the process.

The easiest way is to download the Windows Azure MDF file from jump through all the security warnings in Internet Explorer and save the file and then create the database as follows

That is the way I will do it from now on! After reading this page I tried to download the file with Powershell but it would not as I could not provide a direct link to the file. Maybe someone can help me with that. So I thought I would use my SkyDrive to hold the MDF file and map a drive on the server.

\to do this you need to add the Desktop Experience feature to the server. This can be done as follows

This will take a few minutes to install, reboot and then configure the updates before you can log back in. While it is doing this log into your SkyDrive and navigate to a folder and copy the URL to notepad

It will look something like this

Copy the GUID after the cid=

and write this command

I keep this in a script and pass the user and password in via Read-Host

However, if you try to copy the item from the folder you will get an error

The file size exceeds the limit allowed and cannot be saved

So you will need to alter a registry key as follows

Set-ItemProperty -Path HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\WebClient\Parameters -Name FileSizeLimitInBytes -Value 4294967295

and then restart the WebClient service Then run the net use command to map the drive and copy the file with Copy-Item

But my script to auto install the Adventure Works database via Powershell once you have completed all the pre-requisites is

To be honest I don’t think I will use this method as my copy failed twice before it succeeded so I will just download the file and create the database!!

Please don’t ever trust anything you read on the internet and certainly don’t implement it on production servers without first both understanding what it will do and testing it thoroughly. This solution worked for me in my environment I hope it is of use to you in yours but I know nothing about your environment and you know little about mine

Spinning up and shutting down Windows Azure Lab with Powershell

So at SQL Bits I went to Chris Testa-O’Neill’s session on certification. This has inspired me to start working on passing the MCSE exams. My PC at home doesn’t have much grunt and my tablet wont run SQL. I considered some new hardware but I knew I would have a hard time getting authorisation from the Home Financial Director (Mrs F2B) despite my all the amazing justification and benefits I could provide!!

So I looked at Windows Azure as a means of having some servers to play with. After watching this video and then this video I took the plunge and dived in.

After setting up my account I read a few blogs about Powershell and Windows Azure.

Note – Here I only spin up extra small instances and don’t configure SQL as per Microsoft’s recommendations. I am only using these VMs for learning and talking at my user group your needs may be different

First you’ll need Microsoft Web Platform Installer. Then download and install Windows Azure PowerShell,

This gives you all the Windows Azure Powershell Cmdlets.

Get-AzurePublishSettingsFile which will give you a download for a file.  PowerShell will use this to control your Windows Azure so although you need it now, keep it safe and probably out of your usual directories so it doesn’t get compromised.

Import-AzurePublishSettingsFile and the file path to import it into Powershell.

Get-AzureSubscription to see the results and note the subscription name.

Now we create a storage account

Get-AzureLocation will show you the available locations if you want a different one.I then set the storage account to be default for my subscription

I spent a couple of days sorting out the following scripts. They set up three SQL Servers, configure them to allow SSMS, Powershell and RDP connections and also remove them all. The reasoning behind this is that you will be charged for servers even when they are turned off

First we set some variables

To choose an image run Get-AzureVMImage|select name and pick the one for you. I chose a size of extra small as it is cheaper. As I won’t be pushing the servers very hard I don’t need any extra grunt. Set up a service the first time and use the location switch but then to use the same service again remove the location switch otherwise you will get an error stating DNS name already in use which is a little confusing until you know.

This creates a VM object and adds two endpoints for the server, one for Powershell and one for SSMS. When you provision more than one server you will need to make sure you use a different Public Port for each server otherwise you will get an error. You will need to note which server has which port when you need to connect with SSMS.

Once you have your VM object just pass it to New-AzureVM as shown


Providing you have no errors you can then just wait until you see this.


It will take a few minutes. Long enough to get a cuppa. Even then you won’t be able to connect straightaway as Azure will be provisioning the server still.

The next bit of the script downloads the RDP shortcut to a folder on the desktop and assigns a variable for the clean up script. I use this because the next time you spin up a server it may not use exactly the same port for RDP.

The Invoke-Expression will open up a RDP connection but unless you have gone to get a cuppa I would check in your management portal before trying to connect as the server may still be provisioning. In fact,I would go to your Windows Azure Management Portal and check your virtual machine tab where you will see your VMs being provisioned

Now you have three servers but to be able to connect to them from your desktop and practice managing them you still need to do a bit of work. RDP to each server run the following script in Powershell.

I use netsh advfirewall as I find it easy and I understand it. I know you can do it with Set-NetFirewallProfile but that’s the beauty of Powershell you can still use all your old cmd knowledge as well. This will allow you to remote manage the  servers. You can do it from your laptop with the creation of some more endpoints but I just use one server as a management server for my learning.

The last part of the script changes SQL to Mixed authentication mode and creates a SQL user with sysadmin and restarts the SQL service on each server and that’s it. Its ready to go.

Open up SSMS on your desktop and connect to “YourServiceName.Cloudapp.Net, PortNumber (57500-5702 in this example)

To remove all of the implementation run the code that is commented out in steps. First it assigns a variable to each VHD, then it removes the VM. You should then wait a while before removing the VHDs as it takes a few minutes to remove the lease and finally remove the RDP shortcuts as next time they will be different.


Please don’t ever trust anything you read on the internet and certainly don’t implement it on production servers without first both understanding what it will do and testing it thoroughly. This solution worked for me in my environment I hope it is of use to you in yours but I know nothing about your environment and you know little about mine