How to Break Replication

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » Article Discussions » Article Discussions by Author » Discuss Content Posted by David Poole » How to Break Replication

20 posts, Page 1 of 2

1 2 »»

How to Break Replication

Rate Topic

Display Mode

Topic Options

Author

Message

David.Poole

Posted Thursday, March 06, 2008 7:21 AM

SSCrazy

Group: General Forum Members
Last Login: Today @ 8:46 AM
Points: 2,750, Visits: 1,410

Comments posted to this topic are about the item How to Break Replication

LinkedIn Profile

Post #464929

RL Coppedge

Posted Thursday, March 06, 2008 8:11 AM

Forum Newbie

Group: General Forum Members
Last Login: Monday, January 11, 2010 10:39 AM
Points: 4, Visits: 60

Nice article. I've spent a good chunk of the last year working on transactional replication (about 400k commands/day) from Oracle -> SQL 2005. It was already set up by the client, and it took many months to undo/redo (and learning all the way) a lot of the issues.

Some additional thoughts:

1> Optimize, optimize, optimize! If you think that the application of replicated commands are slow, use Profiler to see why they are.
2> Make sure your codepages match. In my case, Oracle was set up handling all sorts of characters that SQL isn't. So the Log Reader would hurl, causing calamity.
3> Don't try several fixes at once (a variant of the "ready, fire, aim" mentality so many ITfolks like). Remember that a lot of times, it takes a while for a "fix" to "take."
4> Take a look at some of the isolation modes that SQL 2005 brings to the game. Snapshot Isolation mode can really cut down on some of the locks.
5> Understand how SQL creates transactions out of replication. Then explain it to me . If the Log Reader stays offline for a bit, the commands accumulate on the Publisher side, and are essentially consumed and processed as single transactions. This can create a single transaction of huge size (that really isn't a transaction per se). This can really dog performance.

Thanks for the article!

Bob Coppedge
MCSE/MCDBA/MCTS/MCITP/MCT/SOB

Post #465188

giorgio-459258

Posted Thursday, March 06, 2008 11:12 AM

Grasshopper

Group: General Forum Members
Last Login: Monday, January 03, 2011 9:45 AM
Points: 13, Visits: 49

That's a nice article.

Another common issue with replication is the famous error:
The row was not found at the Subscriber when applying the replicated command.
(Source: SQL-1 (Data source); Error number: 20598)

I get a lot of this in a transactional replication and I haven't figure out yet what is causing it! There's no process that deletes data at the subscriber end so I am a bit confused at the moment...

Have you experienced this problem before? How did you dealt with it?

Thanks

Post #465011

Mahender-233322

Posted Thursday, March 06, 2008 11:35 AM

Grasshopper

Group: General Forum Members
Last Login: Friday, June 05, 2009 6:03 AM
Points: 19, Visits: 24

Hi Poole,
I have been working on Replication since 2002, and faced many problems with SQL 2000. With every new release of Service Pack some problems got solved. There are some problems still unattended, still I have to spend some time with the Conflict Viewer unnecessarily.

With SQL 2005 all the problems got rectified. As a person suffering from SQL 2000, I loved the new version. I loved the way we can filter the data with joins.

I have faced couple of problems with SQL 2005 as well

Problem1: One table didn't had Primary key and had duplicate rows. I wanted to assign a Primary key for the table, so I deleted all the duplicate rows then created the Primary Key. I started the replication agent, to my surprise, agent failed to replicate saying could not create Index due to duplicate records. The agent was trying to create the Index before deleting the rows. The agent executes the DDL changes first then data. I had to delete the rows manually in the replication database and then restart the agent.

Problem2: I have a Merge Replication setup which involves many tables(around 60) with filters, broken into 20 tables per replication, distributed to 5 different servers with unique filter for each server. When I insert a row in one of the key table which is used as join filter for most of filtered tables, it takes 12 hours to complete the job and consumes 27 GB of hard disk space for the log file. Its little bizarre why it needs some much of space and time. I have to make room for the Log file before executing the Insert statement. Luckily, I have 30 GB free space in my hard disk, otherwise what will happen I dont know.

Thanks

Post #465020

EdVassie

Posted Thursday, March 06, 2008 12:32 PM

SSCrazy

Group: General Forum Members
Last Login: 2 days ago @ 3:26 AM
Points: 2,621, Visits: 2,759

Dave,

A very good article! There is so little good information available on troubleshooting replication problems, so this is a valuable addition.

Author: SQL Server FineBuild 1-click install and best practice configuration of SQL Server 2012, 2008 R2, 2008 and 2005. 25 March 2013: now over 23,000 downloads.
Disclaimer: All information provided is a personal opinion that may not match reality.
Concept: "Pizza Apartheid" - the discrimination that separates those who earn enough in one day to buy a pizza if they want one, from those who can not.

Post #465040

Andy Warren

Posted Thursday, March 06, 2008 12:56 PM

SSCertifiable

Group: Moderators
Last Login: Thursday, May 09, 2013 12:38 PM
Points: 6,462, Visits: 1,384

David, rather than spelunk through ms_replcmds to find the offending row, there are two simpler alternatives. One is to add the error number as a '-skiperror' parameter to the agent, the other is to modify the proc on the subscriber to handle the condition. For example, the default delete proc raises an error if the rowcount <> 1. Just remove that code and the bad deletes get absorbed.

Andy
SQLShare - Learn One New Thing Each Day
SQLAndy - My Professional Blog
Connect with me on LinkedIn
Follow me on Twitter

Post #465050

Erik Bitemo

Posted Thursday, March 06, 2008 2:40 PM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, April 01, 2013 2:14 AM
Points: 122, Visits: 173

Some thoughts...

1. Replication can be very troublesome, that's true, however, in general, I found the system reasonable in one way or another, so I do believe in determinism :)
2. I didn't experience snapshot locking - on a table with a size of 13GB serving out hundreds/thousands of requests per minute.
3. nosync init: yes, it's really weird that it doesn't create the stored procedures, BUT you can create them extremely easily:

 sp_scriptpublicationcustomprocs @publication = publication_name

4. And my favorite scenario wasn't discussed: Replication chaining, that is, publishing something I subscribed to :) - that makes your life a fun :)

-- Erik http://blog.rollback.hu

Post #465504

Michael Earl-395764

Posted Thursday, March 06, 2008 6:02 PM

SSCrazy

Group: General Forum Members
Last Login: Tuesday, August 10, 2010 5:07 AM
Points: 2,732, Visits: 23,078

Nice article.

I want to comment on your removal of the guts of the delete stored procedures by telling you to look up "Deferred updates".
If you update a column with a unique constraint on it, replication translates this into a delete and an insert - so be careful removing the guts of a delete procedure because they are sometimes used for updates as well.

Post #465057

Ian Yates

Posted Friday, March 07, 2008 4:47 AM

Ten Centuries

Group: General Forum Members
Last Login: Sunday, September 16, 2012 3:26 AM
Points: 1,038, Visits: 443

Great article. Replication has been one of those "spooky" things that I have wanted to play with but setting up a test environment to learn in and then willing myself to break and fix it when I have a hundred other things on my plate means that I just haven't got round to it.

The main bit that I'm unsure of, and I think you touched on it briefly without meaning to, is that we have what I'd hope is now a largely off-the-shelf product but with a database that I'm modifying (new tables, columns, etc) on a reasonably regular basis. I make change scripts to move the database from version to version but I haven't wanted to worry about having subscribers with data and making changes to articles, etc, etc... In SQL 2005, does it pick up table schema modifications and replicate them too? That'd be nice :D

I'll certainly add this article (and reference the thread here) to my briefcase for later reference. Thanks!

Post #465727

Paul Clancy

Posted Friday, March 07, 2008 5:53 AM

SSC Rookie

Group: General Forum Members
Last Login: Tuesday, May 07, 2013 12:48 PM
Points: 41, Visits: 175

Good article. My experience is that replication works well, but remains tricky (particularly when your structure changes regularly). A test environment is a must, and scripting and documenting everything takes a lot of the hard work out of it.

Post #465766

« Prev Topic | Next Topic »

20 posts, Page 1 of 2

1 2 »»

Permissions