March 4, 2010 at 10:41 am
Jeff Moden (3/4/2010)
It's not a common occurance, for sure, but in one company I worked in, there were some remarkable business folks who were actually very good at defining what they wanted in the form of a query. Because of the nature of the products (company was a huge global MLM that sold telephone time and other telephony/internet products), ad hoc analysis for individual customers was a way of life. There would have been no way for IT to keep up with all daily and frequent query/report requests (small shop doing a very big job). The Finance and Accounting departments both needed the ability to write their own queries at the drop of a hat... any hat. We gave it to them in the form of their own server with a SAN "snapshot" delivered from production every night at midnight. I have to say that with only the occasional exception, it worked out very well for all of them and IT. If someone screwed something up, it took less than 2 minutes to deliver a new SAN "snapshot" without even slowing down production and everything was back up and running.
Really, small world. I worked for an ASP where we provided services for MLM's and we did some of the same things you mention here
The key here is that the folks in IT were considered to be "heroes" because the uses got everything they needed when they needed it because they had all the access to data they needed and without having to wait for a very busy IT department to crank out a query for them. Shoot... the business users knew more about the data and tables in their respective areas than the IT department did. It worked out very well.
Agreed.
Since I know the source of the editorial, I would also add some more to the story. Some of these downstream servers also replicate to more downstream servers - up to four of them. The process is quite convoluted and some of the downstream servers are no longer used. Every server has Enterprise edition installed. I am now working on a plan to consolidate due to a network migration, usage patterns, and optimization.
I concede that one server as a report server can be quite useful and less costly than increasing staff to support the reporting needs. I find it counter productive and cost-inefficient to use four or more reporting servers due to lack of tuning exercises. This is an inherited environment.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
March 4, 2010 at 1:29 pm
I agree with the seperate server to do whatever instead of touching the production server. Actually we did the same thing. We port data from Oracle production database to dedicated SQL server for anything related to reporting/queries/ssrs etc. Its a day old since the SSIS jobs run every night but atleast it takes the load of the production Oracle database.
March 4, 2010 at 1:50 pm
I'm about to do something very similar. Moving SSRS and ad hoc reports to a separate server. Will have a mix of copies of the production databases, and some actual warehousing (denormalization via ETL and all that, as well as SSAS cubes). There are a lot of reasons for this, but the main one is to move the report CPU cycles, et al, out of the way of the OLTP transactions.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
March 4, 2010 at 2:33 pm
I'm a big advocate of data warehousing for reporting.
Structuring data to support reporting for faster querying
OLTP and OLAP not competing for resources
OLTP schema changes generally only affects your ETL and not your reporting data layer
I'm not sure how I feel about users writing thier own queries. It tends to be some great people do some really great work and it works out well for a period, then those people leave and others start using thier work without understanding, schema changes break them, and you've got a nightmare on your hands.
However we do have a couple of people that we do allow this for, because we don't wish to be the bottleneck for them...
Viewing 4 posts - 16 through 18 (of 18 total)
You must be logged in to reply to this topic. Login to reply