Defining a framework for reporting on SQL server risks

With many great SQL scripts out there to monitor and report on issues and risks in a SQL server environment it is easy to get lost in what is really important, and what can be ignored.

Summary: Defining a Framework for SQL Server Risk Assessments

Creating a robust framework for conducting SQL Server risk assessments is essential for daily DBA checks. This framework should encompass several key components to ensure thorough evaluation and management of risks associated with SQL Server environments.

1. Identify Key Risk Areas

- **Data Integrity**: Assess the mechanisms in place to maintain data accuracy and consistency. - **Security**: Evaluate user access controls, authentication methods, and data encryption practices. - **Performance**: Monitor system performance metrics to identify bottlenecks or inefficiencies. - **Backup and Recovery**: Review backup schedules and recovery plans to ensure data can be restored in case of failure.

2. Establish Assessment Criteria

- Define specific metrics and benchmarks for each risk area. - Use industry standards and best practices as a reference point. - Create a scoring system to quantify risk levels.

3. Develop Assessment Procedures

- Outline step-by-step processes for conducting assessments in each key risk area. - Incorporate regular checks and audits into daily DBA routines. - Utilize automated tools where possible to streamline the assessment process.

4. Document Findings

- Maintain detailed records of assessment results, including identified risks and their potential impact. - Create reports that summarize findings for stakeholders and management review.

5. Implement Mitigation Strategies

- Develop action plans for addressing identified risks based on their severity. - Prioritize remediation efforts to focus on high-risk areas first.

6. Review and Update Framework

- Regularly revisit the risk assessment framework to adapt to new threats and changes in the SQL Server environment. - Solicit feedback from team members to improve assessment processes. By following this structured approach, DBAs can effectively manage risks associated with SQL Server, ensuring the reliability, security, and performance of their database systems.

Here are some checks to consider:

Some SQL servers are incorrectly licensed. There is a risk of financial liability. Take urgent action.

Sub-optimal CPU configuration

Not all SQL servers use all the CPUs allocated. This is a performance and potential financial risk. Take immediate action.

TempDB Has >16 Data Files

Multiple Log Files on One Drive

Automated housekeeping seems to be missing.

Some SQL server(s) are not running in High Performance mode

TempDB Only Has 1 Data File

TempDB Unevenly Sized Data Files

@ databases with High internal VLF Counts

@ TraceFlags are configured. Some are missing

Database File growth set to percent for @ databases

Database File growth set to 1MB for @ databases

Max File Size Set for @ database files

Default Config Changed on @ databases Default Config Delayed Durability Enabled

Default Config Changed on @ databases Default Config Recursive Triggers Enabled

Servername different from computer name

Extended Stored Procedures in Master Database

Default config has been changed on @ server(s) Default Configuration for CPU I/O affinity

Default config has been changed on @ server(s) Default Configuration for CPU affinity

Default config has been changed on @ server(s) Default Configuration for Index create memory (KB)

@ server(s) are running bad default settings for Backup checksum

@ server(s) are running bad default settings for Agent XPs

@ server(s) are running bad default settings for Database Mail XPs

@ server(s) are running bad default settings for server memory

@ server(s) are running bad default settings for replication size

Default config has been changed on @ server(s) Default Configuration for Min memory per query (KB) Min memory per query (KB)

Default config has been changed on @ server(s) Default Configuration for CPU I/O affinity 64-bit Affinity64 mask

Default config has been changed on @ server(s) Default Configuration for CPU affinity 64-bit Affinity64 I/O mask

@ server(s) are running bad default settings for parallelism

Default config has been changed on @ server(s) Default Configuration for Cursor threshold

Default config has been changed on @ server(s) Default Configuration for Fill factor (%)

Default config has been changed on @ server(s) Default Configuration for Lightweight pooling

Configure additional SQL alerts. This will enable early issue detection.

Some other Alerts are missing or not configured

SQL Operators are Not Configured

@ Alerts do not have Follow Up Configured

Configure corruption alerts. Corruption leads to data loss. Immediate notification of corruption is required. Take urgent action.

Failsafe Operator Not present

Automatic alert notifications are off. No alerts will be sent to support teams. Take action.

@ Agent Jobs Without Failure Emails configured

Disabled Internal Monitoring Features

Agent is currently offline

Poison Wait Detected: Serializable Locking

Queries are causing Memory Pressure

Memory Dangerously Low

Deadlocks are occurring. These impact application behaviour and performance. Take urgent action.

Significant Wait times recorded.

Shrink Database Job found

Max Memory Set Too High on @ server(s)

@ Missing Index found

Database Query Store Disabled

Auto-Close Enabled on @ databases

Auto-Shrink Enabled on @ databases

@ Active Tables Without Clustered Indexes

Auto-Create Stats Disabled on @ databases

Auto-Update Stats Disabled on @ databases

@ Index improvements recommended by the SQL Engine

@ Column Constraints are not trusted

@ Foreign Keys trust issues

@ Leftover Fake Indexes

@ Stale Column Statistics found

Asynchronous Stats Updates enabled on @ databases

@ RID or Key Lookups

@ Implicit Conversions impacting SQL queries

Some SQL server(s) do not have query plan memory optimization enabled. There are no reasons why this should be disabled.

@ Objects created with dangerous SET Options

Resource Governor Enabled. This could have performance impacts, or be used to mitigate them.

Slow Storage Writes on @ Drives

@ Indexes Disabled

Drive Reads are averaging longer than 200ms

@ Non-Aligned indexes

@ server(s) have Unusual SQL Server Editions

Merge Replication Retention issues

@ server(s) have CPUs that are Offline

Poison Wait Detected

@ Memory Nodes Offline

Slow File Growth recorded

SQL server(s) have 32-bit binaries

@ NUMA nodes reporting THREAD_RESOURCES_LOW

High Number of Cached Plans

Poison Wait Detected: CMEMTHREAD & NUMA

@ server(s) have Too Much Free Memory

Locked Pages In Memory Enabled

High Memory Use for In-Memory OLTP (Hekaton)

Forced Parallelism

Plan Cache cleared recently

Wait Stats Have Been Cleared

Possible parallelism threshold configuration issues

Minimum Server Memory setting is the same as the Maximum

Instant File Initialization Disabled on @ server(s)

Odd number of CPU cores

Shrink Database Step In Maintenance Plan

Wait Stats Cleared Recently

Auto-Shrink Ran Recently

DBCC DROPCLEANBUFFERS Ran Recently

DBCC FREEPROCCACHE Ran Recently

Buffer Pool Extensions Enabled on @ server(s)

Memory Leak in USERSTORE_TOKENPERM Cache

Query store state issues

Snapshotting too many databases

Database Corruption Detected

SQL server(s) are out of support. This could impact SLAs and leave SQL servers vulnerable for attack. Take urgent action.

Backup Compression is NOT enabled on all server(s). It is easy to configure and improves backup and restore times

Remote Admin connections are enabled on all server(s)

@ Errors found in the SQL Trace Log

Some databases have no backups. Get SQL backups configured now. Take immediate action.

@ databases have no recent LOG Backups

@ databases have automatic corruption checking turned off

There are data consistency risks. These could lead to data loss. Take immediate action.

Possibly Broken Log Shipping

Full Text Indexes Not Updating

@ Databases have Unusual Database States

Database Corruption Detected - Automatically repaired AG

@ backups done to a disk hosting databases

Database Corruption Detected - Automatically repaired mirror

Log Backups to NUL, which means the backup does not exist

SQL Server updates may fail on @ server(s)

DBCC WRITEPAGE Used Recently

No Failover Cluster Nodes Available

Resumable Index Operation Paused

There are some Evaluation Edition server(s)

There are Errors with the Default SQL Trace

Encryption Certificate Not Backed Up Recently

Dangerous Third Party Modules

@ Memory Dumps Have Occurred

Priority Boost Enabled on @ server(s)

@ server(s) have a Dangerous Build of SQL Server (Corruption)

TDE Certificate Not Backed Up

In-Memory OLTP Transaction Errors

@ Database Files on Network File Shares

@ Database Files Stored in Azure

@ server(s) have a Dangerous Build of SQL Server (Security)

Weak passwords on @ server(s)

Data Sync Endpoints are Owned by a User

Transparent Data Encryption not enabled on @ databases.

@ Invalid users found with no matching valid AD login

SQL Server is running under an NT Service account

SQL Server Agent is running under an NT Service account