Tuesday, April 24, 2012

CQRS & Separation of Church and State

CQRS is a design pattern acronym which stands for Command Query and Responsibility Segregation.  I would call this the R/RW pattern, or the separation of Church and State.  In a reporting application design, it would silo the read queries from the write commands. 

In its simplicity, it is designing two services for each application interface, one that reads and one that writes. 

That is it. That is the entirety of the CQRS pattern.

CQRS, Task Based UIs, Event Sourcing agh! | Greg Young

Since the bulk of applications have highly concurrent reads and minimal or bulk writes, thinking about this pattern during design is very important.  What if you were able to architect a system where you could spin up unlimited services that provide querying, and a few services that provide writes/updates, queuing them up for later?  Would this improve performance and scalability? What if you could split the data for these systems into unlimited read-only models and a few read/write models?

What if the data in the read-only models was effortlessly in-synch with the read/write models, with minimal latency and lazy consistency?

This is one of the tenets behind Azure and cloud computing in general.

You can do this in a SQL Server environment, albeit not so effortlessly and with some gotchas, using Log Shipping, Undo and database snapshots for a read-only picture of the database.  There is some latency, and keeping the databases in synch adds lots of overhead. 

SQL 2012 appears to solve some of the limitations of SQL 2008 R2 log shipping with it’s AlwaysOn capabilities for rolling out read-only, distributed clusters. It still doesn’t seem as effortless as it should be.

Replication also offers a solution, though it can be brittle and has its limitations and maintenance overhead. 

SANs also offer a high-cost solution for fast bulk loads, with some downtime.

You cannot use SAN snapshots of an active SQL Server database file(s) unless you detach them first. This is very intrusive.

I actually do this for a situation where I need 800GB of data from one server to another every night.

  • Detach the database on ServerA
  • SAN snapshot serverA drives F:,G:,H:,I:
  • reattach database on ServerA
  • detach database on ServerB
  • detach drive L,M,N,O on ServerB
  • mount new SAN snapshots L,M.N,O on ServerB
  • attach database files from L,M,N,O on ServerB

It requires an outage on both, but is very fast to virtually move the data to the other server.


Change data capture is another solution for maintaining multiple concurrent copies of a data stream.  SQL 2012 now has an Oracle CDC feature which improves the integration and reporting story.

The above solutions are very hammer-based approaches to the solution and don’t necessarily relate to CQRS.  Message queuing, eventing, change data capture and pub/sub systems all seem to fall within the CQRS design pattern, though they are really just an extension to the thought process. 

Chad Meyers has an excellent posting on Laws, Rules, Principles, Patterns, and Practices.

Another set of excellent posts on the concept of CQRS.


There doesn’t seem to be a silver bullet to the solution just yet, though I’m sure someone will get it right sooner or later. The CQRS pattern is merely that, a pattern. The devil is in the details.

No comments: