insentia / Windows_sql

windows_sql

This is the windows_sql puppet module.

##Description

This module allows you to generate and install SQL Server 2012 on windows Server. You can also configure maintenance plan.

This module have been tested with SQL Server 2012 SP1 on Windows Server 2012 R2. Should work, on Windows Server since 2012 and with SQL Server since 2012.

This module have been tested with puppet open source v3.5.1 and v3.6.2, the puppetmaster version is v3.4.3 (on ubuntu 14.04 LTS). Should work since version 3.5.1 of puppet

Last Fix/Update

v 0.0.9 :

• Pull request 4 from StefanSchoof/master
• Add pid when use master mode. (powershell)
• Fix execution of installation script when using master mode

Generate configurationfile.ini

Generate Microsoft SQL Server 2012 file confsiguration from parameters you define. If you create your service account with windows_ad module and his xml file before calling the windows_sql class the password for each service account will be automatically retrieve from the users.xml file, else you can provide manually your password. All password manually provided have priority on xml password.

The user domain doesn't need to be provided, it will be supplied by the $env:userdomain powershell variable for each service account variable (like AGTSVCACCOUNT) and SQLSYSADMINACCOUNTS

Setup Requirements

Depends on the following modules:

• 'puppetlabs/powershell', '>=1.0.2',
• 'puppetlabs/stdlib', '>= 4.2.1'.

• Optional
  • 'jriviere/windows_isos', '>= 0.0.4' (if you supply $isopath variable),
  • 'jriviere/windows_ad', '>= 0.1.2'] (fill password automatically for each account specified with this module and with his xml file)

Example

class {'windows_sql':
  features            => 'SQL,RS_SHP,RS_SHPWFE,TOOLS',
  pid                 => 'SYOUR-PRODU-CTKEY-OFSQL-2012S',
  sqlsysadminaccounts => 'SQLAdmin',
  agtsvcaccount       => 'svc_sqlagt',
  isopath             => 'C:\\Users\\Administrator\\Desktop\\SQLServer2012SP1-FullSlipstream-ENU-x64.iso',
  sqlsvcaccount       => 'svc_sqlsvc',
  securitymode        => 'sql',
  sapwd               => '[email protected]@ssw0rDO3nOT',
  mode                => 'master',
}

Parameters

Complete list and parameter options can be found on MSDN

SQL
	action              # Default Install. For now only Install, Uninstall action works
	features            # list of feature that should be install
	pid                 # product key. Default its empty.
	agtsvcaccount       # svc account for sql agt
	agtsvcpassword      # his password
	agtsvcstartuptype   # Startup. Default Automatic
	sqlpath             # install dir of SQL
	isopath             # iso path of SQL iso. Need [jriviere/windows_isos](https://forge.puppetlabs.com/jriviere/windows_isos) puppet module
	securitymode        # if SQL is supply sapwd is mandatory
	sapwd               # local admin of sql
	instancename        # instancename. Default MSSQLSERVER
	instanceid          # instanceid
	instancedir         # instancedir
	sqltempdbdir        # Default empty so, will use the default path of provide by sql
	sqltempdblogdir     # Default empty so, will use the default path of provide by sql
	sqluserdbdir        # Default empty so, will use the default path of provide by sql
	sqluserdblogdir     # Default empty so, will use the default path of provide by sql
	
	rssvcaccount        # When you provide one of this, specific options will be available.
	issvcaccount        # This 3, have same default account parameters as agtsvcpassword.
	assvcaccount        # If their features are provide and you don't provide them account, an error will occur.


Common parameter (default value)
	action                       # Install
	enu                          # true
	quiet                        # false
	quietsimple                  # true
	uimode                       # empty
	help                         # false
	indicateprogress             # false
	x86                          # false
	sqmreporting                 # false
	errorreporting               # false
	$sqlcollation                # Latin1_General_CI_AS
	tcpenabled                   # 1
	npenabled                    # 0
	browsersvcstartuptype        # Disabled
	iacceptsqlserverlicenseterms # true
	enableranu                   # false
	filestreamlevel              # 0
	forcerestart                 # Default : true
 

Other parameter
	configurationfile  # path of configurationfile.ini. Default C:\\configurationfile.ini
	userxml            # path of users.xml file created by [jriviere/windows_ad](https://forge.puppetlabs.com/jriviere/windows_ad) module. Default C:\\users.xml
	isopath            # Full iso path
	sqlpath            # for Drive use E:\\, for directory use C:\SQL where SQL is the folder that contains setup.exe
	mode               # the way how you want to deploy SQL, by using a master or only a agent. Default value agent.

##Plan Backup JOB Resource: windows_sql::maintenanceplan::backup

	windows_sql::maintenance::backup{'full':
	  backuptype   => 'FULL',
	  databases    => 'USER_DATABASES',
	  servername   => 'srvsql01',
	  jobname      => 'Full Backup - USER_Databases',
	  scheduletime => '021900',
	  schedulename => 'Daily Backup - Backup',
	  freq_type    => 'Daily'
	}
	windows_sql::maintenance::integritycheck{'integrity':
	  noindex      => 'N',
	  databases    => 'USER_DATABASES',
	  servername   => 'srvsql01',
	  jobname      => 'Check Integrity - USER_DATABASES',
	  scheduletime => '021100',
	  schedulename => 'Daily Backup - Integrity',
	  freq_type    => 'Daily'
	}
	windows_sql::maintenance::indexoptimize{'indexop':
	  databases    => 'USER_DATABASES',
	  servername   => 'srvsql01',
	  jobname      => 'Index Optimize - USER_DATABASES',
	  scheduletime => '023000',
	  schedulename => 'Daily Backup - Index Optimize',
	  freq_type    => 'Daily'
	}
	windows_sql::maintenance::deletebackuphistory{'delete':
	  servername   => 'srvsql01',
	  jobname      => 'Delete Backup History',
	  scheduletime => '030000',
	  schedulename => 'Daily Backup - Delete History',
	  freq_type    => 'Daily'
	}

Parameters:

	$databases           # Database to maintain
	$directory           # Directory to save backup
	$backuptype          # FULL, LOG, DIFF. Default FULL
	$verify              # 'N' or 'Y'
	$compress            # 'Y' or 'N'
	$servername          # 
	$fragmentationlow    # 'NULL',
	$fragmentationmedium # 'INDEX_REORGANIZE,INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE',
	$fragmentationhigh   # 'INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE',
	$fragmentationlevel1 # 5,
	$fragmentationlevel2 # 30,
	$noindex             # 'N' or 'Y', don't create index ?
	$checkcommands       # List of Available commands on ola hallengren site
	$scriptpath          # Where to save the maintenance script. Default C:\\Puppet-SQL
	$jobname             # Job Name
	$scheduletime        # Time to execute ex : 020000 -> exec at 02am
	$schedulename        # Scheduled name
	$freq_type           # Can be once, daily, Weekly, Monthly
	$freq_freq_interval  # Can be empty or any days of the week
	$daystokeep          # Number of days to keep backup. Default 7 (one week).

Known issues

If you declare the users with windows_ad::users in the same manifest of windows_sql Class, a error will occur and will inform you that you haven't provide a password. You need to declare your users first and then declare your SQL class (users must exist in AD before calling SQL class).

License

Apache License, Version 2.0

Contact

Jerome RIVIERE

Support

Please log tickets and issues on GitHub