Yeah I totally agree. the staging server could be very minimal as long as the data itself was on the SAN.

Ideally, I'd like the two reporting DBs to be on...

not huge

currently the OLTP side is ~50GB and the Reporting DB is probably ~60GB or so.

growing pretty quickly though this is a very new system.

Our next phase in this project...

our transactional system is used by thousands of Auto Dealers all across the country and in Canada.

the reporting system is mostly used by tech support (not a huge number of...

The performance of SSIS has not been an issue for us in any way. In fact, the ability of SSIS to modify the batch sizes and the ability to use...

good points....

Partitioning is a really great tool but for my case (and likely in most cases) it doesnt solve all the problems associated with using your transactional system for reporting...

Rpt1 and Rpt2 are not aware of each other.

They are never synched with each other. Each of them are individually and seperately synched with the Prod OLTP system.

basically we have...

the reason for our production reporting issues were MANY

1) lack of proper indexes

2) poorly written sprocs

3) way too many joins

4) too much logic in the report sprocs

5) poorly designed DB

6)...

yeah weve thought about that.

1) all of our Reporting sprocs have isolation level set to read uncommitted so that they dont block. We are no worried about irrepeatable reads, etc.

2)...

great question....

I"m actually thinking of writing a follow up article on the CodeGen'd sprocs.

so we basically have an xml file that lists all reports by name and it lists the...

Don,

I dont understand your post regard the negatives to SSIS and regarding blocking....

(I think I might disagree with you but I want to make sure before I reply in that...

Good Question,

Log Shipping was one of the other choices\alternatives for us.

I've used log shipping in the past, and in my experience, it seemed fragile...

It would stop shipping logs, and we'd...

yes and we also have numerous versions of each "Report" sproc.

Our UI is a bit strange in that the users can pick a sorting and grouping option in the UI....

2 reasons really....

1) I was concerned that Replication would put a load on the Source Server (more so then simple mirroring)

2) the ease of setting up mirroring. literally click a...

thanks for the comments Mike.

One thing that really helped us was that 100% of our middle tier code, including all of our sprocs is Code Gen'd.

It's been a lot of...

this is good advice.

I'd also add that you should highly consider getting an MBA.

I ran my own consulting gig for 4 years and did quite well. But I didnt really...