<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
»
SQL Server 2005
»
Administering
»
Internal auditors want access to production...
23 posts, Page 1 of 3
1
2
3
»
»»
Internal auditors want access to production databases
Rate Topic
Display Mode
Topic Options
Author
Message
Sam Greene
Sam Greene
Posted Monday, April 27, 2009 5:37 PM
SSC Veteran
Group: General Forum Members
Last Login: Monday, May 13, 2013 2:36 PM
Points: 253,
Visits: 492
Auditors had access to another system using MS Access. We've replaced that system with another and they would like their access to production data back via MS Access. I don't want to give them this type of access to run adhoc and MSAccess is a consistent poor performer against our server.
The database is 2GB and growing.
The only option I can come up with is to make a copy of the database on the same server.
We don't have another server to move it to. I considered our development environment, but it would be a licensing issue.
The database is too large for access.
Any ideas?
Post #705434
Grant Fritchey
Grant Fritchey
Posted Monday, April 27, 2009 8:48 PM
SSChampion
Group: General Forum Members
Last Login: Today @ 4:50 AM
Points: 13,371,
Visits: 25,143
Since you are worried about licensing, but you correctly are trying to avoid Access... This is hardly and optimal solution, but what about another instance on the same server. You can put limits on it to try to keep it masked off from your production instance. Limit it's memory and keep it down to a single processor. That'll also allow you a bit of leeway in terms of security. It's not perfect, but I think it'll work out better than putting it on the same instance as your production data.
Move to 2008 then you can put the Resource Governor to work along with some policy based management.
----------------------------------------------------
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood..." Theodore Roosevelt
The Scary DBA
Author of:
SQL Server 2012 Query Performance Tuning
SQL Server 2008 Query Performance Tuning Distilled
and
SQL Server Execution Plans
Product Evangelist for
Red Gate Software
Post #705474
Michael Valentine Jones
Michael Valentine Jones
Posted Tuesday, April 28, 2009 12:42 AM
SSCrazy
Group: General Forum Members
Last Login: Yesterday @ 3:14 PM
Points: 2,941,
Visits: 10,481
Tell the auditors that they will have to supply their own server(s) because Access will cause problems on the production server
They can restore the prod DB onto their server and run all the queries they want.
Post #705534
Ratheesh.K.Nair
Ratheesh.K.Nair
Posted Tuesday, April 28, 2009 1:30 AM
SSCrazy
Group: General Forum Members
Last Login: 2 days ago @ 3:21 AM
Points: 2,415,
Visits: 3,370
Why can't you try installing an Express Edition and then restore your DB .
Since your DB is less than 4Gb you can use SQL Server Express Edition and Express Edition is free.
Post #705552
Stamey
Stamey
Posted Tuesday, April 28, 2009 8:05 AM
SSC Veteran
Group: General Forum Members
Last Login: Thursday, April 11, 2013 11:44 AM
Points: 222,
Visits: 866
My solution for this, in the many cases I have run into in the past, is to restore the nightly backup of said DB to another DB on the same server, called [DB]Reports and tell the people to use that one. Typically it doesn't tax the server hardware enough to matter, and it causes no locks or waits on the production data, so everyone is happy. As part of the automated restore process I run the necessary scripts to grant or limit access for the various users.
Chris
Learning something new on every visit to SSC. Hoping to pass it on to someone else.
Post #705850
Sam Greene
Sam Greene
Posted Tuesday, April 28, 2009 9:39 AM
SSC Veteran
Group: General Forum Members
Last Login: Monday, May 13, 2013 2:36 PM
Points: 253,
Visits: 492
Ratheesh.K.Nair (4/28/2009)
Why can't you try installing an Express Edition and then restore your DB .
Since your DB is less than 4Gb you can use SQL Server Express Edition and Express Edition is free.
Not a bad idea, but it will be over 4GB in one year.
Post #705996
Sam Greene
Sam Greene
Posted Tuesday, April 28, 2009 9:40 AM
SSC Veteran
Group: General Forum Members
Last Login: Monday, May 13, 2013 2:36 PM
Points: 253,
Visits: 492
Stamey (4/28/2009)
My solution for this, in the many cases I have run into in the past, is to restore the nightly backup of said DB to another DB on the same server, called [DB]Reports and tell the people to use that one. Typically it doesn't tax the server hardware enough to matter, and it causes no locks or waits on the production data, so everyone is happy. As part of the automated restore process I run the necessary scripts to grant or limit access for the various users.
Chris
This will probably be what we do.
Post #706001
Sam Greene
Sam Greene
Posted Tuesday, April 28, 2009 9:43 AM
SSC Veteran
Group: General Forum Members
Last Login: Monday, May 13, 2013 2:36 PM
Points: 253,
Visits: 492
Grant Fritchey (4/27/2009)
Move to 2008 then you can put the Resource Governor to work along with some policy based management.
We're just finishing up our migration of 170 dbs to 2005. I'll get started on that ASAP boss!
Post #706006
FNS
FNS
Posted Tuesday, April 28, 2009 9:54 AM
SSC-Enthusiastic
Group: General Forum Members
Last Login: Tuesday, September 06, 2011 7:51 AM
Points: 105,
Visits: 581
As others have said, don't let them run ad-hoc queries against the production system for their reports, it will all end in tears. People with no appreciation for SQL won't think twice about running SELECT *, exporting the results into Excel then using Excel to find the 1 record they actually wanted in the first place.
I would keep them off the production box altogether. It's been my experience that once a system is in place, however temporary you may have intended it to be, it suddenly becomes the "business critical" can't-be-changed-ever way going forward. And if the production db is slow it's your fault (as the DBA) not their fault because they are running "essential reports for senior managers".
I think you could make a good business case for them having their own server with a copy of the db. Cost would be minimal if you use SQL Express and, dare I say it, a desktop PC with 4Gb RAM. I'd probably still attempt to persuade them that they need a server with SQL Standard but be prepared to drop back to hideous desktop PC solution later. It will make the cost seem suddenly uber cheap and then they feel they've "saved" the company money by digging their heels in and overcoming the nasty DBA.
When (not if) the PC hard drive decides to throw in the towel you can smugly point out they should have gone for a server in the first place (with RAID 1 on the disk).
Post #706023
Grant Fritchey
Grant Fritchey
Posted Tuesday, April 28, 2009 10:08 AM
SSChampion
Group: General Forum Members
Last Login: Today @ 4:50 AM
Points: 13,371,
Visits: 25,143
sam (4/28/2009)
Grant Fritchey (4/27/2009)
Move to 2008 then you can put the Resource Governor to work along with some policy based management.
We're just finishing up our migration of 170 dbs to 2005. I'll get started on that ASAP boss!
Excellent! I mean, you don't have to get it done today. Just do it before you go home.
----------------------------------------------------
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood..." Theodore Roosevelt
The Scary DBA
Author of:
SQL Server 2012 Query Performance Tuning
SQL Server 2008 Query Performance Tuning Distilled
and
SQL Server Execution Plans
Product Evangelist for
Red Gate Software
Post #706039
« Prev Topic
|
Next Topic »
23 posts, Page 1 of 3
1
2
3
»
»»
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.
