MS SQL Server patch status
Job details
| Name: | MS SQL Server patch status | |
| Platform: | Sqlserver | |
| Category: | Security | |
| Description: | Checks latest updates for Microsoft SQL Server. | |
| Long description: | Checks latest updates for Microsoft SQL Server. | |
| Version: | 2.74 | |
| Default schedule: | 16 1 * * | |
| Requires engine install: | Yes | |
| Compatibility tag: | .[type=‘instance’ & databasetype=‘sqlserver’]/instance[maj_version > ‘2005′ & hasengine=‘YES’ & eng_inst_priv = 0 & (engine_edition = ‘Microsoft SQL Server’ | engine_edition = ‘Azure SQL Managed Instance’)] |
Parameters
| Name | Default value | Description |
|---|---|---|
| support end date threshold | 30 | Warning/Alarm threshold (in days) before support end date. |
| extended support end date threshold | 30 | Warning/Alarm threshold (in days) before extended support end date. |
| return status (support end) | 1 | Return status value (ALARM – 2, WARNING – 1, or OK – 0) when the “support end date threshold” has been reached. |
| last patch release threshold | 60 | The number of days after the last patch was released and still not deployed on the instance. |
| return status (extended support end) | 1 | Return status value (ALARM – 2, WARNING – 1, or OK – 0) when the “extended support end date threshold” has been reached. |
| return status (last patch release) | 1 | Return status value (ALARM – 2, WARNING – 1, or OK – 0) when the “last patch release threshold” has been reached. |
Job Summary
- Purpose: The purpose of this monitoring job is to check for patch updates and status related to Microsoft SQL Server instances.
- Why: Maintaining up-to-date patching is critical for security, compliance, and operational stability. This job assists in ensuring that each instance runs the latest patches, which helps protect against vulnerabilities and ensures support from Microsoft.
- Manual checking: Manual checking can be conducted through SQL Server configuration and patch management tools to verify the current patch versions and compare them with the latest available patches as listed by Microsoft.
Job Description
This monitoring job is designed to track and report on the patch status of Microsoft SQL Server installations. It verifies the installation against the latest patches released by Microsoft to determine if the SQL Server instances are currently up-to-date.
Key Functionalities
- Checks for the latest available patches for each SQL Server installation.
- Compares installed patches against the latest to determine if updates are needed.
- Alerts if installed SQL Servers are nearing the end of their support period or if a patch is significantly out of date.
Detailed Implementation
The job involves several components and procedural calls to ensure comprehensive monitoring:
- Creates necessary database tables to record patch information and history.
- Implements procedures to fetch and record current patch levels of the SQL Server instance.
- Handles various scenarios such as updating records when new patches are applied, and insertion of new records if it’s a new instance or newly applied patch not previously tracked.
Expected Outputs
Depending on the current state of the SQL Server instances and their patches, the output can vary:
- If patches are up-to-date: Standard reports indicating no action required.
- If patches are outdated: Alerts and detailed reports on which patches are missing, including severity based on how old the missing patches are.
Schedule
- Default Schedule: The job is scheduled to run once every month to ensure regular checking and updates of the SQL Server patch status.
Dependencies
- SQL Server instances with versions later than 2005.
- Access to Microsoft’s patch release information.
- Database modifications rights to update patch tables.
Security and Compliance Information
- Acknowledgements: The job allows acknowledging alerts, which helps in managing known issues during patch application schedules.
- Compliance: Helps in maintaining compliance with internal security polices and external regulations by ensuring that all SQL Server instances are up-to-date with security patches.
This monitoring job is essential for maintaining the health, security, and compliance of Microsoft SQL Server installations in any environment.