4

I have a web app that is used on multiple continents. The underlying transaction DB is locking up when large reports are run. I want to:

  1. make a realtime of the SQL Server 2005 database to a reports DB
  2. direct all reporting to the reports DB

In theory this will prevent transaction locking for inserts and updates. It is OK if the reports DB gets locked temporarily as long as the transactions continue. The reports DB can receive the new transactions when it is under a lighter load. What is the best way to maintain a near realtime copy of the transaction DB? Should I use replication, mirroring or a combination of the two? What general guidelines I should follow?

Thanks!

Tarzan
  • 4,270
  • 8
  • 50
  • 70
  • Have you considered using OLAP? – YWE Nov 17 '10 at 19:02
  • OLAP is not close to real time and is a specialized reporting scenario. Though it would probably help the performance of long-running reports, the real-time requirements trumps the technology. – Paul Sasik Nov 17 '10 at 19:09
  • I love OLAP and my customers love the decision making power it gives them. However, it doesn't provide a complete solution in this scenario. – Tarzan Nov 17 '10 at 21:25

2 Answers2

3

Mirroring = whole DB, replication is a subset usually based on what you "publish"

In this case, I'd use replication if you need near real-time. I'd consider mirroring if reporting could lag a few minutes or reports should be "static" for a period of time.

gbn
  • 422,506
  • 82
  • 585
  • 676
  • A 30 minutes lag is no problem. Is it practical to generate snapshots every 30 minutes in the mirroring scenario or would it still be better to use replication? – Tarzan Nov 17 '10 at 21:23
2

Use Sql Server replication for implementing this kind of architecture. E.g. replicate data from your main db server to the reporting server. It's not quite real time (few systems are) but can be very close.

There are several flavors with Sql Server replication and you'll probably want server-to-server. Also see this article about data warehousing and reporting. It describes the exact scenario that you're up against and your goals:

By design, transactional replication addresses the principal requirements for this scenario:

  • Transactional consistency
  • Low latency
  • High Throughput
  • Minimal overhead
Paul Sasik
  • 79,492
  • 20
  • 149
  • 189