June 4, 2013 at 10:55 am
I am looking for resources to help evaluate options for allowing users to query our data mart directly. (T-SQL). These are not experienced users and we can't risk having them block existing processes or using too many server resources. This is a large DM. If someone can point me to some resources or articles addressing this I'd appreciate it. I'm aware of the options but not the pros/cons of each regarding this scenario. Additional hardware is not an option but new instances/databases are.
Specs:
SQL Server 2008 R2 active/passive 2 node cluster
40TB database
2TB largest fact table
Thanks.
-Brian
June 4, 2013 at 11:25 am
Brian Carlson (6/4/2013)
I am looking for resources to help evaluate options for allowing users to query our data mart directly. (T-SQL). These are not experienced users and we can't risk having them block existing processes or using too many server resources. This is a large DM. If someone can point me to some resources or articles addressing this I'd appreciate it. I'm aware of the options but not the pros/cons of each regarding this scenario. Additional hardware is not an option but new instances/databases are.Specs:
SQL Server 2008 R2 active/passive 2 node cluster
40TB database
2TB largest fact table
ad-hoc queries would cause problems sooner or later but, they come with the turf, don't they?
As far as mitigation, I would suggest to check Resource Governor.
_____________________________________
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.June 4, 2013 at 11:38 am
Thanks Paul. Right now I'm leaning towards replicating the most relevant tables to another database and then using the resource governer to limit resource usage. I thought about mirroring but can't use another 40TB drive space right now. We just got a SAN upgrade and the infrastructure would be upset if we used it up too quickly. It's supposed to last us 3 years minimum. 🙂 I'm also trying to think about snapshots and other possibilities. I'm not sure how snapshot select locking works though. I'll have to research that. For now, we've taken a hard-line about not allowing direct access. We do have a query tool that submits queries and then emails the results in a spreadsheet. I'm not sure about the pros/cons of these different approaches though and want to be prepared when I present a solution and make sure I don't miss anything.
Unfortunately, I don't have a key term to google about this subject. "ad-hoc queries" and "allow external access" get me tech "how-to" articles involving sp_configure and so forth instead of architecture articles.
Thanks.
June 5, 2013 at 10:07 am
From my experience, it's a waste of time to allow non-technical users to run ad-hoc queries against a database. What will typically happen is that they'll run a bunch of "SELECT *" queries against tables, and then when they see that it's taking hours to run, or they can't get the results they need, they'll end up coming to you at the end of the day anyhow. Meanwhile your server will get hammered.
Perhaps what you need to do is ask them what type of reporting they need, and then build an Analysis Services cube that gets refreshed nightly. Once done, they can use pitvot tabes in Excel to slice and dice to their hearts context, the results will come back quickly, and it won't hit your server during peak hours.
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
June 5, 2013 at 4:40 pm
Agreed about the problems with allowing non-technical users access. One of the requirements to get login permissions will be passing a SQL Query exam though. Of course, user's may cheat and help each other out since it will likely be on-line or some honor system. Unfortunately, I'm just the guy hired to figure out how to do it (among other things). I'm not making the decision whether to allow it. I think their motivation is that they can't keep up with user requests for reports and data feeds. This is a multi-terabyte data mart with a lot of users (100?) accessing it and there are only 7 database developers on the BI team.
I wound up liking the idea of using a database snapshot with the resource governor. It looks like the resource governor is in, but not the database snapshot. They're just going to allow them direct access to the underlying tables.
June 6, 2013 at 6:20 am
Brian Carlson (6/5/2013)
Agreed about the problems with allowing non-technical users access. One of the requirements to get login permissions will be passing a SQL Query exam though. Of course, user's may cheat and help each other out since it will likely be on-line or some honor system. Unfortunately, I'm just the guy hired to figure out how to do it (among other things). I'm not making the decision whether to allow it. I think their motivation is that they can't keep up with user requests for reports and data feeds. This is a multi-terabyte data mart with a lot of users (100?) accessing it and there are only 7 database developers on the BI team.I wound up liking the idea of using a database snapshot with the resource governor. It looks like the resource governor is in, but not the database snapshot. They're just going to allow them direct access to the underlying tables.
Another option is to write a collection of views, so you control how underlying tables are joined, and then users are granted select permission on the views.
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
June 6, 2013 at 10:56 am
The views option would work if we could prevent them from joining views. There might be a way to prevent that but I'm not sure how. I read an article a while back about how to prevent queries without a where clause. It's been too long now though and I don't remember how to do it.
June 6, 2013 at 11:03 am
Brian Carlson (6/6/2013)
The views option would work if we could prevent them from joining views. There might be a way to prevent that but I'm not sure how. I read an article a while back about how to prevent queries without a where clause. It's been too long now though and I don't remember how to do it.
It's OK for them to join the views. The purpose of the view is just to insure that key tables are joined correctly.
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply