Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 123»»»

Internal auditors want access to production databases Expand / Collapse
Author
Message
Posted Monday, April 27, 2009 5:37 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Thursday, September 25, 2014 7:11 AM
Points: 253, Visits: 546
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
Posted Monday, April 27, 2009 8:48 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 3:26 PM
Points: 13,871, Visits: 28,266
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
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
Posted Tuesday, April 28, 2009 12:42 AM
Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Friday, October 3, 2014 2:23 AM
Points: 3,108, Visits: 11,503
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
Posted Tuesday, April 28, 2009 1:30 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Yesterday @ 3:19 AM
Points: 2,631, Visits: 3,974
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
Posted Tuesday, April 28, 2009 8:05 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Yesterday @ 8:17 AM
Points: 233, Visits: 923
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
Posted Tuesday, April 28, 2009 9:39 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Thursday, September 25, 2014 7:11 AM
Points: 253, Visits: 546
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
Posted Tuesday, April 28, 2009 9:40 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Thursday, September 25, 2014 7:11 AM
Points: 253, Visits: 546
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
Posted Tuesday, April 28, 2009 9:43 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Thursday, September 25, 2014 7:11 AM
Points: 253, Visits: 546
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
Posted Tuesday, April 28, 2009 9:54 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, September 6, 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
Posted Tuesday, April 28, 2009 10:08 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 3:26 PM
Points: 13,871, Visits: 28,266
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 Query Performance Tuning
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 »

Add to briefcase 123»»»

Permissions Expand / Collapse