How to Publish a Complex Windows Azure Application with System Center 2012 Orchestrator

Following on from my previous post about how easy it was to publish an application to Windows Azure with Orchestrator, I thought it useful to complicate things and include a few scenarios we see customers using with their Windows Azure Applications.

The cases:

• Publish the Windows Azure Application with RDP enabled.
• Create a SQL Azure Server and SQL Azure Database.
• Creating the Storage Service at deployment.

And to make it more interesting:

• Geo-locate components together
• Do the housekeeping afterwards

Credit for this post goes to my good friend and very talented colleague Fabrice Aubert, whom I worked with to create a Microsoft Premier workshop titled: Monitoring Windows Azure Applications with System Center 2012 Operations Manager.

Prerequisites

• Download and install the Windows Powershell cmdlets for Windows Azure: https://www.windowsazure.com/en-us/manage/downloads/ on your Runbook and optionally on your workstation.
• You must run Get-AzurePublishSettingsFile which will open IE for you to log into your subscription and download the PublishSettingsFile, save this file and put it on your Runbook Servers. IMPORTANT NOTE: The file is very sensitive, strip away the NTFS permission to the bare minimum.
• Package your application with RDP enabled roles: https://msdn.microsoft.com/en-us/library/windowsazure/gg443832.aspx
• Copy your Windows Azure Certificate as a .pfx file, with the private key, to your Runbook servers (or somewhere accessible). Note the password.

A note on documentation

Try to use a few conventions when developing Runbooks and writing documentation, it's quite OCD but it has served me well:

• Colour success links in green
• Colour unencrypted variables in {purple}
• Colour encrypted variables in {gold}
• Colour published data in {maroon}

A note on scripting

When writing PowerShell for Run .NET Script activity, always try to put all variables that will use SCO Variables and Published Data at the top of the script, this allows for easy maintenance in the future. Also use try & catch, as Orchestrator can have a habit of completing an activity successfully, only for you to find that the script actually didn't work. I broke one rule I live by in this RunBook and that is to keep PowerShell scripts simple, don't try to throw the entire Runbook into one Run .NET Script Activity. It can really affect your ability to troubleshoot a failed activity. Very good PowerShell authors find this REALLY hard to do, sadly I don't!

The Runbook

• Initialize Data Activity| Intialize Data
  • Input parameters:
    • ServiceName
    • StorageServiceName
    • location
  • Notes:
    • StorageServiceName must be unique, all lowercase and no special characters.
    • Location must be a valid Windows Azure data center location:
      • West US
      • East US
      • East Asia
      • Southeast Asia
      • North Europe
      • West Europe
• Azure Cloud Services Activity| Create Affinity Group
  • Choose an Activity: Create Affinity Group
  • Affinity Group Name: afgrp{StorageServiceName from "Initialize Data"}
  • Label: AffGrp-{ServiceName from "Initialize Data"}-{location from "Initialize Data"}
  • Description: Affinity Group for {ServiceName from "Initialize Data"} in {location from "Initialize Data"} -created by Orchestrator
  • Location: {location from "Initialize Data"}
  • Notes:
    • Affinity Group Name must be all lowercase and no special characters
• Azure Cloud Services Activity| Create Cloud Service
  • Choose an Activity: Create Cloud Service
  • Service DNS Prefix: {ServiceName from "Initialize Data"}
  • Label: {ServiceName from "Initialize Data"} V2
  • Description: Created by Orchestrator
  • Location/Affinity Group: AffinityGroup
  • Location/Affinity Group Value: {Affinity Group Name from "Create Affinity Group"}
  • Notes:
    • Service DNS Prefix must be unique
• Run .NET Script | Upload RDP Certificate
  • Language: PowerShell
  • Script:

try{

#Grab Certificate

$pathToRDPCertificate = "{AzureDeploymentPath}\GuestBookPackage\GuestBook-RD.pfx"

$RDPCertificatePassword = "{CertificatePassword}"

 

#Get Service

$service = "{Service DNS Prefix from "Create Cloud Service"}"

 

#Import Windows Azure PowerShell Module

Import-Module "{AzurePowershellModulePath}"

 

#Get PublishSettingsFile

Import-AzurePublishSettingsFile -PublishSettingsFile "{AzurePublishSettingsFile}"

$sub = select-AzureSubscription –SubscriptionName "{AzureSubscriptionName}"

 

#Add Service Certificate

Add-AzureCertificate -servicename $service -CertToDeploy $pathToRDPCertificate -Password $RDPCertificatePassword

}

catch

{

Throw $_.Exception

}

• Published Data: None
• Best Practise: Create variables for published data and SCO variables at the top of PowerShell scripts, it makes for easy maintenance.

• Azure Storage Activity | Create Azure Storage Account
  • Choose an Activity: Create Storage Account
  • Storage Account Name: {StorageAccountName from "Initialize Data"}
  • Label: Created By Orchestrator
  • Description: Created By Orchestrator for {Service DNS Prefix from "Create Cloud Service"} in {Affinity Group Name from "Create Affinity Group"} Affinity Group
  • Location/Affinity Group: AffinityGroup
  • Location/Affinity Group Value: {Location/Affinity Group Value from "Create Cloud Service"}
  • Wait for Completion: True
• Azure Storage Activity | Get Storage Account Keys
  • Choose an Activity: Get Storage Account Keys
  • Storage Account Name: {StorageAccountName from "Create Azure Storage Account"}
• Run .NET Script | Create SQL Azure Server and Database Properties
  • Language: PowerShell
  • Script:

try{

#Import Windows Azure Powershell Module

Import-Module "{AzurePowerShellModulePath}"

 

#Storage information

$storageAccount = "{StorageAccountName from "Get Storage Account Keys"}/"

$storageLocation = "{Location from "Initialize Data}"

 

#SQL Azure Server information

$adminLogin = "{SQLLogin}"

$adminPassword = "{SQLPassword}"

 

Import-AzurePublishSettingsFile -PublishSettingsFile "{AzurePublishSettingsFile}"

$sub = Get-AzureSubscription –SubscriptionName "{AzureSubscriptionName}"

 

#Create a new SQL Azure Server

$newServer = New-AzureSqlDatabaseServer -AdministratorLogin $adminLogin -AdministratorLoginPassword $adminPassword -Location $storageLocation

$newServer | New-AzureSqlDatabaseServerFirewallRule -RuleName "EveryBody" -StartIpAddress "0.0.0.0" -EndIpAddress "255.255.255.255"

 

Start-Sleep -s 30

 

#Create ADO.Net Object

$cn = New-Object System.Data.SqlClient.SqlConnection

$cm = New-Object System.Data.SqlClient.SqlCommand

 

#Create GuestBookDB database

$cn.ConnectionString = "Server=tcp:" + $newServer.ServerName + ".database.windows.net,1433;Database=master;User ID=" + $adminLogin + "@" + $newServer.ServerName + ";Password=" + $adminPassword + ";Trusted_Connection=False;Encrypt=True;"

$outconn = $cn.connectionstring

$sql = "CREATE DATABASE GuestBookDB (EDITION='WEB', MAXSIZE=5GB)"

$cm.Connection = $cn

$cm.CommandText = $sql

$cn.Open()

$cm.ExecuteNonQuery()

$cn.Close()

 

Start-Sleep -s 30

 

#Create tables and constraints in GuestBookDB

$cn.ConnectionString = "Server=tcp:" + $newServer.ServerName + ".database.windows.net,1433;Database=GuestBookDB;User ID=" + $adminLogin + "@" + $newServer.ServerName + ";Password=" + $adminPassword + ";Trusted_Connection=False;Encrypt=True;"

 

#Create table Users

$sql = "CREATE TABLE [dbo].[Users]("

$sql = $sql + "[Alias] [nvarchar](50) NOT NULL,"

$sql = $sql + "[FirstName] [nvarchar](100) NOT NULL,"

$sql = $sql + "[LastName] [nvarchar](100) NOT NULL,"

$sql = $sql + "CONSTRAINT [PrimaryKey_9f4b532a-c0c4-47ba-9382-1cd19b4cf96f] PRIMARY KEY CLUSTERED"

$sql = $sql + "("

$sql = $sql + "[Alias] ASC"

$sql = $sql + ")WITH (STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF)"

$sql = $sql + ")"

 

$cm.CommandText = $sql

$cn.Open()

$cm.ExecuteNonQuery()

$cn.Close()

 

#Create table Comments

$sql = "CREATE TABLE [dbo].[Comments]("

$sql = $sql + "[ID] [int] IDENTITY(1,1) NOT NULL,"

$sql = $sql + "[AliasKey] [nvarchar](50) NOT NULL,"

$sql = $sql + "[Comment] [nvarchar](200) NOT NULL,"

$sql = $sql + "CONSTRAINT [PrimaryKey_4d45f55b-36c9-48d5-b89c-3d9f7149d4a9] PRIMARY KEY CLUSTERED "

$sql = $sql + "("

$sql = $sql + "[ID] ASC"

$sql = $sql + ")WITH (STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF)"

$sql = $sql + ")"

 

$cm.CommandText = $sql

$cn.Open()

$cm.ExecuteNonQuery()

$cn.Close()

 

#Add a foreign key constraint

$sql = "ALTER TABLE [dbo].[Comments] WITH CHECK ADD CONSTRAINT [FK_Comments_0] FOREIGN KEY([AliasKey])"

$sql = $sql + "REFERENCES [dbo].[Users] ([Alias])"

 

$cm.CommandText = $sql

$cn.Open()

$cm.ExecuteNonQuery()

$cn.Close()

 

$sql = "ALTER TABLE [dbo].[Comments] CHECK CONSTRAINT [FK_Comments_0]"

 

$cm.CommandText = $sql

$cn.Open()

$cm.ExecuteNonQuery()

$cn.Close()

 

#Return DB Server Name

$SQLserver = $newServer.ServerName

 

}

catch

{

Throw $_.Exception

}

• Published Data: SCOSQLServerName: $SQLServer

• Run .NET Script | Edit ServiceConfig File
  • Language: PowerShell
  • Script:

$storageAccount = "{StorageAccountName from "Get Storage Account Keys"}/"

$storageAccountKey = "{PrimaryKey from "Get Storage Account Keys"}"

$adminLogin = "{SQLLogin}"

$adminPassword = "{SQLPassword}"

$SQLServer = "{SCOSQLServerName from "Create SQL Azure Server and Database"}"

$svccfgfile = "{AzureDeploymentPath}\GuestBookPackage\ServiceConfiguration.cscfg"

 

try

{

 

#Create Connection String

$connectionString = "DefaultEndpointsProtocol=https;AccountName=" + $storageAccount + ";AccountKey=" + $storageAccountKey

 

#Update the ServiceConfiguration.cscfg with the information about the storage account and the SQL Server database created

$xml = New-Object XML

$xml.Load($svccfgfile)

 

$count = $xml.ServiceConfiguration.Role[0].ConfigurationSettings.Setting.count

For($i=0; $i -lt $count; $i++) {

if ($xml.ServiceConfiguration.Role[0].ConfigurationSettings.Setting[$i].Name -eq "Microsoft.WindowsAzure.Plugins.Diagnostics.ConnectionString")

    {

    $xml.ServiceConfiguration.Role[0].ConfigurationSettings.Setting[$i].Value = $connectionString

    }

      

if ($xml.ServiceConfiguration.Role[0].ConfigurationSettings.Setting[$i].Name -eq "ServerName")

    {

    $xml.ServiceConfiguration.Role[0].ConfigurationSettings.Setting[$i].Value = $SQLServer

    }

      

if ($xml.ServiceConfiguration.Role[0].ConfigurationSettings.Setting[$i].Name -eq "UserName")

    {

    $xml.ServiceConfiguration.Role[0].ConfigurationSettings.Setting[$i].Value = $adminLogin

    }    

    

    if ($xml.ServiceConfiguration.Role[0].ConfigurationSettings.Setting[$i].Name -eq "Password")

    {

    $xml.ServiceConfiguration.Role[0].ConfigurationSettings.Setting[$i].Value = $adminPassword

    }

}

 

$count = $xml.ServiceConfiguration.Role[1].ConfigurationSettings.Setting.count

 

For($i=0; $i -lt $count; $i++) {

if ($xml.ServiceConfiguration.Role[1].ConfigurationSettings.Setting[$i].Name -eq "Microsoft.WindowsAzure.Plugins.Diagnostics.ConnectionString")

    {

    $xml.ServiceConfiguration.Role[1].ConfigurationSettings.Setting[$i].Value = $connectionString

    }

}

 

$xml.Save($svccfgfile)

 

}

catch

{

Throw $_.Exception

}

• Azure Storage Activity | Create Blob Container
  • Choose an Activity: Create Container
  • Storage Account Name: {Storage Account Name from "Get Storage Account Keys"}
  • Container Name: scoblob{Activity end time (minutes) from "Get Storage Account Keys"}
  • Primary Key: {Primary Key from "Get Storage Account Keys"}
• Azure Storage Activity | Put "Service Package" Blob
  • Choose an Activity: Put Blob
  • File to Upload (File Path): {AzureDeploymentPath}\GuestBookPackage\GuestBook.cspkg
  • Storage Account Name: {Storage Account Name from "Get Storage Account Keys"}
  • Container Name: {Container Name from "Create Blob Container"}
  • Blob Name: GuestBookPackage
  • Primary Key: {Primary Key from "Get Storage Account Keys"}
• Azure Deployments Activity | Azure Deployment
  • Choose an Activity: Create Deployment
  • Service DNS Prefix: {Service DNS Prefix from "Create Cloud Service"}
  • Deployment Slot: Production
  • Deployment Name: SCOGuestBook
  • Label: SCO
  • Service Configuration File Path: {AzureDeploymentPath}\GuestBookPackage\ServiceConfiguration.cscfg
  • Service Package URL: {Blob URL from "Put "Service Package" Blob"}
  • Start Deployment Immediately: True
  • Treat Warnings as Errors: True
  • Wait for Completion: True
• Azure Storage Activity | Remove Blob
  • Choose an Activity: Delete Container
  • Storage Account Name: {Storage Account Name from "Put "Service Package" Blob"}
  • Container Name: {Container Name from "Create Blob Container"}
  • Primary Key: {Primary Key from "Get Storage Account Keys"}

 

Running the Runbook

Open the Runbook tester and fire this up. Input the Initialize Data values making sure to keep the storage name lowercase and use the correct Azure Data Center Location name.

On each step, check the activities are doing their things against Azure.

 

Initialize Data
Create Affinity Group
Create Cloud Service
Upload RDP Certificate
Create Azure Storage Account
Storage Account Keys	Check J
Create SQL Server and Database
Edit Service Configuration File
Create Blob Container	Check!
Put "Service Package" Blob	Check!
Azure Deployment
Remove Blob	No SCOBlobxx container, just containers for the GuestBook pictures and the diagnostic monitor data.
Check the site works!