Database mirroring
Job details
| Name: |
Database mirroring |
| Platform: |
Sqlserver |
| Category: |
Cluster and Replication |
| Description: |
Checks state information of all mirrored databases. |
| Long description: |
Checks state information of all mirrored databases. The Catalog View master.sys.database_mirroring is used to alert if any Principals or Mirrors are in an abnormal state (normal states: SYNCHRONIZED,SYNCHRONIZING). |
| Version: |
1.2 |
| Default schedule: |
* 7-17 * * |
| Requires engine install: |
Yes |
| Compatibility tag: |
.[type=‘instance’ & databasetype=‘sqlserver’]/instance[maj_version > ‘2000′ & hasengine=‘YES’ & engine_edition = ‘Microsoft SQL Server’ & eng_inst_priv = 0] |
Parameters
| Name |
Default value |
Description |
| ignore databases |
model |
List over the databases (separated by comma) which will be ignored from being checked. |
Job Summary
- Purpose: The purpose of this monitoring job is to check the state information of all mirrored databases on a Microsoft SQL Server.
- Why: This job is important to ensure that database mirroring states such as ‘SYNCHRONIZED’ and ‘SYNCHRONIZING’ are maintained properly. Anomalies in these states could indicate potential issues which can impact data consistency and system resilience in case of a server failure or disaster recovery scenario.
- Manual checking: To manually check the status of mirrored databases, use the following SQL command on your server:
SELECT db_name(database_id) as name, mirroring_state_desc, mirroring_role_desc FROM master.sys.database_mirroring WHERE mirroring_role_desc IN ('PRINCIPAL','MIRROR')
Monitoring Strategy
- “Database mirroring” is the designated object for this task, targeting instances where major version is greater than 2000, SQL server engine is present, and user does not have instance privileges.
- The task focuses on Microsoft SQL Server editions and operates daily from 7 AM to 5 PM.
- It specifically checks for databases that are either in principal or mirror roles and have deviated from ‘SYNCHRONIZED’ or ‘SYNCHRONIZING’ states.
- During execution, databases listed under the ‘ignore databases’ parameter are excluded from checks.
Implementation Details
- The implementation involves a stored procedure, ‘dbw_database_mirroring’, which dynamically constructs and executes a query to monitor mirroring states of all non-excluded databases.
- The procedure utilizes a cursor to iterate through database mirroring details extracted via system views.
- Abnormal states trigger an alert, logging each affected database followed by its associated mirroring status.
Dependencies and Post-installation Behavior
- This task is dependent on the ‘dbw_database_mirroring’ object, ensuring that it is self-contained and managed directly within dbWatch.
- Upon installation, the procedure is always forcibly installed and executes independently of express installations.
Reporting Features
- A report template configured for this job presents database mirroring states in a formatted table consisting of database names, their mirroring, and role descriptions.
- The report is designed to give a straightforward presentation on the state of database mirroring, aiding quick recognition of issues and immediate response actions.
Key Outputs and Upgrade Paths
- The primary output of this job is a detailed report on the status of all mirrored databases, highlighting those that do not conform to expected conditions.
- A prior version (1.1) exists, and the stored procedure allows modifications if upgrading from this earlier version to incorporate enhanced error handling and dynamic SQL enhancements to improve operational accuracy and efficiency.