🛢 sqmSQLTool

Get-sqmAlwaysOnHealthReport

Get-sqmAlwaysOnHealthReport -SqlInstance "SQL01" -MaxRedoQueueMB 200 -OutputPath "D:\Reports"

Add-sqmDatabaseToAG -AvailabilityGroup "AG1" -Database "SalesDB"

Add-sqmDatabaseToAG -AvailabilityGroup "AG1" -All

Invoke-sqmFailover -SqlInstance "SQL01" -AvailabilityGroup "AG_Prod" -WhatIf

        -TargetReplica "SQL02" -MaxRedoQueueMB 10

Invoke-sqmSqlAlwaysOnAutoseeding

Invoke-sqmSqlAlwaysOnAutoseeding -SqlInstance "SQL01\INSTANCE"

Invoke-sqmSqlAlwaysOnAutoseeding -All

New-sqmAlwaysOnRepairJob

New-sqmAlwaysOnRepairJob -Schedule "FREQ=DAILY;INTERVAL=1" -StartTime "02:00:00"

Remove-sqmDatabaseFromAvailabilityGroup -Database "SalesDB"

Remove-sqmDatabaseFromAvailabilityGroup -All

Sync-sqmAgNode

Sync-sqmAgNode -SqlInstance "SQL01" -AvailabilityGroup "AG_Prod" -ObjectName "AppLogin_*"

Invoke-sqmLogShrink -Database "MyDB" -ShrinkTargetPercent 20

Invoke-sqmLogShrink -SqlInstance "SQL01" -All -WhatIf

Invoke-sqmRestoreDatabase -SqlInstance "SQL01" -BackupFile "D:\Backup\AdventureWorks.bak" -DatabaseName "AdventureWorks"

$backupSequence = @(
    "D:\Backup\AdventureWorks_Full.bak",
    "D:\Backup\AdventureWorks_Diff.bak",
    "D:\Backup\AdventureWorks_Log1.trn",
    "D:\Backup\AdventureWorks_Log2.trn"
)
Invoke-sqmRestoreDatabase -SqlInstance "SQL01" -BackupFiles $backupSequence -DatabaseName "AdventureWorks"

Invoke-sqmRestoreDatabase -SqlInstance "SQL01" -BackupFile "D:\Backup\OldDB.bak" -DatabaseName "OldDB" -NewDatabaseName "NewDB" -ForceSingleUser

Invoke-sqmUserDatabaseBackup -All

Invoke-sqmUserDatabaseBackup -SqlInstance "SQL01" -Database "SalesDB", "InventoryDB"

Invoke-sqmUserDatabaseBackup -All -BackupPath "D:\Backup\User-Db"

Invoke-sqmUserDatabaseBackup -All -UseExcludeTable

Invoke-sqmUserDatabaseBackup -SqlInstance "SQL01" -All -UseExcludeTable

Invoke-sqmUserDatabaseBackup -SqlInstance "SQL01" -All -CheckPreferredReplica

Invoke-sqmUserDatabaseBackup -All -MailTo "dba@example.com"

Invoke-sqmUserDatabaseBackup -All -MailTo "dba@example.com" -MailProfile "SQLAlerts" -MailOnSuccess

Invoke-sqmUserDatabaseBackup -SqlInstance "SQL01" -All -UseExcludeTable `
    -CheckPreferredReplica -MailTo "dba@example.com" -MailOnSuccess

	New-sqmBackupMaintenanceJob -SqlInstance "SQL01" -BackupType FULL `
	    -UseExcludeTable -CheckPreferredReplica `
	    -MailTo "dba@company.com" -MailProfile "DBA-Mail"

	New-sqmBackupMaintenanceJob -SqlInstance "SQL01" -BackupType DIFF `
	    -UseExcludeTable -ScheduleTime "22:00"

	New-sqmBackupMaintenanceJob -SqlInstance "SQL01" -BackupType LOG `
	    -ScheduleIntervalMinutes 15 -UseExcludeTable

	New-sqmBackupMaintenanceJob -SqlInstance "SQL01" -BackupType FULL -Update

New-sqmOlaSysDbBackupJob -SqlInstance "SQL01"

New-sqmOlaSysDbBackupJob -SqlInstance "SQL01" -ScheduleTime "20:00" -OperatorName "DBAs"

Get-sqmADAccountStatus -SamAccountName 'jdoe'

'jdoe','jsmith' | Get-sqmADAccountStatus

    #>

    [CmdletBinding()]
    [OutputType([PSCustomObject])]
    param (
        [Parameter(Mandatory, ValueFromPipeline, ValueFromPipelineByPropertyName)]
        [ValidateNotNullOrEmpty()]
        [string[]] $SamAccountName,

        [Parameter()]
        [string] $DomainController
    )

    begin {
        #region --- Modul-Verfuegbarkeit einmalig pruefen ---
        $useRSAT = $false
        if (Get-Module -Name ActiveDirectory -ListAvailable) {
            try {
                Import-Module ActiveDirectory -ErrorAction Stop
                $useRSAT = $true
                Write-Verbose 'ActiveDirectory-Modul geladen (RSAT-Pfad aktiv).'
            }
            catch {
                Write-Verbose "ActiveDirectory-Modul nicht ladbar: $_  - Fallback auf ADSI."
            }
        }
        else {
            Write-Verbose 'ActiveDirectory-Modul nicht installiert - Fallback auf ADSI.'
        }

        # Hilfsfunktion: Ergebnis-Objekt erzeugen
        function New-ResultObject {
            param (
                [string]   $Sam,
                [bool]     $Enabled        = $false,
                [bool]     $LockedOut      = $false,
                [bool]     $PwdExpired     = $false,
                [bool]     $AcctExpired    = $false,
                [string]   $Source         = '',
                [string]   $ErrorMessage   = ''
            )
            [PSCustomObject]@{
                SamAccountName  = $Sam
                Enabled         = $Enabled
                LockedOut       = $LockedOut
                PasswordExpired = $PwdExpired
                AccountExpired  = $AcctExpired
                Source          = $Source
                QueryTime       = (Get-Date)
                ErrorMessage    = $ErrorMessage
            }
        }

        #region --- ADSI-Hilfsfunktion ---
        function Get-ADAccountStatusViaADSI {
            param ([string] $Sam)

            # Searcher aufbauen
            $searcher = [adsisearcher]"(sAMAccountName=$Sam)"
            $searcher.PropertiesToLoad.AddRange(@(
                'sAMAccountName',
                'userAccountControl',
                'lockoutTime',
                'pwdLastSet',
                'accountExpires',
                'msDS-UserPasswordExpiryTimeComputed'
            )) | Out-Null
            $searcher.SizeLimit = 1

            $entry = $searcher.FindOne()
            if (-not $entry) {
                throw "Konto '$Sam' wurde im Verzeichnis nicht gefunden."
            }

            $uac = [int]$entry.Properties['useraccountcontrol'][0]

            # Enabled: Bit 2 (0x0002) = disabled
            $enabled   = -not [bool]($uac -band 0x0002)

            # LockedOut: Bit 16 (0x0010) oder lockoutTime > 0
            $lockedBit = [bool]($uac -band 0x0010)
            $lockoutTimeRaw = $entry.Properties['lockouttime']
            $lockedTime = $false
            if ($lockoutTimeRaw.Count -gt 0) {
                $lt = [long]$lockoutTimeRaw[0]
                $lockedTime = ($lt -gt 0)
            }
            $lockedOut = $lockedBit -or $lockedTime

            # PasswordExpired: Bit 8388608 (0x800000) oder msDS-Attribut
            $pwdExpiredBit = [bool]($uac -band 0x800000)
            $pwdExpired = $pwdExpiredBit

            if (-not $pwdExpired) {
                $expiryRaw = $entry.Properties['msds-userpasswordexpirytimecomputed']
                if ($expiryRaw.Count -gt 0) {
                    $expiryFt = [long]$expiryRaw[0]
                    # 0 = laeuft nie ab, 9223372036854775807 = nie
                    if ($expiryFt -gt 0 -and $expiryFt -ne [long]::MaxValue) {
                        $expiryDate = [datetime]::FromFileTime($expiryFt)
                        $pwdExpired = ($expiryDate -lt (Get-Date))
                    }
                }
            }

            # AccountExpired
            $acctExpired = $false
            $acctExpiresRaw = $entry.Properties['accountexpires']
            if ($acctExpiresRaw.Count -gt 0) {
                $ae = [long]$acctExpiresRaw[0]
                # 0 und Int64.MaxValue bedeuten "laeuft nie ab"
                if ($ae -gt 0 -and $ae -ne [long]::MaxValue) {
                    $expDate = [datetime]::FromFileTime($ae)
                    $acctExpired = ($expDate -lt (Get-Date))
                }
            }

            return [PSCustomObject]@{
                Enabled         = $enabled
                LockedOut       = $lockedOut
                PasswordExpired = $pwdExpired
                AccountExpired  = $acctExpired
            }
        }
        #endregion
    }

    process {
        foreach ($sam in $SamAccountName) {
            Write-Verbose "Verarbeite Konto: $sam"

            #region --- RSAT-Pfad ---
            if ($useRSAT) {
                try {
                    $params = @{
                        Identity    = $sam
                        Properties  = @(
                            'Enabled',
                            'LockedOut',
                            'PasswordExpired',
                            'AccountExpirationDate'   # 'AccountExpired' ist keine abrufbare Property
                        )
                        ErrorAction = 'Stop'
                    }
                    if ($DomainController) { $params['Server'] = $DomainController }

                    $adUser = Get-ADUser @params

                    # AccountExpired: ExpirationDate vorhanden und in der Vergangenheit?
                    $acctExpired = ($null -ne $adUser.AccountExpirationDate) -and
                                   ($adUser.AccountExpirationDate -lt (Get-Date))

                    New-ResultObject `
                        -Sam         $sam `
                        -Enabled     ([bool]$adUser.Enabled) `
                        -LockedOut   ([bool]$adUser.LockedOut) `
                        -PwdExpired  ([bool]$adUser.PasswordExpired) `
                        -AcctExpired $acctExpired `
                        -Source      'RSAT'
                    continue
                }
                catch {
                    # Konto nicht gefunden ? direkt Fehlerobjekt, kein ADSI-Fallback
                    if ($_.Exception.GetType().Name -eq 'ADIdentityNotFoundException') {
                        New-ResultObject -Sam $sam -ErrorMessage "Konto nicht gefunden: $_" -Source 'RSAT'
                        continue
                    }
                    Write-Verbose "RSAT-Fehler fuer '$sam': $_ - Fallback auf ADSI."
                }
            }
            #endregion

            #region --- ADSI-Fallback ---
            try {
                $adsi = Get-ADAccountStatusViaADSI -Sam $sam

                New-ResultObject `
                    -Sam        $sam `
                    -Enabled    $adsi.Enabled `
                    -LockedOut  $adsi.LockedOut `
                    -PwdExpired $adsi.PasswordExpired `
                    -AcctExpired $adsi.AccountExpired `
                    -Source     'ADSI'
            }
            catch {
                New-ResultObject -Sam $sam -ErrorMessage $_.ToString() -Source 'ADSI'
            }
            #endregion
        }
    }
}

Get-sqmADGroupMembers -GroupName "DL_SQL_Admins"

Get-sqmADGroupMembers -GroupName "Administrators" -Domain "FITS"

Get-sqmSysadminAccounts

#>
function Get-sqmSysadminAccounts
{
	[CmdletBinding(SupportsShouldProcess = $true, ConfirmImpact = 'None')]
	[OutputType([PSCustomObject])]
	param (
		[Parameter(Mandatory = $false, ValueFromPipeline = $true)]
		[string[]]$SqlInstance = @($env:COMPUTERNAME),
		[Parameter(Mandatory = $false)]
		[System.Management.Automation.PSCredential]$SqlCredential,
		[Parameter(Mandatory = $false)]
		[string[]]$ExcludeLogin = @(),
		[Parameter(Mandatory = $false)]
		[switch]$ExcludeSysAccounts,
		[Parameter(Mandatory = $false)]
		[bool]$IncludeDisabled = $true,
		[Parameter(Mandatory = $false)]
		[string]$OutputPath = '$env:ProgramData\sqmSQLTool\Logs',
		[Parameter(Mandatory = $false)]
		[switch]$ContinueOnError,
		[Parameter(Mandatory = $false)]
		[switch]$EnableException
	)
	
	begin
	{
		$functionName = $MyInvocation.MyCommand.Name
		$allInstanceResults = [System.Collections.Generic.List[PSCustomObject]]::new()
		
		if (-not (Get-Module -ListAvailable -Name dbatools))
		{
			$errMsg = "dbatools-Modul nicht gefunden."
			Invoke-sqmLogging -Message $errMsg -FunctionName $functionName -Level "ERROR"
			throw $errMsg
		}
		
		Invoke-sqmLogging -Message "Starte $functionName mit OutputPath: $OutputPath" -FunctionName $functionName -Level "INFO"
		
		# Systemkonten-Muster fuer -ExcludeSysAccounts
		$sysAccountPatterns = @(
			'NT SERVICE\*',
			'NT AUTHORITY\SYSTEM',
			'NT AUTHORITY\NETWORK SERVICE',
			'NT AUTHORITY\LOCAL SERVICE',
			'NT AUTHORITY\*',
			'##MS_*##'
		)
		
		if ($ExcludeSysAccounts)
		{
			$ExcludeLogin = @($ExcludeLogin) + $sysAccountPatterns | Sort-Object -Unique
			Invoke-sqmLogging -Message "ExcludeSysAccounts: $($sysAccountPatterns.Count) Systemmuster hinzugefuegt." -FunctionName $functionName -Level "DEBUG"
		}
		
		# Hilfsfunktion fuer Ausschlusspruefung
		function _IsExcluded
		{
			param ([string]$Name,
				[string[]]$Patterns)
			if (-not $Patterns) { return $false }
			foreach ($p in $Patterns)
			{
				if ($Name -like $p) { return $true }
			}
			return $false
		}
	}
	
	process
	{
		foreach ($instance in $SqlInstance)
		{
			$connParams = @{ SqlInstance = $instance }
			if ($SqlCredential) { $connParams['SqlCredential'] = $SqlCredential }
			
			$detailRows = [System.Collections.Generic.List[PSCustomObject]]::new()
			
			try
			{
				Invoke-sqmLogging -Message "[$instance] Starte Sysadmin-Audit ..." -FunctionName $functionName -Level "INFO"
				
				$disabledFilter = if ($IncludeDisabled) { '' }
				else { 'AND sp.is_disabled = 0' }
				
				# Achtung: password_last_set_time und last_login_date wurden entfernt,
				# da sie in aelteren SQL Server-Versionen nicht existieren.
				$query = @"
SELECT
    sp.name                                          AS LoginName,
    sp.type_desc                                     AS LoginType,
    sp.is_disabled                                   AS IsDisabled,
    CASE WHEN sp.sid = 0x01 THEN 1 ELSE 0 END        AS IsSa,
    sp.create_date                                   AS CreateDate,
    sp.modify_date                                   AS ModifyDate,
    NULL                                             AS LastPasswordChange,
    NULL                                             AS LastLogin,
    sp.default_database_name                         AS DefaultDatabase
FROM sys.server_principals       sp
JOIN sys.server_role_members     rm ON rm.member_principal_id = sp.principal_id
JOIN sys.server_principals       sr ON sr.principal_id        = rm.role_principal_id
WHERE sr.name        = 'sysadmin'
  AND sp.type        IN ('S','U','G','R')
  AND sp.principal_id > 1
  $disabledFilter
ORDER BY sp.type_desc, sp.name;
"@
				$rows = Invoke-DbaQuery @connParams -Query $query -EnableException:$EnableException
				
				if (-not $rows)
				{
					$msg = "Keine sysadmin-Logins auf '$instance' gefunden (unerwartet)."
					Invoke-sqmLogging -Message $msg -FunctionName $functionName -Level "WARNING"
					$detailRows.Add([PSCustomObject]@{
							SqlInstance	       = $instance
							LoginName		   = '(keine)'
							LoginType		   = 'n/a'
							IsEnabled		   = $null
							IsSa			   = $false
							LastPasswordChange = $null
							LastLogin		   = $null
							CreateDate		   = $null
							Status			   = 'Error'
							Message		       = $msg
						})
				}
				else
				{
					Invoke-sqmLogging -Message "[$instance] $($rows.Count) sysadmin-Login(s) gefunden." -FunctionName $functionName -Level "INFO"
					
					foreach ($row in $rows)
					{
						$loginName = $row.LoginName
						$isSa = [bool]$row.IsSa
						$isEnabled = -not [bool]$row.IsDisabled
						$excluded = _IsExcluded $loginName $ExcludeLogin
						$isBuiltinAdmins = ($loginName -eq 'BUILTIN\Administrators')
						
						$status = if ($isSa) { 'SA' }
						elseif ($isBuiltinAdmins) { 'BuiltinAdmins' }
						elseif ($excluded) { 'Excluded' }
						elseif (-not $isEnabled) { 'Disabled' }
						else { 'Unexpected' }
						
						$msg = switch ($status)
						{
							'SA'            { 'SA-Konto (SID 0x01).' }
							'BuiltinAdmins' { 'BUILTIN\Administrators hat Sysadmin-Rechte - SICHERHEITSPRueFUNG ERFORDERLICH.' }
							'Excluded'      { 'Ausgeschlossen via -ExcludeLogin.' }
							'Disabled'      { 'Login hat sysadmin-Rechte, ist aber deaktiviert.' }
							'Unexpected'    { 'Sysadmin-Login - kein Ausschluss definiert.' }
						}
						
						$createDate = if ($row.CreateDate) { $row.CreateDate.ToString('yyyy-MM-dd') }
						else { $null }
						
						$detailRows.Add([PSCustomObject]@{
								SqlInstance = $instance
								LoginName   = $loginName
								LoginType   = $row.LoginType
								IsEnabled   = $isEnabled
								IsSa	    = $isSa
								LastPasswordChange = $null # Nicht verfuegbar in aelteren Versionen
								LastLogin   = $null # Nicht verfuegbar in aelteren Versionen
								CreateDate  = $createDate
								Status	    = $status
								Message	    = $msg
							})
					}
				}
				
				# Statistik
				$cntSa = ($detailRows | Where-Object Status -eq 'SA').Count
				$cntExcluded = ($detailRows | Where-Object Status -eq 'Excluded').Count
				$cntDisabled = ($detailRows | Where-Object Status -eq 'Disabled').Count
				$cntUnexpected = ($detailRows | Where-Object Status -eq 'Unexpected').Count
				$cntBuiltinAdmins = ($detailRows | Where-Object Status -eq 'BuiltinAdmins').Count
				
				Invoke-sqmLogging -Message ("[$instance] Gesamt: $($detailRows.Count) | SA: $cntSa | Ausgeschlossen: $cntExcluded | " +
					"Deaktiviert: $cntDisabled | Unerwartet: $cntUnexpected | BUILTIN\\Admins: $cntBuiltinAdmins") -FunctionName $functionName -Level "INFO"
				
				# Dateien schreiben
				$timestamp = Get-Date -Format 'yyyy-MM-dd HH:mm:ss'
				$datestamp = Get-Date -Format 'yyyy-MM-dd'
				$safeInst = $instance -replace '[\\/:*?"<>|]', '_'
				$txtFile = Join-Path $OutputPath "SysadminAccounts_${safeInst}_${datestamp}.txt"
				$csvFile = Join-Path $OutputPath "SysadminAccounts_${safeInst}_${datestamp}.csv"
				
				if ($PSCmdlet.ShouldProcess($instance, "Erstelle Sysadmin-Bericht in $OutputPath"))
				{
					if (-not (Test-Path $OutputPath))
					{
						New-Item -ItemType Directory -Path $OutputPath -Force -ErrorAction Stop | Out-Null
						Invoke-sqmLogging -Message "Verzeichnis $OutputPath wurde erstellt." -FunctionName $functionName -Level "INFO"
					}
					
					# TXT-Bericht (identisch zum vorherigen, daher hier ausgelassen - bitte aus Original uebernehmen)
					# ... (der Code fuer die TXT-Erstellung bleibt unveraendert)
					$lines = [System.Collections.Generic.List[string]]::new()
					$lines.Add("# ================================================================")
					$lines.Add("# MSSQLTools - Sysadmin-Konten Bericht")
					$lines.Add("# Instanz   : $instance")
					$lines.Add("# Erstellt  : $timestamp")
					$lines.Add("# Gesamt    : $($detailRows.Count) Logins")
					$lines.Add("# SA        : $cntSa")
					$lines.Add("# Ausgesch. : $cntExcluded")
					$lines.Add("# Deaktiv.  : $cntDisabled")
					$lines.Add("# Unerwartet: $cntUnexpected  ? PRueFEN")
					$lines.Add("# BUILTIN\\Adm: $cntBuiltinAdmins  ? SICHERHEITSPRueFUNG")
					$lines.Add("# SysExclude: $(if ($ExcludeSysAccounts) { 'Ja (NT SERVICE\*, NT AUTHORITY\*, ##MS_*##)' }
							else { 'Nein (manuell via -ExcludeLogin)' })")
					$lines.Add("# ================================================================")
					
					# BUILTIN\Administrators
					$builtinEntries = $detailRows | Where-Object { $_.Status -eq 'BuiltinAdmins' }
					$lines.Add(""); $lines.Add("# ================================================================")
					$lines.Add("# BUILTIN\Administrators - SICHERHEITSPRueFUNG ERFORDERLICH ($cntBuiltinAdmins)")
					$lines.Add("# ================================================================")
					if ($builtinEntries)
					{
						foreach ($e in $builtinEntries)
						{
							$lines.Add(("  Name   : {0}" -f $e.LoginName))
							$lines.Add(("  Typ    : {0}  |  Aktiv: {1}  |  Erstellt: {2}" -f $e.LoginType, $e.IsEnabled, $e.CreateDate))
							$lines.Add("  ? Empfehlung: Pruefen ob BUILTIN\Administrators Sysadmin-Rechte")
							$lines.Add("    gemaess Sicherheitsrichtlinie zulaessig sind. Ggf. entfernen:")
							$lines.Add("    EXEC sp_dropsrvrolemember 'BUILTIN\Administrators','sysadmin';")
						}
					}
					else { $lines.Add("  (nicht vorhanden - kein Befund)") }
					
					# Unerwartete Konten
					$unexpected = $detailRows | Where-Object { $_.Status -eq 'Unexpected' }
					$lines.Add(""); $lines.Add("# ----------------------------------------------------------------")
					$lines.Add("# UNERWARTETE SYSADMIN-KONTEN ($cntUnexpected)  ? PRueFEN")
					$lines.Add("# ----------------------------------------------------------------")
					if ($unexpected)
					{
						foreach ($e in ($unexpected | Sort-Object LoginType, LoginName))
						{
							$lines.Add(("  {0,-40} {1,-20} Enabled:{2,-5} Erstellt:{3}" -f $e.LoginName, $e.LoginType, $e.IsEnabled, $e.CreateDate))
						}
					}
					else { $lines.Add("  (keine)") }
					
					# Deaktivierte Konten
					$disabledEntries = $detailRows | Where-Object { $_.Status -eq 'Disabled' }
					$lines.Add(""); $lines.Add("# ----------------------------------------------------------------")
					$lines.Add("# DEAKTIVIERTE SYSADMIN-KONTEN ($cntDisabled)")
					$lines.Add("# ----------------------------------------------------------------")
					if ($disabledEntries)
					{
						foreach ($e in ($disabledEntries | Sort-Object LoginName))
						{
							$lines.Add("  $($e.LoginName)  [$($e.LoginType)]  Erstellt: $($e.CreateDate)")
						}
					}
					else { $lines.Add("  (keine)") }
					
					# SA-Konto
					$saEntry = $detailRows | Where-Object { $_.Status -eq 'SA' }
					$lines.Add(""); $lines.Add("# ----------------------------------------------------------------")
					$lines.Add("# SA-KONTO (SID 0x01)")
					$lines.Add("# ----------------------------------------------------------------")
					if ($saEntry)
					{
						foreach ($e in $saEntry)
						{
							$lines.Add(("  Name: {0,-40} Enabled: {1}" -f $e.LoginName, $e.IsEnabled))
						}
					}
					else { $lines.Add("  (nicht gefunden)") }
					
					# Ausgeschlossene Konten
					$excludedEntries = $detailRows | Where-Object { $_.Status -eq 'Excluded' }
					$lines.Add(""); $lines.Add("# ----------------------------------------------------------------")
					$lines.Add("# AUSGESCHLOSSENE KONTEN ($cntExcluded)")
					$lines.Add("# ----------------------------------------------------------------")
					if ($excludedEntries)
					{
						foreach ($e in ($excludedEntries | Sort-Object LoginType, LoginName))
						{
							$lines.Add(("  {0,-40} {1,-20} Enabled:{2}" -f $e.LoginName, $e.LoginType, $e.IsEnabled))
						}
					}
					else { $lines.Add("  (keine)") }
					
					$lines | Out-File -FilePath $txtFile -Encoding UTF8 -Force
					$detailRows | Export-Csv -Path $csvFile -Encoding UTF8 -NoTypeInformation -Force
					
					Invoke-sqmLogging -Message "[$instance] Bericht erstellt: $txtFile" -FunctionName $functionName -Level "INFO"
				}
				else
				{
					Invoke-sqmLogging -Message "[$instance] WhatIf: Berichtsdateien wuerden erstellt werden." -FunctionName $functionName -Level "VERBOSE"
					$txtFile = $null
					$csvFile = $null
				}
				
				if ($cntBuiltinAdmins -gt 0)
				{
					Invoke-sqmLogging -Message ("[$instance] BUILTIN\Administrators hat Sysadmin-Rechte - Sicherheitspruefung erforderlich!") -FunctionName $functionName -Level "WARNING"
				}
				if ($cntUnexpected -gt 0)
				{
					Invoke-sqmLogging -Message ("[$instance] $cntUnexpected unerwartete(s) sysadmin-Konto(en) gefunden.") -FunctionName $functionName -Level "WARNING"
				}
				
				$instanceResult = [PSCustomObject]@{
					SqlInstance							     = $instance
					Timestamp							     = $timestamp
					DetailRows							     = $detailRows
					TxtFile								     = $txtFile
					CsvFile								     = $csvFile
					Status								     = if ($cntUnexpected -gt 0 -or $cntBuiltinAdmins -gt 0) { 'Warning' } else { 'OK' }
				}
				$allInstanceResults.Add($instanceResult)
			}
			catch
			{
				$errMsg = "Fehler auf '$instance': $($_.Exception.Message)"
				Invoke-sqmLogging -Message $errMsg -FunctionName $functionName -Level "ERROR"
				$allInstanceResults.Add([PSCustomObject]@{
						SqlInstance = $instance
						Status	    = 'Error'
						Message	    = $errMsg
						DetailRows  = $null
						TxtFile	    = $null
						CsvFile	    = $null
					})
				if ($EnableException) { throw }
				if (-not $ContinueOnError) { throw $_ }
			}
		}
	}
	
	end
	{
		Invoke-sqmLogging -Message "$functionName abgeschlossen. $($allInstanceResults.Count) Instanzen verarbeitet." -FunctionName $functionName -Level "INFO"
		return $allInstanceResults
	}
}

Invoke-sqmLoginAudit

Invoke-sqmLoginAudit -SqlInstance "SQL01" -CheckAdOrphans -IncludeSystemLogins

Invoke-sqmSaObfuscation -SqlInstance "SQL01"

Invoke-sqmSaObfuscation -SqlInstance "SQL01" -NewName "hidden_sa" -PasswordLength 24

    Set-sqmDatabaseOwner -SqlInstance "SQL01"

    Set-sqmDatabaseOwner -SqlInstance "SQL01" -Database "Prod*" -OwnerLogin "svc_sqlowner"

    'SQL01','SQL02' | Set-sqmDatabaseOwner

    Set-sqmDatabaseOwner -SqlInstance "SQL01" -WhatIf

    Copies all permissions from D: to E: (recursively).

    Copies permissions and creates missing target folders.

Get-sqmConnectionStats -SqlInstance "SQL01"

Get-sqmConnectionStats -SqlInstance "SQL01" -GroupBy Login -TopN 10

Get-sqmConnectionStats -SqlInstance "SQL01" -GroupBy Database -IncludeSystemConnections

Get-sqmDatabaseHealth

Get-sqmDatabaseHealth -SqlInstance "SQL01" -IncludeSystemDatabases -OutputPath "D:\Reports"

    Returns disk information for drive C: and copies it to the clipboard.

    Returns disk information for drive D: without clipboard output.

    Returns disk information for multiple drives.

Get-sqmDiskSpaceReport

Get-sqmDiskSpaceReport -SqlInstance "SQL01" -WarnThresholdPct 15 -OutputPath "D:\Reports"

Get-sqmOperationStatus

Get-sqmOperationStatus -SqlInstance "SQL01" -OperationType AutoSeed

Get-sqmOperationStatus -Continuous -RefreshSeconds 10

Get-sqmOrphanedFiles -SqlInstance "SQL01"

Get-sqmOrphanedFiles -SqlInstance "SQL01" -SearchPath "D:\SQLData","E:\SQLLog" -Recurse

    Checks all SQL Server instances on the local computer.

    Checks only the default instance on SQL01.

    Checks all instances on two servers; errors are skipped.

    $result.DetailRows | Where-Object Status -eq 'Missing' | Select-Object Spn, SetSpnCommand
    Returns only missing SPNs with the ready-to-use setspn command.

Get-sqmSQLInstanceCheck

Get-sqmSQLInstanceCheck -SqlInstance "SQL01\INSTANCE" -Detailed

#>
function Get-sqmTempDbRecommendation
{
	[CmdletBinding()]
	[OutputType([PSCustomObject])]
	param (
		[Parameter(Mandatory = $false)]
		[string]$SqlInstance = $env:COMPUTERNAME,
		[Parameter(Mandatory = $false)]
		[System.Management.Automation.PSCredential]$SqlCredential,
		[Parameter(Mandatory = $false)]
		[string]$OutputPath,
		[Parameter(Mandatory = $false)]
		[switch]$EnableException
	)
	
	begin
	{
		$functionName = $MyInvocation.MyCommand.Name
		if (-not (Get-Module -ListAvailable -Name dbatools))
		{
			throw "dbatools-Modul nicht gefunden."
		}
	}
	
	process
	{
		try
		{
			$server = Connect-DbaInstance -SqlInstance $SqlInstance -SqlCredential $SqlCredential -ErrorAction Stop
			$tempdb = $server.Databases['tempdb']
			$cpuCount = $server.Processors
			$idealFileCount = [Math]::Min($cpuCount, 8)
			
			$files = $tempdb.FileGroups[0].Files
			$fileCount = $files.Count
			$fileSizeMB = $files | ForEach-Object { [math]::Round($_.Size / 1024, 2) }
			$fileGrowth = $files.ForEach('Growth') | ForEach-Object { ($_ / 1024) } # in MB
			$growthTypes = $files.ForEach('GrowthType')
			$paths = $files.ForEach('FileName') | ForEach-Object { Split-Path $_ -Parent }
			
			# Bewertung
			$status = 'OK'
			$messages = [System.Collections.Generic.List[string]]::new()
			if ($fileCount -ne $idealFileCount)
			{
				$status = 'Warning'
				$messages.Add("Anzahl TempDB-Dateien: $fileCount (empfohlen $idealFileCount).")
			}
			$sizeDifferences = ($fileSizeMB | Select-Object -Unique).Count -gt 1
			if ($sizeDifferences)
			{
				$status = 'Warning'
				$messages.Add("TempDB-Dateien haben unterschiedliche Groessen: $($fileSizeMB -join ', ') MB.")
			}
			$hasPercent = $growthTypes -contains 'Percent'
			if ($hasPercent)
			{
				$status = 'Warning'
				$messages.Add("Autogrow in Prozent wird verwendet (MB empfohlen).")
			}
			$hasLargeGrow = $fileGrowth -gt 1024
			if ($hasLargeGrow)
			{
				$status = 'Warning'
				$messages.Add("Autogrow-Schrittweite >1024 MB: $($fileGrowth -join ', ') MB.")
			}
			$uniquePaths = $paths | Select-Object -Unique
			if ($uniquePaths.Count -eq 1)
			{
				$messages.Add("Alle TempDB-Dateien liegen auf demselben Laufwerk ($($uniquePaths[0])) - fuer optimale Leistung separate Laufwerke empfehlenswert.")
				if ($status -eq 'OK') { $status = 'Info' }
			}
			if ($messages.Count -eq 0) { $messages.Add("TempDB-Konfiguration ist optimal.") }
			
			$result = [PSCustomObject]@{
				SqlInstance	     = $SqlInstance
				Status		     = $status
				FileCount	     = $fileCount
				RecommendedCount = $idealFileCount
				FileSizesMB	     = $fileSizeMB
				GrowthMB		 = $fileGrowth
				Paths		     = $paths
				Recommendations  = ($messages -join ' ')
			}
			
			if ($OutputPath) { $result | Export-Csv -Path $OutputPath -NoTypeInformation -Encoding UTF8 -Force }
			return $result
		}
		catch
		{
			Invoke-sqmLogging -Message $_.Exception.Message -FunctionName $functionName -Level "ERROR"
			if ($EnableException) { throw }
			return $null
		}
	}
}

Invoke-sqmPatchAnalysis -SqlInstance "SQL01"

"SQL01","SQL02","SQL03" | Invoke-sqmPatchAnalysis

Invoke-sqmPatchAnalysis -SqlInstance "SQL01","SQL02" -OutputPath "D:\Reports"

    Invoke-sqmSetupReport -SqlInstance "SQL01"

    Invoke-sqmSetupReport -SqlInstance "SQL01" -CheckProfile FiTs -PassThru

    Invoke-sqmSetupReport -SqlInstance "SQL01" -CheckProfile Generic

    New-sqmAgentProxy -SqlInstance "SQL01" -CredentialName "DOMAIN\SqlServiceAccount" `
        -ProxyName "SSIS Proxy"
    # Credential direkt uebergeben - kein Dialog
    $cred = Get-Credential "DOMAIN\SvcSSIS"
    New-sqmAgentProxy -SqlInstance "SQL01" -CredentialName "DOMAIN\SvcSSIS" `
        -ProxyName "SSIS Proxy" -WindowsCredential $cred

    New-sqmAgentProxy -SqlInstance "SQL01" -CredentialName "DOMAIN\SvcSSIS" `
        -ProxyName "SSIS Only Proxy" -Subsystem SSIS

    New-sqmAgentProxy -SqlInstance "SQL01" -CredentialName "DOMAIN\SvcPS" `
        -ProxyName "Script Proxy" -Subsystem CmdExec, PowerShell

    New-sqmAgentProxy -SqlInstance "SQL01" -CredentialName "ProxyCred_SSIS" `
        -ProxyName "SSIS Proxy" -WindowsUserName "DOMAIN\SvcSSIS" -Force

    $secPwd = ConvertTo-SecureString "P@ssw0rd" -AsPlainText -Force
    $cred   = New-Object System.Management.Automation.PSCredential("DOMAIN\SvcSSIS", $secPwd)
    New-sqmAgentProxy -SqlInstance "SQL01" -CredentialName "DOMAIN\SvcSSIS" `
        -ProxyName "SSIS Proxy" -WindowsCredential $cred -Subsystem SSIS
    New-sqmAgentProxy -SqlInstance "SQL01\INST1" -CredentialName "DOMAIN\SvcSSIS" `
        -ProxyName "SSIS Execution Proxy" -ProxyDescription "Fuehrt SSIS-Pakete aus" `
        -WindowsCredential $winCred -Force

Test-sqmCostThreshold -SqlInstance "SQL01"

Test-sqmCostThreshold -SqlInstance "SQL01" -MinRecommendedValue 25

Test-sqmTempDbFileCount -SqlInstance "SQL01"

Test-sqmTempDbFileCount -SqlInstance "SQL01\INST1" -MaxFiles 4

Get-sqmAutoGrowthReport -SqlInstance "SQL01"

Get-sqmAutoGrowthReport -SqlInstance "SQL01" -Detailed -IncludeSystem

Get-sqmBlockingReport

Get-sqmBlockingReport -SqlInstance "SQL01" -MinWaitSeconds 30

    if ((Get-sqmBlockingReport -SqlInstance "SQL01").HasBlocking) { Write-Warning "Blocking detected!" }

    Get-sqmBlockingReport -SqlInstance "SQL01" -OutputPath "$env:ProgramData\sqmSQLTool\Logs\Blocking"

Get-sqmDeadlockReport

Get-sqmDeadlockReport -SqlInstance "SQL01" -StartTime (Get-Date).AddDays(-7)

    Get-sqmDeadlockReport -SqlInstance "SQL01" -OutputPath "$env:ProgramData\sqmSQLTool\Logs\Deadlocks"

    Get-sqmDeadlockReport -StartTime (Get-Date).AddHours(-1) |
        Select-Object Timestamp, VictimLogin, VictimStatement, ProcessCount

Get-sqmIndexFragmentation -Database 'AdventureWorks' -MinFragmentationPercent 10

Get-sqmIndexFragmentation -SqlInstance 'SQL01' -MinFragmentationPercent 30

Get-sqmLongRunningQueries

Get-sqmLongRunningQueries -SqlInstance "SQL01" -MinDurationSeconds 60

    Get-sqmLongRunningQueries -MinDurationSeconds 10 | Sort-Object DurationSeconds -Descending | Select-Object -First 10

    Get-sqmLongRunningQueries -MinDurationSeconds 120 -OutputPath "$env:ProgramData\sqmSQLTool\Logs\LongRunning"

Get-sqmMissingIndexes -SqlInstance "SQL01"

    Get-sqmMissingIndexes -SqlInstance "SQL01" -MinImpactScore 50 -MinSeeks 500

    Get-sqmMissingIndexes -SqlInstance "SQL01" -Top 10 -OutputPath "D:\Reports"

    Get-sqmMissingIndexes -SqlInstance "SQL01" -MinImpactScore 30 |
        Select-Object DatabaseName, TableName, ImpactScore, CreateIndexStatement |
        Format-List

Get-sqmPerfCounters -SqlInstance "SQL01"

Get-sqmPerfCounters -SqlInstance "SQL01" -Category "Buffer","Memory"

Get-sqmWaitStatistics -SqlInstance "SQL01" -TopN 20

    Get-sqmWaitStatistics -SqlInstance "SQL01" -SnapshotBefore $before

    Invoke-sqmExtendedEvents -SqlInstance SQL01 -Create -Start

    Invoke-sqmExtendedEvents -SqlInstance SQL01 -Template SlowQueries -SlowQueryThresholdMs 2000 -TargetType File -Create -Start

    Invoke-sqmExtendedEvents -SqlInstance SQL01 -Read -Diagnose

    Invoke-sqmExtendedEvents -SqlInstance SQL01 -Stop -Drop

    Invoke-sqmPerfBaseline -SqlInstance "SQL01" -BaselineName "before_patch"

    Invoke-sqmPerfBaseline -SqlInstance "SQL01" -BaselineName "after_patch"
    Invoke-sqmPerfBaseline -SqlInstance "SQL01" -Action Compare

    Invoke-sqmPerfBaseline -SqlInstance "SQL01" -Action List

    Invoke-sqmQueryStore -All

    Invoke-sqmQueryStore -Database "SalesDB","CRM" -Configure -Query -Diagnose

    Invoke-sqmQueryStore -Database "SalesDB" -Query -TopN 50 -OrderBy CPU -LookbackHours 48

    Invoke-sqmQueryStore -All -Diagnose -StorageWarningPct 70 -OutputPath "D:\Reports\QS"

Invoke-sqmUpdateStatistics -Database 'SalesDB' -SamplePercent 10

    Checks drive D: and formats it with 64 KB clusters if needed.
    Data is backed up to C:\Temp\DriveBackup first.

    Same as above, without confirmation prompt, using a different backup path.

    Simulates the entire process without making any changes.

    Tests the default instance on TCP port 1433.

    Tests a custom port.

    Determines the dynamic port of the "SAGE" instance via SQL Browser (UDP 1434)
    and then tests the TCP connection.

    Tests the "PROD" instance on three servers via pipeline.

Compare-sqmServerConfiguration -SourceInstance "SQL01" -TargetInstance "SQL02"

Get-sqmConfig

#>
function Get-sqmConfig
{
	[CmdletBinding()]
	param (
		[Parameter(Mandatory = $false)]
		[string]$Key
	)
	if ($Key)
	{
		if ($script:sqmModuleConfig.ContainsKey($Key))
		{
			return $script:sqmModuleConfig[$Key]
		}
		else
		{
			Write-Warning "Konfigurationsschluessel '$Key' existiert nicht. Verfuegbare Schluessel: $($script:sqmModuleConfig.Keys -join ', ')"
			return $null
		}
	}
	return $script:sqmModuleConfig
}

$backupPath = Get-sqmServerSetting -Name "BackupDirectory"

Get-sqmServerSetting -All

$cred = Get-Credential
Get-sqmServerSetting -SqlInstance "SQL01" -SqlCredential $cred -All

Invoke-sqmCollationChange -NewCollation "Latin1_General_CI_AS"

Invoke-sqmCollationChange -SqlInstance "SQL01\INST2" -NewCollation "German_CI_AS" -IncludeUserDatabases -BackupBeforeChange

Invoke-sqmSetDatabaseRecoveryMode -All -RecoveryMode Full

Invoke-sqmSetDatabaseRecoveryMode -Database "SalesDB" -RecoveryMode Simple -Confirm

Invoke-sqmSplunkConfiguration

Invoke-sqmSplunkConfiguration -Mode Test

Invoke-sqmSplunkConfiguration -Remote -SearchOU "OU=DB-Server,DC=contoso,DC=com"

Invoke-sqmSplunkConfiguration -ComputerList "SRV-SQL01","SRV-SQL02"

Invoke-sqmSplunkConfiguration -ComputerList "C:\Listen\db-server.txt" -Mode Test

    Invoke-sqmSsisConfiguration -SqlInstance "SQL01" -CatalogPassword $pwd

    Invoke-sqmSsisConfiguration -SqlInstance "SQL01" -AgName "AG_SSIS" -CatalogPassword $pwd

Set-sqmConfig -LogPath "D:\Logs" -OlaJobNameFull "Prod-FULL"

Set-sqmConfig -TsmManagementClasses @('MC_10','MC_30','MC_100')

        [PSCustomObject]@{ DomainPattern = '*.sfinance.net'; GroupNamePattern = 'Rg_DC_AouAllowManageAuditSecLogSrvAll_Mod' },
        [PSCustomObject]@{ DomainPattern = '*';              GroupNamePattern = 'Rg_DC_AouAllowManageAuditSecLogSrvAll_Mod' }
    )

#>
function Set-sqmConfig
{
	[CmdletBinding(SupportsShouldProcess = $true)]
	param (
		[Parameter(Mandatory = $false)]
		[string]$LogPath,
		[Parameter(Mandatory = $false)]
		[string]$OutputPath,
		[Parameter(Mandatory = $false)]
		[string]$CentralPath,
		[Parameter(Mandatory = $false)]
		[string]$OlaJobNameFull,
		[Parameter(Mandatory = $false)]
		[string]$OlaJobNameDiff,
		[Parameter(Mandatory = $false)]
		[string]$OlaJobNameLog,
		[Parameter(Mandatory = $false)]
		[string]$OlaJobNameIndexOpt,
		[Parameter(Mandatory = $false)]
		[string]$OlaJobNameIntUserDb,
		[Parameter(Mandatory = $false)]
		[string]$OlaJobNameIntSysDb,
		[Parameter(Mandatory = $false)]
		[string]$OlaJobNameSysDbBackup,
		[Parameter(Mandatory = $false)]
		[string[]]$TsmManagementClasses,
		[Parameter(Mandatory = $false)]
		[bool]$AutoUpdate,
		[Parameter(Mandatory = $false)]
		[string]$UpdateRepository,
		[Parameter(Mandatory = $false)]
		[string]$DefaultPolicy,
		[Parameter(Mandatory = $false)]
		[PSCustomObject[]]$HpuDomainGroupMap,
		[Parameter(Mandatory = $false)]
		[string]$SsrsInstallerPath,
		[Parameter(Mandatory = $false)]
		[ValidateSet('Auto', 'FiTs', 'Generic')]
		[string]$CheckProfile,
		[Parameter(Mandatory = $false)]
		[ValidateRange(1, 1000)]
		[int]$CheckCostThresholdMin,
		[Parameter(Mandatory = $false)]
		[ValidateRange(1, 64)]
		[int]$CheckTempDbMaxFiles,
		[Parameter(Mandatory = $false)]
		[ValidateSet(4096, 8192, 16384, 32768, 65536, 131072)]
		[int]$CheckDiskBlockSize,
		[Parameter(Mandatory = $false)]
		[ValidateSet('de-DE', 'en-US')]
		[string]$Language,
		[Parameter(Mandatory = $false)]
		[switch]$PassThru
	)
	
	# Hilfsfunktion zum Pruefen/Erstellen eines Pfads
	# Pfad-Existenz und Schreibbarkeit werden geprueft wenn moeglich.
	# Nicht-erreichbare Pfade erzeugen nur eine Warnung - kein Fehler.
	# Konfiguration wird trotzdem gespeichert (Pfad kann spaeter entstehen).
	function Test-AndCreatePath($Path, $Purpose)
	{
		if (-not $Path) { return $true }
		if ($Path -match '^\s*$')
		{
			Write-Error "Pfad fuer $Purpose darf nicht leer sein."
			return $false
		}
		try
		{
			if (-not (Test-Path $Path))
			{
				New-Item -ItemType Directory -Path $Path -Force -ErrorAction Stop | Out-Null
				Write-Verbose "Verzeichnis '$Path' ($Purpose) wurde erstellt."
			}
			# Schreibtest nur wenn Pfad erreichbar - Fehler hier ist nicht kritisch
			try
			{
				$testFile = Join-Path $Path "test_$(Get-Random).tmp"
				New-Item -ItemType File -Path $testFile -Force -ErrorAction Stop | Out-Null
				Remove-Item -Path $testFile -Force -ErrorAction SilentlyContinue
			}
			catch
			{
				Write-Warning "Pfad '$Path' ($Purpose) ist nicht beschreibbar: $($_.Exception.Message). Konfiguration wird trotzdem gespeichert."
			}
			return $true
		}
		catch
		{
			# Verzeichnis konnte nicht erstellt werden - nur warnen, nicht abbrechen
			Write-Warning "Pfad '$Path' ($Purpose) konnte nicht erstellt werden: $($_.Exception.Message). Wird spaeter automatisch angelegt. Konfiguration wird gespeichert."
			return $true
		}
	}
	
	$updated = $false
	$globalConfig = $script:sqmModuleConfig
	
	# Pfad-Parameter
	if ($PSBoundParameters.ContainsKey('LogPath'))
	{
		if (Test-AndCreatePath $LogPath "LogPath")
		{
			$globalConfig['LogPath'] = $LogPath
			$updated = $true
		}
		else { return }
	}
	if ($PSBoundParameters.ContainsKey('OutputPath'))
	{
		if (Test-AndCreatePath $OutputPath "OutputPath")
		{
			$globalConfig['OutputPath'] = $OutputPath
			$updated = $true
		}
		else { return }
	}
	if ($PSBoundParameters.ContainsKey('CentralPath'))
	{
		if ($CentralPath)
		{
			if (Test-AndCreatePath $CentralPath "CentralPath")
			{
				$globalConfig['CentralPath'] = $CentralPath
				$updated = $true
			}
			else { return }
		}
		else
		{
			$globalConfig['CentralPath'] = $null
			$updated = $true
		}
	}
	
	# Ola-Job-Namen
	if ($PSBoundParameters.ContainsKey('OlaJobNameFull'))
	{
		if (-not [string]::IsNullOrWhiteSpace($OlaJobNameFull))
		{
			$globalConfig['OlaJobNameFull'] = $OlaJobNameFull
			$updated = $true
		}
		else { Write-Error "OlaJobNameFull darf nicht leer sein."; return }
	}
	if ($PSBoundParameters.ContainsKey('OlaJobNameDiff'))
	{
		if (-not [string]::IsNullOrWhiteSpace($OlaJobNameDiff))
		{
			$globalConfig['OlaJobNameDiff'] = $OlaJobNameDiff
			$updated = $true
		}
		else { Write-Error "OlaJobNameDiff darf nicht leer sein."; return }
	}
	if ($PSBoundParameters.ContainsKey('OlaJobNameLog'))
	{
		if (-not [string]::IsNullOrWhiteSpace($OlaJobNameLog))
		{
			$globalConfig['OlaJobNameLog'] = $OlaJobNameLog
			$updated = $true
		}
		else { Write-Error "OlaJobNameLog darf nicht leer sein."; return }
	}
	if ($PSBoundParameters.ContainsKey('OlaJobNameIndexOpt'))
	{
		if (-not [string]::IsNullOrWhiteSpace($OlaJobNameIndexOpt))
		{
			$globalConfig['OlaJobNameIndexOpt'] = $OlaJobNameIndexOpt
			$updated = $true
		}
		else { Write-Error "OlaJobNameIndexOpt darf nicht leer sein."; return }
	}
	if ($PSBoundParameters.ContainsKey('OlaJobNameIntUserDb'))
	{
		if (-not [string]::IsNullOrWhiteSpace($OlaJobNameIntUserDb))
		{
			$globalConfig['OlaJobNameIntUserDb'] = $OlaJobNameIntUserDb
			$updated = $true
		}
		else { Write-Error "OlaJobNameIntUserDb darf nicht leer sein."; return }
	}
	if ($PSBoundParameters.ContainsKey('OlaJobNameIntSysDb'))
	{
		if (-not [string]::IsNullOrWhiteSpace($OlaJobNameIntSysDb))
		{
			$globalConfig['OlaJobNameIntSysDb'] = $OlaJobNameIntSysDb
			$updated = $true
		}
		else { Write-Error "OlaJobNameIntSysDb darf nicht leer sein."; return }
	}
	if ($PSBoundParameters.ContainsKey('OlaJobNameSysDbBackup'))
	{
		if (-not [string]::IsNullOrWhiteSpace($OlaJobNameSysDbBackup))
		{
			$globalConfig['OlaJobNameSysDbBackup'] = $OlaJobNameSysDbBackup
			$updated = $true
		}
		else { Write-Error "OlaJobNameSysDbBackup darf nicht leer sein."; return }
	}
	
	# TSM Management-Klassen
	if ($PSBoundParameters.ContainsKey('TsmManagementClasses'))
	{
		if ($TsmManagementClasses -and $TsmManagementClasses.Count -gt 0)
		{
			$globalConfig['TsmManagementClasses'] = $TsmManagementClasses
			$updated = $true
		}
		else
		{
			Write-Error "TsmManagementClasses darf nicht leer sein."
			return
		}
	}
	
	# Update-Einstellungen
	if ($PSBoundParameters.ContainsKey('AutoUpdate'))
	{
		$globalConfig['AutoUpdate'] = $AutoUpdate
		$updated = $true
	}
	if ($PSBoundParameters.ContainsKey('UpdateRepository'))
	{
		$globalConfig['UpdateRepository'] = $UpdateRepository
		$updated = $true
	}
	
	# Default Policy Name
	if ($PSBoundParameters.ContainsKey('DefaultPolicy'))
	{
		if (-not [string]::IsNullOrWhiteSpace($DefaultPolicy))
		{
			$globalConfig['DefaultPolicy'] = $DefaultPolicy
			$updated = $true
		}
		else { Write-Error "DefaultPolicy darf nicht leer sein."; return }
	}
	
	# HPU-Domain-Gruppen-Mapping
	if ($PSBoundParameters.ContainsKey('HpuDomainGroupMap'))
	{
		if ($HpuDomainGroupMap -and $HpuDomainGroupMap.Count -gt 0)
		{
			# Pflichtfelder jedes Eintrags pruefen
			foreach ($entry in $HpuDomainGroupMap)
			{
				if ([string]::IsNullOrWhiteSpace($entry.DomainPattern))
				{
					Write-Error "HpuDomainGroupMap: Jeder Eintrag benoetigt ein nicht-leeres Feld 'DomainPattern'."
					return
				}
				if ([string]::IsNullOrWhiteSpace($entry.GroupNamePattern))
				{
					Write-Error "HpuDomainGroupMap: Jeder Eintrag benoetigt ein nicht-leeres Feld 'GroupNamePattern'."
					return
				}
			}
			$globalConfig['HpuDomainGroupMap'] = $HpuDomainGroupMap
			$updated = $true
		}
		else
		{
			Write-Error "HpuDomainGroupMap darf nicht leer sein."
			return
		}
	}
	
	# SSRS-Installer-Pfad
	if ($PSBoundParameters.ContainsKey('SsrsInstallerPath'))
	{
		if (-not [string]::IsNullOrWhiteSpace($SsrsInstallerPath))
		{
			$ext = [System.IO.Path]::GetExtension($SsrsInstallerPath).ToLower()
			if ($ext -notin @('.exe', '.msi'))
			{
				Write-Error "SsrsInstallerPath: Nur .exe oder .msi-Dateien sind gueltig (angegeben: '$SsrsInstallerPath')."
				return
			}
			$globalConfig['SsrsInstallerPath'] = $SsrsInstallerPath
			$updated = $true
		}
		else
		{
			Write-Error "SsrsInstallerPath darf nicht leer sein."
			return
		}
	}
	
	# Check-Profil und Grenzwerte
	if ($PSBoundParameters.ContainsKey('CheckProfile'))
	{
		$globalConfig['CheckProfile'] = $CheckProfile
		$updated = $true
	}
	if ($PSBoundParameters.ContainsKey('CheckCostThresholdMin'))
	{
		$globalConfig['CheckCostThresholdMin'] = $CheckCostThresholdMin
		$updated = $true
	}
	if ($PSBoundParameters.ContainsKey('CheckTempDbMaxFiles'))
	{
		$globalConfig['CheckTempDbMaxFiles'] = $CheckTempDbMaxFiles
		$updated = $true
	}
	if ($PSBoundParameters.ContainsKey('CheckDiskBlockSize'))
	{
		$globalConfig['CheckDiskBlockSize'] = $CheckDiskBlockSize
		$updated = $true
	}

	# Ausgabe-Sprache
	if ($PSBoundParameters.ContainsKey('Language'))
	{
		$globalConfig['Language'] = $Language
		$script:_strings = $null    # String-Cache invalidieren
		$updated = $true
	}

	if (-not $updated)
	{
		Write-Warning "Es wurde kein gueltiger Konfigurationsparameter angegeben."
		return
	}
	
	# Persistenz: JSON-Datei schreiben
	$configFile = Join-Path $env:APPDATA "MSSQLTools\config.json"
	$configDir = Split-Path $configFile -Parent
	if (-not (Test-Path $configDir))
	{
		New-Item -ItemType Directory -Path $configDir -Force | Out-Null
	}
	$globalConfig | ConvertTo-Json -Depth 10 | Set-Content -Path $configFile -Force
	Write-Verbose "Konfiguration gespeichert: $configFile"
	
	if ($PassThru)
	{
		return Get-sqmConfig
	}
}

Set-sqmSsrsConfiguration

Set-sqmSsrsConfiguration -ComputerName "SSRS01" -DatabaseServer "AG_Listener" -PbmPolicyName "Password Policy"

Set-sqmTcpPort -SqlInstance 'MSSQLSERVER' -BasePort 1433

Set-sqmTcpPort -SqlInstance 'INST01' -BasePort 1433 -PortIncrement 10

Export-sqmDatabaseDocumentation

Export-sqmDatabaseDocumentation -SqlInstance "SQL01" -Database "SalesDB","HRApp" -OutputPath "D:\Reports"

Export-sqmDatabaseDocumentation -SqlInstance "SQL01" -IncludeSystemDatabases -ContinueOnError

    "SQL01","SQL02","SQL03" | Export-sqmDatabaseDocumentation -ContinueOnError

Find-sqmDatabaseObject -SqlInstance "SQL01" -ObjectName "sp_GetOrders"

Find-sqmDatabaseObject -SqlInstance "SQL01" -ObjectName "*log*" -ObjectType "TABLE","VIEW" -Database "Sales*"

Get-sqmLinkedServerUsage -SqlInstance "SQL01" -LinkedServer "PROD_SRV"

Invoke-sqmInstanceInventory

Invoke-sqmInstanceInventory -SqlInstance "SQL01", "SQL02" -ContinueOnError

    if (-not $info.Success) { Write-Error $info.ErrorMessage; return }
    $info.ClusterName
    $info.Nodes | Format-Table
    $info.Roles | Where-Object OwnerNode -eq "Node1" | Select Name, IPAddresses

    Queries the local cluster and returns all roles including the core group.

    Checks the directories of the default SSAS instance and corrects missing permissions.

    Shows which permissions would be set for the named instance.

    Test-sqmSSISPackageCompatibility -SqlInstance "NewServer2025" -TargetVersion 2025

    Test-sqmSSISPackageCompatibility -PackagePath "C:\OldPackages" -TargetVersion 2025 -Recurse

    Test-sqmSSISPackageCompatibility -SqlInstance "NewServer2025" `
      -PackagePath "C:\OldPackages" -TargetVersion 2025

Get-sqmCertificateReport

Get-sqmCertificateReport -SqlInstance "SQL01","SQL02" -WarningThresholdDays 180

    Get-sqmCertificateReport -SqlInstance "SQL01" |
        Select-Object -ExpandProperty Certificates |
        Where-Object { $_.ExpiryStatus -ne 'OK' } |
        Select-Object SqlInstance, DatabaseName, CertificateName, ExpiryDate, DaysRemaining, ExpiryStatus, Purpose

    'SQL01','SQL02','SQL03' | Get-sqmCertificateReport -OutputPath "D:\Reports\Certs"

    Audits all SQL Server instances on the local computer and saves results to the default log folder.

    Audits SQL01 and SQL02, saves reports to D:\Reports.

    Get-sqmTlsStatus -ComputerName "SQL01" -Credential $cred | Where-Object Status -ne "OK"
    Audits SQL01 with explicit credentials and filters for non-OK results.

    Install-sqmCertificate -SqlInstance "SQL01" -CertFile "C:\Certs\sql01.pfx" `
        -CertPassword (Read-Host -AsSecureString) -Purpose AlwaysOn

    Install-sqmCertificate -SqlInstance "SQL02" -CertFile "C:\Certs\SQL01_AG_CERT.cer" `
        -CertificateName "SQL01_AG_CERT" -Purpose AlwaysOn

    Install-sqmCertificate -SqlInstance "SQL01" `
        -CertFile "C:\Certs\tde_new.cer" `
        -PrivateKeyFile "C:\Certs\tde_new.pvk" `
        -CertPassword (Read-Host -AsSecureString "PVK password") `
        -Purpose TDE -TdeDatabaseName "ProdDB"

    Install-sqmCertificate -SqlInstance "SQL01" -CertFile "C:\Certs\ssl.pfx" `
        -CertPassword (Read-Host -AsSecureString) -Purpose SSL -SetSqlServerSslCert

    $nodes = 'SQL-AG-01', 'SQL-AG-02', 'SQL-AG-03'
    Install-sqmCertificateToStore -CertFile 'C:\Certs\CompanyRootCA.cer' `
        -StoreName Root -ComputerName $nodes

    Install-sqmCertificateToStore -CertFile 'C:\Certs\SQL-PROD-01.cer' `
        -StoreName TrustedPeople -ComputerName 'ADMINWS-01'

    $replicas = 'SQL-AG-02', 'SQL-AG-03'
    Install-sqmCertificateToStore -CertFile 'C:\Certs\SQL-AG-01_AG_CERT.cer' `
        -StoreName My -ComputerName $replicas

    $pwd = Read-Host -AsSecureString 'PFX password'
    Install-sqmCertificateToStore -CertFile 'C:\Certs\sql-ssl.pfx' `
        -StoreName My -CertPassword $pwd

    New-sqmCertificateRequest -SqlInstance "SQL01" -CertificateName "AG_CERT"

    New-sqmCertificateRequest -SqlInstance "SQL01" -Purpose "SSL" `
        -Subject "CN=SQL01.firma.de,O=Firma GmbH,L=Muenchen,C=DE" `
        -SubjectAlternativeNames @("sql01.firma.de","sql01","192.168.1.10") `
        -KeyLength 4096 -ValidityYears 2

    New-sqmCertificateRequest -SqlInstance "SQL01" -CertificateName "TLS_CERT" `
        -OutputPath "D:\CertRequests"

    New-sqmSqlCertificate -SqlInstance "SQL01" -CertificateName "AG_CERT" -BackupEncryptionPassword (Read-Host -AsSecureString)

    New-sqmSqlCertificate -SqlInstance "SQL01" -CertificateName "AG_CERT" `
        -ValidityYears 10 -BindEndpoint `
        -BackupEncryptionPassword (Read-Host -AsSecureString "Backup-Passwort")

    New-sqmSqlCertificate -SqlInstance "SQL01" -CertificateName "TDE_PROD" `
        -BindTde -BackupEncryptionPassword (Read-Host -AsSecureString "Backup-Passwort")

    Binds the specified certificate to the default instance on SQL01.
    Service restart must be performed manually.

    Binds the certificate to the named instance INST1, enables Force Encryption,
    and restarts the SQL Server service automatically.

    Shows what would be done for the local default instance without making changes.
    Thumbprint spaces are stripped automatically.

		Binds the specified certificate to SSRS on the local machine on port 443.
		The SSRS instance is auto-detected.

		Binds the certificate to Power BI Report Server on REPSERVER01, port 8443,
		and requires HTTPS (SecureConnectionLevel = 1).

		Set-sqmSsrsHttpsCertificate -ComputerName "REPSERVER02" -Thumbprint "A1B2C3D4E5F6A1B2C3D4E5F6A1B2C3D4E5F6A1B2" -Credential $cred -WhatIf
		Shows what changes would be made on REPSERVER02 without applying them.

Invoke-sqmTsmConfiguration -ManagementClass MC_B_NL.NL_42.42.NA

Invoke-sqmTsmConfiguration -ComputerName "SQL01" -UseDiff

Invoke-sqmTsmConfiguration -ComputerName "SQL01" -AdditionalIncludePaths "E:\Archive"

Test-sqmTsmConnection

#>
function Test-sqmTsmConnection
{
	[CmdletBinding(SupportsShouldProcess = $true, ConfirmImpact = 'None')]
	[OutputType([PSCustomObject])]
	param (
		[Parameter(Mandatory = $false)]
		[string]$ComputerName = $env:COMPUTERNAME,
		[Parameter(Mandatory = $false)]
		[string]$DsmadmcPath,
		[Parameter(Mandatory = $false)]
		[string]$UserName,
		[Parameter(Mandatory = $false)]
		[System.Security.SecureString]$Password,
		[Parameter(Mandatory = $false)]
		[string]$ServerName,
		[Parameter(Mandatory = $false)]
		[string]$DsmOptPath,
		[Parameter(Mandatory = $false)]
		[System.Management.Automation.PSCredential]$Credential,
		[Parameter(Mandatory = $false)]
		[switch]$EnableException
	)
	
	begin
	{
		$functionName = $MyInvocation.MyCommand.Name
		Invoke-sqmLogging -Message "Starte $functionName auf $ComputerName" -FunctionName $functionName -Level "INFO"
	}
	
	process
	{
		$result = [PSCustomObject]@{
			Success	    = $false
			Message	    = $null
			DsmadmcPath = $null
			ServerName  = $null
			UserName    = $null
			Output	    = $null
			ErrorOutput = $null
		}
		
		try
		{
			# ---- 1. dsmadmc.exe Pfad ermitteln ----
			$dsmadmc = if ($DsmadmcPath) { $DsmadmcPath }
			else { _FindDsmadmcPath -ComputerName $ComputerName -Credential $Credential }
			if (-not $dsmadmc)
			{
				$msg = "dsmadmc nicht gefunden. Bitte TSM-Client installieren oder -DsmadmcPath angeben."
				Invoke-sqmLogging -Message $msg -FunctionName $functionName -Level "ERROR"
				if ($EnableException) { throw $msg }
				$result.Message = $msg
				return $result
			}
			$result.DsmadmcPath = $dsmadmc
			Invoke-sqmLogging -Message "Verwende dsmadmc: $dsmadmc" -FunctionName $functionName -Level "VERBOSE"
			
			# ---- 2. TSM-Konfiguration aus dsm.opt lesen (falls nicht alle Parameter angegeben) ----
			$effUserName = $UserName
			$effPassword = $Password
			$effServerName = $ServerName
			
			if (-not $effUserName -or -not $effPassword -or -not $effServerName)
			{
				$cfg = Get-sqmTsmConfiguration -ComputerName $ComputerName -DsmOptPath $DsmOptPath -Credential $Credential -IncludePasswordPlain -ErrorAction Stop
				if (-not $cfg.Success)
				{
					throw "TSM-Konfiguration konnte nicht gelesen werden: $($cfg.ErrorMessage)"
				}
				if (-not $effServerName) { $effServerName = $cfg.ServerName }
				if (-not $effUserName) { $effUserName = $cfg.UserName }
				if (-not $effPassword -and $cfg.Password) { $effPassword = $cfg.Password }
			}
			
			if (-not $effUserName)
			{
				$msg = "Kein TSM-Benutzername angegeben und kein USERID in dsm.opt gefunden."
				Invoke-sqmLogging -Message $msg -FunctionName $functionName -Level "ERROR"
				if ($EnableException) { throw $msg }
				$result.Message = $msg
				return $result
			}
			if (-not $effPassword)
			{
				$msg = "Kein TSM-Kennwort angegeben und kein PASSWORD in dsm.opt gefunden."
				Invoke-sqmLogging -Message $msg -FunctionName $functionName -Level "ERROR"
				if ($EnableException) { throw $msg }
				$result.Message = $msg
				return $result
			}
			if (-not $effServerName)
			{
				$msg = "Kein TSM-Server angegeben und kein TCPServeraddress in dsm.opt gefunden."
				Invoke-sqmLogging -Message $msg -FunctionName $functionName -Level "ERROR"
				if ($EnableException) { throw $msg }
				$result.Message = $msg
				return $result
			}
			$result.UserName = $effUserName
			$result.ServerName = $effServerName
			Invoke-sqmLogging -Message "TSM-Server: $effServerName, Benutzer: $effUserName" -FunctionName $functionName -Level "INFO"
			
			# ---- 3. Kennwort aus SecureString extrahieren ----
			$plainPwd = _SecureToPlain $effPassword
			
			# ---- 4. dsmadmc-Befehl aufbauen ----
			$cmdArgs = "-id=$effUserName -password=$plainPwd -se=$effServerName -dataonly=yes show version"
			Invoke-sqmLogging -Message "Fuehre dsmadmc aus: $dsmadmc $cmdArgs" -FunctionName $functionName -Level "VERBOSE"
			
			# ---- 5. Befehl ausfuehren ----
			$output = $null
			$errorOut = $null
			$exitCode = 0
			$isLocal = $ComputerName -in @($env:COMPUTERNAME, 'localhost', '127.0.0.1', '.')
			
			if ($PSCmdlet.ShouldProcess("TSM-Verbindung zu $effServerName mit Benutzer $effUserName", "Pruefen"))
			{
				if ($isLocal)
				{
					$psi = New-Object System.Diagnostics.ProcessStartInfo
					$psi.FileName = $dsmadmc
					$psi.Arguments = $cmdArgs
					$psi.UseShellExecute = $false
					$psi.RedirectStandardOutput = $true
					$psi.RedirectStandardError = $true
					$psi.CreateNoWindow = $true
					$p = [System.Diagnostics.Process]::Start($psi)
					$output = $p.StandardOutput.ReadToEnd()
					$errorOut = $p.StandardError.ReadToEnd()
					$p.WaitForExit()
					$exitCode = $p.ExitCode
				}
				else
				{
					$scriptBlock = {
						param ($exe,
							$args)
						$psi = New-Object System.Diagnostics.ProcessStartInfo
						$psi.FileName = $exe
						$psi.Arguments = $args
						$psi.UseShellExecute = $false
						$psi.RedirectStandardOutput = $true
						$psi.RedirectStandardError = $true
						$psi.CreateNoWindow = $true
						$p = [System.Diagnostics.Process]::Start($psi)
						$out = $p.StandardOutput.ReadToEnd()
						$err = $p.StandardError.ReadToEnd()
						$p.WaitForExit()
						return @{ ExitCode = $p.ExitCode; Output = $out; Error = $err }
					}
					$session = New-PSSession -ComputerName $ComputerName -Credential $Credential -ErrorAction Stop
					$remoteResult = Invoke-Command -Session $session -ScriptBlock $scriptBlock -ArgumentList $dsmadmc, $cmdArgs -ErrorAction Stop
					$exitCode = $remoteResult.ExitCode
					$output = $remoteResult.Output
					$errorOut = $remoteResult.Error
					Remove-PSSession $session
				}
				
				$result.Output = $output
				$result.ErrorOutput = $errorOut
				
				if ($exitCode -eq 0 -and $output -match 'IBM Spectrum Protect')
				{
					$result.Success = $true
					$result.Message = "Verbindung zu TSM-Server '$effServerName' mit Benutzer '$effUserName' erfolgreich."
					Invoke-sqmLogging -Message $result.Message -FunctionName $functionName -Level "INFO"
				}
				else
				{
					$result.Success = $false
					$result.Message = "Fehler bei TSM-Verbindung (Exitcode $exitCode). Ausgabe: $output $errorOut".Trim()
					Invoke-sqmLogging -Message $result.Message -FunctionName $functionName -Level "ERROR"
				}
			}
			else
			{
				$result.Success = $false
				$result.Message = "WhatIf: Verbindungstest wuerde ausgefuehrt."
				Invoke-sqmLogging -Message $result.Message -FunctionName $functionName -Level "VERBOSE"
			}
		}
		catch
		{
			$errMsg = "Allgemeiner Fehler: $($_.Exception.Message)"
			Invoke-sqmLogging -Message $errMsg -FunctionName $functionName -Level "ERROR"
			if ($EnableException) { throw }
			$result.Message = $errMsg
		}
		return $result
	}
	
	end
	{
		Invoke-sqmLogging -Message "$functionName abgeschlossen." -FunctionName $functionName -Level "INFO"
	}
}

# ---- Hilfsfunktionen (lokal) ----
function _FindDsmadmcPath
{
	param ([string]$ComputerName,
		[System.Management.Automation.PSCredential]$Credential)
	
	$isLocal = $ComputerName -in @($env:COMPUTERNAME, 'localhost', '127.0.0.1', '.')
	$candidates = [System.Collections.Generic.List[string]]::new()
	
	if ($isLocal)
	{
		try
		{
			$regPath = 'HKLM:\SOFTWARE\IBM\ADSM\CurrentVersion'
			$installPath = (Get-ItemProperty $regPath -Name 'InstallPath' -ErrorAction SilentlyContinue).InstallPath
			# KORREKTUR: Doppelte Klammern fuer den Methodenaufruf
			if ($installPath) { $candidates.Add((Join-Path $installPath 'dsmadmc.exe')) }
		}
		catch { }
		
		if ($env:DSM_DIR) { $candidates.Add((Join-Path $env:DSM_DIR 'dsmadmc.exe')) }
		
		$candidates.Add('C:\Program Files\Tivoli\TSM\baclient\dsmadmc.exe')
		$candidates.Add('C:\Program Files\IBM\TSM\baclient\dsmadmc.exe')
		$candidates.Add('C:\Program Files\IBM\SpectrumProtect\baclient\dsmadmc.exe')
	}
	else
	{
		# Remote-Logik... (hier ebenfalls Klammern pruefen falls Join-Path genutzt wird)
	}
	
	foreach ($c in $candidates)
	{
		if (Test-Path $c) { return $c }
	}
	return $null
}

    Invoke-sqmDeployScripts -SqlInstance "SQLSERVER01" -Database "SalesDB" `
        -ScriptPath "D:\Deploy\v2.3" -LogPath "D:\Logs\Deploy"

    Invoke-sqmDeployScripts -SqlInstance "SQLSERVER01" -Database "SalesDB" `
        -ScriptPath "D:\Deploy\v2.3" -LogPath "D:\Logs\Deploy" -JobNumber "AU-2026-042"

    Invoke-sqmDeployScripts -SqlInstance "SQLSERVER01" -Database "SalesDB" `
        -ScriptPath "D:\Deploy\v2.3" -LogPath "D:\Logs\Deploy" -SkipBackup

    Invoke-sqmDeployScripts -SqlInstance "SQLSERVER01" -Database "SalesDB" `
        -ScriptPath "D:\Deploy\v2.3" -LogPath "D:\Logs\Deploy" -WhatIf

    Invoke-sqmDeployScripts -SqlInstance "SQLSERVER01" -Database "SalesDB" `
        -ScriptPath "D:\Deploy\v2.3" -LogPath "D:\Logs\Deploy" -NoWrapTransaction

    $cred = Get-Credential
    Invoke-sqmDeployScripts -SqlInstance "SQLSERVER01" -Database "SalesDB" `
        -ScriptPath "D:\Deploy\v2.3" -LogPath "D:\Logs\Deploy" -SqlCredential $cred

    Invoke-sqmSignModule -ModulePath "C:\Dev\MyModule" `
        -CertificateThumbprint "AB12CD34EF56..."

    $pwd = ConvertTo-SecureString "secret" -AsPlainText -Force
    Invoke-sqmSignModule -ModulePath "C:\Dev\MyModule" `
        -CertificatePath "C:\Certs\CodeSign.pfx" -CertificatePassword $pwd

    Invoke-sqmSignModule -ModulePath "C:\Dev\MyModule"

    Invoke-sqmSignModule -ModulePath "C:\Dev\MyModule" -WhatIf

    Invoke-sqmSignModule -ModulePath "C:\Dev\MyModule" -Force

    Copies all non-system logins. Policy is disabled/re-enabled,
    AD check and orphan repair run automatically.

    Copies all logins and switches the target server to Mixed Mode if needed.
    Automatically restarts the SQL service if required.

    Copies only logins starting with 'App_' and overwrites existing ones.

    Simulates the operation without policy handling.

Get-sqmAgentJobHistory

Get-sqmAgentJobHistory -JobName '*Backup*' -Status Failure -Since (Get-Date).AddDays(-1)

    Get-sqmDiskBlockSize -Drive 'F', 'G', 'H'

    Get-sqmDiskBlockSize -SqlInstance "SQL01"

    'F','G' | Get-sqmDiskBlockSize

    Get-sqmDiskBlockSize -SqlInstance "SQL01" | Where-Object { -not $_.IsRecommended }

Get-sqmHpuAllowGroup

#>
function Get-sqmHpuAllowGroup
{
    [CmdletBinding()]
    [OutputType([string])]
    param (
        [Parameter(Mandatory = $false)]
        [switch]$EnableException
    )
    $functionName = $MyInvocation.MyCommand.Name
    # ?? 1. Domain des lokalen Rechners ermitteln ????????????????????????????
    try
    {
        $currentDomain = (Get-CimInstance -ClassName Win32_ComputerSystem -ErrorAction Stop).Domain
    }
    catch
    {
        $msg = "Domain konnte nicht ermittelt werden: $($_.Exception.Message)"
        Invoke-sqmLogging -Message $msg -FunctionName $functionName -Level 'ERROR'
        if ($EnableException) { throw $msg }
        return $null
    }
    Invoke-sqmLogging -Message "Starte $functionName. Lokale Domain: '$currentDomain'" `
                      -FunctionName $functionName -Level 'INFO'
    # ?? 2. Mapping aus der Konfiguration lesen ??????????????????????????????
    $domainGroupMap = Get-sqmConfig -Key 'HpuDomainGroupMap'
    if (-not $domainGroupMap -or $domainGroupMap.Count -eq 0)
    {
        $msg = "Konfigurationsschluessel 'HpuDomainGroupMap' ist nicht gesetzt. " +
               "Bitte Set-sqmConfig -HpuDomainGroupMap <...> ausfuehren."
        Invoke-sqmLogging -Message $msg -FunctionName $functionName -Level 'ERROR'
        if ($EnableException) { throw $msg }
        Write-Warning $msg
        return $null
    }
    # ?? 3. Passenden Mapping-Eintrag per Wildcard-Match suchen ?????????????
    $matchedEntry = $null
    foreach ($entry in $domainGroupMap)
    {
        if ($currentDomain -like $entry.DomainPattern)
        {
            $matchedEntry = $entry
            Invoke-sqmLogging -Message "Domain '$currentDomain' trifft Pattern '$($entry.DomainPattern)'." `
                              -FunctionName $functionName -Level 'INFO'
            break
        }
    }
    if (-not $matchedEntry)
    {
        $msg = "Kein Mapping-Eintrag fuer Domain '$currentDomain' gefunden."
        Invoke-sqmLogging -Message $msg -FunctionName $functionName -Level 'WARNING'
        if ($EnableException) { throw $msg }
        Write-Warning $msg
        return $null
    }
    $groupNamePattern = $matchedEntry.GroupNamePattern
    # ?? 4. AD-Suche ????????????????????????????????????????????????????????
    try
    {
        $ldapFilter = "(&(objectCategory=group)(sAMAccountName=*$groupNamePattern))"
        Invoke-sqmLogging -Message "LDAP-Filter: $ldapFilter" -FunctionName $functionName -Level 'INFO'
        $searcher                    = [adsisearcher]$ldapFilter
        $searcher.PageSize           = 20
        foreach ($prop in @('name', 'distinguishedname'))
        {
            $searcher.PropertiesToLoad.Add($prop) | Out-Null
        }
        $result = $searcher.FindOne()
    }
    catch
    {
        $msg = "AD-Suche fehlgeschlagen: $($_.Exception.Message)"
        Invoke-sqmLogging -Message $msg -FunctionName $functionName -Level 'ERROR'
        if ($EnableException) { throw $msg }
        Write-Warning $msg
        return $null
    }
    # ?? 5. Ergebnis auswerten ???????????????????????????????????????????????
    if ($null -eq $result)
    {
        $msg = "Keine HPU-Allow-Gruppe '*$groupNamePattern' in Domain '$currentDomain' gefunden."
        Invoke-sqmLogging -Message $msg -FunctionName $functionName -Level 'WARNING'
        if ($EnableException) { throw $msg }
        Write-Warning $msg
        return $null
    }
    $dn = ($result.Properties['distinguishedname'])[0]
    Invoke-sqmLogging -Message "Gruppe gefunden: $dn" -FunctionName $functionName -Level 'INFO'
    return $dn
}

    Get-sqmServerHardwareReport

    Get-sqmServerHardwareReport -ComputerName "SQL01"

    Get-sqmServerHardwareReport -ComputerName "SQL01","SQL02","SQL03" -ReportPath "C:\Reports"

    $path = Get-sqmServerHardwareReport -ComputerName "SQL01" -NoOpen -PassThru
    Write-Host "Report: $path"

    Get-sqmServerHardwareReport -ComputerName "SQL01","SQL02" -OutputFormat CSV -NoOpen

    Get-sqmServerHardwareReport -ComputerName "SQL01" -OutputFormat All -NoOpen -PassThru

    Checks whether the AD module is present and installs it if necessary.

    Returns $false if installation fails instead of throwing an exception.

    {
        Write-Warning "AD module not available - AD check will be skipped."
    }

Install-sqmDb2Driver -SourcePath '\\srv\Treiber\DB2'

Install-sqmDb2Driver -SourcePath 'C:\Downloads\db2_odbc_cli_64.exe'

Install-sqmJdbcDriver -SourcePath '\\srv\Treiber\JDBC'

Install-sqmJdbcDriver -SourcePath 'C:\Downloads\jdbc' -UpdateClassPath $true

Install-sqmOdbcDriver -SourcePath '\\srv\Treiber\ODBC'

Install-sqmOdbcDriver -SourcePath 'C:\Setup\msodbcsql18.msi'

Install-sqmOlaMaintenanceSolution -SqlInstance "SQL01"

Install-sqmOlaMaintenanceSolution -SqlInstance "SQL01" -Force

    Installs SSRS using the installer path stored in sqmConfig,
    Edition Developer, followed by full configuration with default values.

        -InstallerPath '\\srv-share\Software\SSRS2022\SQLServerReportingServices.exe' `
        -Edition Standard `
        -DatabaseServer 'SQL-AG-Listener' `
        -ServiceAccount 'DOMAIN\svc_ssrs' `
        -EncryptionKeyPassword (Read-Host -AsSecureString 'Key-Passwort')

    Shows what would be installed without making any changes.

Invoke-sqmMonitoringKey

Invoke-sqmMonitoringKey -Operation Set -SQL Standard -TSM 1 -AutoDetectSQLFreeSpaceVersion

#>
function Invoke-sqmMonitoringKey
{
	[CmdletBinding(SupportsShouldProcess = $true, ConfirmImpact = 'None')]
	[OutputType([PSCustomObject])]
	param (
		[Parameter(Mandatory = $false, ValueFromPipeline = $true)]
		[Alias('Computer', 'Server')]
		[string[]]$ComputerName = @($env:COMPUTERNAME),
		[Parameter(Mandatory = $false)]
		[ValidateSet('Get', 'Set')]
		[string]$Operation = 'Get',
		[Parameter(Mandatory = $false)]
		[ValidateSet('None', 'Standard', 'Full')]
		[string]$SQL,
		[Parameter(Mandatory = $false)]
		[ValidateSet('Standard', 'Cluster')]
		[string]$SQLFreeSpaceVersion,
		[Parameter(Mandatory = $false)]
		[ValidateSet(0, 1)]
		[Nullable[int]]$TSM,
		[Parameter(Mandatory = $false)]
		[string]$RegistryBase = 'System',
		[Parameter(Mandatory = $false)]
		[switch]$AutoDetectSQLFreeSpaceVersion,
		[Parameter(Mandatory = $false)]
		[System.Management.Automation.PSCredential]$Credential,
		[Parameter(Mandatory = $false)]
		[switch]$ContinueOnError,
		[Parameter(Mandatory = $false)]
		[switch]$EnableException
	)
	
	begin
	{
		$functionName = $MyInvocation.MyCommand.Name
		$regSubKey = "$RegistryBase\dtcSoftware\sqmSQLTool"
		$regPath = "HKLM:\$regSubKey"
		
		$sqlToDword = @{ 'None' = 0; 'Standard' = 1; 'Full' = 2 }
		$sqlDesc = @{ 0 = 'NoMonitoring'; 1 = 'ServiceMonitoring'; 2 = 'FullMonitoring' }
		$tsmDesc = @{ 0 = 'Inactive'; 1 = 'Active' }
		
		$allResults = [System.Collections.Generic.List[PSCustomObject]]::new()
	}
	
	process
	{
		foreach ($computer in $ComputerName)
		{
			try
			{
				Invoke-sqmLogging -Message "[$computer] Operation: $Operation" -FunctionName $functionName -Level "INFO"
				
				$effectiveFreeSpaceVersion = $SQLFreeSpaceVersion
				
				# AutoDetect (nur bei Set)
				if ($Operation -eq 'Set' -and $AutoDetectSQLFreeSpaceVersion -and [string]::IsNullOrWhiteSpace($effectiveFreeSpaceVersion))
				{
					Invoke-sqmLogging -Message "[$computer] AutoDetect SQLFreeSpaceVersion ..." -FunctionName $functionName -Level "INFO"
					try
					{
						if (-not (Get-Module -ListAvailable -Name dbatools))
						{
							Invoke-sqmLogging -Message "dbatools nicht gefunden - AutoDetect uebersprungen, verwende 'Standard'." -FunctionName $functionName -Level "WARNING"
							$effectiveFreeSpaceVersion = 'Standard'
						}
						else
						{
							$agCheck = Get-DbaAvailabilityGroup -SqlInstance $computer -ErrorAction SilentlyContinue
							$effectiveFreeSpaceVersion = if ($agCheck) { 'Cluster' }
							else { 'Standard' }
							Invoke-sqmLogging -Message "[$computer] AutoDetect Ergebnis: $effectiveFreeSpaceVersion" -FunctionName $functionName -Level "INFO"
						}
					}
					catch
					{
						Invoke-sqmLogging -Message "[$computer] AutoDetect fehlgeschlagen: $($_.Exception.Message)" -FunctionName $functionName -Level "WARNING"
						$effectiveFreeSpaceVersion = 'Standard'
					}
				}
				
				# Schreibvorgang
				if ($Operation -eq 'Set')
				{
					if ([string]::IsNullOrWhiteSpace($SQL) -and [string]::IsNullOrWhiteSpace($effectiveFreeSpaceVersion) -and $null -eq $TSM)
					{
						Invoke-sqmLogging -Message "[$computer] Keine Werte zum Setzen angegeben." -FunctionName $functionName -Level "WARNING"
						continue
					}
					
					if ($PSCmdlet.ShouldProcess($computer, "Setze Monitoring-Registry-Werte in '$regPath'"))
					{
						# Stelle sicher, dass der Schluessel existiert (lokal/remote)
						$keyExists = $false
						try
						{
							if ($computer -eq $env:COMPUTERNAME -or $computer -eq 'localhost' -or $computer -eq '.')
							{
								$fullPath = "HKLM:\$regSubKey"
								if (-not (Test-Path $fullPath))
								{
									New-Item -Path $fullPath -Force -ErrorAction Stop | Out-Null
									Invoke-sqmLogging -Message "[$computer] Registry-Schluessel erstellt: $fullPath" -FunctionName $functionName -Level "INFO"
								}
								$keyExists = $true
							}
							else
							{
								# Remote: Pruefe/Erstelle ueber Invoke-Command
								$sb = {
									param ($sk)
									$fullPath = "HKLM:\$sk"
									if (-not (Test-Path $fullPath))
									{
										New-Item -Path $fullPath -Force -ErrorAction Stop | Out-Null
										Write-Output "CREATED"
									}
									else
									{
										Write-Output "EXISTS"
									}
								}
								$invokeParams = @{
									ComputerName = $computer
									ScriptBlock  = $sb
									ArgumentList = $regSubKey
									ErrorAction  = 'Stop'
								}
								if ($Credential) { $invokeParams['Credential'] = $Credential }
								$result = Invoke-Command @invokeParams
								if ($result -eq 'CREATED')
								{
									Invoke-sqmLogging -Message "[$computer] Registry-Schluessel remote erstellt: HKLM:\$regSubKey" -FunctionName $functionName -Level "INFO"
								}
								$keyExists = $true
							}
						}
						catch
						{
							Invoke-sqmLogging -Message "[$computer] Fehler bei Schluesselerstellung: $($_.Exception.Message)" -FunctionName $functionName -Level "ERROR"
							throw
						}
						
						# Nun Werte setzen
						$values = @{ }
						if (-not [string]::IsNullOrWhiteSpace($SQL)) { $values['SQL'] = $sqlToDword[$SQL] }
						if (-not [string]::IsNullOrWhiteSpace($effectiveFreeSpaceVersion)) { $values['SQLFreeSpaceVersion'] = $effectiveFreeSpaceVersion }
						if ($null -ne $TSM) { $values['TSM'] = [int]$TSM }
						
						if ($computer -eq $env:COMPUTERNAME -or $computer -eq 'localhost' -or $computer -eq '.')
						{
							$fullPath = "HKLM:\$regSubKey"
							foreach ($entry in $values.GetEnumerator())
							{
								$type = if ($entry.Value -is [int]) { 'DWord' }
								else { 'String' }
								Set-ItemProperty -Path $fullPath -Name $entry.Key -Value $entry.Value -Type $type -ErrorAction Stop
							}
						}
						else
						{
							$sb2 = {
								param ($sk,
									$vals)
								$fullPath = "HKLM:\$sk"
								foreach ($entry in $vals.GetEnumerator())
								{
									$type = if ($entry.Value -is [int]) { 'DWord' }
									else { 'String' }
									Set-ItemProperty -Path $fullPath -Name $entry.Key -Value $entry.Value -Type $type -ErrorAction Stop
								}
								return $true
							}
							$invokeParams2 = @{
								ComputerName = $computer
								ScriptBlock  = $sb2
								ArgumentList = $regSubKey, $values
								ErrorAction  = 'Stop'
							}
							if ($Credential) { $invokeParams2['Credential'] = $Credential }
							Invoke-Command @invokeParams2 | Out-Null
						}
						Invoke-sqmLogging -Message "[$computer] Werte erfolgreich gesetzt." -FunctionName $functionName -Level "INFO"
					}
					else
					{
						Invoke-sqmLogging -Message "[$computer] WhatIf: Schreibvorgang uebersprungen." -FunctionName $functionName -Level "VERBOSE"
					}
				}
				
				# Lesen (immer, auch nach Set)
				# Hier wird der Schluessel NICHT erstellt - nur lesen
				$current = $null
				try
				{
					if ($computer -eq $env:COMPUTERNAME -or $computer -eq 'localhost' -or $computer -eq '.')
					{
						$fullPath = "HKLM:\$regSubKey"
						if (Test-Path $fullPath)
						{
							$key = Get-ItemProperty -Path $fullPath -ErrorAction Stop
							$current = @{
								SQL = $key.SQL
								SQLFreeSpaceVersion = $key.SQLFreeSpaceVersion
								TSM = $key.TSM
								_KeyExists = $true
							}
						}
						else
						{
							$current = @{ _KeyExists = $false }
						}
					}
					else
					{
						$sbRead = {
							param ($sk)
							$fullPath = "HKLM:\$sk"
							if (Test-Path $fullPath)
							{
								$key = Get-ItemProperty -Path $fullPath -ErrorAction Stop
								return @{
									SQL = $key.SQL
									SQLFreeSpaceVersion = $key.SQLFreeSpaceVersion
									TSM = $key.TSM
									_KeyExists = $true
								}
							}
							else
							{
								return @{ _KeyExists = $false }
							}
						}
						$invokeRead = @{
							ComputerName = $computer
							ScriptBlock  = $sbRead
							ArgumentList = $regSubKey
							ErrorAction  = 'Stop'
						}
						if ($Credential) { $invokeRead['Credential'] = $Credential }
						$current = Invoke-Command @invokeRead
					}
				}
				catch
				{
					throw "Registry-Lesen fehlgeschlagen: $($_.Exception.Message)"
				}
				
				$status = if ($Operation -eq 'Set') { if ($current._KeyExists) { 'Updated' }
					else { 'Created' } }
				elseif ($current._KeyExists) { 'OK' }
				else { 'KeyNotFound' }
				
				$sqlVal = $current.SQL
				$tsmVal = $current.TSM
				$fsvVal = $current.SQLFreeSpaceVersion
				$sqlText = if ($null -ne $sqlVal -and $sqlDesc.ContainsKey([int]$sqlVal)) { $sqlDesc[[int]$sqlVal] }
				else { '(nicht gesetzt)' }
				$tsmText = if ($null -ne $tsmVal -and $tsmDesc.ContainsKey([int]$tsmVal)) { $tsmDesc[[int]$tsmVal] }
				else { '(nicht gesetzt)' }
				
				$msg = switch ($status)
				{
					'KeyNotFound' { "Registry-Schluessel '$regPath' nicht vorhanden." }
					'Created'     { "Schluessel neu erstellt und Werte gesetzt." }
					'Updated'     { "Werte aktualisiert." }
					default       { "Werte erfolgreich ausgelesen." }
				}
				
				$allResults.Add([PSCustomObject]@{
						ComputerName = $computer
						RegistryPath = "HKLM:\$regSubKey"
						SQL		     = $sqlVal
						SQL_Description = $sqlText
						SQLFreeSpaceVersion = $fsvVal
						TSM		     = $tsmVal
						TSM_Description = $tsmText
						Status	     = $status
						Message	     = $msg
					})
			}
			catch
			{
				$errMsg = $_.Exception.Message
				Invoke-sqmLogging -Message "[$computer] Fehler: $errMsg" -FunctionName $functionName -Level "ERROR"
				$allResults.Add([PSCustomObject]@{
						ComputerName = $computer
						RegistryPath = "HKLM:\$regSubKey"
						SQL		     = $null
						SQL_Description = '(Fehler)'
						SQLFreeSpaceVersion = $null
						TSM		     = $null
						TSM_Description = '(Fehler)'
						Status	     = 'Failed'
						Message	     = $errMsg
					})
				if ($EnableException) { throw }
				if (-not $ContinueOnError) { throw }
			}
		}
	}
	
	end
	{
		Invoke-sqmLogging -Message "$functionName abgeschlossen." -FunctionName $functionName -Level "INFO"
		return $allResults
	}
}

New-sqmOlaMaintenanceJobs -SqlInstance "SQL01"

New-sqmOlaMaintenanceJobs -SqlInstance "SQL01" -ScheduleTime "22:00" -ScheduleDay 64 -OperatorName "DBAs"

    # Gibt SecureString zurueck

    # SecureString + DPAPI-Export nach C:\Temp\sa.txt

    $pwd = New-sqmRandomSaPassword
    $cred = New-Object PSCredential('sa', $pwd)
    $cred.GetNetworkCredential().Password

Repair-sqmAlwaysOnDatabases

Repair-sqmAlwaysOnDatabases -Force

Set-sqmSqlPolicyState -SqlInstance "SQL01" -Policy "xp_cmdshell must be disabled" -State Disable

"SQL01","SQL02" | Set-sqmSqlPolicyState -Policy "Password Policy" -State Enable

Test-sqmDriverInstalled -DriverType ODBC

Test-sqmDriverInstalled -DriverType ODBC -DriverName 'ODBC Driver 18 for SQL Server'

Test-sqmDriverInstalled -DriverType JDBC

Test-sqmDriverInstalled -DriverType DB2

Test-sqmMaxDop -SqlInstance 'MSSQLSERVER'

Test-sqmMaxDop -SqlInstance 'SQL01\INST01'

Test-sqmMaxMemory -SqlInstance 'MSSQLSERVER'

Test-sqmMaxMemory -SqlInstance 'SQL01\INST01' | Where-Object { $_.Status -ne 'OK' }

    # Prueft Default-Instanz MSSQLSERVER

Test-sqmSqlInstanceInstalled -InstanceName 'INST01'

if ((Test-sqmSqlInstanceInstalled).IsInstalled) { Write-Host "SQL installiert" }

Uninstall-sqmDb2Driver

Uninstall-sqmJdbcDriver

Uninstall-sqmJdbcDriver -RemoveClassPath

Uninstall-sqmOdbcDriver

Uninstall-sqmOdbcDriver -DriverName 'Microsoft ODBC Driver 17 for SQL Server'