<a href="http://g.adspeed.net/ad.php?do=clk&zid=15220&wd=468&ht=60&pair=as" target="_blank"><img style="border:0px;" src="http://g.adspeed.net/ad.php?do=img&zid=15220&wd=468&ht=60&pair=as" alt="i" width="468" height="60"/></a>
Log in
::
Register
::
Not logged in
Home
Tags
Articles
Editorials
Stairways
Forums
Scripts
Videos
Blogs
QotD
Books
Ask SSC
SQL Jobs
Training
Authors
About us
Contact us
Newsletters
Write for us
<a href="http://g.adspeed.net/ad.php?do=clk&zid=15491&wd=120&ht=300&pair=as" target="_top"><img style="border:0px;" src="http://g.adspeed.net/ad.php?do=img&zid=15491&wd=120&ht=300&pair=as" alt="i" width="120" height="300"/></a>
Recent Posts
Recent Posts
Popular Topics
Popular Topics
Home
Search
Members
Calendar
Who's On
Home
»
SQLServerCentral.com
»
Editorials
»
A Cheap Fix?
19 posts, Page 1 of 2
1
2
»»
A Cheap Fix?
Rate Topic
Display Mode
Topic Options
Author
Message
Steve Jones - SSC Editor
Steve Jones - SSC Editor
Posted Wednesday, March 03, 2010 8:39 PM
SSC-Dedicated
Group: Administrators
Last Login: Today @ 4:46 PM
Points: 31,433,
Visits: 13,745
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
Jeff Moden
Jeff Moden
Posted Wednesday, March 03, 2010 10:02 PM
SSC-Dedicated
Group: General Forum Members
Last Login: Today @ 7:51 PM
Points: 32,910,
Visits: 26,800
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 "
R
ow-
B
y-
A
gonizing-
R
ow".
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."
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
Post #876536
JamesNZ
JamesNZ
Posted Thursday, March 04, 2010 1:44 AM
Valued 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
reuben.anderson
reuben.anderson
Posted Thursday, March 04, 2010 2:12 AM
SSC Rookie
Group: General Forum Members
Last Login: Monday, April 15, 2013 8:28 AM
Points: 27,
Visits: 135
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
chris.turner
chris.turner
Posted Thursday, March 04, 2010 3:11 AM
SSC Veteran
Group: General Forum Members
Last Login: Thursday, April 18, 2013 5:02 AM
Points: 206,
Visits: 546
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
Jeff Moden
Jeff Moden
Posted Thursday, March 04, 2010 5:52 AM
SSC-Dedicated
Group: General Forum Members
Last Login: Today @ 7:51 PM
Points: 32,910,
Visits: 26,800
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 "
R
ow-
B
y-
A
gonizing-
R
ow".
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."
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
Post #876745
Jeff Moden
Jeff Moden
Posted Thursday, March 04, 2010 6:03 AM
SSC-Dedicated
Group: General Forum Members
Last Login: Today @ 7:51 PM
Points: 32,910,
Visits: 26,800
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 "
R
ow-
B
y-
A
gonizing-
R
ow".
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."
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
Post #876763
rmechaber
rmechaber
Posted Thursday, March 04, 2010 6:14 AM
SSChasing Mays
Group: General Forum Members
Last Login: Wednesday, May 15, 2013 7:43 AM
Points: 656,
Visits: 2,921
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
Dennis Wagner-347763
Dennis Wagner-347763
Posted Thursday, March 04, 2010 6:33 AM
SSC Rookie
Group: General Forum Members
Last Login: Thursday, January 17, 2013 5:28 AM
Points: 42,
Visits: 170
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
Martin Vrieze
Martin Vrieze
Posted Thursday, March 04, 2010 7:09 AM
Old Hand
Group: General Forum Members
Last Login: Tuesday, December 07, 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 »
19 posts, Page 1 of 2
1
2
»»
Permissions
You
cannot
post new topics.
You
cannot
post topic replies.
You
cannot
post new polls.
You
cannot
post replies to polls.
You
cannot
edit your own topics.
You
cannot
delete your own topics.
You
cannot
edit other topics.
You
cannot
delete other topics.
You
cannot
edit your own posts.
You
cannot
edit other posts.
You
cannot
delete your own posts.
You
cannot
delete other posts.
You
cannot
post events.
You
cannot
edit your own events.
You
cannot
edit other events.
You
cannot
delete your own events.
You
cannot
delete other events.
You
cannot
send private messages.
You
cannot
send emails.
You
may
read topics.
You
cannot
rate topics.
You
cannot
vote within polls.
You
cannot
upload attachments.
You
may
download attachments.
You
cannot
post HTML code.
You
cannot
edit HTML code.
You
cannot
post IFCode.
You
cannot
post JavaScript.
You
cannot
post EmotIcons.
You
cannot
post or upload images.
Copyright © 2002-2013 Simple Talk Publishing. All Rights Reserved.
Privacy Policy.
Terms of Use.
Report Abuse.
