Replication (or, SQL Server's "canary in a coal mine")

As I mentioned previously, one of the first areas I worked on when I joined Exony was to overhaul the use of replication in our reporting and analytics product. I can vividly recall the degree of mistrust my new colleagues felt back then towards SQL Server replication as a component of our product offering. I felt that their mistrust was unjustified; but I hadn't earned the right to just tell them so! :-) So, I took on the task of redesigning our replication strategy from the ground up, and I genuinely believe that my colleagues (particularly those engaged in directly installing and supporting our products) are now much more comfortable with the concepts of replication , much more confident in its role within our products, and much less inclined to immediately pick up the phone and call me if there's a "replication problem" in the lab or on a customer site. Arguably, replication now serves a secondary purpose for us; it's a fantastic environmental health check utility.

Say what? Well, replication is so dependant upon the wider environment within which the database servers sit, that many issues which might arise in that environment will manifest themselves most quickly and visibly within replication. A network link is interrupted? Watch the distribution agents fail. Someone changes the firewall settings? Or applies inappropriate Group Policy settings? See authentication fail. Bottom line; if the distributed environment within which replication operates varies even a little from optimal, then replication will probably let you know (via the propagation of red X's in Replication Monitor) that all is not well.

Back then, a "problem" with replication would have sent shivers down the spine. Now, when a problem manifests itself in replication, my colleagues know to check the environment first. In the vast majority of cases, replication is not the problem; it is merely highlighting a problem which exists elsewhere, long before other symptoms might have become visible.

I wouldn't go so far as to say that organisations should always deploy replication as a network environment monitoring tool! ;-) Even so, that functionality is inherent in any but the most trivial replication topology; might as well take advantage of it. Replication doesn't just fail without cause; if it experiences difficulties, then *something* is causing those difficulties. When miners used to take a canary with them into a coal-mine, they knew to leave the area if the canary fell off its perch; we know to suspect the network environment when Replication Monitor falls off its perch.

This blog has been migrated to new software on a different server (http://www.multidimensional.me.uk) and comments on this post on *this* blog are now closed. All existing comments have been copied to the equivalent post on the new blog. If you still wish to comment on this post, please use the equivalent post at: http://www.multidimensional.me.uk/