ADLAB PowerShell source file: buildup-sql.ps1

(C) Ondrej Sevecek, 2019 - www.sevecek.com, ondrej@sevecek.com



#$global:outClass = 'main'

$libDir = Split-Path -parent $MyInvocation.MyCommand.Definition
& "$libDir\lib-common.ps1" -defaultConfig -rootDir $libDir -outFile sqlLib
& "$libDir\lib-modifyActions.ps1"
& "$libDir\lib-buildup.ps1"

$vmName = $args[0]

DBG ("SQL Server Installation library.")
Redirect-TempToOutput
Load-VMConfig

Find-MarkedVolumes


$instanceName = $args[1]
DBG ('SQL instance to be installed: {0}' -f $instanceName)

$appTag = 'sql'
$appConfig = $vmConfig.SelectSingleNode('./sql[@instance="{0}"]' -f $instanceName)
$firstAppHost = Get-FirstAppHostInInstance $appTag $appConfig.instance 'waitParams'
$firstAppHostInInstance = Check-FirstAppHostInInstance $appTag $appConfig.instance

#====================
#====================


# Note: just to make it nicer for further operation
#       take care, this can be done only now, the previous XPATH needs case sensitivity!
$instanceName = $instanceName.ToUpper()



#====================
$sqlVersion = $appConfig.version
if (Is-EmptyString $sqlVersion) { $sqlVersion = '2012' }


#====================
DBG ('Installing SQL Server: {0}' -f $sqlVersion)

if ($appConfig.type -eq 'express') {

  $instRoot = Get-FirstPathFromWildcard (Join-Path $installMediaVolume ('SQLServer{0}-*\Express' -f $sqlVersion))
  $customRoot = Join-Path $global:rootDir ('SQLServer{0}' -f $sqlVersion)
}

else {

  DBGIF $MyInvocation.MyCommand.Name { @('full','as','tools', 'rs') -notcontains $appConfig.type }

  $instRoot = Get-FirstPathFromWildcard (Join-Path $installMediaVolume ('SQLServer{0}-*\EnterpriseEdition' -f $sqlVersion))
  $customRoot = Join-Path $global:rootDir ('SQLServer{0}' -f $sqlVersion)
}

#
#

[string] $additionalFeatures = $appConfig.features

DBG ('Are there any additional features required by any application')
$appFeatureRequirements = $xmlConfig.SelectNodes('./VMs/MACHINE[vm/@do="true"]/*/sql[translate(@instance,"ABCDEFGHIJKLMNOPQRSTUVWXYZ","abcdefghijklmnopqrstuvwxyz")="{0}" and @features]' -f $instanceName.ToLower())
DBG ('Any feature requirements: {0}' -f (Get-CountSafe $appFeatureRequirements))

if ((Get-CountSafe $appFeatureRequirements) -gt 0) {

  foreach ($oneAppFeatureRequrement in $appFeatureRequirements) {

    DBG ('One app requires the following features: {0}' -f $oneAppFeatureRequrement.features)
    $additionalFeatures = Add-MultiValue $additionalFeatures (Split-MultiValue $oneAppFeatureRequrement.features) -unique $true
  }
}


if (Is-ValidString $additionalFeatures) {

  $additionalFeatures = ',{0}' -f ((Split-MultiValue $additionalFeatures) -join ',')
}

DBG ('Additional SQL features requested: {0}' -f $additionalFeatures)

#
#

if ($appConfig.type -eq 'tools') {

  DBG ('Installing sql management studio only')
  $answerFilePath = Get-DataFileApp ('sql-answers-Tools-{0}' -f $instanceName) $null '.ini'
  $argReplacement = 'additionalFeatures${0}' -f (Escape-ForMultiValue $additionalFeatures)
  Replace-ArgumentsInFile (Join-Path $customRoot 'sql-installation-mgmtOnly.ini') $argReplacement $answerFilePath Unicode $true

} elseif ($appConfig.type -eq 'as') {

  DBG ('Installing SQL Analysis Services only')

  $asSvcNode = $appConfig.SelectSingleNode('./svc[@appTag="as" or @appTag="ppvt"]')
  $asSvcAccount = Get-SAMLogin $asSvcNode.login $asSvcNode.domain
  $asSvcPwd = $asSvcNode.pwd

  DBG ('Found SQL server Analysis Services account: {0}' -f $asSvcAccount)

  if (Is-ValidString $appConfig.app.aGroup) {

    DBG ('SQL AS (SSAS) sysadmins group specified: {0} | {1}' -f $appConfig.app.aGroup, $appConfig.app.iDomain)
    $asSysAdmins = Get-SAMLogin $appConfig.app.aGroup $appConfig.app.iDomain

  } else {

    DBG ('SQL AS (SSAS) sysadmins group NOT specified. Defaulting to local Administrators group.')
    $asSysAdmins = Get-SAMLogin '.\Administrators'
  }

  DBG ('SQL AS sysadmins: {0}' -f $asSysAdmins)

  #
  #

  DBG ('Generate the answer file for SQL AS installation: installationMode = {0}' -f $appConfig.asMode)
  DBGIF $MyInvocation.MyCommand.Name { Is-EmptyString $appConfig.asMode }
  DBGIF ('Weird SQL AS mode specified: {0}' -f $appConfig.asMode ) { @('TABULAR', 'POWERPIVOT', 'MULTIDIMENSIONAL') -notcontains $appConfig.asMode }

  $answerFilePath = Get-DataFileApp ('sql-answers-SSAS-{0}' -f $instanceName) $null '.ini'
  $argReplacement = 'instanceName${0}|dataDir${1}|svcAccount${2}|svcPwd${3}|sysAdmins${4}|additionalFeatures${5}|analysisMode${6}' -f (Escape-ForMultiValue $instanceName, (Resolve-VolumePath $appConfig.dataDir), $asSvcAccount, $asSvcPwd, $asSysAdmins, $additionalFeatures, $appConfig.asMode)
  
  DBG ('Should we install SSAS in the PowerPivot mode aka SharePoint mode: {0}' -f (Parse-BoolSafe $appConfig.powerPivot))
  
  if (Parse-BoolSafe $appConfig.powerPivot) {

    Replace-ArgumentsInFile (Join-Path $customRoot 'sql-installation-powerpivot.ini') $argReplacement $answerFilePath Unicode $true

  } else {

    Replace-ArgumentsInFile (Join-Path $customRoot 'sql-installation-ASonly.ini') $argReplacement $answerFilePath Unicode $true
  }

} elseif ($appConfig.type -eq 'rs') {

  DBG ('Installing SQL Reporting Services in SharePoint Native mode only')

  #$rsNode = $null
  #$rsNode = $appConfig.SelectSingleNode('./svc[@appTag="rs"]')
  #DBGIF $MyInvocation.MyCommand.Name { Is-Null $rsNode }

  #$rsAccount = Get-SAMLogin $rsNode.login $rsNode.domain
  #$rsPwd = $rsNode.pwd
  #DBG ('Found Reporting Services (RS) service accout: {0}' -f $rsAccount)

  DBG ('Update installation answer file .INI with Reporting Services (RS)')
  $answerFilePath = Get-DataFileApp ('sql-answers-withRS-RSsharepointMode-{0}' -f $instanceName) $null '.ini'
  #$argReplacement = 'dataDir${0}|rsAccount${1}|rsPwd${2}|additionalFeatures${3}' -f (Escape-ForMultiValue (Resolve-VolumePath $appConfig.dataDir), $rsAccount, $rsPwd, $additionalFeatures)
  $argReplacement = 'additionalFeatures${0}' -f (Escape-ForMultiValue $additionalFeatures)
  Replace-ArgumentsInFile (Join-Path $customRoot 'sql-installation-RSsharepointMode.ini') $argReplacement $answerFilePath Unicode $true

} else {

  $serverNode = $appConfig.SelectSingleNode('./svc[@appTag="s"]')
  $svcAccount = Get-SAMLogin $serverNode.login $serverNode.domain
  $svcPwd = $serverNode.pwd
  DBG ('Found SQL server service accout: {0}' -f $svcAccount)

  $agentNode = $appConfig.SelectSingleNode('./svc[@appTag="a"]')
  $agentAccount = Get-SAMLogin $agentNode.login $agentNode.domain
  $agentPwd = $agentNode.pwd
  DBG ('Found SQL Agent service account: {0}' -f $agentAccount)


  if (Is-ValidString $appConfig.app.aGroup) {

    DBG ('SQL SysAdmin group specified: {0} | {1}' -f $appConfig.app.aGroup, $appConfig.app.iDomain)
    $sysAdmins = Get-SAMLogin $appConfig.app.aGroup $appConfig.app.iDomain

  } else {

    DBG ('SQL SysAdmin group NOT specified. Defaulting to local Administrators group.')
    $sysAdmins = Get-SAMLogin '.\Administrators'
  }

  DBG ('SQL SysAdmin: {0}' -f $sysAdmins)


  $agentStartup = 'Manual'
  if (Is-ValidString $appConfig.agentStartup) {

    $agentStartup = $appConfig.agentStartup
    DBG ('SQL Agent service startup mode requested: {0}' -f $agentStartup)
  }


  DBG ('Are there any additional agent startup changes required by any application')
  $appAgentRequirements = $xmlConfig.SelectNodes('./VMs/MACHINE[vm/@do="true"]/*/sql[translate(@instance,"ABCDEFGHIJKLMNOPQRSTUVWXYZ","abcdefghijklmnopqrstuvwxyz")="{0}" and @agentStartup]' -f $instanceName.ToLower())
  DBG ('Any agent startup required: {0}' -f (Get-CountSafe $appAgentRequirements))

  [string] $finalAppAgentStartup = $null
  if ((Get-CountSafe $appAgentRequirements) -gt 0) {

    foreach ($oneAppAgentRequirement in $appAgentRequirements) {

      DBG ('One app requires the following agent startup: {0}' -f $oneAppAgentRequirement.agentStartup)
      DBGIF $MyInvocation.MyCommand.Name { (Is-ValidString $finalAppAgentStartup) -and ($finalAppAgentStartup -ne $oneAppAgentRequirement.agentStartup) }
      $finalAppAgentStartup = $oneAppAgentRequirement.agentStartup
    }
  }

  if (Is-ValidString $finalAppAgentStartup) {

    $agentStartup = $finalAppAgentStartup
    DBG ('The agent startup mode determined finally: {0}' -f $agentStartup)
  }


  $rsNode = $null
  $rsNode = $appConfig.SelectSingleNode('./svc[@appTag="rs"]')

  if (Is-NonNull $rsNode) {

    $rsAccount = Get-SAMLogin $rsNode.login $rsNode.domain
    $rsPwd = $rsNode.pwd
    DBG ('Found Reporting Services (RS) service accout: {0}' -f $rsAccount)

    DBG ('Update installation answer file .INI with Reporting Services (RS)')
    $answerFilePath = Get-DataFileApp ('sql-answers-withRS-{0}' -f $instanceName) $null '.ini'
    $argReplacement = 'instanceName${0}|dataDir${1}|svcAccount${2}|svcPwd${3}|agentAccount${4}|agentPwd${5}|rsAccount${6}|rsPwd${7}|sysAdmins${8}|additionalFeatures${9}|agentStartup${10}' -f (Escape-ForMultiValue $instanceName, (Resolve-VolumePath $appConfig.dataDir), $svcAccount, $svcPwd, $agentAccount, $agentPwd, $rsAccount, $rsPwd, $sysAdmins, $additionalFeatures, $agentStartup)
    Replace-ArgumentsInFile (Join-Path $customRoot 'sql-installation-withRS.ini') $argReplacement $answerFilePath Unicode $true
  }

  else {

    DBG ('Reporting Services (RS) should not be installed.')

    if (Is-ValidString $appConfig.userDbDir) {

      DBG ('Update installation answer file .INI without RS and with different DATA/DB/TEMP/BACKUP folders: data = {0} | backup = {1} | tempDb = {2} | tempLog = {3} | userDb = {4} | userLog = {5}' -f $appConfig.dataDir, $appConfig.backupDir, $appConfig.tempDbDir, $appConfig.tempLogDir, $appConfig.userDbDir, $appConfig.userLogDir)
      $answerFilePath = Get-DataFileApp ('sql-answers-{0}' -f $instanceName) $null '.ini'
      $argReplacement = 'instanceName${0}|dataDir${1}|svcAccount${2}|svcPwd${3}|agentAccount${4}|agentPwd${5}|sysAdmins${6}|additionalFeatures${7}|agentStartup${8}|backupDir${9}|tempDbDir${10}|tempLogDir${11}|dbDir${12}|logDir${13}' -f (Escape-ForMultiValue $instanceName, (Resolve-VolumePath $appConfig.dataDir), $svcAccount, $svcPwd, $agentAccount, $agentPwd, $sysAdmins, $additionalFeatures, $agentStartup, (Resolve-VolumePath $appConfig.backupDir), (Resolve-VolumePath $appConfig.tempDbDir), (Resolve-VolumePath $appConfig.tempLogDir), (Resolve-VolumePath $appConfig.userDbDir), (Resolve-VolumePath $appConfig.userLogDir))
      Replace-ArgumentsInFile (Join-Path $customRoot 'sql-installation-SQLdifferentDisks.ini') $argReplacement $answerFilePath Unicode $true

    } else {

      DBG ('Update installation answer file .INI without RS and a single DATADIR folder')
      $answerFilePath = Get-DataFileApp ('sql-answers-{0}' -f $instanceName) $null '.ini'
      $argReplacement = 'instanceName${0}|dataDir${1}|svcAccount${2}|svcPwd${3}|agentAccount${4}|agentPwd${5}|sysAdmins${6}|additionalFeatures${7}|agentStartup${8}' -f (Escape-ForMultiValue $instanceName, (Resolve-VolumePath $appConfig.dataDir), $svcAccount, $svcPwd, $agentAccount, $agentPwd, $sysAdmins, $additionalFeatures, $agentStartup)
      Replace-ArgumentsInFile (Join-Path $customRoot 'sql-installation.ini') $argReplacement $answerFilePath Unicode $true
    }
  }

}


#================  
DBG ('Install SQL Server by using the configuration file.')
Run-Process (Join-Path $instRoot 'setup.exe') ('/CONFIGURATIONFILE="{0}" /IACCEPTSQLSERVERLICENSETERMS' -f $answerFilePath)



if (($appConfig.type -eq 'full') -or ($appConfig.type -eq 'express') -or ($appConfig.type -eq 'as')) {

    DBG ('Get SQL Browser service executable in order to define firewall exceptions')

    $browserSvcExeStartParams = (Get-WmiQuerySingleObject '.' 'SELECT * FROM Win32_Service WHERE Name = "SQLBrowser"').PathName
    DBG ('SQL Broser service start path: {0}' -f $browserSvcExeStartParams)
    DBGIF $MyInvocation.MyCommand.Name { Is-EmptyString $browserSvcExeStartParams }
    $browserSvcExePath = Get-ExecutablePathWithoutParams $browserSvcExeStartParams -mustExist

    if (Is-ValidString $browserSvcExePath) {

      Run-Process 'NETSH' ('ADVFIREWALL FIREWALL ADD RULE Name="Sevecek: SQL Server Browser SQL (UDP 1434)" Dir=IN Action=Allow Program="{0}" Service=SQLBrowser Enable=Yes Profile=Any Protocol=UDP LocalPort=1434 InterfaceType=LAN' -f $browserSvcExePath)
      Run-Process 'NETSH' ('ADVFIREWALL FIREWALL ADD RULE Name="Sevecek: SQL Server Browser SSAS (TCP 2382)" Dir=IN Action=Allow Program="{0}" Service=SQLBrowser Enable=Yes Profile=Any Protocol=TCP LocalPort=2382 InterfaceType=LAN' -f $browserSvcExePath)

    } else {

      Run-Process 'NETSH' ('ADVFIREWALL FIREWALL ADD RULE Name="Sevecek: SQL Server Browser SQL (UDP 1434)" Dir=IN Action=Allow Service=SQLBrowser Enable=Yes Profile=Any Protocol=UDP LocalPort=1434 InterfaceType=LAN')
      Run-Process 'NETSH' ('ADVFIREWALL FIREWALL ADD RULE Name="Sevecek: SQL Server Browser SSAS (TCP 2382)" Dir=IN Action=Allow Service=SQLBrowser Enable=Yes Profile=Any Protocol=TCP LocalPort=2382 InterfaceType=LAN')
    }
}


if ($appConfig.type -eq 'as') {

    DBG ('Prepare application and service requirements for SQLAS (SSAS)')
    $appASRequirements = $xmlConfig.SelectNodes('./VMs/MACHINE[vm/@do="true"]/*/ssas[translate(@instance,"ABCDEFGHIJKLMNOPQRSTUVWXYZ","abcdefghijklmnopqrstuvwxyz")="{0}"]' -f $instanceName.ToLower())
    DBG ('Found SSAS requirements for this instance: {0}' -f (Get-CountSafe $appASRequirements))

    if ((Get-CountSafe $appASRequirements) -gt 0) {

      DBG ('Load the SSAS AMO library')
      DBGSTART
      [void] [System.Reflection.Assembly]::LoadWithPartialName('Microsoft.AnalysisServices')
      [void] [System.Reflection.Assembly]::LoadWithPartialName('Microsoft.AnalysisServices.Core')
      DBGER $MyInvocation.MyCommand.Name $error
      DBGEND

      $ssasServerName = 'data source=localhost\{0}' -f $instanceName

      DBG ('Get the SSAS server object')
      DBGSTART
      $ssasServer = $null
      $ssasServer = New-Object Microsoft.AnalysisServices.Server
      DBGER $MyInvocation.MyCommand.Name $error
      DBGEND

      DBG ('Connect the SSAS server object: {0}' -f $ssasServerName)
      DBGSTART
      $ssasServer.Connect($ssasServerName)
      DBGER $MyInvocation.MyCommand.Name $error
      DBGEND
      DBG ('Connected SSAS server: {0} | {1}' -f $ssasServer.Connected, $ssasServer.Version, $ssasServer.ServerMode)
      DBGIF $MyInvocation.MyCommand.Name { -not $ssasServer.Connected }

      if ($ssasServer.Connected) {

        DBGIF $MyInvocation.MyCommand.Name { $ssasServer.ServerMode -ne $appConfig.asMode }
        DBG ('Roles on the server: #{0}' -f (Get-CountSafe $ssasServer.Roles))
      
        DBG ('Get the Administrators role')
        DBGIF $MyInvocation.MyCommand.Name { Is-Null $ssasServer.Roles['Administrators'] }
        DBGSTART
        $ssassAdminsRole = $null
        $ssassAdminsRole = $ssasServer.Roles['Administrators']
        DBGER $MyInvocation.MyCommand.Name $error
        DBGEND
        DBG ("Current SSAS administrators: -->`r`n{0}" -f ($ssassAdminsRole.Members | Out-String))


        foreach ($oneAppASRequirement in $appASRequirements) {

          DBG ('One SSAS requirement: {0} | asAdmins = {1}' -f $oneAppASRequirement.asAdmins, (Is-ValidString $oneAppASRequirement.asAdmins))
        
          if (Is-ValidString $oneAppASRequirement.asAdmins) {

            [string[]] $ssasAdmins = Split-MultiValue $oneAppASRequirement.asAdmins
            DBG ('SSAS admins to be set: #{0} | {1}' -f (Get-CountSafe $ssasAdmins), ($ssasAdmins -join ','))

            foreach ($oneSSASAdmin in $ssasAdmins) {

              $oneSSASAdminSAM = Get-SAMLogin $oneSSASAdmin
              DBG ('One SSAS admin to be set: {0} | {1}' -f $oneSSASAdmin, $oneSSASAdminSAM)
              DBGSTART
              [void] $ssassAdminsRole.Members.Add($oneSSASAdminSAM)
              DBGER $MyInvocation.MyCommand.Name $error
              DBGEND
            }

            DBG ('Update the SSAS admins to the configuration')
            DBGSTART
            $ssassAdminsRole.Update()
            DBGER $MyInvocation.MyCommand.Name $error
            DBGEND
          }
        }
      }
    }


    if (Is-ValidString $instanceName) {

      $ssasInstanceSvc = 'MSOLAP${0}' -f $instanceName

    } else {

      $ssasInstanceSvc = 'MSSQLServerOLAPService'
    }



    #================
    DBG ('Get SSAS service executable in order to be able to setup firewall exceptions')

    $ssasSvcExeStartParams = (Get-WmiQuerySingleObject '.' ('SELECT * FROM Win32_Service WHERE Name = "{0}"' -f $ssasInstanceSvc)).PathName
    DBG ('SSAS server service start path: {0}' -f $ssasSvcExeStartParams)
    DBGIF $MyInvocation.MyCommand.Name { Is-EmptyString $ssasSvcExeStartParams }
    $ssasSvcExePath = Get-ExecutablePathWithoutParams $ssasSvcExeStartParams -mustExist

    #================
    DBG ('Enable firewall rules for SSAS service')

    if (Is-ValidString $ssasSvcExePath) {

      Run-Process 'NETSH' ('ADVFIREWALL FIREWALL ADD RULE Name="Sevecek: SSAS Server {0} (TCP Dynamic)" Dir=IN Action=Allow Program="{2}" Service={1} Enable=Yes Profile=Any Protocol=TCP LocalPort=Any InterfaceType=LAN' -f $instanceName, $ssasInstanceSvc, $ssasSvcExePath)

    } else {

      Run-Process 'NETSH' ('ADVFIREWALL FIREWALL ADD RULE Name="Sevecek: SSAS Server {0} (TCP Dynamic)" Dir=IN Action=Allow Service={1} Enable=Yes Profile=Any Protocol=TCP LocalPort=Any InterfaceType=LAN' -f $instanceName, $ssasInstanceSvc)
    }

    #
    #

    DBG ('Restart the SSAS instance service to finalize the setup: {0}' -f $ssasInstanceSvc)
    DBGSTART
    Restart-Service $ssasInstanceSvc
    DBGER $MyInvocation.MyCommand.Name $error
    DBGEND
}



if (($appConfig.type -eq 'full') -or ($appConfig.type -eq 'express')) {

    DBG ('Installing full sql server instead of mgmt tools only, so proceed with after installation configuration')

    #================
    $instanceRegName = (Get-ItemProperty -Path "HKLM:\SOFTWARE\Microsoft\Microsoft SQL Server\Instance Names\SQL" -Name $instanceName -EV er -EA SilentlyContinue).$instanceName
    DBGER $MyInvocation.MyCommand.Name $er

    $instanceRegKey = "HKLM:\SOFTWARE\Microsoft\Microsoft SQL Server\$instanceRegName"
    DBG ('Instance registry path: {0}' -f $instanceRegKey)


    #================
    DBG ('Set AuditLevel to audit logins: {0}' -f (Is-ValidString $appConfig.auditLevel))

    if (Is-ValidString $appConfig.auditLevel) {

      DBG ('Current AuditLevel: {0}' -f (Get-ItemProperty -Path "$instanceRegKey\MSSQLServer" -Name AuditLevel -EV er -EA SilentlyContinue).AuditLevel)
      DBGER $MyInvocation.MyCommand.Name $er
  
      DBG ('Set AuditLevel: {0}' -f $appConfig.auditLevel)
      Set-RegistryValue "$instanceRegKey\MSSQLServer" AuditLevel ($appConfig.auditLevel) DWord
    }


    #================
    # Note: init WMI namespace for future use and some asserts

    if ($sqlVersion -eq '2005') {
  
      $sqlWmiNamespace = 'root\Microsoft\SqlServer\ComputerManagement'

    } elseif (($sqlVersion -eq '2008') -or ($sqlVersion -eq '2008r2')) {

      $sqlWmiNamespace = 'root\Microsoft\SqlServer\ComputerManagement10'
  
    } elseif ($sqlVersion -eq '2012') {

      $sqlWmiNamespace = 'root\Microsoft\SqlServer\ComputerManagement11'

    } elseif ($sqlVersion -eq '2014') {

      $sqlWmiNamespace = 'root\Microsoft\SqlServer\ComputerManagement12'
    
    } elseif ($sqlVersion -eq '2016') {

      $sqlWmiNamespace = 'root\Microsoft\SqlServer\ComputerManagement12'
    }


    if ((Is-ValidString $instanceName) -and ($instanceName -ne 'MSSQLSERVER')) {

      $sqlInstanceSvc = 'MSSQL${0}' -f $instanceName

    } else {

      $sqlInstanceSvc = 'MSSQLServer'
    }


    DBG ('Assert some basic things with WMI')
    $sqlInstanceFromWMI_VERSION = (Get-WmiQuerySingleObject '.' ('SELECT * FROM SqlServiceAdvancedProperty WHERE PropertyName = "VERSION" AND ServiceName = "{0}"' -f $sqlInstanceSvc) -namespace $sqlWmiNamespace).PropertyStrValue
    $sqlInstanceFromWMI_SKUNAME = (Get-WmiQuerySingleObject '.' ('SELECT * FROM SqlServiceAdvancedProperty WHERE PropertyName = "SKUNAME" AND ServiceName = "{0}"' -f $sqlInstanceSvc) -namespace $sqlWmiNamespace).PropertyStrValue
    $sqlInstanceFromWMI_INSTALLPATH = (Get-WmiQuerySingleObject '.' ('SELECT * FROM SqlServiceAdvancedProperty WHERE PropertyName = "INSTALLPATH" AND ServiceName = "{0}"' -f $sqlInstanceSvc) -namespace $sqlWmiNamespace).PropertyStrValue

    # Note: this will be like: MSSQL11.INFOSYS
    $sqlInstanceFromWMI_INSTANCEID = (Get-WmiQuerySingleObject '.' ('SELECT * FROM SqlServiceAdvancedProperty WHERE PropertyName = "INSTANCEID" AND ServiceName = "{0}"' -f $sqlInstanceSvc) -namespace $sqlWmiNamespace).PropertyStrValue

    # Note: this will be like: Software\Microsoft\Microsoft SQL Server\MSSQL11.INFOSYS
    #       for example, certificate thumprint goes into: SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL11.INFOSYS\MSSQLServer\SuperSocketNetLib
    #                                                     Certificate = REG_SZ = thumbprint
    $sqlInstanceFromWMI_REGROOT = (Get-WmiQuerySingleObject '.' ('SELECT * FROM SqlServiceAdvancedProperty WHERE PropertyName = "REGROOT" AND ServiceName = "{0}"' -f $sqlInstanceSvc) -namespace $sqlWmiNamespace).PropertyStrValue

    DBG ('SQL server instance queried as: {0} | {1} | {2}' -f $sqlInstanceFromWMI_VERSION, $sqlInstanceFromWMI_SKUNAME, $sqlInstanceFromWMI_INSTALLPATH)
    DBGIF $MyInvocation.MyCommand.Name { ($sqlVersion -eq '2005') -and ($sqlInstanceFromWMI_VERSION -notlike '9.?*.?*') }
    DBGIF $MyInvocation.MyCommand.Name { ($sqlVersion -eq '2008') -and ($sqlInstanceFromWMI_VERSION -notlike '10.?*.?*') }
    DBGIF $MyInvocation.MyCommand.Name { ($sqlVersion -eq '2008r2') -and ($sqlInstanceFromWMI_VERSION -notlike '10.5?*.?*') }
    DBGIF $MyInvocation.MyCommand.Name { ($sqlVersion -eq '2012') -and ($sqlInstanceFromWMI_VERSION -notlike '11.?*.?*') }
    DBGIF $MyInvocation.MyCommand.Name { ($sqlVersion -eq '2014') -and ($sqlInstanceFromWMI_VERSION -notlike '12.?*.?*') }
    DBGIF $MyInvocation.MyCommand.Name { ($sqlVersion -eq '2016') -and ($sqlInstanceFromWMI_VERSION -notlike '13.0.?*.?*') }
    DBGIF $MyInvocation.MyCommand.Name { Is-EmptyString $sqlInstanceFromWMI_INSTALLPATH }
    DBGIF $MyInvocation.MyCommand.Name { -not (Test-Path $sqlInstanceFromWMI_INSTALLPATH) }

    $sqlInstanceFromWMI_SERVICES = Get-WmiQueryArray '.' 'SELECT * FROM SqlService' -namespace $sqlWmiNamespace
    DBG ('SQL services found: {0}' -f ($sqlInstanceFromWMI_SERVICES | Select ServiceName, DisplayName, StartName | Out-String))
    DBGIF $MyInvocation.MyCommand.Name { -not (Contains-Safe $sqlInstanceFromWMI_SERVICES $sqlInstanceSvc ServiceName) }
    DBGIF $MyInvocation.MyCommand.Name { -not (Contains-Safe $sqlInstanceFromWMI_SERVICES SQLBrowser ServiceName) }
    DBGIF $MyInvocation.MyCommand.Name { (Obtain-ListMember $sqlInstanceFromWMI_SERVICES @{ 'ServiceName' = 'SQLBrowser' }).StartName -ne 'NT AUTHORITY\LocalService' }


    #================
    DBG ('Should we set a static port: {0} | {1}' -f (Is-ValidString $appConfig.staticPort), $appConfig.staticPort)

    if (Is-ValidString $appConfig.staticPort) {

      $ipAll = Get-WmiQuerySingleObject '.' ('SELECT * FROM ServerNetworkProtocolProperty WHERE InstanceName = "{0}" ANd IPAddressName = "IPAll" AND PropertyName = "TcpPort" AND ProtocolName = "Tcp"' -f $instanceName) $false $sqlWmiNamespace
  
      DBG ('Set static SQL TCP port to: {0}' -f $appConfig.staticPort)
      DBGSTART
      $wmiRs = $null
      $wmiRs = $ipAll.SetStringValue($appConfig.staticPort)
      DBGER $MyInvocation.MyCommand.Name $error
      DBGEND
      DBGWMI $wmiRs
    }


    #================
    DBG ('Get SQL service executable in order to be able to setup firewall exceptions')

    $dbSvcExeStartParams = (Get-WmiQuerySingleObject '.' ('SELECT * FROM Win32_Service WHERE Name = "{0}"' -f $sqlInstanceSvc)).PathName
    DBG ('MSSQL server service start path: {0}' -f $dbSvcExeStartParams)
    DBGIF $MyInvocation.MyCommand.Name { Is-EmptyString $dbSvcExeStartParams }
    $dbSvcExePath = Get-ExecutablePathWithoutParams $dbSvcExeStartParams -mustExist
    DBGIF $MyInvocation.MyCommand.Name { $dbSvcExePath -notlike (Join-Path $sqlInstanceFromWMI_INSTALLPATH *) }

    #================
    DBG ('Enable firewall rules for SQL services')

    if (Is-ValidString $dbSvcExePath) {

      Run-Process 'NETSH' ('ADVFIREWALL FIREWALL ADD RULE Name="Sevecek: SQL Server {0} (TCP Dynamic)" Dir=IN Action=Allow Program="{2}" Service={1} Enable=Yes Profile=Any Protocol=TCP LocalPort=Any InterfaceType=LAN' -f $instanceName, $sqlInstanceSvc, $dbSvcExePath)

    } else {

      Run-Process 'NETSH' ('ADVFIREWALL FIREWALL ADD RULE Name="Sevecek: SQL Server {0} (TCP Dynamic)" Dir=IN Action=Allow Service={1} Enable=Yes Profile=Any Protocol=TCP LocalPort=Any InterfaceType=LAN' -f $instanceName, $sqlInstanceSvc)
    }

    #================
    DBG ('Connect to SQL instance: {0}' -f $instanceName)

    DBG ('Loading SQL assembly')
    DBGSTART
    [void] [System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.Smo')
    DBGER $MyInvocation.MyCommand.Name $error
    DBGEND

    $sqlServerName = "localhost\$instanceName"

    DBG ('Connecting SQL server')
    $sql = $null
    $sql = New-Object Microsoft.SqlServer.Management.Smo.Server $sqlServerName -EV er -EA SilentlyContinue
    DBGER $MyInvocation.MyCommand.Name $er

    if (Is-NonNull $sql) {

        DBG ('Enumerate databases')
        DBGSTART
        $dbs = $null
        $dbs = $sql.Databases
        DBGER $MyInvocation.MyCommand.Name $error
        DBGEND

        if (Is-NonNull $dbs) {

        DBG ('Databases found: {0}' -f ($dbs | Select Name, CreateDate, IsAccessible, Tables | ft | Out-String))

        DBG ('Open MASTER database')
        DBGSTART
        $masterDB = $null
        $masterDB = $dbs['master']
        DBGER $MyInvocation.MyCommand.Name $error
        DBGEND

        DBG ('Should mount sample AdventureWorks database: {0}' -f (Parse-BoolSafe $appConfig.mountSample))
    
        if (Parse-BoolSafe $appConfig.mountSample) {
    
          $sqlDataRoot = (Get-ItemProperty -Path "$instanceRegKey\Setup" -Name SQLDataRoot -EV er -EA SilentlyContinue).SQLDataRoot
          DBGER $MyInvocation.MyCommand.Name $er
      
          $samplePath = Join-Path $sqlDataRoot 'Data\AdventureWorks.mdf'
          DBG ('Copy AdventureWorks sample to data dir: {0}' -f $samplePath)
          Copy-Item -Path (Join-Path (Split-Path $instRoot -Parent) ('AdventureWorks{0}_Data.mdf' -f $sqlVersion)) -Destination $samplePath -Force -EV er -EA SilentlyContinue
          DBGER $MyInvocation.MyCommand.Name $er

          DBG ('Mounting sample .MDF file: {0}' -f $samplePath)
          Execute-NonQuery $masterDB ('CREATE DATABASE AdventureWorks ON (FILENAME = N''{0}'') FOR ATTACH_REBUILD_LOG' -f $samplePath)
          #Execute-NonQuery $masterDB ('CREATE DATABASE AdventureWorks ON (FILENAME = N''{0}''), (FILENAME = N''{1}'') FOR ATTACH' -f $samplePath, ([System.IO.Path]::ChangeExtension($samplePath, '.ldf')))

          Grant-SQLLoginIntoDatabase $sqlServerName 'AdventureWorks' $appConfig.sampleUsers 'R$db_owner'

          Test-SQL $sqlServerName AdventureWorks $false @('FirstName', 'LastName') 'Person.Person'
        }
    

        $mountDatabases = $appConfig.SelectNodes('./mountDb')
        DBG ('Should we mount any databases: {0}' -f ((Get-CountSafe $mountDatabases) -gt 0))

        if ((Get-CountSafe $mountDatabases) -gt 0) { foreach ($oneMountDatabase in $mountDatabases) {

          DBG ('Going to mount a database: {0} | {1}' -f $oneMountDatabase.db, $oneMountDatabase.path)
          DBGIF $MyInvocation.MyCommand.Name { Is-EmptyString $oneMountDatabase.db }
          DBGIF $MyInvocation.MyCommand.Name { Is-EmptyString $oneMountDatabase.path }

          $mountDbSource = Join-Path $global:rootDir $oneMountDatabase.path
          $mountDbTarget = Join-Path $sqlDataRoot (Join-Path 'Data' ([IO.Path]::GetFileName($mountDbSource)))
          DBGIF $MyInvocation.MyCommand.Name { -not (Test-Path $mountDbSource) }
          DBGIF $MyInvocation.MyCommand.Name { Test-Path $mountDbTarget }

          DBG ('Copying the source DB into the target DATA folder: {0} | {1}' -f $mountDbSource, $mountDbTarget)
          DBGSTART
          Copy-Item $mountDbSource $mountDbTarget -Force
          DBGER $MyInvocation.MyCommand.Name $error
          DBGEND

          DBG ('Mounting the database MDF file: {0}' -f $mountDbTarget)
          Execute-NonQuery $masterDB ('CREATE DATABASE {0} ON (FILENAME = N''{1}'') FOR ATTACH_REBUILD_LOG' -f $oneMountDatabase.db, $mountDbTarget)

          DBG ('Do we have to grant any logins to the database: {0}' -f $oneMountDatabase.access)
          if (Is-ValidString $oneMountDatabase.access) {

            foreach ($oneAccessToDB in (Split-MultiValue $oneMountDatabase.access)) {

              $oneAccessToDBSAM = Get-SAMLogin $oneAccessToDB
              Grant-SQLLoginIntoDatabase $sqlServerName $oneMountDatabase.db $oneAccessToDBSAM 'R$public|R$db_owner'
            }
          }

          $mountDbTSQLs = $oneMountDatabase.SelectNodes('./tsql')
          DBG ('Should we run any TSQL commands against the mounted database: {0}' -f ((Get-CountSafe $mountDbTSQLs) -gt 0))

          if ((Get-CountSafe $mountDbTSQLs) -gt 0) { foreach ($oneDbTSQL in $mountDbTSQLs) {

            DBG ('We are going to run the following TSQL command against the mounted database: {0} | {1}' -f $oneMountDatabase.db, $oneDbTSQL.call)
            DBGIF $MyInvocation.MyCommand.Name { Is-EmptyString $oneDbTSQL.call }

            Execute-NonQuery -database (Open-SQLDatabase -sqlServer $sqlServerName -database $oneMountDatabase.db) -nonQuery $oneDbTSQL.call
          }}
        }}


        DBG ('Should we enable CLR integration: {0} | {1}' -f (Is-ValidString $appConfig.clrIntegration), (Parse-BoolSafe $appConfig.clrIntegration))
        if (Is-ValidString $appConfig.clrIntegration) {

          if (Parse-BoolSafe $appConfig.clrIntegration) {

            $clrIntegration = 1

          } else {

            $clrIntegration = 0
          }

          DBG ('Setting CLR Integration: {0}' -f $clrIntegration)
        
          Execute-NonQuery $masterDB 'sp_configure ''show advanced options'', 1'
          Execute-NonQuery $masterDB 'RECONFIGURE WITH OVERRIDE'
          Execute-NonQuery $masterDB ('sp_configure ''clr enabled'', {0}' -f $clrIntegration)
          Execute-NonQuery $masterDB 'RECONFIGURE WITH OVERRIDE'
        }


        DBG ('Prepare application and service requirements')
        $appDBs = $xmlConfig.SelectNodes('./VMs/MACHINE[vm/@do="true"]/*/sql[translate(@instance,"ABCDEFGHIJKLMNOPQRSTUVWXYZ","abcdefghijklmnopqrstuvwxyz")="{0}"]' -f $instanceName.ToLower())
        DBG ("Found {0} DB requirements for this instance" -f $appDBs.Count)

        foreach ($oneApp in $appDBs) {

          DBG ('Should add login into any server roles: {0}' -f (Is-ValidString $oneApp.roles))
          if (Is-ValidString $oneApp.roles) {

            $dbRoles = Split-MultiValue $oneApp.roles
            DBG ('Login to be added to roles: login = {0} | # = {1} | roles = {2}' -f $oneApp.login, (Get-CountSafe $dbRoles), $oneApp.roles)

            foreach ($oneRole in $dbRoles) {
        
              $oneLogin = Grant-SQLLogin $sqlServerName $oneApp.login

              DBG ('Adding login to role: {0} | {1} | {2}' -f $sqlLogin, $oneLogin.name, $oneRole)
              $sql.Roles[$oneRole].AddMember($oneLogin.name)
            }
          }


          DBG ('Should add the login into local Administrators group: {0} | {1}' -f $oneApp.login, (Parse-BoolSafe $oneApp.localAdmins))
          if (Parse-BoolSafe $oneApp.localAdmins) {
      
            Add-MemberLocalGroup 'Administrators' $oneApp.login
          }


          DBG ('Should set MAXDOP (max degree of parallelism): {0} | {1}' -f (Is-ValidString $oneApp.maxdop), $oneApp.maxdop)
          if (Is-ValidString $oneApp.maxdop) {

            DBG ('Setting MAXDOP: {0}' -f $oneApp.maxdop)
        
            Execute-NonQuery $masterDB 'sp_configure ''show advanced options'', 1'
            Execute-NonQuery $masterDB 'RECONFIGURE WITH OVERRIDE'
            Execute-NonQuery $masterDB ('sp_configure ''max degree of parallelism'', {0}' -f ([int] $oneApp.maxdop))
            Execute-NonQuery $masterDB 'RECONFIGURE WITH OVERRIDE'
          }
        }
      }
    }


    DBG ('Verify we have the instance service in place: {0}' -f $sqlInstanceSvc)
    DBGSTART
    $sqlService = Get-Service $sqlInstanceSvc
    DBGER $MyInvocation.MyCommand.Name $error
    DBGEND
    DBGIF $MyInvocation.MyCommand.Name { $sqlService.Status -ne 'Running' }


    if ((($global:thisOSVersionNumber -eq 6.2) -or ($global:thisOSVersionNumber -eq 6.3)) -and ($sqlVersion -eq '2012')) {

      DBG ('Fix 2012 bug about the %windir%\System32\LogFile\Sum folder permissions - Software Usage Metrics, KB2811566')
      # Note: the owner of the folder is SYSTEM actually, thus we have to enable the SeRestorePrivilege in order to be able to set the
      #       owner to something else (actually back to the SYSTEM value) than us/Administrators
      Apply-NtfsDacl "$env:SystemRoot\System32\LogFiles\Sum" ('M$NT SERVICE\{0}' -f $sqlInstanceSvc) -addOnly $true -enableSeRestorePrivilege $true
    }



    DBG ('Restart the service to complete initialization asap.')
    DBGSTART
    # Do the -Force here because the Agent service is dependent on the SQL server
    Restart-Service $sqlInstanceSvc -Force -EA SilentlyContinue
    DBGER $MyInvocation.MyCommand.Name $error
    DBGEND



    DBG ('Test some basic TCP connections after everything has been configured')

    [string[]] $sqlTcpPorts_TcpPort_TcpDynamicPorts = (Get-WmiQueryArray '.' ('SELECT * FROM ServerNetworkProtocolProperty WHERE (PropertyName = "TcpPort" OR PropertyName = "TcpDynamicPorts") AND PropertyStrVal <> "" AND PropertyStrVal <> "0"') -namespace $sqlWmiNamespace) | Select -Expand PropertyStrVal
    DBG ('SQL server listens to the following TCP ports: {0}' -f ($sqlTcpPorts_TcpPort_TcpDynamicPorts -join ','))
    DBGIF $MyInvocation.MyCommand.Name { (Get-CountSafe $sqlTcpPorts_TcpPort_TcpDynamicPorts) -lt 1 }

    [string[]] $sqlTcpPorts_IPAll_TcpPort_TcpDynamicPorts = (Get-WmiQueryArray '.' ('SELECT * FROM ServerNetworkProtocolProperty WHERE (PropertyName = "TcpPort" OR PropertyName = "TcpDynamicPorts") AND PropertyStrVal <> "" AND PropertyStrVal <> "0" AND IPAddressName = "IPAll"') -namespace $sqlWmiNamespace) | Select -Expand PropertyStrVal
    DBG ('SQL server listens on all IP addresses on TCP port: {0}' -f ($sqlTcpPorts_IPAll_TcpPort_TcpDynamicPorts -join ','))
    DBGIF $MyInvocation.MyCommand.Name { (Get-CountSafe $sqlTcpPorts_IPAll_TcpPort_TcpDynamicPorts) -lt 1 }

    DBG ('Test TLS connections on ports on AllIPs')
    [bool] $testTlsResult = $true
    foreach ($oneSqlTcpPorts_IPAll_TcpPort_TcpDynamicPorts in $sqlTcpPorts_IPAll_TcpPort_TcpDynamicPorts) {

      # Note: it is not a pure TLS, thus we cannot use the Test-Tls probably
      #$testTlsResult = $testTlsResult -and (Test-Tls -hostName localhost -port $oneSqlTcpPorts_IPAll_TcpPort_TcpDynamicPorts -doNotValidateCertificate $true)
      $testTlsResult = $testTlsResult -and (Test-Port localhost $oneSqlTcpPorts_IPAll_TcpPort_TcpDynamicPorts)
    }

    DBGIF $MyInvocation.MyCommand.Name { -not $testTlsResult }
}



DBG ('Throw up any remaining errors')
DBGSTART; DBGEND


# SIG # Begin signature block
# MIIc/QYJKoZIhvcNAQcCoIIc7jCCHOoCAQExDzANBglghkgBZQMEAgEFADB5Bgor
# BgEEAYI3AgEEoGswaTA0BgorBgEEAYI3AgEeMCYCAwEAAAQQH8w7YFlLCE63JNLG
# KX7zUQIBAAIBAAIBAAIBAAIBADAxMA0GCWCGSAFlAwQCAQUABCDZ0gjToji9Zynu
# HcX0VLlorJWhcYC0MdMlonmTuyedhqCCGAQwggTlMIIDzaADAgECAhA5vUKe0oFu
# utW8yQO0umXnMA0GCSqGSIb3DQEBCwUAMHUxCzAJBgNVBAYTAklMMRYwFAYDVQQK
# Ew1TdGFydENvbSBMdGQuMSkwJwYDVQQLEyBTdGFydENvbSBDZXJ0aWZpY2F0aW9u
# IEF1dGhvcml0eTEjMCEGA1UEAxMaU3RhcnRDb20gQ2xhc3MgMiBPYmplY3QgQ0Ew
# HhcNMTYxMjAxMTU1MTEzWhcNMTgxMjAxMTU1MTEzWjBRMQswCQYDVQQGEwJDWjEa
# MBgGA1UECAwRSmlob21vcmF2c2t5IEtyYWoxDTALBgNVBAcMBEJybm8xFzAVBgNV
# BAMMDk9uZHJlaiBTZXZlY2VrMIIBIjANBgkqhkiG9w0BAQEFAAOCAQ8AMIIBCgKC
# AQEAr9E9hNj06bash9JX97kpsqK9Z/ciOBC6trI4nvlW9CPwhKBTb5wArhxLYZBG
# 9jWPWrdy1nL/cm5qMqBb/mogYwMwvEYWMvsIOOVn6HD9lVhNAovD6PHz0ziBBKIs
# zXTjyUPQaoIlIELovz967m78HJdUZJGxqhluAsS9o9/fEzA7XXUhUuqRKsetuZV/
# Asfh5sOveeoRsbeW4daTWvtz3TJuULL0w43LNVYJkd6LL8cegvLPVZUe1N7skvid
# EvntdlowQsJlqFdrH3SGKIPKA6ObcY8SZWkEQSbVBF8Kum1UT+jN0gm+84FwOg5W
# qKx+VvTK2ljVWnPrCD0Zzu2oIQIDAQABo4IBkzCCAY8wDgYDVR0PAQH/BAQDAgeA
# MBMGA1UdJQQMMAoGCCsGAQUFBwMDMAkGA1UdEwQCMAAwHQYDVR0OBBYEFG2vSo3N
# hQWILeUs0oN9XzHTejcfMB8GA1UdIwQYMBaAFD5ik5rXxxnuPo9JEIVVFSDjlIQc
# MG0GCCsGAQUFBwEBBGEwXzAkBggrBgEFBQcwAYYYaHR0cDovL29jc3Auc3RhcnRz
# c2wuY29tMDcGCCsGAQUFBzAChitodHRwOi8vYWlhLnN0YXJ0c3NsLmNvbS9jZXJ0
# cy9zY2EuY29kZTIuY3J0MDYGA1UdHwQvMC0wK6ApoCeGJWh0dHA6Ly9jcmwuc3Rh
# cnRzc2wuY29tL3NjYS1jb2RlMi5jcmwwIwYDVR0SBBwwGoYYaHR0cDovL3d3dy5z
# dGFydHNzbC5jb20vMFEGA1UdIARKMEgwCAYGZ4EMAQQBMDwGCysGAQQBgbU3AQIF
# MC0wKwYIKwYBBQUHAgEWH2h0dHBzOi8vd3d3LnN0YXJ0c3NsLmNvbS9wb2xpY3kw
# DQYJKoZIhvcNAQELBQADggEBAJuRiEvHtIYSpsmMkPhTz4QOOShN3p5KWdf8vm71
# A33CR9fds10d8D2B2aE+vjmHJ69GY0bbfg5oZY2Lsq2euL7Da5/hS8+6T3MEtD4h
# njfHV7mxmoSfFuy/KDipoV6uwhI+ksqchXYdUH+5uCQO0MOO8ITjAgzUQsnZ4UIB
# HBGeP+e+3ljxSYSXWdPIrgxdR971P/HhWSVfKNlmBgEKMQM5Jy0aAd4jxSl/AzdY
# t0+6pliFJ1peGhdFni2Fm8fu5oN68aTIrNtc5WY7Lzgf+sRTVeWORWS37+1zAD0m
# jzd8gyfBLxRuaRSfjYxny0rLXelAwfiA3ze2DU2Bfg9/rfcwggXYMIIDwKADAgEC
# AhBsO9J+3TyUnpWOKKmzx1egMA0GCSqGSIb3DQEBCwUAMH0xCzAJBgNVBAYTAklM
# MRYwFAYDVQQKEw1TdGFydENvbSBMdGQuMSswKQYDVQQLEyJTZWN1cmUgRGlnaXRh
# bCBDZXJ0aWZpY2F0ZSBTaWduaW5nMSkwJwYDVQQDEyBTdGFydENvbSBDZXJ0aWZp
# Y2F0aW9uIEF1dGhvcml0eTAeFw0xNTEyMTYwMTAwMDVaFw0zMDEyMTYwMTAwMDVa
# MHUxCzAJBgNVBAYTAklMMRYwFAYDVQQKEw1TdGFydENvbSBMdGQuMSkwJwYDVQQL
# EyBTdGFydENvbSBDZXJ0aWZpY2F0aW9uIEF1dGhvcml0eTEjMCEGA1UEAxMaU3Rh
# cnRDb20gQ2xhc3MgMiBPYmplY3QgQ0EwggEiMA0GCSqGSIb3DQEBAQUAA4IBDwAw
# ggEKAoIBAQC5FARY97LFhiwIMmCtCCbAgXe5aBnZFSsdGGnk2hqWBZcuZHkaqT1R
# M1rQd2r0ApNBw466cBur2Ht0b5jo17mpPmh2pImgIqwX1in4u7hhn9IH0GYOMEcg
# K3ACHv5zCRxxNLXifqmsqKfxjjpABnaSyvd4bO9YBXN9f4NQ6aJVAuMArpanxsJk
# e+P4WECVLk17v92CAN5JVaczI+baT/lgo5NVcTEkloCViSbIfU6ILeyhOSQZvpom
# MYk8eJqI0nimOTJJfmXangNDsrX8np+3lXD0+6rCZisXRWIaeffyTMHZ31Qj1D50
# WYdRtX5yev4WgaXoKJQN3lkgXUcytvyHAgMBAAGjggFaMIIBVjAOBgNVHQ8BAf8E
# BAMCAQYwEwYDVR0lBAwwCgYIKwYBBQUHAwMwEgYDVR0TAQH/BAgwBgEB/wIBADAy
# BgNVHR8EKzApMCegJaAjhiFodHRwOi8vY3JsLnN0YXJ0c3NsLmNvbS9zZnNjYS5j
# cmwwZgYIKwYBBQUHAQEEWjBYMCQGCCsGAQUFBzABhhhodHRwOi8vb2NzcC5zdGFy
# dHNzbC5jb20wMAYIKwYBBQUHMAKGJGh0dHA6Ly9haWEuc3RhcnRzc2wuY29tL2Nl
# cnRzL2NhLmNydDAdBgNVHQ4EFgQUPmKTmtfHGe4+j0kQhVUVIOOUhBwwHwYDVR0j
# BBgwFoAUTgvvGqRAW6UXaYcwyjRoQ9BBrvIwPwYDVR0gBDgwNjA0BgRVHSAAMCww
# KgYIKwYBBQUHAgEWHmh0dHA6Ly93d3cuc3RhcnRzc2wuY29tL3BvbGljeTANBgkq
# hkiG9w0BAQsFAAOCAgEAY6U81bNtJyjY67pTrzAL6kpdEtX5mspw+kxjjNdNVH5G
# 6lLnhaEkIxqdpvY/Wdw+UdNtExs+N8efKPSwh2m/BxXj2fSeLMwXcwHFookScEER
# 8ez0quCNzioqNHac7LCXPEnQzbtG2FHlePKNDWh8eU6KxiAzNzIrIxPthinHGgLT
# BOACHQM2YTlD8YoU5oN3dLmBOqtH0BDMZoLcjEIoEW1zC+TnVb3yU1G0xub6gnN7
# lP50vbAiHJYrnywQiXaloBV8B9YYfe6ZgvjqxwufwFcMVyE3UmCuDTsOpjqDEKpJ
# 25s+FUdkie5VqCS1aaudLo31X+9UvP45pfgyRqzyfUnVEhH4ZXxlBWZMzj2Xov5+
# m/+H3kxYuFA5xdqdshj/Zx00S7PkCSF+8M1NCcvFgQwjIw61bZAjDBl3P3a8xNTX
# sb2CjFdiNKbT3LD6IGeIf0b/EbPf0FXdvBrxm0ofMOhnngdPolPYCtoOGtZPAVe/
# xeu+/ZyKv6TSHlshaUO0iYfsmbXnZ51vvt/kkjwms9/qPFxSuE0fjEfF7aQazwRE
# Df2hiVPR0pAhvShtM3oU4XreEFEUWEYHs25fYV4WMmxkUKSgmSmwRq45tvtGH4LT
# b5+cd+iLqK8rBQL0E6xaUjjGfsYx7bueIvqTvCkrQvoxMbn/qDHCiypowDVq6TAw
# ggZqMIIFUqADAgECAhADAZoCOv9YsWvW1ermF/BmMA0GCSqGSIb3DQEBBQUAMGIx
# CzAJBgNVBAYTAlVTMRUwEwYDVQQKEwxEaWdpQ2VydCBJbmMxGTAXBgNVBAsTEHd3
# dy5kaWdpY2VydC5jb20xITAfBgNVBAMTGERpZ2lDZXJ0IEFzc3VyZWQgSUQgQ0Et
# MTAeFw0xNDEwMjIwMDAwMDBaFw0yNDEwMjIwMDAwMDBaMEcxCzAJBgNVBAYTAlVT
# MREwDwYDVQQKEwhEaWdpQ2VydDElMCMGA1UEAxMcRGlnaUNlcnQgVGltZXN0YW1w
# IFJlc3BvbmRlcjCCASIwDQYJKoZIhvcNAQEBBQADggEPADCCAQoCggEBAKNkXfx8
# s+CCNeDg9sYq5kl1O8xu4FOpnx9kWeZ8a39rjJ1V+JLjntVaY1sCSVDZg85vZu7d
# y4XpX6X51Id0iEQ7Gcnl9ZGfxhQ5rCTqqEsskYnMXij0ZLZQt/USs3OWCmejvmGf
# rvP9Enh1DqZbFP1FI46GRFV9GIYFjFWHeUhG98oOjafeTl/iqLYtWQJhiGFyGGi5
# uHzu5uc0LzF3gTAfuzYBje8n4/ea8EwxZI3j6/oZh6h+z+yMDDZbesF6uHjHyQYu
# RhDIjegEYNu8c3T6Ttj+qkDxss5wRoPp2kChWTrZFQlXmVYwk/PJYczQCMxr7GJC
# kawCwO+k8IkRj3cCAwEAAaOCAzUwggMxMA4GA1UdDwEB/wQEAwIHgDAMBgNVHRMB
# Af8EAjAAMBYGA1UdJQEB/wQMMAoGCCsGAQUFBwMIMIIBvwYDVR0gBIIBtjCCAbIw
# ggGhBglghkgBhv1sBwEwggGSMCgGCCsGAQUFBwIBFhxodHRwczovL3d3dy5kaWdp
# Y2VydC5jb20vQ1BTMIIBZAYIKwYBBQUHAgIwggFWHoIBUgBBAG4AeQAgAHUAcwBl
# ACAAbwBmACAAdABoAGkAcwAgAEMAZQByAHQAaQBmAGkAYwBhAHQAZQAgAGMAbwBu
# AHMAdABpAHQAdQB0AGUAcwAgAGEAYwBjAGUAcAB0AGEAbgBjAGUAIABvAGYAIAB0
# AGgAZQAgAEQAaQBnAGkAQwBlAHIAdAAgAEMAUAAvAEMAUABTACAAYQBuAGQAIAB0
# AGgAZQAgAFIAZQBsAHkAaQBuAGcAIABQAGEAcgB0AHkAIABBAGcAcgBlAGUAbQBl
# AG4AdAAgAHcAaABpAGMAaAAgAGwAaQBtAGkAdAAgAGwAaQBhAGIAaQBsAGkAdAB5
# ACAAYQBuAGQAIABhAHIAZQAgAGkAbgBjAG8AcgBwAG8AcgBhAHQAZQBkACAAaABl
# AHIAZQBpAG4AIABiAHkAIAByAGUAZgBlAHIAZQBuAGMAZQAuMAsGCWCGSAGG/WwD
# FTAfBgNVHSMEGDAWgBQVABIrE5iymQftHt+ivlcNK2cCzTAdBgNVHQ4EFgQUYVpN
# JLZJMp1KKnkag0v0HonByn0wfQYDVR0fBHYwdDA4oDagNIYyaHR0cDovL2NybDMu
# ZGlnaWNlcnQuY29tL0RpZ2lDZXJ0QXNzdXJlZElEQ0EtMS5jcmwwOKA2oDSGMmh0
# dHA6Ly9jcmw0LmRpZ2ljZXJ0LmNvbS9EaWdpQ2VydEFzc3VyZWRJRENBLTEuY3Js
# MHcGCCsGAQUFBwEBBGswaTAkBggrBgEFBQcwAYYYaHR0cDovL29jc3AuZGlnaWNl
# cnQuY29tMEEGCCsGAQUFBzAChjVodHRwOi8vY2FjZXJ0cy5kaWdpY2VydC5jb20v
# RGlnaUNlcnRBc3N1cmVkSURDQS0xLmNydDANBgkqhkiG9w0BAQUFAAOCAQEAnSV+
# GzNNsiaBXJuGziMgD4CH5Yj//7HUaiwx7ToXGXEXzakbvFoWOQCd42yE5FpA+94G
# AYw3+puxnSR+/iCkV61bt5qwYCbqaVchXTQvH3Gwg5QZBWs1kBCge5fH9j/n4hFB
# pr1i2fAnPTgdKG86Ugnw7HBi02JLsOBzppLA044x2C/jbRcTBu7kA7YUq/OPQ6dx
# nSHdFMoVXZJB2vkPgdGZdA0mxA5/G7X1oPHGdwYoFenYk+VVFvC7Cqsc21xIJ2bI
# o4sKHOWV2q7ELlmgYd3a822iYemKC23sEhi991VUQAOSK2vCUcIKSK+w1G7g9BQK
# Ohvjjz3Kr2qNe9zYRDCCBs0wggW1oAMCAQICEAb9+QOWA63qAArrPye7uhswDQYJ
# KoZIhvcNAQEFBQAwZTELMAkGA1UEBhMCVVMxFTATBgNVBAoTDERpZ2lDZXJ0IElu
# YzEZMBcGA1UECxMQd3d3LmRpZ2ljZXJ0LmNvbTEkMCIGA1UEAxMbRGlnaUNlcnQg
# QXNzdXJlZCBJRCBSb290IENBMB4XDTA2MTExMDAwMDAwMFoXDTIxMTExMDAwMDAw
# MFowYjELMAkGA1UEBhMCVVMxFTATBgNVBAoTDERpZ2lDZXJ0IEluYzEZMBcGA1UE
# CxMQd3d3LmRpZ2ljZXJ0LmNvbTEhMB8GA1UEAxMYRGlnaUNlcnQgQXNzdXJlZCBJ
# RCBDQS0xMIIBIjANBgkqhkiG9w0BAQEFAAOCAQ8AMIIBCgKCAQEA6IItmfnKwkKV
# pYBzQHDSnlZUXKnE0kEGj8kz/E1FkVyBn+0snPgWWd+etSQVwpi5tHdJ3InECtqv
# y15r7a2wcTHrzzpADEZNk+yLejYIA6sMNP4YSYL+x8cxSIB8HqIPkg5QycaH6zY/
# 2DDD/6b3+6LNb3Mj/qxWBZDwMiEWicZwiPkFl32jx0PdAug7Pe2xQaPtP77blUjE
# 7h6z8rwMK5nQxl0SQoHhg26Ccz8mSxSQrllmCsSNvtLOBq6thG9IhJtPQLnxTPKv
# mPv2zkBdXPao8S+v7Iki8msYZbHBc63X8djPHgp0XEK4aH631XcKJ1Z8D2KkPzIU
# YJX9BwSiCQIDAQABo4IDejCCA3YwDgYDVR0PAQH/BAQDAgGGMDsGA1UdJQQ0MDIG
# CCsGAQUFBwMBBggrBgEFBQcDAgYIKwYBBQUHAwMGCCsGAQUFBwMEBggrBgEFBQcD
# CDCCAdIGA1UdIASCAckwggHFMIIBtAYKYIZIAYb9bAABBDCCAaQwOgYIKwYBBQUH
# AgEWLmh0dHA6Ly93d3cuZGlnaWNlcnQuY29tL3NzbC1jcHMtcmVwb3NpdG9yeS5o
# dG0wggFkBggrBgEFBQcCAjCCAVYeggFSAEEAbgB5ACAAdQBzAGUAIABvAGYAIAB0
# AGgAaQBzACAAQwBlAHIAdABpAGYAaQBjAGEAdABlACAAYwBvAG4AcwB0AGkAdAB1
# AHQAZQBzACAAYQBjAGMAZQBwAHQAYQBuAGMAZQAgAG8AZgAgAHQAaABlACAARABp
# AGcAaQBDAGUAcgB0ACAAQwBQAC8AQwBQAFMAIABhAG4AZAAgAHQAaABlACAAUgBl
# AGwAeQBpAG4AZwAgAFAAYQByAHQAeQAgAEEAZwByAGUAZQBtAGUAbgB0ACAAdwBo
# AGkAYwBoACAAbABpAG0AaQB0ACAAbABpAGEAYgBpAGwAaQB0AHkAIABhAG4AZAAg
# AGEAcgBlACAAaQBuAGMAbwByAHAAbwByAGEAdABlAGQAIABoAGUAcgBlAGkAbgAg
# AGIAeQAgAHIAZQBmAGUAcgBlAG4AYwBlAC4wCwYJYIZIAYb9bAMVMBIGA1UdEwEB
# /wQIMAYBAf8CAQAweQYIKwYBBQUHAQEEbTBrMCQGCCsGAQUFBzABhhhodHRwOi8v
# b2NzcC5kaWdpY2VydC5jb20wQwYIKwYBBQUHMAKGN2h0dHA6Ly9jYWNlcnRzLmRp
# Z2ljZXJ0LmNvbS9EaWdpQ2VydEFzc3VyZWRJRFJvb3RDQS5jcnQwgYEGA1UdHwR6
# MHgwOqA4oDaGNGh0dHA6Ly9jcmwzLmRpZ2ljZXJ0LmNvbS9EaWdpQ2VydEFzc3Vy
# ZWRJRFJvb3RDQS5jcmwwOqA4oDaGNGh0dHA6Ly9jcmw0LmRpZ2ljZXJ0LmNvbS9E
# aWdpQ2VydEFzc3VyZWRJRFJvb3RDQS5jcmwwHQYDVR0OBBYEFBUAEisTmLKZB+0e
# 36K+Vw0rZwLNMB8GA1UdIwQYMBaAFEXroq/0ksuCMS1Ri6enIZ3zbcgPMA0GCSqG
# SIb3DQEBBQUAA4IBAQBGUD7Jtygkpzgdtlspr1LPUukxR6tWXHvVDQtBs+/sdR90
# OPKyXGGinJXDUOSCuSPRujqGcq04eKx1XRcXNHJHhZRW0eu7NoR3zCSl8wQZVann
# 4+erYs37iy2QwsDStZS9Xk+xBdIOPRqpFFumhjFiqKgz5Js5p8T1zh14dpQlc+Qq
# q8+cdkvtX8JLFuRLcEwAiR78xXm8TBJX/l/hHrwCXaj++wc4Tw3GXZG5D2dFzdaD
# 7eeSDY2xaYxP+1ngIw/Sqq4AfO6cQg7PkdcntxbuD8O9fAqg7iwIVYUiuOsYGk38
# KiGtSTGDR5V3cdyxG0tLHBCcdxTBnU8vWpUIKRAmMYIETzCCBEsCAQEwgYkwdTEL
# MAkGA1UEBhMCSUwxFjAUBgNVBAoTDVN0YXJ0Q29tIEx0ZC4xKTAnBgNVBAsTIFN0
# YXJ0Q29tIENlcnRpZmljYXRpb24gQXV0aG9yaXR5MSMwIQYDVQQDExpTdGFydENv
# bSBDbGFzcyAyIE9iamVjdCBDQQIQOb1CntKBbrrVvMkDtLpl5zANBglghkgBZQME
# AgEFAKCBhDAYBgorBgEEAYI3AgEMMQowCKACgAChAoAAMBkGCSqGSIb3DQEJAzEM
# BgorBgEEAYI3AgEEMBwGCisGAQQBgjcCAQsxDjAMBgorBgEEAYI3AgEVMC8GCSqG
# SIb3DQEJBDEiBCAlpj//i/eRJIKWY1cNzD8i7LXTxPQjdeW8li8eLaXcODANBgkq
# hkiG9w0BAQEFAASCAQBU21ItmsIlCfCsWmWnSO9/eikV3w/oXcMMbOQiHXHJzSXY
# HQHLoKIMnJY1SA98b13w9a5O685ezWoON7pH1eQ6GtDZ74TC1MVezA/J5I9XIUI5
# MOdUTS4z4yA7GTiE7RYLY2GZAlSiR4s7xxaQZU0hmUOKucMVCVSg1oxYYyhwCKCg
# zoku0eQ3FkUk74PpOtxO6gJN6rlwiOo0v5jEwgdreY1l3de6z+tfycgoSIHAU9dK
# e6qApvrsUW0lKpYH8lwaC+2SOn1s/DDO+fGK4XtXGpuoCg+VAoCEuQ/DrZeY0k8V
# 9xMAwvUhWFK4QhjFCgrCSIt1otw7Ky00QENwgmB3oYICDzCCAgsGCSqGSIb3DQEJ
# BjGCAfwwggH4AgEBMHYwYjELMAkGA1UEBhMCVVMxFTATBgNVBAoTDERpZ2lDZXJ0
# IEluYzEZMBcGA1UECxMQd3d3LmRpZ2ljZXJ0LmNvbTEhMB8GA1UEAxMYRGlnaUNl
# cnQgQXNzdXJlZCBJRCBDQS0xAhADAZoCOv9YsWvW1ermF/BmMAkGBSsOAwIaBQCg
# XTAYBgkqhkiG9w0BCQMxCwYJKoZIhvcNAQcBMBwGCSqGSIb3DQEJBTEPFw0xODEx
# MTQwNTM1NDNaMCMGCSqGSIb3DQEJBDEWBBQmSAiWXVO9FOY4tFq9s1+2Hvt4ZjAN
# BgkqhkiG9w0BAQEFAASCAQBQKfm5q5xBBJatWN+MVy8GnfHUWUu2/wGE2fVf7bPB
# 7mOnGelZPQgCMf6wyZUBjuk+JUEZE7aQzfm0CVgqTQbQWor89NBxPBLt3ySbt9g/
# qXwHVBvOnnM3JvGvtPZraU0sPGN77E3kbkWrzPDCYKxYXPBNorP+qmLpIkLWnvzk
# XbUwxuko77kGf16FGQH6IBW0chgPvc31BNyxSd2En1j0kZZCO0NA8wBF4tlTtG3i
# 7gLDDJFR7NUBNQWAvWWmHbsoyBUIBAEnFvLmq07elNQn/IRyOiRRV7nOhvhdwmtx
# 0ZXkojbNZBL1gcmbbBRIjs1P/fDBqMr3KsUsTRs3Z+wa
# SIG # End signature block