#region SQL Server Connection and Health
function Test-SQLServerForLIMS {
param(
[string]$ServerName = "localhost",
[string]$DatabaseName = "LIMS",
[string]$Username,
[string]$Password,
[switch]$IntegratedSecurity = $true,
[int]$TimeoutSeconds = 30
)
# Build connection string
if ($IntegratedSecurity) {
$connectionString = "Server=$ServerName;Database=$DatabaseName;Integrated Security=true;Connection Timeout=$TimeoutSeconds;"
} else {
$connectionString = "Server=$ServerName;Database=$DatabaseName;User Id=$Username;Password=$Password;Connection Timeout=$TimeoutSeconds;"
}
Write-Host "Testing SQL Server connection for LIMS..." -ForegroundColor Yellow
Write-Host "Server: $ServerName" -ForegroundColor Gray
Write-Host "Database: $DatabaseName" -ForegroundColor Gray
try {
# Test basic connectivity
$stopwatch = [System.Diagnostics.Stopwatch]::StartNew()
$connection = New-Object System.Data.SqlClient.SqlConnection($connectionString)
$connection.Open()
# Get server information
$command = New-Object System.Data.SqlClient.SqlCommand("SELECT @@VERSION, @@SERVERNAME, DB_NAME(), SYSTEM_USER, GETDATE()", $connection)
$reader = $command.ExecuteReader()
$serverInfo = @{}
if ($reader.Read()) {
$serverInfo = @{
Version = $reader[0].ToString()
ServerName = $reader[1].ToString()
Database = $reader[2].ToString()
User = $reader[3].ToString()
ServerTime = $reader[4]
}
}
$reader.Close()
# Test a simple query performance
$testCommand = New-Object System.Data.SqlClient.SqlCommand("SELECT COUNT(*) FROM sys.tables", $connection)
$testResult = $testCommand.ExecuteScalar()
$stopwatch.Stop()
$connection.Close()
$result = [PSCustomObject]@{
Status = "Success"
Server = $ServerName
Database = $DatabaseName
ResponseTimeMs = $stopwatch.ElapsedMilliseconds
SqlServerVersion = $serverInfo.Version.Split("`n")[0]
ServerName = $serverInfo.ServerName
ConnectedAs = $serverInfo.User
ServerTime = $serverInfo.ServerTime
TableCount = $testResult
Timestamp = Get-Date
}
Write-Host "✓ Connection successful" -ForegroundColor Green
Write-Host "Response time: $($stopwatch.ElapsedMilliseconds)ms" -ForegroundColor Gray
Write-Host "Connected as: $($serverInfo.User)" -ForegroundColor Gray
Write-Host "Server time: $($serverInfo.ServerTime)" -ForegroundColor Gray
return $result
}
catch {
Write-Host "✗ Connection failed" -ForegroundColor Red
Write-Host "Error: $($_.Exception.Message)" -ForegroundColor Red
# Provide specific guidance for common errors
if ($_.Exception.Message -like "*login failed*") {
Write-Host "Hint: Check username/password or SQL Server authentication mode" -ForegroundColor Yellow
}
elseif ($_.Exception.Message -like "*server was not found*") {
Write-Host "Hint: Check server name, SQL Server service status, and firewall" -ForegroundColor Yellow
}
elseif ($_.Exception.Message -like "*timeout*") {
Write-Host "Hint: Check network connectivity and SQL Server responsiveness" -ForegroundColor Yellow
}
return [PSCustomObject]@{
Status = "Failed"
Server = $ServerName
Database = $DatabaseName
Error = $_.Exception.Message
Timestamp = Get-Date
}
}
}
function Get-SQLServerLIMSPerformance {
param(
[string]$ServerName = "localhost",
[string]$DatabaseName = "LIMS",
[string]$ConnectionString
)
if (-not $ConnectionString) {
$ConnectionString = "Server=$ServerName;Database=$DatabaseName;Integrated Security=true;Connection Timeout=30;"
}
$performanceQueries = @{
"DatabaseInfo" = @"
SELECT
name AS DatabaseName,
database_id,
create_date,
state_desc AS Status,
recovery_model_desc AS RecoveryModel,
page_verify_option_desc AS PageVerifyOption
FROM sys.databases
WHERE name = '$DatabaseName'
"@
"DatabaseSize" = @"
SELECT
type_desc AS FileType,
name AS LogicalName,
physical_name AS PhysicalName,
CAST(size/128.0 AS DECIMAL(12,2)) AS SizeMB,
CAST(FILEPROPERTY(name,'SpaceUsed')/128.0 AS DECIMAL(12,2)) AS UsedMB,
CAST((size-FILEPROPERTY(name,'SpaceUsed'))/128.0 AS DECIMAL(12,2)) AS FreeMB,
CAST((FILEPROPERTY(name,'SpaceUsed')*100.0)/size AS DECIMAL(5,2)) AS PercentUsed
FROM sys.database_files
"@
"ActiveConnections" = @"
SELECT
program_name,
host_name,
login_name,
COUNT(*) AS ConnectionCount,
COUNT(CASE WHEN status = 'running' THEN 1 END) AS ActiveCount,
COUNT(CASE WHEN status = 'sleeping' THEN 1 END) AS IdleCount
FROM sys.dm_exec_sessions
WHERE is_user_process = 1
AND database_id = DB_ID('$DatabaseName')
GROUP BY program_name, host_name, login_name
ORDER BY ConnectionCount DESC
"@
"WaitStats" = @"
SELECT TOP 10
wait_type,
waiting_tasks_count,
wait_time_ms,
max_wait_time_ms,
signal_wait_time_ms,
CAST(wait_time_ms / 1000.0 AS DECIMAL(12,2)) AS wait_time_seconds,
CAST(100.0 * wait_time_ms / SUM(wait_time_ms) OVER() AS DECIMAL(5,2)) AS percentage
FROM sys.dm_os_wait_stats
WHERE wait_type NOT IN ('CLR_SEMAPHORE','LAZYWRITER_SLEEP','RESOURCE_QUEUE','SLEEP_TASK',
'SLEEP_SYSTEMTASK','SQLTRACE_WAIT','WAITFOR', 'LOGMGR_QUEUE','CHECKPOINT_QUEUE',
'REQUEST_FOR_DEADLOCK_SEARCH','XE_TIMER_EVENT','BROKER_TO_FLUSH','BROKER_TASK_STOP',
'CLR_MANUAL_EVENT','CLR_AUTO_EVENT','DISPATCHER_QUEUE_SEMAPHORE', 'FT_IFTS_SCHEDULER_IDLE_WAIT',
'XE_DISPATCHER_WAIT', 'XE_DISPATCHER_JOIN', 'SQLTRACE_INCREMENTAL_FLUSH_SLEEP')
AND wait_time_ms > 0
ORDER BY wait_time_ms DESC
"@
"BlockingSessions" = @"
SELECT
r.session_id AS blocked_session_id,
r.blocking_session_id,
s.login_name AS blocked_user,
s.host_name AS blocked_machine,
s.program_name AS blocked_program,
bs.login_name AS blocking_user,
bs.host_name AS blocking_machine,
bs.program_name AS blocking_program,
r.wait_type,
r.wait_time,
r.last_wait_type,
st.text AS sql_text
FROM sys.dm_exec_requests r
INNER JOIN sys.dm_exec_sessions s ON r.session_id = s.session_id
LEFT JOIN sys.dm_exec_sessions bs ON r.blocking_session_id = bs.session_id
CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) st
WHERE r.blocking_session_id > 0
"@
"LongRunningQueries" = @"
SELECT
r.session_id,
s.login_name,
s.host_name,
s.program_name,
r.status,
r.command,
r.start_time,
DATEDIFF(minute, r.start_time, GETDATE()) AS runtime_minutes,
r.cpu_time,
r.logical_reads,
r.writes,
st.text AS sql_text,
qp.query_plan
FROM sys.dm_exec_requests r
INNER JOIN sys.dm_exec_sessions s ON r.session_id = s.session_id
CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) st
OUTER APPLY sys.dm_exec_query_plan(r.plan_handle) qp
WHERE r.session_id > 50
AND DATEDIFF(minute, r.start_time, GETDATE()) > 5
ORDER BY runtime_minutes DESC
"@
"TopQueries" = @"
SELECT TOP 10
qs.execution_count,
CAST(qs.total_elapsed_time / 1000000.0 AS DECIMAL(12,2)) AS total_elapsed_seconds,
CAST(qs.total_elapsed_time / qs.execution_count / 1000000.0 AS DECIMAL(12,4)) AS avg_elapsed_seconds,
CAST(qs.total_cpu_time / 1000000.0 AS DECIMAL(12,2)) AS total_cpu_seconds,
qs.total_logical_reads,
qs.total_logical_writes,
qs.creation_time,
qs.last_execution_time,
SUBSTRING(st.text, (qs.statement_start_offset/2)+1,
((CASE qs.statement_end_offset WHEN -1 THEN DATALENGTH(st.text)
ELSE qs.statement_end_offset END - qs.statement_start_offset)/2) + 1) AS statement_text
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) st
WHERE st.text NOT LIKE '%sys.dm_%'
ORDER BY qs.total_elapsed_time DESC
"@
}
$results = @{}
Write-Host "Gathering SQL Server performance data for LIMS..." -ForegroundColor Yellow
try {
$connection = New-Object System.Data.SqlClient.SqlConnection($ConnectionString)
$connection.Open()
foreach ($query in $performanceQueries.GetEnumerator()) {
Write-Host "Collecting $($query.Key)..." -ForegroundColor Cyan
try {
$command = New-Object System.Data.SqlClient.SqlCommand($query.Value, $connection)
$adapter = New-Object System.Data.SqlClient.SqlDataAdapter($command)
$dataset = New-Object System.Data.DataSet
$adapter.Fill($dataset) | Out-Null
$results[$query.Key] = $dataset.Tables[0]
}
catch {
Write-Warning "Failed to execute $($query.Key): $($_.Exception.Message)"
$results[$query.Key] = $null
}
}
$connection.Close()
# Display key findings
Write-Host "`nSQL Server Performance Summary:" -ForegroundColor Green
# Database size
if ($results["DatabaseSize"]) {
$totalSizeMB = ($results["DatabaseSize"] | Measure-Object SizeMB -Sum).Sum
$totalUsedMB = ($results["DatabaseSize"] | Measure-Object UsedMB -Sum).Sum
$usedPercent = [math]::Round(($totalUsedMB / $totalSizeMB) * 100, 1)
Write-Host "Database size: $([math]::Round($totalSizeMB,1))MB ($usedPercent% used)" -ForegroundColor Cyan
}
# Active connections
if ($results["ActiveConnections"]) {
$totalConnections = ($results["ActiveConnections"] | Measure-Object ConnectionCount -Sum).Sum
$activeConnections = ($results["ActiveConnections"] | Measure-Object ActiveCount -Sum).Sum
Write-Host "Connections: $totalConnections total, $activeConnections active" -ForegroundColor Cyan
}
# Blocking sessions
if ($results["BlockingSessions"] -and $results["BlockingSessions"].Rows.Count -gt 0) {
Write-Host "⚠ WARNING: $($results["BlockingSessions"].Rows.Count) blocked sessions detected!" -ForegroundColor Red
} else {
Write-Host "✓ No blocking sessions detected" -ForegroundColor Green
}
# Long running queries
if ($results["LongRunningQueries"] -and $results["LongRunningQueries"].Rows.Count -gt 0) {
$maxRuntime = ($results["LongRunningQueries"] | Measure-Object runtime_minutes -Maximum).Maximum
Write-Host "⚠ WARNING: $($results["LongRunningQueries"].Rows.Count) long-running queries (max: $maxRuntime minutes)" -ForegroundColor Yellow
} else {
Write-Host "✓ No long-running queries detected" -ForegroundColor Green
}
return $results
}
catch {
Write-Error "Failed to gather SQL Server performance data: $($_.Exception.Message)"
return $null
}
}
function New-SQLServerLIMSHealthScript {
param(
[string]$OutputPath = "C:\temp\sql_server_lims_health.sql",
[string]$DatabaseName = "LIMS"
)
$sqlScript = @"
-- SQL Server LIMS Health Check Script
-- Generated: $(Get-Date)
-- Database: $DatabaseName
SET NOCOUNT ON
PRINT '================================================'
PRINT 'SQL SERVER LIMS HEALTH CHECK - ' + CONVERT(varchar, GETDATE(), 120)
PRINT '================================================'
PRINT ''
PRINT '1. SERVER INFORMATION'
SELECT
@@SERVERNAME AS ServerName,
@@VERSION AS Version,
SERVERPROPERTY('ProductLevel') AS ServicePack,
SERVERPROPERTY('Edition') AS Edition,
GETDATE() AS CurrentTime
PRINT ''
PRINT '2. DATABASE STATUS'
SELECT
name AS DatabaseName,
state_desc AS Status,
recovery_model_desc AS RecoveryModel,
CAST(size*8.0/1024 AS DECIMAL(10,2)) AS SizeMB
FROM sys.databases
WHERE name = '$DatabaseName'
PRINT ''
PRINT '3. DATABASE FILE USAGE'
USE [$DatabaseName]
SELECT
type_desc AS FileType,
name AS LogicalName,
CAST(size/128.0 AS DECIMAL(12,2)) AS SizeMB,
CAST(FILEPROPERTY(name,'SpaceUsed')/128.0 AS DECIMAL(12,2)) AS UsedMB,
CAST((size-FILEPROPERTY(name,'SpaceUsed'))/128.0 AS DECIMAL(12,2)) AS FreeMB,
CAST((FILEPROPERTY(name,'SpaceUsed')*100.0)/size AS DECIMAL(5,2)) AS PercentUsed
FROM sys.database_files
PRINT ''
PRINT '4. ACTIVE CONNECTIONS'
SELECT
program_name,
login_name,
COUNT(*) AS ConnectionCount,
COUNT(CASE WHEN status = 'running' THEN 1 END) AS ActiveCount
FROM sys.dm_exec_sessions
WHERE is_user_process = 1
AND database_id = DB_ID('$DatabaseName')
GROUP BY program_name, login_name
HAVING COUNT(*) > 2
ORDER BY ConnectionCount DESC
PRINT ''
PRINT '5. BLOCKING SESSIONS (CRITICAL ISSUE IF ANY)'
IF EXISTS (SELECT 1 FROM sys.dm_exec_requests WHERE blocking_session_id > 0)
BEGIN
PRINT '*** BLOCKING DETECTED ***'
SELECT
r.session_id AS BlockedSession,
r.blocking_session_id AS BlockingSession,
s.login_name AS BlockedUser,
bs.login_name AS BlockingUser,
r.wait_type,
r.wait_time,
SUBSTRING(st.text, 1, 200) AS SQLText
FROM sys.dm_exec_requests r
INNER JOIN sys.dm_exec_sessions s ON r.session_id = s.session_id
LEFT JOIN sys.dm_exec_sessions bs ON r.blocking_session_id = bs.session_id
CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) st
WHERE r.blocking_session_id > 0
END
ELSE
PRINT 'No blocking sessions detected'
PRINT ''
PRINT '6. LONG RUNNING QUERIES (>5 MINUTES)'
IF EXISTS (SELECT 1 FROM sys.dm_exec_requests r INNER JOIN sys.dm_exec_sessions s ON r.session_id = s.session_id WHERE r.session_id > 50 AND DATEDIFF(minute, r.start_time, GETDATE()) > 5)
BEGIN
SELECT
r.session_id,
s.login_name,
r.status,
r.start_time,
DATEDIFF(minute, r.start_time, GETDATE()) AS RuntimeMinutes,
SUBSTRING(st.text, 1, 200) AS SQLText
FROM sys.dm_exec_requests r
INNER JOIN sys.dm_exec_sessions s ON r.session_id = s.session_id
CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) st
WHERE r.session_id > 50
AND DATEDIFF(minute, r.start_time, GETDATE()) > 5
ORDER BY RuntimeMinutes DESC
END
ELSE
PRINT 'No long running queries detected'
PRINT ''
PRINT '7. TOP WAIT STATISTICS'
SELECT TOP 10
wait_type,
waiting_tasks_count,
CAST(wait_time_ms / 1000.0 AS DECIMAL(12,2)) AS WaitTimeSeconds,
CAST(100.0 * wait_time_ms / SUM(wait_time_ms) OVER() AS DECIMAL(5,2)) AS Percentage
FROM sys.dm_os_wait_stats
WHERE wait_type NOT IN ('CLR_SEMAPHORE','LAZYWRITER_SLEEP','RESOURCE_QUEUE','SLEEP_TASK',
'SLEEP_SYSTEMTASK','SQLTRACE_WAIT','WAITFOR', 'LOGMGR_QUEUE','CHECKPOINT_QUEUE',
'REQUEST_FOR_DEADLOCK_SEARCH','XE_TIMER_EVENT','BROKER_TO_FLUSH','BROKER_TASK_STOP')
AND wait_time_ms > 1000
ORDER BY wait_time_ms DESC
PRINT ''
PRINT '8. ERROR LOG SUMMARY (RECENT ERRORS)'
EXEC xp_readerrorlog 0, 1, 'error'
EXEC xp_readerrorlog 0, 1, 'fail'
EXEC xp_readerrorlog 0, 1, 'timeout'
PRINT ''
PRINT '9. LIMS-SPECIFIC CHECKS'
-- Check for tables with no recent activity (potential issues)
SELECT
t.name AS TableName,
p.rows AS RowCount,
i.last_user_update,
DATEDIFF(day, i.last_user_update, GETDATE()) AS DaysSinceUpdate
FROM sys.tables t
INNER JOIN sys.dm_db_partition_stats p ON t.object_id = p.object_id AND p.index_id IN (0,1)
LEFT JOIN sys.dm_db_index_usage_stats i ON t.object_id = i.object_id AND i.index_id IN (0,1)
WHERE t.name LIKE '%sample%' OR t.name LIKE '%test%' OR t.name LIKE '%result%'
ORDER BY DaysSinceUpdate DESC
PRINT ''
PRINT 'Health check complete. Review any warnings or errors above.'
PRINT 'Timestamp: ' + CONVERT(varchar, GETDATE(), 120)
"@
$sqlScript | Out-File -FilePath $OutputPath -Encoding UTF8
Write-Host "SQL Server LIMS health check script created: $OutputPath" -ForegroundColor Green
Write-Host "Run this in SSMS: File -> Open -> $OutputPath" -ForegroundColor Cyan
Write-Host "Or from command line: sqlcmd -S servername -d $DatabaseName -i `"$OutputPath`"" -ForegroundColor Cyan
}
#endregion
Write-Host @"
SQL SERVER LIMS MONITORING COMMANDS:
=====================================
1. Test SQL Server connection:
Test-SQLServerForLIMS -ServerName "SQLSERVER01" -DatabaseName "LIMS_PROD"
2. Get comprehensive performance data:
`$perf = Get-SQLServerLIMSPerformance -ServerName "SQLSERVER01" -DatabaseName "LIMS_PROD"
`$perf["BlockingSessions"] # View blocking sessions
`$perf["LongRunningQueries"] # View slow queries
3. Generate health check script:
New-SQLServerLIMSHealthScript -DatabaseName "LIMS_PROD"
4. With SQL authentication:
Test-SQLServerForLIMS -ServerName "SERVER" -DatabaseName "LIMS" -Username "lims_user" -Password "password" -IntegratedSecurity:`$false
"@ -ForegroundColor Green