Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

A Cheap Fix? Expand / Collapse
Author
Message
Posted Wednesday, March 3, 2010 8:39 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: Administrators
Last Login: Yesterday @ 3:05 PM
Points: 31,284, Visits: 15,750
Comments posted to this topic are about the item A Cheap Fix?






Follow me on Twitter: @way0utwest

Forum Etiquette: How to post data/code on a forum to get the best help
Post #876502
Posted Wednesday, March 3, 2010 10:02 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 7:37 PM
Points: 35,617, Visits: 32,212
With hardly an exception, my stance is that users who write their own queries should not have access to the production server... period. A separate "report" instance is the way to go especially if you have a SAN that can rebuild the reporting instance in the proverbial blink of an eye.


--Jeff Moden
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #876536
Posted Thursday, March 4, 2010 1:44 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Wednesday, September 29, 2010 2:32 AM
Points: 73, Visits: 274
Couldn't you consider using Resource Governor to control what these "power users" can do to stop affecting everything with their users?
Post #876627
Posted Thursday, March 4, 2010 2:12 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Thursday, October 30, 2014 3:36 AM
Points: 60, Visits: 166
I find that there's rarely (very rarely) a need for business users to write their own queries. Most adhoc data functions can be solved through OLAP tools. If there's a justification for allowing adhoc analysis, then there's a justification for delivering a controlled, safe interface to permit this.
Post #876639
Posted Thursday, March 4, 2010 3:11 AM


SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Friday, October 31, 2014 4:46 AM
Points: 206, Visits: 566
In twenty years (yes I know I'm still a new-starter!) I've never been allowed to report against a production environment. Data has always been mirrored to a second box, or transformed into a datawarehouse. If real-time data is needed (and it rarely is...not really!) then a real-time replication is put in place.
Post #876674
Posted Thursday, March 4, 2010 5:52 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 7:37 PM
Points: 35,617, Visits: 32,212
JamesNZ (3/4/2010)
Couldn't you consider using Resource Governor to control what these "power users" can do to stop affecting everything with their users?


Heh... I do use "Resource Governor".... it's called "Denial of Access".


--Jeff Moden
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #876745
Posted Thursday, March 4, 2010 6:03 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 7:37 PM
Points: 35,617, Visits: 32,212
reuben.anderson (3/4/2010)
I find that there's rarely (very rarely) a need for business users to write their own queries. Most adhoc data functions can be solved through OLAP tools. If there's a justification for allowing adhoc analysis, then there's a justification for delivering a controlled, safe interface to permit this.


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.

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.


--Jeff Moden
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #876763
Posted Thursday, March 4, 2010 6:14 AM


Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: 2 days ago @ 9:31 AM
Points: 717, Visits: 3,037

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.


Jeff, I infer your quotes around 'snapshot' to mean that you were not using SQL Server snapshots? B/c queries against a daily SQL snapshot would be essentially querying the live data. Did you implement this with a SAN utility? (I don't get involved much with mgmt. of our SAN).

Thanks,
Rich Mechaber
Post #876775
Posted Thursday, March 4, 2010 6:33 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, November 10, 2014 5:54 AM
Points: 48, Visits: 204
I find that typically "children must play." There are those upper-level, high-profile, strongly-connected, politically-minded (that's enough adjectives) "business people" who took a database theory class while getting their degree and are suddenly experts. They just have to get their hands into SQL and write queries, no matter how inefficient, now matter how misdirected, no matter how poor they are at answering the question. They have enough pull in the company to get access to SQL and then they run a query right before going to a meeting that brings the entire production server to its knees. Telling them not to do it works for a while. Complaining to a higher up might get them a slap on the hand, but the sting goes away with time, and they are back at it again. Unfortunately there is a trade-off between writing all their queries and having no time to do anything else or just letting them play on their own.

Yes, you can deny them access, but their political pull and "absolute necessity for having access" will get them in unless you want your budget slashed for the next year. You can limit what they can do, but then they'll complain that you're "handcuffing their ability to do their job with the utmost efficiency." It's far better to build them their own sandbox to play in rather than letting them mess up the sand in everyone else's.

Of course, this reflects just a hypothetical scenario and in no way resembles any of the companies I've worked for or any of the "business people" I've worked with.
Post #876796
Posted Thursday, March 4, 2010 7:09 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Tuesday, December 7, 2010 8:19 AM
Points: 327, Visits: 123
That or limiting the access from the power users to prevent them from causing issues with large queries that might not be efficiently run.


That's It!!! Lock 'Em Down and Lock 'Em Out!!!

Entire careers in database marketing have been made by building offline reporting systems to get around some IT's narrow viewpoint that power users should be restricted. Far better to move power users to an alternate system and assign resources to assist in optimizing performance on those systems. If these power users are any good at what they do, the business payback is easily quantifiable and of a very high order of magnitude.
Post #876815
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse