SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


A Cheap Fix?


A Cheap Fix?

Author
Message
Steve Jones
Steve Jones
SSC Guru
SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)

Group: Administrators
Points: 63897 Visits: 19116
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
My Blog: www.voiceofthedba.com
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)

Group: General Forum Members
Points: 87778 Visits: 41121
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.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
JamesNZ
JamesNZ
SSC-Enthusiastic
SSC-Enthusiastic (133 reputation)SSC-Enthusiastic (133 reputation)SSC-Enthusiastic (133 reputation)SSC-Enthusiastic (133 reputation)SSC-Enthusiastic (133 reputation)SSC-Enthusiastic (133 reputation)SSC-Enthusiastic (133 reputation)SSC-Enthusiastic (133 reputation)

Group: General Forum Members
Points: 133 Visits: 274
Couldn't you consider using Resource Governor to control what these "power users" can do to stop affecting everything with their users?
reuben.anderson
reuben.anderson
SSC-Enthusiastic
SSC-Enthusiastic (143 reputation)SSC-Enthusiastic (143 reputation)SSC-Enthusiastic (143 reputation)SSC-Enthusiastic (143 reputation)SSC-Enthusiastic (143 reputation)SSC-Enthusiastic (143 reputation)SSC-Enthusiastic (143 reputation)SSC-Enthusiastic (143 reputation)

Group: General Forum Members
Points: 143 Visits: 217
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.
chris.turner
chris.turner
SSC Veteran
SSC Veteran (214 reputation)SSC Veteran (214 reputation)SSC Veteran (214 reputation)SSC Veteran (214 reputation)SSC Veteran (214 reputation)SSC Veteran (214 reputation)SSC Veteran (214 reputation)SSC Veteran (214 reputation)

Group: General Forum Members
Points: 214 Visits: 568
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.
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)

Group: General Forum Members
Points: 87778 Visits: 41121
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". :-P:-DHehe;-)

--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.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)

Group: General Forum Members
Points: 87778 Visits: 41121
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.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Rich Mechaber
Rich Mechaber
SSCommitted
SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)

Group: General Forum Members
Points: 1561 Visits: 3665

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
Dennis Wagner-347763
Dennis Wagner-347763
SSC-Enthusiastic
SSC-Enthusiastic (128 reputation)SSC-Enthusiastic (128 reputation)SSC-Enthusiastic (128 reputation)SSC-Enthusiastic (128 reputation)SSC-Enthusiastic (128 reputation)SSC-Enthusiastic (128 reputation)SSC-Enthusiastic (128 reputation)SSC-Enthusiastic (128 reputation)

Group: General Forum Members
Points: 128 Visits: 228
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.
Martin Vrieze
Martin Vrieze
SSC-Addicted
SSC-Addicted (450 reputation)SSC-Addicted (450 reputation)SSC-Addicted (450 reputation)SSC-Addicted (450 reputation)SSC-Addicted (450 reputation)SSC-Addicted (450 reputation)SSC-Addicted (450 reputation)SSC-Addicted (450 reputation)

Group: General Forum Members
Points: 450 Visits: 125
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!!!w00t

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.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search