Database compatibility
Job details
| Name: | Database compatibility | |
| Platform: | Sqlserver | |
| Category: | Security | |
| Description: | Checks if there is a difference in compatibility levels of databases and the instance. | |
| Long description: | Checks if there is a difference in compatibility levels of databases and the instance. | |
| Version: | 1.3 | |
| Default schedule: | 9 1 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 |
|---|---|---|
| ignore databases | model | List of databases (separated by comma) which will be ignored. You can use % (percent sign) to represent wild card characters. |
| return status | 1 | Return status value (ALARM – 2, WARNING – 1, or OK – 0) when any database are in lower compatibility level than master database. |
| enforce compatibility | NO | If set to “YES“ the alert will enforce instance compatibility for every databases by running ALTER DATABASE [db_name] SET COMPATIBILITY_LEVEL. |
| history threshold | 365 | The maximum number of day to kept statistics for in the historic tables. |
Job Summary
- Purpose: The purpose of this job is to monitor and ensure the compatibility levels of all databases within a SQL Server instance match the compatibility level of the master database.
- Why: This job is important because discrepancies in database compatibility levels can lead to unexpected behavior or performance issues. Ensuring all databases within an instance are compatible with the version of the master database is essential for database reliability and consistency.
- Manual checking: You can check compatibility levels manually by querying the system views in SQL Server:
SELECT name, compatibility_level FROM sys.databases;Job Description
The “Database compatibility” job checks if there is any difference in the compatibility levels between the databases and the SQL Server instance’s master database. It creates and uses several objects in the SQL instance, such as tables to store current compatibility information and a history of changes, and a stored procedure to perform the checks and possibly enforce the preferred compatibility level.
Dependencies
- dbw_db_compatibility_proc (Main procedure)
- dbw_db_compatibility_info (Current status table)
- dbw_db_compatibility_histr (History table)
These objects are critical for the operation as they store and handle data required during the execution of the job.
Parameters Impact (Descriptive Purpose Only, No Deep Details)
- A parameter is available to specify databases that should be ignored during the checks.
- Alerts and notifications can be triggered based on the conditions specified.
- An option is provided to automatically enforce compatibility levels across all databases if required.
- Another parameter controls the retention period of the historical data.
Implementation Details
The procedure `dbw_db_compatibility_proc` carries out the following operations:
- Checks for differences in compatibility levels across databases.
- Provides options to ignore specified databases.
- Can enforce a compatibility level setting across all or specific databases based on the discrepancy and settings.
- Maintains historical record of compatibility checks and the results.
During execution, detailed logs and return statuses are managed to indicate the success or failure of the operation along with a description of what was processed.
Output Report Summary
Current Database Compatibility
- This report section lists all databases along with their compatibility levels. Databases that do not match the compatibility level of the master database are highlighted.
| Database | Compatibility Level | Compatibility |
|---|---|---|
| Master | 140 | 2017 |
| OtherDB | 100 | 2008 |
Aggregated Statistics
- Historical data on compatibility checks, showing per-database compatibility, instance compatibility, and count of checks.
| Database | |
|---|---|
| Compatibility | |
| Instance Compatibility | |
| First time registered | |
| Last time registered | |
| Times check |
Conclusion
This monitoring job is essential for maintaining consistency in compatibility levels across databases in a SQL Server instance. It provides both preventive measures and corrective actions to manage database compatibility effectively.