mssql

Puppet module to install and manage Microsoft SQL Server

Project URL RSS Feed Report issues

Module Stats

7,298 downloads

1,813 latest version

5.0 quality score

Version information

released Sep 17th 2020

This version is compatible with:

Puppet Enterprise 2019.8.x, 2019.7.x, 2019.5.x, 2019.4.x, 2019.3.x, 2019.2.x, 2019.1.x, 2019.0.x, 2018.1.x, 2017.3.x, 2017.2.x, 2017.1.x, 2016.5.x, 2016.4.x
Puppet >= 4.7.0 < 7.0.0

Start using this module

Installation method

Add this module to your Puppetfile:

mod 'umaritimus-mssql', '0.9.12'

Learn more about managing modules with a Puppetfile

Add this module to your Bolt project:

bolt module add umaritimus-mssql

Learn more about using this module with an existing project

Manually install this module globally with Puppet module tool:

puppet module install umaritimus-mssql --version 0.9.12

Documentation

umaritimus/mssql — version 0.9.12 Sep 17th 2020

mssql

Description
Setup - The basics of getting started with mssql
- Setup requirements
- Beginning with mssql
Usage - Configuration options and additional functionality
Changelog - what's new
Limitations - OS compatibility, etc.

Description

Puppet module to install and manage Microsoft SQL Server.

Setup

Setup Requirements

This module requires the following modules to be present:

puppetlabs/stdlib
puppetlabs/powershell
puppetlabs/pwshlib
puppetlabs/dsc

Beginning with mssql module

Deploy required puppet modules to your PUPPET_CODE_DIR

${PuppetCodedir} = 'c:\ProgramData\PuppetLabs\code\environments'
puppet.bat module install puppetlabs-stdlib --modulepath ${PuppetCodedir}\production\modules --force
puppet.bat module install puppetlabs-powershell --modulepath ${PuppetCodedir}\production\modules --force
puppet.bat module install puppetlabs-pwshlib --modulepath ${PuppetCodedir}\production\modules --force
puppet.bat module install puppetlabs-dsc --modulepath ${PuppetCodedir}\production\modules --force
puppet.bat module install umaritimus-mssql --modulepath ${PuppetCodedir}\production\modules --force

Define requirements in hiera. Please see examples:
Run puppet.bat apply ...

Usage

Example ( Installing SQL Server for a PeopleSoft DPK):

Note: This implementation utilizes Powershell DSC, which makes it very easy to extend and maintain the module. However, due to the nature of SQL Server installation parameters, the installation itself may demand some experimentation with parameter dependencies. The following example illustrates the choice of settings that I utilize for PeopleSoft DPK. The installation parameters within mssql.server.instance hash reflect the dsc equivalents in MSFT_SqlSetup MOF.


---
psadmin:
  name: 'PeopleSoft Administrator'
  user: 'domain\psadmin'
  password: 'TheD0mainPasswrod4psadminUser!'
  email: 'psadmin@domain.com'

mssql:
  client:
    odbc:
      drivers:
        'ODBC Driver 17 for SQL Server' :
          ensure: 'present'
          source: '//share/software/msodbcsql_17.6.1.1_x64.msi'
          options:
            - 'ADDLOCAL': 'All'
            - 'IACCEPTMSODBCSQLLICENSETERMS': 'YES'
      datasources:
        'CSTST':
          platform: '64-bit'
          dsntype: 'System'
          drivername:  'ODBC Driver 17 for SQL Server'
          propertyvalue:
            - "database=CSTST"
            - "server=PSCSTSTDB01"
            - "trusted_connection=Yes"
    cli:
      'Microsoft Command Line Utilities 15 for SQL Server':
        ensure: 'present'
        source: '//share/software/MsSqlCmdLnUtils_15.0.2000.5_x64.msi'
        options:
          - 'ADDLOCAL': 'All'
          - 'IACCEPTMSSQLCMDLNUTILSLICENSETERMS': 'YES'
  server:
    ensure: 'present'
    source:
      install: '//share/software/SQLServer2019-x64-ENU-Enterprise'
      update : '//share/software/SQLServer2019-KB4563110-x64'
    instance:
      action: 'Install'
      features: 'SQLENGINE'
      instancedir: 'D:\\microsoft'
      instancename: 'MSSQLSERVER'
      sqlcollation: 'Latin1_General_BIN2'
      securitymode: 'SQL'
      sapwd:
        user: 'sa'
        password: "%{::random_password}"
      psdscrunascredential:
        user: "%{lookup('psadmin.user')}"
        password: "%{lookup('psadmin.password')}"
      sqlsysadminaccounts:
        - "%{lookup('psadmin.user')}"
        - 'NT AUTHORITY\SYSTEM'
      agtsvcstartuptype: 'Automatic'
      forcereboot: 'False'
      suppressreboot: 'True'
      browsersvcstartuptype: 'Disabled'
    configuration:
      - 'backup compression default' : 1
      - 'cost threshold for parallelism': 60
      - 'contained database authentication': 1
      - 'Database Mail XPs': 1
    login:
      'sa' :
        logintype: 'SqlLogin'
        disabled: 'True'
      'people' :
        logintype: 'SqlLogin'
        logincredential:
          user: 'people'
          password: 'peop1e'
    maxdop : 2
    tcpport: '1433'
    memory:
      'minmemory': 8192
      'maxmemory': 8192
    firewall:
      allow_remoteaddress:
        - '10.10.10.0/24'
      allow_localport:
        - '1433'
        - '5022'
    email:
      accountname: "%{lookup('psadmin.name')}"
      profilename: "%{lookup('psadmin.name')}"
      address: "%{lookup('psadmin.email')}"
      replytoaddress: "%{lookup('psadmin.email')}"
      displayname: "%{lookup('psadmin.name')}"
      servername: "smtp.domain.com"
      description:  "%{lookup('psadmin.name')}"
      logginglevel: 'Normal'
      tcpport: 25
    security:
      credentials:
        "%{lookup('psadmin.user')}":
          user: "%{lookup('psadmin.user')}"
          password: "%{lookup('psadmin.password')}"
    linkedservers:
      'HRDB':
        server:    "%{lookup('hr_database_server')}"
        database:  "HRTST"
        login:     "%{lookup('hr_linked_username')}"
        password:  "%{lookup('hr_linked_password')}"
      'FSDB':
        server:    "%{lookup('fs_database_server')}"
        database:  "FSTST"
        login:     "%{lookup('fs_linked_username')}"
        password:  "%{lookup('fs_linked_password')}"
    sqlagent:
      properties:
        'IsCpuPollingEnabled':
          name: 'IsCpuPollingEnabled'
          value: 'True'
        'MaximumHistoryRows':
          name: 'MaximumHistoryRows'
          value: 10000
        'MaximumJobHistoryRows':
          name: 'MaximumJobHistoryRows'
          value: 1000
      operators:
        "%{lookup('psadmin.name')}":
          name: "%{lookup('psadmin.name')}"
          email: "%{lookup('psadmin.email')}"
      alerts:
        '017':
          name: '017 - Insufficient Resources'
          severity: '17'
          notify: "%{lookup('psadmin.name')}"
        '018' :
          name: '018 - Nonfatal Internal Error'
          severity: '18'
          notify: "%{lookup('psadmin.name')}"
        '019':
          name: '019 - Fatal Error in Resource'
          severity: '19'
          notify: "%{lookup('psadmin.name')}"
        '020' :
          name: '020 - Fatal Error in Current Process'
          severity: '20'
          notify: "%{lookup('psadmin.name')}"
        '021':
          name: '021 - Fatal Error in Database Processes'
          severity: '21'
          notify: "%{lookup('psadmin.name')}"
        '022' :
          name: '022 - Fatal Error: Table Integrity Suspect'
          severity: '22'
          notify: "%{lookup('psadmin.name')}"
        '023':
          name: '023 - Fatal Error: Database Integrity Suspect'
          severity: '23'
          notify: "%{lookup('psadmin.name')}"
        '024' :
          name: '024 - Fatal Error: Hardware Error'
          severity: '24'
          notify: "%{lookup('psadmin.name')}"
        '025':
          name: '025 - Fatal Error'
          severity: '25'
          notify: "%{lookup('psadmin.name')}"
      proxies:
        "%{lookup('psadmin.user')}":
          - 'Powershell'
          - 'CmdExec'
    startupparameters:
      'Trace Flag 834':
        value: '-T834'
        ensure: 'present'
      'Trace Flag 1222':
        value: '-T1222'
        ensure: 'present'
      'Trace Flag 3023':
        value: '-T3023'
        ensure: 'present'

Note: The ensure='absent' functionality is presently not implemented within DSC... probably because the only feasible way to completely remove Sql Server is to utilize a pack of plastic explosives. In the meantime, our uninstallation is implemented using the native setup command. Just simply toggle the mssql.server.ensure: to 'absent' and rerun the include ::mssql::server.

puppet apply -e "include ::mssql::server"

The output should look similar to:

Notice: Compiled catalog for demo.domain.com in environment production in 1.84 seconds
Notice: Processing mssql::server
Notice: /Stage[main]/Mssql::Server/Notify[Processing mssql::server]/message: defined 'message' as 'Processing mssql::server'
Notice: Processing mssql::server::install
Notice: /Stage[main]/Mssql::Server::Install/Notify[Processing mssql::server::install]/message: defined 'message' as 'Processing mssql::server::install'
Notice: /Stage[main]/Mssql::Server::Install/Dsc_userrightsassignment[Grant PerformVolumeMaintenanceTasks to sqlsvcaccountusername]/ensure: created
Notice: /Stage[main]/Mssql::Server::Install/Dsc_userrightsassignment[Grant LockPagesInMemory to sqlsvcaccountusername]/ensure: created
Notice: /Stage[main]/Mssql::Server::Install/Dsc_sqlsetup[Install SQL Server]/ensure: created
Notice: Processing mssql::server::update
Notice: /Stage[main]/Mssql::Server::Update/Notify[Processing mssql::server::update]/message: defined 'message' as 'Processing mssql::server::update'
Notice: /Stage[main]/Mssql::Server::Update/Exec[Apply SQL Server Cumulative Update]/returns: [output redacted]
Notice: /Stage[main]/Mssql::Server::Update/Exec[Apply SQL Server Cumulative Update]/returns: executed successfully
Notice: Processing mssql::server::config
Notice: /Stage[main]/Mssql::Server::Config/Notify[Processing mssql::server::config]/message: defined 'message' as 'Processing mssql::server::config'
Notice: /Stage[main]/Mssql::Server::Config/Dsc_sqlserverlogin[sa]/ensure: created
Notice: /Stage[main]/Mssql::Server::Config/Dsc_sqlserverlogin[people]/ensure: created
Notice: /Stage[main]/Mssql::Server::Config/Dsc_sqlserverconfiguration[backup compression default]/ensure: created
Notice: /Stage[main]/Mssql::Server::Config/Dsc_sqlserverconfiguration[cost threshold for parallelism]/ensure: created
Notice: /Stage[main]/Mssql::Server::Config/Dsc_sqlserverconfiguration[contained database authentication]/ensure: createdNotice: /Stage[main]/Mssql::Server::Config/Dsc_sqlserverconfiguration[Database Mail XPs]/ensure: created
Notice: /Stage[main]/Mssql::Server::Config/Dsc_sqlserverdatabasemail[Enable Database Mail]/ensure: created
Notice: /Stage[main]/Mssql::Server::Config/Dsc_sqlservermaxdop[Set MAXDOP to 2]/ensure: created
Notice: /Stage[main]/Mssql::Server::Config/Dsc_sqlservermemory[Set Sql Server Memory to 8192]/ensure: created
Notice: /Stage[main]/Mssql::Server::Config/Notify[Processing mssql::server::config]/message: defined 'message' as 'Processing mssql::server::config'
Notice: /Stage[main]/Mssql::Server::Config/Dsc_firewall[Create SQL Server Firewall Rule]/ensure: created
Notice: /Stage[main]/Mssql::Server::Config/Exec[Add Alert for '017 - Insufficient Resources' to 'PeopleSoft Administrator']/returns: [output redacted]
Notice: /Stage[main]/Mssql::Server::Config/Exec[Add Alert for '017 - Insufficient Resources' to 'PeopleSoft Administrator']/returns: executed successfully
Notice: /Stage[main]/Mssql::Server::Config/Exec[Add Alert for '018 - Nonfatal Internal Error' to 'PeopleSoft Administrator']/returns: [output redacted]
Notice: /Stage[main]/Mssql::Server::Config/Exec[Add Alert for '018 - Nonfatal Internal Error' to 'PeopleSoft Administrator']/returns: executed successfully
Notice: /Stage[main]/Mssql::Server::Config/Exec[Add Alert for '019 - Fatal Error in Resource' to 'PeopleSoft Administrator']/returns: [output redacted]
Notice: /Stage[main]/Mssql::Server::Config/Exec[Add Alert for '019 - Fatal Error in Resource' to 'PeopleSoft Administrator']/returns: executed successfully
Notice: /Stage[main]/Mssql::Server::Config/Exec[Add Alert for '020 - Fatal Error in Current Process' to 'PeopleSoft Administrator']/returns: [output redacted]
Notice: /Stage[main]/Mssql::Server::Config/Exec[Add Alert for '020 - Fatal Error in Current Process' to 'PeopleSoft Administrator']/returns: executed successfully
Notice: /Stage[main]/Mssql::Server::Config/Exec[Add Alert for '021 - Fatal Error in Database Processes' to 'PeopleSoft Administrator']/returns: [output redacted]
Notice: /Stage[main]/Mssql::Server::Config/Exec[Add Alert for '021 - Fatal Error in Database Processes' to 'PeopleSoft Administrator']/returns: executed successfully
Notice: /Stage[main]/Mssql::Server::Config/Exec[Add Alert for '022 - Fatal Error: Table Integrity Suspect' to 'PeopleSoft Administrator']/returns: [output redacted]
Notice: /Stage[main]/Mssql::Server::Config/Exec[Add Alert for '022 - Fatal Error: Table Integrity Suspect' to 'PeopleSoft Administrator']/returns: executed successfully
Notice: /Stage[main]/Mssql::Server::Config/Exec[Add Alert for '023 - Fatal Error: Database Integrity Suspect' to 'PeopleSoft Administrator']/returns: [output redacted]
Notice: /Stage[main]/Mssql::Server::Config/Exec[Add Alert for '023 - Fatal Error: Database Integrity Suspect' to 'PeopleSoft Administrator']/returns: executed successfully
Notice: /Stage[main]/Mssql::Server::Config/Exec[Add Alert for '024 - Fatal Error: Hardware Error' to 'PeopleSoft Administrator']/returns: [output redacted]
Notice: /Stage[main]/Mssql::Server::Config/Exec[Add Alert for '024 - Fatal Error: Hardware Error' to 'PeopleSoft Administrator']/returns: executed successfully
Notice: /Stage[main]/Mssql::Server::Config/Exec[Add Alert for '025 - Fatal Error' to 'PeopleSoft Administrator']/returns: [output redacted]
Notice: /Stage[main]/Mssql::Server::Config/Exec[Add Alert for '025 - Fatal Error' to 'PeopleSoft Administrator']/returns: executed successfully
Notice: /Stage[main]/Mssql::Server::Config/Exec[Set SQL Agent Property for 'IsCpuPollingEnabled']/returns: [output redacted]
Notice: /Stage[main]/Mssql::Server::Config/Exec[Set SQL Agent Property for 'IsCpuPollingEnabled']/returns: executed successfully
Notice: /Stage[main]/Mssql::Server::Config/Exec[Set SQL Agent Property for 'MaximumHistoryRows']/returns: [output redacted]
Notice: /Stage[main]/Mssql::Server::Config/Exec[Set SQL Agent Property for 'MaximumHistoryRows']/returns: executed successfully
Notice: /Stage[main]/Mssql::Server::Config/Exec[Set SQL Agent Property for 'MaximumJobHistoryRows']/returns: [output redacted]
Notice: /Stage[main]/Mssql::Server::Config/Exec[Set SQL Agent Property for 'MaximumJobHistoryRows']/returns: executed successfully
Notice: /Stage[main]/Mssql::Server::Config/Exec[Add Linked Server for PSHRTSTDB01]/returns: [output redacted]
Notice: /Stage[main]/Mssql::Server::Config/Exec[Add Linked Server for PSHRTSTDB01]/returns: executed successfully
Notice: /Stage[main]/Mssql::Server::Config/Exec[Add Linked Server for PSFSTSTDB01]/returns: [output redacted]
Notice: /Stage[main]/Mssql::Server::Config/Exec[Add Linked Server for PSFSTSTDB01]/returns: executed successfully
Notice: /Stage[main]/Mssql::Server::Config/Exec[Add Credential for domain\psadmin]/returns: [output redacted]
Notice: /Stage[main]/Mssql::Server::Config/Exec[Add Credential for domain\psadmin]/returns: executed successfully
Notice: /Stage[main]/Mssql::Server::Config/Exec[Register 'Powershell' subsystem to 'domain\psadmin' proxy account]/returns: [output redacted]
Notice: /Stage[main]/Mssql::Server::Config/Exec[Register 'Powershell' subsystem to 'domain\psadmin' proxy account]/returns: executed successfully
Notice: /Stage[main]/Mssql::Server::Config/Exec[Register 'CmdExec' subsystem to 'domain\psadmin' proxy account]/returns: [output redacted]
Notice: /Stage[main]/Mssql::Server::Config/Exec[Register 'CmdExec' subsystem to 'domain\psadmin' proxy account]/returns: executed successfully
Notice: /Stage[main]/Mssql::Server::Config/Exec[Ensure Startup Parameter 'Trace Flag 834' is 'present']/returns: [output redacted]
Notice: /Stage[main]/Mssql::Server::Config/Exec[Ensure Startup Parameter 'Trace Flag 834' is 'present']/returns: executed successfully
Notice: /Stage[main]/Mssql::Server::Config/Exec[Ensure Startup Parameter 'Trace Flag 1222' is 'present']/returns: [output redacted]
Notice: /Stage[main]/Mssql::Server::Config/Exec[Ensure Startup Parameter 'Trace Flag 1222' is 'present']/returns: executed successfully
Notice: /Stage[main]/Mssql::Server::Config/Exec[Ensure Startup Parameter 'Trace Flag 3023' is 'present']/returns: [output redacted]
Notice: /Stage[main]/Mssql::Server::Config/Exec[Ensure Startup Parameter 'Trace Flag 3023' is 'present']/returns: executed successfully
Notice: Applied catalog in 304.53 seconds

Example ( Installing OdbcDriver, when used as defined type ):

puppet apply -e "mssql::client::odbc::driver { 'ODBC Driver 17 for SQL Server' : ensure => 'present', driver => 'ODBC Driver 17 for SQL Server', source => 'c:/temp/msodbcsql_17.3.1.1_x64.msi',  }"

Note: The OdbcDriver name needs to be exact, as defined by Microsoft. If you don't specify the correct name, problems will follow you around, e.g. during DSN creation... Also, Microsoft ODBC Driver 17 for SQL Server has the prerequisite of Visual Studio Redistributable Package, so ensure that it's already installed, e.g.
package { 'Visual Studio Redistributable Package' :
  ensure          => installed,
  source          => 'c:/temp/vc_redist.x64.exe',
  install_options => [ '/quiet'],
}

package { 'Visual Studio Redistributable Package' : ensure => installed, source => lookup('vc_redist_location'), install_options => [ '/quiet'], }

Example ( Installing sqlcmd )

Note: Microsoft Command Line Utilities 15 for SQL Server package has a prerequisite of Microsoft ODBC Driver and a modern .NET libraries to be already installed

puppet apply -e "mssql::client::cli::sqlcmd { 'Add sqlcmd' : package => 'Microsoft Command Line Utilities 15 for SQL Server', ensure => 'present', source => 'c:/temp/MsSqlCmdLnUtils.msi', }"

Example ( Uninstalling sqlcmd )

puppet apply -e "mssql::client::cli::sqlcmd { 'Remove sqlcmd' : package => 'Microsoft Command Line Utilities 15 for SQL Server', ensure => 'absent', source => 'c:/temp/MsSqlCmdLnUtils.msi', }"

Changelog

For updates please see the changelog

Limitations

Currently this module only works on Microsoft Windows platform.
It has been tested with Microsoft SQL Server 2017 and Microsoft SQL Server 2019
Module assumes 1 SQL Server instance per machine, because that the best practice
Only ODBC Driver 13 for SQL Server and ODBC Driver 17 for SQL Server are supported as SQL Server ODBC drivers
Linked servers are only implemented for SQL Servers
FCI and AG high availability configurations have not been fully implemented or tested.

Development

Use Pull Requests to contribute code, please! Please see description of how this was developed

Changelog

All notable changes to this project will be documented in this file.

Release 0.9.12

Added

SQL Client enhancements

Release 0.9.10

Added

SQL Agent properties configuration

Release 0.9.9

Added

Required user rights assignments for SQL Server and SQL Server Agent accounts
Configuration of Startup Parameters

Release 0.9.8

Fixed

Documentation fixups

Release 0.9.7

Added

Administration of Linked Servers
Administration of Credentials and Proxies
SQL Agent Operators
SQL Agent Alerts

Release 0.9.6

Fixed

Update dependency definitions
Resolved issues

Release 0.9.5

Changed

Improved documentation
Fixed parameter hierarchy

Release 0.9.1

Added

Condition to indicate that the module only works on Windows

Release 0.9.0

Changed

Major rewrite of the installation routine using powershell dsc module instead of the setup script.

Release 0.5.0

Added

Dependency on puppetlabs-dsc module for sql server configuration

Release 0.4.2

Fixed

Conditional source locations and improved error handling

Added

Placeholder for config class

Release 0.4.1

Added

Capability to apply a cumulative patch

Release 0.4.0

Fixed

PDK 1.18 version dependency
inifile module dependency

Added

unless clause in sql server installation

Release 0.3.2

Fixed

Minor documentation grammar fixups

Release 0.3.1

Fixed

Links to CHANGELOG and CONTRIBUTING
Minor documentation clarifications

Release 0.3.0

Added

Installation of Microsoft SQL Server

Release 0.2.1

Fixed

Instructions for installing SQL Server Command Line Utilities were not clear enough

Release 0.2.0

Added

Installation of SQL Server Command Line Utilities
Changelog references

Fixed

SQL Server ODBC Driver defaults that fixes expects a value for parameter error in case module-level default values are not specified

Release 0.1.0

Added

Installation of SQL Server ODBC Driver

Modules

Discover

Contribute

Resources

About Forge

Getting Started

mssql

Version information

This version is compatible with:

Start using this module

Documentation

mssql

Table of Contents

Description

Setup

Setup Requirements

Beginning with mssql module

Usage

Example ( Installing SQL Server for a PeopleSoft DPK):

Example ( Installing OdbcDriver, when used as defined type ):

Example ( Installing sqlcmd )

Example ( Uninstalling sqlcmd )

Changelog

Limitations

Development

Changelog

Release 0.9.12

Release 0.9.10

Release 0.9.9

Release 0.9.8

Release 0.9.7

Release 0.9.6

Release 0.9.5

Release 0.9.1

Release 0.9.0

Release 0.5.0

Release 0.4.2

Release 0.4.1

Release 0.4.0

Release 0.3.2

Release 0.3.1

Release 0.3.0

Release 0.2.1

Release 0.2.0

Release 0.1.0

Dependencies