Powershell: Automating QBO Deployments

posted Jan 1, 2014, 6:25 PM by Eric Patrick   [ updated Dec 12, 2014, 10:09 AM ]

Background

The qbo.ps1 Powershell script is a cmdlet library for managing QBO deployment tasks. High level features include management of:
  • IIS: create and copy websites, install SSL certs, manage host headers and configuration files
  • SQL: build and deploy data application packages (.dacpacs)
  • Amazon Web Services: manage creation of new images, add DNS entries, manage load balancers
  • Miscellaneous: manage secure storage of logins used to manage machines, access SVN, etc.
In order to leverage this script, you should have Powershell 4 installed:
  • Open Powershell ISE, and enter $PSVersionTable to determine your PSVersion; 4 is preferred, 3 is a minimum
  • If you don't have it, install .NET Framework 4.5
  • Install Windows Management Framework 4.0
    • this will install without .NET Framework 4.5, but you won't get Powershell 4! Or an error.
    • so install .NET Framework 4.5 first, then install (or re-install) WMF 4.0
  • Install Web Deploy
    • if you don't see a Deploy option in the context menu for a website in IIS Manager, re-install in accordance with this SO post.
  • Install Powershell Community Extensions (for Zip file manipulation)
  • Run the following in Powershell: Set-ExecutionPolicy bypass

Use Case: Create a Web Deploy Package for Easy Installation on IIS

In this use case, we will create a web deploy package (.zip file) that can be imported into IIS, creating our QBO website for us. For this example, we'll build the package from trunk, and include the HelpDesk theme. You can instead build from a branch by specifying a -sourcePath parameter to the qbo.Core folder that is part of your branch (e.g. -sourcePath "e:\source\branches\qbo\201409\qbo.Core").

# Load the standard QBO cmdlets
Import-Module .\qbo.ps1
# Create a package file called "HelpDesk201410.zip" with the core applications
New-qboWebPackage -apps "Application,Accounting,Attachment,Contact,Decision,Heartbeat,Import,Logging,Message,Process,Report,Score,Security,Credit,Debt,Mortgage,SkipTrace" -packageName "HelpDesk201410.zip" -pubfile "UAT.pubxml" -Verbose
# Add in plugins
New-qboWebPackage -plugins "Amazon,ABCPDF,Adobe,Aspose,AuthorizeNet,Form,Google,Quandis,Rebex,Service.Http,Service.InboundService,qbo.HTML,qbo.Queue.MSSQL,DbMaintenance,RemoteQboService" -packageName "HelpDesk201410.zip" -pubfile "UAT.pubxml" -Verbose
# Add in a theme
New-qboWebPackage -theme "HelpDesk" -packageName "HelpDesk201410.zip" -pubfile "UAT.pubxml" -Verbose
# Write the package to SVN, so the target servers can download them
Write-qboSvnFile .\Package\Packages\HelpDesk201410.zip -comment "Created a new Help Desk site package"


Use Case: Install A New Website On An Existing Server

In this use case, we will create help.quandis.net on A1P1QBO.


Import-Module .\qbo.ps1

# When connecting to a remote machine, PS required that you explicitly trust that machine.
# For a machine called u1qbs, this is done via: winrm s winrm/config/client '@{TrustedHosts="u1qbs"}'
# By default, Open-qboSession ... -ensureHost will ensure *.quandis.net is part of your machine's winrm configuration.
# Include '.quandis.net' in all machine connections; if you machine is named u1qbs, create a host header for u1qbs.quandis.net to the private IP.
Open-qboSession "a1p3qbo.quandis.net" -ensureScript -Verbose

Get-PSSession | % {invoke-command -Session $_ -ScriptBlock { Confirm-qboSite -site "help.quandis.net" }}
Get-PSSession | % {invoke-command -Session $_ -ScriptBlock { Publish-qboWebPackage -packageUrl "HelpDesk201410.zip" -appName "help.quandis.net" }}
Get-PSSession | % {invoke-command -Session $_ -ScriptBlock { Confirm-qboWebSetup -site "help.quandis.net" }}

# Create a1p3qbohelp.quandis.net (in addition to help.quandis.net) so we can test the a1p3qbo box directly via its IP address
Get-PSSession | % {invoke-command -Session $_ -ScriptBlock { New-qboSiteAlias -alias "a1p3qbo" -ssl }}

# Grant full control on the website folder to the iis user (this should happen as part of Confirm-qboSite, but if you skipped that step for an existing site, use this.)
Get-PSSession | % {invoke-command -Session $_ -ScriptBlock { Set-qboSiteAcl -site 'help.quandis.net' }}

# Clean up the package file
Delete-qboSvnFile .\Package\Packages\UATQBS-12.04.14-1.zip -comment "Deployment Complete"

Use Case: Ad-Hoc Deployments to Local or DEV

The New-qboWebPackage will use whatever .pubxml file you wish by specifying the -pubfile parameter.  If you wish to deploy a handful of projects to your local machine, create a localhost.pubxml file using FileSystem (or WebDeploy) to publish, instead of Package.

New-qboWebPackage -apps "Application,Attachment" -plugins "Amazon,Rebex" -pubfile .\localhost.pubxml -Verbose 

If you find yourself repeatedly deploying the same set of apps or plugins, you can leverage Set-qboDefault:

set-qboDefault "AllApps" "Application,Accounting,Attachment,Contact,Decision,Heartbeat,Import,Logging,Message,Process,Report,Score,Security,Credit,Debt,Mortgage,SkipTrace"
set-qboDefault "AllPlugins" "Amazon,Rebex"
new-qboWebPackage -pubfile .\localhost.pubxml -apps (get-qboDefault "AllApps") 

QBO Code Deployment

Confirm-qboWebSetup

This will ensure a website is properly configured to run QBO by ensuring required .config files are present.  This is typically used when first setting up a website, and will verify the existence of:
  • web.config
  • Config/connectionStrings.config (including calling Confirm-qboDbUser)
  • Config/FileObject.config
  • Config/MailInbound.config
  • Config/MailOutbound.config
  • Config/Credential.config
Confirm-qboDbUser

This will create a server and database login, if the specified login does not exists.  

Example:

Confirm-qboDbUser -dbServer TAURUS2 -dbName client.quandis.net -dbUser client.login@quandis.net -dbPassword AAdf23!kj


AWS Management

New-qboInstance

This will create a new AWS instance to be used as a web or application server. Parameters include:
  • name: (required) instance name (e.g. A1U7QBO)
  • profile: (optional, default is 'default') the AWS credential profile you previously saved on the machine running powershell
  • region: (optional, default is us-east-1) the AWS region to launch into
  • ami: (optional, default is QBO DEV 2014.06) AMI to base the instance on
  • keyPair: (optional, default is AWS_SQL) certificate key pair to use when spinning up the image
  • sg: (optional, default is VPCWebSG) security group the instance should be a member of
  • type: (optional, default is m1.small) AWS image size to launch
  • subnet: (optional, default is 3.0) subnet to bind instance NIC to
  • dsn: (optional, default is true) when true, the instance is added to DNS as part of the quandis.net domain. 
  • pollingSeconds: (optional, default is 5) when waiting for an instance to spin up, number of seconds between polling
  • pollingMax: (optional, default is 180) maximum number of seconds to wait for an instance to spin up
Get-qboInstances

This will get a list of AWS EC2 instances, filtered by a string. Parameters include:
  • tag: (optional, default is 'name') the tag to filter by
  • value: (optional) the tag value to filter by
Example: 
get-qboinstances "qbo"

This will return all AWS EC2 instances with 'qbo' somewhere in the 'name' tag.

IIS Management

Confirm-qboSite

This will create a website if it does not already exist, including:
  • creating an application pool dedicated to the site
    • enables loading user profiles so QBO code can access certificates
    • enables 32 bit processing for plugins
    • sets the runtime version to v4.0
  • sets up SSL on the site (if -ssl is specified
  • sets up the site's folder ACL to allow saving to .config file
Example:

Confirm-qboSite -site "client.quandis.net" -ssl

Set-qboConnection

This will modify a connection string in /Config/ConnectionStrings.config in a target website. Parameters are:
  • site: name of the website containing a connectionStrings.config file to modify
  • name: name of the connection (defaults to "qbo.Default")
  • connectionString: full connection string to use
  • dataSource: data server to use
  • dataBase: database to use
  • timeout: timeout to use
  • username: database username
  • password: database password
  • appName: application name
You can modify the entire connection string, or just one part. Connection strings are in the format:

Data Source={dataSource};Initial Catalog={database};Connect Timeout={timeout};Trusted_Connection=false;UID={username};PWD={password};Application Name={appName}

Examples:

// Set a connection string (-name will default to "qbo.Default")
Set-qboConnection -site forms.quandis.net -cs "Data Source={mydbserver};Initial Catalog=forms.quandis.net;Connect Timeout=120;Trusted_Connection=false;UID=login@quandis.net;PWD={...};Application Name=forms.quandis.net"

// Change the app name used by a machine (so SQL profiler shows which machine is making a DB call)
Set-qboConnection -site forms.quandis.net -appName "A1P4Forms"

Copy-qboSite

This will create a new target website, copy the Bin and Config folders from the source, and create a virtual directory for all other folders in the source.

Examples:

// Copy the forms.quandis.net site to slsforms.quandis.net, so we can customize the UI
Copy-qboSite "forms.quandis.net" "slsforms.quandis.net"
Set-qboConnection -site slsforms.quandis.net -appName "A1P4SlsForms"

Write-qboIISLogs

This will use LogParser.exe to copy IIS log files to a SQL server. The cmdlet is smart enough to query the target database for the last log written from a machine, and only write log entries that occur after that last log date-time stamp.  If there are no previous log entries from the machine, the last 3 days worth of logs will be written by default; this can be overridden with the -from parameter.

Note that if LogParser is not installed on the machine, the cmdlet will download and install it.

Parameters:
  • dbserver (required): IP address or DNS entry of the SQL Server to copy IIS Logs to
  • dbname (required): name of the database on the dbserver
  • dbcredential (optional, defaults to "DB-{dbserver}"): the name of the credential entry to use to connect to the dbserver
  • machine (optional, defaults to $env:COMPUTERNAME): the name of the machine the logs are being copied from
  • from (optional, defaults to Get-Date.AddDays(-3)): the earliest log entry
Example:

// Connect to the SLS PROD 
Open-qboSession -targets "a1p1qbo.quandis.net,a1p2qbo.quandis.net"
// For each connection, copy IIS logs to the support.quandis.com database
Get-PSSession | % { Invoke-Command -Session $_ -ScriptBlock {  Write-qboIISLogs -machine $_.ComputerName -dbserver 10.11.101.125 -dbname support.quandis.com } }

Set-qboUser

Creates a Windows local user, sets their password, and adds them to the Administrators group.

Examples:

// Create user jdoe with a password YoYoMa
Set-qboUser jdoe YoYoMa

// Connect to a1p1qbo and a1p4qbo, ensuring your client trusts *.quandis.net and *.quandis.com. Loads the qbo.ps1 module on the target machine.
Open-qboSession "a1p1qbo.quandis.net,a1p4qbo.quandis.net" -ensureHost

// Set user jdoe password to YoYoMa on multiple machine at once
Get-PSSession | % {invoke-command -Session $_ -ScriptBlock { Set-qboUser jdoe YoYoMa }}

Remove-qboUser

Removes a Windows local user.

Get-qboSvnFile

Downloads a file from SVN to a target path on the local machine.

Examples:

// Download the Queue Service MSI
Get-qboSvnFile -path "Trunk/qbo.3/Installation/qbo.QueueService.2.3.0.x64.msi" -target "c:\program files (x86)\Quandis"

Remoting

Powershell remoting allows you to execute scripts on a remote machine.

Troubleshooting

Error: 

New-PSSession : [a1p1qbo.quandis.net] Connecting to remote server a1p1qbo.quandis.net failed with the following error message : The WinRM client cannot process the 
request. If the authentication scheme is different from Kerberos, or if the client computer is not joined to a domain, then HTTPS transport must be used or the 
destination machine must be added to the TrustedHosts configuration setting. Use winrm.cmd to configure TrustedHosts. Note that computers in the TrustedHosts list might 
not be authenticated. You can get more information about that by running the following command: winrm help config. For more information, see the 
about_Remote_Troubleshooting Help topic.

Solution:

On your client (development machine), ensure you trust the server you are connecting to by adding the host to the TrustedHosts attribute in WinRM. This can be done by calling:

Open-qboSession -target a1p1qbo.quandis.net -ensureHost

Then, on the server, ensure Powershell Remoting is enabled:

enable-psremoting -force

History


Powershell can be used to automate the initial setup and maintain code base of QBO deployments.

Prerequisites:
Source > qbo.3 > Publish contains several files relevant to deployments, including the following PowerShell scripts:
  • db.ps1: creates a database, and runs all table and function scripts found in QBO 3 source resource files
  • iis.ps1: creates an apppool, website, installs an SSL cert, and grants apppool permissions to the root of the website
  • deploy.ps1: uses MSBuild to build and deploy code, including copying missing .config files

IIS Deployments: deploy.ps1

Open Powershell, and navigate to the folder containing deploy.ps1 (usual your SVN > qbo.3 > Publish folder):

// Deploy all core apps and plugins to an IIS site using localhost.pubxml file
PS E:\source\qbo.3\publish> .\deploy.ps1 -site localhost -allapps -allplugins

// Deploy just the Accounting and Security modules to an IIS site using localhost.pubxml file
PS E:\source\qbo.3\publish> .\deploy.ps1 -site localhost -apps @("Accounting", "Security")

// Deploy all Amazon plugins to an IIS site using uatadr.pubxml file
PS E:\source\qbo.3\publish> .\deploy.ps1 -site uatadr -plugins "Amazon"

// Deploy just the Support theme to an IIS site using a impdev.pubxml file; note a password is required if the pubxml file uses MSDeploy (Web Deployment)
PS E:\source\qbo.3\publish> .\deploy.ps1 -site impdev -password {Your NT/IIS account password} -themes "Support"

AWS Server Configuration

Open Powershell, and navigate to the folder containing aws.ps1:

// Set your AWS credentials (one-time)
Set-AWSCredentials -AccessKey {your access key} -SecretKey {your secret key} -StoreAs default

// Create a new web VM called A1U2Forms based on all defaults.
PS E:\source\qbo.3\publish> .\aws.ps1 -name "A1U2Forms"

// Create a new VM called A1U3Forms based on the QBO Standard AMI in the 10.0.4.0 subnet
PS E:\source\qbo.3\publish> .\aws.ps1 -name "A1U3Forms" -ami "QBO Standard" -subnet "4.0"

Parameters:
  • name: the name of the instance (e.g. A1U2Forms)
  • ami: (default = "QBO DEV with SQL Express") the Quandis-owned AMI to use
  • subnet: (default = "3.0") part of a CIDR to match on subnets (e.g. 3.0 matches 10.0.3.0/24, 4.0 matches 10.0.4.0/24; careful that "4" matches anything with /24 at the end!)
  • type: (default = m1.small) the EC2 instance type ot use
  • sg: (default = VPCWebSG) the security group to assign to the EC2 instance
  • profile: (default = 'default') the AWS profile on your local machine to use (see setting up AWS credentials)
  • region: (default = us-east-1) the AWS region to default to
  • keypair: (default = AWS_SQL) the keypair credentials in AWS to use
Actions taken:
  • Finds an AMI matching the 'ami' parameter
  • Finds a Security Group matching the 'sg' parameter
  • Finds a Subnet matching the 'subnet' parameter
  • Creates an EC2 instance from the specified AMI on the specified subnet
  • Finds the 'sister' subnet of the Subnet parameter (e.g. 10.0.2.0 and 3.0 are 'sisters'; 10.0.4.0 and 5.0 are 'sisters')
  • Creates a new NIC on the sister subnet
  • Waits for the EC2 instance to be in a running state
  • Adds the second NIC to the EC2 instance
  • Adds the EC2 instance to the quandis.net domain (E.g. A1U5MySite creates a A1U5MySite.quandis.net DNS entry)






The rest of this post is still unstructured notes on what we've done so far.

Example db.ps1 usage:
  • Install a db on a local machine:
    .\db.ps1 -password "mypassword" -db "mysite.quandis.net"
  • Install a db on taurus2:
    .\db.ps1 -server "taurus2.quandis.net\taurus2uat" -password "mypassword" -db "mysite.quandis.net" -env "UAT"
Parameters for db.ps1 include:
  • db: name of database to create
  • server: name of server to create db on (the server portion of a connection string); defaults to 'localhost'
  • user: username of SQL account to run under (defaults to logged in Windows user)
  • password: password of SQL account to run under
  • env: environment deploying to; determines the file system locations of the file groups. Valid values are PROD | UAT | DEV; default is DEV
  • admin: the QBO admin account to create; defaults to admin@quandis.com / password of 'password'
  • notable: when specified, tables are not installed
  • nofunction: when specified, functions are not installed

Creating a new Database:
  • $query = [IO.File]::ReadAllText("e:\source\qbo.3\publish\CreateDatabase.sql")
  • Invoke-SqlQuery -Parameters @{Db='qboTest'} -Query $query
Install all tables and user-defined functions from source:
  • foreach ($item in Get-ChildItem -Path ..\qbo.Core -Filter CreateTable*.txt -Recurse)  
    {
      echo $item.Name
      Invoke-SqlQuery -File $item.FullName -Database "qboTest"
    }
  • foreach ($item in Get-ChildItem -Path ..\qbo.Core -Filter CreateFunction*.txt -Recurse)
    {
      echo $item.Name
      Invoke-SqlQuery -File $item.FullName -Database "qboTest"
    }
Set up an admin account with the password 'Password':
  •  Invoke-SqlQuery -Query $query -Parameters @{Person='admin@quandis.net'} -Database 'qboTest'
Running MSBuild:
  • Download the Invoke-MsBuild Powershell Module
  • Run Unblock-File C:\Users\epatrick\Documents\WindowsPowerShell\Modules\Invoke-MsBuild.psm1 (change the path appropriately)
  • Import-Module C:\Users\epatrick\Documents\WindowsPowerShell\Modules\Invoke-MsBuild
  • $core = "e:\Source\qbo.3\qbo.Core"
  • $vs = "12.0"
  • $profile = "local"
  • Invoke-MsBuild -Path "${core}/Web Tier/qbo.ApplicationWeb/qbo.ApplicationWeb.csproj" -MsBuildParameters "/p:VisualStudioVersion=${vs} /p:PublishProfile=${pubFile} /p:DeployOnBuild=true"
Setting up IIS:
  • Download and install the IIS Powershell Snap-in, unless it's already installed
  • # enable web administration
  • import-module WebAdministration
  • # add a host file entry for the new site
  • "127.0.0.1 qbo3test.quandis.net" | Out-File $hostsPath -append -enc ascii
  • # Create a new website
  • new-item iis:\Sites\qbo3test.quandis.net -physicalPath "$env:systemdrive\inetpub\qbo3test" -bindings @{protocol="http";bindingInformation=":80:qbo3test.quandis.net"}
  • # remove the website sample
  • # remove-item qbo3test -recurse
  • # find a .pfx file
  • $certFile = (Get-ChildItem -Path .\SSL -Filter mar2012*.pfx -Recurse)
  • # prep the password for the pfx file
  • $pwd = ConvertTo-SecureString -String "{password; escape $ with `}" -Force -AsPlainText
  • # register the pfx file in the cert store
  • $cert = $certFile | Import-PfxCertificate -CertStoreLocation cert:\localmachine\my -password $pwd
  • # add https to the site
  • New-ItemProperty iis:\Sites\$url -name bindings -value @{protocol="https";bindingInformation=":443:$url"}
  • # apply the SSL cert to all sites
  • $cert | new-item 0.0.0.0!443
  • # deploy web.config for first install only
  • if ((test-path ($target + "\web.config")) -eq $false)
    { 
      copy-item -path "..\qbo.core\Web Tier\qbo.ApplicationWeb\web.config" -destination "c:\inetpub\qbo3test" 
    }


Comments