SQL Server¶

# Discover all SQL Server instances on this machine
$instancePath = "HKLM:\SOFTWARE\Microsoft\Microsoft SQL Server\Instance Names\SQL"
Get-ItemProperty $instancePath -ErrorAction SilentlyContinue

# Read SQL Server installation details
$instanceId = "MSSQL16.MSSQLSERVER"
$setupPath = "HKLM:\SOFTWARE\Microsoft\Microsoft SQL Server\$instanceId\Setup"
Get-ItemProperty $setupPath |
    Select-Object SQLPath, SQLDataRoot, SQLBinRoot, Version, PatchLevel, Edition

# Check SQL Server service configuration
Get-ItemProperty "HKLM:\SYSTEM\CurrentControlSet\Services\MSSQLSERVER" |
    Select-Object DisplayName, ImagePath, Start, ObjectName

# Read memory configuration from registry
$mssqlPath = "HKLM:\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL16.MSSQLSERVER\MSSQLServer"
Get-ItemProperty $mssqlPath -ErrorAction SilentlyContinue |
    Select-Object MinServerMemory, MaxServerMemory

# Set SQL Server memory limits via T-SQL (recommended approach)
$query = @"
EXEC sp_configure 'show advanced options', 1;
RECONFIGURE;
EXEC sp_configure 'min server memory (MB)', 8192;
EXEC sp_configure 'max server memory (MB)', 32768;
RECONFIGURE;
"@
Invoke-Sqlcmd -Query $query -ServerInstance "."

# Calculate recommended MaxServerMemory based on installed RAM
$totalRamMB = (Get-CimInstance Win32_PhysicalMemory | Measure-Object Capacity -Sum).Sum / 1MB
$osReserveMB = [math]::Max(4096, [math]::Ceiling($totalRamMB * 0.1))
$recommendedMax = $totalRamMB - $osReserveMB

Write-Output "Total RAM: $totalRamMB MB"
Write-Output "OS Reserve: $osReserveMB MB"
Write-Output "Recommended MaxServerMemory: $recommendedMax MB"

# Check current SQL Server memory usage
$query = @"
SELECT
    physical_memory_in_use_kb / 1024 AS MemoryUsedMB,
    locked_page_allocations_kb / 1024 AS LockedPagesMB,
    total_virtual_address_space_kb / 1024 AS VirtualMemMB,
    process_physical_memory_low AS MemoryPressureLow,
    process_virtual_memory_low AS VirtualPressureLow
FROM sys.dm_os_process_memory;
"@
Invoke-Sqlcmd -Query $query -ServerInstance "."

# Check network protocol status
$netLibPath = "HKLM:\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL16.MSSQLSERVER\MSSQLServer\SuperSocketNetLib"

$protocols = @("Tcp", "Np", "Sm")
foreach ($proto in $protocols) {
    $protoPath = Join-Path $netLibPath $proto
    $enabled = (Get-ItemProperty $protoPath -ErrorAction SilentlyContinue).Enabled
    Write-Output "$proto : Enabled = $enabled"
}

# Check TCP/IP port configuration
$tcpAllPath = "HKLM:\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL16.MSSQLSERVER\MSSQLServer\SuperSocketNetLib\Tcp\IPAll"
Get-ItemProperty $tcpAllPath | Select-Object TcpPort, TcpDynamicPorts

# Set a static TCP port and disable dynamic ports
$tcpAllPath = "HKLM:\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL16.MSSQLSERVER\MSSQLServer\SuperSocketNetLib\Tcp\IPAll"

# Set static port to 1433
Set-ItemProperty -Path $tcpAllPath -Name "TcpPort" -Value "1433" -Type String

# Clear dynamic port (empty string disables dynamic assignment)
Set-ItemProperty -Path $tcpAllPath -Name "TcpDynamicPorts" -Value "" -Type String

# Restart SQL Server to apply
Restart-Service MSSQLSERVER -Force

# Enable Named Pipes protocol
$npPath = "HKLM:\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL16.MSSQLSERVER\MSSQLServer\SuperSocketNetLib\Np"
Set-ItemProperty -Path $npPath -Name "Enabled" -Value 1 -Type DWord

# Named Pipes pipe name
Get-ItemProperty $npPath -Name "PipeName"

# Check SQL Browser service status
Get-Service SQLBrowser | Select-Object Name, Status, StartType

# Check authentication mode and audit level
$mssqlPath = "HKLM:\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL16.MSSQLSERVER\MSSQLServer"
$props = Get-ItemProperty $mssqlPath
$loginMode = switch ($props.LoginMode) { 1 {"Windows Only"} 2 {"Mixed Mode"} default {"Unknown"} }
$auditLevel = switch ($props.AuditLevel) { 0 {"None"} 1 {"Failed logins"} 2 {"Successful logins"} 3 {"All logins"} default {"Unknown"} }

Write-Output "Authentication Mode: $loginMode"
Write-Output "Login Audit Level: $auditLevel"

# Switch to Windows Authentication only (more secure)
$mssqlPath = "HKLM:\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL16.MSSQLSERVER\MSSQLServer"
Set-ItemProperty -Path $mssqlPath -Name "LoginMode" -Value 1 -Type DWord

# Enable auditing of all login attempts
Set-ItemProperty -Path $mssqlPath -Name "AuditLevel" -Value 3 -Type DWord

# Restart SQL Server to apply
Restart-Service MSSQLSERVER -Force

# Check and enable forced encryption
$netLibPath = "HKLM:\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL16.MSSQLSERVER\MSSQLServer\SuperSocketNetLib"
Get-ItemProperty $netLibPath | Select-Object ForceEncryption, Certificate

# Enable forced encryption with a specific certificate
$netLibPath = "HKLM:\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL16.MSSQLSERVER\MSSQLServer\SuperSocketNetLib"
$certThumbprint = "A1B2C3D4E5F6A1B2C3D4E5F6A1B2C3D4E5F6A1B2"

Set-ItemProperty -Path $netLibPath -Name "ForceEncryption" -Value 1 -Type DWord
Set-ItemProperty -Path $netLibPath -Name "Certificate" -Value $certThumbprint -Type String

# Restart SQL Server to apply
Restart-Service MSSQLSERVER -Force

# Check which databases have TDE enabled
$query = @"
SELECT db.name, db.is_encrypted,
    dm.encryption_state, dm.key_algorithm, dm.key_length
FROM sys.databases db
LEFT JOIN sys.dm_database_encryption_keys dm ON db.database_id = dm.database_id
WHERE db.is_encrypted = 1 OR dm.encryption_state IS NOT NULL;
"@
Invoke-Sqlcmd -Query $query -ServerInstance "."

# Check SQL Agent configuration
$agentPath = "HKLM:\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL16.MSSQLSERVER\SQLServerAgent"
Get-ItemProperty $agentPath -ErrorAction SilentlyContinue |
    Select-Object WorkingDirectory, ErrorLogFile, DatabaseMailProfile

# Check SQL Agent service status
Get-Service SQLSERVERAGENT | Select-Object Name, Status, StartType

# Move SQL Agent working directory to a dedicated volume
$agentPath = "HKLM:\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL16.MSSQLSERVER\SQLServerAgent"

New-Item -Path "D:\SQLAgent\Jobs" -ItemType Directory -Force | Out-Null
Set-ItemProperty -Path $agentPath -Name "WorkingDirectory" -Value "D:\SQLAgent\Jobs" -Type String

# Restart SQL Agent
Restart-Service SQLSERVERAGENT

# Check if Always On is enabled
$hadrPath = "HKLM:\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL16.MSSQLSERVER\MSSQLServer\HADR"
$hadrEnabled = (Get-ItemProperty $hadrPath -ErrorAction SilentlyContinue).HADR_Enabled

if ($hadrEnabled -eq 1) {
    Write-Output "Always On Availability Groups: ENABLED"
} else {
    Write-Output "Always On Availability Groups: DISABLED"
}

# Enable Always On Availability Groups
$hadrPath = "HKLM:\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL16.MSSQLSERVER\MSSQLServer\HADR"
if (-not (Test-Path $hadrPath)) {
    New-Item -Path $hadrPath -Force | Out-Null
}
Set-ItemProperty -Path $hadrPath -Name "HADR_Enabled" -Value 1 -Type DWord

# Restart SQL Server to activate
Restart-Service MSSQLSERVER -Force

# Check Availability Group status
$query = @"
SELECT
    ag.name AS AGName,
    ar.replica_server_name AS Replica,
    ars.role_desc AS Role,
    ars.synchronization_health_desc AS SyncHealth,
    ars.connected_state_desc AS ConnState
FROM sys.availability_groups ag
JOIN sys.availability_replicas ar ON ag.group_id = ar.group_id
JOIN sys.dm_hadr_availability_replica_states ars ON ar.replica_id = ars.replica_id;
"@
Invoke-Sqlcmd -Query $query -ServerInstance "."

# Check Windows Failover Cluster node status
Get-ClusterNode | Select-Object Name, State, DynamicWeight

# Read all SQL Server startup parameters
$paramsPath = "HKLM:\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL16.MSSQLSERVER\MSSQLServer\Parameters"
$params = Get-ItemProperty $paramsPath

$params.PSObject.Properties |
    Where-Object { $_.Name -like "SQLArg*" } |
    Sort-Object Name |
    ForEach-Object { Write-Output "$($_.Name) = $($_.Value)" }

# Add trace flags to SQL Server startup parameters
$paramsPath = "HKLM:\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL16.MSSQLSERVER\MSSQLServer\Parameters"

# Find the next available SQLArg index
$existing = (Get-ItemProperty $paramsPath).PSObject.Properties |
    Where-Object { $_.Name -like "SQLArg*" }
$nextIndex = $existing.Count

# Add trace flag 3226 (suppress backup log messages)
Set-ItemProperty -Path $paramsPath -Name "SQLArg$nextIndex" -Value "-T3226" -Type String
$nextIndex++

# Add trace flag 7412 (lightweight query profiling)
Set-ItemProperty -Path $paramsPath -Name "SQLArg$nextIndex" -Value "-T7412" -Type String

Write-Output "Trace flags added. Restart SQL Server to apply."

# Start SQL Server in minimal configuration mode
# Add -f flag to startup parameters temporarily
$paramsPath = "HKLM:\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL16.MSSQLSERVER\MSSQLServer\Parameters"
$existing = (Get-ItemProperty $paramsPath).PSObject.Properties |
    Where-Object { $_.Name -like "SQLArg*" }
$nextIndex = $existing.Count

Set-ItemProperty -Path $paramsPath -Name "SQLArg$nextIndex" -Value "-f" -Type String

# Start SQL in minimal mode
Restart-Service MSSQLSERVER -Force
Write-Output "SQL Server started in minimal configuration mode"

# IMPORTANT: Remove the -f flag after maintenance
Remove-ItemProperty -Path $paramsPath -Name "SQLArg$nextIndex"

# Check current TCP port configuration on SQLPROD01
$tcpAllPath = "HKLM:\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL16.MSSQLSERVER\MSSQLServer\SuperSocketNetLib\Tcp\IPAll"
$tcpConfig = Get-ItemProperty $tcpAllPath
Write-Output "Static Port (TcpPort): '$($tcpConfig.TcpPort)'"
Write-Output "Dynamic Port (TcpDynamicPorts): '$($tcpConfig.TcpDynamicPorts)'"

# Check which protocols are enabled
$netLibPath = "HKLM:\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL16.MSSQLSERVER\MSSQLServer\SuperSocketNetLib"
$protocols = @{
    Tcp = "TCP/IP"
    Np = "Named Pipes"
    Sm = "Shared Memory"
}

foreach ($key in $protocols.Keys) {
    $enabled = (Get-ItemProperty (Join-Path $netLibPath $key)).Enabled
    $status = if ($enabled -eq 1) { "ENABLED" } else { "DISABLED" }
    Write-Output "$($protocols[$key]) : $status"
}

# Set static TCP port 1433 and disable dynamic port
$tcpAllPath = "HKLM:\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL16.MSSQLSERVER\MSSQLServer\SuperSocketNetLib\Tcp\IPAll"

# Set static port
Set-ItemProperty -Path $tcpAllPath -Name "TcpPort" -Value "1433" -Type String

# Clear dynamic port
Set-ItemProperty -Path $tcpAllPath -Name "TcpDynamicPorts" -Value "" -Type String

# Verify the change
$tcpConfig = Get-ItemProperty $tcpAllPath
Write-Output "New Static Port: '$($tcpConfig.TcpPort)'"
Write-Output "New Dynamic Port: '$($tcpConfig.TcpDynamicPorts)'"

# Open firewall for SQL Server static port
New-NetFirewallRule -DisplayName "SQL Server TCP 1433" `
    -Direction Inbound -Protocol TCP -LocalPort 1433 -Action Allow -Profile Domain

# Open firewall for SQL Browser (UDP 1434) - required for named instances
New-NetFirewallRule -DisplayName "SQL Server Browser UDP 1434" `
    -Direction Inbound -Protocol UDP -LocalPort 1434 -Action Allow -Profile Domain

# Restart SQL Server to apply port change
Restart-Service MSSQLSERVER -Force
Write-Output "SQL Server restarted with static port 1433"

# Test connectivity from the app server (run on WEBAPP01)
Test-NetConnection -ComputerName SQLPROD01 -Port 1433

# Test SQL authentication from the app server
$query = "SELECT @@SERVERNAME AS ServerName, @@VERSION AS Version"
Invoke-Sqlcmd -Query $query -ServerInstance "SQLPROD01" -TrustServerCertificate

# Full SQL Server network configuration audit
$instanceId = "MSSQL16.MSSQLSERVER"
$basePath = "HKLM:\SOFTWARE\Microsoft\Microsoft SQL Server\$instanceId\MSSQLServer"

# Authentication mode
$loginMode = (Get-ItemProperty "$basePath").LoginMode
$loginDesc = if ($loginMode -eq 1) { "Windows Only" } else { "Mixed Mode" }

# TCP port
$tcpPort = (Get-ItemProperty "$basePath\SuperSocketNetLib\Tcp\IPAll").TcpPort

# Encryption
$forceEnc = (Get-ItemProperty "$basePath\SuperSocketNetLib").ForceEncryption

Write-Output "Authentication: $loginDesc"
Write-Output "TCP Port: $tcpPort"
Write-Output "Force Encryption: $forceEnc"
Write-Output "SQL Browser: $((Get-Service SQLBrowser).Status)"