How to deploy an SQL template to Azure Stack Hub using PowerShell

This article explains how to deploy an SQL Always On Cluster using an ARM Template to Azure Stack Hub using PowerShell.

It will guide you through the process of:

• Obtaining an ARM Template

• Deploying an ARM Template for SQL Always On

What is an ARM Template?

You can use Azure Resource Manager (ARM) templates to deploy and provision all the resources for your application in a single, coordinated operation. You can also redeploy templates to make changes to the resources in a resource group. These templates can be deployed via the Azure Stack Hub portal, PowerShell, Azure CLI, REST API and Visual Studio. The following quick-start templates are available on GitHub.

Prerequisites

Prerequisites from a Windows-based external client are:

• PowerShell 5.1 and AzureStack PowerShell Module:

  • Configure PowerShell Environment and Azure Stack Hub Module

• An active Azure Stack Hub subscription

• An Azure Stack Hub Service Principal Name (SPN)

Microsoft documentation

• Azure Stack Hub ARM Templates Overview

• Deploy a template to Azure Stack Hub using PowerShell

• Understand the structure and syntax of Azure Resource Manager Templates

• GitHub Template Repository

Download SQL Always On template from GitHub

SQL Always On Repository - sql-2016-alwayson

Note

This will download just the sql-2016-alwayson folder from the repository instead of downloading all of it.

Change $Env:USERPROFILE\sql-2016-alwayson below to the desired folder.

• From an elevated (run as administrator) PowerShell prompt run:

  mkdir $Env:USERPROFILE\sql-2016-alwayson
  cd $Env:USERPROFILE\sql-2016-alwayson
  git init
  git config core.sparseCheckout true
  git remote add origin -f "https://github.com/Azure/AzureStack-QuickStart-Templates"
  "sql-2016-alwayson*" | Out-File -FilePath .git/info/sparse-checkout -Encoding ASCII
  cat .git/info/sparse-checkout
  git checkout master
  

What does it deploy?

This template deploys two SQL Server 2016 SP1 or SP2 Enterprise / Standard / Developer instances in the Always On Availability Group using the PowerShell DSC extension. It creates the following resources:

• A network security group.

• A virtual network.

• Four storage accounts (One for AD, one for SQL, one for file share witness and one for VM diagnostics).

• Four public IP address (One for AD, one for each SQL VM and one for a public LB bound to SQL always on listener).

• One external load balancer for SQL VMs with a public IP bound to the SQL always on listener.

• One VM (WS2016) configured as a domain controller for a new forest with a single domain.

• Two VMs (WS2016) configured as SQL Server 2016 SP1 or SP2 Enterprise/Standard/Developer.

• One VM (WS2016) configured as a file share witness for the cluster.

• Two availability sets, one containing the SQL and FSW 2016 VMs, the other containing the domain controller VM.

Notes

The images used to create this deployment are:

• Active Directory (AD) and File Share Witness (FSW) - Windows Server 2016 Datacenter image.

• SQL Server - SQL Server 2016 SP1 or SP2 on Windows Server 2016 image (Enterprise, Standard or Developer).

• SQL IAAS Extension 1.2.30.

• Latest DSC Extension (2.76.0 or higher).

• Latest custom script extension for Windows (1.9.1, or higher).

Warning

If you deploy SQL STANDARD, your Availability Group will have limitations as per Basic Availability Groups:

Basic Availability Group Limitations

Basic availability groups use a subset of features compared to those normally found in advanced availability groups on SQL Server 2016 Enterprise Edition. Basic availability groups include the following limitations:

• Limit of two replicas (primary and secondary).

• No read access on secondary replica.

• No backups on secondary replica.

• No integrity checks on secondary replicas.

• No support for replicas hosted on servers running a version of SQL Server prior to SQL Server > 2016 Community Technology Preview 3 (CTP3).

• Support for one availability database.

• Basic availability groups cannot be upgraded to advanced availability groups. The group must be dropped and re-added to a group that contains servers running only SQL Server 2016 Enterprise Edition.

• Basic availability groups are only supported for Standard Edition servers.

• Basic availability groups can not be part of a distributed availability group.

Configuration

• Each SQL VM will have the number and size of data disks specified, of up to 1TiB each. The SQL extension will configure these into a single volume using storage spaces.

• The template configures the SQL instances with contained database authentication set to true.

• The SQL VMs and the file share witness will be configured in an availability set:

  • Integrated systems (fault domains:3, update domains:5)

• The DNS suffix for public IP addresses.

Overview of the ARM Template deployment process for Azure Stack Hub using Service Principal Name (SPN) authentication

1. Declare your variables accordingly.

2. Create your Azure Stack Hub environment.

3. Log in to your Azure Stack Hub subscription with the SPN.

4. Check if the resource group exists and create one if it does not.

5. Validate the JSON ARM template to ensure it is a valid deployment.

6. Deploy resources from ARM Template.

List of parameters you can define in the template

Name	Description	AllowedValues	DefaultValue
_artifactsLocation	The blob store where all deployment artifacts are stored		https://raw.githubusercontent.com/Azure/AzureStack-QuickStart-Templates/master/sql-2016-alwayson
AdminPassword	The password for the administrator account of the new VMs and domain
AdminUsername	The name of the administrator of the new VMs and domain		localadmin
AdPDCNICIPAddress	The IP address of the new AD VM		10.0.0.250
AdVMSize	The size of the AD VM created	{Standard_D1_v2, Standard_D2_v2}	Standard_D2_v2
AutoPatchingDay	The day of the week for auto patching	{Never, Everyday, Sunday, Monday...}	Sunday
AutoPatchingStartHour	The start hour of the day for auto patching	{0, 1, 2, 3... 23}	2
DeploymentPrefix	The DNS Prefix for the public IP address for the always on cluster		aodns
DnsSuffix	The DNS Suffix for reverse lookup of public IP addresses	azure.ukcloud.com
DomainName	The FQDN of the AD domain created		fabrikam.local
SqlAOAGName	The SQL AlwaysOn group name		sqlaa-ag
SqlAOListenerName	The SQL AG listener name		[tolower(concat('ao-listen-' , resourceGroup().name))]
SqlAOListenerPort	The SQL AG listener port		1433
SqlAuthPassword	The SQL server auth account password
SqlAuthUserName	The SQL server auth account name		sqlsa
SqlServerServiceAccountPassword	The SQL server service account password
SqlServerServiceAccountUserName	The SQL server service account name		sqlservice
SqlServerOffer	The SQL server version	{SQL2016SP1-WS2016, SQL2016SP2-WS2016}	SQL2016SP2-WS2016
SqlServerSku	The name of the SQL server SKU	{Enterprise, Standard, SQLDEV}	Enterprise
SqlStorageAccountName	The name of the SQL server storage account		[tolower(concat(take(uniqueString(resourceGroup().id),8),'sql'))]
SqlStorageAccountType	The type of the SQL server storage account created	{Premium_LRS, Standard_LRS}	Premium_LRS
SqlSubnet	The address range of the SQL subnet created in the new VNET		10.0.1.0/26
SqlVMSize	The size of the SQL VMs created	{Standard_DS2_v2, Standard_DS3_v2, Standard_DS4_v2, Standard_DS5_v2, Standard_DS11_v2, Standard_DS12_v2, Standard_DS13_v2}	Standard_DS2_v2
NumberOfSqlVMDisks	The number of data disks for each SQL VM	{1...32}	2
StaticSubnet	The address range of the subnet static IPs which are allocated from within the new VNET		10.0.0.0/24
VirtualNetworkAddressRange	The address range of the new VNET in CIDR format		10.0.0.0/16
VirtualNetworkName	Name of virtual network to be created		sqlhaVNET
VMDiskSize	The size of the SQL VMs' data disk(s) in GB.	{128, 256, 512, 1023}	128
WitnessVMSize	The size of the witness VM created	{Standard_D1_v2, Standard_D2_v2}	Standard_D1_v2
WorkloadType	The SQL VM work load type	{GENERAL, OLTP, DW}	GENERAL
SampleDatabaseName	Sample HA database		AutoHa-sample

Deploy ARM template code

Declare variables

Important

Credential variables to change:

$AppGUID, $AppPassword

Required Variables for SQL template:

$AdminPassword, $SqlServerServiceAccountPassword, $SqlAuthPassword

Additional variables that you should change:

$DomainName, $AdminUsername, $SqlServerServiceAccountUserName

In the example below it has been already set accordingly.

To change the SQL server version that is deployed, set $SqlServerOffer accordingly: SQL2016SP1-WS2016, SQL2016SP2-WS2016. The current default is set to SQL2016SP2-WS2016.

To change the SQL server SKU that is deployed, set SqlServerSKU accordingly: Enterprise or Standard or SQLDEV. The current default is set to Enterprise.

Enter details below to provide values for the variables in the following scripts in this article:

Variable name	Variable description	Input
$ArmEndpoint	The Azure Resource Manager endpoint for Azure Stack Hub
$ClientID	The client ID of your Azure Stack Hub SPN
$ClientSecret	The client secret of your Azure Stack Hub SPN
$TenantID	The Tenant/Directory ID of your AAD domain
$AdminPassword	The password for the administrator account of the new VMs and domain
$SqlServerServiceAccountPassword	The SQL server service account password
$SqlAuthPassword	The SQL server auth account password
$DomainName	The FQDN of the AD domain created
$AdminUsername	The name of the administrator of the new VMs and domain
$SqlServerServiceAccountUserName	The SQL server service account name
$CustomTemplateJSON	The full path to the directory containing the azuredeploy.json file
$DNSSuffix	The DNS Suffix for reverse lookup of public IP addresses
$ResourceGroupName	The name of the resource group to deploy the template within
$SqlServerOffer	The SQL server version
$SqlServerSKU	The name of the SQL server SKU
$ArmDeploymentName	The name of the template deploment in Azure Resource Manager (ARM)

# Declare login variables
$ClientID = "
00000000-0000-0000-0000-000000000000
"
$ClientSecret = "
ftE2u]iVLs_J4+i-:q^Ltf4!&{!w3-%=3%4+}F2jk]=
"

$TenantID = "
contoso.onmicrosoft.com
"
$ArmEndpoint = "
https://management.frn00006.azure.ukcloud.com
"
$AzureStackEnvironment = "AzureStackUser"

# Declare variables for the template
$AdminPassword = "
Password123!
"
$AdminPasswordCred = ConvertTo-SecureString -String $AdminPassword -AsPlainText -Force

$SqlServerServiceAccountPassword = "
Password123!
"
$SqlServerServiceAccountPasswordCred = ConvertTo-SecureString -String $SqlServerServiceAccountPassword -AsPlainText -Force

$SqlAuthPassword = "
Password123!
"
$SqlAuthPasswordCred = ConvertTo-SecureString -String $SqlAuthPassword -AsPlainText -Force

$DomainName = "
fabrikam.local
"
$AdminUsername = "
localadmin
"
$SqlServerServiceAccountUserName = "
sqlservice
"

$CustomTemplateJSON = "
$Env:USERPROFILE\sql-2016-alwayson
"

$DNSSuffix = "
azure.ukcloud.com
"
$ResourceGroupName = "
Sql2016AlwaysOnRG01
"
$Location = (Get-AzLocation).Location

$SqlServerOffer = "
SQL2016SP2-WS2016
"
$SqlServerSKU = "
Enterprise
"
$ArmDeploymentName = "
Sql2016AlwaysOnDeployment
"

# Create Azure Stack Hub environment so that you can log in to it
Add-AzEnvironment -Name $AzureStackEnvironment -ArmEndpoint $ArmEndpoint

# Create your SPN credentials for log in
$AzsUsername = $ClientID
$AzsUserPassword = ConvertTo-SecureString -String $ClientSecret -AsPlainText -Force
$AzsCred = New-Object -TypeName System.Management.Automation.PSCredential -ArgumentList $AzsUsername, $AzsUserPassword

# Log in to Azure Stack Hub using SPN account
Connect-AzAccount -EnvironmentName $AzureStackEnvironment -Credential $AzsCred -ServicePrincipal -TenantId $TenantID

# Create a new resource group if it does not exist
try {
    $RG = Get-AzResourceGroup -Name $ResourceGroupName -Location $Location -ErrorAction 'SilentlyContinue'
    if (-not $RG) {
        Write-Output -InputObject "Could not find the resource group, creating now..."
        New-AzResourceGroup -Name $ResourceGroupName -Location $Location -Verbose
    }
    else {
        Write-Output -InputObject "The resource group: $ResourceGroupName exists."
    }
}
catch {
    Write-Output -InputObject "Could not query the resource group: $ResourceGroupName"
    Write-Error -Message "$($_.Exception.Message)"
    exit
}

# Test deployment
Test-AzResourceGroupDeployment -ResourceGroupName $ResourceGroupName -TemplateFile $CustomTemplateJSON -DnsSuffix $DNSSuffix -AdminPassword $AdminPasswordCred -SqlServerServiceAccountPassword $SqlServerServiceAccountPasswordCred -SqlAuthPassword $SqlAuthPasswordCred -DomainName $DomainName -AdminUsername $AdminUsername -SqlServerServiceAccountUserName $SqlServerServiceAccountUserName -SqlServerOffer $SqlServerOffer -SqlServerSku $SqlServerSKU -Verbose

# Start deployment
New-AzResourceGroupDeployment -Name $ArmDeploymentName -ResourceGroupName $ResourceGroupName -TemplateFile $CustomTemplateJSON -DnsSuffix $DNSSuffix -AdminPassword $AdminPasswordCred -SqlServerServiceAccountPassword $SqlServerServiceAccountPasswordCred -SqlAuthPassword $SqlAuthPasswordCred -DomainName $DomainName -AdminUsername $AdminUsername -SqlServerServiceAccountUserName $SqlServerServiceAccountUserName -SqlServerOffer $SqlServerOffer -SqlServerSku $SqlServerSKU -Verbose

# Verify deployment
## Note: $ArmDeploymentName can be changed to query each deployment in your resource group
Get-AzResourceGroupDeployment -Name $ARMDeploymentName -ResourceGroupName $ResourceGroupName

In the Azure Stack Hub portal, you will see the deployment progressing and resources being created:

Under Settings, click Deployments and you can see the stages of the ARM template deployment:

Tip

Every parameter in the parameter list can be defined in the New-AzResourceGroupDeployment by simply adding -<ParameterName>

For example: -WitnessVMSize "Standard_D1_v2"

Note

If the template fails validation, PowerShell will output the reason for the failure (see below):

Test-AzResourceGroupDeployment -ResourceGroupName $ResourceGroupName -TemplateFile $CustomTemplateJSON -dnsSuffix $DnsSuffix -AdminPassword $AdminPasswordCred -SqlServerServiceAccountPassword $SqlServerServiceAccountPasswordCred -SqlAuthPassword $SqlAuthPasswordCred -DomainName $DomainName -AdminUsername $AdminUsername -SqlServerServiceAccountUserName $SqlServerServiceAccountUserName -SqlServerOffer $SqlServerOffer -SqlServerSku $SqlServerSKU -WitnessVMSize "Standard_232" -Verbose

Code    : InvalidTemplate
Message : Deployment template validation failed: 'The provided value 'Standard_232' for the template parameter 'witnessVMSize' at line '39' and column '26' is not valid. The parameter value is not part of the allowed value(s): 'Standard_D1_v2,Standard_D2_v2'.'.
Details :

Known issues

• Sometimes the Domain Account does not get correctly created and you will get the following error:

  "statusMessage":
  "{\"status\":\"Failed\",\"error\":{\"code\":\"ResourceDeploymentFailure\",\"message\":\"The
  resource operation completed with terminal provisioning state 'Failed'.\",\"details\":{\"code\":\"VMExtensionProvisioningError\",\"message\":\"VM
  has reported a failure when processing extension 'sqlAOPrepare'. Error
  message: DSC Configuration 'PrepareAlwaysOnSqlServer' completed with error(s). Following are the first few: FindDomainForAccount: Call to DsGetDcNameWithAccountW failed with return value0x0000054B Could not find account SQL-AYQE0\\r\\n The PowerShell DSC resource '[xSqlServerConfigureSqlServerWithAlwaysOn' with SourceInfo'C:\\\\Packages\\\\Plugins\\\\Microsoft.Powershell.DSC\\\\2.76.00\\\\DSCWork\\\\PrepareAlwaysOnSqlServer.ps1.0\\\\PrepareAlwaysOnSqlServerps1::205::9::xSqlServer'
  threw one or more non-terminating errors while running the Set-TargetResource functionality.These errors are logged to the ETW channel called Microsoft-Windows-DSC/Operational. Refer to this channel for more details.\"}]}}"
  

  If that happens, you can just redeploy and it should be fine.

Troubleshooting DSC extensions

• PowerShell DSC Extension

  • C:\WindowsAzure\Logs\Plugins\Microsoft.Powershell.DSC\<version number>

• DSC Configuration and cmdlets

  • To view the status of the DSC deployment run:

    Get-AzVMDscExtension -ResourceGroupName "<ResourceGroupName>" -VMName "<VMName>" -Name "<ExtensionName>"
    Get-AzVMDscExtensionStatus -ResourceGroupName "<ResourceGroupName>" -VMName "<VMName>" -Name "<ExtensionName>" | Select-Object -ExpandProperty DscConfigurationLog
    

• Event Viewer Logs

  • Errors are located in: Application And Service Logs / Microsoft / Windows / Desired State Configuration

Feedback

If you find a problem with this article, click Improve this Doc to make the change yourself or raise an issue in GitHub. If you have an idea for how we could improve any of our services, send an email to feedback@ukcloud.com.