Replication status
Job details
| Name: | Replication status |
| Platform: | Sqlserver |
| Category: | Cluster and Replication |
| Description: | The following check provides general info in regards to any replication going on in a server. |
| Long description: | This procedure analyze MSdistribution_agents table which contains one row for each Distribution Agent running at the local Distributor. This table is stored in the distribution database. |
| Version: | 2 |
| Default schedule: | 0 * * * |
| 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 |
|---|---|---|
| return code | 1 | Returned code (0-OK, 1-WARNING, 2-ALARM) when the replication status is “Failed”. |
| distribution database name | distribution | Name of the distribution database which stores metadata and history data for all types of replication, and transactions for transactional replication. |
Job Summary
- Purpose: The purpose of this job is to monitor and manage the replication statuses within an SQL Server environment, ensuring that all subscriptions are functioning correctly and efficiently.
- Why: This job is important to ensure the integrity and reliability of database replication across different databases and servers. It helps in identifying issues like lag in replication or failure in the pipeline which could severely impact data consistency and availability.
- Manual checking: To manually check the replication status in a database:
SELECT * FROM dbw_repl_status_tab;Job Details
Tables and Procedures Created
- “dbw_repl_status_tab” Table: Holds detailed replication status information for monitoring.
- “dbw_replication_status” Procedure: Gathers and logs the replication status into the “dbw_repl_status_tab” and is responsible for updating task outputs based on the findings.
Dependency
The job depends on itself and the replication status table and ensures its cleanup on failure which is crucial for maintaining the accuracy of logs and system stability.
SQL Implementation
The following SQL tasks are performed:
- Creation of temporary tables “#tempsub1″ and “#tempsub2″ to handle intermediate data.
- Gathering data from “msdistribution_agents” and “MSdistribution_history” tables.
- Manipulating and moving data between tables to format the final output into “dbw_repl_status_tab.”
- Exception handling to capture and log errors during execution.
Reporting
The job involves a presentation/reporting mechanism where the replication statuses are displayed:
- Report Title: “Replication Status”
- Displays fields like publisher, subscriber, delivery latency, etc.
- Enabled by selecting data from “dbw_repl_status_tab.”
Upgrade Notes
If upgrading from previous versions (1.4, 1.5):
- The table “dbw_repl_status_tab” might be recreated with possibly altered structure ensuring better handling and storage of data.
- Modifications in the replication status procedure to enhance efficiency and error management.
Other configurations
- “dbw~schema” is used for log on, which indicates using a specific schema setting.
- Version: Given task is described in detail in versions up to 1.6.
- Task runs are scheduled every fifteen minutes by default, ensuring timely updates and checks.
Monitoring and Reaction
Upon identifying a replication failure, the system:
- Logs detailed error messages and status.
- Propagates status values that can trigger alerts or warnings based on defined thresholds.
Conclusion
This dbWatch job is integral to maintaining robust SQL Server replication systems, providing administrators comprehensive insights and automated oversight of replication statuses to preemptively resolve potential issues affecting critical database operations.