Tag Archives: Windows Azure

Uploading a Source Folder to Azure File Storage

Posted on by

This blog has been migrated to blog.robsewell.com

Azure File Storage enables you to present an Azure Storage Account to your IaaS VMs as a share using SMB. You can fid out further details here

http://azure.microsoft.com/en-gb/documentation/articles/storage-dotnet-how-to-use-files/ 

Once you have created your Azure File Storage Account and connected your Azure Virtual Machines to it, you may need to upload data from your premises into the storage to enable it to be accessed by the Virtual Machines

To accomplish this I wrote a function and called it Upload-ToAzureFileStorage

I started by creating a source folder and files to test

New-Item -Path C:\temp\TestUpload\New1 -ItemType Directory
New-Item -Path C:\temp\TestUpload\New2 -ItemType Directory
New-Item -Path C:\temp\TestUpload\New3 -ItemType Directory
New-Item -Path C:\temp\TestUpload\New4 -ItemType Directory
New-Item -Path C:\temp\TestUpload\New5 -ItemType Directory
New-Item -Path C:\temp\TestUpload\New1\list -ItemType Directory
New-Item -Path C:\temp\TestUpload\New1\list\a -ItemType Directory
New-Item -Path C:\temp\TestUpload\New1\list\b -ItemType Directory
New-Item -Path C:\temp\TestUpload\New1\list\c -ItemType Directory
New-Item -Path C:\temp\TestUpload\New1\list\d -ItemType Directory
New-Item -Path C:\temp\TestUpload\New1\list\a\1 -ItemType Directory
New-Item -Path C:\temp\TestUpload\New1\list\a\2 -ItemType Directory
New-Item -Path C:\temp\TestUpload\New1\list\a\3 -ItemType Directory
New-Item -Path C:\temp\TestUpload\New1\list\a\4 -ItemType Directory

New-Item -Path C:\temp\TestUpload\New1\file.txt -ItemType File
New-Item -Path C:\temp\TestUpload\New2\file.txt -ItemType File
New-Item -Path C:\temp\TestUpload\New3\file.txt -ItemType File
New-Item -Path C:\temp\TestUpload\New4\file.txt -ItemType File
New-Item -Path C:\temp\TestUpload\New5\file.txt -ItemType File
New-Item -Path C:\temp\TestUpload\New1\list\file.txt -ItemType File
New-Item -Path C:\temp\TestUpload\New1\list\a\file.txt -ItemType File
New-Item -Path C:\temp\TestUpload\New1\list\a\1\file.txt -ItemType File
New-Item -Path C:\temp\TestUpload\New1\list\a\2\file.txt -ItemType File
New-Item -Path C:\temp\TestUpload\New1\list\a\3\file.txt -ItemType File
New-Item -Path C:\temp\TestUpload\New1\list\a\4\file.txt -ItemType File

Then we needed to connect to the subscription, get the storage account access key and create a context to store them

#Select Azure Subscription
Select-AzureSubscription -SubscriptionName $AzureSubscriptionName

# Get the Storage Account Key
$StorageAccountKey = (Get-AzureStorageKey -StorageAccountName $StorageAccountName).Primary

# create a context for account and key
$ctx=New-AzureStorageContext $StorageAccountName $StorageAccountKey

The Get-AzureStorageShare  cmdlet shows the shares available for the context so we can check if the share exists

$S = Get-AzureStorageShare -Context $ctx -ErrorAction SilentlyContinue|Where-Object {$_.Name -eq $AzureShare}

and if it doesnt exist create it using New-AzureStorageShare 

$s = New-AzureStorageShare $AzureShare -Context $ctx

For the sake only of doing it a different way we can check for existence of the directory in Azure File Storage that we are going to upload the files to like this

$d = Get-AzureStorageFile -Share $s -ErrorAction SilentlyContinue|select Name

if ($d.Name -notcontains $AzureDirectory)

and if it doesnt exist create it using New-AzureStorageDirectory

$d = New-AzureStorageDirectory -Share $s -Path $AzureDirectory

Now that we have the directory created in the storage account we need to create any subfolders. First get the folders

# get all the folders in the source directory
$Folders = Get-ChildItem -Path $Source -Directory -Recurse

We can then iterate through them using a foreach loop. If we do this and select the FullName property the results will be

C:\temp\TestUpload\New1
C:\temp\TestUpload\New2
C:\temp\TestUpload\New3
C:\temp\TestUpload\New4
C:\temp\TestUpload\New5
C:\temp\TestUpload\New1\list
C:\temp\TestUpload\New1\list\a
C:\temp\TestUpload\New1\list\b
C:\temp\TestUpload\New1\list\c
C:\temp\TestUpload\New1\list\d
C:\temp\TestUpload\New1\list\a\1
C:\temp\TestUpload\New1\list\a\2
C:\temp\TestUpload\New1\list\a\3
C:\temp\TestUpload\New1\list\a\4

but to create new folders we need to remove the “C:\temp\TestUpload” and replace it with the Directory name in Azure. I chose to do this as follows using the substring method and the length of the source folder path.

foreach($Folder in $Folders)
 {
 $f = ($Folder.FullName).Substring(($source.Length))
 $Path = $AzureDirectory + $f

and tested that the results came out as I wanted

AppName\New1
AppName\New2
AppName\New3
AppName\New4
AppName\New5
AppName\New1\list
AppName\New1\list\a
AppName\New1\list\b
AppName\New1\list\c
AppName\New1\list\d
AppName\New1\list\a\1
AppName\New1\list\a\2
AppName\New1\list\a\3
AppName\New1\list\a\4

I could then create the new folders in azure using New-AzureStorageDirectory again

New-AzureStorageDirectory -Share $s -Path $Path -ErrorAction SilentlyContinue

I followed the same process with the files

$files = Get-ChildItem -Path $Source -Recurse -File</pre>
<pre>foreach($File in $Files)
 {
 $f = ($file.FullName).Substring(($Source.Length))
 $Path = $AzureDirectory + $f

and then created the files using Set-AzureStorageFileContent this has a -Force and a -Confirm switch and I added those into my function by using a [switch] Parameter

#upload the files to the storage

 if($Confirm)
 {
 Set-AzureStorageFileContent -Share $s -Source $File.FullName -Path $Path -Confirm
 }
 else
 {
 Set-AzureStorageFileContent -Share $s -Source $File.FullName -Path $Path -Force
 }

You can download the function from the Script Center

https://gallery.technet.microsoft.com/scriptcenter/Recursively-upload-a-bfb615fe

As also, any comments or queries are welcome and obviously the internet lies so please understand and test all code you find before using it in production

#TSQL2sDay Why My Head is Always in The Cloud

Posted on by

Todays post is my first for the TSQL2sDay series. For those not familiar this is rotating blog party that was started by Adam Machanic (@AdamMachanic | blog) back in 2009. If you want to catch up on all the fun to date? Check out this nice archive (link) put together by Steve Jones (@way0utwest |blog). Thank you Steve!!!

Azure Ballon - Credit http://owenrichardson.com/

This one is hosted by Jorge Segarra @SQLChicken:  who said This month’s topic is all about the cloud. What’s your take on it? Have you used it? If so, let’s hear your experiences. Haven’t used it? Let’s hear why or why not? Do you like/dislike recent changes made to cloud services? It’s clear skies for writing! So let’s hear it folks, where do you stand with the cloud?

My wife would tell you that my head is always in the cloud and she’s right (she usually is) just not like that picture! I would love to float gracefully above the land and gaze upon the view but its the landing that bothers me and will always stop me from trying it

Credit http://owenrichardson.com/

She’s right, pedantically and literally too, because this year I have spent a lot of time with my head and my fingers and my thinking in Virtual Machines using Windows Azure. That is where I have learnt a lot of my SQL and Powershell this year. After SQL Saturday Exeter and SQL Bits in Nottingham this year I have needed a place to practice and learn, an environment to try things and break things and mend them again and experiment.

I learn just as well by doing things as I do reading about them. Stuart Moore  @napalmgram has a great post called Learning to Play with SQL Server and whist I haven’t been as rough with my Azure SQL instances as he suggests I have been able to practice at will without worry and thanks to my MSDN subscription without cost. I have taken examples from blog posts and demos from User Group Sessions and run them on my Windows Azure VMs

Every single blog post I have written this year that has examples has been written in Azure and screen shots from Azure. Whilst some of my Powershell scripts in the PowerShell Box of Tricks series had already been written to solve one particular problem or another at MyWork, every single one was refined and demo’d and all the screen shots were from Azure and several were developed on Azure too

My first ever session to the SQL South West user group was about Spinning up and Shutting Down VMS in Azure was about Azure and was an interesting experience in Murphys Law which meant I ended up having to deliver it  on Azure.

The second time I have talked was about the PowerShell Box of Tricks series to the Cardiff User Group. Having learnt my lesson from the first time I had bought a mini HDMI to VGA converter and I had tested it using a couple of monitors at home and it worked wonderfully. However, when I got to Cardiff my little Asus convertible didn’t provide enough grunt to power the funky presentation screen. Luckily thanks to Stuart Moore @napalmgram who was also there doing his excellent PowerShell Back Up and Restore Session who let me use his Mac I was able to deliver the session using Office Web App to run the PowerPoint from my SkyDrive whilst all the demos were on ………Yup you guessed it Windows Azure !!!

So I feel qualified to answer Jorge’s questions and take part in T-SQL Tuesday this time round.

I like Azure. I like the ease I can spin up and down machines or any PaaS services at will. I love that I can do it with PowerShell because I really enjoy using PowerShell in my day to day work and at home too. Living as I do in a beautifully convenient bungalow in the country, I still enjoy the frustration of watching that spinning ring as my videos buffer on our 1.8Mbs at best internet connection. Whilst that does have an impact on using Azure it is a damn sight better than waiting many days trying to download one single file. Something like an ISO file for the latest SQL Server CTP for example.

There is no way I would have got a look at SQL Server 2014 if it wasn’t for Azure. I was able to spin up a SQL Server 2014 machine in only a few minutes and log in and have a play and then delete it. I have done the same with Server 2012 and 2012 R2. It has enabled me to try setting up Availability Groups and other technologies not yet implemented at MyWork

I wouldn’t have been able to do any of that on my machines at home as I don’t have anything capable of running Hyper-V whilst this 8 year old desktop still keeps hanging on despite the odd noises. (Negotiations are currently in place to replace it with something shiny and new. Just need that lottery win now !!)

I have also transferred my Cricket Averages database to WASD and am talking with a friend of mine about developing an app that will use the mobile service as well.

The rate of change is much quicker in the cloud, things change and change quickly. As quickly as I had written my post about Spinning up and Shutting Down VMS in Azure Microsoft changed the rules and didn’t charge for machines that were turned off. New services appear all the time. New services move quickly through from Preview to release and as Grant Fritchey noticed this week new views have been added to to Windows Azure SQL Database under the covers. I think this is something we are just going to have to live with. The scale of the cloud means it is much easier to test improvements at large scale and that means they can be released quicker.  It makes it more challenging to keep up I admit but it’s a constant drip of new things rather than a big bang all at once.

Azure has brought me to where I am today and I think it will continue to be part of my future. If I remember to submit my PowerShell session for SQL Saturday Exeter (Submit yours here) and it gets chosen then you will be able to see me there (if you register here) using Azure to give back to the SQL Community

Launching Azure VM After Starting With PowerShell

Posted on by

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

D’oh

image

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

image

A little Do Until loop on the PowerState Property 🙂

image

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

image

SQL Server 2014 CTP on Azure. Powershell won’t create XTP tables

Posted on by

Having a couple of hours to play I created a SQL 2014 box on Azure with Windows Server 2012 R1 and followed This Post on SQLServerCentral  to create a File Group and table with T-SQL. Everything went well. Don’t try and provision an extra small box with that image though as it is so slow as to be unusable!!

I then decided to take a look at doing it with Powershell and I cannot create the table. I think it is to do with my Index creation. Can anyone see my mistake as I am going round in circles now. I have posted to DBAStackExchange so answer there

    -- Create new database
CREATE DATABASE TestDatabase
GO
--Add MEMORY_OPTIMIZED_DATA filegroup to the database.
ALTER DATABASE TestDatabase
ADD FILEGROUP XTPFileGroup CONTAINS MEMORY_OPTIMIZED_DATA
-- Add a new file to the previous created file group
ALTER DATABASE TestDatabase ADD FILE
(
NAME = N'HekatonFile1',
FILENAME = N'C:\Program Files\Microsoft SQL
Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\HekatonFile1')
TO FILEGROUP [HekatonFileGroup]
GO
-- Let's create a new Memory Optimized Table
CREATE TABLE TestTable
(
 Col1 INT NOT NULL,
Col2 VARCHAR(100) NOT NULL,
 Col3 VARCHAR(100) NOT NULL
CONSTRAINT chk_PrimaryKey PRIMARY KEY NONCLUSTERED HASH (Col1) WITH (BUCKET_COUNT = 1024)
) WITH (MEMORY_OPTIMIZED = ON)
GO

And Here is my Powershell

# To Load SQL Server Management Objects into PowerShell
[System.Reflection.Assembly]::LoadWithPartialName(‘Microsoft.SqlServer.SMO’)  | out-null
[System.Reflection.Assembly]::LoadWithPartialName(‘Microsoft.SqlServer.SMOExtended’)  | out-null
$server= new-Object Microsoft.SqlServer.Management.Smo.Server("(local)")
$dbname = "HekatonTest"
$db = New-Object Microsoft.SqlServer.Management.Smo.Database($server, $dbname)
# Add FileGroups
$FG1Name = "PRIMARY"
$Normalfg = new-object ('Microsoft.SqlServer.Management.Smo.FileGroup') ($db, $FG1Name)
$db.FileGroups.Add($Normalfg)
$FG2Name = "MemOpt"
$MemOptFG = new-object ('Microsoft.SqlServer.Management.Smo.FileGroup') ($db, $FG2Name)
$MemOptFG.FileGroupType = "MemoryOptimizedDataFileGroup"
$db.FileGroups.Add($MemOptFG)
#Create datafiles
$normallogname = "HekatonTest_Data"
$dbdfnormal = new-object ('Microsoft.SqlServer.Management.Smo.DataFile') ($Normalfg, $normallogname)
$dbnormalfile= $server.Information.MasterDBPath + '\' + $normallogname + '.mdf'
$normalfg.Files.Add($dbdfnormal)
$dbdfnormal.FileName = $dbnormalfile
$dbdfnormal.Size = [double](5.0 * 1024.0)
$dbdfnormal.GrowthType = 'Percent'
$dbdfnormal.Growth = 25.0
$dbdfnormal.IsPrimaryFile = 'True'
$MemOptFilename = "MemOpt_Data"
$MemOptDataFile = new-object ('Microsoft.SqlServer.Management.Smo.DataFile') ($MemOptFG, $MemOptFilename)
$MemOptDataFilePath= $server.Information.MasterDBPath + '\' + $MemOptFilename + '.ndf'
$MemOptFG.Files.Add($MemOptDataFile)
$MemOptDataFile.FileName = $MemOptDataFilePath

#Create Database
$db.Create() 
#Create Table
$dbname = "HekatonTest"
$db = $server.databases[$dbname]
$tb = new-object Microsoft.SqlServer.Management.Smo.Table($db, "MemOptTable")
#Add Columns
$col1 = new-object Microsoft.SqlServer.Management.Smo.Column($tb,"Col1", [Microsoft.SqlServer.Management.Smo.DataType]::Int)
$col2 = new-object Microsoft.SqlServer.Management.Smo.Column($tb, "Col2", [Microsoft.SqlServer.Management.Smo.DataType]::varchar(100))
$col3 = new-object Microsoft.SqlServer.Management.Smo.Column($tb, "Col3", [Microsoft.SqlServer.Management.Smo.DataType]::varchar(100))
$tb.Columns.Add($col1)
$tb.Columns.Add($col2)
$tb.Columns.Add($col3)
$C1Name =$col1.Name
#Create Index
$IX = New-Object Microsoft.SqlServer.Management.Smo.Index ($Tb, "PK_PrimaryKeyName")   
$IX_col = New-Object Microsoft.SqlServer.Management.Smo.IndexedColumn ($IX,$C1Name)
$IX.IndexedColumns.Add($IX_col)
$IX.IndexKeyType = "DriPrimaryKey"
$IX.IndexType = "HashIndex"
$IX.BucketCount = "1024"
$IX.FileGroup = "PRIMARY"
$IX.IsMemoryOptimized = $true
$Tb.Indexes.Add($IX)
$tb.FileGroup = "MemOpt"
$tb.Durability = "SchemaAndData"
$tb.IsMemoryOptimized = $true
$tb.Create()

I can create the filegroups and the database with PS and use SSMS to create Memory Optimised Tables (XTP) and if I take out the index creation then the table creates but isnt XTP obviously.

All you get is the frustrating Create() failed error which doesn’t help me to find the problem.

I know I can take the T-SQL and put it into Invoke-SQLCMD but I figured you ought to be able to do it with Powershell.

Can anyone point me in the right direction? For interest only, maybe it’s not available in the CTP. Answer over here

Add Adventure Works Database to Windows Azure VM

Posted on by

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  http://msftdbprodsamples.codeplex.com/ jump through all the security warnings in Internet Explorer and save the file and then create the database as follows

CREATE DATABASE AdventureWorks2012
ON (FILENAME = 'PATH TO \AdventureWorks2012_Data.mdf')
FOR ATTACH_REBUILD_LOG ;

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

Import-Module ServerManager
Add-WindowsFeature Desktop-Experience -restart

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

https://skydrive.live.com/?lc=2137#cid=XXXXXXXXXXXXXXXX&id=CYYYYYYYYYYYYYYYY

Copy the GUID after the cid=

and write this command

net use T:  \\d.docs.live.net@SSL\XXXXXXXXXXXXXXXX /user:$user $password

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

Lessons Learnt from my first talk at SQL SouthWest

Posted on by

The timing was good enough that I could offer to do a talk based on my previous post on Windows Azure for my SQL User Group SQL SouthWest when Jonathan and Annette.( @FatherJack and @MrsFatherJack) put out a call for volunteers.

I did my best with the 7 P’s. I ran through it at lunchtime, I made sure I had power and a HDMI lead after checking with Jonathan, I got a glass of water. I knew the first line I was going to say

However, I neglected to check that I would have HDMI in at the location so everything that was on my laptop was useless! My laptop did very odd things to the USB stick when I tried to transfer to Jonathans laptop and he didn’t have Powershell V3 installed so whilst Neil Hambly @Neil_Hambly from Confio was speaking I was busy ignoring a very interesting talk on Waits to install and configure Powershell Azure on my Azure VM. Sorry Neil.

But in the end it more or less worked and we are lucky to have such a patient and supportive user group who helped me along the way as well. Thank you folks

Things I took away from the evening

  1. Double check you have all the connections
  2. Practice and Practice some more
  3. Think about the times when something is running and what you will say when there is nothing to see
  4. Presenting completely inside a RDP session adds unnecessary complication
  5. The Demo Gods WILL hit you and the curse of the red text will fall upon you during the presentation. Accept it and move on.
  6. Have an opening line
  7. Remember to breath (especially when the demo falls over)
  8. Enjoy it!

It didn’t go perfectly but people gave me some good feedback and I am pleased to say that I have pointed people towards something new that will help them and passed over my knowledge and that to me is what the SQL Community is all about. I have a load of other ideas for things I can talk about and blog about so it is going to be a very busy time for me as I work my way through them and do all the other exciting things coming my way in the SQL world.

Visit your own User Group – You can find them here http://www.sqlpass.org/

If you are in the South West UK then come and join our group. Free training and conversation with like minded people once a month and pizza too what could be better!!

Spinning up and shutting down Windows Azure Lab with Powershell

Posted on by

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.

http://adminian.com/2013/04/16/how-to-setup-windows-azure-powershell/

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,

Import-Module c:\Program Files\Microsoft SDKs\Windows Azure\PowerShell\Azure\Azure.psd1"

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

New-AzureStorageAccount -StorageAccountName chooseaname -label 'a label' -Description 'The Storage Account for the Lab Spin Up and Down' -Location 'West Europe'

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

Set-AzureSubscription -SubscriptionName 'Subscription Name from Earlier' -CurrentStorageAccount 'theoneyouchose'

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

$image = 'fb83b3509582419d99629ce476bcb5c8__Microsoft-SQL-Server-2012SP1-Standard-CY13SU04-SQL11-SP1-CU3-11.0.3350.0-B'
$SQL1 = 'SQL1'
$SQL2 = 'SQL2' 
$SQL3 = 'SQL3' 
$size = 'ExtraSmall' 
$AdminUser = 'ChoosePCAdminName' 
$password = 'SUPERCOMpl1c@teDPassword'
$Service = 'theservicenameyouchoose' 
$Location = 'West Europe'

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.

$vm = New-AzureVMConfig -Name $SQL1 -InstanceSize $size -ImageName $image | 
Add-AzureProvisioningConfig -AdminUsername $AdminUser -Password $password -Windows | 
Add-AzureEndpoint -Name "SQL" -Protocol "tcp" -PublicPort 57502 -LocalPort 1433| 
Add-AzureEndpoint -Name PS-HTTPS -Protocol TCP -LocalPort 5986 -PublicPort 5986

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

New-AzureVM -ServiceName $Service -VMs $vm

 

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

image_thumb

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.

$SQL1RDP = "$ENV:userprofile\Desktop\Azure\RDP\$SQL1.rdp" 
Get-AzureRemoteDesktopFile -ServiceName $Service -name $SQL1 -LocalPath $SQL1RDP 
Invoke-Expression $SQL1RDP

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.

# Configure PowerShell Execution Policy to Run all Scripts – It’s a one time Progress 
Set-ExecutionPolicy –ExecutionPolicy Unrestricted 
netsh advfirewall firewall add rule name=SQL-SSMS dir=in action=allow enable=yes profile=any 
netsh advfirewall firewall add rule name=SQL-SSMS dir=out action=allow program=any enable=yes profile=any 
netsh advfirewall firewall set rule group="Remote Administration" new enable=yes 
netsh advfirewall firewall set rule group="File and Printer Sharing" new enable=yes 
netsh advfirewall firewall set rule group="Remote Service Management" new enable=yes 
netsh advfirewall firewall set rule group="Performance Logs and Alerts" new enable=yes 
netsh advfirewall firewall set rule group="Remote Event Log Management" new enable=yes 
netsh advfirewall firewall set rule group="Remote Scheduled Tasks Management" new enable=yes 
netsh advfirewall firewall set rule group="Remote Volume Management" new enable=yes 
netsh advfirewall firewall set rule group="Remote Desktop" new enable=yes 
netsh advfirewall firewall set rule group="Windows Firewall Remote Management" new enable =yes 
netsh advfirewall firewall set rule group="windows management instrumentation (wmi)" new enable =yes

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.

<# 
.NOTES 
    Name: CreateLab.ps1 
    Author: Rob Sewell http://sqldbawithabeard.com
    Requires: Get the Windows Azures CmdLets then run this
    Version History: 
                    Added New Header 23 August 2014
    
.SYNOPSIS 
	This script will create 3 Windows Azure SQL Servers and open up RDP connections
	ready for use. There is also the scripts to remove the Windows Azure Objects to save on
	usage costs
    
.DESCRIPTION 
    
.PARAMETER 
    
.PARAMETER 

.PARAMETER 

.EXAMPLE 
#> 


# Get the Subscription File and Import it
Get-AzurePublishSettingsFile 

Import-AzurePublishSettingsFile FilepathtoPublishSettingsFile

<# Run this once to set up a Storage Account
New-AzureStorageAccount -StorageAccountName storageaccountname -location 'West Europe' -Label 'Storage Account for My Lab'
#>

Get-AzureSubscription #Note the name

#Set the storage account to the subscription
Set-AzureSubscription -SubscriptionName SubscriptionName -CurrentStorageAccount storageaccountname


#Some variables

# Use Get-AzureVMimage to find the one you want ie Get-AzureVMImage | where { ($_.ImageName -like "*SQL*") }|select ImageName 

$image = 'fb83b3509582419d99629ce476bcb5c8__Microsoft-SQL-Server-2012SP1-Standard-CY13SU04-SQL11-SP1-CU3-11.0.3350.0-B'
$SQL1 = 'SQL1'
$SQL2 = 'SQL2'
$SQL3 = 'SQL3'
$size = 'ExtraSmall'
$AdminUser = 'ChoosePCAdminName'
$password = 'SUPERCOMpl1c@teDPassword'
$Service = 'theservicenameyouchoose'
$Location = 'West Europe'

<# Run this the first time to create a Service

New-AzureService -ServiceName $Service  -Location $Location -Label 'SQLDBA with a Beard Service' 

#>

#Configure the VMs

$vm = New-AzureVMConfig -Name $SQL1 -InstanceSize $size -ImageName $image |
  Add-AzureProvisioningConfig -AdminUsername $AdminUser -Password $password -Windows|
  Add-AzureEndpoint -Name "SQL" -Protocol "tcp" -PublicPort 57500 -LocalPort 1433 


$vm2 = New-AzureVMConfig -Name $SQL2 -InstanceSize $size -ImageName $image |
  Add-AzureProvisioningConfig -AdminUsername $AdminUser -Password $password -Windows |
    Add-AzureEndpoint -Name "SQL" -Protocol "tcp" -PublicPort 57501 -LocalPort 1433


$vm3 = New-AzureVMConfig -Name $SQL3 -InstanceSize $size -ImageName $image |
  Add-AzureProvisioningConfig -AdminUsername $AdminUser -Password $password -Windows |
    Add-AzureEndpoint -Name "SQL" -Protocol "tcp" -PublicPort 57502 -LocalPort 1433| 
    Add-AzureEndpoint -Name PS-HTTPS -Protocol TCP -LocalPort 5986 -PublicPort 5986

#Provision the VMs

New-AzureVM -ServiceName $Service -VMs $vm, $vm2,$vm3 

# Get the RDP Files

$SQL1RDP = "$ENV:userprofile\Desktop\Azure\RDP\$SQL1.rdp"
$SQL2RDP = "$ENV:userprofile\Desktop\Azure\RDP\$SQL2.rdp"
$SQL3RDP = "$ENV:userprofile\Desktop\Azure\RDP\$SQL3.rdp"

Get-AzureRemoteDesktopFile -ServiceName $Service -name $SQL1 -LocalPath $SQL1RDP
Get-AzureRemoteDesktopFile -ServiceName $Service -name $SQL2 -LocalPath $SQL2RDP 
Get-AzureRemoteDesktopFile -ServiceName $Service -name $SQL3 -LocalPath $SQL3RDP

# Open the RDP Fies - Check the machine is up in your Management Portal

Invoke-Expression $SQL1RDP
Invoke-Expression $SQL2RDP
Invoke-Expression $SQL3RDP

# Now run the SetupVM script for each server

<# 

This is the clean up script to remove the servers and services

Run this first

 $SQL1Disk = Get-AzureDisk|where {$_.attachedto.rolename -eq $SQL1}
 $SQL2Disk = Get-AzureDisk|where {$_.attachedto.rolename -eq $SQL2}
 $SQL3Disk = Get-AzureDisk|where {$_.attachedto.rolename -eq $SQL3}

#Then This

    Remove-AzureVM -Name $SQL1 -ServiceName $Service
    Remove-AzureVM -Name $SQL2 -ServiceName $Service
    Remove-AzureVM -Name $SQL3 -ServiceName $Service

Then wait a while and run this

 $SQL1Disk|Remove-AzureDisk -DeleteVHD
 $SQL2Disk|Remove-AzureDisk -DeleteVHD
 $SQL3Disk|Remove-AzureDisk -DeleteVHD

  #Remove-AzureService $Service

  Get-ChildItem "$ENV:userprofile\Desktop\Azure\RDP\*.rdp"|Remove-Item

  #>

  <# 

  This is the clean up script for variables

    
    Remove-Variable [a..z]* -Scope Global 
    Remove-Variable [1..9]* -Scope Global 

   #>

<# 
.NOTES 
    Name: SetUpVMSQL1.ps1 
    Author: Rob Sewell http://sqldbawithabeard.com
    Requires: 
    Version History: 
                    Added New Header 23 August 2014
    
.SYNOPSIS 
 	This script will set up the SQL1 VM ready for use and enable SQL Authentication
	Add a user called SQLAdmin with a password of P@ssw0rd
	Restart SQL Service.Run on SQL1
.DESCRIPTION 
    
.PARAMETER 
    
.PARAMETER 

.PARAMETER 

.EXAMPLE 
#> 

    # Configure PowerShell Execution Policy to Run all Scripts � It�s a one time Progress
    Set-ExecutionPolicy �ExecutionPolicy Unrestricted

netsh advfirewall firewall add rule name=SQL-SSMS dir=in action=allow enable=yes profile=any
netsh advfirewall firewall add rule name=SQL-SSMS dir=out action=allow program=any enable=yes profile=any
netsh advfirewall firewall set rule group="Remote Administration" new enable=yes
netsh advfirewall firewall set rule group="File and Printer Sharing" new enable=yes
netsh advfirewall firewall set rule group="Remote Service Management" new enable=yes
netsh advfirewall firewall set rule group="Performance Logs and Alerts" new enable=yes
Netsh advfirewall firewall set rule group="Remote Event Log Management" new enable=yes
Netsh advfirewall firewall set rule group="Remote Scheduled Tasks Management" new enable=yes
netsh advfirewall firewall set rule group="Remote Volume Management" new enable=yes
netsh advfirewall firewall set rule group="Remote Desktop" new enable=yes
netsh advfirewall firewall set rule group="Windows Firewall Remote Management" new enable =yes
netsh advfirewall firewall set rule group="windows management instrumentation (wmi)" new enable =yes

    # To Load SQL Server Management Objects into PowerShell
    [System.Reflection.Assembly]::LoadWithPartialName(�Microsoft.SqlServer.SMO�)  | out-null
    [System.Reflection.Assembly]::LoadWithPartialName(�Microsoft.SqlServer.SMOExtended�)  | out-null
    [System.Reflection.Assembly]::LoadWithPartialName(�Microsoft.SqlServer.SqlWmiManagement�) | out-null

SQLPS

$Name = 'SQL1'

Invoke-Sqlcmd -ServerInstance $Name -Database master -Query "USE [master]
GO
EXEC xp_instance_regwrite N'HKEY_LOCAL_MACHINE', N'Software\Microsoft\MSSQLServer\MSSQLServer', N'LoginMode', REG_DWORD, 2
GO
"


Invoke-Sqlcmd -ServerInstance $Name  -Database master -Query "USE [master]
GO
CREATE LOGIN [SQLAdmin] WITH PASSWORD=N'P@ssw0rd', DEFAULT_DATABASE=[master]
GO
ALTER SERVER ROLE [sysadmin] ADD MEMBER [SQLAdmin]
GO

"
get-Service -ComputerName $Name  -Name MSSQLSERVER|Restart-Service -force





<# 
.NOTES 
    Name: SetUpVMSQL2.ps1 
    Author: Rob Sewell http://sqldbawithabeard.com
    Requires: 
    Version History: 
                    Added New Header 23 August 2014
    
.SYNOPSIS 
    
.DESCRIPTION 
    
.PARAMETER 
    
.PARAMETER 

.PARAMETER 

.EXAMPLE 
#> 
      #############################################################################################
#
# NAME: SetupVMSQL2.ps1
# AUTHOR: Rob Sewell http://newsqldbawiththebeard.wordpress.com
# DATE:10/05/2013
#
#
# COMMENTS: This script will set up the SQL1 VM ready for use and enable SQL Authentication
# Add a user called SQLAdmin with a password of P@ssw0rd
# Restart SQL Service
# ------------------------------------------------------------------------

   
   # Run on SQL2

    # Configure PowerShell Execution Policy to Run all Scripts � It�s a one time Progress
    Set-ExecutionPolicy �ExecutionPolicy Unrestricted

netsh advfirewall firewall add rule name=SQL-SSMS dir=in action=allow enable=yes profile=any
netsh advfirewall firewall add rule name=SQL-SSMS dir=out action=allow program=any enable=yes profile=any
netsh advfirewall firewall set rule group="Remote Administration" new enable=yes
netsh advfirewall firewall set rule group="File and Printer Sharing" new enable=yes
netsh advfirewall firewall set rule group="Remote Service Management" new enable=yes
netsh advfirewall firewall set rule group="Performance Logs and Alerts" new enable=yes
Netsh advfirewall firewall set rule group="Remote Event Log Management" new enable=yes
Netsh advfirewall firewall set rule group="Remote Scheduled Tasks Management" new enable=yes
netsh advfirewall firewall set rule group="Remote Volume Management" new enable=yes
netsh advfirewall firewall set rule group="Remote Desktop" new enable=yes
netsh advfirewall firewall set rule group="Windows Firewall Remote Management" new enable =yes
netsh advfirewall firewall set rule group="windows management instrumentation (wmi)" new enable =yes

    # To Load SQL Server Management Objects into PowerShell
    [System.Reflection.Assembly]::LoadWithPartialName(�Microsoft.SqlServer.SMO�)  | out-null
    [System.Reflection.Assembly]::LoadWithPartialName(�Microsoft.SqlServer.SMOExtended�)  | out-null
    [System.Reflection.Assembly]::LoadWithPartialName(�Microsoft.SqlServer.SqlWmiManagement�) | out-null

SQLPS

$Name = 'SQL2'

Invoke-Sqlcmd -ServerInstance $Name -Database master -Query "USE [master]
GO
EXEC xp_instance_regwrite N'HKEY_LOCAL_MACHINE', N'Software\Microsoft\MSSQLServer\MSSQLServer', N'LoginMode', REG_DWORD, 2
GO
"


Invoke-Sqlcmd -ServerInstance $Name  -Database master -Query "USE [master]
GO
CREATE LOGIN [SQLAdmin] WITH PASSWORD=N'P@ssw0rd', DEFAULT_DATABASE=[master]
GO
ALTER SERVER ROLE [sysadmin] ADD MEMBER [SQLAdmin]
GO

"
get-Service -ComputerName $Name  -Name MSSQLSERVER|Restart-Service -force





<# 
.NOTES 
    Name: SetUpVMSQL3.ps1 
    Author: Rob Sewell http://sqldbawithabeard.com
    Requires: 
    Version History: 
                    Added New Header 23 August 2014
    
.SYNOPSIS 
    
.DESCRIPTION 
    
.PARAMETER 
    
.PARAMETER 

.PARAMETER 

.EXAMPLE 
#> 
      #############################################################################################
#
# NAME: SetupVMSQL3.ps1
# AUTHOR: Rob Sewell http://newsqldbawiththebeard.wordpress.com
# DATE:10/05/2013
#
#
# COMMENTS: This script will set up the SQL3 VM ready for use and enable SQL Authentication
# Add a user called SQLAdmin with a password of P@ssw0rd
# and enable PS Remoting
# Restart SQL Service
# ------------------------------------------------------------------------

   
   # Run on SQL3

    # Configure PowerShell Execution Policy to Run all Scripts � It�s a one time Progress
    Set-ExecutionPolicy �ExecutionPolicy Unrestricted



netsh advfirewall firewall add rule name=SQL-SSMS dir=in action=allow enable=yes profile=any
netsh advfirewall firewall add rule name=SQL-SSMS dir=out action=allow program=any enable=yes profile=any
netsh advfirewall firewall set rule group="Remote Administration" new enable=yes
netsh advfirewall firewall set rule group="File and Printer Sharing" new enable=yes
netsh advfirewall firewall set rule group="Remote Service Management" new enable=yes
netsh advfirewall firewall set rule group="Performance Logs and Alerts" new enable=yes
Netsh advfirewall firewall set rule group="Remote Event Log Management" new enable=yes
Netsh advfirewall firewall set rule group="Remote Scheduled Tasks Management" new enable=yes
netsh advfirewall firewall set rule group="Remote Volume Management" new enable=yes
netsh advfirewall firewall set rule group="Remote Desktop" new enable=yes
netsh advfirewall firewall set rule group="Windows Firewall Remote Management" new enable =yes
netsh advfirewall firewall set rule group="windows management instrumentation (wmi)" new enable =yes

#Extra one for PS Remoting
netsh advfirewall firewall add rule name="Port 5986" dir=in action=allow protocol=TCP localport=5986

    # To Load SQL Server Management Objects into PowerShell
    [System.Reflection.Assembly]::LoadWithPartialName(�Microsoft.SqlServer.SMO�)  | out-null
    [System.Reflection.Assembly]::LoadWithPartialName(�Microsoft.SqlServer.SMOExtended�)  | out-null
    [System.Reflection.Assembly]::LoadWithPartialName(�Microsoft.SqlServer.SqlWmiManagement�) | out-null

SQLPS

$Name = 'SQL3'

Invoke-Sqlcmd -ServerInstance $Name -Database master -Query "USE [master]
GO
EXEC xp_instance_regwrite N'HKEY_LOCAL_MACHINE', N'Software\Microsoft\MSSQLServer\MSSQLServer', N'LoginMode', REG_DWORD, 2
GO
"


Invoke-Sqlcmd -ServerInstance $Name  -Database master -Query "USE [master]
GO
CREATE LOGIN [SQLAdmin] WITH PASSWORD=N'P@ssw0rd', DEFAULT_DATABASE=[master]
GO
ALTER SERVER ROLE [sysadmin] ADD MEMBER [SQLAdmin]
GO

"
get-Service -ComputerName $Name  -Name MSSQLSERVER|Restart-Service -force
Enable-PSRemoting -force

 

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