Reduce load off primary server by using log shipping, or mirroring, or something else?

Question

I have previously asked the below question about LogShipping frequencies, but I think I might be approaching this problem incorrectly (LogShipping frequency)

Problem

I have a high volume site that I would like to divert some of the load off the primary server. The main area I wish to divert is the admin reporting queries which do not require the data to be real time (a lag of 5-10 mins is ok).

Experiments

LogShipping

This appeared to be ok, but I have an issue with the two ways that it handles connected clients.

1. Disconnect on restore. If I have a long running report from the web app, it will kill the report in the middle of processing.
2. Dont disconnect user. This is also not very good because if the admin db is getting a high amount of traffic it cannot get an exclusive lock on the db to do the restore and the lag of the data difference could push out to be more than 10+ mins.

Mirroring

1. This looked very promising, but seems more focused around data redundancy and the ability to have a failover db, not my actual aim of reducing overhead on the primary server.

Question

Is the something I am missing here, should I be trying something else (readonly database, better hardware, clustering), or am I completly missing the point of the above technologies?

Answer 1

Now that SQL Server 2012 has shipped, don't forget about AlwaysOn, which allows explicitly for reads scale out.

Mirroring works for what you describe, but the orchestration of database snapshots for reporting can get hairy (the mirror database is unreadable, you must report from database snapshots created periodically).

I'm not a fan of transactional replication, but it can be used for your scenarios and others more knowledgeable than me have came out In Defense of Transactional Replication.

Both of AlwaysOn and Transactional Replication offer what you want (scale out reads w/o disruption) and they both achieve near-real-time data latency. They differ though on price point: AlwaysOn is an SQL Server enterprise only feature and requires Windows Clustering which in turn requires Windows enterprise licensing, ie. it costs. Plus it requires an upgrade to SQL Server 2012. Transactional Replication on the other hand costs basically nothing in addition to what you already have (except if your whole setup is on Express Edition instances, which I doubt) but it comes at a cost because it will create friction with your development/deployment cycle, which now has to consider the replication impact (must add new articles to the publications, certain schema change operations require careful planning etc).

And for some out-of-the-box thinking consider how some have resorted to Using Service Broker instead of Replication.

Answer 2

First point is that in any situation where data is changing rapidly and extended-duration table locks are taken, you're going to have induced latency in updates to those locked tables. Unless you use snapshot isolation that is. Caveat: snapshot isolation may not be appropriate for a reporting server (it increases memory usage and can hit tempdb hard), it depends on how hard you're pushing that server.

Second point - have you looked into replication, specifically transactional replication? It should provide near-real-time updates without constantly pushing the database into recovery like log shipping does. Note that it may have a performance impact on your primary database, though.

Third point - Are your indexes appropriate to the workload your database is undertaking? If you're not sure, profile it. The Database Tuning Advisor is a useful guideline, but don't believe what it says ipso facto (like carpentry's "measure twice, cut once", I'd like to propose a similar rule for database tuning - "profile twice, index once"). Use the Index, Luke! is a good site to start with if you thought an index went in the back of a reference book.

Fourth point - if you've got reports that are doing massive rollups (i.e. months of data or whatever), is it okay to have some lag time in those reports? If so, you can schedule a task to create some summary tables (weekly, daily, hourly - depends on your requirements) and have your report hit those instead of summing ten million rows or whatever. Generally if you have a quarterly rolling income report (or whatever), then up-to-the-minute accuracy isn't necessary.