Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Curious question about Transaction Replication from a backup Expand / Collapse
Author
Message
Posted Friday, July 5, 2013 1:31 PM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Monday, July 14, 2014 2:06 PM
Points: 3,865, Visits: 7,130
I've read various articles on how to do this but tried a different approach and while it appears to work for the most part, there were still some issues that I'm hoping someone can point out the obvious.

We have replication already set up and going on a reporting server. There is a separate publisher, distributor, and subscriber server. Our attempt is to recreate "clone" the existing replication set up on the existing reporting server to a new reporting server.

Our process:
- We stopped the distributor agent
- Waited for replicated transactions to complete at the subscriber
- Took a full backup of the subscribed databases
- Restored these databases on the new reporting server (at this point both subscribers should be 100% the same)
- Created a new subscription to the existing publications on the new server
- Re-enabled the distribution agent and waited for replicated commands to hit both subscribers

This seems to work, but for some tables we're off by a few hundred, to a few thousand rows...

The only error we can find is "The row was not found at the Subscriber when applying the replicated command" - which to keep things flowing we added -SKIPERRORS 20598

It appears very random, but is keeping things in sync for the most part (minus the original missing rows)

Any suggestions or ideas as to we are getting these errors? Shouldn't both database servers be exactly the same in the method we took?


______________________________________________________________________________
"Never argue with an idiot; They'll drag you down to their level and beat you with experience"
Post #1470866
Posted Monday, July 8, 2013 1:25 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Tuesday, July 22, 2014 1:19 AM
Points: 1,282, Visits: 1,114
MyDoggieJessie (7/5/2013)
I've read various articles on how to do this but tried a different approach and while it appears to work for the most part, there were still some issues that I'm hoping someone can point out the obvious.

We have replication already set up and going on a reporting server. There is a separate publisher, distributor, and subscriber server. Our attempt is to recreate "clone" the existing replication set up on the existing reporting server to a new reporting server.

Our process:
- We stopped the distributor agent
- Waited for replicated transactions to complete at the subscriber
- Took a full backup of the subscribed databases
- Restored these databases on the new reporting server (at this point both subscribers should be 100% the same)
- Created a new subscription to the existing publications on the new server
- Re-enabled the distribution agent and waited for replicated commands to hit both subscribers

This seems to work, but for some tables we're off by a few hundred, to a few thousand rows...

The only error we can find is "The row was not found at the Subscriber when applying the replicated command" - which to keep things flowing we added -SKIPERRORS 20598

It appears very random, but is keeping things in sync for the most part (minus the original missing rows)

Any suggestions or ideas as to we are getting these errors? Shouldn't both database servers be exactly the same in the method we took?



Your process are correct. Let me ask you few more questions.

1. Do you have any jobs that delete rows the reporting server
2. Do you have SP's that are participating in the replication as an article
3. You mentioned a few tables are not always sync. Does this happen all the time those tables are not in sync. What I meant is when ever you recreate the replication does those tables are not in sync.

I am using P2P replication with more than 3 peers I never encountered this issue. But we do get a tons of conflicts.

Thank you
Post #1471061
Posted Monday, July 8, 2013 1:54 PM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Monday, July 14, 2014 2:06 PM
Points: 3,865, Visits: 7,130
To answer your questions:
1. No
2. No
3. It appears to be the same table(s). We were going to try and drop the articles and add them back in to see if it remedies the issues we encounter for the problematic tables

Thanks for the reply!


______________________________________________________________________________
"Never argue with an idiot; They'll drag you down to their level and beat you with experience"
Post #1471348
Posted Monday, July 8, 2013 2:02 PM
SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Yesterday @ 1:54 PM
Points: 609, Visits: 426
We used the below script to set the replication . I am wondering even stopping the distribution job some batch of transactions was still in progress can cause this or not .

EXEC sp_addsubscription @publication = N'<>',
@subscriber = N'<>',
@destination_db = N'<>',
@subscription_type = N'Push',
@sync_type = N'replication support only',
@article = N'all',
-- @update_mode = N'read only',
@subscriber_type = 0

Or instead of adding a subscription to an exisiting Publication , creating a new pub for the specific new subscription can help in getting the trick done .

Post #1471353
Posted Monday, July 8, 2013 2:10 PM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Monday, July 14, 2014 2:06 PM
Points: 3,865, Visits: 7,130
I am wondering even stopping the distribution job some batch of transactions was still in progress can cause this or not .
This was my original thought. Perhaps we didn't wait long enough for everything to trickle through...

We can't create a new publication because of the snapshot issue. It would involve an entire snap of all the data and place us in a rather long maintenance window we can't really get approval for right now.

The only think I can think of is:
1. Redo it again, waiting much longer to ensure there are no pending rpl_commands, checking and then rechecking the distribution database to make 100% certain everything is replicated before taking the FULL backup of the subscription databases

2. If there are lingering commands, we need to find a way to push those to the new subscriber as well before turning the distribution agent back on

3. Dropping the troublesome article(s) from the existing publication, then re-adding them back in to ensure we minimize the impact on our OLTP system


______________________________________________________________________________
"Never argue with an idiot; They'll drag you down to their level and beat you with experience"
Post #1471356
Posted Monday, July 8, 2013 2:40 PM
SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Yesterday @ 1:54 PM
Points: 609, Visits: 426
MyDoggieJessie (7/8/2013)
I am wondering even stopping the distribution job some batch of transactions was still in progress can cause this or not .
This was my original thought. Perhaps we didn't wait long enough for everything to trickle through...

We can't create a new publication because of the snapshot issue. It would involve an entire snap of all the data and place us in a rather long maintenance window we can't really get approval for right now.



No , it wont require a re-snap thats the point . We are not intializing the subscription as the data is available already in subscription . Replication with backup or Replication with support only is used to avoid massive snapshot generation .
Post #1471369
Posted Monday, July 8, 2013 6:33 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Saturday, July 26, 2014 12:23 PM
Points: 258, Visits: 808
Instead of stopping the distribution agent first, you should stop the log reader agent on the published database.
Wait for replication to quiesce, stop the distribution agent, recreate the new subscription and you should be done.

This of course assumes this is the only publication from that database.

Post #1471400
Posted Monday, July 8, 2013 8:30 PM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Monday, July 14, 2014 2:06 PM
Points: 3,865, Visits: 7,130
Why the log reader agent?

______________________________________________________________________________
"Never argue with an idiot; They'll drag you down to their level and beat you with experience"
Post #1471421
Posted Monday, July 8, 2013 8:39 PM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Tuesday, July 22, 2014 1:19 AM
Points: 1,282, Visits: 1,114
MyDoggieJessie (7/8/2013)
I am wondering even stopping the distribution job some batch of transactions was still in progress can cause this or not .
This was my original thought. Perhaps we didn't wait long enough for everything to trickle through...

We can't create a new publication because of the snapshot issue. It would involve an entire snap of all the data and place us in a rather long maintenance window we can't really get approval for right now.

The only think I can think of is:
1. Redo it again, waiting much longer to ensure there are no pending rpl_commands, checking and then rechecking the distribution database to make 100% certain everything is replicated before taking the FULL backup of the subscription databases

2. If there are lingering commands, we need to find a way to push those to the new subscriber as well before turning the distribution agent back on

3. Dropping the troublesome article(s) from the existing publication, then re-adding them back in to ensure we minimize the impact on our OLTP system


Perfect. Please share us the results of your re work. I can't think of any reason why you miss of records. And please turn on the server side trace when you enable the replication to make sure you capture the all the events.

-- Babu
Post #1471424
Posted Tuesday, July 9, 2013 8:52 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Saturday, July 26, 2014 12:23 PM
Points: 258, Visits: 808
MyDoggieJessie (7/8/2013)
Why the log reader agent?


That stops transactions from being sent to the distribution database.
Post #1471671
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse