mssql

Table of Contents

1. Description
2. Setup - The basics of getting started with mssql
   • Setup requirements
   • Beginning with mssql
3. Usage - Configuration options and additional functionality
4. Changelog - what's new
5. 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

1. 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

2. Define requirements in hiera. Please see examples:

3. 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: '[email protected]'

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 is 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