Dolly was a Sheep in the Scottish highlands minding her own business, wondering each day what that glowing yellow warm thing in the sky was and marvelling at how good that green stuff on the ground tasted.  The fact that she was cloned rather than created was neither here nor there for her nor her sister clones. They ate, grew, and well you know the rest.

The same is true in IT, particularly with DBs in high performance systems.  Not the marvelling-at-the-sun or the chewing-green-stuff, but the concept of cloning for performance or to put it in database terms, DB Replication.

Some DBA pundits would have you believe that its a non-starter, this replication nonsense.  The School of Hard Knocks has shown us otherwise.  It boils down to the fact that a Relational Database Engine is a very poweful thing in the hands of the skilled because it can be tuned to one of two business operational modes. Namely Online Transaction Processing (OLTP) or Management Information System (MIS) modes.  One DB instance cannot be tuned to do both of these things brilliantly.  Passably maybe, but brilliantly? No, it simply can’t.

So what to so?  Replicate, people, replicate.  The key here is to have 2 instances of the same DB schema running at the same time.  Instance 1 handles OLTP operations from apps that need high throughput which are mainly insert and update operations, so this instance has naff-all indices.  Instance 2 handles MIS requests from spreadsheets and nosy-parkers who want to cut, slice, dice and otherwise extract data via select statements – historical data usually.

How does this work? Instance 2 follows instance 1’s transaction log, replicating all CRUD operations in its own DB.  Its reliable, elegant and its FAST.  The key lies in copying over the delta between the two DB’s transaction logs, so keeping in sync is a cinch.

Oh, and you can load up instance 2  – the MIS instance, remember – with as many indexes as you like.  Just as long as you remember to update your table and index stats daily (see my earlier blog on this), you will have a system that absolutely flies.  Instance 2 will usually keep its indices up to snuff when a transaction log change is detected.  Do check with your DBA/DB vendor.

One client was able to achieve in excess of 20 full object graph inserts into the underlying relational DB tables per second moving to this model. In Dev. Up hill. With a headwind.

You know it make sense so Just Do It (thanks, Nike)