21 July 2011

Denali-runner: Do DBAs dream of readable replicas?

Several years ago, I sat attentively in my new boss’ office, as he described a constant and painful thorn in the side of our company. The problem was with one particular highly visible and critical OLTP system that was now firmly rooted in production; it was being beaten up, badly. It was a large database, pushing into the terabyte range, and it was being kicked hard by the OLTP application to which it was married, and incessantly battered by at least four different reporting tools, each with hundreds of users scanning through tens of millions of records.

We needed a quick resolution, and the only hope was to find a way to offload reporting from the production database. “You will be a hero if you come up with a solution”, he said to me, in earnest, and it is not every day a new DBA, looking to establish his merit, hears that. I jumped in enthusiastically, gathering requirements and searching feverishly for an answer within the community.

The enthusiasm would be short lived. Firstly, the data in the reports needed to be real-time, or very close (no more than 5 minutes lag), and users needed constant access to the data. This ruled out log shipping, and database mirroring with snapshots. Secondly, our schema was locked down by the vendor and many of the 100+ tables did not have primary keys or unique clustered indexes; this ruled out transactional replication.

I turned my attention to third party tool vendors. After many weeks of endless explanations of the requirements and painstaking testing, I finally uncovered a tool that met our needs, more or less. It wasn’t cheap and it wasn’t designed exclusively for SQL Server, so it came with foibles and frustrations. I reported my recommendation back to my boss, though it was a little disheartening to offer what was essentially an expensive compromise. I remember wishing fervently, usually on sleepless nights, that the day would come soon when Microsoft would include in SQL Server a solution that would give me the reliability and native support of log shipping and database mirroring coupled with the real time data availability of replication, without the schema requirements, administrative overhead and complexity.

Cue a huge silent drum roll and let’s raise a glass to Microsoft, as it looks like they have finally come through, with the release of AlwaysOn and Availability Groups in SQL 11 (Denali). Together, these technologies extend the benefits of their High Availability/Disaster Recovery (HADR) offerings and also enable reading of the secondary or replica database, or groups of databases, on the secondary, tertiary and dare I say quaternary servers, so spreading the processing load across the local or remote networks.

There are those that will argue that their time and money might have been better spent developing a true data warehousing solution and maybe they have a point, but the fact is that the ability to offload reporting databases with this new technology offers me an immediate solution to a real world problem that we face every day. I am excited; in fact I haven’t been this excited about new Microsoft technology releases since SharePoint, Reporting Services and Xbox. It’s certainly a huge incentive for us to move to Denali sooner rather than later.

How much money it will save us depends on whether AlwaysOn and Availability Groups turn out to be Enterprise-only features, like snapshots in SQL Server 2005. If they do, well at least Microsoft has finally provided a very compelling reason to go down the Enterprise route. I hope, with baited breath, that it is only the primary server that is required to be Enterprise Edition, and all replica servers can be Standard Edition.

There I go dreaming again.

For more articles like this, sign up to the fortnightly Simple-Talk newsletter.


  • Rate
    [Total: 0    Average: 0/5]