Forge Home

sqlserveralwayson

Microsoft SQL Server puppet module with AlwaysOn clustering features

6,333 downloads

6,333 latest version

2.9 quality score

We run a couple of automated
scans to help you access a
module's quality. Each module is
given a score based on how well
the author has formatted their
code and documentation and
modules are also checked for
malware using VirusTotal.

Please note, the information below
is for guidance only and neither of
these methods should be considered
an endorsement by Puppet.

Version information

  • 2.0.0 (latest)
released Mar 4th 2018
This version is compatible with:
  • Puppet Enterprise 2018.1.x, 2017.3.x, 2017.2.x, 2017.1.x, 2016.5.x, 2016.4.x
  • Puppet >= 3.8.0 < 6.0.0

Start using this module

  • r10k or Code Manager
  • Bolt
  • Manual installation
  • Direct download

Add this module to your Puppetfile:

mod 'virtualdesktopdevops-sqlserveralwayson', '2.0.0'
Learn more about managing modules with a Puppetfile

Add this module to your Bolt project:

bolt module add virtualdesktopdevops-sqlserveralwayson
Learn more about using this module with an existing project

Manually install this module globally with Puppet module tool:

puppet module install virtualdesktopdevops-sqlserveralwayson --version 2.0.0

Direct download is not typically how you would use a Puppet module to manage your infrastructure, but you may want to download the module in order to inspect the code.

Download

Documentation

virtualdesktopdevops/sqlserveralwayson — version 2.0.0 Mar 4th 2018

sqlserveralwayson

This modules installs 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.

Integration informations

The default MSSQLSERVER SQL Server instance is created during installation. This module does not provide the capability to create other SQL instances.

The database failover mecanism integrated in this module is SQL Server AlwaysOn.

The module can be installed on a Standard, Datacenter, Core version of Windows 2012R2 or Windows 2016.

BREAKING CHANGE : This module requires puppetlabs/dsc compiled with SQLServerDSC >= 10.0.0.0

Usage

  • setup_svc_username : (string) Privileged account used by Puppet for installing the software and creating the failover cluster (spn creation, computer registration, local administrator privilèges needed)
  • setup_svc_password : (string) Password of the privileged account. Should be encrypted with hiera-eyaml.
  • setupdir : (string) Path of a folder containing the SQL Server installer (unarchive the ISO image in this folder).
  • sa_password : (string) SQL Server SA password for mixed mode SQL authentication configuration.
  • productkey : (string)(optionnal) Product key for licensed installations.
  • sqlservicecredential_username : (String) Domain service account for the SQL service WITHOUT Netbios Domain Name prefix. The account will be automatically created in Active Directory by the module. MSSQLSvc/fqdn_of_sql_server_node SPN will be associated with the service account.
  • sqlservicecredential_password : (String) : Password of the service account for the SQL service. Should be encrypted with hiera-eyaml.
  • sqlagentservicecredential_username : (String) Domain service account for the SQL Agent service WITHOUT Netbios Domain Name prefix. The account will be automatically created in Active Directory by the module.
  • sqlagentservicecredential_password : (String) Password of the service account for the SQL Agent service. Should be encrypted with hiera-eyaml.
  • sqladministratoraccounts : (String[] Array) : Array of accounts to be made SQL administrators.
  • sqluserdbdir : (String)(optionnal) Path for SQL database files. Default to 'C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\Data'
  • sqluserdblogdir : (String)(optionnal) Path for SQL log files. Default to 'C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\Data'
  • sqlbackupdir : (String)(optionnal) Path for SQL backup files. Default to 'C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\Backup'
  • sqltempdbdir : (String)(optionnal) Path for SQL TempDB files. Default to 'C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\Data'
  • sqltempdblogdir : (String)(optionnal) Path for SQL TempDB log files. Default to 'C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\Data'
  • clusterName : (String) Failover cluster name.
  • clusterIP : (String) Failover cluster IP address.
  • fileShareWitness : (String) Fileshare witness UNC path in the format'\witness.company.local\witness$'. Needs to be writable by SQL nodes.
  • listenerIP : (String) The IP address used for the availability group listener, in the format 192.168.10.45/255.255.252.0.
  • role : (String) Needs to be 'primary' for primary SQL nodes or 'secondary' for SQL replica nodes

Installing a Microsoft SQL Server AlwaysOn cluster

The following example creates a 2 nodes SQL Server Always On Availability group :

  • SQL Server is installed on both nodes using the privileged DOMAIN-TEST\svc-puppet account.
  • SQL Server service and agent are configured to run using the DOMAIN-TEST\svc-sql-puppet service account.
  • Mixed mode logon is configured with the required "SA password" used to recover SQL Server access in case of windows authentication service failure
  • Windows Failover Cluster named CLDB01 is created and configured with the \192.168.1.10\quorum file share witness
  • Always On Availability group is created including endpoints and CLDB01LI listener (IP address : 192.168.1.61). The listener name is derived from the failover cluster name by the module

The replica node is installed with the same parameters and joined to the CLDB01 windows failover cluster and to the Avalability Group. Notice the role => 'secondary' which defines the role of the node.

Sample architecture :

Sample SQL Server Always On architecture

Sample Puppet code :

#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'
    }
}

#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'
    }
}