This template uses the PowerShell DSC extension to deploy a fully configured Always On Availability Group with SQL Server replicas.
This template creates the following resources:
- 1 storage account for the diagnostics
- 1 internal load balancer
- 1 availability set for SQL Server and Witness virtual machines
- 3 virtual machines in a Windows Server Cluster
- 2 SQL Server edition replicas with an availability group
- 1 virtual machine is a File Share Witness for the Cluster
Original code modified from SQL VM Alwayson Cluster (https://github.com/Azure/azure-quickstart-templates/tree/master/sqlvm-alwayson-cluster>
The following security controls can be met through configuration of this template:
- Information at Rest: SC-28, SC-28 (1).
- SQL Transation Backup: CP-10 (2).
To fully meet Information at Rest controls you should run Disk Encryption post install.
The deployment assumes the following items are already deployed:
module "sql-server-cluster" {
source = "github.com/canada-ca-terraform-modules/terraform-azurerm-sql-server-cluster?ref=20200813.1"
resource_group_name = "pws3-test-sql-cluster-rg"
keyVaultConfig = {
existingRGName = "PwS3-GCPS-CRM-KeyVault-RG"
existingVaultName = "PwS3-CRM-Keyvault"
localAdminPasswordSecret = "server2016DefaultPassword"
domainAdminPasswordSecret = "adDefaultPassword"
}
secretPasswordName = "server2016DefaultPassword"
vnetConfig = {
existingVnetName = "demo-Infra-NetShared-VNET"
existingVnetRG = "Demo-Infra-NetShared-RG"
sqlSubnet = "10.250.29.0/26"
dbSubnetName = "Demo-Shared-DB"
}
location = "canadacentral"
adminUsername = "azureadmin"
domainUsername = "azureadmin"
dnsServerName = "DemoSharedDC01"
sqlServerConfig = {
clusterIp = "169.254.1.15"
sqlLBIPAddress = "10.250.29.14"
sqlLBName = "TST-SWB"
sqlAOListenerPort = "1433"
vmSize = "Standard_DS3_v2"
vmName = "TST-SWB"
sqlServerLicenseType = "AHUB"
sqlpatchingConfig = {
patchingEnabled = true
dayOfWeek = "Sunday"
maintenanceWindowStartingHour = "2"
maintenanceWindowDuration = 60
}
sqlBackupConfig = {
backupEnabled = true
retentionPeriod = 30
enableEncryption = true
backupSystemDbs = true
backupScheduleType = "Manual"
fullBackupFrequency = "Daily"
fullBackupStartTime = 2
fullBackupWindowHours = 5
logBackupFrequency = 60
password = "Canada123!"
}
imageReference = {
sqlImagePublisher = "MicrosoftSQLServer"
offer = "SQL2016SP2-WS2016"
sku = "Enterprise"
version = "latest"
}
dataDisks = {
numberOfSqlVMDisks = "2"
diskSizeGB = "1024"
}
workloadType = "OLTP"
sqlServerServiceAccountUserName = "svc-tstsql1"
sqlStorageAccountName = "tstsqltest1stg"
storageAccountTier = "Standard"
storageAccountReplicationType = "LRS"
diagBlobEncryptionEnabled = true
sqlDatabases = "TestServer"
sqlServerServiceAccountPasswordSecret = "sqlServerServiceAccountPassword"
enableAcceleratedNetworking= true
}
witnessServerConfig = {
vmSize = "Standard_DS2_v2"
vmName = "TST-SVR"
imageReference = {
publisher = "MicrosoftWindowsServer"
offer = "WindowsServer"
sku = "2016-Datacenter"
version = "latest"
}
dataDisks = {
diskSizeGB = "128"
}
sqlStorageAccountTier = "Standard"
sqlStorageAccountReplicationType = "LRS"
enableAcceleratedNetworking = true
}
adConfig = {
"domainName": "shared.demo.ca",
"serverOUPath":"OU=Servers,OU=DemoApp,OU=Applications,OU=PSPC,DC=shared,DC=demo,DC=ca",
"accountOUPath": "OU=Service Accounts,OU=DemoApp,OU=Applications,OU=demo,DC=shared,DC=ca"
}
backupConfig = {
existingBackupVaultRG = "Demo-Shared-CRM-Backup-RG"
existingBackupVaultName = "Demo-Shared-CRM-Backup-Vault"
existingBackupPolicy = "DailyBackupPolicy"
}
tagValues = {
"workload" = "Database"
"owner" = "[email protected]"
"businessUnit" = "Unit1"
"costCenterOwner" = "EA"
"environment" = "Sandbox"
"classification" = "Unclassified"
"version" = "0.1"
}
}| Name | Type | Required | Value |
|---|---|---|---|
| containerSasToken | string | No | A SaS token for the private blob storage |
| keyVaultConfig | object | Yes | Information about the existing keyVault to useKeyVaultConfig Object |
| vnetConfig | object | Yes | Information about the existing vnet to usevnetConfig Object |
| location | string | No | The location to deploy the resources - canadacentral, canadaeast. Default is canadacentral |
| adminUserName | string | Yes | The local administrator name to use for the VM. |
| domainUserName | string | Yes | The local administrator name to use for joining the domain and creating the service accounts. |
| dnsServerName | string | Yes | The existing DNS Server name. |
| sqlServerConfig | object | Yes | The SQL Server configuration options for the primary and secondary server- sqlServerConfig object |
| witnessServerConfig | object | Yes | The SQL witness configuration options - witnessServerConfig object. |
| adConfig | string | object | The Active Directory configuration. - adConfig object |
| backupConfig | object | Yes | The backup configuration. backupConfig Object |
| tagValues | object | No | The tags to set for the deployment. - tagValues object |
| Name | Type | Required | Value |
|---|---|---|---|
| existingRGName | string | Yes | The name of the existing keyvault resource group |
| existingVaultName | string | Yes | The name of the existing keyvault to use |
| localAdminPasswordSecret | string | Yes | The name of the secret where the password is stored for local admin password |
| domainAdminPasswordSecret | string | Yes | The name of the secret where the password is stored for a domain account that can be used to create service accounts and to join the domain |
| Name | Type | Required | Value |
|---|---|---|---|
| existingVnetName | string | Yes | The name of the existing virtual network where sql will reside |
| existingVnetRG | string | Yes | The name of the existing virtual network resource group |
| sqlSubnet | string | Yes | The subnet address range for where SQL will reside |
| dbSubnetName | string | Yes | The name of the subnet where SQL will reside |
| Name | Type | Required | Value |
|---|---|---|---|
| ClusterIp | string | Yes | The IP to use for the SQL cluster. |
| sqlLBIPAddress | string | Yes | The IP to use for the SQL load balencer |
| sqlLBName | string | Yes | The name to use for the SQL load balencer |
| sqlAOListenerPort | string | Yes | The port for the alwayson listener |
| deploymentPrefix | string | Yes | The deployment prefix to use for the naming standard of the objects. |
| vmSize | enum | Yes | Specifies the size of the virtual machine. For more information about virtual machine sizes, see Sizes for virtual machines. |
| vmName" | string | Yes | The name of the VM to use |
| patchingConfig | object | Yes | The patching settings for the VM - patchingConfig oject |
| sqlBackupConfig | object | Yes | The backup settings for the VM - sqlBackupConfig oject |
| imageReference | object | Yes | The image settings for the VM - imageReference object |
| dataDisks | object | Yes | The data disk settings for the VM - dataDisks object |
| workloadType | enum | Yes | The workload type for SQL - GENERAL, OLTP, DW |
| sqlServerServiceAccountUserName | string | Yes | The name to use for the SQL service account |
| sqlStorageAccountName | string | Yes | The name of the storage account for SQL |
| sqlStorageAccountType | enum | Yes | The storage type to use for the disks - Standard_LRS, Premium_LRS, StandardSSD_LRS, UltraSSD_LRS |
| sqlDatabases | string | Yes | The name of the first database to create with always on |
| sqlServerServiceAccountPassword | string | Yes | The name of the keyvault secret where service account password is stored |
| enableAcceleratedNetworking | bool | Yes | Indicates if to use accelerated networking or not. |
| Name | Type | Required | Value |
|---|---|---|---|
| vmSize | enum | Yes | Specifies the size of the virtual machine. For more information about virtual machine sizes, see Sizes for virtual machines. |
| vmName | string | Yes | The name of the SQL cluster. |
| imageReference | object | Yes | The image settings for the VM - imageReference object |
| dataDisks | object | Yes | The data disk settings for the VM - dataDisks object |
| sqlStorageAccountType | enum | Yes | The type of storage to use. - Standard_LRS, Standard_GRS, Standard_RAGRS, Standard_ZRS, Premium_LRS, Premium_ZRS |
| enableAcceleratedNetworking | bool | Yes | Indicates if to use accelerated networking or not. |
| Name | Type | Required | Value |
|---|---|---|---|
| domainName | string | Yes | The domain to join the servers to. |
| serverOUPath | string | Yes | The OU Path to join the servers to. |
| accountOUPath | string | Yes | The OU Path to create the service accounts. |
| Name | Type | Required | Value |
|---|---|---|---|
| existingBackupVaultRG | string | Yes | The name of the existing backup vault resource group. |
| existingBackupVaultName | string | Yes | The name of the existing backup vault. |
| existingBackupPolicy | string | Yes | The name of the existing backup policy to use. |
| Name | Type | Required | Value |
|---|---|---|---|
| tagname1 | string | No | tag1 value |
| ... | ... | ... | ... |
| tagnameX | string | No | tagX value |
| Name | Type | Required | Value |
|---|---|---|---|
| autoPatchingEnabled | bool | Yes | Indicates if auto patching should be enabled |
| autoPatchingDay | string | Yes | The day of the week to do the patching |
| autoPatchingStartHour | string | Yes | The hour to start the patching |
| autoPatchingMainenanceWindowDuration | int | Yes | The maintenance window duration in minutes |
| autoUpgradeMinorVersion | bool | Yes | Indicates if to apply minor updates |
| Name | Type | Required | Value |
|---|---|---|---|
| backupEnabled | bool | Yes | Indicates if backup should be enabled on the VM |
| RetentionPeriod | int | Yes | Specifies the retention period of the encryption |
| EnableEncryption | bool | Yes | Indicates if to enable encryption or not |
| Name | Type | Required | Value |
|---|---|---|---|
| sqlImagePublisher | string | Yes | The name of the image publisher |
| offer | string | Yes | The SQL image to use |
| sku | enum | Yes | The SQL sku to use - Enterprise,Express, SQLDEV, Standard, Web |
| version | string | Yes | The sql template version to use. Use "latest" for the most current |
| Name | Type | Required | Value |
|---|---|---|---|
| numberOfSqlVMDisks | int | Yes | The number of data disks to create |
| diskSizeGB | string | Yes | The size of the disk in GB |
*File Share Witness and SQL Server VMs are from the same Availability Set and currently there is a constraint for mixing DS-Series machine, DS_v2-Series machine and GS-Series machine into the same Availability Set. If you decide to have DS-Series SQL Server VMs you must also have a DS-Series File Share Witness; If you decide to have GS-Series SQL Server VMs you must also have a GS-Series File Share Witness; If you decide to have DS_v2-Series SQL Server VMs you must also have a DS_v2-Series File Share Witness.
- In default settings for compute require that you have at least 15 cores of free quota to deploy.
*This has been tested with the following skus SQL2016SP2-WS2016 and SQL2017-WS2016.
For a list of images run the following in Powershell:
Get-AzureRMVMImageOffer -Location "canadacentral" -Publisher "MicrosoftSqlServer" | Select OfferFor a list of image skus run the following in Powershell:
Get-AzureRmVMImageSku -Location "canadacentral"-Publisher "MicrosoftSQLServer" -Offer "SQL2016SP2-WS2016" | Select Skus- Option for moving the cluster to an Azure Blob
- Integrate the keystore for the server certificates
- Research having cluster communication on seperate private network (best practice)
- Modify template to use servers templates
| Date | Change |
|---|---|
| 2019-01-22 | Modified template to use existing network instead of creating a new one. |
| Modified template to use existing Active Directory instead of creating a new one. | |
| Added keyvault integration. | |
| Switched storage to managed disks. | |
| Removed Public IP's. | |
| Added backup and antimalare extensions at post deploy. | |
| Added retry loop to start availablity listener in CreateFailOvercluster DSC. | |
| Updated DSC packages for xSQL and xComputerManagement. | |
| Added code in DSC files to join servers at a passed in OU path. | |
| Added code to DSC to add the cluster permisions at the OU Path so Availability Lister could auto join | |
| 2019-05-08 | Updated documentation and switch to new sql-server type. |
| 2019-05-15 | Made container Sas token optional |
| Added support for naming the sql LB | |
| 2020-08-13 | Fixed folder structure |