Pin it
3.5 1 1 1 1 1 1 1 1 1 1 Rating 3.50 (1 Vote)

The foundation of a successfull Citrix XenDesktop deployment is a highly available database with stable and reliable performances. This modules install a fully working Microsoft SQL Server AlwaysOn cluster. 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 module requires puppetlabs/dsc compiled with SQLServerDSC >= 10.0.0.0 .

Microsoft SQL Server deployment automation with puppet and powershell dsc

Module usage

Installing a Microsoft SQL Server AlwaysOn cluster

The role => 'primary' defines that this node is the first node of the cluster. Active Directory service account, Microsoft Failover cluster, Always On availability group and listener are created during installation.

#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

The role => 'secondary' defines that this node has to join existing failover cluster and 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

serviceaccounts.pp - Creating SQL service accounts

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.

#Needed for ActiveDirectory remote management using Powershell
	dsc_windowsfeature{ 'RSAT-AD-Powershell':
	 dsc_ensure => 'Present',
	 dsc_name => 'RSAT-AD-Powershell'
	}
	
	#SQL service account creation (Active Directory)
	dsc_xaduser{'SvcSQLAccount':
		dsc_domainname => $domainName,
		dsc_domainadministratorcredential => {'user' => $setup_svc_username, 'password' => $setup_svc_password},
		dsc_username => $sqlservicecredential_username,
		dsc_password => {'user' => $sqlservicecredential_username, 'password' => $sqlservicecredential_password},
		dsc_ensure => 'Present',
		require => Dsc_windowsfeature['RSAT-AD-Powershell']
	}
	
	#Configure MSSQLSvc SPN on SQL service account
  dsc_xadserviceprincipalname{'SvcSQLSPN':
    dsc_account => $sqlservicecredential_username,
    dsc_serviceprincipalname => "MSSQLSvc/${fqdn}",
    dsc_ensure => present,
    dsc_psdscrunascredential => {'user' => $setup_svc_username, 'password' => $setup_svc_password},
    require => Dsc_xaduser['SvcSQLAccount']
  }
	
	#SQL Agent service account creation (Active Directory)
  dsc_xaduser{'SvcSQLAgentAccount':
    dsc_domainname => $domainName,
    dsc_domainadministratorcredential => {'user' => $setup_svc_username, 'password' => $setup_svc_password},
    dsc_username => $sqlagentservicecredential_username,
    dsc_password => {'user' => $sqlagentservicecredential_username, 'password' => $sqlagentservicecredential_password},
    dsc_ensure => 'Present',
    require => Dsc_windowsfeature['RSAT-AD-Powershell']
  }

install.pp - Installing SQL server with Puppet & DSC

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

The xSQLServerSetup 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.

dsc_windowsfeature{'NET-Framework-Core':
	  dsc_ensure => 'Present',
	  dsc_name   => 'NET-Framework-Core',
	  dsc_includeallsubfeature => true
	}
	
	dsc_windowsfeature{'NET-Framework-45-Core':
	  dsc_ensure => 'Present',
	  dsc_name   => 'NET-Framework-45-Core',
	  dsc_includeallsubfeature => true
	}
	
	dsc_windowsfeature{'RSAT-AD-PowerShell':
    dsc_ensure => 'Present',
    dsc_name   => 'RSAT-AD-PowerShell'
  }

   dsc_windowsfeature{'Failover-Clustering':
    dsc_ensure => 'Present',
    dsc_name   => 'Failover-Clustering'
  }
  
  dsc_windowsfeature{'RSATClusteringPowerShell':
    dsc_ensure => 'Present',
    dsc_name   => 'RSAT-Clustering-PowerShell',
    require => [ Dsc_windowsfeature['Failover-Clustering'] ]
  }
  
  #Not working on Windows Server Core edition
  #dsc_windowsfeature{'RSATClusteringMgmt':
  #  dsc_ensure => 'Present',
  #  dsc_name   => 'RSAT-Clustering-Mgmt',
  #  require => [ Dsc_windowsfeature['Failover-Clustering'] ]
  #}
  
  dsc_windowsfeature{'RSATClusteringCmdInterface':
    dsc_ensure => 'Present',
    dsc_name   => 'RSAT-Clustering-CmdInterface',
    require => [ Dsc_windowsfeature['RSATClusteringPowerShell'] ]
  }
	
	dsc_xsqlserversetup{ 'InstallSQLDefaultInstance':
	    dsc_action => 'Install',
      dsc_instancename => 'MSSQLSERVER',
      #dsc_failoverclusternetworkname => $clusterFQDN,
      #dsc_failoverclusteripaddress => $clusterIP,
      dsc_features => 'SQLENGINE,AS',
      dsc_sqlcollation => 'SQL_Latin1_General_CP1_CI_AS',
      dsc_securitymode => 'SQL',
      dsc_sapwd => {'user' => 'sa', 'password' => $sa_password},
      dsc_productkey => $productkey,
      dsc_sqlsvcaccount => {'user' => $sqlservicecredential_username, 'password' => $sqlservicecredential_password},
      dsc_agtsvcaccount => {'user' => $sqlagentservicecredential_username, 'password' => $sqlagentservicecredential_password},
      dsc_assvcaccount => {'user' => $sqlservicecredential_username, 'password' => $sqlservicecredential_password},
      dsc_sqlsysadminaccounts => $sqladministratoraccounts,
      dsc_assysadminaccounts  => $sqladministratoraccounts,
      dsc_installshareddir => 'C:\Program Files\Microsoft SQL Server',
      dsc_installsharedwowdir => 'C:\Program Files (x86)\Microsoft SQL Server',
      dsc_instancedir => 'C:\Program Files\Microsoft SQL Server',
      dsc_installsqldatadir => 'C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\Data',
      dsc_sqluserdbdir => $sqluserdbdir,
      dsc_sqluserdblogdir => $sqluserdblogdir,
      dsc_sqltempdbdir => $sqltempdbdir,
      dsc_sqltempdblogdir => $sqltempdblogdir,
      dsc_sqlbackupdir => $sqlbackupdir,
      dsc_asconfigdir => 'c:\MSOLAP\Config',
      dsc_asdatadir => 'c:\MSOLAP\Data',
      dsc_aslogdir => 'c:\MSOLAP\Log',
      dsc_asbackupdir => 'c:\MSOLAP\Backup',
      dsc_astempdir => 'c:\MSOLAP\Temp',
      dsc_sourcepath => $setupdir,
      dsc_updateenabled => 'False',
      dsc_forcereboot => true,
      dsc_psdscrunascredential => {'user' => $setup_svc_username, 'password' => $setup_svc_password},
      require => [ Dsc_windowsfeature['NET-Framework-Core'], Dsc_windowsfeature['NET-Framework-45-Core'],  Dsc_windowsfeature['Failover-Clustering'] ],
      notify => Reboot['after_run']
  }
	
	reboot { 'after_run':
	  apply => finished,
	}

config.pp - Configure SQL server settings

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 xSQLServer powershell dsc module, hiding firewall rules creation complexity.

#Network configuration
	dsc_xsqlservernetwork{ 'ConfigureSQLNetwork':
	  dsc_instancename => 'MSSQLSERVER',
	  dsc_protocolname => "tcp",
	  dsc_isenabled => true,
	  dsc_tcpport => '1433',
	  dsc_restartservice => true
	}
	
	#Windows Firewall configuration
	dsc_xsqlserverfirewall{'CreateFirewallRules':        
	  dsc_ensure => 'Present',
	  dsc_features => 'SQLENGINE,AS',
	  dsc_instancename => 'MSSQLSERVER',
	  dsc_sourcepath => $setupdir,
	  dsc_psdscrunascredential => {'user' => $setup_svc_username, 'password' => $setup_svc_password}
	}
	
	#Disable UAC
	#dsc_xuac{'UACNeverNotifyAndDisableAll':     
    #  dsc_setting => 'NeverNotifyAndDisableAll'
    #}
	
	#Admin access configuration
	dsc_xsqlserverlogin{'DomainAdminsLogin':
	  dsc_ensure => 'Present',
	  dsc_sqlserver => $hostname,
	  dsc_sqlinstancename => 'MSSQLSERVER',
	  dsc_name => "${domainNetbiosName}\\Domain Admins",
	  dsc_logintype => 'WindowsGroup',
	  dsc_psdscrunascredential => {'user' => $setup_svc_username, 'password' => $setup_svc_password}
	}
	
	dsc_xsqlserverrole{'AddDomainAdminsSQLSysadmin':
	  dsc_ensure => 'Present',
	  dsc_serverrolename => 'sysadmin',
	  dsc_memberstoinclude => "${domainNetbiosName}\\Domain Admins",
	  dsc_sqlserver => $hostname,
	  dsc_sqlinstancename => 'MSSQLSERVER',
	  require => Dsc_xsqlserverlogin['DomainAdminsLogin'],
	  dsc_psdscrunascredential => {'user' => $setup_svc_username, 'password' => $setup_svc_password}
	}
	
	#Service account access configuration. Mandatory for AlwaysOn replica login capability on HADR server endpoint
  dsc_xsqlserverlogin{'ServiceAccountLogin':
    dsc_ensure => 'Present',
    dsc_sqlserver => $hostname,
    dsc_sqlinstancename => 'MSSQLSERVER',
    dsc_name => $sqlservicecredential_username,
    dsc_logintype => 'WindowsUser',
    dsc_psdscrunascredential => {'user' => $setup_svc_username, 'password' => $setup_svc_password}
  }
	
	#User rights configuration
	dsc_userrightsassignment{ 'PerformVolumeMaintenanceTasks':
	  dsc_policy => 'Perform_volume_maintenance_tasks',
	  dsc_identity => 'Builtin\Administrators'
	}

	 dsc_userrightsassignment{ 'LockPagesInMemory':
	  dsc_policy => 'Lock_pages_in_memory',
	  dsc_identity => 'Builtin\Administrators'
	}
  
    #Performances configuration
	dsc_xsqlservermaxdop{ 'SetMAXDOP':
	  dsc_sqlserver => 'localhost',
	  dsc_sqlinstancename => 'MSSQLSERVER',
	  dsc_maxdop => 0
	}
	
	#xSQLServerMemory SetMAXDOP
	#{
	#SQLInstanceName = $Configuration.InstallSQL.InstanceName
	#DependsOn = "[xSqlServerSetup]InstallSQL"
	#MaxMemory = $MAXMemory
	#DynamicAlloc = $False
	#}

clusterconfig.pp - Creating the Microsoft failover cluster

Windows Server Failover Clustering (WSFC) cluster is a requirement for deploying AlwaysOn Availability Groups : to be able to create an 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). 

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.

if ( $role == 'primary' ) {
		#Failover cluster creation
		dsc_xcluster{'CreateFailoverCluster':
			dsc_name => $clusterName,
			dsc_staticipaddress => $clusterIP,
			dsc_domainadministratorcredential => {'user' => $setup_svc_username, 'password' => $setup_svc_password}
		}
		
		#File share whitness configuration
		#Warning, bug https://github.com/PowerShell/xFailOverCluster/issues/35 on Windows 2016
		dsc_xclusterquorum{'SetQuorumToNodeAndDiskMajority':
			dsc_issingleinstance => 'Yes',
			dsc_type => 'NodeAndFileShareMajority',
			dsc_resource => $fileShareWitness,
			require => Dsc_xcluster['CreateFailoverCluster']
     }
		
	}
	else {
	  dsc_xwaitforcluster{'SecondaryReplicaWaitForCluster':
	    dsc_name => $clusterName,
	    dsc_retryintervalsec => 10,
      dsc_retrycount => 6
	  }
	  
	  dsc_xcluster{'JoinCluster':
	    dsc_name => $clusterName,
      dsc_staticipaddress => $clusterIP,
      dsc_domainadministratorcredential => {'user' => $setup_svc_username, 'password' => $setup_svc_password},
      require => Dsc_xwaitforcluster['SecondaryReplicaWaitForCluster']
    }
	}

alwaysonconfig.pp - Initialize the SQL Availability Group

The final step of this setup is to initialize the SQL Availability Group on top of the previously created Windows Failover Cluster. This puppet manifest uses xSQLServer powershell module to configure SQL server to allow the cluster service to access the server. It then creates the SQL server endpoints, the availability group, and the availability group listener which are all three mandatory for an up and running SQL Server AlwaysOn cluster and to enable replication between nodes.

#Enable AlwaysOn on MSSQL service
  dsc_xsqlserveralwaysonservice{'EnableAlwaysOn':
    dsc_ensure => 'Present',
    dsc_sqlserver => $hostname,
    dsc_sqlinstancename => 'MSSQLSERVER',
    dsc_restarttimeout => 15,
    dsc_psdscrunascredential => {'user' => $setup_svc_username, 'password' => $setup_svc_password}
  }->
  
  # Adding the required service account to allow the cluster to log into SQL
  dsc_xsqlserverlogin{'AddNTServiceClusSvc':
    dsc_ensure => 'Present',
    dsc_name => 'NT SERVICE\ClusSvc',
    dsc_logintype => 'WindowsUser',
    dsc_sqlserver => $hostname,
    dsc_sqlinstancename => 'MSSQLSERVER',
    dsc_psdscrunascredential => {'user' => $setup_svc_username, 'password' => $setup_svc_password}
  }->

  # Add the required permissions to the cluster service login
  dsc_xsqlserverpermission{'AddNTServiceClusSvcPermissions':
    dsc_ensure => 'Present',
    dsc_nodename => $hostname,
    dsc_instancename => 'MSSQLSERVER',
    dsc_principal => 'NT SERVICE\ClusSvc',
    dsc_permission => ['AlterAnyAvailabilityGroup', 'ViewServerState'],
    dsc_psdscrunascredential => {'user' => $setup_svc_username, 'password' => $setup_svc_password}
  }->

  dsc_xsqlserverendpoint{'SQLServerEndpoint':
    dsc_endpointname => 'HADR',
    dsc_ensure => 'Present',
    dsc_port => '5022',
    dsc_sqlserver => $fqdn,
    dsc_sqlinstancename => 'MSSQLSERVER',
    dsc_psdscrunascredential => {'user' => $setup_svc_username, 'password' => $setup_svc_password}
  }
  
  dsc_xsqlserverendpointpermission{'SQLConfigureEndpointPermission':
		dsc_ensure => 'Present',
		dsc_nodename => $hostname,
		dsc_instancename => 'MSSQLSERVER',
		dsc_name => 'HADR',
		dsc_principal => $sqlservicecredential_username,
		dsc_permission => 'CONNECT',
		dsc_psdscrunascredential => {'user' => $setup_svc_username, 'password' => $setup_svc_password}
  }
  
  if ( $role == 'primary' ) {
     # Create the availability group on the instance tagged as the primary replica
    dsc_xsqlserveralwaysonavailabilitygroup{'CreateSQLAvailabilityGroup':
      dsc_ensure => 'Present',
      dsc_name => $clusterName,
      dsc_sqlserver => $hostname,
      dsc_sqlinstancename => 'MSSQLSERVER',
      dsc_psdscrunascredential => {'user' => $setup_svc_username, 'password' => $setup_svc_password},
      require => [ Dsc_xsqlserveralwaysonservice['EnableAlwaysOn'] , Dsc_xsqlserverendpoint['SQLServerEndpoint'] ]
    }
    
    dsc_xsqlserveravailabilitygrouplistener{'AvailabilityGroupListener':
			dsc_ensure => 'Present',
			dsc_nodename => $fqdn,
			dsc_instancename => 'MSSQLSERVER',
			dsc_availabilitygroup => $clusterName,
			dsc_name => "${clusterName}LI",
			dsc_ipaddress => $listenerIP,
			dsc_port => 1433,
			dsc_psdscrunascredential => {'user' => $setup_svc_username, 'password' => $setup_svc_password},
      require => [ Dsc_xsqlserveralwaysonavailabilitygroup['CreateSQLAvailabilityGroup'] ]
    }
    
  }
  else {
        
    dsc_xsqlserveralwaysonavailabilitygroupreplica{'SQLAvailabilityGroupAddReplica':
			dsc_ensure => 'Present',
			dsc_name => $hostname,
			dsc_availabilitygroupname => $clusterName,
			dsc_sqlserver => $hostname,
			dsc_sqlinstancename => 'MSSQLSERVER',
			dsc_primaryreplicasqlserver => $clusterName,
			dsc_primaryreplicasqlinstancename => 'MSSQLSERVER',
			dsc_endpointhostname => $hostname,
			dsc_psdscrunascredential => {'user' => $setup_svc_username, 'password' => $setup_svc_password},
      require => [ Dsc_xsqlserveralwaysonservice['EnableAlwaysOn'] , Dsc_xsqlserverendpoint['SQLServerEndpoint'] ]
    }
  }