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


The Brittleness of Replication


The Brittleness of Replication

Author
Message
Dave Poole
Dave Poole
SSCoach
SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)

Group: General Forum Members
Points: 17118 Visits: 3403
What about full text search. It could have been good but the Solr/ElasticSearch horse has well and truly bolted.

LinkedIn Profile
www.simple-talk.com
Tom Thomson
Tom Thomson
One Orange Chip
One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)

Group: General Forum Members
Points: 26389 Visits: 12506
I used replication a lot for about 7 years. This was all transactional replication, except for some(very little) ad-hoc snapshot replication. It certainly had some defects. I called it fragile, rather than brittle, because when it broke one generally didn't have to clean up the mess and start again from creating publications and subscriptions, one just had to mess about a bit and then get it going again, which only rarely would require even reinitialising a subscription. For many problems it was possible to write monitoring software to detect them and do the mesing about and restart (not reinitialise) a subscription - of course that had to be monitored to make sure that it didn't just keep trying again. But there were some really nasty bugs. MS may have fixed them in later versions - I never used replication on anything later than SQL 2000, except for some quick and dirty checks that what we had done before on 2000 would still work on 2008 - but I suspect not. As well as teh bugs, it was harder to use and manage than it ought to have been; and from what I've seen (and from Steve's article) it seems that MS hasn't done anything to make it easier. That's not a good thing. The documentation was prety terible too, but that may for all I know have improved.

But although replication has not improved as fast as MS's customers would like, MS does a lot better at improving hings than some other software suppliers. I remember one product from CISCO that incorporated MSDTC (the SQL 2000 name for SQL Express without tools). Not only did CISCO not provide enhancements, they didn't fix bugs; and they built the thing in such a way that it was impossible to apply MS's fixes to SQL Server, which was a big problem for anyone who used it. My big problem was that the envirenment in which user software added to the thing had to run had race conditions that were not resolvable in the user software, and CISCO was not going to fix them. But for some of its users perhaps it was an especially big problem early in 2003 when this malware hit the internet, and owners of that product couldn't apply the fix MS provided because they were stuck with SQL Server 2000 RTM and no subsequent SP or fix could be applied. I saw similar (perhaps not as extreme as that one) problems with other products too. So let's not get too upset with MS about not improving everything, some others are worse.

Tom

Jim P.
Jim P.
SSCommitted
SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)

Group: General Forum Members
Points: 1887 Visits: 2215
I hate replication. And I do my best to avoid it if at all possible.

Back in 2002 It was my responsibility to build a DR plan for our SQL servers at our offsite 50 miles away connected by 4 T-1s. So I started the first effort try to use transactional replication to a set of standby databases. It failed the first test and then a month later failed the retest. So it was back to square one.

Then I come to my current company and they built the software to have a master DB at the headquarters and supposedly replicate "universal" data like suppliers and standard codes down to the individual faacility DBs. It would only work on a facility that had at least two solid T-1 connections. Otherwise the facilities had to be co-located on servers at HA and then would RDP into facility or terminal servers. They finally shut it down for lack of customers.

So onto the next SW we produced. It had a master DB and then two they had built two silos of data to try and load balance the customers. They used replication to move data down hill in the individual silos. But they never differentiated the replication so it was duplicating the data in both silos. And taking up the space.

Now when someone says replication it takes all my personal skills not to shudder. If I can avoid it -- I will. I'd rather build everything by had to transfer data from DB to DB.



----------------
Jim P.

A little bit of this and a little byte of that can cause bloatware.
Brandon J Williams
Brandon J Williams
SSC-Enthusiastic
SSC-Enthusiastic (147 reputation)SSC-Enthusiastic (147 reputation)SSC-Enthusiastic (147 reputation)SSC-Enthusiastic (147 reputation)SSC-Enthusiastic (147 reputation)SSC-Enthusiastic (147 reputation)SSC-Enthusiastic (147 reputation)SSC-Enthusiastic (147 reputation)

Group: General Forum Members
Points: 147 Visits: 930
Replication is not brittle, buggy, or unstable. Unreliable network connections can cause replication to fail, and the replication novice will interpret this as a bug or as replication being unstable.
Robert Sterbal-482516
Robert Sterbal-482516
SSChasing Mays
SSChasing Mays (615 reputation)SSChasing Mays (615 reputation)SSChasing Mays (615 reputation)SSChasing Mays (615 reputation)SSChasing Mays (615 reputation)SSChasing Mays (615 reputation)SSChasing Mays (615 reputation)SSChasing Mays (615 reputation)

Group: General Forum Members
Points: 615 Visits: 285
Replication doesn't work as well as log shipping on small pipes.

I would like to see a better tool set as well, at least to the point of being able to script the changes needed to bring replication up and down.
Brandon J Williams
Brandon J Williams
SSC-Enthusiastic
SSC-Enthusiastic (147 reputation)SSC-Enthusiastic (147 reputation)SSC-Enthusiastic (147 reputation)SSC-Enthusiastic (147 reputation)SSC-Enthusiastic (147 reputation)SSC-Enthusiastic (147 reputation)SSC-Enthusiastic (147 reputation)SSC-Enthusiastic (147 reputation)

Group: General Forum Members
Points: 147 Visits: 930
Log shipping cannot do what replication does.

I agree, over small pipes, or an unreliable or slow network connection, replication may timeout or fail or something like that. But there are replication agent parameters and other replication settings which can mitigate this. This is why proper design and pre-deployment testing is crucial. Microsoft also recommends a fast network of 100 Mbps or faster.

The cool thing is, once a replication solution is designed, tested, and deployed correctly, nothing can beat its synchronization capabilities.
chrisn-585491
chrisn-585491
Hall of Fame
Hall of Fame (4K reputation)Hall of Fame (4K reputation)Hall of Fame (4K reputation)Hall of Fame (4K reputation)Hall of Fame (4K reputation)Hall of Fame (4K reputation)Hall of Fame (4K reputation)Hall of Fame (4K reputation)

Group: General Forum Members
Points: 3964 Visits: 2565
Steve concluded with this statement:

"I have to believe there are developers at Microsoft that would love to make the product better. I understand the need for new features, and continuing sales, but devoting a percentage of time to improving existing features, regardless of the Connect votes or marketing wishes, would help make the platform that much better for everyone."

Ask any long time SQL DBA or .NET developer, there are hundreds of low hanging bugs and excellent minor feature requests that get ignored. Then we get interfaces like Windows 8 or Visual Studio 2013 and layoffs that include good folks that are MCMs. WTF Redmond???
patrickmcginnis59 10839
patrickmcginnis59 10839
SSCarpal Tunnel
SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)

Group: General Forum Members
Points: 4462 Visits: 5925

I remember one product from CISCO that incorporated MSDTC (the SQL 2000 name for SQL Express without tools).

<nitpick>MSDE</nitpick>

MSDTC is the transaction manager isn't it? Just being picky about an otherwise informative comment!

to properly post on a forum:
http://www.sqlservercentral.com/articles/61537/
skeleton567
skeleton567
SSC Eights!
SSC Eights! (835 reputation)SSC Eights! (835 reputation)SSC Eights! (835 reputation)SSC Eights! (835 reputation)SSC Eights! (835 reputation)SSC Eights! (835 reputation)SSC Eights! (835 reputation)SSC Eights! (835 reputation)

Group: General Forum Members
Points: 835 Visits: 407
Replication, like so many other features, does need to be continuously updated and improved. I used replication also from version 6.5 to move custom pricing and billing data for national accounts around the 48 states to provide consolidated billing. And we were moving large quantities of data. There were many failures and retransmissions mostly due to phone system quality. Many times we had to stop the process and resync via overnighting large datasets at a known point in time.

I can't speak to current replication because I never used it after version 7, but the multimillion dollar project eventually died mostly due to the lack of adequate attention to problems.

Sometimes it seems that new features are more to spread product to geeks than to support heavy users. We don't need all the latest toys, just a solid platform and good performance. Further, it's easier to sell management on 'new and better' than to sell them on need for maintenance and improvement and bug fixing.

I think you are right about the people at Microsoft and elsewhere who are still out there and want to make the fixes and improvements and who care about accuracy and dependability. But they probably often have the experience I had in one position where the stated priority of the owner of the company was that data will be AVAILABLE, even if not ACCURATE.

In my last position, I had quite a number of fixes to proven inaccuracies in systems which timid Project Managers would not implement because of what they perceived as risk in changing the system.
Robert.Sterbal
Robert.Sterbal
Say Hey Kid
Say Hey Kid (679 reputation)Say Hey Kid (679 reputation)Say Hey Kid (679 reputation)Say Hey Kid (679 reputation)Say Hey Kid (679 reputation)Say Hey Kid (679 reputation)Say Hey Kid (679 reputation)Say Hey Kid (679 reputation)

Group: General Forum Members
Points: 679 Visits: 2000
Brandon,

The posts in this forum suggest otherwise.

We use replication at hundreds of sites, yet fail to use it to get a near real time archive of our in house CRM system (We do a database update at 2 am every day).

Log shipping doesn't replace all of replication's features, it provides an alternate way of solving a problem on a small pipe.

Most of the issues I'm reading about would be helped by better reporting of replication status and easier replication repair.
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