November 12, 2010 at 11:59 am
I don't want to do it, but I have to: grant read only access to production databases to business analysts for purposes of reporting. Invariably I am then asked to resolve a performance issue because some neophyte has executed a query resulting in a Cartesian Product or some other non-sense.
So, I need suitable documentation, ammunition, etc to argue the case for either denying business users the ability to execute ad-hoc queries against the databases or, at the very least, requiring demonstration of knowledge/skills before granting access. What do you all do when the boss says that the business analyst in Operations needs read only access to production databases? Because I for one am tired of having to yes all the time.
Gordon Pollokoff
"Wile E. is my reality, Bugs Bunny is my goal" - Chuck Jones
November 12, 2010 at 1:00 pm
Tell the boss that to maintain user satisfaction and acceptable overall performance you would prefer to set up a replica (replication or mirroring with snapshot) of the production server and allow the business analysts to do all their analysis there where they won't affect the main applications
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
November 15, 2010 at 1:22 pm
GilaMonster (11/12/2010)
Tell the boss that to maintain user satisfaction and acceptable overall performance you would prefer to set up a replica (replication or mirroring with snapshot) of the production server and allow the business analysts to do all their analysis there where they won't affect the main applications
A mirrored/snapshotted reporting database is a great start, but if you cant spare the hardware..how many reports are we talking about? Can you just create SPs to return the data, then grant them access to those (with parameters)?
November 15, 2010 at 1:27 pm
A separate copy, like Gail mentioned, is what I've recommended in the past. If what the analysts are doing has value to the business, the RoI on hardware for that is pretty easy to prove.
- 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
November 22, 2010 at 11:52 pm
So, what is the "best practice"?
Stored Procedures accessing base tables, views.
Database Roles granting execute / select / update permissions to objects depending on Role
Logins assigned Roles depending on business requirement? (I use the Active Directory security groups as logins for ease-of-maintenance)
We are a rapidly growing company, and really are evolving from structured Ad-Hoc reports off base tables 🙂 into Reporting Services off aggregated datasets. As this growth is occurring our developers are constantly improving (changing) things, and users are doing their thing (with script files listing queries, and updates and deletes occurring regularly)...
I have been tasked with "locking down the data"... and they aren't changing the way they do their data extracts. At this time the RS reports, CLR webservices, etc. use queries... not SP's. I should like to have a list of options regarding the best way forward to limit the destructive aspects of the user access... ideas? Resources?
Thanks in advance...
So long, and thanks for all the fishpaste 😉
November 29, 2010 at 11:23 am
Typically, we offload reporting to a reporting copy of the data using <insert_ETL/reporting_method_here>, and most (pronounced "all") of the time it is because report queries "get out of hand" on the source database. Unfortunately, there is no "one-size-fits-all" answer for this, but I do have some guidelines for my report writers.
1) use stored procedures as your data sources,
2) use stored procedures as your data sources, and
3) use stored procedures as your data sources.
If you use stored procedures as your data source, you will always have the ability to optimize any garbage that report writers throw at you, and can delay the inevitable costs associated with moving to reporting infrastructure. After that is established, you can monitor for long running queries and explore interesting options like resource-governor and query hints. But it is a very uphill battle if you are chasing ad-hoc queries that are embedded in report sources. If management is insisting on reporting from "live" data, at least have them do it in a way that can be throttled and controlled in the event that it becomes too cumbersome.
Also, as an aside, I would recommend the use of a separate database on the same server to hold logical code based objects for reporting just to keep security and portability (report views and stored procedures). The optimizer won't care, and you can keep "direct table access" to a minimum.
Few quick notes on this (sorry for the edit - you hit one of my soapboxes):
1) stored procedures lend easily to code and peer review prior to deployment - which could eliminate your "neophyte" issue and "demonstration of skill" requirements;
2) I've adapted a "yes, if ..." or "yes, but ..." method of meeting outrageous or tedious requests. The answer to the guys handing you a paycheck should never be "no," but they do pay you to be smart. Give them an answer that works for you and them; and
3) offloading reports to a separate physical structure is inevitable if you expect any amount of growth in data size and user base. The trick to impressing your boss is to implement modular design early that will both prolong the inevitable move to a separate structure by allowing every possible performance mechanism, and decrease the amount of time to shift logical objects to a new reporting structure when that time comes. Save money in hardware now, save money in time later.
November 29, 2010 at 1:04 pm
Alocyte (11/22/2010)
So, what is the "best practice"?
Let me translate what everybody is saying: "don't allow reporting against OLTP database; as a quick fix you can create a separate Reporting database as a clone of your OLTP, as a long term solution plan for a Data Warehouse sourcing from OLTP."
Hope this helps.
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.December 7, 2010 at 8:23 am
Thanks, guys, for affirming my understanding:
Query for reports: PROCS
While migrating the environment from Free-For-All to Reports-Only - I need to go through the locking down phase first. At the lowest level there is Integrated Authentication and SQL User.
History has such a hold and Integrated Authentication far out shines hardcoded connection strings in myriad excel / vba / vb applications... so while these are not RS reports, compiled enquiries (i.e. peer reviewed and optimised queries) how do I secure the db content from some users (AD group A) and expose the data (AD group B) - in the best way for the future not to require redefinitions of the reports (Developer can pick up the spreadsheet, copy the functionality in a RS report and publish it without compromising my carefully crafted access solution)?
I guess it boils down to AD group being given a db role, and the role db permissions OR giving the AD group db permissions. hmmm or am I missing another possibility?
I am experiencing morphing pains... best would likely be to discuss what the final or "blue sky" solution is that we are aiming for, and work backwards.
Any recommendations? Case studies of change controls in action?
So long, and thanks for all the fishpaste 😉
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply