SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


The Trouble with Transactional Replication and large articles.


The Trouble with Transactional Replication and large articles.

Author
Message
Edward.Polley 76944
Edward.Polley 76944
SSC Veteran
SSC Veteran (232 reputation)SSC Veteran (232 reputation)SSC Veteran (232 reputation)SSC Veteran (232 reputation)SSC Veteran (232 reputation)SSC Veteran (232 reputation)SSC Veteran (232 reputation)SSC Veteran (232 reputation)

Group: General Forum Members
Points: 232 Visits: 263
Comments posted to this topic are about the item The Trouble with Transactional Replication and large articles.
acheras
acheras
Grasshopper
Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)

Group: General Forum Members
Points: 24 Visits: 188
Thx for your article.

I have only one question:
is there a way to find the pending transactional replication commands in order to apply them manually to the remote subscriber and exclude them from the sync proccess? Maybe that would fix the problem in some cases without the need to run any other scripts. For example i usually get the error "the row was not found on the subscriber when applying the replication command"
On the other hand how could i find the data differences between large tables in different databases and servers, connected just with an isdn phone connection?

Thx in advance,

Antonios Cheras
Megistal
Megistal
Hall of Fame
Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)

Group: General Forum Members
Points: 3439 Visits: 2565
On step four, synchronize the data, I understand that you have, from whatever mean you want to, select missing rows from the published table and insert them into the subscribed table?

A "manual replicated step" in other words?
sjimmo
sjimmo
SSCertifiable
SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)

Group: General Forum Members
Points: 5388 Visits: 2907
A good article on a very complex subject. My question though is why would you have articles that can be reinitialized in a matter of seconds or minutes to an article that can take hours? Also, on publications with hundreds of millions of rows, is bulk loading actually faster and better? What do you do about records that are added or modified while you are loading the initial load?

Steve Jimmo
Sr DBA
“If we ever forget that we are One Nation Under God, then we will be a Nation gone under." - Ronald Reagan
jei33
jei33
Forum Newbie
Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)

Group: General Forum Members
Points: 9 Visits: 157
Nice article, thanks.
Question:
if you have detected that huge article as the conflicting one, wouldn't be enough just to drop that article from the publication, instead of deleting the whole publication?
Thanks in advance
Edward.Polley 76944
Edward.Polley 76944
SSC Veteran
SSC Veteran (232 reputation)SSC Veteran (232 reputation)SSC Veteran (232 reputation)SSC Veteran (232 reputation)SSC Veteran (232 reputation)SSC Veteran (232 reputation)SSC Veteran (232 reputation)SSC Veteran (232 reputation)

Group: General Forum Members
Points: 232 Visits: 263
Yes pending replicaiton commands can be reviewed and rerun but it's a bit involved for a post reply. I will write about that in a following article. If you need help immediately the Stairway To SQL Server Replication articles on this site are a good start. This article is based upon real experience where there were too many failed commands to restore individually and business critical replication was down. This is the fastest method I have found to get replication back up.
Edward.Polley 76944
Edward.Polley 76944
SSC Veteran
SSC Veteran (232 reputation)SSC Veteran (232 reputation)SSC Veteran (232 reputation)SSC Veteran (232 reputation)SSC Veteran (232 reputation)SSC Veteran (232 reputation)SSC Veteran (232 reputation)SSC Veteran (232 reputation)

Group: General Forum Members
Points: 232 Visits: 263
Yes step 4 is a manual resync since the delta rows missing on the subscriber are relatively few compared to the total rows in the article. I use Redgate's Data Compare to resynch since it is fast and accurate. Keep in mind in my case the primary business concern was on the current data as this is how the business i smonitoried. Data older than a few hours ago was not critical.
Greg Shinder
Greg Shinder
SSC-Enthusiastic
SSC-Enthusiastic (153 reputation)SSC-Enthusiastic (153 reputation)SSC-Enthusiastic (153 reputation)SSC-Enthusiastic (153 reputation)SSC-Enthusiastic (153 reputation)SSC-Enthusiastic (153 reputation)SSC-Enthusiastic (153 reputation)SSC-Enthusiastic (153 reputation)

Group: General Forum Members
Points: 153 Visits: 465
Hi,
Thanks for the article. Makes complicated things simple.
Only one question.
In case when all the articles in the publication are set to @pre_creation_cmd = 'None', do I still need to drop and re-create the publication?
In my case, I have approximately 20 articles per publication, they are not as big, but I cannot allow re-initialization, since the subscriber receives data replicated from different servers.

Thanks,
Greg
paul.millar
paul.millar
SSC-Enthusiastic
SSC-Enthusiastic (129 reputation)SSC-Enthusiastic (129 reputation)SSC-Enthusiastic (129 reputation)SSC-Enthusiastic (129 reputation)SSC-Enthusiastic (129 reputation)SSC-Enthusiastic (129 reputation)SSC-Enthusiastic (129 reputation)SSC-Enthusiastic (129 reputation)

Group: General Forum Members
Points: 129 Visits: 473
Hi,

Short of Redgate Data Compare, check this out for generating Tablediff command lines...has saved my bacon a few times, and is surprisingly quick.

(I've got Red Gate tools (which are cool!), but can't install them on isolated customer boxes on the other side of restrictive VPNs or DMZ's - whereas Tablediff ships with all 2008+ sql)

http://blogs.msdn.com/b/repltalk/archive/2010/02/21/how-to-run-tablediff-utility-for-all-replicated-published-tables-in-sql-2005-or-sql-2008.aspx
Jay Kusch
Jay Kusch
Valued Member
Valued Member (68 reputation)Valued Member (68 reputation)Valued Member (68 reputation)Valued Member (68 reputation)Valued Member (68 reputation)Valued Member (68 reputation)Valued Member (68 reputation)Valued Member (68 reputation)

Group: General Forum Members
Points: 68 Visits: 194
What happens after replication is up and running and later you need to add a new article?

Having the @sync_method set to 'for replication only" would then mean you would have to take care off adding the article and data to the subscriber ... is this true?



Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search