sqlharmony (8/14/2012)
Log shipping is the real thing here that is killing your idea as others have mentioned you will not be able to report against the databases while the transactions logs are being applied, not to mention that the reporting activity could impact the log shipping processes also. Why are you choosing to use log shipping to maintain your DR databases? If you are running sql 2000 that might explain it but assuming you are running one of the later versions (and also assuming you are running the enterprise version) you could take advantage of the mirroring feature. And, you can create immediate snapshots of your mirrored databases which you could report against. So you could run a job every x minutes that would build new snapshots and drop the old. This would give you a near real time reporting environment. Of course, there is also the downside of the reporting users' connections being terminated each time you generate a new snapshot but this is a different story. My point being, log shipping is a very bad technology to use to maintain a real time reporting environment. Mirroring is better but both have their downsides.
+1 Also, if you have the money... You can simply set up a reporting server that acs as a subscriber to your main database. The reality is, you have to assess the situation completely. If you system is is not loaded down, why not just report from the production database? I used to work at a company where the OLTP database was used for real-time reporting non-stop, but the actual transactions to the database were only in the 1000's per day. Some of these reports were pretty large and took a lot of processing. However, they never slowed down other reports or processing. Again, it depends on your company and the usage on the server. In most cases, I would not recommend reporting off of the production database. However, if the company is unwilling to spend the money for a proper setup, you have to weigh the benefits and risks of other routes.
Jared
CE - Microsoft