Replication is a widely-used SQL Server concept for maintaining copies of a database at different locations. The major advantage with replication is the Object level control. Though it is easy to set up, an unhealthy replication status might take time to fix. In this article, we will learn how to fix secondary databases that are out of sync.
When your subscriber is not receiving data from the publisher, any one from the below list could be the root cause
- The published article has a filter on it and there were no recent transactions that passed the filter
- Replication agents like SQL Server Agent, Snapshot Agent, Log Reader Agent, Distribution Agent, Merge Agent and Queue Reader Agent might be failing or might be in stopped state
- Without a proper snapshot, you might have initialized a transactional subscription
- Data might be deleted at subscriber by a user.
Step by Step
Step 1: Rows that were missing at the subscriber might not be replicated from the publisher as they did not meet the criteria for a filter. Replication types like Snapshot and Transactional support static filter whereas filter types like parametrized and join filter are supported by merge replication.
Step 2: An improper snapshot will result in the below error message. The snapshot agent must complete at the publisher before the snapshot was applied.
|Text||The initial snapshot for publication ‘%s’ is not yet available.|
Step 3: Key agents in a transaction replication are distribution and log reader. If either one of these agents is not running, data would not get delivered to the subscriber.
Step 4: To identify if records at subscriber got deleted by a user, you can run validation using the binary checksum. The other options it to run a validation on row count. `
From the replication monitor
Using the replication monitor, you can easily identify the sequence number of the transaction and ID of the command. You can also fetch those records from the replication table msrepl_errors. From these identified values, you must manually copy missing records from the publisher and insert those into the subscriber. Soon as you finish that, the distribution agent will continue to run and apply failed commands. Subscribers will get synchronized with the Publisher. During this task, you might end in turning on and turning off identity inserts into your tables.
Automate the monitoring
Automating the replication monitoring will help you to reduce the amount of time spent on troubleshooting. You can use MSreplication_monitordata to get the replication status. From this table, you can easily get Publishing server name, the name of publishing database and the agent name.
Select * FROM distribution.dbo.MSreplication_monitordata
You can create a SQL agent job to track replication errors. The table MSrepl_errors hold all replication related error text and the time during which those errors occurred.
Select * FROM distribution.dbo.MSrepl_errors
With snapshot replication, you always have a copy of your database with which you can quickly recover from SQL Server corruption
When Author Introduction:
Neil Varley is a data recovery expert in DataNumen, Inc., which is the world leader in data recovery technologies, including pst repair and excel recovery software products. For more information visit www.datanumen.com