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


Internal auditors want access to production databases


Internal auditors want access to production databases

Author
Message
Sam Greene
Sam Greene
SSC-Addicted
SSC-Addicted (435 reputation)SSC-Addicted (435 reputation)SSC-Addicted (435 reputation)SSC-Addicted (435 reputation)SSC-Addicted (435 reputation)SSC-Addicted (435 reputation)SSC-Addicted (435 reputation)SSC-Addicted (435 reputation)

Group: General Forum Members
Points: 435 Visits: 584
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?
Grant Fritchey
Grant Fritchey
SSC-Dedicated
SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)

Group: General Forum Members
Points: 39518 Visits: 32632
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 Query Performance Tuning and SQL Server Execution Plans
Product Evangelist for Red Gate Software
Michael Valentine Jones
Michael Valentine Jones
SSCertifiable
SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)

Group: General Forum Members
Points: 5708 Visits: 11771
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.
VastSQL
VastSQL
SSCarpal Tunnel
SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)

Group: General Forum Members
Points: 4391 Visits: 5157
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.
Stamey
Stamey
Mr or Mrs. 500
Mr or Mrs. 500 (526 reputation)Mr or Mrs. 500 (526 reputation)Mr or Mrs. 500 (526 reputation)Mr or Mrs. 500 (526 reputation)Mr or Mrs. 500 (526 reputation)Mr or Mrs. 500 (526 reputation)Mr or Mrs. 500 (526 reputation)Mr or Mrs. 500 (526 reputation)

Group: General Forum Members
Points: 526 Visits: 1048
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.
Sam Greene
Sam Greene
SSC-Addicted
SSC-Addicted (435 reputation)SSC-Addicted (435 reputation)SSC-Addicted (435 reputation)SSC-Addicted (435 reputation)SSC-Addicted (435 reputation)SSC-Addicted (435 reputation)SSC-Addicted (435 reputation)SSC-Addicted (435 reputation)

Group: General Forum Members
Points: 435 Visits: 584
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.
Sam Greene
Sam Greene
SSC-Addicted
SSC-Addicted (435 reputation)SSC-Addicted (435 reputation)SSC-Addicted (435 reputation)SSC-Addicted (435 reputation)SSC-Addicted (435 reputation)SSC-Addicted (435 reputation)SSC-Addicted (435 reputation)SSC-Addicted (435 reputation)

Group: General Forum Members
Points: 435 Visits: 584
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.
Sam Greene
Sam Greene
SSC-Addicted
SSC-Addicted (435 reputation)SSC-Addicted (435 reputation)SSC-Addicted (435 reputation)SSC-Addicted (435 reputation)SSC-Addicted (435 reputation)SSC-Addicted (435 reputation)SSC-Addicted (435 reputation)SSC-Addicted (435 reputation)

Group: General Forum Members
Points: 435 Visits: 584
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!:-D
FNS
FNS
SSC-Enthusiastic
SSC-Enthusiastic (189 reputation)SSC-Enthusiastic (189 reputation)SSC-Enthusiastic (189 reputation)SSC-Enthusiastic (189 reputation)SSC-Enthusiastic (189 reputation)SSC-Enthusiastic (189 reputation)SSC-Enthusiastic (189 reputation)SSC-Enthusiastic (189 reputation)

Group: General Forum Members
Points: 189 Visits: 584
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).
Grant Fritchey
Grant Fritchey
SSC-Dedicated
SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)

Group: General Forum Members
Points: 39518 Visits: 32632
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!:-D


Excellent! I mean, you don't have to get it done today. Just do it before you go home. :-P

----------------------------------------------------
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 Query Performance Tuning and SQL Server Execution Plans
Product Evangelist for Red Gate Software
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