Skip to content

Latest commit

 

History

History
316 lines (266 loc) · 24.3 KB

readme.md

File metadata and controls

316 lines (266 loc) · 24.3 KB
Raw

High Availability SQL Always-On Cluster

Introduction

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>

Security Controls

The following security controls can be met through configuration of this template:

To fully meet Information at Rest controls you should run Disk Encryption post install.

Dependancies

The deployment assumes the following items are already deployed:

Usage

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"
    }
}

Parameter Values

Main Template

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

KeyVaultConfig 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

vnetConfig object

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

sqlServerConfig object

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.

witnessServerConfig object

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.

adConfig object

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.

backupConfig object

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.

tagValues object

Name Type Required Value
tagname1 string No tag1 value
... ... ... ...
tagnameX string No tagX value

patchingConfig Object

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

sqlBackupConfig object

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

imageReference object

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

dataDisks object

Name Type Required Value
numberOfSqlVMDisks int Yes The number of data disks to create
diskSizeGB string Yes The size of the disk in GB

Additional Notes

*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 Offer

For a list of image skus run the following in Powershell:

Get-AzureRmVMImageSku -Location "canadacentral"-Publisher "MicrosoftSQLServer" -Offer "SQL2016SP2-WS2016" | Select Skus

Future Enhancements

  • 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

History

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