Setup SQL Server AlwaysOn cluster with Puppet & Powershell DSC

The foundation of a successfull Citrix XenDesktop deployment is a highly available database with stable and reliable performances. The virtualdesktopdevops/sqlserveralwayson Puppet & Powershell DSC module setup a fully working Microsoft SQL Server cluster with AlwaysOn high-availability features.

It has been designed to install both primary replica nodes with the following features :

  • SPN creation on sql service account (service account not yet created by this module, schedulded in next release)
  • SQL server installation and initial configuration (MaxDop Firewall, Memory, Admin rights, …)
  • Failover cluster creation (primary node) or join (replica node) with File Share witness
  • AlwaysOn configuration (availability group, server endpoints, availability group listener) on both primary and replica nodes.

This Puppet module requires puppetlabs/dsc compiled with the SQLServerDSC >= 10.0.0.0 Powershell DSC module.

Microsoft SQL Server deployment automation with puppet and powershell dsc

How to setup SQL server with sqlserveralwayson Puppet & Powershell DSC module ?

Installing the primary node of the Microsoft SQL Server AlwaysOn cluster

The role => ‘primary’ defines that the SQL server node is the first node of the cluster. Active Directory service account, Microsoft Failover cluster, Always On availability group and listener are created by Powershell DSC during installation.

SQL server setup is automatically done durting this sequence. SQL server deployment sucess can be verified using SSMS console.

#Primary node
node 'SQL01' {
    class{'sqlserveralwayson':
      setup_svc_username=>'DOMAIN-TEST\svc-puppet',
      setup_svc_password=>'P@ssw0rd',
      setupdir=>'\\fileserver.local\SQLServer2012.en',
      sa_password=>'P@ssw0rd',
      productkey => 'key-key-key',
      sqlservicecredential_username => 'svc-sql-puppet',
      sqlservicecredential_password=>'P@ssw0rd',
      sqlagentservicecredential_username => 'svc-sql-puppet',
      sqlagentservicecredential_password => 'P@ssw0rd',
      sqladministratoraccounts => [ 'DOMAIN-TEST\svc-puppet', 'DOMAIN-TEST\Administrator' ],
      clusterName => 'CLDB01',
      clusterIP => '192.168.1.60',
      fileShareWitness=> '\\192.168.1.10\quorum',
      listenerIP => '192.168.1.61/255.255.255.0',
      role => 'primary'
    }
}

Installing a replica node and adding it to

The role => ‘secondary’ defines that this node has to join existing failover cluster and SQL server availability group. Make sure that the sqlservicecredential_username and sqlagentservicecredential_username are the same on each nodes of the cluster. Same requirement for clusterName, clsterIP, listenerIP.

#Replica node
node 'SQL02' {
    class{'sqlserveralwayson':
      setup_svc_username=>'DOMAIN-TEST\svc-puppet',
      setup_svc_password=>'P@ssw0rd',
      setupdir=>'\\fileserver.local\SQLServer2012.en',
      sa_password=>'P@ssw0rd',
      productkey => 'key-key-key',
      sqlservicecredential_username => 'svc-sql-puppet',
      sqlservicecredential_password=>'P@ssw0rd',
      sqlagentservicecredential_username => 'svc-sql-puppet',
      sqlagentservicecredential_password => 'P@ssw0rd',
      sqladministratoraccounts => [ 'DOMAIN-TEST\svc-puppet', 'DOMAIN-TEST\Administrator' ],
      clusterName => 'CLDB01',
      clusterIP => '192.168.1.60',
      fileShareWitness=> '\\192.168.1.10\quorum',
      listenerIP => '192.168.1.61/255.255.255.0',
      role => 'secondary'
    }
}

Module architecture

The module is composed of 5 main parts :

  • serviceaccounts.pp creates the SQL server and the SQL Agent service account and creates the required SPN in Active Directory.
  • install.pp install all the windows features, including clustering features and management tools, as well as SQL Server.
  • config.pp configures SQL local server basic settings such as windows firewall, SQL admin rights, MaxDOP, …
  • clusterconfig.pp creates the windows failover cluster and liks it to a file share witness for quorum
  • alwaysonconfig.pp deploys SQL server endpoints, configures permissions and initialize the SQL Availability Group and the associated listener

Creating SQL service accounts with Puppet & Powershell DSC : serviceaccounts.pp

This puppet manifest creates the SQL Service and SQL Agent service accounts in ActiveDirectory using Claudiospizzi xActiveDirectory Powershell DSC module fork. This fork integrates service principal name (SPN) creation which is mandatory in an AlwaysOn cluster setup. Claudiospizzi proposed this new ressources for integration in the official xActiveDirectory powershell DSC module supported by Microsoft.

Installing SQL server with Puppet & Powershell DSC : install.pp

Prequisties such as .Net Framework Core, .Net Framework 4.5, and window failover clustering are installed using the WindowsFeature Powershell DSC ressource.

The SQLServerDSC Powershell DSC ressource handles SQL server installation. The SQL server will run under a service account identity shared between the two members of the alwayson cluster. This configuration makes the configuration uniform on both nodes, enhances security, and eases permission management for database replication.

Configure SQL Server settings with Puppet & Powershell DSC : config.pp

After SQL server initial setup, this puppet manifest configures networking, admin permissions, user rights assignment, and performance variables such as MaxDOP. It also configures windows firewall to allow remote access to SQL Server.

Firewall configuration is made easy as it has been integrated in SQLServerDSC Powershell DSC module, hiding firewall rules creation complexity.

Creating the Microsoft Windows Failover Cluster with Puppet & Powershell DSC : clusterconfig.pp

Windows Server Failover Clustering (WSFC) cluster is a requirement for deploying Microsoft SQL Server AlwaysOn Availability Groups : to be able to create a Microsoft SQL Server AlwaysOn Availability Groups, an instance of SQL Server must reside on an up and running failover cluster node (Microsoft failover cluster and node must be online).

Microsoft SQL Server AlwaysOn Availability Groups relies on the Windows Failover Clustering (WSFC) cluster to monitor and manage the current roles of the availability replicas that belong to a given availability group and to determine how a failover event affects the availability replicas. A WSFC resource group is created for every availability group that you create. The WSFC cluster monitors this resource group to evaluate the health of the primary replica.

This puppet manifest uses the xCluster Powershell DSC resource to setup the failover cluster with a file share witness quorum. On a secondary node, the node is configured to join the already existing window failover cluster.

Initialize the Microsoft SQL Server Availability Group with Puppet & Powershell DSC : alwaysonconfig.pp

The final step of this setup is to initialize the Microsoft SQL Server Availability Group on top of the previously created Windows Failover Cluster.

This Puppet manifest uses SQLServerDSC Powershell DSC module to configure SQL server to allow the cluster service to access the server. It then creates the Microsoft SQL Server server endpoints, the availability group, and the availability group listener which are all three mandatory for an up and running Microsoft SQL Server AlwaysOn cluster and to enable replication between nodes.