Sql-server – Reduce load off primary server by using log shipping, or mirroring, or something else

log-shippingmirroringsql server

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?

Best Answer

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.

Related Question