A Cheap Fix?

Question

A Cheap Fix?

Steve Jones - SSC Editor

SSC Guru

Points: 736162
More actions
March 3, 2010 at 8:39 pm

#139119

Comments posted to this topic are about the item A Cheap Fix?

Viewing 15 posts - 1 through 15 (of 18 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1004318 More actions · Answer 1

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.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

JamesNZ SSCommitted Points: 1615 More actions · Answer 2

Couldn't you consider using Resource Governor to control what these "power users" can do to stop affecting everything with their users?

reuben.anderson SSC Eights! Points: 826 More actions · Answer 3

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 Old Hand Points: 372 More actions · Answer 4

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 SSC Guru Points: 1004318 More actions · Answer 5

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:-D:hehe:;-)

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

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1004318 More actions · Answer 6

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.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Rich Mechaber SSChampion Points: 11130 More actions · Answer 7

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 SSC Eights! Points: 945 More actions · Answer 8

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 SSCrazy Points: 2760 More actions · Answer 9

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.

bwillsie-842793 Ten Centuries Points: 1359 More actions · Answer 10

Here's my experience.

Originally I used MS Access to create ad hoc queries against a production server. However, IT wants everyone to use their "SQL query Tool.".

A job string in Access took 30 seconds to download data from production server and run. The final output was a report and a spreadsheet. IT's "query tool" took six minutes to pull data from the server and create a rough spreadsheet that the users then had to restructer.

Which tool sounds more efficient?

SO, IT decides queries cause too much of a hit against the production server and installs a reporting server. The plan is all ERP reports, all reports using their "tool", and my ODBC/Access connectivity will be against that server.

And they decide to update the server using log shipping.

Result after six months? ERP reports still run against production server. I believe (but haven't checked yet to prove it) that all reports using their "tool" still run against the production server.

Why? because the reporting server gets new logs every half hour, goes into recovery mode, and kills all existing connections to the affected database(s). So, connectivity is lost 10 minutes out of every hour.

I say give users a reporting server if you want, but make it a workable solution for the users.

The reality is users frequently need the ability to retrieve information quickly and easily in a format they can work with. Usually this means using ad hoc tools. These tools are also a great way to offload some development issues from IT.

I also believe users need to be trained in using the tools properly. This means explaining to users what causes inefficient queries and how to prevent them. Or perhaps developing power users within departments that create queries for others.

The bottom line is the information is for their use, not the DBA's, and they should have fast, easy access to it in any manner they need to keep the checks coming in, the bills paid, and organization running legally and hopefully ethically.

To put it bluntly, the most secure database on the best tuned instance of SQL Server maintained by the most proficient DBA is totally worthless if the right information can't get into the right hands at the right time.

RonKyle SSC-Dedicated Points: 31556 More actions · Answer 11

Sometimes the database has so many design issues that the final effect of performance tuning is going to be limited. In this case a second database is likely worth it. If this can be part of an HA solution, so much the better, as then you are killing two birds with one stone. This second one can also be used to do more frequent OLAP uploads than might otherwise be possible.

The Natrix SSC Eights! Points: 835 More actions · Answer 12

I think power users shouldn't have access to the production environment. Most companies have a test environment where queries can be run, so whatever queries the power user has should be run there, or on a replicated database. I would prefer to manage an extra instance than have them messing up the production database.

bwillsie-842793 Ten Centuries Points: 1359 More actions · Answer 13

ncodner (3/4/2010)
Most companies have a test environment where queries can be run...

Most test environments are out of date or not suitable for reporting for various reasons.

I'm happy to use a reporting server as long as it is as current as we need(minimum of 5 minutes, preferably 5 seconds). I hate it when users say "I just entered this transaction and it isn't showing on my report..."

And, it offers the same level of connection reliability as the production server.

Jeff Moden SSC Guru Points: 1004318 More actions · Answer 14

rmechaber (3/4/2010)
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

Yes... NOT an "SQL Snapshot". I believe the term the boys from OPS used was a SAN "clone". I wasn't involved much in how it was done but it's a software option (meaning that it costs a bit more, I suppose) that can be purchased with most SANS (if it doesn't come for free). What I did get to see was just how very effective it was. I was totally amazed at how fast it was. The reporting server had to be "down" during the "snapshot" transfer, but that was for all of about 2 minutes right at midnight for a tera-byte of databases. The production server never missed a beat during the process.

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

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)