Using PowerShell to deploy Windows Azure Virtual Machines and Windows Azure SQL Databases

Article
03/21/2013

Author: Benjamin Wright-Jones (Microsoft)
Contributors: Karthika Raman (Microsoft)
Technical Reviewers: Guy Bowerman (Microsoft), Sanjay Nagamangalam (Microsoft)

I have recently been exploring the use IaaS (Infrastructure as a Service) to provide cloud-based virtual machines (VM’s) as opposed to laptop-based VM’s and also PaaS (Platform as a Service) for SQL databases. I like the idea of carrying around a lighter more portable laptop and using cloud services to help me day to day, in contrast to carrying a heavy weight workstation for Hyper-v usage.

I know we can deploy VM’s through the Azure Portal but I prefer an automated approach. Fortunately the new PowerShell cmdlets support Azure VM provisioning and also Azure SQL database provisioning (plus some other nice interfaces). This enables me to quickly spin up a SQL Server VM in Azure or SQL database in Azure. I am actually quite amazed what is possible with PowerShell and it is my new best friend. PowerShell ISE in Windows 8 is superb, I highly recommend this as a development environment due to the intellisense and cmdlets search pane integration.

Below is the PowerShell script I wrote to provision a VM. Unfortunately some manual steps are still required if you wish to manage the SQL Server instance remotely through Management Studio e.g. opening firewall ports, enabling TCP etc. This is all documented here https://www.windowsazure.com/en-us/manage/windows/common-tasks/sql-server-on-a-vm/. Fortunately it is a lot easier to connect to an Azure SQL database and we can automate the registration of the provisioned instance in Management Studio.

Azure VM Provisioning (SQL Server 2012 Evaluation Edition)

Step 1. Download and register the Azure publishing certificate (one time only event).

In order to use PowerShell with the Azure VM and SQL Database services you will need to download and import the publishing file. Fortunately, this is a simple process. I also store my certificate on Skydrive so I can access it everywhere I go in case I need it again.

 Get-AzurePublishSettingsFile
Import-AzurePublishSettingsFile C:\...

If you don’t import the publishing file then you may see an error similar to below when attempting to access the Azure services.

 An error occurred while making the HTTP request to https://management.core.
windows.net/ae81ecb1-a8af-4fb7-87c5-4418babb4ff2/services/sqlservers/servers
/<server>?op=ResetPassword. This could be due to the fact that the server 
certificate is not configured properly with HTTP.SYS in the HTTPS case. This 
could also be caused by a mismatch of the security binding between the client 
and the server.

Step 2. View available subscriptions and set the correct Subscription

 Get-AzureSubscription | Select SubscriptionName
Select-AzureSubscription –SubscriptionName

Incidentally, you may also need to associate a specific Azure Storage Vault (ASV) Account with your subscription, for some reason the default value was null so I had to allocate a specific account. The Azure Storage account is required to host the VM disks which are provisioned during the creation of an Azure VM image.

 Get-AzureStorageAccount | Select Label
Set-AzureSubscription -SubscriptionName "Windows Azure MSDN - Visual Studio Ultimate"

 -CurrentStorageAccount "<storageaccount>"

Step 3. View the available Azure VM images and locations

 Get-AzureVMImage | Select ImageName
Get-AzureLocation | Select DisplayName

Step 4. Create an Azure VM

I like my VM’s big! ExtraLarge! You can use the New-AzureQuickVM syntax, New-AzureVM or New-AzureVMConfig syntax, The New-AzureQuickVM automatically creates and provisions the VM which does not require any additional steps.

 New-AzureQuickVM -Windows -ServiceName "<azureservice>" -Name "<vmname>" 
-ImageName "b83b3509582419d99629ce476bcb5c8__Microsoft-SQL-Server-2012-
Evaluation-CY13Feb-SQL11-SP1-CU2-11.0.3339.0" –Password <password> -Location "North Europe" 
–InstanceSize “ExtraLarge”

You should see something like this below.

If you see a DNS error then you may be trying to provision a under a duplicate service name e.g. Error "DNS name already exists" is misleading as it refers to a duplicate service name so change this in the parameters. This servicename refers to the VM service by which you reference/connect e.g. <vmservice>.cloudapp.net

Step 6. Start the Azure VM

Start the Azure VM using the command below.

 Start-AzureVM -ServiceName "<servicename>" -Name "<vmname>"

You can view the properties of your Azure VM’s by using Get-AzureVM –ServiceName <ServiceName>. I would also add that additional data disks can be simply added using the Add-AzureDataDisk syntax providing the ability to simply increase the capacity of the provisioned Azure VM instance.

Step 7. Download the remote desktop connection file to your local desktop!

Another great feature is the ability to automatically download the remote desktop file for the provisioned Azure VM.

Get-AzureRemoteDesktopFile -ServiceName "<ServiceName>" -name "<vmname>" -LocalPath "$ENV:userprofile\Desktop\myVm01.rdp"

It is also possible to automatically launch and connect to your VM instance using the standard Remote Desktop client, mstsc.exe, which just eliminates another step in the process to connect to your Azure VM.

 mstsc $ENV:userprofile\Desktop\myAzureVm1.rdp

Of course you could parameterise all of this to make life even simpler.

Step 8. Connect!

Simply enter the username and password off you go, simple. You could create multiple PowerShell batch files for each Azure VM image type. I am sticking with the SQL Server 2012 instance for now.

Teardown

Cleaning up the environment is simple too, just a couple of PowerShell commands to stop and remove the provisioned VM.

Step 9. Stop the Azure VM

 Stop-AzureVM  -ServiceName "<ServiceName>" -Name "<VmName>"

Step 10. Remove/delete the Azure VM

 Remove-AzureVM -ServiceName "<ServiceName>" -Name "<VmName>"

Step 11. Remove/delete the Azure VM disks

The VHD’s associated with the image are not automatically removed so you will need to issue the Remove-AzureDisk command. You can view the existing VHD’s and the associated image and container using the Get-AzureDisk command as shown below. You will notice that I only have one disk (VHD) associated to an image. The other VHD’s were from previous Azure VM deployments.

Removing (deleting) the VHD is simple. The –DeleteVHD parameter is required if you wish to permanently delete the image from ASV so use with caution!

 Remove-AzureDisk –DiskName <diskname> –DeleteVHD

Step 12. Remove the allocated cloud service

 Remove-AzureService -ServiceName "<servicename>"

Step 13. Remember to save your PowerShell script and parameterise it for one-click deployment!

Azure SQL Database Provisioning

On a related noted, it is also possible to provision a Windows Azure SQL Database using the new PowerShell cmdlets allowing me to rapidly deploy a cloud-based relational data store. You must register the Azure publishing certificate unless this has been done previously (as above in step 1.).

 Import-AzurePublishSettingsFile C:\....
Set-AzureSqlDatabaseServer –ServerName <server> –AdminPassword <password>

Creating a new Azure SQL Database instance is also easy, an example is provided below. I chose North Europe due to my geographical location.

 New-AzureSqlDatabaseServer -location "North Europe" -AdministratorLogin "<login>" 
-AdministratorLoginPassword "<password>”

You’ll need to create a firewall rule so you can connect to the new Azure SQL Database instance:

 New-AzureSqlDatabaseServerFirewallRule -ServerName <server> –RuleName <rulename> 
-StartIPAddress "0.0.0.0" -EndIPAddress "222.222.222.222"

As I was working through this, I discovered a VERY useful command which pops up a dialog with the help options for a specific command, an example is shown below. Omitting the –ShowWindow syntax will output the help details to the console window.

 Get-Help Set-AzureSqlDatabase –ShowWindow

The next step was to create a SQL Server authenticated connection to the server hosting the Windows Azure SQL Database. This is an important step as it establishes the context for the connection.

 #specify sql auth credential
$servercredential = new-object System.Management.Automation.PSCredential("<username>", 
("<password>" | ConvertTo-SecureString -asPlainText -Force))

#create a connection context
$ctx = New-AzureSqlDatabaseServerContext –ServerName <servername> -Credential $serverCredential

Incidentally, if you are wondering what is stored in the connection context then see below:

 ServerName        : <server>
SessionActivityId : 850d5e6f-7201-4bad-8fd5-331086064d4a
ClientSessionId   : e8d82a6d-0ed2-4aa3-9c38-3c3da924ab6a-2013-03-13 15:49:35Z
ClientRequestId   : d2436b67-ecca-453d-8d7c-12619e599784-2013-03-13 16:02:36Z
Databases         : {master}

Wondering what databases you can see? Easy.

 Get-AzureSqlDatabase -Context $ctx

Which returns all the databases deployed on the provisioned instance. In the case below, only the master database was listed as no other databases are currently deployed.

 Name          : master
CollationName : SQL_Latin1_General_CP1_CI_AS
Edition       : Web
MaxSizeGB     : 5
CreationDate  : 12/03/2013 22:35:57

Want a new database? Easy again.

 New-AzureSqlDatabase -Context $ctx –DatabaseName <databasename>-Collation SQL_Latin1_General_CP1_CI_AS 
-Edition Web -MaxSizeGB 5

Interestingly, there are a host of DataServiceContext class options made available under the context of the connection such as ServerMetrics and DatabaseMetrics. This provides some interesting insight into the metadata for your Azure SQL database server such as throttled connections and failures. Unfortunately, the context commands are not documented right now so this is just exploratory and the exposed properties may be removed in the future.

 $ctx.ServerMetrics.IncludeTotalCount()
$ctx.DatabaseMetrics.IncludeTotalCount()

Beyond the ability to provision a Windows Azure SQL Database using PowerShell cmdlets, I can also save time by automatically registering the Azure SQL instance in SQL Server Management Studio by invoking the SQL Server 2012 PowerShell command New-Item as below (thereby saving even more time!). The AzureSqlDbServer1 reference is the friendly name which appears in the SQL Server Management console.

 Import-Module sqlps
Cd "sqlregistration\Database Engine Server Group"
New-Item AzureSqlDbServer1 -ItemType Registration -Value "server=<server>.database.windows.net; 
integrated security=false; userid=<username>; password=<password>; initial catalog=<databasename>"

Teardown

Removing (or de-provisioning) the Azure SQL database, instance and Management Studio registration is simple. The last command, Remove-Item, is a SQL Server PowerShell command to delete the Management Studio server registration and this must be invoked using sqlps as above.

 Remove-AzureSqlDatabase $ctx –DatabaseName "<dbname>"
Remove-AzureSqlDatabaseServer -ServerName "<AzureSqlDbServer>"

 Import-Module sqlps
Remove-Item AzureSqlDbServer1

Closing Remarks

The new PowerShell cmdlets for Azure are a fantastic way to easily provision either VM’s or a database in the cloud. I will be parameterising my scripts (and including try.. catch blocks) to quickly create an Windows Azure Virtual Machine or Windows Azure SQL Database as needed (one-click deployment made easy!). PowerShell ISE is also an excellent development environment which can be leveraged for not only Azure VM or SQL database provisioning but also for many more solution scenarios.

What is missing?

Provisioning Azure HDInsight clusters is not currently possible however this should be coming soon https://hadoopsdk.codeplex.com (refer to Programmatic Cluster Management).
PowerShell cmdlets for Azure SQL Reporting are not currently available.
The ability to provision an Azure VM image with the full business intelligence stack deployed i.e. SharePoint 2013, PowerView and Power Pivot integration.
PowerShell remoting to be automatically enabled in the Azure VM.
The ability to invoke SqlCmd and query Azure SQL databases through PowerShell (inc. support for Federations).

References

Managing Windows Azure SQL Databases with PowerShell https://gallery.technet.microsoft.com/scriptcenter/Managing-Windows-Azure-SQL-632acc4b

Windows Azure SQL Database Management with PowerShell(https://blogs.msdn.com/b/windowsazure/archive/2013/02/07/windows-azure-sql-database-management-with-powershell.aspx

Getting Started with SQL Server on a Windows Azure Virtual Machine https://www.windowsazure.com/en-us/manage/windows/common-tasks/sql-server-on-a-vm/

Share via

Using PowerShell to deploy Windows Azure Virtual Machines and Windows Azure SQL Databases

Azure VM Provisioning (SQL Server 2012 Evaluation Edition)

Step 1. Download and register the Azure publishing certificate (one time only event).

Step 2. View available subscriptions and set the correct Subscription

Step 3. View the available Azure VM images and locations

Step 4. Create an Azure VM

Step 6. Start the Azure VM

Step 7. Download the remote desktop connection file to your local desktop!

Step 8. Connect!

Teardown

Step 9. Stop the Azure VM

Step 10. Remove/delete the Azure VM

Step 11. Remove/delete the Azure VM disks

Step 12. Remove the allocated cloud service

Step 13. Remember to save your PowerShell script and parameterise it for one-click deployment!

Azure SQL Database Provisioning

Teardown

Closing Remarks

What is missing?

References

Additional resources