Click here to monitor SSC

Denali-runner: Do DBAs dream of readable replicas?

Published 21 July 2011 2:50 pm

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.

6 Responses to “Denali-runner: Do DBAs dream of readable replicas?”

  1. rjh says:

    That database situation sounds very familar. Thank you for pointing out the new functionality in Denali that addresses it, I will be looking into it!

  2. Keith Rowley says:

    Sounds like I am finally hearing some really good reasons to upgrade to Denali. Unfortunately our shop is still solidly on the 2005 version and the primary piece of software we use has a requirement from the vendor of being run in 2000 compatibility mode.

  3. jerryhung says:

    Wow, thanks for this article, I didn’t know about these features before
    But now that’s another reasons we want to go Denali/SQL 2011, since we use Enterprise versions (but nothing in Terabytes)

    We are just slowly phasing out all SQL 2005 servers to move to SQL 2008, and soon to dream, move our SQL 2008 to Denali then

    I still think, with really short time LOG shipping/mirroring it’s possible to do report off the OLTP system instead, but I haven’t seen such case yet.

  4. TPowell_3557 says:

    I don’t know if all DBAs dream of readable replicas, but I do. :-)

  5. timothyawiseman@gmail.com says:

    I have run into somewhat similar situations before, but fortunately I have generally either had a proper primary key or had the freedom to add one, so replication has worked very well for our reporting needs.

    I understand that if you are locked into a schema without them then this would not at all work and I am glad to see that more options are coming.

  6. TodConover says:

    This idea probably does not solve your specific problem, but it might solve somebody elses. A long time ago my group faced a similar reporting problem in that there were many reports that had to run in a given timeframe at night after the backup and before production started in the morning. Most of the reports performed what was essentially a sequential scan of the data in order to find records – very painful. The solution for us was to pull data from the database in a single scan for all reports. The process looked at each record and if it was required for any of the nightly reports it was written to a file just for that report. Then the reports were run. This rather wacky approach cut processing time from many hours down to just a few minutes – the time it took to scan every record in the database just once, plus just a few seconds to run the reports themselves.

Leave a Reply